C3P0多資料來源的死結問題，

C3P0多資料來源的死結問題，

最近在寫的資料移轉工具完成的差不多了，今天將串連池換成C3P0，發現一個問題，就是配置了多個資料來源的C3P0在同時擷取不同資料來源的Connection時會發生死結。

1.運行如下的代碼，用JProfiler測試，會發現死結的情況：

代碼：

package com.highgo.test.c3p0deadlock;import java.sql.SQLException;import com.mchange.v2.c3p0.ComboPooledDataSource;//加鎖source個postgre的ComboPooledDataSource的getConnection用一個鎖public class Test {public static void main(String[] args) throws InterruptedException {ComboPooledDataSource source = new ComboPooledDataSource("source");ComboPooledDataSource source2 = new ComboPooledDataSource("source");ComboPooledDataSource postgres = new ComboPooledDataSource("postgres");ComboPooledDataSource postgres2 = new ComboPooledDataSource("postgres");new Thread(new SourceGetConn(source), "source").start();//new Thread(new SourceGetConn(source2), "source2").start();//Thread.sleep(1000);new Thread(new DestGetConn(postgres), "postgres").start();//new Thread(new DestGetConn(postgres2), "postgres2").start();}}class SourceGetConn implements Runnable {private ComboPooledDataSource source = null;public SourceGetConn(ComboPooledDataSource source) {this.source = source;}@Overridepublic void run() {while (true) {try {Thread.sleep(1000);source.getConnection();System.out.println("I get a Connection! I am in " + Thread.currentThread().getName());} catch (InterruptedException | SQLException e) {e.printStackTrace();}}}}class DestGetConn implements Runnable {private ComboPooledDataSource postgres = null;public DestGetConn(ComboPooledDataSource source) {this.postgres = source;}@Overridepublic void run() {while (true) {try {Thread.sleep(1000);postgres.getConnection();System.out.println("I get a Connection! I am in " + Thread.currentThread().getName());} catch (InterruptedException | SQLException e) {e.printStackTrace();}}}}

死結情況：

可以看到source和postgre兩個進程都被一個沒有記錄的對象鎖住了。

2.將上邊的代碼的Thread.sleep注釋去掉，在運行，是不會有死結問題的，於是查看C3P0的原始碼，ComboPooledDataSource@getConnection是繼承自AbstractPoolBackedDataSource#getConnection，代碼如下：

public Connection getConnection() throws SQLException    {        PooledConnection pc = getPoolManager().getPool().checkoutPooledConnection();        return pc.getConnection();    }    public Connection getConnection(String username, String password) throws SQLException    {         PooledConnection pc = getPoolManager().getPool(username, password).checkoutPooledConnection();        return pc.getConnection();    }

先看這個PoolManager，AbstractPoolBackedDataSource#getPoolManager方法的實現如下，是安全執行緒的

 private synchronized C3P0PooledConnectionPoolManager getPoolManager() throws SQLException    {        if (poolManager == null)        {            ConnectionPoolDataSource cpds = assertCpds();            poolManager = new C3P0PooledConnectionPoolManager(cpds, null, null, this.getNumHelperThreads(), this.getIdentityToken(), this.getDataSourceName());            if (logger.isLoggable(MLevel.INFO))                logger.info("Initializing c3p0 pool... " + this.toString( true )  /* + "; using pool manager: " + poolManager */);        }        return poolManager;        }

從上邊的代碼也可以看出，一個DataSource執行個體，只保持一個PoolManager的引用。
再接著看getPool方法，也是安全執行緒的；

public synchronized C3P0PooledConnectionPool getPool(String username, String password, boolean create) throws SQLException    {        if (create)            return getPool( username, password );        else        {            DbAuth checkAuth = new DbAuth( username, password );            C3P0PooledConnectionPool out = (C3P0PooledConnectionPool) authsToPools.get(checkAuth);            if (out == null)                throw new SQLException("No pool has been initialized for databse user '" + username + "' with the specified password.");            else                return out;        }    }

再看 C3P0PooledConnectionPool#checkoutPooledConnection（）；

