> -----Original Message----- > From: [EMAIL PROTECTED] > [mailto:[EMAIL PROTECTED] Behalf Of David Rushby > Sent: Thursday, August 07, 2003 11:12 AM > To: [EMAIL PROTECTED] > Subject: [Webware-discuss] MiscUtils.DBPool.returnConnection bug? > > > Why doesn't the returnConnection method (either the threadsafe or > threadunsafe implementation of it) call connection.rollback() before > returning the connection to the pool? > > As it is, this scenario could arise: > > A1: ask the DBPool for a connection > A2: start a transaction implicitly by performing some database ops, and > leave the transaction unresolved > A3: return the connection (an instance of > MiscUtils.DBPool.PooledConnection) to the pool with the close() method, > which doesn't resolve the transaction > > B1: ask the DBPool for a connection (and get the same connection as in > a1) > B2: perform some database ops, unintentionally resuming the transaction > started in A2 > B3: commit the transaction, unintentionally comitting the operations > performed in A2 > > > I use a subclass of DBPool whose returnConnection method rolls back any > unresolved transaction before placing the connection back in the > available pool, so that a connection freshly allocated from the pool is > guaranteed not to have a residual transaction. Is there a reason why > the official DBPool shouldn't behave this way?
One short answer is that not all db's support transactional commit() and rollback() I think though that the issue you describe should be handled in the application code, not a connection framework. I would consider an unresolved transaction to be a bug. As a rule of thumb, I always wrap any transactional calls with a "try ... except ... else" block to ensure that either commit() or rollback() is called before the connection is returned to the pool. I brought up a related issue about a couple months ago in a post to this list. I have a Webware site backed by Oracle, and I'm connecting with DCOracle2. DCOracle2 defines it's threadsafety as 3 because technically connections can be shared across threads. DBPool configures itself using that threadsafety attribute and will pass out connections even if they have already been given to another thread. So using DBPool I run into a similar problem that you have outlined, but the connection doesn't even need to be returned to the pool in an unfinished state for an unintentional commit/rollback to occur. For example with 10 pooled connections: - thread 0 obtains connection 0 from the DBPool and goes to work on a series of long running statements that make up one transaction - 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 does a quick 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 (ie does not throw error) but the changes are already committed. doh! I have a homegrown db pool for use with DCOracle2 to get around this issue. I've come to the conclusion that pooling connections is highly dependent upon the db itself, the db interface, and one's application. Ben ------------------------------------------------------- This SF.Net email sponsored by: Free pre-built ASP.NET sites including Data Reports, E-commerce, Portals, and Forums are available now. Download today and enter to win an XBOX or Visual Studio .NET. http://aspnet.click-url.com/go/psa00100003ave/direct;at.aspnet_072303_01/01 _______________________________________________ Webware-discuss mailing list [EMAIL PROTECTED] https://lists.sourceforge.net/lists/listinfo/webware-discuss
