List:General Discussion« Previous MessageNext Message »
From:<lakshmi.narasimharao Date:August 30 2005 2:56pm
Subject:RE: Regarding the usage of mutex in the mysql connection threading
View as plain text  
Hi Green,

We are at the last stage of the project (migration from Oracle to
MySQL). We are demanded to adopt the MYODBC as a customer requrement.
As mutex was implemented for oracle so it is there in our code. Now we
are thinking to remove that because application level we have connection
id.
So, please advise us in this case can we remove the mutex?.

I beleive that in the ODBC and in MySQL mutex will be handled
automatically, right?.

Regards
Narasimha

-----Original Message-----
From: SGreen@stripped [mailto:SGreen@stripped]
Sent: Tuesday, August 30, 2005 8:03 PM
To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
Cc: mysql@stripped
Subject: RE: Regarding the usage of mutex in the mysql connection
threading




<lakshmi.narasimharao@stripped> wrote on 08/30/2005 10:03:51 AM:

> 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

<lakshmi.narasimharao@stripped> wrote on 08/30/2005 09:54:54 AM:

> In brief if I explain the architecture it something goes like this:-
> -  Element management Apllications uses MySQL database through DB
access
> layer.
> -  DB access layer provides the application some APIs needed for DB
> opearations.
> -  for Each DB transactions the applications login to the database->
> gets free connection id from pool (local data structure maintained in
> application)  -> do the operation -> logout -> return the connection
id
> to the pool.
> - we are going to MySQL through ODBC which identifies the transactions
> by there connection handles.
> So every parralel transactions are having separate connection id and
> separate handles for ODBC.
>
>
> Now in this kind of implementation do we need to put any of the sql
> statement execution / reading from result set opearation into a
critical
> section (mutex)?
>
> -----Original Message-----
> From: SGreen@stripped [mailto:SGreen@stripped]
>
> Sent: Tuesday, August 30, 2005 6:40 PM
> To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
> Cc: mysql@stripped
> Subject: Re: Regarding the usage of mutex in the mysql connection
> threading
>
> <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
>
>

I will respond to both emails at once (or at least try to). First, I
have to ask: Why are you trying to use ODBC?

I ask because you describe "critical sections" and "mutexes" which are
lower level programming concepts. If you are working at that level you
have a much easier and more direct access to the database if you use the
MySQL C API (Chapter 23 in the current manual). The DLLs, .h files, and
.lib files should already exist on your system.

You explain that each thread/process gets its own connection (without
sharing) from the ODBC connection manager pool. You asked if you now
needed to synchronize access so that your various connections are only
in use one-at-a-time. The answer is "no" because you do not SHARE
connections. You do not need to worry about concurrent connection usage
so long as only one thread or process is using any single connection at
any one time.

Now, because you are using a connection pool, a minimum number of
connections are created and maintained by the pool manager. Each
connection has specific to it any user variables or temporary variables
created with that connection. Those will persist until the connection is
closed by the pool manager.  Here is an example:

1) Process A borrows connection 1 from the pool, creates the user
variable "@proccount" then disconnects (returns the connection to the
pool)

2) Process B needs a connection and gets connection 1 from the pool.
Within process b the MySQL user variable "@proccount" still exists and
contains whatever value Process A left it with. This is because the
variables are connection-specific. The same thing happens with temporary
tables as they are also connection-specific.

If Process B requested a connection before Process A had released
connection 1, it could have either gotten another connection from the
pool (if one were available) -or- it would receive some error to the
effect that the pool is as large as it can get and no more connections
are available at this time -or- it could go into a wait state until a
connection became available. Exactly which scenario happens depend on
precisely how you interact with the pool manager and how the pool
manager is configured to handle your particular pool.

You can actually turn off (disable) connection pooling in the ODBC
manager on a driver-by-driver basis. This prevents the "carry over" I
just described when one thread/process inherits a connection that has
been previously used. Disabling pooling would also mean that you would
no longer have a pool of pre-established connections to draw from. Each
connect/disconnect will create and destroy a connection as it is used
(probably a good thing to do).

As I said before, your testing will reveal which method works best for
your application.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine





Confidentiality Notice

The information contained in this electronic message and any attachments to this message
are intended
for the exclusive use of the addressee(s) and may contain confidential or privileged
information. If
you are not the intended recipient, please notify the sender at Wipro or
Mailadmin@stripped immediately
and destroy all copies of this message and any attachments.
Thread
Regarding the usage of mutex in the mysql connection threadinglakshmi.narasimharao30 Aug
  • Re: Regarding the usage of mutex in the mysql connection threadingSGreen30 Aug
RE: Regarding the usage of mutex in the mysql connection threadinglakshmi.narasimharao30 Aug
RE: Regarding the usage of mutex in the mysql connection threadinglakshmi.narasimharao30 Aug
  • RE: Regarding the usage of mutex in the mysql connection threadingSGreen30 Aug
RE: Regarding the usage of mutex in the mysql connection threadinglakshmi.narasimharao30 Aug
  • RE: Regarding the usage of mutex in the mysql connection threadingSGreen30 Aug
RE: Regarding the usage of mutex in the mysql connection threadinglakshmi.narasimharao30 Aug
  • RE: Regarding the usage of mutex in the mysql connection threadingSGreen30 Aug