数据库死锁分析(行锁、间隙锁)
分享遇到过的一种间隙锁导致的死锁案例。文后有总结知识供参考
日志出现：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
