Mysql異常刨析：Could not commit JDBC transaction;No operations allowed after statement closed

問題描述

異常堆疊如下：

org.springframework.transaction.TransactionSystemException:   nested exception is com.mysql.jdbc.excedbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:316)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.processCommit(AbstractPlatformTransactionManage1)
	at org.springframework.transaction.support.AbstractPlatformTransactionManager.commit(AbstractPlatformTransactionManager.java:
	at org.springframework.transaction.interceptor.TransactionAspectSupport.commitTransactionAfterReturning(TransactionAspectSupp518)
	at org.springframework.transaction.interceptor.TransactionAspectSupport.invokeWithinTransaction(TransactionAspectSupport.java
	at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:96)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:179)
	at org.springframework.aop.framework.CglibAopProxy$DynamicAdvisedInterceptor.intercept(CglibAopProxy.java:673)
	at com.weshow.interlive.business.service.AccountInfoService$$EnhancerBySpringCGLIB$$d0052aac.getAccountInfo(<generated>)
	at com.weshow.interlive.business.handler.AccoutMessageHandler.dealAccountBalance(AccoutMessageHandler.java:65)
	at com.weshow.interlive.business.handler.AccoutMessageHandler.processCommand(AccoutMessageHandler.java:41)
	at com.weshow.interlive.business.handler.AccoutMessageHandler$$FastClassBySpringCGLIB$$1db0c1c6.invoke(<generated>)
	at org.springframework.cglib.proxy.MethodProxy.invoke(MethodProxy.java:204)
	at org.springframework.aop.framework.CglibAopProxy$CglibMethodInvocation.invokeJoinpoint(CglibAopProxy.java:738)
	at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:157)
	at org.springframework.aop.interceptor.AsyncExecutionInterceptor$1.call(AsyncExecutionInterceptor.java:115)
	at java.util.concurrent.FutureTask.run(FutureTask.java:266)
	at java.lang.Thread.run(Thread.java:748)
Caused by: com.mysql.jdbc.exceptions.jdbc4.MySQLNonTransientConnectionException: No operations allowed after connection closed.
	at sun.reflect.GeneratedConstructorAccessor69.newInstance(Unknown Source)
	at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45)
	at java.lang.reflect.Constructor.newInstance(Constructor.java:423)
	at com.mysql.jdbc.Util.handleNewInstance(Util.java:425)
	at com.mysql.jdbc.Util.getInstance(Util.java:408)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:918)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:897)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:886)
	at com.mysql.jdbc.SQLError.createSQLException(SQLError.java:860)
	at com.mysql.jdbc.ConnectionImpl.throwConnectionClosedException(ConnectionImpl.java:1187)
	at com.mysql.jdbc.ConnectionImpl.checkClosed(ConnectionImpl.java:1182)
	at com.mysql.jdbc.ConnectionImpl.commit(ConnectionImpl.java:1517)
	at sun.reflect.GeneratedMethodAccessor58.invoke(Unknown Source)
	at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
	at java.lang.reflect.Method.invoke(Method.java:498)
	at org.apache.tomcat.jdbc.pool.ProxyConnection.invoke(ProxyConnection.java:126)
	at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	at org.apache.tomcat.jdbc.pool.interceptor.AbstractCreateStatementInterceptor.invoke(AbstractCreateStatementInterceptor.java:
	at org.apache.tomcat.jdbc.pool.JdbcInterceptor.invoke(JdbcInterceptor.java:108)
	at org.apache.tomcat.jdbc.pool.DisposableConnectionFacade.invoke(DisposableConnectionFacade.java:81)
	at com.sun.proxy.$Proxy88.commit(Unknown Source)
	at org.springframework.jdbc.datasource.DataSourceTransactionManager.doCommit(DataSourceTransactionManager.java:313)
	... 17 common frames omitted

我這邊採用的配置如下：

    max-idle: 5
    max-wait: 10000
    min-idle: 2

可能原因

原因1：

MySQLNonTransientConnectionException: No operations allowed after statement closed

    之所以會出現這個異常，是因為Mysql在5以後針對超長時間DB連線做了一個處理，那就是如果一個DB連線在無任何操作情況下過了8個小時後，Mysql會自動把這個連線關閉。所以使用連線池的時候雖然連線物件還在但是連結資料庫的時候會一直報這個異常。解決方法很簡單在Mysql的官方網站上就可以找到。

解決方案：

第一種是在DB連線字串後面加一個引數。
這樣的話，如果當前連結因為超時斷掉了，那麼驅動程式會自動重新連線資料庫。
 jdbc:mysql://localhost:3306/makhtutat?autoReconnect=true
不過Mysql並不建議使用這個方法。因為第一個DB操作失敗的後，第二DB成功前如果出現了重新連線的效果。這個失敗操作將不會處於一個事務以內，第二DB操作如果成功的話，這個事務將被提交。
conn.createStatement().execute(
  "UPDATE checking_account SET balance = balance - 1000.00 WHERE customer='Smith'");
conn.createStatement().execute(
  "UPDATE savings_account SET balance = balance + 1000.00 WHERE customer='Smith'");
conn.commit();

當然如果出現了重新連線，一些使用者變數和臨時表的資訊也會丟失。

另一種方法是Mysql推薦的，需要程式設計師手動處理異常。

<span style="font-family:'Microsoft YaHei';font-size:12px;">public void doBusinessOp() throws SQLException {
    Connection conn = null;
    Statement stmt = null;
    ResultSet rs = null;
    int retryCount = 5;
    boolean transactionCompleted = false;
    do {
        try {
            conn = getConnection(); // assume getting this from a
                                    // javax.sql.DataSource, or the
                                    // java.sql.DriverManager
            conn.setAutoCommit(false);
            retryCount = 0;
            stmt = conn.createStatement();
            String query = "SELECT foo FROM bar ORDER BY baz";
            rs = stmt.executeQuery(query);
            while (rs.next()) {
            }
            all.close()
            transactionCompleted = true;
        } catch (SQLException sqlEx) {
            String sqlState = sqlEx.getSQLState();
           // 這個08S01就是這個異常的sql狀態。單獨處理手動重新連結就可以了。
            if ("08S01".equals(sqlState) || "40001".equals(sqlState)) 
                {                
                    retryCount--;            
                 } else {                
                     retryCount = 0;            
                     }        
         } finally {            
                 all close:        
             }    
      } while (!transactionCompleted && (retryCount > 0));}
}</span>

