今天涛哥跟我说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;
已有数据
11hi
22dcdcdf
303dcdcdf2
335043494
3420dcdcdf473894739
58200dcdcdf
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。
死锁重现第一个事务执行
insert into test_dup(num,name) values(41,'no') on duplicate key update name='dcdcdf4738';
成功
第二个事务执行
insert into test_dup(num,name) values(40,'no') on duplicate key update name='dcdcdf4738';
事务陷入等待
第一个事务执行
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锁,死锁形成。
原因分析第二个事务在获取insert intention lock之前先获取了gap lock,导致第一个事务也不能获取insert intention lock。gap lock不是被第一个事务获取了么?是互斥锁呀?因为mysql的gap锁是兼容的,与互斥还是只读无关。所以第二个gap锁才能获取。
为什么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隔离级别下的数据不一致,所以加强了。好吧。