分享因遇到缝隙锁而导致的死锁案例。文章最后有一个知识总结，以供参考。
[En]
Share a deadlock case caused by a gap lock encountered. There is a summary of knowledge at the end of the article for reference.
日志出现：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);
缝隙外的那些可以顺利地插入。
[En]
Those outside the gap can be inserted smoothly.
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);
如果没有索引，将导致完全表锁。
[En]
If there is no index, it will result in a full table lock.
事务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条件可以有效避免并发时候间隙锁的危害。
如果字段不是主键或唯一键，建议先查询，然后用主键或唯一键更新。
[En]
If the field is not a primary key or unique key, it is recommended to query it first and update it with a primary key or unique key.
或者修改数据库隔离级别为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
Original: https://www.cnblogs.com/wanglifeng717/p/15993400.html
Author: 王李峰
Title: 数据库死锁分析(行锁、间隙锁)
原创文章受到原创版权保护。转载请注明出处：https://www.johngo689.com/505582/
转载文章受原作者版权保护。转载请注明原作者出处！