Hibernate+C3P0下連線逾時總結，

Hibernate+C3P0下連線逾時總結，

最近後台總是會遇到當幾個遊戲區並發通知後台寫入統計資料時，發生串連已逾時的問題，拋出如下異常，導致一些統計資料未有寫進去.

Mysql伺服器預設的“wait_timeout”是8小時【也就是預設的值預設是28800秒】，也就是說一個connection空閑超過8個小時，Mysql將自動斷開該connection，通俗的講就是一個串連在8小時內沒有活動，就會自動斷開該串連。
wait timeout的值可以設定，但最多隻能是2147483，不能再大了。也就是約24.85天

這個參數大致的意思是這樣：當一個用戶端串連到MySQL資料庫後，如果用戶端不自己斷開，也不做任何操作，MySQL資料庫會將這個串連保留"wait_timeout"這麼長時間（單位是s，預設是28800s，也就是8小時），超過這個時間之後，MySQL資料庫為了節省資源，就會在資料庫端斷開這個串連；當然，在此過程中，如果用戶端在這個串連上有任意的操作，MySQL資料庫都會重新開始計算這個時間。

這麼看來，發生上面Exception的原因就是因為我的伺服器和MySQL資料庫的串連超過了”wait_timeout"時間，MySQL伺服器端將其斷開了，但是我的程式再次使用這個串連時沒有做任何判斷，所以就掛了。

異常日誌如下:

Caused by: org.hibernate.TransactionException: JDBC begin transaction failed:        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:76) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473) ~[spring-orm-3.2.6.RELEASE.jar:3.2.6.RELEASE]        ... 15 moreCaused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failureThe last packet successfully received from the server was 3,599,995 milliseconds ago.  The last packet sent successfully to the server was 1 milliseconds ago.

Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: The last packet successfully received from the server was 22,312,103 milliseconds ago.  The last packet sent successfully to the server was 22,312,105 milliseconds ago. is longer than the server configured value of 'wait_timeout'. You should consider either expiring and/or testing connection validity before use in your application, increasing the server configured values for client timeouts, or using the Connector/J connection property 'autoReconnect=true' to avoid this problem.        at sun.reflect.GeneratedConstructorAccessor137.newInstance(Unknown Source) ~[?:?]        at sun.reflect.DelegatingConstructorAccessorImpl.newInstance(DelegatingConstructorAccessorImpl.java:45) ~[?:1.7.0_65]        at java.lang.reflect.Constructor.newInstance(Constructor.java:526) ~[?:1.7.0_65]        at com.mysql.jdbc.Util.handleNewInstance(Util.java:411) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.SQLError.createCommunicationsException(SQLError.java:1121) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3941) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2551) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5339) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912) ~[c3p0-0.9.2.1.jar:0.9.2.1]        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473) ~[spring-orm-3.2.6.RELEASE.jar:3.2.6.RELEASE]        ... 21 moreCaused by: java.net.SocketException: 斷開的管道        at java.net.SocketOutputStream.socketWrite0(Native Method) ~[?:1.7.0_65]        at java.net.SocketOutputStream.socketWrite(SocketOutputStream.java:113) ~[?:1.7.0_65]        at java.net.SocketOutputStream.write(SocketOutputStream.java:159) ~[?:1.7.0_65]        at java.io.BufferedOutputStream.flushBuffer(BufferedOutputStream.java:82) ~[?:1.7.0_65]        at java.io.BufferedOutputStream.flush(BufferedOutputStream.java:140) ~[?:1.7.0_65]        at com.mysql.jdbc.MysqlIO.send(MysqlIO.java:3922) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.MysqlIO.sendCommand(MysqlIO.java:2551) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.MysqlIO.sqlQueryDirect(MysqlIO.java:2731) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.ConnectionImpl.execSQL(ConnectionImpl.java:2812) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mysql.jdbc.ConnectionImpl.setAutoCommit(ConnectionImpl.java:5339) ~[mysql-connector-java-5.1.26-bin.jar:?]        at com.mchange.v2.c3p0.impl.NewProxyConnection.setAutoCommit(NewProxyConnection.java:912) ~[c3p0-0.9.2.1.jar:0.9.2.1]        at org.hibernate.engine.transaction.internal.jdbc.JdbcTransaction.doBegin(JdbcTransaction.java:72) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.hibernate.engine.transaction.spi.AbstractTransactionImpl.begin(AbstractTransactionImpl.java:160) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.hibernate.internal.SessionImpl.beginTransaction(SessionImpl.java:1392) ~[hibernate-core-4.1.7.Final.jar:4.1.7.Final]        at org.springframework.orm.hibernate4.HibernateTransactionManager.doBegin(HibernateTransactionManager.java:473) ~[spring-orm-3.2.6.RELEASE.jar:3.2.6.RELEASE]        ... 21 more

以下是我做的C3P0配置，問題解決:

<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource"destroy-method="close"><property name="jdbcUrl" value="jdbc:mysql://192.168.1.21:3306/game_admin?autoReconnect=true&useUnicode=true&characterEncoding=utf-8"/><property name="driverClass" value="com.mysql.jdbc.Driver"/><property name="user" value="linyu"/><property name="password" value="com.123"/><!-- 初始化串連池中的串連數，取值應在minPoolSize與maxPoolSize之間，預設為3--> <property name="initialPoolSize" value="2"/><!-- 串連池中保留的最小串連數，預設為：3--> <property name="minPoolSize" value="2"/><!--串連池中保留的最大串連數。預設值: 15 -->   <property name="maxPoolSize" value="15"/><!--當串連池中的串連耗盡的時候c3p0一次同時擷取的串連數。預設值: 3 -->   <property name="acquireIncrement" value="2"/><!--定義在從資料庫擷取新串連失敗後重複嘗試的次數。預設值: 30 ；小於等於0表示無限次-->            <property name="acquireRetryAttempts" value="0"/>           <!--重新嘗試的時間間隔，預設為：1000毫秒-->             <property name="acquireRetryDelay" value="1000" />   <!--最大空閑時間，3600秒內未使用則串連被丟棄。若為0則永不丟棄。預設值: 0 -->   <property name="maxIdleTime" value="3600"/> <!--c3p0全域的PreparedStatements緩衝的大小。如果maxStatements與maxStatementsPerConnection均為0，則緩衝不生效，只要有一個不為0，則語句的緩衝就能生效。如果預設值: 0-->   <property name="maxStatements" value="0"/> <!--maxStatementsPerConnection定義了串連池內單個串連所擁有的最大緩衝statements數。預設值: 0 -->   <property name="maxStatementsPerConnection" value="0"/><!--定義所有串連測試都執行的測試語句。在使用串連測試的情況下這個一顯著提高測試速度。測試的表必須在初始資料來源的時候就存在。Default: null--><property name="preferredTestQuery" value="select 1"/> <!--每1800秒檢查所有串連池中的空閑串連。Default: 0 --><property name="idleConnectionTestPeriod" value="1800"/><!-- 擷取串連時測試有效性，每次都驗證串連是否可用 --><property name="testConnectionOnCheckout" value="false"/></bean></span>

由於我配置的是C3P0的配置，和hibernate下進行C3P0配置不同，這個要注意

c3p0.maxIdleTime=hibernate.c3p0.timeout

配置是否生效請參考：com.mchange.v2.c3p0.ComboPooledDataSource 這個類

參考:

使用Hibernate串連MySQL資料庫，MySQL連線逾時斷開的問題

c3p0 - JDBC3 Connection and Statement Pooling