Glassfish Connection Pool - java.sql.SQLException: Connection closed

Question

I'm using for a web project JSF2 with Oracle Glassfish Server Open Source Edition 4.0 and Oracle Database 11g (Version 11.2.0-1.0). The server and database are running on the same windows machine. A connection pool managed the connections to the database.

Does anybody know why I sometimes get the following exception:

java.sql.SQLException: Connection closed
    at com.sun.gjc.spi.base.ConnectionHolder.checkValidity(ConnectionHolder.java:766)
    at com.sun.gjc.spi.base.ConnectionHolder.commit(ConnectionHolder.java:243)
    at de.mydomain.myproject.Hl7MessageHandler.run(Hl7MessageHandler.java:123)
    ...

Or sometimes this one:

java.sql.SQLRecoverableException: Closed connection
    at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:5675)
    at oracle.jdbc.driver.PhysicalConnection.commit(PhysicalConnection.java:5735)
    at com.sun.gjc.spi.base.ConnectionHolder.commit(ConnectionHolder.java:244)
    at de.mydomain.myproject.Hl7MessageHandler.run(Hl7MessageHandler.java:123)
    ...

The Database Class:

public static Connection getConnection() throws NamingException, SQLException {     
    Context initContext = new InitialContext(); 
    DataSource datasSource = (DataSource)initContext.lookup("jdbc/Oracle"); 
    Connection connnection = datasSource.getConnection();
    return connnection; 
}

Request handling in the servlet:

public IResponseSendable<String> run(String hl7MsgString, boolean publishErrorToDB) {

    // ... do something

    try {
        con = Database.getConnection();
    } catch (NamingException | SQLException conExc) {

        return generateAck(true, conExc.getMessage(), hl7MsgString);        
    }

    try {
        con.setAutoCommit(false); 
        process();
        con.commit();
    } catch (HL7Exception | SQLException pe) {
        logger.error(...);

        // Exceptionhandling...

        try { 
            con.rollback();
        } catch (SQLException rollbackExc) { 
            logger.error(...);
        }           
        return generateAck(true, pe.getMessage(),hl7MsgString, _log);
    }
    finally {

        try { 
            con.setAutoCommit(true);
            con.close();
        } catch (SQLException e) { 
            logger.error(...);
        }
    }
    return generateAck(false, "", hl7MsgString);
}

The process-Methode:

private void process() throws HL7Exception, SQLException {
    // Do something...

    String sql = "BEGIN save_patient_data(?,?,?,?,?,?,?); END;";

    CallableStatement stmt = (CallableStatement) con.prepareCall(sql);
    stmt.setString(1, ...);
    // ...

    stmt.registerOutParameter(6, java.sql.Types.VARCHAR);
    stmt.registerOutParameter(7, java.sql.Types.NUMERIC);
    stmt.execute(); 
    // ... 
    stmt.close();

    // More databse stored procedure can be called ...
}

Connection Pool Settingts:

Initial and Minimum Pool Size: 10 Connections
Maximum Pool Size: 60 Connections
Pool Resize Quantity: 2 Connections
Idle Timeout: 600 Seconds
Max Wait Time: 0 Milliseconds

Validate At Most Once: 0 Seconds
Connection Leak Timeout: 10 Seconds
Connection Leak Reclaim: enabled
Statement Leak Timeout: 6 Seconds
Statement Leak Reclaim: enabled
Creation Retry Attempts: 0
Retry Interval: 10 Seconds

Connection Validation: Required
Validation Method: meta-data

The database IDLE-Timeout setting is "UNLIMITED".

Notcie: The exception occurred either when to call "con.prepareCall(sql);" (must not be at the first time) or when I try to commit the connection or later when to try to turn autocommit on.

Does any body know the reason or what is the best way to debug the application to find it out?

Thank you.

---

Edit: Maybe it's important: I can find in the server log many warnings about connection leaks:

2014-07-28T14:49:17.961+0200|Warnung: A potential connection leak detected for connection pool OraclePool. The stack trace of the thread is provided below : 
com.sun.enterprise.resource.pool.ConnectionPool.setResourceStateToBusy(ConnectionPool.java:324)
com.sun.enterprise.resource.pool.ConnectionPool.getResourceFromPool(ConnectionPool.java:758)
com.sun.enterprise.resource.pool.ConnectionPool.getUnenlistedResource(ConnectionPool.java:632)
com.sun.enterprise.resource.pool.AssocWithThreadResourcePool.getUnenlistedResource(AssocWithThreadResourcePool.java:200)
com.sun.enterprise.resource.pool.ConnectionPool.internalGetResource(ConnectionPool.java:526)
com.sun.enterprise.resource.pool.ConnectionPool.getResource(ConnectionPool.java:381)
com.sun.enterprise.resource.pool.PoolManagerImpl.getResourceFromPool(PoolManagerImpl.java:245)
com.sun.enterprise.resource.pool.PoolManagerImpl.getResource(PoolManagerImpl.java:170)
com.sun.enterprise.connectors.ConnectionManagerImpl.getResource(ConnectionManagerImpl.java:360)
com.sun.enterprise.connectors.ConnectionManagerImpl.internalGetConnection(ConnectionManagerImpl.java:307)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:196)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:171)
com.sun.enterprise.connectors.ConnectionManagerImpl.allocateConnection(ConnectionManagerImpl.java:166)
com.sun.gjc.spi.base.AbstractDataSource.getConnection(AbstractDataSource.java:114)
de.mydomain.myproject.utilities.Database.getConnection(Database.java:17)
...

Answer 1

You have connection leak reclaim enabled and the connection leak timeout is 10 seconds. This means that if you hold onto a logical connection for longer than 10 seconds, it is forcibly revoked and closed by the connection pool manager (and the physical connection is returned to the connection pool). Subsequent attempts to use the logical connection will result in a SQLException as the connection is closed.

Find out which operation takes longer than 10 seconds and try to reduce the time it takes or configure a longer connection leak timeout (10 seconds is IMHO a bit short for connection leak detection). The same BTW applies to your statement leak detection (6 seconds is also pretty short).