Funny behaviour of MySQLdb

[Alex Martelli]

On Sunday 21 July 2002 04:36 am, Skip Montanaro wrote:
>     Alex> If MySQLdb.threadsafety >= 2, and there are no errors in your
>     Alex> implementation, you're supposed to be safe.
>
> I just checked the docs for MySQLdb.threadsafety (version 0.9.2).  It's
> value in that version is 1.  About this the docs says:

By itself I believe threadsafety == 1 does not imply that different threads
can use the same connection over time.  However, the paragraph you
quote does appear to say that MySQLdb, in particular, can do that -- not
quite a threadsafety of 2, but more than just 1 per se would imply.


>     Alex> If each "chat" is complicated enough and can't easily be
>     Alex> encapsulated as a callable, you may even have some advantage wrt
>     Alex> the architecture I'd consider even safer -- five dedicated
>     Alex> threads, one per connection, all waiting for work requests on a
>     Alex> single queue and returning results on a separate queue per
>     Alex> requestor thread: you're saving a few resources by having 5 fewer
>     Alex> threads and many fewer Queues around.
>
> Each time I .get() a connection from the pool, I do tend to execute
> multiple SQL statements.

Yes, but, if that could be reasonably encapsulated in a callable, you could
just pass the (callable, returnqueue) pair as the work request.  I guess it
does happen that the program's structure is just not easily cut up into
functions, methods, callable instances, etc, but I've never met such cases.

> Using a fixed numbers of threads associated with
> each connection is a bit more complicated, especially since I already wind
> up with a thread per request courtesy of SocketServer.ThreadingMixin.  Why
> waste it? <wink>

Without even having seen your code I suspect your highest gain in
scalability would come from a slightly modified ThreadingMixin that
reuses worker-threads from a thread pool rather than churning out
one thread per request.  I've not specifically tried that, but in other
similar situations in the past (in other languages) moving to a fixed
pool with a reasonable number of threads was always an important
scalability win.


>     Alex> But the moment problems should start appearing, good luck.
>
> Well understood.  I was really quite surprised that I had very few problems
> as I added locks to the shared objects when I converted the server from
> single to multiple threads.

It's nice to get a GOOD surprise once in a while, we're so used to BAD
ones:-).


Alex