把max_statements设置为0。
c3p0在同时关闭statement和connection的时候,或者关闭他们之间的时间很短的时候,有时候connection并没有被关闭,因为有些preparedstatement还在被cached住。这是c3p0的作者自己说的。
http://forum.hibernate.org/viewtopic.php?t=947246&highlight=apparent+deadlock+c3p0
C3P0增加以下配置信息:
- //set to 'SELECT 1'
- preferredTestQuery = 'SELECT 1'
- //set to something much less than wait_timeout, prevents connections from going stale
- idleConnectionTestPeriod = 18000
- //set to something slightly less than wait_timeout, preventing 'stale' connections from being handed out
- maxIdleTime = 25000
- //if you can take the performance 'hit', set to "true"
- testConnectionOnCheckout = true
A c3p0 pool with the settings you have should recover from a database reset, but that doesn't mean you will never see an Exception. Stale Connections from the old database session will still be broken, and if those Connections have already been checked out, or if they are in the pool and not tested on checkout, the application will see the broken Connection, in the form of an Exception.
You can use c3p0 to minimize the likelihood that your application will see a stale Connection on database shutdown/restart. The most reliable means of preventing this is to set hibernate.c3p0.validate to true (in a hibernate application -- all other c3p0 apps should use the c3p0-native property c3p0.testConnectionOnCheckout). If you set this property to true, c3p0 will test Connections prior to checkout, and your app will never see a stale Connection on database restart unless the Connection had already been checked out when the database went down.
Another less reliable, but potentially less expensive, strategy is to set c3p0.testConnectionsOnCheckin and hibernate.c3p0.idle_test_period (c3p0-native c3p0.idleConnectionTestPeriod) to a low value, in which case all connection tests are asynchronous and you are guanteed that no Connection will be checked out that hasn't been tested in the last idle_test_period seconds. Thus, your app will only see broken Connections from the pool if Connections are checked out during a short window of time.
In either case, I recommend setting "c3p0.preferredTestQuery" or "c3p0.automaticTestTable" in your c3p0 properties file, as c3p0's default Connection test is often slow.
See "Configuring Connection Testing" in c3p0's docs for more information.
在使用c3p0作为连接池时,其中的一些配置参数需要修改。主要是maxIdleTime和idleConnectionTestPeriod。 MySQL默认是8小时(28800秒)后自动关闭已打开的连接,所以c3p0要在8小时内关闭不使用的连接,上面的2参数要小于28800秒。附上在 hibernate中配置c3p0的关键字。
c3p0.acquireIncrement | hibernate.c3p0.acquire_increment |
c3p0.idleConnectionTestPeriod | hibernate.c3p0.idle_test_period |
c3p0.initialPoolSize | not available -- uses minimum size |
c3p0.maxIdleTime | hibernate.c3p0.timeout |
c3p0.maxPoolSize | hibernate.c3p0.max_size |
c3p0.maxStatements | hibernate.c3p0.max_statements |
c3p0.minPoolSize | hibernate.c3p0.min_size |