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