Automatic transactions and datsource's connection pool ("Closed connection" SQLException)

[Chema <de...@gmail.com>]

Hi:

I'm using iBatis 2.3.0 framework in an application deployed on Sun Java
Application Server 7.1 (release 1)
iBatis gets connection objects from a Datasource's pool defined in AS.  All
work fine.

But we had to upgrade the AS to release 6 and , suddenly, a "Closed
connection" SQLException is thrown when is executed a query with automatic
transaction.
Here is the log trace:

com.ibatis.common.jdbc.exception.NestedSQLException:
--- The error occurred in com/fastline/sql/Utils.xml.
--- The error occurred while executing query.
--- Check the           SELECT sysdate FROM DUAL   .
--- Check the SQL Statement (preparation failed).
--- Cause: java.sql.SQLException: Closed connection

Caused by: java.sql.SQLException: Closed connection
       at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:112)
       at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:146)
       at
oracle.jdbc.driver.DatabaseError.throwSqlException(DatabaseError.java:208)
       at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:840)
       at
oracle.jdbc.driver.PhysicalConnection.prepareStatement(PhysicalConnection.java:759)
       at
com.sun.enterprise.resource.JdbcXAConnection$JdbcConnection.prepareStatement(JdbcXAConnection.java:394)
       at
com.ibatis.sqlmap.engine.execution.SqlExecutor.prepareStatement(SqlExecutor.java:494)
       at
com.ibatis.sqlmap.engine.execution.SqlExecutor.executeQuery(SqlExecutor.java:176)
       at com.tid.igri.utils.sql.SqlExecutoriGRI.executeQuery(Unknown
Source)
       at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.sqlExecuteQuery(GeneralStatement.java:205)
       at
com.ibatis.sqlmap.engine.mapping.statement.GeneralStatement.executeQueryWithCallback(GeneralStatement.java:173)

I've seen on documentation that "each thread that calls startTransaction()
will get a unique Connection object for their transaction.only way to return
a connection to the DataSource (or close the connection) is to call
commitTransacor endTransaction(). Not doing so could cause your pool to run
out of connections and lock up."

But I've also seen used that "if you do not explicitly demarcate
transactions using the startTransaction(), commitTransaction() and
endTransaction() methods, they will all be called automatically for you
whenever you execute a statement outside of a transactional block"

So,

Is there any known problem with "automatic transactions" and Connection
object when are returned to the Datasource?
What is the most usual root cause for a "Closed connection" issue ?


Thanks !