MySQLdb and threads

[Piet van Oostrum]

>>>>> "Gillou" <nospam at bigfoot.com> (G) writes:

G> Hi,
G> I plan to make SQL queries in different threads of an application using
G> MySQLdb.
G> Can I use the same Connection object in all threads (each thread having its
G> own cursor) for the queries ? Or should I use a different Connection object
G> for each thread ?

The doc says:

threadsafety

Integer constant stating the level of thread safety the interface supports.
As of MySQLdb version 0.9.0, this is set to 1, which means: Threads may
share the module.

The MySQL protocol can not handle multiple threads using the same
connection at once. Some earlier versions of MySQLdb utilized locking to
achieve a threadsafety of 2. While this is not terribly hard to accomplish
using the standard Cursor class (which uses mysql_store_result()), it is
complicated by SSCursor (which uses mysql_use_result(); with the latter you
must ensure all the rows have been read before another query can be
executed. It is further complicated by the addition of transactions, since
transactions start when a cursor execute a query, but end when COMMIT or
ROLLBACK is executed by the Connection object. Two threads cannot share a
connection while a transaction is in progress, in addition to not being
able to share it during query execution. This excessively complicated the
code to the point where it just isn't worth it.

The general upshot of this is: Don't share connections between threads.
It's really not worth your effort or mine, and in the end, will probably
hurt performance, since the MySQL server runs a separate thread for each
connection. You can certainly do things like cache connections in a pool,
and give those connections to one thread at a time. If you let two threads
use a connection simultaneously, the MySQL client library will probably
upchuck and die. You have been warned.

For threaded applications, try using a connection pool. This can be done
using the Pool module.

-- 
Piet van Oostrum <piet at cs.uu.nl>
URL: http://www.cs.uu.nl/~piet [PGP]
Private email: P.van.Oostrum at hccnet.nl