[Zope-DB] Zope database connectivity

[Maciej Wisniowski]

>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
class:

class PersistentConnectionPool(AbstractConnectionPool):
    """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 
procedures yet),
with ability to reconnect after things like DB restart and with REAL 
ability to
open/close connections from ZMI.

I did one important change. Instead of having pool of connections I have 
pool
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' 
function
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
    in Transaction

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
like:

    _connections_lock.acquire()
    try:
        ....
    finally:
        _connections_lock.release()

may it be slow with high load? I mean that every time you need
the connection this code is used.

-- 
Maciej Wisniowski