Consulting...Connection is invalid (still)

Clinton,

 

If you remember, I contacted you in early 2008 regarding some “Connection is invalid” issues we were seeing.  First you recommended making sure in all transactions we were using the correct try/except/finally pattern, which we did.  Then using the latest version which removed object pools, etc.  Unfortunately, while the problem may have improved, we are still seeing it on a couple of our most heavily loaded database instances.

 

We are using iBatis 2.3.4.726, and it’s definitely related to load.  On our more lightly instances we never see it, but on a few that are heavily used we see it much more frequently.  The stack trace is:

 

--- Cause: java.lang.RuntimeException: Error accessing SimplePooledConnection. Connection is invalid.

        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:110)

        at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.update(SqlMapExecutorDelegate.java:457)

        at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.update(SqlMapSessionImpl.java:90)

        at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.update(SqlMapClientImpl.java:66)

                ...our code...

Caused by: java.lang.RuntimeException: Error accessing SimplePooledConnection. Connection is invalid.

        at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.getValidConnection(SimpleDataSource.java:913)

        at com.ibatis.common.jdbc.SimpleDataSource$SimplePooledConnection.invoke(SimpleDataSource.java:958)

        at $Proxy0.prepareStatement(Unknown Source)

        at com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:497)

        at com.ibatis.sqlmap.engine.execution.SqlExecutor.executeUpdate(SqlExecutor.java:75)

        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.sqlExecuteUpdate(MappedStatement.java:216)

        at com.ibatis.sqlmap.engine.mapping.statement.MappedStatement.executeUpdate(MappedStatement.java:94)

        ... 28 more

 

I was wondering if you have any expertise in troubleshooting such a tough problem, and if so, if you are available for some consulting to help resolve.  I think we’ve eliminated all the easy causes, and would like some guidance as to putting in hooks or statements to help narrow down the root cause.  If you are not available, do you know anyone you can recommend who could?

 

Thanks so much for any help you can provide!

 

Best Regards,

Eric

 

 

 

Thanks Eric.

 

If you’re seeing issues similar to those Eric describes, also try building iBATIS from the trunk.  It should help draw out the problem because we’ve removed the object pools for sessions, requests etc.  So where you may have seen a deadlock before, now you should see a big nasty exception from your app server that tells you that you’ve had a connection open too long, or that you forgot to close a connection.  Or perhaps it will hang too, who knows.  :-)

 

I apologize for not getting a release out yet.  It’s simply a matter of finding the time in an insanely busy year already!

 

I’d suggest holding a vote for which iBATIS developer should do the build, but I have a feeling I’d win (i.e. lose).  ;-)

 

Cheers,

Clinton

 

 

All,

 

I wanted to let you know my experience with this issue.  We saw the same issues and deadlocks that others have reported, especially under heavy loads.

 

As Clinton suggested, we looked to verify we were using the proper transaction pattern (start, commit, end) in the right try…catch…finally blocks.  It was there that we noticed within the transaction block a bit of code that took a lot of time to run and did not need to be in the transaction block.  Removing that code to outside the transaction and thus tightening up the transaction block really helped.  Just FYI in case anyone else is seeing this issue.

 

Regards,

Eric