数据库死锁分析(行锁、间隙锁)
分享遇到过的一种间隙锁导致的死锁案例。文后有总结知识供参考
日志出现:Deadlock found when trying to get lock; try restarting transaction
导致原因:并发导致的数据库间隙锁死锁(MySql数据库默认RR级别)
业务主要操作提炼:首先进来将t1表原来的记录状态更新掉,然后插入新的记录。
线程1 |
线程2 |
...... |
update t1 set status =0 where rule=1 insert t1 () values (),(),(); |
update t1 set status =0 where rule=2 insert t1 () values (),(),(); |
...... |
复现问题:
测试数据准备
-- 准备测试表 CREATE TABLE IF NOT EXISTS `test` ( `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键', `col` int(11) NULL COMMENT '普通字段', `idx_col` int(11) NULL COMMENT '索引字段', `uni_col` tinyint(4) NULL COMMENT '唯一键字段', PRIMARY KEY (`id`), KEY `idx_1` (`idx_col`) USING BTREE, unique KEY `uni_idx_1` (`uni_col`) USING BTREE ) ENGINE=InnoDB AUTO_INCREMENT=0 ; |
-- 插入测试数据 INSERT INTO `test` ( `col`,`idx_col`,`uni_col`) VALUES (3,3,3), (10,10,10), (11,11,11), (20,20,20), (25,25,25), (26,26,26), (50,50,50) ; |
-- 清空数据表 DELETE FROM test ; DROP table test;
-- 查询测试表数据 SELECT * FROM test; |
验证间隙锁的存在:
事务1 |
事务2 |
-- 开始事务1 BEGIN; |
|
|
-- 开始事务2 BEGIN; |
-- 更新test,使用索引字段 UPDATE test set col=99 WHERE idx_col=20; |
|
|
-- 插入数据,因为有间隙锁,[11-25)这个区间全部被锁上了,插入被阻塞 INSERT INTO `test` ( `idx_col`) VALUES (11); INSERT INTO `test` ( `idx_col`) VALUES (12); INSERT INTO `test` ( `idx_col`) VALUES (20); INSERT INTO `test` ( `idx_col`) VALUES (24);
在间隙之外的可以顺利插入 INSERT INTO `test` ( `idx_col`) VALUES (10); |
由于间隙锁导致的死锁案例:(本次报错复现)
事务1 |
事务2 |
-- 开始事务1 BEGIN; |
|
|
-- 开始事务2 BEGIN; |
-- 更新test,使用索引字段,锁间隙[11,25) UPDATE test set col=99 WHERE idx_col=20; |
|
|
-- 更新test,使用索引字段,锁间隙[20,26) UPDATE test set col=99 WHERE idx_col=25; |
-- 使用了事务2的间隙锁,所以阻塞 |
|
|
-- 使用了事务1的间隙锁,阻塞,互相需要对方的锁,导致死锁 INSERT INTO `test` ( `idx_col`) VALUES (12);
-- 该事务被回滚,事务1提交成功。 |
where条件如果换成唯一键或者主键,没有间隙锁
事务1 |
事务2 |
-- 开始事务1 BEGIN; |
|
|
-- 开始事务2 BEGIN; |
-- 更新test,使用唯一键或主键无间隙锁 UPDATE test set col=99 WHERE
uni_col=20; |
|
|
-- 更新test,使用唯一键或主键无间隙锁 UPDATE test set col=99 WHERE uni_col=25; 或 |
-- 无间隙锁,顺利执行 或 |
|
|
-- 无间隙锁,顺利执行 INSERT INTO `test` ( `uni_col`) VALUES (12); 或 |
如果没有索引,导致全表锁
事务1 |
事务2 |
-- 开始事务1 BEGIN; |
|
|
-- 开始事务2 BEGIN; |
-- 更新test UPDATE test set col=99 WHERE col=20; |
|
|
-- 以下语句全部阻塞 或 |
结论:
对于update ,insert组合的这种业务操作,建议update操作使用主键或者唯一键作为where条件可以有效避免并发时候间隙锁的危害。
如果该字段不是主键或者唯一键,建议先查询,使用主键或唯一键进行更新。
或者修改数据库隔离级别为RC级别。
线程1 |
线程2 |
...... |
xx = select id from t1 where
rule=1 insert t1 () values (),(),(); |
xx = select id from t1 where
rule=1 insert t1 () values (),(),(); |
...... |
注意:以下写法无效
update t1 set status=0
WHERE id IN (
select id FROM
(SELECT id FROM t1 where rule=2) t
);
本文来自博客园,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/15993400.html
知识示例与参考如下:
在InnoDB中,主键可以被理解为聚簇索引,聚簇索引中的叶子结点就是相应的数据行,具有聚簇索引的表也被称为聚簇索引表,数据在存储的时候,是按照主键进行排序存储的。
我们都知道,数据库在select的时候,会选择索引列进行查找,索引列都是按照B+树(多叉搜索树)数据结构进行存储,找到主键之后,再回到聚簇索引表中进行查询,这叫回表查询。
id列是主键,RC或RR隔离级别 只有id=10记录上有行锁 |
![]()
|
id列是二级唯一索引,RC或RR隔离级别
|
![]()
|
id列是二级非唯一索引RC级别 |
![]()
|
id列是二级非唯一索引RR级别 在RR隔离级别下,为了防止幻读的发生,会使用Gap锁。 这里,你可以把Gap锁理解为,不允许在数据记录前面插入数据。首先,通过id索引定位到第一条满足查询条件的记录,加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回;然后读取下一条,重复进行。 直至进行到第一条不满足条件的记录[11,f],此时,不需要加记录X锁,但是仍旧需要加GAP锁, |
![]()
|
id上没有索引,RC级别
若id列上没有索引,SQL会走聚簇索引的全扫描进行过滤,由于过滤是由MySQL Server层面进行的。因此每条记录,无论是否满足条件,都会被加上X锁。 但是,为了效率考量,MySQL做了优化,对于不满足条件的记录,会在判断后放锁,最终持有的,是满足条件的记录上的锁,但是不满足条件的记录上的加锁/放锁动作不会省略。 同时,优化也违背了2PL的约束(同时加锁同时放锁) |
![]()
|
id上没有索引,RR隔离级别 聚簇索引上的所有记录,都被加上了X锁。其次,聚簇索引每条记录间的间隙(GAP),也同时被加上了GAP锁。 MySQL是做了相关的优化的,就是所谓的semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁,同时也不会添加Gap锁。 |
![]() |
实例:
在RR隔离级别下,针对一个复杂的SQL,首先需要提取其where条件。 Index Key确定的范围,需要加上GAP锁;Index Filter过滤条件,视MySQL版本是否支持ICP,若支持ICP(index condition pushdown ,mysql 5.6),则不满足Index Filter的记录,不加X锁,否则需要X锁; Table Filter过滤条件,无论是否满足,都需要加X锁。 |
|
本文来自博客园,作者:wanglifeng,转载请注明原文链接:https://www.cnblogs.com/wanglifeng717/p/15993400.html