|
Home > Archive > MySQL ODBC Connector > August 2005 > Regarding the usage of mutex in the mysql connection threading
You are viewing an archived Text-only version of the thread.
To view this thread in it's original format and/or if you want to reply to
this thread please [click here]
| Author |
Regarding the usage of mutex in the mysql connection threading
|
|
|
|
Hi,
=0D
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
Confidentiality Notice=0D
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@wipro.com immediately
and destroy all copies of this message and any attachments.
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql? unsub...sie.nctu.edu.tw
| |
| SGreen@unimin.com 2005-08-30, 9:25 am |
| --=_alternative 0048BA988525706D_=
Content-Type: text/plain; charset="US-ASCII"
<lakshmi.narasimharao@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.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
--=_alternative 0048BA988525706D_=--
| |
|
| ------ _=_NextPart_001_01C5
AD6A.393E2205
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi Green,
=0D
Thanks a lot for your reply.
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.
=0D
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)?
=0D
Please send the reply asap.
=0D
Regards,
Narasimha,=0D
-----Original Message-----
From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]=0D
Sent: Tuesday, August 30, 2005 6:40 PM
To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
Cc: mysql@lists.mysql.com
Subject: Re: Regarding the usage of mutex in the mysql connection
threading
<lakshmi.narasimharao@wipro.com> wrote on 08/30/2005 08:54:44 AM:
>=0D
> Hi,
> =0D
>=0D
> 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.
>=0D
> Thanks,
> Narasimha
>=0D
>=0D
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.=0D
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.=0D
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=0D
Confidentiality Notice=0D
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@wipro.com immediately
and destroy all copies of this message and any attachments.
------ _=_NextPart_001_01C5
AD6A.393E2205--
| |
|
| ------ _=_NextPart_001_01C5
AD6B.79C6A8C2
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
hi ,
=0D
Could any one of you please let me know in mysql/myODBC
that the session control is now under their management meaning=0D
- handles different queries/write from different threads within the same
connection=0D
- handles different queries (or batch fetches) from multi-connections=0D
=0D
=0D
Please reply asap.=0D
=0D
Thanks,
Narasimha
-----Original Message-----
From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]=0D
Sent: Tuesday, August 30, 2005 6:40 PM
To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
Cc: mysql@lists.mysql.com
Subject: Re: Regarding the usage of mutex in the mysql connection
threading
<lakshmi.narasimharao@wipro.com> wrote on 08/30/2005 08:54:44 AM:
>=0D
> Hi,
> =0D
>=0D
> 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.
>=0D
> Thanks,
> Narasimha
>=0D
>=0D
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.=0D
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.=0D
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=0D
Confidentiality Notice=0D
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@wipro.com immediately
and destroy all copies of this message and any attachments.
------ _=_NextPart_001_01C5
AD6B.79C6A8C2--
| |
|
| ------ _=_NextPart_001_01C5
AD6C.4150656D
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi,
=0D
Please advise us whether mysql/myodbc=0D
=0D
- handles different queries/write from different threads within the same
connection or not?=0D
- handles different queries (or batch fetches) from multi-connections or
not?
=0D
Thanks,
Narasimha
-----Original Message-----
From: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)=0D
Sent: Tuesday, August 30, 2005 7:34 PM
To: mysql@lists.mysql.com
Cc: 'SGreen@unimin.com'
Subject: RE: Regarding the usage of mutex in the mysql connection
threading
hi ,
=0D
Could any one of you please let me know in mysql/myODBC
that the session control is now under their management meaning=0D
- handles different queries/write from different threads within the same
connection=0D
- handles different queries (or batch fetches) from multi-connections=0D
=0D
=0D
Please reply asap.=0D
=0D
Thanks,
Narasimha
-----Original Message-----
From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]=0D
Sent: Tuesday, August 30, 2005 6:40 PM
To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
Cc: mysql@lists.mysql.com
Subject: Re: Regarding the usage of mutex in the mysql connection
threading
<lakshmi.narasimharao@wipro.com> wrote on 08/30/2005 08:54:44 AM:
>=0D
> Hi,
> =0D
>=0D
> 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.
>=0D
> Thanks,
> Narasimha
>=0D
>=0D
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.=0D
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.=0D
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine=0D
Confidentiality Notice=0D
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@wipro.com immediately
and destroy all copies of this message and any attachments.
------ _=_NextPart_001_01C5
AD6C.4150656D--
| |
| SGreen@unimin.com 2005-08-30, 11:25 am |
| --=_alternative 005045E98525706D_=
Content-Type: text/plain; charset="US-ASCII"
<lakshmi.narasimharao@wipro.com> 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@wipro.com> 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@unimin.com & #91;mailto:SGreen@un
imin.com]
>
> Sent: Tuesday, August 30, 2005 6:40 PM
> To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
> Cc: mysql@lists.mysql.com
> Subject: Re: Regarding the usage of mutex in the mysql connection
> threading
>
> <lakshmi.narasimharao@wipro.com> wrote on 08/30/2005 08:54:44 AM:
>
> in
>
>
> 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
--=_alternative 005045E98525706D_=--
| |
|
| ------ _=_NextPart_001_01C5
AD72.CEDEDE44
Content-Type: text/plain;
charset="us-ascii"
Content-Transfer-Encoding: quoted-printable
Hi Green,
=0D
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?.
=0D
I beleive that in the ODBC and in MySQL mutex will be handled
automatically, right?.=0D
=0D
Regards
Narasimha
-----Original Message-----
From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]=0D
Sent: Tuesday, August 30, 2005 8:03 PM
To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
Cc: mysql@lists.mysql.com
Subject: RE: Regarding the usage of mutex in the mysql connection
threading
<lakshmi.narasimharao@wipro.com> 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@wipro.com> 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.
>=0D
>=0D
> 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)?
>=0D
> -----Original Message-----
> From: SGreen@unimin.com & #91;mailto:SGreen@un
imin.com]
>=0D
> Sent: Tuesday, August 30, 2005 6:40 PM
> To: Lakshmi NarasimhaRao (WT01 - Voice & Next Generation Networks)
> Cc: mysql@lists.mysql.com
> Subject: Re: Regarding the usage of mutex in the mysql connection
> threading
>=0D
> <lakshmi.narasimharao@wipro.com> wrote on 08/30/2005 08:54:44 AM:=0D
>=0D
used[color=darkred]
of[color=darkred]
required[color=darkr
ed]
> in
for[color=darkred]
it[color=darkred]
>=0D
>=0D
> 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.
>=0D
>=0D
> 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.
>=0D
>=0D
>=0D
> Shawn Green
> Database Administrator
> Unimin Corporation - Spruce Pine
>=0D
>=0D
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?=0D
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.=0D
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.=0D
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:=0D
1) Process A borrows connection 1 from the pool, creates the user
variable "@proccount" then disconnects (returns the connection to the
pool)=0D
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.=0D
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.=0D
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).=0D
As I said before, your testing will reveal which method works best for
your application.=0D
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Confidentiality Notice=0D
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@wipro.com immediately
and destroy all copies of this message and any attachments.
------ _=_NextPart_001_01C5
AD72.CEDEDE44--
| |
| SGreen@unimin.com 2005-08-30, 11:25 am |
| --=_alternative 0054A3E28525706D_=
Content-Type: text/plain; charset="US-ASCII"
I can not and will not make an absolute recommendation to keep or lose
the mutex before I performed a full code review. IMHO, the final stages of
a project is one of the worst times to be making this kind of decision as
it could impact so much work already accomplished.
If you are certain that each connection can only be used by one
thread/process at a time, then you should not need to synchronize
(serialize) access to any connection. Connection pooling IS NOT connection
sharing.
It sounds to me that the previous version of your application shared a
single connection between several threads and required a transaction-level
mutex to ensure proper SQL command serialization. You probably do not
need that now as you are not _sharing_ a single common connection between
more than one execution at a time. If you DO share a connection between
two or more threads or processes, you will need to keep the mutex to keep
one thread from clobbering the SQL being executed by the other.
Does MySQL or ODBC handle the mutex? No, that is part of your execution
environment (usually handled by the OS). Will ODBC pool connections? Yes,
if you allow it to do so. MySQL keeps all connections separated from each
other until the point data is committed to the database (transactional
boundaries). How your application uses transactions is up to you. Under
most circumstances, what one connection is doing is invisible to what
another connection is doing up to the point at which one of them commits
their changes to the database. Then those changes may, depending on your
transaction isolation level, become instantly visible to the other
connection or not. As I said, it all depends.
If each thread/process establishes its own connection to the database
server, you are in a situation (from the point of view of the database
server) identical to what would be happening if all of your processes were
connecting in from different physical machines. If you need a mutex to
serialize access under that scenario, then you should keep it.
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
<lakshmi.narasimharao@wipro.com> wrote on 08/30/2005 10:56:20 AM:
>
> 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
>
--=_alternative 0054A3E28525706D_=--
|
|
|
|
|