一条Insert on duplicate引发的血案

今天涛哥跟我说mysql死锁了，问我怎么回事，我对于mysql的自我感觉一直很良好，觉得不会有啥大问题，结果真的把我难住了。

表

CREATE TABLE `test_dup` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`num` int(10) unsigned DEFAULT NULL,

`name` varchar(45) DEFAULT NULL,

PRIMARY KEY (`id`),

UNIQUE KEY `num_index` (`num`)

) ENGINE=InnoDB AUTO_INCREMENT=138 DEFAULT CHARSET=utf8;

已有数据

1 1 hi

2 2 dcdcdf

30 3 dcdcdf2

33 50 43494

34 20 dcdcdf473894739

58 200 dcdcdf

mysql的版本和隔离级别

Repeatable read

mysql 5.7.17.

涛哥的操作

多个线程，每个线程执行许多这样的操作，但是保证每个线程执行的num是绝对不一样的：

insert into test_dup(num,name) values(38,'no') on duplicate key update name='dcdcdf4738';

事务大小设置为100。

死锁日志

LATEST DETECTED DEADLOCK

------------------------

2017-09-14 22:35:44 0x7f8f447c6700

*** (1) TRANSACTION:

TRANSACTION 6559008, ACTIVE 7 sec inserting

mysql tables in use 1, locked 1

LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1

MySQL thread id 67521, OS thread handle 140253305255680, query id 16260084 10.47.54.38 sync_master update

insert into test_dup(num,name) values(39,'no') on duplicate key update name='dcdcdf4738'

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2392 page no 4 n bits 80 index num_index of table `idoo`.`test_dup` trx id 6559008 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 00000028; asc (;;

1: len 4; hex 8000008a; asc ;;

*** (2) TRANSACTION:

TRANSACTION 6558977, ACTIVE 21 sec inserting

mysql tables in use 1, locked 1

4 lock struct(s), heap size 1136, 4 row lock(s), undo log entries 2

MySQL thread id 67531, OS thread handle 140253306054400, query id 16260339 10.47.54.38 sync_master update

insert into test_dup(num,name) values(38,'no') on duplicate key update name='dcdcdf4738'

*** (2) HOLDS THE LOCK(S):

RECORD LOCKS space id 2392 page no 4 n bits 80 index num_index of table `idoo`.`test_dup` trx id 6558977 lock_mode X locks gap before rec

Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 00000032; asc 2;;

1: len 4; hex 80000021; asc !;;

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 00000028; asc (;;

1: len 4; hex 8000008a; asc ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:

RECORD LOCKS space id 2392 page no 4 n bits 80 index num_index of table `idoo`.`test_dup` trx id 6558977 lock_mode X locks gap before rec insert intention waiting

Record lock, heap no 10 PHYSICAL RECORD: n_fields 2; compact format; info bits 0

0: len 4; hex 00000028; asc (;;

1: len 4; hex 8000008a; asc ;;

从日志上来看似乎是两个事务各自在等待对方的gap lock。

死锁重现

1. 第一个事务执行
   insert into test_dup(num,name) values(41,'no') on duplicate key update name='dcdcdf4738';
   成功
2. 第二个事务执行
   insert into test_dup(num,name) values(40,'no') on duplicate key update name='dcdcdf4738';
   事务陷入等待
3. 第一个事务执行
   insert into test_dup(num,name) values(38,'no') on duplicate key update name='dcdcdf4738';
   显示死锁。

mysql可以打开锁统计，通过以下语句打开mysql的锁的统计

set GLOBAL innodb_status_output_locks=ON;

set GLOBAL innodb_status_output=ON;

在第二步的时候我们运行show engine innodb status;查看锁的情况发现：

事务1持有：IX锁（表锁），gap x锁（在num_index上num=50之前的gap），gap x锁（在num_index上num=41之前的gap），num_index上的record lock（num=41），

事务2持有：IX锁（表锁），gap x锁（在num_index上num=41之前的gap），insert intention lock（在等待事务1的第二个gap锁）。

如果这时候第三步执行，那么事务1的insert intention也会等待事务2的gap锁，死锁形成。

原因分析

1. 第二个事务在获取insert intention lock之前先获取了gap lock，导致第一个事务也不能获取insert intention lock。gap lock不是被第一个事务获取了么？是互斥锁呀？因为mysql的gap锁是兼容的，与互斥还是只读无关。所以第二个gap锁才能获取。
2. 为什么insert intention lock的获取在gap lock获取之后，导致悲剧的发生。如果intention insert lock在之前那么就不会有死锁了。这可能是mysql5.7的一个bug吧.

解决方案

• 单线程执行数据库写入
• 减小事务的大小
• 修改事务隔离级别为read committed，read committed隔离级别锁的粒度是index lock。只有在foreign-key constraint checking 和duplicate-key checking.的时候才会使用gap lock。
  这里什么叫duplicate-key checking.的时候才会使用gap lock？
• 举个例子，update test_dup set num=1 where num=50;会引起Duplicate entry '1' for key 'num_index'。此时查看此事务的加锁。index lock(num=50)，index lock（num=1）, s next key lock(num=1)。此时执行一个插入num=0的事务一定会阻塞。
• 不要使用 insert on duplicate，使用普通的insert。
  insert会在num_index和pk中加record x locks，而不是gap lock或者next key lock，所以不会有死锁。
• 尽量减少在数据库中使用unique index和foreign key
  因为unique key 和foreign key会引起额外的index检查，需要更大的开销。
• mysql版本改为5.6
  这里要特别说一下，mysql5.6是没有这个问题的，insert into test_dup(num,name) values(41,'no') on duplicate key update name='dcdcdf4738'; 会在最终的num的index上加index lock(num=41)，所以不会有死锁。

思考

为什么mysql对于5.6和5.7中的insert on duplicate的加锁处理会变化如此大？其实如此大的变更我们可以直接去看mysql的release note。在5.7的release note中我们发现了这样一条。

INSERT INTO ... SELECT ... ON DUPLICATE KEY UPDATE and LOAD DATA CONCURRENT REPLACE took too weak a lock, leading to the possibility of concurrent SELECT statements returning inconsistent results. (Bug #38046, Bug #11749055)

大意就是原来加锁太弱了，会引起RR隔离级别下的数据不一致，所以加强了。好吧。