Zope SQL connectors allows to query SQL databases from Zope server. Following article is focussed on ZMySQLDA.
Table of Contents
Actual network connections to SQL server must be of limited number to avoid wasting resources on the server. Originaly (as of Zope 2.7) the connections were stored as volatile attributes on the ZMySQLDA instance. But Zope 2.8 volatile numbers can vanish at virtualy any time - even during a transaction - so connection might become unavailable even once the exchange with the SQL server has started. A constraint on the possible solutions is that Zope connection-related code relies on those volatile attributes.
Chosen solution is to rely on 2 levels of object pooling.
DA pooling¶ is implemented to allow a thread to recover its volatile attribute if it vanished during a transaction. This pooling is done in ZMySQLDA/DA.py. It keeps at most one entry per DA instance (ie. one for cmf activity connection, another for catalog connection, etc).
But just this pooling mechanism is not enough because all threads accessing the same connection would share the same network connection - unless transactions get serialized, which implies potentially time-costly locking mechanism.
So there is a second level of pooling.
MySQLdb pooling¶ is implemented to allow distinct threads using the same pooled DA object to use distinct MySQLdb instances. This pooling is done in ZMySQLDA/db.py. It keeps at most one entry per thread id.