[mod_python] mod_python and db connections - architectural advice needed

Christoph Pingel wrote:

> Hello,
>
> I'm looking for good advice for the architecture of a web (service) 
> application.
>
> Using mod_python and psycopg, how do I *keep* a database connection 
> while using and disposing of cursors? And how can I integrate the 
> session handler with a (user specific) database connection?
>
> I've got my application running, but I create and destroy db 
> connections far too often. Is it, for example, good practice to pass a 
> cursor into an object that abstracts the db connection details, rather 
> than creating the cursor inside that abstraction and distroying it 
> after every curs.execute?
>
> I'm looking for references, hints, and especially programming examples.
>
> TIA,
> Christoph

These are all very interesting questions, and many that I've asked myself.

When using psycopg in full isolation mode (fastest mode), then each 
cursor is an independent connection, and the driver keeps a pool of open 
connections in order to re-use previously closed ones (which aren't 
really closed) when a .cursor() request is made.

As such, each cursor has a commit() and rollback() method, and each 
cursor has independent transactions; this is only important for those of 
us who don't use autocommit.

I use the method you explain above, passing a reference to the cursor to 
use to all my DB accessing functions; but, because of the pool that 
psycopg keeps around, cursor creating and destroying is really really 
cheap, so if you design your application that way, it's no reall biggie.

BUT, I'd really like to hear other people's impressions on this.  One of 
the things I've bumped into is when creating the first connection, 
sometimes, upon initial import, many connections are made simultaneously 
and then abandoned, but not destroyed.  This really irks me; I've tried 
gating access to the base DB connection object with a lock, but it's no 
good.

The base DB object is a module-global reference to a psycopg connection, 
and I keep around a lock whenever I need to change it (because I use 
apache2 configured with worker). I don't know if I'm stepping on my own 
toes or what, but it works:

[code]
# module global database connection and it's lock.
DB = None
DBLOCK = thread.allocate_lock()

def Cursor():
    "Return a database cursor."
    global DB
    DBLOCK.acquire()
    try:
        # Try to return a cursor immediately.
        try:
            return DB.cursor()
        except:
            # Maybe the connection isn't open yet, or was dropped.
            DB = psycopg.connection(....)

            # Try to return a new cursor, again.
            try:
                return DB.cursor()
            except:
                # Something happened; leave a message.
                from traceback import format_exception
                for line in format_exception(*sys.exc_info()):
                    apache.log_error(line.rstrip(), apache.APLOG_ERR)
                DB = None
    finally:
        DBLOCK.free()
[/code]

So, the idea here is to immediately lock so that only a single thread
has access to DB; then try to return a new cursor as quick as possible;
if not, create a new connection and try to return a new cursor again;
if not, leave a message at the log and return None.

Somehow, this feels a bit "clunky" for me, and I might be doing something
horribly wrong.

Any takers?

-gca

-------------- next part --------------
A non-text attachment was scrubbed...
Name: gustavo.cordova.vcf
Type: text/x-vcard
Size: 196 bytes
Desc: not available
Url : http://mm_cfg_has_not_been_edited_to_set_host_domains/pipermail/mod_python/attachments/20050217/2653f43c/gustavo.cordova.vcf