commons-user mailing list archives

Site index · List index
Message view « Date » · « Thread »
Top « Date » · « Thread »
From Tim Dudgeon <tdudg...@informaticsmatters.com>
Subject Re: [dbcp] ORA-01453 on connections with previous errors
Date Sun, 13 Jun 2010 18:44:00 GMT
Phil Steitz wrote:
> Tim Dudgeon wrote:
>> Phil Steitz wrote:
>>>>>> 4. The isolation level tries to be set and you get an error like
this:
>>>>> Do you have testOnBorrow or testOnReturn set to true?  Do you have a
>>>>>  validationQuery defined?
>>>> Neither are defined.
>>> Try setting testOnBorrow=true and supplying a validationQuery.  This
>>> will cause connections to be validated when they are borrowed from
>>> the pool.
>>>
>>> Phil
>> To clarify what the the problem seems to be:
>>
>> 1. Connection obtain from pool. AutoCommit is set as true.
>> 2. Oracle performs a DDL operation that fails and causes SQLException.
>> Although autoCommit is still set as true, there is now a transaction
>> that has been started.
>> 3. Connection released back to pool. The
>> PoolableConnectionFactory.passivateObject() method does not try to roll
>> back the transaction as autoCommit is true and so assumes there can be
>> no open transaction in place.
>> 4. Same connection is obtained from pool again.
>> 5. Transaction isolation level is changed -> Exception as there is
>> already an ongoing transaction.
>>
>> If instead an explicit rollback() is done at the end of step 2 then all
>> is OK.
> 
> Sounds correct.  What I don't get is why the failed DDL statement
> leaves a transaction open. Have you verified this by skipping the
> return to the pool?
> 

Yes, I just tried this. Same execption happens.

> DBCP does test autocommit before issuing a rollback when it returns
> a connection to the pool.  Eliminating this test would have
> performance impacts.  To workaround the Oracle driver behavior, we
> need to force the rollback.  As an immediate workaround, this can be
> done on the client side (in your finally block before the close).

Yes, in my testcase this is possible, but in real system this happens 
deep within Spring's JDBC support, so is not possible.

> 
> To "fix" this in DBCP, we should probably either extend the fix in
> DBCP-116 to PoolableConnectionFactory so validation can configured
> to include rollback, or add a configuration parameter
> "alwaysRollback" or somesuch to force rollback on each connection
> passivate regardless of the automcommit state of the connection. We
> should continue this discussion on commons-dev.  I have opened
> https://issues.apache.org/jira/browse/DBCP-338 to track this issue.
>  Thanks for reporting the problem.

Yes, solution will be a bit ugly. This seems to me to be realy an Oracle 
bug, but I guess a workaround is inevitable.

Thanks for your help with this.

Tim

> 
> Phil
> 
> 
>>
>> Tim
>>
>>
>> ---------------------------------------------------------------------
>> To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
>> For additional commands, e-mail: user-help@commons.apache.org
>>


---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org


Mime
View raw message