ITPub博客
首页 博客 专栏 学院 社区 IT新媒体评选
发博

首页 > 数据库 > MySQL > 死锁案例一

死锁案例一

原创 MySQL 作者：yzs87 时间：2018-07-01 16:25:49 0 删除 编辑

1、环境说明

MySQL5.6.33，隔离级别是RR。表结构及数据：

点击(此处)折叠或打开

  1. CREATE TABLE `t0` (
  2.   `id` int(11) NOT NULL AUTO_INCREMENT,
  3.   `c` int(11) NOT NULL DEFAULT '0',
  4.   `d` int(11) NOT NULL DEFAULT '0',
  5.   PRIMARY KEY (`id`),
  6.   KEY `c` (`c`,`d`)
  7. ) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

点击(此处)折叠或打开

  1. mysql> select *from t0;
  2. +----+----+---+
  3. | id | c | d |
  4. +----+----+---+
  5. | 1 | 1 | 0 |
  6. | 2 | 3 | 0 |
  7. | 3 | 5 | 0 |
  8. | 4 | 7 | 0 |
  9. | 5 | 10 | 0 |
  10. | 6 | 12 | 0 |
  11. | 7 | 14 | 0 |
  12. | 8 | 16 | 0 |
  13. +----+----+---+
  14. 8 rows in set (0.00 sec)

2、测试用例

会话1   
 会话2
begin; begin

update t0 set d=1 where c=6;
update t0 set d=1 where c in(5,10);

update t0 set d=1 where c=7;

deadlock

3、死锁日志

点击(此处)折叠或打开

  1. ------------------------
  2. LATEST DETECTED DEADLOCK
  3. ------------------------
  4. 2018-06-30 18:50:57 a3445b90
  5. *** (1) TRANSACTION:
  6. TRANSACTION 7486, ACTIVE 21 sec updating or deleting
  7. mysql tables in use 1, locked 1
  8. LOCK WAIT 5 lock struct(s), heap size 320, 7 row lock(s), undo log entries 1
  9. MySQL thread id 7, OS thread handle 0xa3414b90, query id 183 localhost root updating
  10. update t0 set d=1 where c in(5,10)
  11. *** (1) WAITING FOR THIS LOCK TO BE GRANTED:
  12. RECORD LOCKS space id 16 page no 4 n bits 80 index `c` of table `yzs`.`t0` trx id 7486 lock_mode X locks gap before rec insert intention waiting
  13. Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
  14.  0: len 4; hex 80000007; asc ;;
  15.  1: len 4; hex 80000000; asc ;;
  16.  2: len 4; hex 80000004; asc ;;
  17.  
  18. *** (2) TRANSACTION:
  19. TRANSACTION 7485, ACTIVE 59 sec updating or deleting
  20. mysql tables in use 1, locked 1
  21. 5 lock struct(s), heap size 320, 5 row lock(s), undo log entries 1
  22. MySQL thread id 6, OS thread handle 0xa3445b90, query id 184 localhost root updating
  23. update t0 set d=1 where c=7
  24. *** (2) HOLDS THE LOCK(S):
  25. RECORD LOCKS space id 16 page no 4 n bits 80 index `c` of table `yzs`.`t0` trx id 7485 lock_mode X locks gap before rec
  26. Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
  27.  0: len 4; hex 80000007; asc ;;
  28.  1: len 4; hex 80000000; asc ;;
  29.  2: len 4; hex 80000004; asc ;;
  30.  
  31. Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  32.  0: len 4; hex 8000000a; asc ;;
  33.  1: len 4; hex 80000000; asc ;;
  34.  2: len 4; hex 80000005; asc ;;
  35.  
  36. *** (2) WAITING FOR THIS LOCK TO BE GRANTED:
  37. RECORD LOCKS space id 16 page no 4 n bits 80 index `c` of table `yzs`.`t0` trx id 7485 lock_mode X locks gap before rec insert intention waiting
  38. Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
  39.  0: len 4; hex 8000000a; asc ;;
  40.  1: len 4; hex 80000000; asc ;;
  41.  2: len 4; hex 80000005; asc ;;
  42.  
  43. *** WE ROLL BACK TRANSACTION (2)

4、分析死锁日志

TRANSACTION 7485事务：

    对二级索引（7,0,4）获取X类型的gap锁；

    对二级索引（10,0,5）获取X类型的gap锁

    等待X类型的插入意向锁，对(10,0,5)

TRANSACTION 7486事务：

    等待X类型的插入意向锁，对（7,0,4）

5、加锁原理


6、解析

1）会话2事务执行update t0 set d=1 where c=6;首先进入search阶段，row_search_for_mysql调用btr_pcur_open_with_no_init从二级索引中搜索c=6；(搜索模式是>=6)。搜索出的返回结果是(7,0,4)，不等于6，进入判断条件事务的隔离级别是RR并且没有设置innodb_locks_unsafe_for_binlog，所以调用err = sel_set_rec_lock(btr_pcur_get_block(pcur),rec, index, offsets,prebuilt->select_lock_type, LOCK_GAP,thr);对二级索引(7,0,4)加LOCK_GAP锁。因为没有找到对应的二级索引值，所以没有进入update阶段就返回了。

