在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
上面例子可能要多运行几次才有我这样的结果