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

今天在测试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)

原文发布于微信公众号 - 杨建荣的学习笔记(jianrong-notes)

原文发表时间:2017-08-31

本文参与腾讯云自媒体分享计划,欢迎正在阅读的你也加入,一起分享。

发表于

我来说两句

0 条评论
登录 后参与评论

相关文章

来自专栏逸鹏说道

触发器在渗透中的利用

0x01 什么是触发器: 触发器对表进行插入、更新、删除的时候会自动执行的特殊存储过程。触发器一般用在check约束更加复杂的约束上面。触发器和普通的存储过程的...

3425
来自专栏杨建荣的学习笔记

awr中DB CPU过低的原因分析(r4笔记第20天)

前几天在做巡检的时候发现有个库的负载在某一个时间段内极高,高达100倍。一个10分钟的awr报告,得到的db time 却有1000分钟。 Snap Id ...

3309
来自专栏杨建荣的学习笔记

数据紧急修复之启用错误日志 (r2第12天)

昨晚对测试环境进行了升级,同步了部分生产的数据。整个过程比较顺利,但是在最后一步启用foreign key constraint的时候报了错误。 ora-022...

2969
来自专栏杨建荣的学习笔记

由drop datafile导致的oracle bug(r6笔记第56天)

今天碰到了一个dataguard在10gR2的bug,不管怎么样确实是在特定的时间做了特定的操作结果碰到了特定的问题。 这个问题是在10gR2的版本10.2.0...

2853
来自专栏Aloys的开发之路

Oracle系统表整理+常用SQL语句收集

-- DBA/ALL/USER/V_$/GV_$/SESSION/INDEX开头的绝大部分都是视图 -- DBA_TABLES意为DBA拥有的或可以访问的所有...

23410
来自专栏阿杜的世界

初始化数据库和导入数据

在Spring Boot应用的测试一文中,我们在StarterRunner类的run(...)方法中给数据库中添加一些初始数据。尽管通过编程方式添加初始数据比较...

1144
来自专栏杨建荣的学习笔记

MySQL中如何得到权限信息

最近在做一次MySQL数据迁移的时候,突然发现自己遗漏了一个地方,那就是权限信息没有导出,如果我们使用mysqldump --all-databases...

3075
来自专栏杨建荣的学习笔记

手工创建/删除数据库的步骤

今天和大家分享下数据库的创建和删除的步骤,里面有很多细节需要大家考虑。创建数据库不只是一个create database语句。删除数据库 drop databa...

3196
来自专栏乐沙弥的世界

SQL*PLus 帮助手册(SP2-0171)

    对于经常在SQL*Plus 下工作的大师们而言,总是时不时查询SQL*Plus的帮助命令。着实太多了,记不住。SQL*Plus下直接提供了help命令来...

2323
来自专栏数据和云

返璞归真:实例会话RAC条件下的笛卡尔积种参数可能性

? 杨廷琨(yangtingkun) 云和恩墨 CTO 高级咨询顾问,Oracle ACE 总监,ITPUB Oracle 数据库管理版版主 参数文件是Or...

28910

扫码关注云+社区