破局 MySQL 死锁：深入理解锁机制与高效解决方案

死锁的原理

1. 什么是死锁？

当 多个事务 在并发执行时，每个事务都 持有其他事务需要的锁，同时又在 等待对方释放锁，导致所有事务都无法继续执行的状态，称为 死锁（Deadlock）。

2. 死锁的四个必要条件

• 互斥条件：资源（如某行数据）一次只能被一个事务独占。

• 请求与保持条件：事务在持有某些锁的同时，请求新的锁。

• 不剥夺条件：事务已获得的锁不能被强制剥夺。

• 循环等待条件：事务之间形成 环形等待链，如事务A等待事务B，事务B又等待事务A。

MySQL 中死锁的常见场景

1. 场景1：交叉更新不同顺序

-- 事务1：先更新表A，再更新表B
BEGIN;
UPDATE table_a SET col = 1 WHERE id = 1;
UPDATE table_b SET col = 2 WHERE id = 2;
COMMIT;

-- 事务2：先更新表B，再更新表A
BEGIN;
UPDATE table_b SET col = 3 WHERE id = 2;
UPDATE table_a SET col = 4 WHERE id = 1;
COMMIT;

• 事务1持有table_a.id=1的锁，请求table_b.id=2的锁。

• 事务2持有table_b.id=2的锁，请求table_a.id=1的锁。

• 形成循环等待，触发死锁。

2. 场景2：索引缺失导致全表锁

当 SQL 语句的 WHERE 条件字段无索引 时，InnoDB 引擎无法通过索引快速定位目标行，必须通过 全表扫描 逐行检查数据。在此过程中，InnoDB 会对 所有扫描到的行加锁（具体锁类型由隔离级别决定）。这种机制会导致以下问题：

锁范围扩大：即使实际需要修改的行很少，也可能因全表扫描锁定大量无关行。

间隙锁扩散：在可重复读（REPEATABLE READ）隔离级别下，InnoDB 会为全表扫描的行加上 间隙锁，锁定整个表的间隙。

锁冲突概率激增：多个事务并发执行全表扫描操作时，可能因锁竞争导致死锁。

场景示例

假设有一张 users 表，存储用户信息，其中 age 字段无索引：

CREATE TABLE users (
    id INT PRIMARY KEY,
    name VARCHAR(50),
    age INT,          -- 无索引
    status VARCHAR(20)
);

两个事务并发执行以下操作：

-- 事务1：更新年龄大于30的用户状态
BEGIN;
UPDATE users SET status = 'inactive' WHERE age > 30;  -- age字段无索引

-- 事务2：更新年龄小于20的用户状态
BEGIN;
UPDATE users SET status = 'active' WHERE age < 20;    -- age字段无索引

锁行为分析

事务1 执行 UPDATE 时，由于 age 无索引，InnoDB 必须 全表扫描，对所有扫描到的行加锁：

• 若隔离级别为 可重复读（REPEATABLE READ），会加 Next-Key 锁（记录锁 + 间隙锁），锁定全表所有行及其间隙。
• 若隔离级别为 读已提交（READ COMMITTED），仅加 记录锁，但全表扫描仍可能锁定大量行。

事务2 同样执行全表扫描，尝试锁定符合条件的行。若两事务锁定的行存在 交叉或重叠，可能导致相互等待，最终触发死锁。

3. 场景3：间隙锁（Gap Lock）冲突

隔离级别为可重复读（REPEATABLE READ） 时，InnoDB 会使用 间隙锁（锁定一个范围）。

例如：SELECT * FROM users WHERE id > 100 FOR UPDATE; 会锁定 id > 100 的所有间隙。

两个事务锁定不同的间隙范围时，可能因间隙交叉导致死锁。

 分析死锁

1. 查看死锁日志

执行以下命令获取死锁信息：

SHOW ENGINE INNODB STATUS;

在输出中查找 LATEST DETECTED DEADLOCK 部分，关键信息包括：

• 涉及的事务：事务ID、执行的SQL语句。

• 持有的锁：事务当前持有的锁类型（行锁、间隙锁等）。

• 等待的锁：事务正在请求的锁。

2. 示例日志分析

LATEST DETECTED DEADLOCK
------------------------
2023-10-01 10:00:00 0x7f8e12345600
*** (1) TRANSACTION:
TRANSACTION 1001, ACTIVE 2 sec updating
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 1, OS thread handle 123456, query id 100 localhost root
UPDATE table_b SET col = 3 WHERE id = 2;

