c3p0.properties配置引起的com.mchange.v2.resourcepool.TimeoutException

最近由于客户业务量的增长，公司决定使用分布式处理的方式来解决客户反应的处理速度慢的问题。本博主要讲解项目中遇到的问题，如有不对敬请批评指正。废话不多说直接上干货。
首先，我把业务逻辑重新梳理了一遍，抽出了无关的业务和可能产生并发的逻辑，以便考虑分布式（简单分布式）。于是我把能单独运行的一个工程放到一台服务器上在复制一套放到另外一台服务器上。两台服务器同时运行处理报文。结果。。。

org.hibernate.exception.GenericJDBCException: Could not open connection
    at org.hibernate.exception.internal.StandardSQLExceptionConverter.convert(StandardSQLExceptionConverter.java:54)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:125)
    at org.hibernate.engine.jdbc.spi.SqlExceptionHelper.convert(SqlExceptionHelper.java:110)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:304)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.getConnection(LogicalConnectionImpl.java:169)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.extractPhysicalConnection(ConnectionProxyHandler.java:82)
    at org.hibernate.engine.jdbc.internal.proxy.ConnectionProxyHandler.continueInvocation(ConnectionProxyHandler.java:138)
    at org.hibernate.engine.jdbc.internal.proxy.AbstractProxyHandler.invoke(AbstractProxyHandler.java:81)
    at com.sun.proxy.$Proxy104.prepareStatement(Unknown Source)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$5.doPrepare(StatementPreparerImpl.java:147)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl$StatementPreparationTemplate.prepareStatement(StatementPreparerImpl.java:166)
    at org.hibernate.engine.jdbc.internal.StatementPreparerImpl.prepareQueryStatement(StatementPreparerImpl.java:145)
    at org.hibernate.loader.Loader.prepareQueryStatement(Loader.java:1854)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1831)
    at org.hibernate.loader.Loader.executeQueryStatement(Loader.java:1811)
    at org.hibernate.loader.Loader.doQuery(Loader.java:899)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:341)
    at org.hibernate.loader.Loader.doQueryAndInitializeNonLazyCollections(Loader.java:311)
    at org.hibernate.loader.Loader.loadEntity(Loader.java:2111)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:82)
    at org.hibernate.loader.entity.AbstractEntityLoader.load(AbstractEntityLoader.java:72)
    at org.hibernate.persister.entity.AbstractEntityPersister.load(AbstractEntityPersister.java:3887)
    at org.hibernate.event.internal.DefaultLoadEventListener.loadFromDatasource(DefaultLoadEventListener.java:458)
    at org.hibernate.event.internal.DefaultLoadEventListener.doLoad(DefaultLoadEventListener.java:427)
    at org.hibernate.event.internal.DefaultLoadEventListener.load(DefaultLoadEventListener.java:204)
    at org.hibernate.event.internal.DefaultLoadEventListener.proxyOrLoad(DefaultLoadEventListener.java:260)
    at org.hibernate.event.internal.DefaultLoadEventListener.onLoad(DefaultLoadEventListener.java:148)
    at org.hibernate.internal.SessionImpl.fireLoad(SessionImpl.java:1078)
    at org.hibernate.internal.SessionImpl.access$2000(SessionImpl.java:175)
    at org.hibernate.internal.SessionImpl$IdentifierLoadAccessImpl.load(SessionImpl.java:2424)
    at org.hibernate.internal.SessionImpl.get(SessionImpl.java:974)
    at com.ygjt.bcb.base.AbstractBaseDAO.get(AbstractBaseDAO.java:40)
    at com.ygjt.bcb.base.AbstractCommonDAO.findById(AbstractCommonDAO.java:65)
    at com.ygjt.bcb.cmn.dao.impl.CustomsDAOImpl.findById(CustomsDAOImpl.java:19)
    at com.ygjt.bcb.cmn.dao.impl.CustomsDAOImpl.findById(CustomsDAOImpl.java:1)
    at sun.reflect.GeneratedMethodAccessor513.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.aop.aspectj.MethodInvocationProceedingJoinPoint.proceed(MethodInvocationProceedingJoinPoint.java:80)
    at com.google.code.ssm.aop.SingleReadCacheAdvice.cache(SingleReadCacheAdvice.java:78)
    at com.google.code.ssm.aop.ReadThroughSingleCacheAdvice.cacheGetSingle(ReadThroughSingleCacheAdvice.java:52)
    at sun.reflect.GeneratedMethodAccessor509.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethodWithGivenArgs(AbstractAspectJAdvice.java:621)
    at org.springframework.aop.aspectj.AbstractAspectJAdvice.invokeAdviceMethod(AbstractAspectJAdvice.java:610)
    at org.springframework.aop.aspectj.AspectJAroundAdvice.invoke(AspectJAroundAdvice.java:65)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:161)
    at org.springframework.aop.interceptor.ExposeInvocationInterceptor.invoke(ExposeInvocationInterceptor.java:90)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at com.sun.proxy.$Proxy46.findById(Unknown Source)
    at com.ygjt.bcb.base.BaseBO.findById(BaseBO.java:27)
    at sun.reflect.GeneratedMethodAccessor508.invoke(Unknown Source)
    at sun.reflect.DelegatingMethodAccessorImpl.invoke(Unknown Source)
    at java.lang.reflect.Method.invoke(Unknown Source)
    at org.springframework.aop.support.AopUtils.invokeJoinpointUsingReflection(AopUtils.java:317)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.invokeJoinpoint(ReflectiveMethodInvocation.java:183)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:150)
    at org.springframework.transaction.interceptor.TransactionInterceptor.invoke(TransactionInterceptor.java:110)
    at org.springframework.aop.framework.ReflectiveMethodInvocation.proceed(ReflectiveMethodInvocation.java:172)
    at org.springframework.aop.framework.JdkDynamicAopProxy.invoke(JdkDynamicAopProxy.java:204)
    at com.sun.proxy.$Proxy47.findById(Unknown Source)
    at com.ygjt.bcb.datainterface.proc.impl.EAutoCheck.check(EAutoCheck.java:172)
    at com.ygjt.bcb.datainterface.proc.impl.EAutoCheck.eede(EAutoCheck.java:124)
    at com.ygjt.bcb.datainterface.proc.impl.EdeMessageProcessorImpl.process(EdeMessageProcessorImpl.java:116)
    at com.ygjt.bcb.datainterface.proc.impl.DataProcessImpl.process(DataProcessImpl.java:19)
    at com.ygjt.bcb.datainterface.DataProcThread.run(DataProcThread.java:27)
    at java.util.concurrent.ThreadPoolExecutor.runWorker(Unknown Source)
    at java.util.concurrent.ThreadPoolExecutor$Worker.run(Unknown Source)
    at java.lang.Thread.run(Unknown Source)
