c3p0的连接池优化导致的异常

在项目“物业报修系统”里，框架是SpringMVC/Spring/MyBatis，而MyBatis在底层调用的是c3p0，我在试图优化c3p0时犯了一些错误，访问数据库时有时会超时，有时不会，超时会抛出以下异常：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
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 file [C:\Users\DELL\Documents\propertyManagement\target\classes\mapper\AdminDao.xml]
### The error may involve com.ilovecl.dao.AdminDao.queryById
### 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.

     at org.mybatis.spring.MyBatisExceptionTranslator.translateExceptionIfPossible(MyBatisExceptionTranslator.java:76)
     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:399)
     at com.sun.proxy.$Proxy13.selectOne(Unknown Source)
     at org.mybatis.spring.SqlSessionTemplate.selectOne(SqlSessionTemplate.java:165)
     at org.apache.ibatis.binding.MapperMethod.execute(MapperMethod.java:69)
     at org.apache.ibatis.binding.MapperProxy.invoke(MapperProxy.java:53)
     at com.sun.proxy.$Proxy14.queryById(Unknown Source)
     at com.ilovecl.dao.AdminDaoTest.queryById(AdminDaoTest.java:30)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:483)
     at org.junit.runners.model.FrameworkMethod$1.runReflectiveCall(FrameworkMethod.java:47)
     at org.junit.internal.runners.model.ReflectiveCallable.run(ReflectiveCallable.java:12)
     at org.junit.runners.model.FrameworkMethod.invokeExplosively(FrameworkMethod.java:44)
     at org.junit.internal.runners.statements.InvokeMethod.evaluate(InvokeMethod.java:17)
     at org.springframework.test.context.junit4.statements.RunBeforeTestMethodCallbacks.evaluate(RunBeforeTestMethodCallbacks.java:73)
     at org.springframework.test.context.junit4.statements.RunAfterTestMethodCallbacks.evaluate(RunAfterTestMethodCallbacks.java:82)
     at org.springframework.test.context.junit4.statements.SpringRepeat.evaluate(SpringRepeat.java:73)
     at org.junit.runners.ParentRunner.runLeaf(ParentRunner.java:271)
     at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:224)
     at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.runChild(SpringJUnit4ClassRunner.java:83)
     at org.junit.runners.ParentRunner$3.run(ParentRunner.java:238)
     at org.junit.runners.ParentRunner$1.schedule(ParentRunner.java:63)
     at org.junit.runners.ParentRunner.runChildren(ParentRunner.java:236)
     at org.junit.runners.ParentRunner.access$000(ParentRunner.java:53)
     at org.junit.runners.ParentRunner$2.evaluate(ParentRunner.java:229)
     at org.springframework.test.context.junit4.statements.RunBeforeTestClassCallbacks.evaluate(RunBeforeTestClassCallbacks.java:61)
     at org.springframework.test.context.junit4.statements.RunAfterTestClassCallbacks.evaluate(RunAfterTestClassCallbacks.java:68)
     at org.junit.runners.ParentRunner.run(ParentRunner.java:309)
     at org.springframework.test.context.junit4.SpringJUnit4ClassRunner.run(SpringJUnit4ClassRunner.java:163)
     at org.junit.runner.JUnitCore.run(JUnitCore.java:160)
     at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:119)
     at com.intellij.junit4.JUnit4IdeaTestRunner.startRunnerWithArgs(JUnit4IdeaTestRunner.java:42)
     at com.intellij.rt.execution.junit.JUnitStarter.prepareStreamsAndStart(JUnitStarter.java:234)
     at com.intellij.rt.execution.junit.JUnitStarter.main(JUnitStarter.java:74)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:483)
     at com.intellij.rt.execution.application.AppMain.main(AppMain.java:144)
Caused by: 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 file [C:\Users\DELL\Documents\propertyManagement\target\classes\mapper\AdminDao.xml]
### The error may involve com.ilovecl.dao.AdminDao.queryById
### 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.
     at org.apache.ibatis.exceptions.ExceptionFactory.wrapException(ExceptionFactory.java:30)
     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:122)
     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:113)
     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectOne(DefaultSqlSession.java:73)
     at sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
     at sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:62)
     at sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:43)
     at java.lang.reflect.Method.invoke(Method.java:483)
     at org.mybatis.spring.SqlSessionTemplate$SqlSessionInterceptor.invoke(SqlSessionTemplate.java:386)
     ... 39 more