*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table `test`.`table_b` 
trx id 1001 lock_mode X locks rec but not gap

*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`table_a` 
trx id 1001 lock_mode X locks rec but not gap waiting

*** (2) TRANSACTION:
TRANSACTION 1002, ACTIVE 1 sec updating
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 2, OS thread handle 123457, query id 101 localhost root
UPDATE table_a SET col = 4 WHERE id = 1;

*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 10 page no 4 n bits 72 index PRIMARY of table `test`.`table_a` 
trx id 1002 lock_mode X locks rec but not gap

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 10 page no 3 n bits 72 index PRIMARY of table `test`.`table_b` 
trx id 1002 lock_mode X locks rec but not gap waiting

• 务1 持有 table_b.id=2 的锁，等待 table_a.id=1 的锁。

• 事务2 持有 table_a.id=1 的锁，等待 table_b.id=2 的锁。

• 结论：典型的交叉更新死锁。

解决死锁

1. 统一资源访问顺序

• 核心思想：所有事务按固定顺序访问资源（如先操作表A，再操作表B）。

• 示例：修改事务2的更新顺序：

-- 事务2调整为先更新表A，再更新表B
BEGIN;
UPDATE table_a SET col = 4 WHERE id = 1;
UPDATE table_b SET col = 3 WHERE id = 2;
COMMIT;

2.优化索引

• 避免全表扫描：为 WHERE 条件字段添加索引。

ALTER TABLE users ADD INDEX idx_age (age);

优化后，UPDATE users SET name = 'Tom' WHERE age = 20; 只会锁定符合条件的行，而非全表。

3. 缩短事务时间

• 尽早提交事务：减少锁的持有时间。

• 避免长事务：不在事务中执行耗时操作（如文件IO、网络请求）。

4. 调整隔离级别

• 降低隔离级别：将隔离级别从 REPEATABLE READ 改为 READ COMMITTED，减少间隙锁的使用。

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5.锁定读（FOR UPDATE）

• 提前获取锁：在事务开始时锁定所有需要的资源。

• 示例：

BEGIN;
SELECT * FROM table_a WHERE id = 1 FOR UPDATE; -- 提前锁定行
UPDATE table_a SET col = 1 WHERE id = 1;
COMMIT;

6.使用锁超时

• 设置 innodb_lock_wait_timeout（默认50秒），强制终止长时间等待锁的事务：

SET GLOBAL innodb_lock_wait_timeout = 30; -- 设置为30秒

 补充：Mysql锁

一、锁的分类

1. 按锁的粒度划分

• 全局锁：锁定整个数据库实例，用于全库备份。

• 表级锁：锁定整张表，MyISAM 默认使用。

• 行级锁：锁定表中的特定行，InnoDB 支持。

2. 按锁的兼容性划分

• 共享锁（S锁）：允许读，阻止写。

  SELECT * FROM table WHERE id = 1 LOCK IN SHARE MODE;

• 排他锁（X锁）：阻止读和写。

  SELECT * FROM table WHERE id = 1 FOR UPDATE;

3. 按锁的实现方式划分

• 悲观锁：默认认为并发冲突会发生，先加锁再操作。

• 乐观锁：假设冲突较少，通过版本号（如CAS）控制。

二、InnoDB 的行级锁类型

1. 记录锁（Record Locks）

• 作用：锁定索引中的一行记录。

• 场景：精确匹配索引的查询。

UPDATE users SET name = 'Tom' WHERE id = 1; -- 锁定 id=1 的行

2.间隙锁（Gap Locks）

• 作用：锁定索引记录之间的间隙（范围，不包含记录本身）。

• 场景：防止其他事务插入数据（解决幻读）。

SELECT * FROM users WHERE age > 20 FOR UPDATE; -- 锁定 age > 20 的间隙

3.临键锁（Next-Key Locks）

• 作用：记录锁 + 间隙锁，锁定一个左开右闭的区间。

• 场景：可重复读（REPEATABLE READ）下的默认锁机制。

SELECT * FROM users WHERE id BETWEEN 5 AND 10 FOR UPDATE; -- 锁定 (5,10]

4.插入意向锁（Insert Intention Locks）

• 作用：标记一个间隙，表示事务准备在此插入数据。

• 场景：插入新数据前触发，与间隙锁互斥。

INSERT INTO users (id, name) VALUES (6, 'Jerry'); -- 对 id=6 的间隙加插入意向锁

三、锁的兼容性

四、不同隔离级别的锁行为