2017年11月10日

      已經(jīng)一個(gè)多月沒有寫東西了，不過最近確實(shí)很忙。前兩天在線上碰到一個(gè)C3P0的鏈接死鎖的異常，話說這個(gè)上古神物 ,我已經(jīng)是很久不碰了。先貼異常

異常截圖

"apparent deadlocks"：名詞解釋是說c3p0拿到鏈接之后，最終使用之后沒有返回到pool,導(dǎo)致死鏈檢測(cè)失敗。經(jīng)過在stack Overflow檢索,https://stackoverflow.com/questions/3730844/c3p0-apparent-deadlock-when-the-threads-are-all-empty.發(fā)現(xiàn)增加一個(gè)statementCacheNumDeferredCloseThreads該參數(shù)的定義，就可以避免這個(gè)問題。

經(jīng)過查看官方文檔：http://www.mchange.com/projects/c3p0/#statementCacheNumDeferredCloseThreads

解釋：如果把該值設(shè)為超過1，statement的緩存就會(huì)自動(dòng)跟蹤當(dāng)前可用的connections,如果沒有再用，就會(huì)自動(dòng)銷毀掉。如果需要另外的線程來專門銷毀緩存的statement,則還需要設(shè)置maxStatements與maxStatementsPerConnection。

再貼一個(gè)官方的說明：

Configuring Statement Pooling

c3p0 implements transparent PreparedStatement pooling as defined by the JDBC spec. Under some circumstances, statement pooling can dramatically improve application performance. Under other circumstances, the overhead of statement pooling can slightly harm performance. Whether and how much statement pooling will help depends on how much parsing, planning, and optimizing of queries your databases does when the statements are prepared. Databases (and JDBC drivers) vary widely in this respect. It's a good idea to benchmark your application with and without statement pooling to see if and how much it helps.

You configure statement pooling in c3p0 via the following configuration parameters:

maxStatements

maxStatementsPerConnection

statementCacheNumDeferredCloseThreads

maxStatementsis JDBC's standard parameter for controlling statement pooling.maxStatementsdefines the total numberPreparedStatementsa DataSource will cache. The pool will destroy the least-recently-used PreparedStatement when it hits this limit. This sounds simple, but it's actually a strange approach, because cached statements conceptually belong to individual Connections; they are not global resources. To figure out a size formaxStatementsthat does not "churn" cached statements, you need to consider the number offrequently usedPreparedStatements in your application,and multiply that by the number of Connections you expect in the pool (maxPoolSizein a busy application).

maxStatementsPerConnectionis a non-standard configuration parameter that makes a bit more sense conceptually. It defines how many statements each pooled Connection is allowed to own. You can set this to a bit more than the number ofPreparedStatementsyour applicationfrequentlyuses, to avoid churning.

If either of these parameters are greater than zero, statement pooling will be enabled. If both parameters are greater than zero, both limits will be enforced. If only one is greater than zero, statement pooling will be enabled, but only one limit will be enforced.

大概意思就是這兩個(gè)，有一個(gè)值如果大于0，c3p0的statement pool就會(huì)發(fā)生作用。

以上所有的配置都是基于c3p0的最新版本。PS一下，還是2015年的JAR。

通過引入最新的C3P0包，另外增加了兩段配置，線上觀察兩天，問題解決。

最后打個(gè)小廣告，JAVA世界最快的JDBC連接池，非HikariCP莫屬。已經(jīng)甩c3p0好幾個(gè)街角，有圖有真像。