C3P0多数据源的死锁问题

最近在写的数据迁移工具完成的差不多了，今天将连接池换成C3P0，发现一个问题，就是配置了多个数据源的C3P0在同时获取不同数据源的Connection时会发生死锁。 1.运行如下的代码，用JProfiler测试，会发现死锁的情况： 代码： package com.highgo.test.c3p0dea

最近在写的数据迁移工具完成的差不多了，今天将连接池换成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;	}	@Override	public 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;	}	@Override	public 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;	}	@Override	public 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;	}	@Override	public void run() {		while (true) {			try {				Thread.sleep(1000);				postgres.getConnection();				System.out.println("I get a Connection! I am in " + Thread.currentThread<div>本文来源gaodai^.ma#com搞#代!码网</div>().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;	}	@Override	public 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;	}	@Override	public 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";		@Override		public void run() {			while (true) {				try {					Connection conn = C3P0Util.getInstance().getConnection("");					System.out.println("hello,this is " + dataSource);				} catch (SQLException e) {					// TODO Auto-generated catch block					e.printStackTrace();				}				if ("source".equals(dataSource)) {					dataSource = "postgres";				} else {					dataSource = "source";				}			}		}	}}

喜欢 (0)赏

[搞代码]

分享 (0)