数据库死锁分析(行锁、间隙锁)
分享遇到过的一种间隙锁导致的死锁案例。文后有总结知识供参考
日志出现：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);
INSERT INTO `test` ( `idx_col`) VALUES (25);
INSERT INTO `test` ( `idx_col`) VALUES (26);
由于间隙锁导致的死锁案例：(本次报错复现)
事务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的间隙锁，所以阻塞
INSERT INTO `test` ( `idx_col`) VALUES (21);
-- 使用了事务1的间隙锁，阻塞，互相需要对方的锁，导致死锁
-- Deadlock found when trying
to get lock; try restarting transaction
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;
或
UPDATE test set col=99 WHERE
uni_col=20 AND col=20;
-- 更新test，使用唯一键或主键无间隙锁
UPDATE test set col=99 WHERE
uni_col=25;
或
UPDATE test set col=99 WHERE uni_col=25 AND col=25;
-- 无间隙锁，顺利执行
INSERT INTO `test` ( `uni_col`) VALUES (21);
或
INSERT INTO `test` ( `uni_col`,`col`) VALUES (21,21);
-- 无间隙锁，顺利执行
INSERT INTO `test` ( `uni_col`)
VALUES (12);
或
INSERT INTO `test` ( `uni_col`,`col`) VALUES (12,12);
如果没有索引，导致全表锁
事务1
事务2
-- 开始事务1
BEGIN;
-- 开始事务2
BEGIN;
-- 更新test
UPDATE test set col=99
WHERE col=20;
-- 以下语句全部阻塞
UPDATE test set col=99 WHERE col=25;
或
INSERT INTO `test` ( `uni_col`,`col`) VALUES (100,100);
结论：
对于update ,insert组合的这种业务操作，建议update操作使用主键或者唯一键作为where条件可以有效避免并发时候间隙锁的危害。
如果该字段不是主键或者唯一键，建议先查询，使用主键或唯一键进行更新。
或者修改数据库隔离级别为RC级别。
线程1
线程2
......
xx = select id from t1 where
rule=1
update t1 set status =0 where id in(xx)
insert t1 () values (),(),();
xx = select id from t1 where
rule=1
update t1 set status =0 where id in(xx)
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锁。
实例：
delete from t1 where pubtime>1 and pubtime<20 and userid="hdc" and
comment is not null
在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
以上是 数据库死锁分析(行锁、间隙锁) 的全部内容， 来源链接： utcz.com/z/536341.html