2）会话1事务执行update t0 set d=1 where c in(5,10);

      首先，进入search阶段，二级索引搜索5。row_search_for_mysql调用btr_pcur_open_with_no_init从二级索引中搜索c=5；(搜索模式>=5)，搜索出的结果集是(5,0,3),等于5，进入右边流程，锁类型是 lock_type = LOCK_ORDINARY;，调用err = sel_set_rec_lock(btr_pcur_get_block(pcur),rec, index, offsets,prebuilt->select_lock_type,lock_type, thr);对二级索引记录(5,0,3)加LOCK_ORDINARY锁。然后重新调用row_search_for_mysql->btr_pcur_move_to_next(pcur, &mtr)检索next record,next record是（7,0,4），不等于5，对(7,0,4)加 LOCK_GAP锁。

      其次，search阶段，二级索引继续搜10。同理对(10,0,5)加LOCK_ORDINARY锁，对(12,0,6)加LOCK_GAP锁。

    然后，search阶段，搜索聚集索引3，即二级索引记录(5,0,3)解析出的聚集索引。row_search_for_mysql调用调用btr_pcur_open_with_no_init搜索返回的结果集是(3,5,0),等于3，所以锁类型lock_type改为LOCK_REC_NOT_GAP，调用err = sel_set_rec_lock(btr_pcur_get_block(pcur),rec, index, offsets,prebuilt->select_lock_type,lock_type, thr);对聚集索引记录(3,5,0)加LOCK_REC_NOT_GAP锁。（注：后面因为会本地更新，所以加锁的聚集索引记录是(3,5,1)）

     进入update阶段。调用函数row_update_for_mysql->row_upd_step->row_upd->row_upd_clust_step

                                                                                                                                ->row_upd_sec_step

先对聚集索引记录进行处理，然后对二级索引处理。row_upd_clust_step调用流程一直向右流动，最终调用row_upd_clust_rec->btr_cur_optimistic_update->btr_cur_update_in_place对聚集索引就本地更新，不会再加锁（search阶段以及对其加了锁了）。然后对二级索引处理，调用row_upd_sec_step->row_upd_sec_index_entry->btr_cur_del_mark_set_sec_rec->lock_sec_rec_modify_check_and_lock->err = lock_rec_lock(TRUE, LOCK_X | LOCK_REC_NOT_GAP,block, heap_no, index, thr); 对二级索引记录（5,0,3）在删除前先加X类型的LOCK_REC_NOT_GAP锁,判断已经对其加了更强的锁next-key锁，所以就不用加了；然后接着执行->row_ins_sec_index_entry->row_ins_sec_index_entry_low-> btr_cur_optimistic_insert->btr_cur_ins_lock_and_undo判断加插入意向锁在(7,0,4)。（因为会话2已经在(7,0,4)加上了gap锁，所以这里需要申请插入意向锁），插入意向锁和gap锁互斥，所以会等待申请该插入意向锁。

    同理，search阶段搜索聚集索引记录5，并update阶段处理二级索引。search阶段对聚集索引(5,10,0)即(5,10,1)加LOCK_REC_NOT_GAP锁，对二级索引记录(10,0,5)不用再加锁。插入(10,1,5)，没有gap锁限制，插入成功。

3）会话1事务继续执行update t0 set d=1 where c=7;

      同理，对二级索引记录(7,0,4)加next key锁，对(10,0,5)加gap锁；对聚集索引记录(4,7,0)加LOCK_REC_NOT_GAP锁；然后插入二级索引记录(7,1,4)时，对(10,0,5)申请插入意向锁，因为会话2对(10,0,5)已结加了next key锁，所以进入锁等待。

4）此时已经分析出，会话1等待申请(10,0,5)的插入意向锁，拥有（7,0,4）的gap锁；会话2拥有(10,0,5)的next key锁，等待申请(7,0,4)的插入意向锁，发生死锁。

7、解决方法

解决方法比较简单，把组合索引(c,d)中的d去掉，改为只对c建立索引，即不更改二级索引记录来避免gap/next-key锁阻塞二级索引记录上的插入意向锁。

8、参考

 https://mp.weixin.qq.com/s/b9gNbdEHV3NNQrV9PKDPSw




来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/31493717/viewspace-2157163/ ，如需转载，请注明出处，否则将追究法律责任。

1
0
上一篇： InnoDB purge原理--哪些undo log可purge
下一篇： 死锁案例二
请登录后发表评论 登录
全部评论
yzs87
专家姓名：闫宗帅
专注于MySQL、Aerospike、PostgreSQL数据库

注册时间：2017-10-03

  • 博文量
    149
  • 访问量
    285489

最新文章

支持我们 作者招募 用户协议 FAQ Contact Us

北京盛拓优讯信息技术有限公司. 版权所有  京ICP备16024965号-8 北京市公安局海淀分局网监中心备案编号：11010802021510 niuxiaotong@pcpop.com 17352615567 未成年人举报专区

广播电视节目制作经营许可证(京) 字第1234号 中国互联网协会会员

违法和不良信息举报电话:18101376593