Possible failure to cleanup connection when returned to pool

[tapina]

Hi

Wasn't sure whether to post to JIRA but decided best to check on the forums first. Sorry it's a bit woolly but it's an intermittent fault and I'm not that familiar with the JBoss connection wrapper classes. Just for extra fun, it only seems to happen for us under production load.

I've recently been seeing some occasional errors from our Oracle datasource to do with being unable to SET TRANSACTION due to the current connection state being inappropriate (ORA-01453 locally and ORA-02070 on database links). These errors originate when not explicitly setting transaction (e.g. executing a query, or closing a connection when JBoss tries to reset the transaction). These are on no-tx-connections (i.e. unmanaged).

My suspicion is that the current transaction is not "finished" as far as Oracle is concerned when the JBoss wrappers try to change it, and I know it's picky about this. Because connections don't really get closed, our Java code has to be meticulous about closing statements and result sets (if autocommit is on) and committing and rolling back when its off. However, this is a huge legacy app recently migrated to JBoss and I'm sure there are areas where it fails to clean up properly.

This is where I'd hope the pool comes in, cleaning up connections before reissuing them to another unsuspecting thread. JBoss makes an attempt to clean up by setting autocommit on closure: I think there's a case where the JBoss pools don't clean up properly but I can't be sure.

We use the connections from the pool in both autocommit and non-autocommit modes in different parts of the code. The JBoss wrapped connections only apply this flag at the point where another operation is carried out on the connection (if the flag has changed). This is what I think might be happening:

1. One bit of our code gets a nice fresh connection from the pool using JNDI.

2. The connection is set to autocommit FALSE.

3. We fail to clean up properly after ourselves. Oracle's transaction state is not reset.

4. We close the connection and JBoss sets the autocommit flag TRUE in BaseWrapperManagedConnection.cleanup(), but crucially does not apply this to the underlying connection yet.

5. Another bit of our code gets the reused connection.

6. We set it to autocommit FALSE. Note that this does not get applied to the underlying connection at all because it matches the current state of the underlying connection already and the previous change was never applied.

7. At some later point the transaction level is changed, but there has been no reset of Oracle's transaction status so it complains.

I have tried to mitigate this by making sure to setAutoCommit(false), rollback() and then setAutoCommit(true) if required EVERY TIME I fetch a connection with JNDI. I will see what success this has.

Would be interested in any suggested fixes, workarounds, ideas etc. Is this a bug? I think only Oracle is as picky about the transaction state as this, and the root cause really is most likely to be our failure to clean up properly ourselves - but shouldn't the pool make sure this doesn't trip up unrelated areas of the app?

Regards
Gareth

[adrian.brock]

I assume by "transaction level" you mean the transaction isolation level.

I know in the past Oracle had problems with changing the transaction isolation level.
It only allowed it if you hadn't executed any other queries.

That's probably changed since I last looked at it, which was a while ago?

Having said that, I'd guess your problem (from what I understand of the description)
is that WrappedConnection.setTransactionIsolation() is not checking whether
the connection is in the correct auto commit state.

 public void setTransactionIsolation(int isolationLevel) throws SQLException
 {
 lock();
 try
 {
 checkStatus();
 mc.setJdbcTransactionIsolation(isolationLevel);
 }
 finally
 {
 unlock();
 }
 }

If that is true then changing checkStatus() to be checkTransaction()
would solve the problem. i.e. it would make sure the physical connection
is in the same autocommit state as the logical connection before trying to
change the isolation level.

[tapina]

Actually I'm not sure it would.

The physical and logical connections are both in the same autocommit state (FALSE) at the time of the set transaction isolation call. Inbetween, the logical connection had autocommit TRUE but this was not applied to the physical connection at the time because no other methods were called on the connection whilst it was logically in this state. If the connection has not been committed or rolled back explicitly, then a newly returned connection could be part of an old transaction, I think.

Is there a reason why set autocommit is not passed down to the physical connection immediately but instead checked on every other call and passed down then?

The more I think about this the more I think such a fundamental mistake probably wasn't made and my interpretation is wrong.. but I'd love it if someone could put my mind to rest!

[tapina]

Pretty sure there's an error in BaseWrapperManagedConnection.cleanup:

 if (jdbcTransactionIsolation != transactionIsolation)
 {
 try
 {
 con.setTransactionIsolation(jdbcTransactionIsolation);
 jdbcTransactionIsolation = transactionIsolation;
 }
 catch (SQLException e)
 {
 mcf.log.warn("Error resetting transaction isolation ", e);
 }
 }

Surely the first statement in the try-block should be

con.setTransactionIsolation(transactionIsolation);

?

[jesper.pedersen]

tapina, its https://jira.jboss.org/jira/browse/JBAS-4871.

Please, update.

[tapina]

Fair enough (although that's not an option for me on this large multi-team project...)

Doesn't change my original question though which was relating to autocommit status changes.