問題：
我的目标是通路遠端資料庫，操作遠端資料庫。現在我使用了C3P0資料源，是可以通路本地資料庫，可以早增删改查等操作。但我将資料庫換為伺服器上的資料庫的時候，報APPARENT DEADLOCK問題，有朋友知道怎麼解決這個問題嗎？
嘗試
我自己首先搜尋答案C3P0錯誤APPARENT DEADLOCK!!!解決 ，添加maxStatements和maxStatementsPerConnection到我的配置檔案中再次嘗試，還是不行。
然後又搜尋了c3p0 APPARENT DEADLOCK!!! 錯誤參考 ，裡面也是說把maxStatements設定為0
其他的解決方法也不大行。
我出現的異常
十二月 07, 2016 9:30:43 下午 com.mchange.v2.log.MLog
資訊: MLog clients using java 1.4+ standard logging.
十二月 07, 2016 9:30:43 下午 com.mchange.v2.c3p0.C3P0Registry banner
資訊: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 07, 2016 9:30:43 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
資訊: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempt
s -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure
-> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.
c3p0.impl.DefaultConnectionTester, dataSourceName -> 1bqvvjb9k1occ65kc53q2e|f7df584, debugUnreturnedConnectionStackT
races -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnore
UnresolvedTransactions -> false, identityToken -> 1bqvvjb9k1occ65kc53q2e|f7df584, idleConnectionTestPeriod -> 0, ini
tialPoolSize -> 5, jdbcUrl -> jdbc:mysql://123.206.16.183:3306/dbWaterColInfo, maxAdministrativeTaskTime -> 0, maxCo
nnectionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxSt
atementsPerConnection -> 100, minPoolSize -> 5, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, p
referredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin
-> false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies ->
false ]
十二月 07, 2016 9:31:03 下午 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
警告: com.mchange.v2.async.ThreadPoolAsynchronousRunne[email protected] -- APPARENT DEADLOCK!!! Creating eme
rgency threads for unassigned pending tasks!
十二月 07, 2016 9:31:03 下午 com.mchange.v2.async.ThreadPoolAsynchronousRunner$DeadlockDetector run
警告: com.mchange.v2.async.ThreadPoolAsynchronousRunne[email protected] -- APPARENT DEADLOCK!!! Complete Sta
tus:
Managed Threads: 3
Active Threads: 3
Active Tasks:
com.mchange.v2.resourcepool.BasicResourcePool$Acqu[email protected] (com.mchange.v2.async.ThreadPoolAsynchronousRun
ner$PoolThread-#1)
com.mchange.v2.resourcepool.BasicResourcePool$Acqu[email protected] (com.mchange.v2.async.ThreadPoolAsynchronousRunn
er$PoolThread-#2)
com.mchange.v2.resourcepool.BasicResourcePool$Acqu[email protected] (com.mchange.v2.async.ThreadPoolAsynchronousRun
ner$PoolThread-#0)
Pending Tasks:
co[email protected]
co[email protected]
Pool thread stack traces:
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#1,5,main]
java.lang.Thread.sleep(Native Method)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#2,5,main]
java.lang.Thread.sleep(Native Method)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
Thread[com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread-#0,5,main]
java.lang.Thread.sleep(Native Method)
com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1805)
com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
十二月 07, 2016 9:31:12 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$Ac[email protected] -- Acquisition Attempt Failed!!! Clearing pen
ding acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of a
llowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Host is down
STACKTRACE:
java.net.SocketException: Host is down
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:579)
at java.net.Socket.connect(Socket.java:528)
at java.net.Socket.(Socket.java:425)
at java.net.Socket.(Socket.java:241)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:271)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182
)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171
)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0Poole
dConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
** END NESTED EXCEPTION **
Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2847)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182
)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171
)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0Poole
dConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
十二月 07, 2016 9:31:12 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$Ac[email protected] -- Acquisition Attempt Failed!!! Clearing pen
ding acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of a
llowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Host is down
STACKTRACE:
java.net.SocketException: Host is down
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:579)
at java.net.Socket.connect(Socket.java:528)
at java.net.Socket.(Socket.java:425)
at java.net.Socket.(Socket.java:241)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:271)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182
)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171
)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0Poole
dConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
** END NESTED EXCEPTION **
Last packet sent to the server was 0 ms ago.
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2847)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182
)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171
)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0Poole
dConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
十二月 07, 2016 9:31:12 下午 com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask run
警告: com.mchange.v2.resourcepool.BasicResourcePool$Ac[email protected] -- Acquisition Attempt Failed!!! Clearing pend
ing acquires. While trying to acquire a needed new resource, we failed to succeed more than the maximum number of al
lowed acquisition attempts (30). Last acquisition attempt exception:
com.mysql.jdbc.CommunicationsException: Communications link failure due to underlying exception:
** BEGIN NESTED EXCEPTION **
java.net.SocketException
MESSAGE: Host is down
STACKTRACE:
java.net.SocketException: Host is down
at java.net.PlainSocketImpl.socketConnect(Native Method)
at java.net.AbstractPlainSocketImpl.doConnect(AbstractPlainSocketImpl.java:339)
at java.net.AbstractPlainSocketImpl.connectToAddress(AbstractPlainSocketImpl.java:200)
at java.net.AbstractPlainSocketImpl.connect(AbstractPlainSocketImpl.java:182)
at java.net.SocksSocketImpl.connect(SocksSocketImpl.java:392)
at java.net.Socket.connect(Socket.java:579)
at java.net.Socket.connect(Socket.java:528)
at java.net.Socket.(Socket.java:425)
at java.net.Socket.(Socket.java:241)
at com.mysql.jdbc.StandardSocketFactory.connect(StandardSocketFactory.java:256)
at com.mysql.jdbc.MysqlIO.(MysqlIO.java:271)
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2771)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182
)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171
)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0Poole
dConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
** END NESTED EXCEPTION **
Last packet sent to the server was 1 ms ago.
at com.mysql.jdbc.Connection.createNewIO(Connection.java:2847)
at com.mysql.jdbc.Connection.(Connection.java:1555)
at com.mysql.jdbc.NonRegisteringDriver.connect(NonRegisteringDriver.java:285)
at com.mchange.v2.c3p0.DriverManagerDataSource.getConnection(DriverManagerDataSource.java:134)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:182
)
at com.mchange.v2.c3p0.WrapperConnectionPoolDataSource.getPooledConnection(WrapperConnectionPoolDataSource.java:171
)
at com.mchange.v2.c3p0.impl.C3P0PooledConnectionPool$1PooledConnectionResourcePoolManager.acquireResource(C3P0Poole
dConnectionPool.java:137)
at com.mchange.v2.resourcepool.BasicResourcePool.doAcquire(BasicResourcePool.java:1014)
at com.mchange.v2.resourcepool.BasicResourcePool.access$800(BasicResourcePool.java:32)
at com.mchange.v2.resourcepool.BasicResourcePool$AcquireTask.run(BasicResourcePool.java:1810)
at com.mchange.v2.async.ThreadPoolAsynchronousRunner$PoolThread.run(ThreadPoolAsynchronousRunner.java:547)
下面貼一下正常操作本地資料庫時，列印的日志
十二月 07, 2016 9:32:11 下午 com.mchange.v2.log.MLog
資訊: MLog clients using java 1.4+ standard logging.
十二月 07, 2016 9:32:11 下午 com.mchange.v2.c3p0.C3P0Registry banner
資訊: Initializing c3p0-0.9.1.2 [built 21-May-2007 15:04:56; debug? true; trace: 10]
十二月 07, 2016 9:32:11 下午 com.mchange.v2.c3p0.impl.AbstractPoolBackedDataSource getPoolManager
資訊: Initializing c3p0 pool... com.mchange.v2.c3p0.ComboPooledDataSource [ acquireIncrement -> 3, acquireRetryAttempt
s -> 30, acquireRetryDelay -> 1000, autoCommitOnClose -> false, automaticTestTable -> null, breakAfterAcquireFailure
-> false, checkoutTimeout -> 0, connectionCustomizerClassName -> null, connectionTesterClassName -> com.mchange.v2.
c3p0.impl.DefaultConnectionTester, dataSourceName -> 1bqvvjb9k1oce1vc1vntxdw|f7df584, debugUnreturnedConnectionStack
Traces -> false, description -> null, driverClass -> com.mysql.jdbc.Driver, factoryClassLocation -> null, forceIgnor
eUnresolvedTransactions -> false, identityToken -> 1bqvvjb9k1oce1vc1vntxdw|f7df584, idleConnectionTestPeriod -> 0, i
nitialPoolSize -> 5, jdbcUrl -> jdbc:mysql://localhost:3306/dbWaterColInfo, maxAdministrativeTaskTime -> 0, maxConne
ctionAge -> 0, maxIdleTime -> 30, maxIdleTimeExcessConnections -> 0, maxPoolSize -> 20, maxStatements -> 0, maxState
mentsPerConnection -> 100, minPoolSize -> 5, numHelperThreads -> 3, numThreadsAwaitingCheckoutDefaultUser -> 0, pref
erredTestQuery -> null, properties -> {user=******, password=******}, propertyCycle -> 0, testConnectionOnCheckin ->
false, testConnectionOnCheckout -> false, unreturnedConnectionTimeout -> 0, usesTraditionalReflectiveProxies -> fal
se ]
1
aaa
test1
test1
null
這是我的查詢代碼:
public void tese1() throws SQLException{
QueryRunner qr = new QueryRunner(C3P0Util.getDataSource());
Object[] arr = qr.query("select * from userInfos_t", new ArrayHandler());
for (Object o : arr) {
System.out.println(o);
}
}
我的配置檔案：
com.mysql.jdbc.Driver
jdbc:mysql://localhost:3306/dbWaterColInfo
root
root
5
30
20
5
100
有朋友知道解決方法的話請告知一下，謝謝了。