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; }
再接着看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; } }
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); } }
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(); } }
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().getName()); } catch (InterruptedException | SQLException e) { e.printStackTrace(); } } } }
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"; } } } } }

Hot AI Tools

Undresser.AI Undress
AI-powered app for creating realistic nude photos

AI Clothes Remover
Online AI tool for removing clothes from photos.

Undress AI Tool
Undress images for free

Clothoff.io
AI clothes remover

AI Hentai Generator
Generate AI Hentai for free.

Hot Article

Hot Tools

Notepad++7.3.1
Easy-to-use and free code editor

SublimeText3 Chinese version
Chinese version, very easy to use

Zend Studio 13.0.1
Powerful PHP integrated development environment

Dreamweaver CS6
Visual web development tools

SublimeText3 Mac version
God-level code editing software (SublimeText3)

Hot Topics



When managing WordPress websites, you often encounter complex operations such as installation, update, and multi-site conversion. These operations are not only time-consuming, but also prone to errors, causing the website to be paralyzed. Combining the WP-CLI core command with Composer can greatly simplify these tasks, improve efficiency and reliability. This article will introduce how to use Composer to solve these problems and improve the convenience of WordPress management.

When developing a project that requires parsing SQL statements, I encountered a tricky problem: how to efficiently parse MySQL's SQL statements and extract the key information. After trying many methods, I found that the greenlion/php-sql-parser library can perfectly solve my needs.

During the development process, we often need to perform syntax checks on PHP code to ensure the correctness and maintainability of the code. However, when the project is large, the single-threaded syntax checking process can become very slow. Recently, I encountered this problem in my project. After trying multiple methods, I finally found the library overtrue/phplint, which greatly improves the speed of code inspection through parallel processing.

In Laravel development, dealing with complex model relationships has always been a challenge, especially when it comes to multi-level BelongsToThrough relationships. Recently, I encountered this problem in a project dealing with a multi-level model relationship, where traditional HasManyThrough relationships fail to meet the needs, resulting in data queries becoming complex and inefficient. After some exploration, I found the library staudenmeir/belongs-to-through, which easily installed and solved my troubles through Composer.

In the process of developing a website, improving page loading has always been one of my top priorities. Once, I tried using the Miniify library to compress and merge CSS and JavaScript files in order to improve the performance of the website. However, I encountered many problems and challenges during use, which eventually made me realize that Miniify may no longer be the best choice. Below I will share my experience and how to install and use Minify through Composer.

I'm having a tricky problem when developing a front-end project: I need to manually add a browser prefix to the CSS properties to ensure compatibility. This is not only time consuming, but also error-prone. After some exploration, I discovered the padaliyajay/php-autoprefixer library, which easily solved my troubles with Composer.

I encountered a tricky problem when developing a small application: the need to quickly integrate a lightweight database operation library. After trying multiple libraries, I found that they either have too much functionality or are not very compatible. Eventually, I found minii/db, a simplified version based on Yii2 that solved my problem perfectly.

When developing PHP projects, you often encounter the problem that some functions or methods have been executed for too long, causing program timeout. I've tried multiple solutions, but the results are not satisfactory until I discovered the phpunit/php-invoker library. This library completely solved my problem by setting the timeout time to call the executable function.
