[Zope] Z SQL Method, ZMySQLDA, connection pooling, threads, life, the universe, and everything

[Dennis Allison]

I'm about to add connection pooling for MySQL.  This necessarily interacts
with threading and persistance and other arcane arts.  Advice and/or
pointers to hidden gotchas would be much appreciated.

Mu understanding:  Zope runs multiple threads (up to 7 without recompiling
-- it's a ZODB connection limit) using the underlying Python threading
model.  The scheduling unit for threads is a single HTTP transaction. (IS
THIS RIGHT?) Threads are scheduled out of the medusa asyncore by polling
select.

Python local data is, of course, automatically thread-safe.  Global data
is not and is shared across threads.  Persistence is mostly orthogonal to 
the thread issue.

When created, each ZSQL method searches for and binds to one particular
RDBMS adaptor. For my pooled connection approach, there'd be only one
ZMYSQLDA instance to which all ZSQL Methods would bind.  The ZSQL Method
would find the connector by acquisiton (as it does currently) and allow
binding to one or another of the predefined virtual connections.  Most
of the changes look simple.

The ZMySQLDA mechanism will need some significant changes, some of which
need to be thread safe.  ZMySQLDA and MySQLdb are thread-safe at level
one, that is, threads may share the modules but **not the connections**.

On startup, there are no actual database connections only virtual
connections. Connections are created (up to a max number) as needed and
retired (that is, closed) when they are no longer needed. If the virtual
connections are tagged with the Zope thread (how does one identify which
thread) virtual connections become thread safe (since a different actual
connection is allocated for each executing thread)--a feature which should
give a performance boost when certain connections are hot spots.
 
Allocation and deallocation of actual connections will need to be under
the control of a lock on some shared data structure.  A simple mangement
strategy can be used provided database transactions complete before the
thread is rescheduled.

Because this is being patched into a Zope Product that is a heavy reuser
of Zope, I've been pondering the best way to fit this feature
transparently (or nearly transparently) into the existing code.
Given the system structure, it seems to me that the shared data strutures 
and methods can be maintained as class variables (that is, shared accross 
instances) while each instance has its own (lightweight) state.  Is there
any better system structure which will meld neatly into the Zope
structure.

	-dra