Caused by: java.sql.SQLException: An attempt by a client to checkout a Connection has timed out.
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:106)
    at com.mchange.v2.sql.SqlUtils.toSQLException(SqlUtils.java:65)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:527)
    at com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource.getConnection(AbstractPoolBackedDataSource.java:128)
    at org.hibernate.service.jdbc.connections.internal.DatasourceConnectionProviderImpl.getConnection(DatasourceConnectionProviderImpl.java:141)
    at org.hibernate.internal.AbstractSessionImpl$NonContextualJdbcConnectionAccess.obtainConnection(AbstractSessionImpl.java:292)
    at org.hibernate.engine.jdbc.internal.LogicalConnectionImpl.obtainConnection(LogicalConnectionImpl.java:297)
    ... 70 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@5f927daf -- timeout at awaitAvailable()
    at com.mchange.v2.resourcepool.BasicResourcePool.awaitAvailable(BasicResourcePool.java:1317)
    at com.mchange.v2.resourcepool.BasicResourcePool.prelimCheckoutResource(BasicResourcePool.java:557)
    at com.mchange.v2.resourcepool.BasicResourcePool.checkoutResource(BasicResourcePool.java:477)
    at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool.checkoutPooledConnection(C3P0PooledConnectionPool.java:525)
    ... 74 more
