Java for Web学习笔记（一零五）：Spring框架中使用JPA（5）Isolation和C3P0（上）

何为事务的isolation

isolation对于数据的一致性很重要。网上有很多介绍，推荐阅读：

• MYSQL官网 14.5.2.1 Transaction Isolation Levels
• Hibernate Tutorial 20.7.2 Transaction Isolation Levels

一般而言，如果使用READ_UNCOMMITTED，这适合于只读的表格，大家读取同一条目无需等待；但是对于读写的表格，如果我们读取某个数据，然后修改并写入，就需要使用READ_COMMITTED、REPEATABLE_READ或者SERIALIZABLE，它们会将读操作放入到事务中，如果使用READ_UNCOMMITTED，就会有数据混乱（脏数据）的风险。其中SERIALIZABLE慎用，很容易会造成死锁。具体采用哪种，根据需求，下面是javadoc的介绍：

Connection.TRANSACTION_READ_UNCOMMITTED
A constant indicating that dirty reads, non-repeatable reads and phantom reads can occur. This level allows a row changed by one transaction to be read by another transaction before any changes in that row have been committed (a "dirty read"). If any of the changes are rolled back, the second transaction will have retrieved an invalid row.
Connection.TRANSACTION_READ_COMMITTED
A constant indicating that dirty reads are prevented; non-repeatable reads and phantom reads can occur. This level only prohibits a transaction from reading a row with uncommitted changes in it.
Connection.TRANSACTION_REPEATABLE_READ
A constant indicating that dirty reads and non-repeatable reads are prevented; phantom reads can occur. This level prohibits a transaction from reading a row with uncommitted changes in it, and it also prohibits the situation where one transaction reads a row, a second transaction alters the row, and the first transaction rereads the row, getting different values the second time (a "non-repeatable read").
Connection.TRANSACTION_SERIALIZABLE
A constant indicating that dirty reads, non-repeatable reads and phantom reads are prevented. This level includes the prohibitions in TRANSACTION_REPEATABLE_READ and further prohibits the situation where one transaction reads all rows that satisfy a WHERE condition, a second transaction inserts a row that satisfies that WHERE condition, and the first transaction rereads for the same condition, retrieving the additional "phantom" row in the second read.

数据源的isolaction设置

tomcat数据源

isolation是针对数据源进行设置的。如果我们采用tomcat的数据源，只需进行全局配置。

<Resource name="jdbc/learnTest" type="javax.sql.DataSource"
     maxActive="20" maxIdle="5" maxWait="10000"
     username="test" password="test123456"
     driverClassName="com.mysql.jdbc.Driver"
     defaultTransactionIsolation="READ_COMMITTED"
     url="jdbc:mysql://191.8.1.107:3306/test" />

C3P0数据源

一般情况，我们推荐使用tomcat设置数据源的方式，因为在war包无需考虑封装mysql的相关包，也无需在卸装是谨慎处理数据源的关闭。但是基于某些原因，例如在war启动后，动态获取数据库的配置信息，这时就很难使用静态的tomcat数据源的配置。采用的提供数据库连接管理池为C3P0，我们之前也在代码中介绍过，再次借isolation的机会再次详细说明。

如何创建C3P0数据源并安全关闭

为来方便使用，我们使用C3P0Utils来实现数据源的创建，并提供destory()方式，供war关闭时安全地关闭数据源。

public class C3P0Utils {
	private static final Logger logger = LogManager.getLogger();	
	private static final Map<String,ComboPooledDataSource> dataSourceDb = new HashMap<>();
	
