由MySQL加锁机制引发的死锁案例分析

StringKai

已于 2024-03-15 19:25:49 修改

阅读量475

点赞数

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

于 2023-03-21 21:12:05 首次发布

本文链接：https://blog.csdn.net/string_kai/article/details/129696364

版权

数据库专栏收录该内容

8 篇文章

订阅专栏

文章通过一个具体的死锁案例解释了在高并发场景下，两个事务如何因为不同的加锁顺序产生死锁。事务1对value=1的索引项及前后间隙加锁，事务2对id=1的主键索引项加锁，导致双方在获取对方已持有的锁时产生循环等待，从而引发死锁。同时，文章强调了MySQL的NextKey锁在防止幻读和行锁策略中的作用。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

1、死锁案例

--建表
CREATE TABLE t1(
    `id` int(11) NOT NULL,
    `value` int(11) NOT NULL
    PRIMARY KEY (`id`),
    KEY `idx_value` (`value`)
);
--插入初始数据
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

--事务1
select count(*) from t1 where value=1 for update;
--事务2
update t1 set value=2 where id=1;

在高并发场景下事务1和事务2会发生死锁。

2、MySQL加锁机制

Mysql基础(十九)：锁
 MySQL 加锁机制验证记录 | 三点水

首先需要知道的是，MySQL的行锁是对索引项加锁，可重复读隔离级别下具体的加锁方式如下：

SQL操作的索引	加锁方式
主键索引	对应的主键索引项加行锁
唯一索引	对应的唯一索引项+对应的主键索引项都加行锁
非唯一索引	对应的索引项加NextKey锁，对应的主键索引项加行锁
无索引	对所有的主键索引项加NextKey锁，实际效果如同锁表

NextKey锁即行锁+间隙锁，用于锁定一个范围，主要用于解决幻读的问题

3、分析

--建表
CREATE TABLE t1(
    `id` int(11) NOT NULL,
    `value` int(11) NOT NULL
    PRIMARY KEY (`id`),
    KEY `idx_value` (`value`)
);
--插入初始数据
insert into t1 values(1,1);
insert into t1 values(2,2);
insert into t1 values(3,3);

--事务1
select count(*) from t1 where value=1 for update;
--事务2
update t1 set value=2 where id=1;

如果不了解MySQL具体的加锁机制（加锁顺序及加锁对象），我们可能会错误地认为事务1和事务2都是对 id=1 这行进行了加锁，即只存在对单个资源的竞争，两个事务会发生锁等待，但不会发生死锁。
但是如果考虑到MySQL的加锁顺序及加锁对象，我们可以看到：
事务1会先对 value=1 的索引项加锁（此外还会对该索引项前后的间隙加间隙锁），再对 id=1 的主键索引项加锁；
事务2会先对 id=1 的主键索引项加锁，之后修改value的索引需要获取value=1的索引项的锁。
这样就很容易看出来，在高并发情况下，事务1和事务2对 id=1 的主键索引项、value=1 的索引项这两个资源产生了竞争，可能会产生循环等待，即产生死锁。
分析的过程不复杂，但会因为知识储备存在漏洞，而解不出来；并且case不好手动触发，只有高并发场景才能复现。