public PooledConnection checkoutPooledConnection() throws SQLException    {         //System.err.println(this + " -- CHECKOUT");        try     { PooledConnection pc = (PooledConnection) this.checkoutAndMarkConnectionInUse(); pc.addConnectionEventListener( cl );return pc;    }        catch (TimeoutException e)        { throw SqlUtils.toSQLException("An attempt by a client to checkout a Connection has timed out.", e); }        catch (CannotAcquireResourceException e)        { throw SqlUtils.toSQLException("Connections could not be acquired from the underlying database!", "08001", e); }        catch (Exception e)        { throw SqlUtils.toSQLException(e); }    }

返回一個C3P0PooledConnection 執行個體；C3P0PooledConnection 這個類裡的方法都是安全執行緒的。ComboPooledDataSource@getConnection的最後一站就是C3P0PooledConnection #getConnection；如下：

 public synchronized Connection getConnection()throws SQLException    { if ( exposedProxy != null)    {//DEBUG//System.err.println("[DOUBLE_GET_TESTER] -- double getting a Connection from " + this );//new Exception("[DOUBLE_GET_TESTER] -- Double-Get Stack Trace").printStackTrace();//origGet.printStackTrace();// System.err.println("c3p0 -- Uh oh... getConnection() was called on a PooledConnection when " +//    "it had already provided a client with a Connection that has not yet been " +//    "closed. This probably indicates a bug in the connection pool!!!");logger.warning("c3p0 -- Uh oh... getConnection() was called on a PooledConnection when " +       "it had already provided a client with a Connection that has not yet been " +       "closed. This probably indicates a bug in the connection pool!!!");return exposedProxy;    }else    { return getCreateNewConnection(); }    }

從上邊的源碼分析可以看出，一個ComboPooledDataSource執行個體的ComboPooledDataSource@getConnection是安全執行緒的，可以放心調用；可以測試一下，將最開始的代碼稍微修改下，如下：

package com.highgo.test.c3p0deadlock;import java.sql.SQLException;import com.mchange.v2.c3p0.ComboPooledDataSource;//加鎖source個postgre的ComboPooledDataSource的getConnection用一個鎖public class Test {public static void main(String[] args) throws InterruptedException {ComboPooledDataSource source = new ComboPooledDataSource("source");//ComboPooledDataSource source2 = new ComboPooledDataSource("source");ComboPooledDataSource postgres = new ComboPooledDataSource("postgres");//ComboPooledDataSource postgres2 = new ComboPooledDataSource("postgres");new Thread(new SourceGetConn(source), "source").start();new Thread(new SourceGetConn(source), "source2").start();//Thread.sleep(1000);//new Thread(new DestGetConn(postgres), "postgres").start();//new Thread(new DestGetConn(postgres2), "postgres2").start();}}class SourceGetConn implements Runnable {private ComboPooledDataSource source = null;public SourceGetConn(ComboPooledDataSource source) {this.source = source;}@Overridepublic void run() {while (true) {try {Thread.sleep(1000);source.getConnection();System.out.println("I get a Connection! I am in " + Thread.currentThread().getName());} catch (InterruptedException | SQLException e) {e.printStackTrace();}}}}class DestGetConn implements Runnable {private ComboPooledDataSource postgres = null;public DestGetConn(ComboPooledDataSource source) {this.postgres = source;}@Overridepublic void run() {while (true) {try {Thread.sleep(1000);postgres.getConnection();System.out.println("I get a Connection! I am in " + Thread.currentThread().getName());} catch (InterruptedException | SQLException e) {e.printStackTrace();}}}}

將一個 ComboPooledDataSource執行個體，傳給兩個線程分別getConnection，getConnection的過程沒有加鎖的情況下是可以啟動並執行，完全沒有問題。
3.經過測試發現同一個資料來源的兩個 ComboPooledDataSource執行個體，getConnection方法不加鎖的情況下，也是沒有問題的。

稍微總結一下：

C3P0在一個ComboPooledDataSource執行個體的getConnection方法是安全執行緒的

C3P0在一個資料來源的多個ComboPooledDataSource執行個體的getConnection方法也是安全執行緒的

C3P0在多個資料來源的多個ComboPooledDataSource不同時調用getConnection的情況下，不會發生死結（基於機率，若干時間之後，肯定會發生死結）

C3P0在多個資料來源的多個ComboPooledDataSource執行個體的getConnection方法同時（相鄰的兩行代碼）調用時，會發生死結現象，如1中所述

4.總結：

屬於不同資料來源的多個ComboPooledDataSource執行個體的getConnection方法調用要互斥

測試代碼如下：

package com.highgo.test.c3p0deadlock;import java.sql.SQLException;import java.util.concurrent.locks.ReentrantLock;import com.mchange.v2.c3p0.ComboPooledDataSource;//加鎖source個postgre的ComboPooledDataSource的getConnection用一個鎖public class Test2 {public static void main(String[] args) throws InterruptedException {ComboPooledDataSource source = new ComboPooledDataSource("source");ComboPooledDataSource source2 = new ComboPooledDataSource("source");ComboPooledDataSource postgres = new ComboPooledDataSource("postgres");ComboPooledDataSource postgres2 = new ComboPooledDataSource("postgres");ReentrantLock lock = new ReentrantLock();new Thread(new SourceGetConn2(source, lock), "source").start();new Thread(new SourceGetConn2(source2, lock), "source2").start();Thread.sleep(1000);new Thread(new DestGetConn2(postgres, lock), "postgres").start();new Thread(new DestGetConn2(postgres2, lock), "postgres2").start();}}class SourceGetConn2 implements Runnable {private ComboPooledDataSource source = null;private ReentrantLock lock;public SourceGetConn2(ComboPooledDataSource source, ReentrantLock lock) {this.source = source;this.lock = lock;}@Overridepublic void run() {while (true) {try {Thread.sleep(1000);lock.lock();source.getConnection();lock.unlock();System.out.println("I get a Connection! I am in " + Thread.currentThread().getName());} catch (InterruptedException | SQLException e) {e.printStackTrace();}}}}class DestGetConn2 implements Runnable {private ComboPooledDataSource postgres = null;private ReentrantLock lock;public DestGetConn2(ComboPooledDataSource source, ReentrantLock lock) {this.postgres = source;this.lock = lock;}@Overridepublic void run() {while (true) {try {Thread.sleep(1000);lock.lock();postgres.getConnection();lock.unlock();System.out.println("I get a Connection! I am in " + Thread.currentThread().getName());} catch (InterruptedException | SQLException e) {e.printStackTrace();}}}}

5.最後總結一個效率還可以的工具類

package com.highgo.hgdbadmin.myutil;import java.sql.Connection;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Statement;import com.mchange.v2.c3p0.ComboPooledDataSource;public class C3P0Util {public static String SOURCE = "source";public static String POSTGRES = "postgres";private ComboPooledDataSource source = null;private ComboPooledDataSource postgres = null;private static C3P0Util instance = null;private C3P0Util() {source = new ComboPooledDataSource("source");postgres = new ComboPooledDataSource("postgres");}public static final synchronized C3P0Util getInstance() {if (instance == null) {instance = new C3P0Util();}return instance;}public synchronized Connection getConnection(String dataSource) throws SQLException {if ("source".equals(dataSource)) {return source.getConnection();} else if ("postgres".equals(dataSource)) {return postgres.getConnection();}return null;}public synchronized void close(Connection conn) {try {if (conn != null) {conn.close();conn = null;}} catch (SQLException e) {}}public synchronized void close(Statement stat) {try {if (stat != null) {stat.close();stat = null;}} catch (SQLException e) {}}public synchronized void close(ResultSet rest) {try {if (rest != null) {rest.close();rest = null;}} catch (SQLException e) {}}public static void main(String[] args) {new Thread(new TestThread(), "test").start();}private static class TestThread implements Runnable {private String dataSource = "source";@Overridepublic void run() {while (true) {try {Connection conn = C3P0Util.getInstance().getConnection("");System.out.println("hello,this is " + dataSource);} catch (SQLException e) {// TODO Auto-generated catch blocke.printStackTrace();}if ("source".equals(dataSource)) {dataSource = "postgres";} else {dataSource = "source";}}}}}