> -----Original Message-----
> From: webware-discuss-admin@...
> [mailto:webware-discuss-admin@... Behalf Of David
> Sent: Thursday, August 07, 2003 11:12 AM
> To: webware-discuss@...
> 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
> 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
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
- 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.