测试使用的隔离级别为Read Repeatable
建表和插入数据的SQL语句
create table test1(id int auto_increment primary key, no int, key(no));
create table test2(id int auto_increment primary key, no int, key(no));
insert into test1(no) values (1), (1), (1);
insert into test2(no) values (2), (2), (2);
情况一,直接上代码
事务1中执行的SQL语句(略去start transaction和commit,下同),先执行
update test1 set no = 4 where id = 1;
事务2
update test2 a set no = (select no from test1 where id = a.id) where exists (select no from test1 where id = a.id) and id = 2;
两个事务都正常执行。
情况二
事务1,先执行
update test1 set no = 4 where id = 2;
事务2