	public static ComboPooledDataSource open (String name,String jdbcUrl,String user, String pw, 
			int minPoolSize,int maxPoolSize,int poolIncrement) throws Exception{
		if(dataSourceDb.containsKey(name))
			throw new Exception("数据源已经存在，请不要重复创建。");
		
		ComboPooledDataSource dataSource = new ComboPooledDataSource();
		dataSource.setDriverClass("com.mysql.jdbc.Driver");
		dataSource.setJdbcUrl(jdbcUrl + "?useSSL=true&useUnicode=true&characterEncoding=utf-8&autoReconnect=true");
		dataSource.setUser(user);
		dataSource.setPassword(pw);		
		dataSource.setMinPoolSize(minPoolSize);
		dataSource.setAcquireIncrement(poolIncrement);
		dataSource.setMaxPoolSize(maxPoolSize);
		dataSource.setInitialPoolSize(minPoolSize);
		dataSource.setMaxIdleTime(30);		
		dataSource.setIdleConnectionTestPeriod(30);
		//下面这句和设置Isolation有关，我们将后面介绍
		dataSource.setConnectionCustomizerClassName("com.wei.utils.MyConnectionCustomizer");
		/* 检查数据源是否有效。C3P0不会在创建dataSource时就去连接，而在第一次连接请求时进行。
		 * 一方面会增加第一个用户请求的处理时间，影响第一个用户的体验；
		 * 另一方面如果密码错误或者地址不可访问，我们需要在项目部署的第一时间知道是否部署正确，而不是等到业务来了才发现。*/
		try{
			Connection conn = dataSource.getConnection();
			conn.close();
		}catch(Exception e){
			DataSources.destroy( dataSource);  // or dataSource.close()
			throw new Exception("DataSource  open error :" + e.toString());			
		}
		
		dataSourceDb.put(name, dataSource);		
		logger.info("[INIT] Opened DataSource '{}' ... [OK]",name);
		return dataSource;
	}

	
	/**
	 * 在项目结束的时候，应释放所有资源，应调用本方法。建议阅读C3P0官网。
	 */
	public static void destroy(){
		logger.info("[CLOSE] close DataSource...");
		
		Iterator<Map.Entry<String,ComboPooledDataSource>> iter = dataSourceDb.entrySet().iterator();
		while (iter.hasNext()) {
			Map.Entry<String,ComboPooledDataSource> entry =  iter.next();
			ComboPooledDataSource dataSource = (ComboPooledDataSource)entry.getValue();
			dataSource.close();
		}
		dataSourceDb.clear();
		
		com.mysql.jdbc.AbandonedConnectionCleanupThread.checkedShutdown();
		
		Enumeration<java.sql.Driver> drivers = DriverManager.getDrivers();
		while(drivers.hasMoreElements()) {
			try {				
				Driver driver = drivers.nextElement();
				DriverManager.deregisterDriver(driver);
			} catch (SQLException e) {
				logger.error("C3M0 destroy error : {}", e.toString());
			}
		}
	}	
}

在上下文配置中：

@Bean 
public DataSource springJpaDataSource() throws Exception{
    return C3P0Utils.open("learnTest","jdbc:mysql://192.168.1.2:3306/test","test","test123456",2, 200, 2);
}

设置C3P0的isolation等级

如果采用C3P0，稍微要复杂一点。C3P0的缺省可能是NONE，或者是READ_UNCOMMITTED，这个我未确定。可以通过定制ConnectionCustomizer接口来实现：

public class MyConnectionCustomizer implements ConnectionCustomizer{ 
    @Override
    public void onAcquire( Connection c, String pdsIdt ) {
        // override the default transaction isolation of newly acquired Connections 
        c.setTransactionIsolation( Connection.TRANSACTION_SERIALIZABLE);
    }
    @Override
    public void onDestroy( Connection c, String pdsIdt ) {
    }
    @Override
    public void onCheckOut( Connection c, String pdsIdt ) {
    }
    @Override
    public void onCheckIn( Connection c, String pdsIdt ) {
    }
}

在创建c3p0数据源时，需要设置改定制的连接接口。

dataSource.setConnectionCustomizerClassName("com.wei.utils.MyConnectionCustomizer");

相关链接：我的Professional Java for Web Applications相关文章