首页
学习
活动
专区
工具
TVP
发布
社区首页 >专栏 >MySQL在RR隔离级别下的unique失效和死锁模拟

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

作者头像
jeanron100
发布2018-03-21 17:30:47
1.4K0
发布2018-03-21 17:30:47
举报

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

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

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

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

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

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)

本文参与 腾讯云自媒体分享计划,分享自微信公众号。
原始发表:2017-08-31,如有侵权请联系 cloudcommunity@tencent.com 删除

本文分享自 杨建荣的学习笔记 微信公众号,前往查看

如有侵权,请联系 cloudcommunity@tencent.com 删除。

本文参与 腾讯云自媒体分享计划  ,欢迎热爱写作的你一起参与!

评论
登录后参与评论
0 条评论
热度
最新
推荐阅读
相关产品与服务
云数据库 SQL Server
腾讯云数据库 SQL Server (TencentDB for SQL Server)是业界最常用的商用数据库之一,对基于 Windows 架构的应用程序具有完美的支持。TencentDB for SQL Server 拥有微软正版授权,可持续为用户提供最新的功能,避免未授权使用软件的风险。具有即开即用、稳定可靠、安全运行、弹性扩缩等特点。
领券
问题归档专栏文章快讯文章归档关键词归档开发者手册归档开发者手册 Section 归档