1、环境说明

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

CREATE TABLE `t0` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) NOT NULL DEFAULT '0',
  `d` int(11) NOT NULL DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `c` (`c`,`d`)
) ENGINE=InnoDB AUTO_INCREMENT=9 DEFAULT CHARSET=utf8

mysql> select *from t0;
+----+----+---+
| id | c  | d |
+----+----+---+
|  1 |  1 | 0 |
|  2 |  3 | 0 |
|  3 |  5 | 0 |
|  4 |  7 | 0 |
|  5 | 10 | 0 |
|  6 | 12 | 0 |
|  7 | 14 | 0 |
|  8 | 16 | 0 |
+----+----+---+
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、死锁日志

------------------------
LATEST DETECTED DEADLOCK
------------------------
2018-06-30 18:50:57 a3445b90
*** (1) TRANSACTION:
TRANSACTION 7486, ACTIVE 21 sec updating or deleting
mysql tables in use 1, locked 1
LOCK WAIT 5 lock struct(s), heap size 320, 7 row lock(s), undo log entries 1
MySQL thread id 7, OS thread handle 0xa3414b90, query id 183 localhost root updating
update t0 set d=1 where c in(5,10)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000004; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 7485, ACTIVE 59 sec updating or deleting
mysql tables in use 1, locked 1
5 lock struct(s), heap size 320, 5 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 0xa3445b90, query id 184 localhost root updating
update t0 set d=1 where c=7
*** (2) HOLDS THE LOCK(S):
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
Record lock, heap no 5 PHYSICAL RECORD: n_fields 3; compact format; info bits 32
 0: len 4; hex 80000007; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000005; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
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
Record lock, heap no 6 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 4; hex 80000000; asc     ;;
 2: len 4; hex 80000005; asc     ;;

*** 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