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

大家好，我是考100分的小小码，祝大家学习进步，加薪顺利呀。今天说一说数据库死锁分析(行锁、间隙锁)[亲测有效],希望您对编程的造诣更进一步.

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

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