1. 代码100分首页
  2. 代码基础

数据库死锁分析(行锁、间隙锁)[亲测有效]

小小码 • • 代码基础 • 阅读 136
一键激活最新全家桶

数据库死锁分析(行锁、间隙锁)[亲测有效]分享遇到过的一种间隙锁导致的死锁案例。文后有总结知识供参考 日志出现：Deadlock found when trying to get lock; try restarting transactio

数据库死锁分析(行锁、间隙锁)

 

分享遇到过的一种间隙锁导致的死锁案例。文后有总结知识供参考

 

日志出现：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

 

 

 

 

 

 

 

 

原文地址：https://www.cnblogs.com/wanglifeng717/archive/2022/03/11/15993400.html

版权声明：本文内容由互联网用户自发贡献，该文观点仅代表作者本人。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。如发现本站有涉嫌侵权/违法违规的内容， 请发送邮件至 举报，一经查实，本站将立刻删除。
转载请注明出处: https://daima100.com/5449.html

赞 (0)
小小码小小码
0 0
上一篇 2023-05-08
下一篇 2023-05-09

相关推荐

发表回复

您的电子邮箱地址不会被公开。 必填项已用*标注