Warren, thanks for the quick reply.

I think we should proceed as follows:

1) The MiscUtils/DBPool.py should be kept as it is, but it should be a little bit better documented. The advantage of DBPool will be that the code is very easy to understand. So it may serve as a “starting point” for people who want to make it more sophisticated.

2) Your DBConnectionPool should be placed in MiscUtils as “DBPool2.py” or something, it may serve as a sophisticated “ending point” complete with all the bells and whistles.

3) WebKit should support database connections tied to the servlet threads. It would be very handy if the Servlet or Page class automatically provided a DB API compliant “connection” method. This method should however be tied to the respective thread and cache the database connections, so that there is always only one connection (to the same database) per thread at a time.

> I'll try to get the latest changes into the sandbox. I would welcome any help that you or anyone else could give in testing and further development.

Yes, please do so. Please comment as much as possible. I will definitely try it out. Currently I’m writing some stability and performance tests and would like to compare the various methods.

> a connection stored as a servlet instance variable could still
> be called by more than one thread, dictating some thread safety
> within the database interface code.  To mitigate this,
> the connection could be stored with the thread somehow.

Absolutely. Also, you will have full control over the number of database connections – they will be exactly the same as the number of AppServer Threads (in the long run, the connections should be only opened on demand).and not the same as the number of servlets that use the database.

> The problem of having a stored reference to a "bad" connection
> still exists, so some mechanism for dealing with these would still
> need to be implemented.  In DbConnectionPool, I couldn't think of
> any good way of finding out if a connection was "bad", so I chose to
> implement the "expiration" concept as somewhat of a workaround.

My idea is the following: We could make a wrapper class around the DB API connection (this has to be done and is done in the DBPool.py implementation anyway, in order to return the connection back to the pool). Then we could also wrap the “execute” method. Whenever the method call fails with an OperationalError, this would indicate that most probably the connection was lost, the wrapper should try to reopen the connection and its cursors, and rerun execute.

I have done a similar thing with the classical PyGreSQL interface (which is not conform to DB API). This interface provides a flag for the status of the connection (integer: 1 - OK, 0 - BAD). Also, it has no cursors. So in this case it was very easy to implement, and it worked very well.

Unfortunately, the DB API does not seem to offer a method for checking whether really the connection is lost or whether the problem is somewhere else (normally, in these cases a different exception is produced, but sometimes it may be an OperationalError also). Maybe after an OperationalError, we could execute a simple query like “select 1” or “select 1 from dual”. If this fails, we assume that the connection is bad. If this works, we assume that there is another problem that does not have to do with the connection and raise the OperationalError again.

So I think somehow it should be possible to detect and transparently intercept bad connections in DB API with tricks like mentioned above.

-- Christoph


-------------------------------------------------------
SF.Net email is sponsored by: Discover Easy Linux Migration Strategies
from IBM. Find simple to follow Roadmaps, straightforward articles,
informative Webcasts and more! Get everything you need to get up to
speed, fast. http://ads.osdn.com/?ad_id=7477&alloc_id=16492&op=click
_______________________________________________
Webware-devel mailing list
Webware-devel@lists.sourceforge.net
https://lists.sourceforge.net/lists/listinfo/webware-devel

Reply via email to