You are viewing a plain text version of this content. The canonical link for it is here.
Posted to user@commons.apache.org by Tim Dudgeon <td...@informaticsmatters.com> on 2010/06/11 16:35:51 UTC
[dbcp] ORA-01453 on connections with previous errors
I'm getting an strange problem with ORA-01453 errors on connections
retrieved from the pool that I'm having difficulty resolving.
There is some info on the web about this, but I can't find any
solutions. It is described in places as a DBCP bug, but I can't be sure.
I _think_ the problem is like this (can't be 100% sure of this):
1. A SQLException happens during a previous operation.
2. This connection gets returned to the pool in a bad state
3. A new operation is started within a transaction (I think this must
use the non-default isolation level) and this "bad" connection is
obtained from the pool.
4. The isolation level tries to be set and you get an error like this:
java.sql.SQLException: ORA-01453: SET TRANSACTION must be first
statement of transaction
at
oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
at
oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
at
oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
at
oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
at
oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
at
oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
at
oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
at
oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:4053)
at
org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
at
org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setTransactionIsolation(PoolingDataSource.java:332)
at
org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
at
org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:174)
at
org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:210)
Does this make any sense, and is there a solution?
Thanks
Tim
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
Phil Steitz wrote:
>>> Tim Dudgeon wrote:
>>> 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.
I don't think that will help. The connection is OK - it just blows up
when you try to change the transaction isolaion level, and running a
query won't test that.
Tim
>
> Phil
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
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
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Phil Steitz <ph...@gmail.com>.
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?
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).
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.
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
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
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.
Tim
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Phil Steitz <ph...@gmail.com>.
Tim Dudgeon wrote:
> Phil Steitz wrote:
>> Tim Dudgeon wrote:
>>> I'm getting an strange problem with ORA-01453 errors on connections
>>> retrieved from the pool that I'm having difficulty resolving.
>>> There is some info on the web about this, but I can't find any
>>> solutions. It is described in places as a DBCP bug, but I can't be sure.
>>>
>>> I _think_ the problem is like this (can't be 100% sure of this):
>>>
>>> 1. A SQLException happens during a previous operation.
>>> 2. This connection gets returned to the pool in a bad state
>>> 3. A new operation is started within a transaction (I think this must
>>> use the non-default isolation level) and this "bad" connection is
>>> obtained from the pool.
>>> 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
>
>>
>> Also, what versions of DBCP (stack trace looks like 1.2.2?) and POOL
>> are you using? See https://issues.apache.org/jira/browse/DBCP-308
>> for a related bug that was fixed in DBCP 1.3/1.4; but it looks to me
>> like you are facing a different problem. One more question: What
>> Oracle driver?
>
> I'm using 1.3, but the same problems is seen with 1.2.2.
> Pool version is 1.5.4 (with DBCP 1.3) or 1.5.2 (with 1.2.2).
> Oracle driver version is 11.1.0.7.0.
>
> Tim
>
>>
>> Phil
>>> java.sql.SQLException: ORA-01453: SET TRANSACTION must be first
>>> statement of transaction
>>> at
>>> oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
>>>
>>> at
>>> oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
>>> at
>>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
>>>
>>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
>>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
>>> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
>>> at
>>> oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
>>> at
>>> oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
>>> at
>>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
>>>
>>>
>>> at
>>> oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
>>>
>>>
>>> at
>>> oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
>>> at
>>> oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
>>>
>>>
>>> at
>>> oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:4053)
>>>
>>>
>>> at
>>> org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
>>>
>>>
>>> at
>>> org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setTransactionIsolation(PoolingDataSource.java:332)
>>>
>>>
>>> at
>>> org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
>>>
>>>
>>> at
>>> org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:174)
>>>
>>>
>>> at
>>> org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:210)
>>>
>>>
>>>
>>>
>>>
>>> Does this make any sense, and is there a solution?
>>>
>>> Thanks
>>> 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
>
---------------------------------------------------------------------
To unsubscribe, e-mail: user-unsubscribe@commons.apache.org
For additional commands, e-mail: user-help@commons.apache.org
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
Phil Steitz wrote:
> Tim Dudgeon wrote:
>> I'm getting an strange problem with ORA-01453 errors on connections
>> retrieved from the pool that I'm having difficulty resolving.
>> There is some info on the web about this, but I can't find any
>> solutions. It is described in places as a DBCP bug, but I can't be sure.
>>
>> I _think_ the problem is like this (can't be 100% sure of this):
>>
>> 1. A SQLException happens during a previous operation.
>> 2. This connection gets returned to the pool in a bad state
>> 3. A new operation is started within a transaction (I think this must
>> use the non-default isolation level) and this "bad" connection is
>> obtained from the pool.
>> 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.
>
> Also, what versions of DBCP (stack trace looks like 1.2.2?) and POOL
> are you using? See https://issues.apache.org/jira/browse/DBCP-308
> for a related bug that was fixed in DBCP 1.3/1.4; but it looks to me
> like you are facing a different problem. One more question: What
> Oracle driver?
I'm using 1.3, but the same problems is seen with 1.2.2.
Pool version is 1.5.4 (with DBCP 1.3) or 1.5.2 (with 1.2.2).
Oracle driver version is 11.1.0.7.0.
Tim
>
> Phil
>> java.sql.SQLException: ORA-01453: SET TRANSACTION must be first
>> statement of transaction
>> at
>> oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
>> at
>> oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
>> at
>> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
>> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
>> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
>> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
>> at
>> oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
>> at
>> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
>>
>> at
>> oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
>>
>> at
>> oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
>> at
>> oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
>>
>> at
>> oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:4053)
>>
>> at
>> org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
>>
>> at
>> org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setTransactionIsolation(PoolingDataSource.java:332)
>>
>> at
>> org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
>>
>> at
>> org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:174)
>>
>> at
>> org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:210)
>>
>>
>>
>>
>> Does this make any sense, and is there a solution?
>>
>> Thanks
>> 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
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Phil Steitz <ph...@gmail.com>.
Tim Dudgeon wrote:
> I'm getting an strange problem with ORA-01453 errors on connections
> retrieved from the pool that I'm having difficulty resolving.
> There is some info on the web about this, but I can't find any
> solutions. It is described in places as a DBCP bug, but I can't be sure.
>
> I _think_ the problem is like this (can't be 100% sure of this):
>
> 1. A SQLException happens during a previous operation.
> 2. This connection gets returned to the pool in a bad state
> 3. A new operation is started within a transaction (I think this must
> use the non-default isolation level) and this "bad" connection is
> obtained from the pool.
> 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?
Also, what versions of DBCP (stack trace looks like 1.2.2?) and POOL
are you using? See https://issues.apache.org/jira/browse/DBCP-308
for a related bug that was fixed in DBCP 1.3/1.4; but it looks to me
like you are facing a different problem. One more question: What
Oracle driver?
Phil
>
> java.sql.SQLException: ORA-01453: SET TRANSACTION must be first
> statement of transaction
> at
> oracle.jdbc.driver.SQLStateMapping.newSQLException(SQLStateMapping.java:70)
> at
> oracle.jdbc.driver.DatabaseError.newSQLException(DatabaseError.java:131)
> at
> oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:204)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:455)
> at oracle.jdbc.driver.T4CTTIoer.processError(T4CTTIoer.java:413)
> at oracle.jdbc.driver.T4C8Oall.receive(T4C8Oall.java:1034)
> at oracle.jdbc.driver.T4CStatement.doOall8(T4CStatement.java:183)
> at
> oracle.jdbc.driver.T4CStatement.executeForRows(T4CStatement.java:942)
> at
> oracle.jdbc.driver.OracleStatement.doExecuteWithTimeout(OracleStatement.java:1222)
>
> at
> oracle.jdbc.driver.OracleStatement.executeInternal(OracleStatement.java:1770)
>
> at
> oracle.jdbc.driver.OracleStatement.execute(OracleStatement.java:1739)
> at
> oracle.jdbc.driver.OracleStatementWrapper.execute(OracleStatementWrapper.java:299)
>
> at
> oracle.jdbc.driver.PhysicalConnection.setTransactionIsolation(PhysicalConnection.java:4053)
>
> at
> org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
>
> at
> org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.setTransactionIsolation(PoolingDataSource.java:332)
>
> at
> org.apache.commons.dbcp.DelegatingConnection.setTransactionIsolation(DelegatingConnection.java:340)
>
> at
> org.springframework.jdbc.datasource.DataSourceUtils.prepareConnectionForTransaction(DataSourceUtils.java:174)
>
> at
> org.springframework.jdbc.datasource.DataSourceTransactionManager.doBegin(DataSourceTransactionManager.java:210)
>
>
>
>
> Does this make any sense, and is there a solution?
>
> Thanks
> 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
Re: [dbcp] ORA-01453 on connections with previous errors
Posted by Tim Dudgeon <td...@informaticsmatters.com>.
I finally managed to create reproducible test case for this.
See attached.
The trick seems to be that the failed operation needs to be a DDL statement.
Any insights into this will be greatly appreciated.
Tim