If one is careful, one can find a reliable solution...
Hopefully I think I did this today...
I've found that psycopg pool implementation has nice
"""A pool that assigns persistent connections to different threads.
Note that this connection pool generates by itself the required keys
using the current thread id. This means that untill a thread put away
a connection it will always get the same connection object by successive
.getconn() calls. This also means that a thread can't use more than one
single connection from the pool.
I used this, and finally I have DCOracle2 with connection pool, without
volatile attributes (I didn't changed implementation of stored
with ability to reconnect after things like DB restart and with REAL
open/close connections from ZMI.
I did one important change. Instead of having pool of connections I have
of Resource Managers (or Transaction Managers - I'm not sure which name
is correct). These are descendants of Shared.DC.ZRDB.TM.TM and are usually
defined in db.py ad DA(Shared.DC.ZRDB.TM.TM). I'll call them RM.
Every RM in my pool is already connected to database - it has self.db.
With this implementation the typical request is:
0. pool of RM is created - usually it is during first call to 'getconn'
that pops connection from the pool.
1. ZSQLMethod (or other DA) calls database connection object
which is descendant of Shared.DC.ZRDB.Connection.Connection
2. Descendant of Shared.DC.ZRDB.Connection.Connection
in it's __call__ function gets free RM from the pool and
returns it to ZSQLMethod.
RM when taken from pool is marked as 'used' in the pool
and is assigned a key that is thread id. Every subsequent call
to 'getconn' checks if there already is 'used' RM under specific
key (thread id), and if so, returns this. During request we always
have same thread id so... it just works :)
3. ZSQLMethod executes 'query' on RM. RM registers itself
Steps 1-3 are repeated for each ZSQLMethod used during request.
Thanks to PersistentConnectionPool ZSQLs always get
same RM. As you probably noticed, RM taken from the pool
at the begining was not returned to the pool yet - it is still marked
as 'used' so no other thread is able to get it.
4. Request finishes so all registered RMs are commited (or aborted)
and their _finish method is called. A bit ugly thing happens here:
5. In _finish method RM puts itself :-/ back to the pool.
It is not possible (at last I have no idea how) to release RM in
different place because I don't know when request is finished.
But this implementation seems to work correcly.
One more thing I'm wondering about is getting RMs from
the pool (free or even used) because it is always done with code
may it be slow with high load? I mean that every time you need
the connection this code is used.
Zope-DB mailing list