Multi-Data Source deadlock in C3P0

Recently, the data migration tool is almost completed. Today, the connection pool is changed to C3P0, and a problem is found, A deadlock occurs when C3P0 of multiple data sources is configured to obtain connections of different data sources at the same time. 1. Run the following code and use JProfiler for testing. The Code packagecom. highgo. test. c3p0dea is deadlocked.

Recently, the data migration tool is almost completed. Today, the connection pool is changed to C3P0, and a problem is found, A deadlock occurs when C3P0 of multiple data sources is configured to obtain connections of different data sources at the same time. 1. Run the following code and use JProfiler for testing. The code package com. highgo. test. c3p0dea is deadlocked.

Recently, the data migration tool is almost completed. Today, the connection pool is changed to C3P0, and a problem is found, A deadlock occurs when C3P0 of multiple data sources is configured to obtain connections of different data sources at the same time.

1. Run the following code and use JProfiler for testing. The deadlock may occur:

Code:

Package com. highgo. test. c3p0deadlock; import java. SQL. SQLException; import com. mchange. v2.c3p0. comboPooledDataSource; // lock the getConnection of the ComboPooledDataSource of the source postgre using a lock public class Test {public static void main (String [] args) throws InterruptedException {ComboPooledDataSource source = new ComboPooledDataSource ("source"); combooleddatasource source2 = new ComboPooledDataSource ("source"); ComboP OoledDataSource 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 (ipvs2), "ipvs2 "). start () ;}} clas S SourceGetConn implements Runnable {private ComboPooledDataSource source = null; public SourceGetConn (combooleddatasource 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. S = source ;}@ Overridepublic void run () {while (true) {try {Thread. sleep (1000); ipvs. getConnection (); System. out. println ("I get a Con Nection! I am in "+ Thread. currentThread (). getName ();} catch (InterruptedException | SQLException e) {e. printStackTrace ();}}}}

Deadlock:

We can see that both the source and postgre processes are locked by an unrecorded object.

2. thread of the above Code. the sleep comment is removed and there is no deadlock during running. Therefore, check the source code of C3P0. ComboPooledDataSource @ getConnection is inherited from AbstractPoolBackedDataSource # getConnection. The Code is as follows:

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();    }

First look at this PoolManager,AbstractPoolBackedDataSource # the implementation of the getPoolManager method is as follows, which is thread-safe.

 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;        }

The code above also shows that only one PoolManager reference is maintained for a DataSource instance.
The getPool method is thread-safe;

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;        }    }

Check again 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); }    }

A C3P0PooledConnection instance is returned. The methods in the C3P0PooledConnection class are thread-safe. The last site of ComboPooledDataSource @ getConnection is C3P0PooledConnection. # GetConnection; as follows:

 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(); }    }

From the source code analysis above, we can see that the ComboPooledDataSource @ getConnection of a combooleddatasource instance is thread-safe and can be safely called. You can test it and slightly modify the initial Code as follows:

Package com. highgo. test. c3p0deadlock; import java. SQL. SQLException; import com. mchange. v2.c3p0. comboPooledDataSource; // lock the getConnection of the ComboPooledDataSource of the source postgre using a lock public class Test {public static void main (String [] args) throws InterruptedException {ComboPooledDataSource source = new ComboPooledDataSource ("source"); // ComboPooledDataSource source2 = new ComboPooledDataSource ("source"); Comb OPooledDataSource 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 (ipvs2), "ipvs2 "). start () ;}} c Lass SourceGetConn implements Runnable {private ComboPooledDataSource source = null; public SourceGetConn (combooleddatasource 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. S = source ;}@ Overridepublic void run () {while (true) {try {Thread. sleep (1000); ipvs. getConnection (); System. out. println ("I get a Con Nection! I am in "+ Thread. currentThread (). getName ();} catch (InterruptedException | SQLException e) {e. printStackTrace ();}}}}

Set The ComboPooledDataSource instance is passed to the two threads respectively for getConnection. The getConnection process can be run without locking, and there is no problem at all.
3. After testing, we found two For a ComboPooledDataSource instance, The getConnection method is no longer locked.

Summary:

C3P0The getConnection method of the ComboPooledDataSource instance is thread-safe.

C3P0 in multiple data sourcesThe getConnection method of the ComboPooledDataSource instance is thread-safe.

C3P0 in multiple data sourcesWhen ComboPooledDataSource does not call getConnection at the same time, no deadlock will occur (based on probability, a deadlock will certainly occur after several times)

C3P0 in multiple data sourcesA deadlock occurs when the getConnection method of the ComboPooledDataSource instance is called at the same time (two adjacent lines of code), as described in 1.

4. Conclusion:

Multiple Data sourcesComboPooledDataSourceThe getConnection method call of the instance must be mutually exclusive.

The test code is as follows:

Package com. highgo. test. c3p0deadlock; import java. SQL. SQLException; import java. util. concurrent. locks. reentrantLock; import com. mchange. v2.c3p0. comboPooledDataSource; // lock the getConnection of the ComboPooledDataSource of the source postgre using a lock public class Test2 {public static void main (String [] args) throws InterruptedException {ComboPooledDataSource source = new ComboPooledDataSource ("source"); combooleddatasource sou Rce2 = new release ("source"); ComboPooledDataSource postgres = new ComboPooledDataSource ("postgres"); latest release S2 = 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 DestGetConn 2 (postgres, lock), "postgres "). start (); new Thread (new DestGetConn2 (latency S2, lock), "latency S2 "). 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); l Ock. 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 versions S = null; private ReentrantLock lock; public DestGetConn2 (ComboPooledDataSource source, ReentrantLock lock) {this. postgres = source; this. lock = lock ;}@ Overridepublic void run () {while (true) {try {Thread. sl Eep (1000); lock. lock (); ipvs. getConnection (); lock. unlock (); System. out. println ("I get a Connection! I am in "+ Thread. currentThread (). getName ();} catch (InterruptedException | SQLException e) {e. printStackTrace ();}}}}

5. Finally, summarize a tool class with high efficiency.

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";}}}}}