Funny behaviour of MySQLdb

[Heiko Wundram]

Hi List!

I'm currently writing a little XML-RPC server in Python, which is used
to power a database driven Web-Application. So far, so good... :) Í have
a little problem with the newest MySQLdb, though.

As the server is multithreaded (and the MySQLdb module isn't), I've
followed their advice, creating a connection pool of database
connections on startup, which are then distributed thread-safe (yeah,
I'm sure it's threadsafe! :)) to the handlers when the XML-RPC server
class fires them.

So far, this works.

When in the handler though, a funny thing happens: I try to lock tables
for correct access of multiple handler instances working parallel, but
the execution of "LOCK TABLES" just hangs. I'm sure that is the first
place I'm executing a table lock, and that no other thread working on
the database is running, but still locking hangs.

If I start a mysql commandline session, and try to lock from there,
locking also blocks, so a lock is acquired somewhere/somehow...

Debugging is difficult, as multithreading isn't quite supported by pdb,
is it? ;)

Has anybody had this problem before? If not, maybe it's a bug in MySQLdb
or in MySQL itself...

Code that is executed (slightly simplified):

Server class:

# Acquire a mutex on the database mutex, and after that, enter a
# critical section.
self._dbmutex.acquire()
self._mutex.acquire()

# Retrieve a connection ID from the list and initialize the database
# array.
id = self._availdatabases.pop(0)
dbs = {}

# Retrieve the connections.
for db in self._databases:
    # Enter database into array.
    dbs[db] = self._databases[db][id]

# Release critical section.
self._mutex.release()

Handler class:

# Get database.
self._db = dbs["user_db"]

# Create global binding.
self._dbpsql = pSQL.pSQL(self._db,"User")

# Lock tables.
if not locks_set:
    print "Locking in _CheckLoginID"
    self._dbpsql.sql_query("LOCK TABLES logins READ, users READ, groups\
READ")
    print "Locking succeeded"

"Locking in _CheckLoginID" gets printed, but then the process locks on
the next statement.

pSQL is a module that facilitates database access, but all it actually
does is work on an global cursor (which is created when pSQL is bound),
and sql_query executes the query you pass in on that cursor...

Any help is appreciated! :)

Yours,

	Heiko W.