Funny behaviour of MySQLdb

[Alex Martelli]

On Saturday 20 July 2002 02:48 pm, Skip Montanaro wrote:
>     >> I just tried the single-threaded way, and that works... Hmm...
>     >> Nothing apparently different, except that I took out the base class
>     >> SocketServer.ThreadingMixIn from the definition of the server
>     >> class...
>
>     Alex> Rule of thumb: always ensure a single thread interacts with any
>     Alex> given external entity, be it a file, a database, the user, or
> even Alex> a global shared variable subject to updates.
>
> Hmmm...  I'm not discounting your rule of thumb, but I use MySQL from a
> multi-threaded application with no problems (at least no problems of which
> I am aware).  I maintain a pool of (currently five) connection objects in a
> Queue.  Each thread which needs to chat with the database calls .get(),
> creates a cursor, chats, then returns the connection to the Queue.  Guess
> I'll have to go back and reread the MySQL docs and refine my stress tests.

If MySQLdb.threadsafety >= 2, and there are no errors in your implementation
(and you being quite a good programmer, I'd bet there probably aren't), 
you're supposed to be safe.  If each "chat" is complicated enough and can't
easily be encapsulated as a callable, you may even have some advantage
wrt the architecture I'd consider even safer -- five dedicated threads, one 
per connection, all waiting for work requests on a single queue and returning 
results on a separate queue per requestor thread: you're saving a few 
resources  by having 5 fewer threads and many fewer Queues around.

But the moment problems should start appearing, good luck.  You know as
well as I do that there's no bug so deucedly hard to reproduce, and thus fix,
as one due to multiprogramming (threading or otherwise) with all related
issues.  That's a good part of why I avoid multiprogramming when I can
avoid it and wear braces AND a belt when I can't.


Alex