RE: Regarding the usage of mutex in the mysql connection threading - laks...@wipro.com - com.mysql.lists.mysql

9 messages in com.mysql.lists.mysqlRE: Regarding the usage of mutex in t...

From	Sent On	Attachments
laks...@wipro.com	30 Aug 2005 05:54
SGr...@unimin.com	30 Aug 2005 06:10
laks...@wipro.com	30 Aug 2005 06:54
laks...@wipro.com	30 Aug 2005 07:03
laks...@wipro.com	30 Aug 2005 07:09
SGr...@unimin.com	30 Aug 2005 07:32
SGr...@unimin.com	30 Aug 2005 07:45
laks...@wipro.com	30 Aug 2005 07:55
SGr...@unimin.com	30 Aug 2005 08:20

Subject:	RE: Regarding the usage of mutex in the mysql connection threading
From:	laks...@wipro.com (laks...@wipro.com)
Date:	08/30/2005 07:09:05 AM
List:	com.mysql.lists.mysql

Hi,

Please advise us whether mysql/myodbc

- handles different queries/write from different threads within the same connection or not? - handles different queries (or batch fetches) from multi-connections or not?

Thanks, Narasimha

-----Original Message----- From: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks) Sent: Tuesday, August 30, 2005 7:34 PM To: mys...@lists.mysql.com Cc: 'SGr...@unimin.com' Subject: RE: Regarding the usage of mutex in the mysql connection threading

hi ,

Could any one of you please let me know in mysql/myODBC that the session control is now under their management meaning

- handles different queries/write from different threads within the same connection

- handles different queries (or batch fetches) from multi-connections

Please reply asap.

Thanks, Narasimha

-----Original Message----- From: SGr...@unimin.com [mailto:SGr...@unimin.com] Sent: Tuesday, August 30, 2005 6:40 PM To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks) Cc: mys...@lists.mysql.com Subject: Re: Regarding the usage of mutex in the mysql connection threading

<laks...@wipro.com> 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.

Confidentiality Notice