c3p0 签出超时：resourcepool.TimeoutException: A client timed out while waiting to acquire a resource

全部异常：

2019-08-19 14:33:49.860 |-ERROR [http-nio-8081-exec-317] org.apache.catalina.core.ContainerBase.[Tomcat].[localhost].[/queue-admin].[dispatcherServlet] [181] -| Servlet.service() for servlet [dispatcherServlet] in context with path [/queue-admin] threw exception [Request processing failed; nested exception is org.mybatis.spring.MyBatisSystemException: nested exception is org.apache.ibatis.exceptions.PersistenceException:
### Error querying database.  Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
### The error may exist in class path resource [mybatis/EndUserMapper.xml]
### The error may involve com.***.impl.dao.impl.mybatis.EndUserMapper.searchPageOfEndUser
### The error occurred while executing a query
### Cause: org.springframework.jdbc.CannotGetJdbcConnectionException: Could not get JDBC Connection; nested exception is java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.] with root cause
com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@51671b08 -- timeout at awaitAvailable()
        at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1467)
        at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:644)
        at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:554)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutAndMarkConnectionInUse(C3P0PooledConnectionPool.java:758)
        at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:685)
        at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:140)
        at org.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
        at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
......

百度翻译：
###查询数据库时出错。原因：org.springframework.jdbc.cannotgetjdbconnectionexception:无法获取JDBC连接；嵌套异常为java.sql.sql exception:客户端尝试签出连接已超时。
###类路径资源[mybatis/endusermapper.xml]中可能存在错误。
###错误可能涉及com.biomatch.qadm.service.impl.dao.impl.mybatis.endusermapper.searchpageofenduser。
###执行查询时出错
###原因：org.springframework.jdbc.cannotgetjdbconnectionexception:无法获取JDBC连接；嵌套异常为java.sql.sql exception:客户端尝试签出连接已超时。]根本原因为
com.mchange.v2.resourcepool.timeoutexception:客户端在等待从com.mchange.v2.resourcepool.basicResourcePool@51671b08获取资源时超时--等待可用时超时（）

出现原因：

1.使用 c3p0 数据库连接池，查询时超时:客户端在等待从com.mchange.v2.resourcepool.basicResourcePool@51671b08获取资源时超时--等待可用时超时（）

 

解决办法：

1.查看数据库SQL语句，或嵌套语句。优化SQL语句，该添加索引的添加

2.修改checkoutTimeout （签出超时 的时间设置）的值，时间单位为毫秒。等待时间，如果说是线程死掉了，等再久也没有，直接死锁。

<!--SQLEXCEPTION，如果设置0，则无限等待。Default:0-->
<property name="checkoutTimeout" value="3500"/>

3.数据库链接的URL加上 &autoReconnect=true&rewriteBatchedStatements=TRUE （自动连接 & 重写匹配状态）(不建议使用)

如果连接闲置8小时 (8小时内没有进行数据库操作), mysql就会自动断开连接, 要重启tomcat.

jdbc.url=jdbc:mysql://127.0.0.1:3306/test?useUnicode=true&characterEncoding=utf-8&autoReconnect=true&rewriteBatchedStatements=TRUE

4.适当修改最大链接数

<property name="maxPoolSize" value="30"/>

我的链接配置

<!-- maven c3p0 数据库连接池 -->
<bean id="dataSource" class="com.mchange.v2.c3p0.ComboPooledDataSource" destroy-method="close" primary="true">
	<property name="driverClass"><value>${jdbc.driverClassName}</value></property>
	<property name="jdbcUrl"><value>${jdbc.url}</value></property>
	<property name="user"><value>${jdbc.username}</value></property>
	<property name="password"><value>${jdbc.password}</value></property>
	<property name="minPoolSize"><value>5</value></property>
	<property name="maxPoolSize"><value>50</value></property>
	<property name="maxIdleTime"><value>1800</value></property>
	<!--当连接池中的连接耗尽的时候c3p0一次同时获取的连接数。Default: 3 -->
	<property name="acquireIncrement"><value>2</value></property>
	<property name="maxStatements"><value>1000</value></property>
	<property name="initialPoolSize"><value>5</value></property>
	<property name="idleConnectionTestPeriod"><value>60</value></property>
	<property name="acquireRetryAttempts"><value>30</value></property>
	<property name="breakAfterAcquireFailure"><value>false</value></property>
	<property name="testConnectionOnCheckout"><value>true</value></property>
	<!--连接池用完时客户调用getConnection()后等待获取连接的时间，单位：毫秒。超时后会抛出异常-->
	<!--SQLEXCEPTION，如果设置0，则无限等待。Default:0-->
	<property name="checkoutTimeout" value="3500"/>
</bean>