Oracle和Mysql中的数据库事务问题：Mysql Read-Repeatable有问题

今天不知不觉想到数据库的乐观锁和悲观锁，遂想写个程序测测，却发现了另一个问题，Mysql InnoDB的Read-Repeatable事务级别使用不当会存在数据一致性问题。

 

如下的测试程序：

 

 

public class OptimisticAndPessimisticLockTest2 {

	public static void main(String[] args) throws Exception {
		//创建测试表和数据
		initDatabase();
		
		//创建两个线程同时操作同一条记录
		OptimisticThread ot1 = new OptimisticThread(newConnection(), "O1");
		OptimisticThread ot2 = new OptimisticThread(newConnection(), "O2");
		
		ot1.start();
		ot2.start();
	}
	
	public static Connection newConnection() throws Exception {
		Class.forName("com.mysql.jdbc.Driver");
		Connection con = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "root");
		return con;
	}
	
	public static void initDatabase() throws Exception {
		Connection con = newConnection();
		Statement stmt = con.createStatement();
		stmt.execute("drop table if exists locktest");
		stmt.execute("create table locktest( name varchar(10)) ENGINE=InnoDB");
		stmt.executeUpdate("insert into locktest values('XJD')");
		con.close();
	}
	
	public static class OptimisticThread extends Thread {
		Connection con;
		String name;
		
		public OptimisticThread(Connection con, String name) {
			this.con = con;
			this.name = name;
		}
		
		@Override
		public void run() {
			try {
				//设置事务级别为可重复读
				this.con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);
				this.con.setAutoCommit(false); //开始事务
				System.out.println("Started Transaction...: " + name);
				
				Statement stmt = this.con.createStatement();
				
				//先查询一下是否有'XJD'的记录
				ResultSet rs = stmt.executeQuery("select * from locktest where name='XJD'");
				if (rs.next()) {
					System.out.println("Got Record: " + name + " value: " + rs.getString(1));
				}
				rs.close();
				
				Thread.sleep(5000);//暂停5s让另一个线程也查询完成
				
				//更新'XJD'的记录
				int i = stmt.executeUpdate("update locktest set name = '" + name + "' where name = 'XJD'");
				System.out.println("Update Record: " + name + " count: " + i);//更新成功后i为1
				
				Thread.sleep(5000);//暂停5s让另一个线程也作更新操作
				
				//查询更新后的记录
				rs = stmt.executeQuery("select * from locktest where name='" + name + "'");
				if (rs.next()) {
					System.out.println("Got Record: " + name + " value: " + rs.getString(1));
				}
				rs.close();
				//查询原来的记录
				rs = stmt.executeQuery("select * from locktest where name='XJD'");
				if (rs.next()) {
					System.out.println("Got Record: " + name + " value: " + rs.getString(1));
				}
				rs.close();
				
				Thread.sleep(10000);
				
				System.out.println("Commiting Transaction...: " + name);
				this.con.commit();
				this.con.close();
				
			} catch (SQLException e) {
				System.out.println("Exception in " + name + ": " + e);
				e.printStackTrace();
			} catch (InterruptedException e) {
				e.printStackTrace();
			}
			
		}
		
	}

}

 

输出结果：

 

 

Started Transaction...: O1
Got Record: O1 value: XJD
Started Transaction...: O2
Got Record: O2 value: XJD
Update Record: O1 count: 1
Got Record: O1 value: O1
Commiting Transaction...: O1
Update Record: O2 count: 0    //见下一行的说明
Got Record: O2 value: XJD       //此处在O2线程中还可以查询到XJD记录，但前一条的Update结果却是0
Commiting Transaction...: O2

 

从输出结果中注释的两行可以看出，Read-Repeatable事务级别容易出现问题：

  O1和O2两个线程都开启了事务--O1和O2中都可以查询到XJD的记录--O1更新并提交了XJD记录为'O1'--O2中Update语句返回0可知O2没有更新到‘XJD'的记录--但是O2中仍可使用查询语句查询到‘XJD'的记录(因为可重得读事务设置)--但此时问题就来了，我在O2中可查询到记录，为什么更新不到呢？？？？

 

从上面的分析，可以看出，Read-Repeatable事务级别容易出现业务上的问题，比如我们在一个事务中查询到一条记录，而后我们对该记录进行操作，发现这些操作跟本不起作用，如果业务比较复杂，跨度大，很容易使我们“迷惑“，我们错在哪，为什么查询到了，却更新不到！！！

 

难怪Oracle不支持Read-Repeatable事务，在对Oracle执行：

 

 

con.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

 抛出异常：

 

 

java.sql.SQLException: 仅 READ_COMMITTED 和 SERIALIZABLE 是有效的事务处理级

 

我使用的是Mysql默认安装，发现Mysql Innodb默认使用的就是Read-Repeatable事务，所以只能说要谨慎吧！

 

 

再则对于乐观锁，可能使用版本列、时间戳列、其它列数据对比，无论是何种方式，都要在提交前先查询一次，与之前查询的数据进行某些列的对比，如果使用了Read-Repeatable事务，那么对比永远是相同的，这样乐观锁就出现问题了，不是我们想要的结果，所以在使用乐观锁时不能使用Read-Repeatable事务，而应该使用Read-Commited事务。

 

以上为个人见解，本人对数据库事使用不是很多，有问题之处望高人指点赐教！！！

 

此处附上一处乐观与悲观锁的文章：http://www.zhujiangroad.com/program/Oracle/21775.html