java.sql.SQLException: Already closed error with IBatis 2.3.4

Question

I am seeing a strange behavior in the code. I am using Spring DI for getting connection. Following is my ibatis-context.xml

<bean id="dataSource" class="org.apache.commons.dbcp.BasicDataSource" destroy-method="close">  
<property name="driverClassName" value="oracle.jdbc.driver.OracleDriver" />                       <property name="url" value="jdbc:oracle:thin:@hostname:port:dbname" />
<property name="username" value="$usrname" />
<property name="password" value="$pwd" />
<property name="initialSize" value="1"/>
<property name="maxActive" value="1"/>
<property name="maxIdle" value="1"/>
<property name="testWhileIdle" value="true"/>
<property name="minEvictableIdleTimeMillis" value="500"/>
<property name="timeBetweenEvictionRunsMillis" value="500"/>
<property name="validationQuery" value="select 1 from dual"/>
</bean>

When i execute first query, it returns me the ResultSet. But when i execute second query with the same connection, it throws me error(java.sql.SQLException: Already closed).
code

    try {
        // First Query
        personList = sqlMap.queryForList("getPersonList", parameterMap);
    } catch (Exception e) {
        e.printStackTrace();            
    }

    try {
        // Second Query
        firstNameList = sqlMap.queryForList("getfirstNameList", parameterMap);
    } catch (Exception e) {
        e.printStackTrace();            
    }

The same code and configuration works fine few days before but now i am getting error.
The stack trace of the issue.

java.sql.SQLException: Already closed.
    at org.apache.commons.dbcp.PoolableConnection.close(PoolableConnection.java:114)
    at org.apache.commons.dbcp.PoolingDataSource$PoolGuardConnectionWrapper.close(PoolingDataSource.java:191)
    at org.springframework.jdbc.datasource.DataSourceUtils.doReleaseConnection(DataSourceUtils.java:278)
    at org.springframework.jdbc.datasource.TransactionAwareDataSourceProxy$TransactionAwareInvocationHandler.invoke(TransactionAwareDataSourceProxy.java:160)
    at $Proxy10.close(Unknown Source)
    at com.ibatis.sqlmap.engine.transaction.external.ExternalTransaction.close(ExternalTransaction.java:82)
    at com.ibatis.sqlmap.engine.transaction.TransactionManager.end(TransactionManager.java:93)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.endTransaction(SqlMapExecutorDelegate.java:734)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.endTransaction(SqlMapSessionImpl.java:176)
    at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.endTransaction(SqlMapClientImpl.java:153)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.autoEndTransaction(SqlMapExecutorDelegate.java:835)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:574)
    at com.ibatis.sqlmap.engine.impl.SqlMapExecutorDelegate.queryForList(SqlMapExecutorDelegate.java:541)
    at com.ibatis.sqlmap.engine.impl.SqlMapSessionImpl.queryForList(SqlMapSessionImpl.java:118)
    at com.ibatis.sqlmap.engine.impl.SqlMapClientImpl.queryForList(SqlMapClientImpl.java:94)
    at      org.junit.internal.runners.TestMethodRunner.executeMethodBody(TestMethodRunner.java:99)
    at org.junit.internal.runners.TestMethodRunner.runUnprotected(TestMethodRunner.java:81)
    at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
    at org.junit.internal.runners.TestMethodRunner.runMethod(TestMethodRunner.java:75)
    at org.junit.internal.runners.TestMethodRunner.run(TestMethodRunner.java:45)
    at org.junit.internal.runners.TestClassMethodsRunner.invokeTestMethod(TestClassMethodsRunner.java:71)
    at org.junit.internal.runners.TestClassMethodsRunner.run(TestClassMethodsRunner.java:35)
    at org.junit.internal.runners.TestClassRunner$1.runUnprotected(TestClassRunner.java:42)
    at org.junit.internal.runners.BeforeAndAfterRunner.runProtected(BeforeAndAfterRunner.java:34)
    at org.junit.internal.runners.TestClassRunner.run(TestClassRunner.java:52)
    at org.eclipse.jdt.internal.junit4.runner.JUnit4TestReference.run(JUnit4TestReference.java:49)
    at org.eclipse.jdt.internal.junit.runner.TestExecution.run(TestExecution.java:38)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:467)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.runTests(RemoteTestRunner.java:683)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.run(RemoteTestRunner.java:390)
    at org.eclipse.jdt.internal.junit.runner.RemoteTestRunner.main(RemoteTestRunner.java:197)

Is it code issue or database issue?
Anyone have solution for this?

Answer 1

TL;DR; If you see this exception it means your database driver closed the connection, usually due to an IOException level failure. Check your logs; IBatis may have logged the exception for you. If not run the same commands without a pool and you'll see the real exception.

The issue here is that the "already closed" exception is thrown by the common-pool implementation during the finally clause and hides the real exception. I have run into this exact same issue while working through an issue with the JTDS driver. I believe the most straightforward fix is to add a try/catch around around the connection.close in the finally block. Since the iBatis 2.3.x stream is basically dead I don't have a good solution as to how to workaround this issue other than try other pool implementations.

Answer 2

I found the root cause of the problem.
There is no problem with the configuration given above.
The problem is with the second query(It is an invalid query with Inappropriate Joins), oracle throws internal error and the connection is closed.
When I execute the query in SQLDeveloper(UI Query Tool), oracle throws below error and the session is closed.

ORA-00600: internal error code, arguments: [kkqcscpopn_Int: 0], [], [], [], [], [], [], [], [], [], [], []
00600. 00000 -  "internal error code, arguments: [%s], [%s], [%s], [%s], [%s], [%s], [%s], [%s]"
*Cause:    This is the generic internal error number for Oracle program
           exceptions.  This indicates that a process has encountered an
           exceptional condition.
*Action:   Report as a bug - the first argument is the internal error number

More info about the error is in the follwoing link. http://www.orafaq.com/wiki/ORA-00600

To conclude, Inappropriate Joins in the query might be one of the reason for this error(java.sql.SQLException: Already closed).
So, It is good to double check the query and proceed further.