最近做了个记录用户行为操作记录的功能,并要最终展示处理,出于各种原因,将数据库表设计了个唯一索引,包含三个字段,start_id,type,time,其中start_id唯一标识一个用户进入app的一次会话,type表示其具体的操作行为,time即时间戳。
Create Table: CREATE TABLE `footrecord` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT 'id',
`start_id` varchar(50) NOT NULL COMMENT '会话id',
`type` varchar(20) NOT NULL COMMENT '操作类型',
`add_time` int(11) NOT NULL COMMENT '记录时间',
PRIMARY KEY (`id`),
UNIQUE KEY `uk_start_type_atime` (`start_id`,`type`,`add_time`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT=''
其实吧,数据记录重复也就重复了,业务上当然是能够有手段去重处理的,只是会麻烦一些,但是当时就为了省点事,希望能够利用MySQL的唯一索引的性质,尽量帮我减少些业务逻辑上的负担。当然,这是后话了
起初,业务上的逻辑是这样婶的,数据过来,噼里啪啦一顿解密过滤格式化成为可直接插入数据库中的数据;开启事务;插入数据;提交事务就大功告成了。
乍这么一看好像没啥不对,但是呢,我上面为自己图方便的索引就按耐不住了,错误日志一直在报duplicate key exist,唯一索引已经存在导致插入失败了。经过一番搜索,发现了三个解决唯一索引插入有冲突失败的方法。具体使用方法和作用不再赘述
- insert ignore into table_name(a,b,c) values(1,2,3);
- replace into `table_name` SET `col_name`='value',
- insert into `table_name` set `col_name`=`value` onduplicate key on update `col_name`=`value`
一开始,我采用了方法三(还特意修改了底层数据库操作类的update方法),去update了一个无关紧要的字段,主要是为了防止MySQL报duplicate错误。然鹅,新的问题出现了,一天七十多万请求,大概有10个死锁错误。当然,业务上的数据是正常的,毕竟死锁导致的被回退的事务想插入的数据 和 提交的事务插入的数据是一样的,但问题还是得解决啊。
当时我的想法是这样的,假设会话1是先请求进来的会话,会话2是后一个会话。由于数据的顺序是一致的,比如依次有a、b、c三行记录,会话1插入a记录,然后插入b记录;会话2应该先要插入a记录,但它发现拿不到锁插入不了,(其实会话2select不到这行记录,但是它就是无法插入,因为这里出现了大名鼎鼎的【幻读】),既然会话2插入不了a记录,也就不应该会执行到插入c记录去,会话2不会拿到会话1将要插入的行记录的锁,它应该会一直等待会话1释放a记录的锁才对。我苦思冥想百思不得其解,死锁到底是怎么产生的呢???
于是乎请教我们的dba,我们优秀的dba通过死锁日志的信息以及自己的测试,最终给我复现了这个死锁,复现出来就基本能够知道其原因了。
简化复现以及测试步骤:
mysql 5.6.26 RR
CREATE TABLE `insert_DUPLICATE_DEADLOCK` (
`id` int NOT NULL AUTO_INCREMENT COMMENT 'id',
`c1` int NOT NULL,
`c2` int NOT NULL,
`c3` int NOT NULL,
`c4` int NOT NULL DEFAULT 0,
PRIMARY KEY (`id`),
UNIQUE KEY `uniq_c123` (`c1`,`c2`,`c3`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='INSERT xx ON DUPLICATE KEY UPDATE xx DEADLOCK';
truncate table insert_DUPLICATE_DEADLOCK;
trx 1 语句一
insert into insert_DUPLICATE_DEADLOCK(c1,c2,c3) values (2,3,4) ON DUPLICATE KEY UPDATE c4=1;
锁:lock_mode X locks rec but not gap
trx 2 语句一
insert into insert_DUPLICATE_DEADLOCK(c1,c2,c3) values (2,3,4) ON DUPLICATE KEY UPDATE c4=1;
锁:lock_mode X waiting
状态:等待中(出现幻读,查不到这条记录,但也无法插入这条记录)
trx 1 语句二
insert into insert_DUPLICATE_DEADLOCK(c1,c2,c3) values (2,2,5) ON DUPLICATE KEY UPDATE c4=1;
锁:lock_mode X locks gap before rec insert intention waiting
trx 2 报死锁回退
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
测试结果
如果trx1 语句二执行在唯一索引(-∞,(2,3,4))区间的都会报死锁,如(2,2,5),(1,1,1)等,
如果trx1 语句二执行在唯一索引((2,3,4),+∞)区间的都会直接成功,不会出现死锁,如(2,3,5)等
要搞懂这个问题,我们得先明白MySQL对于insert操作的加锁机制(RR级别):
-
找到满足条件的记录,并且记录有效,则对记录加X锁,No Gap锁(lock_mode X locks rec but not gap);
-
找到满足条件的记录,但是记录无效(标识为新增或删除的记录),则对记录加next key锁(同时锁住记录本身,以及记录之前的Gap:lock_mode X);
-
未找到满足条件的记录,则对第一个不满足条件的记录加Gap锁,保证没有满足条件的记录插入(locks gap before rec),若有唯一索引则退化为lock_mode X locks rec but not gap
如果并发事务插入的记录不产生duplicate key冲突就不会有问题,只是获取占用了record lock,但发生dulicate key冲突时(机制2)则对记录加next key锁(同时锁住记录本身,以及记录之前的Gap:lock_mode X)
现在回顾一下上面的测试过程,
- trx 1语句一 lock_mode X locks rec but not gap这里是唯一索引没有产生duplicate key冲突,只占用了排它record锁
- trx 2语句一 虽然一模一样的语句,但此时产生了duplicate key冲突,先加记录之前的gap lock,再等待trx 1释放X record lock(next key锁即为 gap锁+record锁)
- trx 1语句二 希望往 trx 2语句一中已加锁的gap区间插入数据,获取X lock失败,产生死锁
首先,trx2语句一 等待trx1语句一 释放锁lock x锁,trx1语句二 等待trx2语句一 释放lock gap lock x,形成相互等待故trx 2回退
后来,我了解到ignore语句更适合我这个场景,我只需要保证正常的数据记录上了就够了,于是又调整为ignore语句测试。然鹅死锁依然存在,唯一的不同仅仅是ignore语句在trx 2语句一 加的gap lock不是X锁而是S锁,record S锁等待获取
trx 1 语句一
insert ignore into insert_DUPLICATE_DEADLOCK(c1,c2,c3) values (2,3,4);
锁:lock_mode X locks rec but not gap
trx 2 语句一
insert ignore into insert_DUPLICATE_DEADLOCK(c1,c2,c3) values (2,3,4);
锁:lock_mode S waiting
状态:等待中
trx 1 语句二
insert ignore into insert_DUPLICATE_DEADLOCK(c1,c2,c3) values (2,2,5);
锁:lock_mode X locks gap before rec insert intention waiting
经过测试,trx 1语句二在插入小于2,3,4的记录时才会失败,大于2,3,4的数据记录能正常插入成功,这也与上述说明(对记录之前加gap lock)吻合
总结
对于RC/RR模式下 ,insert 遇到唯一键冲突的时候的死锁不可避免。需要开发在设计表结构的时候 减少unique 索引设计。
解决方法
这里提供几个参考的解决方法,根据业务场景酌情考虑使用
- 及时提交事务,在trx 1语句一执行完毕后立马提交事务,后面的事务自然不会再获取gap lock 并等待record lock释放了。
- 入库前按照唯一索引顺序先行排序,如c1,c2,c3先在业务上进行一次排序再入库,保证trx 1后面的语句一定是大于前面已执行的语句。
由于这里业务上对数据并不是严格要求数据一致的,不开启事务,保证正常数据入库即可,笔者已采用方法1解决了此死锁问题,特此记录,仅供参考。