```自己在网上google了一下，大部分的同行都说是c3p0的一个bug，大部分认为只要将c3p0.properties中的一个配置项（如下）设置一下：




<div class="se-preview-section-delimiter"></div>

The size of c3p0’s global PreparedStatement cache

simple.c3p0.maxStatements=500

<div class="se-preview-section-delimiter"></div>

```ruby
#The size of c3p0's global PreparedStatement cache
simple.c3p0.maxStatements=0

后来我看了一下c3p0.properties的讲解，贴出以前有问题的配置：

# C3P0 setup
# how many connections at a time c3p0 will try to acquire when the pool is exhausted
simple.c3p0.acquireIncrement=3
# Number of Connections a pool will try to acquire upon startup
simple.c3p0.initialPoolSize=2
#If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
simple.c3p0.idleConnectionTestPeriod=10
#Minimum number of Connections a pool will maintain at any given time
simple.c3p0.minPoolSize=2
#Maximum number of Connections a pool will maintain at any given time
simple.c3p0.maxPoolSize=200
#c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. 
#Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously. 
simple.c3p0.numHelperThreads=10
#Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire
simple.c3p0.maxIdleTime=10
#The size of c3p0's global PreparedStatement cache
simple.c3p0.maxStatements=500
#the max size for every connection
simple.c3p0.maxStatementsPerConnection=20
# how many times c3p0 will try to acquire a new Connection from the database before giving up
simple.c3p0.acquireRetryAttempts=30
# a pooled DataSource will declare itself broken and be permanently closed if a Connection cannot be obtained from the database after making 
# to acquire one.
simple.c3p0.breakAfterAcquireFailure=false
#time c3p0 will wait between acquire attempts
simple.c3p0.acquireRetryDelay=1000
#The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted
simple.c3p0.checkoutTimeout=10000
#The JDBC spec is unforgivably silent on what should happen to unresolved, pending transactions on Connection close
simple.c3p0.autoCommitOnClose=false

这是后来自己研究以后的配置：

# C3P0 setup
# how many connections at a time c3p0 will try to acquire when the pool is exhausted
simple.c3p0.acquireIncrement=5
# Number of Connections a pool will try to acquire upon startup
simple.c3p0.initialPoolSize=2
#If this is a number greater than 0, c3p0 will test all idle, pooled but unchecked-out connections, every this number of seconds
simple.c3p0.idleConnectionTestPeriod=10
#Minimum number of Connections a pool will maintain at any given time
simple.c3p0.minPoolSize=2
#Maximum number of Connections a pool will maintain at any given time
simple.c3p0.maxPoolSize=200
#c3p0 is very asynchronous. Slow JDBC operations are generally performed by helper threads that don't hold contended locks. 
#Spreading these operations over multiple threads can significantly improve performance by allowing multiple operations to be performed simultaneously. 
simple.c3p0.numHelperThreads=20
#Seconds a Connection can remain pooled but unused before being discarded. Zero means idle connections never expire
simple.c3p0.maxIdleTime=10
#The size of c3p0's global PreparedStatement cache
#HC change
simple.c3p0.maxStatements=10
#the max size for every connection
simple.c3p0.maxStatementsPerConnection=20
# how many times c3p0 will try to acquire a new Connection from the database before giving up
simple.c3p0.acquireRetryAttempts=30
# a pooled DataSource will declare itself broken and be permanently closed if a Connection cannot be obtained from the database after making 
# to acquire one.
simple.c3p0.breakAfterAcquireFailure=false
#time c3p0 will wait between acquire attempts
simple.c3p0.acquireRetryDelay=1000
#The number of milliseconds a client calling getConnection() will wait for a Connection to be checked-in or acquired when the pool is exhausted
simple.c3p0.checkoutTimeout=10000

分析：
最大连接数200，maxStatements用以控制数据源内加载的PreparedStatements数量。但由于预缓存的statements属于单个connection而不是整个连接池。所以设置这个参数需要考虑到多方面的因素。配置太大反而会产生文章开始时的异常。