一条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隔离级别下的数据不一致,所以加强了。好吧。

编辑于 2017-09-15

文章被以下专栏收录