mysql jdbc 事务隔离级别_mysql数据库不可重复读事务隔离级别问题

在mysql数据库中，默认的事物隔离级别为不可重复读，而mysql对不可重复读实现得比较诡异，情况如下：

假如有两个事务同时执行两次查询和一次修改数据库中一张表，为啦方便起见下面使用A事务和B事务进行说明

启动A事务——>A查询表——>启动B事务——>B查询表——>B修改表——B事务提交(不会出错哦，郁闷中。。。)——>A查询表(数据会保证和上一次A查询的数据一致)——>A事务修改表(不出错)——>A事务提交(事务提交成功，不出错)

这样势必造成一个结果就是B修改的数据对于A是透明的，就好像不存在，而且最终会覆盖B的结果，如果大伙不信可以试试，如果哪位大侠有什么好的解决方法请跟帖，

当然最好不要修改数据库的事务隔离级别为最高级别。

我的测试环境是Win7 mysql5.1.60，数据库test,表t_test的引擎为InnoDB,编码为utf8，表结构如下:

mysql> select * from t_test;

+------+-------+------+--------+

| id | name | age | status |

+------+-------+------+--------+

| 1 | name1 | 27 | 1 |

| 2 | yyyy | 27 | 1 |

+------+-------+------+--------+

2 rows in set (0.00 sec)

问题补充

我的测试代码也贴出来

public class TestThread implements Runnable {

private Thread t;

public void setT(Thread t) {

this.t = t;

}

@Override

public void run() {

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "silence");

if(conn != null){

conn.setAutoCommit(false);

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

Statement statement = conn.createStatement();

if(statement != null){

ResultSet rs = statement.executeQuery("SELECT * FROM t_test");

while(rs.next()){

System.out.println("表字段name:"+rs.getString("name"));

}

}

System.out.println(String.format("time:%d,thread name:%s,thread id:%d,#############################################华丽的分割线",System.currentTimeMillis(),Thread.currentThread().getName(),Thread.currentThread().getId()));

if(statement != null){

ResultSet rs = statement.executeQuery("SELECT * FROM t_test");

while(rs.next()){

System.out.println("表字段name:"+rs.getString("name"));

}

}

System.out.println(String.format("time:%d,thread name:%s,thread id:%d,#############################################华丽的分割线",System.currentTimeMillis(),Thread.currentThread().getName(),Thread.currentThread().getId()));

PreparedStatement ps = conn.prepareStatement("update t_test set name=? where status=?");

if(ps != null){

ps.setString(1, "china");

ps.setInt(2, 1);

}

int n = ps.executeUpdate();

System.out.println(String.format("time:%d,thread name:%s,thread id:%d,updte row number:%d", System.currentTimeMillis(),Thread.currentThread().getName(),Thread.currentThread().getId(),n));

conn.commit();

}

} catch (ClassNotFoundException ex) {

Logger.getLogger(RunTestDB.class.getName()).log(Level.SEVERE, null, ex);

} catch (SQLException ex) {

Logger.getLogger(NewClassTest.class.getName()).log(Level.SEVERE, null, ex);

} finally {

try {

if(conn != null){

conn.rollback();

}

} catch (SQLException ex) {

Logger.getLogger(NewClassTest.class.getName()).log(Level.SEVERE, null, ex);

}

}

}

}

public class RunTestDB implements Runnable {

private Thread t;

public void setT(Thread t) {

this.t = t;

}

@Override

public void run() {

Connection conn = null;

try {

Class.forName("com.mysql.jdbc.Driver");

conn = DriverManager.getConnection("jdbc:mysql://localhost:3306/test", "root", "silence");

if(conn != null){

conn.setAutoCommit(false);

conn.setTransactionIsolation(Connection.TRANSACTION_REPEATABLE_READ);

Statement statement = conn.createStatement();

if(statement != null){

ResultSet rs = statement.executeQuery("SELECT * FROM t_test");

while(rs.next()){

System.out.println("表字段name:"+rs.getString("name"));

}

}

System.out.println(String.format("time:%d,thread name:%s,thread id:%d,#############################################华丽的分割线",System.currentTimeMillis(),Thread.currentThread().getName(),Thread.currentThread().getId()));

t.join(10000);

if(statement != null){

ResultSet rs = statement.executeQuery("SELECT * FROM t_test");

while(rs.next()){

System.out.println("表字段name:"+rs.getString("name"));

}

}

System.out.println(String.format("time:%d,thread name:%s,thread id:%d,#############################################华丽的分割线",System.currentTimeMillis(),Thread.currentThread().getName(),Thread.currentThread().getId()));

PreparedStatement ps = conn.prepareStatement("update t_test set name=? where status=?");

if(ps != null){

ps.setString(1, "english");

ps.setInt(2, 1);

}

int n = ps.executeUpdate();

conn.commit();

System.out.println(String.format("time:%d,thread name:%s,thread id:%d,updte row number:%d", System.currentTimeMillis(),Thread.currentThread().getName(),Thread.currentThread().getId(),n));

}

} catch (ClassNotFoundException ex) {

Logger.getLogger(RunTestDB.class.getName()).log(Level.SEVERE, null, ex);

} catch (InterruptedException ex) {

Logger.getLogger(RunTestDB.class.getName()).log(Level.SEVERE, null, ex);

} catch (SQLException ex) {

Logger.getLogger(NewClassTest.class.getName()).log(Level.SEVERE, null, ex);

} finally {

try {

if(conn != null){

conn.rollback();

}

} catch (SQLException ex) {

Logger.getLogger(NewClassTest.class.getName()).log(Level.SEVERE, null, ex);

}

}

}

public static void main(String[] args) {

RunTestDB r1 = new RunTestDB();

TestThread r2 = new TestThread();

Thread t1 = new Thread(r1);

Thread t2 = new Thread(r2);

r1.setT(t2);

r2.setT(t1);

t1.start();

t2.start();

}

运行结果：

表字段name:name1

表字段name:yyyy

time:1341903606280,thread name:Thread-0,thread id:9,#############################################华丽的分割线

表字段name:name1

表字段name:yyyy

time:1341903606294,thread name:Thread-1,thread id:10,#############################################华丽的分割线

表字段name:name1

表字段name:yyyy

time:1341903606296,thread name:Thread-1,thread id:10,#############################################华丽的分割线

time:1341903606308,thread name:Thread-1,thread id:10,updte row number:2

表字段name:name1

表字段name:yyyy

time:1341903606312,thread name:Thread-0,thread id:9,#############################################华丽的分割线

time:1341903606314,thread name:Thread-0,thread id:9,updte row number:2

上面例子可能要多运行几次才有我这样的结果