Caused by: 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.
     at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:80)
     at org.mybatis.spring.transaction.SpringManagedTransaction.openConnection(SpringManagedTransaction.java:82)
     at org.mybatis.spring.transaction.SpringManagedTransaction.getConnection(SpringManagedTransaction.java:68)
     at org.apache.ibatis.executor.BaseExecutor.getConnection(BaseExecutor.java:315)
     at org.apache.ibatis.executor.SimpleExecutor.prepareStatement(SimpleExecutor.java:75)
     at org.apache.ibatis.executor.SimpleExecutor.doQuery(SimpleExecutor.java:61)
     at org.apache.ibatis.executor.BaseExecutor.queryFromDatabase(BaseExecutor.java:303)
     at org.apache.ibatis.executor.BaseExecutor.query(BaseExecutor.java:154)
     at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:102)
     at org.apache.ibatis.executor.CachingExecutor.query(CachingExecutor.java:82)
     at org.apache.ibatis.session.defaults.DefaultSqlSession.selectList(DefaultSqlSession.java:120)
     ... 46 more
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.springframework.jdbc.datasource.DataSourceUtils.doGetConnection(DataSourceUtils.java:111)
     at org.springframework.jdbc.datasource.DataSourceUtils.getConnection(DataSourceUtils.java:77)
     ... 56 more
Caused by: com.mchange.v2.resourcepool.TimeoutException: A client timed out while waiting to acquire a resource from com.mchange.v2.resourcepool.BasicResourcePool@290222c1 -- 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)
     ... 59 more

我先是检查代码逻辑，没发现哪个地方会出错，再看Spring配置里关于c3p0的部分：

1
2
3
4
5
6
7
8
9
<!--c3p0连接池的私有属性-->
<property name="maxPoolSize" value="4000"/>
<property name="minPoolSize" value="1"/>
<property name="autoCommitOnClose" value="false"/>
<!--获取连接超时时间-->
<property name="checkoutTimeout" value="1024"/>
<!--获取失败的重试次数-->
<property name="acquireRetryAttempts" value="30"/>

咋一眼看过去，找不到错误，我这时想起我的数据库放在阿里云上，我想：“会不会是运行数据库的机器配置不好，没法及时响应我请求？”。于是我加了一条initialPoolSize优化选项，在建立与数据库的连接时，试图建100个（我想，建立这么多连接，怎么地也会成功一个吧），配置如下：

1
2
3
4
5
6
7
8
9
10
11
<!--试图进行的优化-->
<property name="initialPoolSize" value="100"/>

<!--c3p0连接池的私有属性-->
<property name="maxPoolSize" value="4000"/>
<property name="minPoolSize" value="1"/>
<property name="autoCommitOnClose" value="false"/>
<!--获取连接超时时间-->
<property name="checkoutTimeout" value="1024"/>
<!--获取失败的重试次数-->
<property name="acquireRetryAttempts" value="30"/>

优化之后，虽然效果好了一些，但偶尔依旧会有超时的情况，于是我再仔细研究代码和配置，直到我发现了上面那些配置里有一条：

1
<property name="checkoutTimeout" value="1024"/>

将获取连接的超时时间设置为1024毫秒，在1秒多一点的时间里如果没获取到连接就返回失败并抛出异常。

此时，我突然反应过来：远程机器那么渣，在10秒内能和远在千里之外的它建立连接我就该嗨皮了，居然还要求1秒就建立成功，失败是正常的，能成功都是运气。

于是我将它注释掉，也注释掉之前加上去的initialPoolSize优化，如下：

1
2
3
4
5
6
7
8
9
<!--<property name="initialPoolSize" value="100"/>-->
<!--c3p0连接池的私有属性-->
<property name="maxPoolSize" value="4000"/>
<property name="minPoolSize" value="1"/>
<property name="autoCommitOnClose" value="false"/>
<!--获取连接超时时间-->
<!--<property name="checkoutTimeout" value="1024"/>-->
<!--获取失败的重试次数-->
<property name="acquireRetryAttempts" value="30"/>

如此，再也不会超时了。