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 more
Caused by: com.mysql.jdbc.exceptions.jdbc4.CommunicationsException: Communications link failure
The 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 more
Caused 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