Home Database Mysql Tutorial C3P0多数据源的死锁问题

C3P0多数据源的死锁问题

Jun 07, 2016 pm 04:09 PM
tool data data source deadlock migrate question

最近在写的数据迁移工具完成的差不多了,今天将连接池换成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();
			}
		}
	}

}
Copy after login

死锁情况:\

可以看到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();
    }
Copy after login

先看这个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;	    
    }
Copy after login
从上边的代码也可以看出,一个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;
        }
    }
Copy after login
再看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); }
    }
Copy after login
返回一个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(); }
    }
Copy after login
从上边的源码分析可以看出,一个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().getName());
			} catch (InterruptedException | SQLException e) {
				e.printStackTrace();
			}
		}
	}

}
Copy after login
将一个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();
			}
		}
	}

}
Copy after login

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

		}

	}
}
Copy after login
Statement of this Website
The content of this article is voluntarily contributed by netizens, and the copyright belongs to the original author. This site does not assume corresponding legal responsibility. If you find any content suspected of plagiarism or infringement, please contact admin@php.cn

Hot AI Tools

Undresser.AI Undress

Undresser.AI Undress

AI-powered app for creating realistic nude photos

AI Clothes Remover

AI Clothes Remover

Online AI tool for removing clothes from photos.

Undress AI Tool

Undress AI Tool

Undress images for free

Clothoff.io

Clothoff.io

AI clothes remover

AI Hentai Generator

AI Hentai Generator

Generate AI Hentai for free.

Hot Article

R.E.P.O. Energy Crystals Explained and What They Do (Yellow Crystal)
2 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Repo: How To Revive Teammates
4 weeks ago By 尊渡假赌尊渡假赌尊渡假赌
Hello Kitty Island Adventure: How To Get Giant Seeds
3 weeks ago By 尊渡假赌尊渡假赌尊渡假赌

Hot Tools

Notepad++7.3.1

Notepad++7.3.1

Easy-to-use and free code editor

SublimeText3 Chinese version

SublimeText3 Chinese version

Chinese version, very easy to use

Zend Studio 13.0.1

Zend Studio 13.0.1

Powerful PHP integrated development environment

Dreamweaver CS6

Dreamweaver CS6

Visual web development tools

SublimeText3 Mac version

SublimeText3 Mac version

God-level code editing software (SublimeText3)

What are the recommended websites for virtual currency app software? What are the recommended websites for virtual currency app software? Mar 31, 2025 pm 09:06 PM

This article recommends ten well-known virtual currency-related APP recommendation websites, including Binance Academy, OKX Learn, CoinGecko, CryptoSlate, CoinDesk, Investopedia, CoinMarketCap, Huobi University, Coinbase Learn and CryptoCompare. These websites not only provide information such as virtual currency market data, price trend analysis, etc., but also provide rich learning resources, including basic blockchain knowledge, trading strategies, and tutorials and reviews of various trading platform APPs, helping users better understand and make use of them

How to roll positions in digital currency? What are the digital currency rolling platforms? How to roll positions in digital currency? What are the digital currency rolling platforms? Mar 31, 2025 pm 07:36 PM

Digital currency rolling positions is an investment strategy that uses lending to amplify trading leverage to increase returns. This article explains the digital currency rolling process in detail, including key steps such as selecting trading platforms that support rolling (such as Binance, OKEx, gate.io, Huobi, Bybit, etc.), opening a leverage account, setting a leverage multiple, borrowing funds for trading, and real-time monitoring of the market and adjusting positions or adding margin to avoid liquidation. However, rolling position trading is extremely risky, and investors need to operate with caution and formulate complete risk management strategies. To learn more about digital currency rolling tips, please continue reading.

On which platform is web3 transaction? On which platform is web3 transaction? Mar 31, 2025 pm 07:54 PM

This article lists the top ten well-known Web3 trading platforms, including Binance, OKX, Gate.io, Kraken, Bybit, Coinbase, KuCoin, Bitget, Gemini and Bitstamp. The article compares the characteristics of each platform in detail, such as the number of currencies, trading types (spot, futures, options, NFT, etc.), handling fees, security, compliance, user groups, etc., aiming to help investors choose the most suitable trading platform. Whether it is high-frequency traders, contract trading enthusiasts, or investors who focus on compliance and security, they can find reference information from it.

Top 10 of the formal Web3 trading platform APP rankings (authoritatively released in 2025) Top 10 of the formal Web3 trading platform APP rankings (authoritatively released in 2025) Mar 31, 2025 pm 08:09 PM

Based on market data and common evaluation criteria, this article lists the top ten formal Web3 trading platform APPs in 2025. The list covers well-known platforms such as Binance, OKX, Gate.io, Huobi (now known as HTX), Crypto.com, Coinbase, Kraken, Gemini, BitMEX and Bybit. These platforms have their own advantages in user scale, transaction volume, security, compliance, product innovation, etc. For example, Binance is known for its huge user base and rich product services, while Coinbase focuses on security and compliance. Choosing a suitable platform requires comprehensive consideration based on your own needs and risk tolerance.

Recommended tutorial for newbies in the commonly used virtual currency exchange in the currency circle Recommended tutorial for newbies in the commonly used virtual currency exchange in the currency circle Mar 31, 2025 pm 10:45 PM

This article provides detailed exchange recommendations and introductory tutorials for beginners in the currency circle. Commonly used exchanges such as Coinbase, Binance, Kraken, Ouyi and Sesame Open Door are recommended, and the steps for registration, identity verification, security settings, recharge and trading are introduced. The article also emphasizes the importance of security awareness, risk control and continuous learning, aiming to help beginners enter the digital asset field safely and rationally.

What are the free market software websites What are the free market software websites Mar 31, 2025 pm 10:36 PM

There are six free market viewing software websites: 1. Binance platform, suitable for digital asset investors; 2. OKX platform, providing rich market data; 3. Sesame Open Door (Gate.io) platform, suitable for users who trade in Gate.io; 4. TradingView, providing professional charting tools; 5. CoinMarketCap, covering a wide range of digital asset data; 6. CoinGecko, providing project fundamental evaluation. When choosing a platform, you need to consider investment objects, chart function requirements, data comprehensiveness and user experience.

The latest tutorial for commonly used virtual currency exchanges in the currency circle 2025 The latest tutorial for commonly used virtual currency exchanges in the currency circle 2025 Mar 31, 2025 pm 10:57 PM

This article recommends several commonly used and relatively safe virtual currency exchanges for beginners entering the currency circle in 2025, including Binance, Ouyi, Coinbase and Sesame Open Door. The article provides detailed tutorials on registration, authentication, security setup and transaction process, and emphasizes the importance of risk control, security awareness and ongoing learning, aiming to help beginners securely get started with digital asset trading.

See all articles