<lakshmi.narasimharao@stripped> wrote on 08/30/2005 08:54:44 AM:
>
> Hi,
>
>
> We migrated a NMS project from oracle 7.3 database to mysql
> 4.0.23. In the migration we kept the mutex mechanism followed in the
> oracle for connection threading. Actually these mutex are mainly used
> before executing the sql statements which returns multiple number of
> records and released immediately after the execution. Could any one of
> you please advise us, whether this mutex mechanism is really required in
> mysql 4.0.23 or not?. Is there any problem if we did not use mutex for
> connection threading?. Please advise us and send the reaply asap as it
> is very urgent.
>
> Thanks,
> Narasimha
>
>
It sounds as though you SHARE at least one connection between several
processes/threads. If that is what you do, then YES. You will need a mutex
to prevent one process from using a connection currently being used by
another process. A more scalable solution for a managed connection
environment would be to build a connection pool and allow your processes
to borrow connections to the pool only as long as they need them. That way
each process has their OWN CONNECTION and you won't have to worry about
concurrent requests. One caution with this technique: MySQL variables and
temporary tables are connection specific. If you do not properly manage
your variables or your temporary tables when entering or exiting a
borrowed connection you may run into data created by a process that
previously the connection (This is true in any SHARED connection
scenario). Ensure that when your application ends, that all of the
connections are properly closed.
The best solution may be for each process/thread to manage its own
connection independently. An effective way to prevent "connection
overload" on your server is to make sure you properly close every
connection as soon as you are through using it. If your application
performs database activity in bursts (do some database work, wait for
user, do more database work, wait for user...) it may be an optimal design
choice to close the connection after each burst. However, that bit of
tuning is best decided by benchmarking on your equipment with your
software operating under both normal and abnormal loads. Use whichever
connection plan works best during testing.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
