破局-MySQL-死锁深入理解锁机制与高效解决方案
目录
破局 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 的间隙加插入意向锁三、锁的兼容性
| 锁类型 | 记录锁（X） | 间隙锁（Gap） | 临键锁（Next-Key） | 插入意向锁 |
|---|---|---|---|---|
| 记录锁（X） | 冲突 | 冲突 | 冲突 | 冲突 |
| 间隙锁（Gap） | 兼容 | 兼容 | 冲突 | 冲突 |
| 临键锁（Next-Key） | 冲突 | 冲突 | 冲突 | 冲突 |
| 插入意向锁 | 兼容 | 冲突 | 冲突 | 兼容 |
四、不同隔离级别的锁行为
| 隔离级别 | 脏读 | 不可重复读 | 幻读 | 锁机制 |
|---|---|---|---|---|
| READ UNCOMMITTED | 允许 | 允许 | 允许 | 不加锁（仅快照读） |
| READ COMMITTED | 禁止 | 允许 | 允许 | 仅记录锁，无间隙锁 |
| REPEATABLE READ | 禁止 | 禁止 | 禁止 | 记录锁 + 间隙锁（Next-Key Locks） |
| SERIALIZABLE | 禁止 | 禁止 | 禁止 | 所有操作加锁，强制串行执行 |