Glassfish连接池- java.sql.SQLException:连接关闭

Question

我使用的web项目JSF2与Oracle Glassfish Server开源版4.0和Oracle Database 11g (Version 11.2.0-1.0)。服务器和数据库运行在同一台windows机器上。连接池管理到数据库的连接。

有谁知道为什么我有时得到以下异常:

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)
    ...

或者这个

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)
    ...

数据库类:

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

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);
}

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 ...
}

连接池设置:

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

数据库的IDLE-Timeout设置为"UNLIMITED"。

Notcie: 当调用"con.prepareCall(sql);"(一定不是第一次)，或者当我尝试提交连接时，或者以后当我尝试打开自动提交时。

有人知道原因吗?或者调试应用程序找出原因的最佳方法是什么?

谢谢。

---

编辑:也许这很重要:我可以在服务器日志中找到许多关于连接泄漏的警告:

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

您启用了连接泄漏回收，并且连接泄漏超时时间为10秒。这意味着，如果您保持一个逻辑连接超过10秒，它将被连接池管理器强制撤销并关闭(物理连接将返回到连接池)。当连接关闭时，后续使用该逻辑连接的尝试将导致SQLException。

找出哪个操作耗时超过10秒，并尝试减少或配置更长的连接泄漏超时所需的时间(对于连接泄漏检测来说，10秒有点短)。同样的BTW也适用于语句泄漏检测(6秒也很短)。