MySQL在RR隔离级别下的unique失效和死锁模拟

发布时间:2020-08-10 09:53:44 作者:jeanron100
来源:ITPUB博客 阅读:215

今天在测试MySQL事务隔离级别的时候,发现了一个有趣的问题,也参考了杨一之前总结的一篇。http://blog.itpub.net/22664653/viewspace-1612574/

    问题的背景是在MySQL隔离级别为RR(Repeatable Read)时,唯一性约束没有失效,多并发的场景下能够复现出下面的问题。

  这样一个看起来不可能的事情,能否复现呢。

MySQL在RR隔离级别下的unique失效和死锁模拟

  我都这么问了,潜台词就是可以,要不今天的笔记就一个问题就结束了。

  为了模拟这个问题,我们打开两个会话窗口,来模拟一下这个问题。

mysql> create table test3(id1 int primary key,id2 int unique,id3 int);
Query OK, 0 rows affected (0.01 sec)

#会话1
set autocommit=0;
mysql> insert into test3 values(1,20170831,1);
Query OK, 1 row affected (0.00 sec)
commit;


#会话2

这个时候充分利用了MVCC的特性,这是一个快照读。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   1 | 20170831 |    1 |
+-----+----------+------+
1 row in set (0.00 sec)
会话1插入了一条数据,我们在会话2中删除。
mysql> delete from test3 where id1=1;
Query OK, 1 row affected (0.01 sec)
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
提交之后,会话2中就修改完毕了。

#会话1

这个时候根据MVCC的特点,会话2中已经删除了id1=1的记录。所以主键列相关数据是插入不了了,那么唯一性索引呢。根据MVCC的特点,能够保证重复读的特点,读到的数据还是不变。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   1 | 20170831 |    1 |
+-----+----------+------+
1 row in set (0.00 sec)

现在的关键就来了,我们插入一条数据,主键不冲突,唯一性索引冲突,看看是否能够插入成功。

mysql> insert into test3 values(2,20170831,2);
Query OK, 1 row affected (0.00 sec)

魔性的一幕上演了。

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   1 | 20170831 |    1 |
|   2 | 20170831 |    2 |
+-----+----------+------+
2 rows in set (0.00 sec)

当然到了这里,我们继续玩一玩,常规来说,插入主键列冲突数据可能是行不通的,比如id1=1,id2=20170831,id3=1,客户端很快会反馈失败。但是在这里做唯一性校验时,因为id1=1的数据已经被物理删除了。

mysql>  insert into test3 values(1,20170831,1);
ERROR 1062 (23000): Duplicate entry '20170831' for key 'id2'


我们就来继续模拟个死锁吧。

会话2:

这个步骤是做一次数据清理,where条件中是根据主键来查找删除。

mysql> delete from test3 where id1=1;
Query OK, 0 rows affected (0.00 sec)


会话1:

mysql> insert into test3 values(1,20170831,1);

这个时候会话会被阻塞


会话2:

这个时候在会话2继续插入这个值,就会报出死锁问题。

mysql> insert into test3 values(1,20170831,1);
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

--产生死锁


会话1:

这个时候死锁有了,事务也自动回滚了。再次插入违反约束的数据,就不行了。

mysql> insert into test3 values(1,20170831,1);
ERROR 1062 (23000): Duplicate entry '20170831' for key 'id2'


我们来看看在上面的测试过程中,关于死锁的日志:
2017-08-28T07:27:48.329631Z 14140 [Note] InnoDB: Transactions deadlock detected, dumping detailed information.
2017-08-28T07:27:48.329740Z 14140 [Note] InnoDB:
*** (1) TRANSACTION:

TRANSACTION 31790, ACTIVE 315 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 1136, 5 row lock(s), undo log entries 1
MySQL thread id 14138, OS thread handle 139809903986432, query id 108686 localhost root update
insert into test3 values(1,20170831,1)
2017-08-28T07:27:48.329801Z 14140 [Note] InnoDB: *** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 36 page no 3 n bits 72 index PRIMARY of table `test`.`test3` trx id 31790 lock mode S waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000007c2f; asc     |/;;
 2: len 7; hex 33000001ac2f63; asc 3    /c;;
 3: len 4; hex 8133c84f; asc  3 O;;
 4: len 4; hex 80000001; asc     ;;

2017-08-28T07:27:48.330040Z 14140 [Note] InnoDB: *** (2) TRANSACTION:

TRANSACTION 31791, ACTIVE 51 sec inserting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 1
MySQL thread id 14140, OS thread handle 139809903720192, query id 108687 localhost root update
insert into test3 values(1,20170831,1)
2017-08-28T07:27:48.330084Z 14140 [Note] InnoDB: *** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 36 page no 3 n bits 72 index PRIMARY of table `test`.`test3` trx id 31791 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 80000001; asc     ;;
 1: len 6; hex 000000007c2f; asc     |/;;
 2: len 7; hex 33000001ac2f63; asc 3    /c;;
 3: len 4; hex 8133c84f; asc  3 O;;
 4: len 4; hex 80000001; asc     ;;

2017-08-28T07:27:48.330342Z 14140 [Note] InnoDB: *** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 36 page no 4 n bits 72 index id2 of table `test`.`test3` trx id 31791 lock mode S waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 4; hex 8133c84f; asc  3 O;;
 1: len 4; hex 80000002; asc     ;;
2017-08-28T07:27:48.330470Z 14140 [Note] InnoDB: *** WE ROLL BACK TRANSACTION (2)

这里会充分把x,s锁,细粒度锁的知识联系起来,搞明白又进步了一大截。


会话1:

最后,我们提交一下事务,再次查看数据,一切又恢复了平静。

mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select *from test3;
+-----+----------+------+
| id1 | id2      | id3  |
+-----+----------+------+
|   2 | 20170831 |    2 |
+-----+----------+------+
1 row in set (0.00 sec)





推荐阅读:
  1. MySQL的在RC和RR模式下的锁
  2. RR隔离级别的查询的幻读场景

免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。

mysql rr unique

上一篇:VUE中V-IF条件判断改变元素样式的方法有哪些

下一篇:如何查找php替换字符串

相关阅读

您好,登录后才能下订单哦!

密码登录
登录注册
其他方式登录
点击 登录注册 即表示同意《亿速云用户服务条款》