[jboss-user] [JCA/JBoss] - Possible failure to cleanup connection when returned to pool

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 View the original post : http://www.jboss.org/index.html?module=bb&op=viewtopic&p=4234702#... Reply to the post : http://www.jboss.org/index.html?module=bb&op=posting&mode=reply&a...