由MySQL死锁问题思考和部分解决方案

最新推荐文章于 2024-08-05 11:10:07 发布

夏沫余晖

最新推荐文章于 2024-08-05 11:10:07 发布

阅读量725

点赞数 1

分类专栏： MySQL死锁文章标签： mysql 数据库 sql

本文链接：https://blog.csdn.net/weixin_41984330/article/details/126557850

版权

MySQL死锁专栏收录该内容

1 篇文章 0 订阅

订阅专栏

背景

项目上操作事务时偶尔会遇到MySQL数据库提示【Deadlock found when trying to get lock; try restarting transaction】的死锁问题，虽然MySQL5.6版本后可以使用死锁检测进行相关事务回滚，但总会有错误预警日志萦绕在周末氛围下使我不得安生；因此特挑几个生产中遇到的死锁加以剖析，一则给自己加深自己的记忆，二则给其他同僚提供部分解决死锁的思路.

项目准备

数据库版本：MySQL-5.7.36
数据库隔离级别:RR(REPEATABLE READ)

部分锁类型介绍

因后续使用【show engine innodb status】观察死锁日志时可能会看到，故提前记录名词，便于后续匹配.

记录锁(LOCK_REC_NOT_GAP): lock_mode X locks rec but not gap
间隙锁(LOCK_GAP): lock_mode X locks gap before rec Next-key
锁(LOCK_ORNIDARY): lock_mode X 插入意向锁(LOCK_INSERT_INTENTION): lock_mode
X locks gap before rec insert intention

死锁问题1

场景模拟

场景说明
1.业务场景：服务程序启动成功后，有一个校验机制平均需要20S才能加载完成，因项目中采取的是懒加载机制，所以在业务已经开始如两个或多个相同事务并发执行时，可能某一个事务才会去加载校验机制。
2.步骤说明：
1).事务执行删除/更新操作时，删除/更新的条件为添加有索引的列；
2).业务逻辑(第一次加载需要执行20秒，后续则不需要加载，实际运行可能仅0.5秒)；
3).插入一条或多条数据.
表结构及数据

-- 表结构
create table table1(
id BIGINT(20) PRIMARY KEY AUTO_INCREMENT,
name VARCHAR(32) not null,
age TINYINT(2) ,
index idx_name(name)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
-- 数据
INSERT INTO `table1` (`id`, `name`, `age`) VALUES (1, 'sy', 10);
INSERT INTO `table1` (`id`, `name`, `age`) VALUES (2, '张三', 20);

tx1(事务1)

-- 开始事务
start TRANSACTION;
-- 删除表中所有名称为【admin】的数据.
DELETE FROM table1  WHERE name = 'admin'
-- 再插入一条名称为【admin】的数据.
INSERT INTO table1( name,age)  VALUES  ( 'admin',100 );
-- 事务提交
COMMIT;

tx2(事务2)

-- 开始事务
start TRANSACTION;
-- 删除表中所有名称为【admin】的数据.
DELETE FROM table1  WHERE name = 'admin'
-- 再插入一条名称为【admin】的数据.
INSERT INTO table1  ( name,age)  VALUES  ( 'admin',100 );
-- 事务提交
COMMIT;

执行顺序

步骤序号	执行解释	结果
1	开启tx1事务	成功
2	执行tx1的delete语句	成功
3	开启tx2事务	成功
4	执行tx2的delete语句	成功
5	执行tx1的插入语句	阻塞
6	执行tx2的插入语句	提示死锁

相关日志和执行结果
步骤执行完毕结果截图

使用show engine innodb status获取死锁检测状态

LATEST DETECTED DEADLOCK
------------------------
2022-08-26 18:32:29 0x7f9b04104700
*** (1) TRANSACTION:
TRANSACTION 15136, ACTIVE 30 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 4, OS thread handle 140303765104384, query id 421 172.17.0.1 root update
INSERT INTO table1  ( name,age)  VALUES  ( 'admin',100 )
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 4 n bits 72 index idx_name of table `test`.`table1` trx id 15136 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 7379; asc sy;;
 1: len 8; hex 8000000000000001; asc         ;;

*** (2) TRANSACTION:
TRANSACTION 15137, ACTIVE 24 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s), undo log entries 1
MySQL thread id 6, OS thread handle 140303764834048, query id 425 172.17.0.1 root update
INSERT INTO table1  ( name,age)  VALUES  ( 'admin',100 )
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 50 page no 4 n bits 72 index idx_name of table `test`.`table1` trx id 15137 lock_mode X locks gap before rec
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 7379; asc sy;;
 1: len 8; hex 8000000000000001; asc         ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 50 page no 4 n bits 72 index idx_name of table `test`.`table1` trx id 15137 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 2 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
 0: len 2; hex 7379; asc sy;;
 1: len 8; hex 8000000000000001; asc         ;;

*** WE ROLL BACK TRANSACTION (2)

原因剖析

根本原因：间隙锁与插入意向锁不兼容;间隙锁与间隙锁兼容
步骤和原因详解

tx1和tx2执行删除操作时，满足两个条件，一是（name）列存在索引，说明可以将表锁变更为行锁（记录锁）；二是删除操作时，（name）列没有任何数据可以命中，即应删除行数为0，则此时tx1和tx2的删除操作可以同时进行。但实际上tx1和tx2都会隐式持有表中最大记录行数据（id =2）与 asc supremum（上伪记录行）的间隙锁。
对此间隙锁较为形象的解释是：类似于[2,正无穷]的形式.
执行tx1的插入操作时，因意向锁同间隙锁冲突,需要判断其他事务是否持有[2,正无穷]的间隙锁。但扫描后发现tx2已有该范围的间隙锁,则需要阻塞等待tx2的间隙锁释放，此时tx1的插入操作进行阻塞状态.
执行tx2的插入操作时，同tx1的插入操作流程一样回去检测相关间隙锁的持有情况，此时检测判断tx1已经持有[2,正无穷]的间隙锁,但由于tx1的插入正在等待本事务间隙锁释放，构成死锁条件(互相拥有对象想要的资源，同时也需要等待对象释放资源)，由MySQL事务进行事务判断，选择需要中断的事务并释放其锁资源，打出报错日志.

补充说明

1.如删除时条件列不是索引列。则不会出现该死锁情况，这是由于非索引列情况下，会直接获取表锁，而不是获取到具体行的记录锁；举例：如上述事务中以(age)列作为条件，则在步骤4就会阻塞，这是由于表锁无法获取而导致的，这样虽然可以完全避免死锁问题，但它实际等同于Serializable级别，会降低并发事务的吞吐级别.

2.如删除时条件列为索引列，但条件可以命中部分数据。此时可能会发生死锁，需要看具体的间隙锁范围，如间隙锁范围并未在表中最大记录行数据中，则不会发生死锁，否则也会发生死锁，原因同上.

解决方案

Delete/Update和Insert操作同一张表在同一事务时，需要合理考虑事务隔离级别是否已经需要间隙锁；是否需要使用分布式锁进行事务的顺序处理，从根本保证事务不会同时操作同一张表.

死锁问题2

今天就写到这里,其实题主还有很多中死锁的情况，但人太懒，那就只能后面再补充了,下次一定…（insert into table1 select * from xxx）

后记

死锁问题不难定位,一般都是资源抢占冲突导致的,下面的部分死锁侦测手段已献上，如有需要自取…

是否锁表查询：SHOW OPEN TABLES WHERE In_use > 0;
查询执行线程：SHOW PROCESSLIST；
正在执行事务查询：SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
最近死锁日志查询: show engine innodb status;
正在执行事务获取锁查询：SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;