原因2：開啟事務與提交事務的時長超出了，連線等待的時長。

於是看了下mysql的設定：SHOW VARIABLES LIKE '%timeout%';重點觀察
interactive_timeout

wait_timeout

兩個欄位如下，可以發現試採用MYSQL預設設定8小時，可以排除此類原因。

mysql> SHOW VARIABLES LIKE '%timeout%';
+-----------------------------+----------+
| Variable_name               | Value    |
+-----------------------------+----------+
| connect_timeout             | 10       |
| delayed_insert_timeout      | 300      |
| innodb_flush_log_at_timeout | 1        |
| innodb_lock_wait_timeout    | 50       |
| innodb_rollback_on_timeout  | OFF      |
| interactive_timeout         | 28800    |
| lock_wait_timeout           | 31536000 |
| net_read_timeout            | 30       |
| net_write_timeout           | 60       |
| rpl_stop_slave_timeout      | 31536000 |
| slave_net_timeout           | 3600     |
| wait_timeout                | 28800    |
+-----------------------------+----------+
12 rows in set (0.01 sec)

若以上2個欄位值很小，可以通過以下方式進行配置。

而mysql的預設設定應給是28800秒，即8小時。

解決辦法有兩種:
一、修改資料庫設定
      1. 修改配置檔案，my.cnf（windows下my.ini），在[mysqld]下加兩行interactive_timeout=設定值     wait_timeout=設定的值  然後重啟mysql服務
      2. 直接在sql命令列裡設定  set global interactive_timeout=設定值     set global wait_timeout=設定值  這樣的設定好像是隻要一重啟mysql服務 就會還原的
二、修改應用配置
       將事務的操作時間控制在配置範圍內。連線池閒置連線關掉設定為0 maxIdle=0。（保證每次獲得的連線是最新的，已便有足夠的時間）.

       優化自己的單個事務的處理時長，以便在合理範圍內！

結果分析

通過針對原因進行分析對比發現，我這邊基本確定試原因1，但是autoReconnect=true既不是一個好的選擇，也不是每次都生效的選擇，一定還有其他原因和解決方法？進一步分析發現，如果一個DB連線在無任何操作情況下過了8個小時後(Mysql 伺服器預設的“wait_timeout”是8小時)，Mysql會自動把這個連線關閉。這就是問題的所在，在連線池中的connections如果空閒超過8小時，mysql將其斷開，而連線池自己並不知道該connection已經失效，如果這時有 Client請求connection，連線池將該失效的Connection提供給Client，將會造成上面的異常。

所以配置datasource時需要配置相應的連線池引數，定是去檢查連線的有效性，定時清理無效的連線。

所以解決方法試新增保活檢測：

    initial-size: 5
    validation-query: SELECT 1
    test-on-borrow: false
    test-while-idle: true
    time-between-eviction-runs-millis: 18800