Webware for Python

> I found no appropriate documenation in the net
> considering pooling of database connections.
> So, how can I pool connections ?

Take a look at MiscUtils.DBPool.    I use it for postgres and it works great.  
You can subclass it as needed to turn off transaction mode, or whatever.

I have a file called SiteDatabase.py that creates an instance of a DBPool in 
the module scope (global?), and any servlet or other class can import it, 
thus sharing my pool.

> Is it a good idea to store a connection
> in the session() (timeout 60secs) so
> that during a session database acess
> is accelerated ???

Probably not.  If you had lots of users, they would each get a connection and 
you'd run out of resources on your database.

> Last questions: when are servelt instances
> created ?

I'll leave that for the gurus :)

: > I found no appropriate documenation in the net
: > considering pooling of database connections.
: > So, how can I pool connections ?
:
: Take a look at MiscUtils.DBPool.    I use it for postgres and it works
great.
: You can subclass it as needed to turn off transaction mode, or whatever.
:
: I have a file called SiteDatabase.py that creates an instance of a DBPool
in
: the module scope (global?), and any servlet or other class can import it,
: thus sharing my pool.

I found DBPool meanwhile, but did not know how to share one instance of
this class. That is the reason why I asked about creation of servlet
instances...


:
:
: > Last questions: when are servelt instances
: > created ?
:
: I'll leave that for the gurus :)

Greetings, Uwe.

On Tue, 2003-04-22 at 08:52, Uwe Schmitt wrote:
> I found DBPool meanwhile, but did not know how to share one instance of
> this class. That is the reason why I asked about creation of servlet
> instances...

You can just put it in a global variable, like:

# SiteDatabase.py
import DBPool, MySQLdb

pool = DBPool.DBPool(MySQLdb, 10, user=...)

# SomeServlet.py
from SiteDatabase import pool

class SomeServlet(SitePage):

    def awake(self):
        conn = pool.getConnection()

  Ian

There's a somewhat subtle issue using DCOracle2 with DBPool.  DCOracle2
defines it's threadsafety as 3 because technically connections can be shared
across threads. However, invoking <connection>.commit() will commit any
pending transactions on the connection.  Since DBPool could allow multiple
threads to share the same connection (with threadsafety 3), then one thread
could commit another thread's changes.

For example with 10 pooled connections:
- thread 0 obtains connection 0 from the DBPool and goes to work on a long
series of transactions
- meanwhile ... connections 1-9 are allocated to other threads
- thread x comes along and obtains connection 0 from the DBPool as it is the
next in the rotation
- thread x can do a simple insert and commit() before thread 0 has finished
all it's transactions
- thread 0 excounters an error and tries to rollback, but the rollback only
covers what happened AFTER thread x's commit.
- so the rollback succeeds but the changes are already committed. doh!

I have a homegrown db pool for use with DCOracle2 that uses the Queue
method. A better solution though, might be a subclass of DBPool which allows
for both threadsafe and unthreadsafe retrieval of db connections. That way,
when threads need to modify the db they can truly reserve a connection,
while threads that need to fetch data can obtain a threadsafe connection.
Another solution is to simply have one's application maintain 2 DBPools, one
for querying and one for DML.

Any thoughts on this little snag?

Regards,
Ben

PS - Should this thread be moved into discuss?

-----Original Message-----
From: webware-devel-admin@...
[mailto:webware-devel-admin@... Behalf Of Ian
Bicking
Sent: Tuesday, April 22, 2003 10:16 AM
To: Uwe Schmitt
Cc: Jeff Johnson; Webware devel
Subject: Re: [mailinglist] Re: [Webware-devel] pooling of
databaseconnections


On Tue, 2003-04-22 at 08:52, Uwe Schmitt wrote:
> I found DBPool meanwhile, but did not know how to share one instance of
> this class. That is the reason why I asked about creation of servlet
> instances...

You can just put it in a global variable, like:

# SiteDatabase.py
import DBPool, MySQLdb

pool = DBPool.DBPool(MySQLdb, 10, user=...)

# SomeServlet.py
from SiteDatabase import pool

class SomeServlet(SitePage):

    def awake(self):
        conn = pool.getConnection()

  Ian




-------------------------------------------------------
This sf.net email is sponsored by:ThinkGeek
Welcome to geek heaven.
http://thinkgeek.com/sf
_______________________________________________
Webware-devel mailing list
Webware-devel@...
https://lists.sourceforge.net/lists/listinfo/webware-devel