锁机制

锁是数据库并发控制的核心，理解锁机制是掌握 MySQL 的关键。

锁的基本概念

什么是锁？

锁是用来管理对共享资源的并发访问的一种机制。在数据库中，锁用于：

保证数据一致性：防止多个事务同时修改同一数据
实现隔离性：控制事务之间的可见性
协调并发访问：在并发和一致性之间取得平衡

为什么需要锁？

-- 场景：两个事务同时给账户加 100 元
-- 初始余额：1000 元

-- 事务 A                        事务 B
BEGIN;                           BEGIN;
SELECT balance FROM account      
WHERE id = 1;  -- 读到 1000
                                 SELECT balance FROM account 
                                 WHERE id = 1;  -- 读到 1000
UPDATE account SET balance = 1100
WHERE id = 1;  -- 1000 + 100
                                 UPDATE account SET balance = 1100
                                 WHERE id = 1;  -- 1000 + 100
COMMIT;                          COMMIT;

-- 结果：余额是 1100，而不是 1200！（丢失更新）

使用锁后：

-- 事务 A                              事务 B
BEGIN;                                 BEGIN;
SELECT balance FROM account 
WHERE id = 1 FOR UPDATE;  -- 加 X 锁
-- 读到 1000
                                       SELECT balance FROM account 
                                       WHERE id = 1 FOR UPDATE;  
                                       -- 阻塞，等待事务 A 释放锁
UPDATE account SET balance = 1100;
COMMIT;  -- 释放锁
                                       -- 获得锁，读到 1100
                                       UPDATE account SET balance = 1200;
                                       COMMIT;

-- 结果：余额是 1200 ✅

锁的分类

MySQL 的锁可以从三个维度分类：

1. 按锁的粒度分类

全局锁
  └─ 锁整个数据库实例
表级锁
  ├─ 表锁（Table Lock）
  ├─ 元数据锁（MDL）
  ├─ 意向锁（Intention Lock）
  └─ AUTO-INC 锁
行级锁（InnoDB）
  ├─ 记录锁（Record Lock）
  ├─ 间隙锁（Gap Lock）
  ├─ 临键锁（Next-Key Lock）
  └─ 插入意向锁（Insert Intention Lock）

1.1 全局锁（Global Lock）

加锁方式：

FLUSH TABLES WITH READ LOCK;

特点：

锁定整个数据库实例
所有表都变成只读
其他线程的写操作、DDL 操作都会被阻塞

使用场景：

全库逻辑备份（mysqldump）
不推荐在生产环境使用

更好的替代方案：

-- 使用 mysqldump 的 --single-transaction 参数
-- 在可重复读隔离级别下，利用 MVCC 实现一致性读
mysqldump --single-transaction --master-data=2 database > backup.sql

1.2 表级锁

1.2.1 表锁（Table Lock）

加锁方式：

-- 读锁（共享锁）
LOCK TABLES table_name READ;

-- 写锁（排他锁）
LOCK TABLES table_name WRITE;

-- 释放锁
UNLOCK TABLES;

读锁（共享锁）特点：

当前会话：可以读，不能写
其他会话：可以读，写会阻塞

写锁（排他锁）特点：

当前会话：可以读写
其他会话：读写都会阻塞

锁兼容性矩阵：

读锁

写锁

读锁

✅ 兼容

❌ 互斥

写锁

❌ 互斥

示例：

-- 会话 A
LOCK TABLES t READ;
SELECT * FROM t;  -- ✅ 成功
UPDATE t SET a=1; -- ❌ 报错：Table 't' was locked with a READ lock

-- 会话 B
SELECT * FROM t;  -- ✅ 成功（读不阻塞）
UPDATE t SET a=1; -- ⏳ 阻塞（等待会话 A 释放锁）

1.2.2 元数据锁（MDL，Metadata Lock）

特点：

MySQL 5.5 引入
自动加锁，无需显式使用
保护表结构的一致性

加锁规则：

-- DML 操作（SELECT、INSERT、UPDATE、DELETE）自动加 MDL 读锁
SELECT * FROM t;  -- 加 MDL 读锁

-- DDL 操作（ALTER TABLE、DROP TABLE）自动加 MDL 写锁
ALTER TABLE t ADD COLUMN c INT;  -- 加 MDL 写锁

MDL 锁兼容性：

MDL 读锁

MDL 写锁

MDL 读锁

✅ 兼容

❌ 互斥

MDL 写锁

❌ 互斥

经典问题：长事务持有 MDL 读锁导致 DDL 阻塞

-- 会话 A：长事务
BEGIN;
SELECT * FROM t;  -- 加 MDL 读锁
-- ... 执行了很长时间，没有提交

-- 会话 B：DDL 操作
ALTER TABLE t ADD COLUMN c INT;  -- ⏳ 阻塞（等待 MDL 写锁）

-- 会话 C：普通查询
SELECT * FROM t;  -- ⏳ 也被阻塞了！

原因：

会话 A 持有 MDL 读锁
会话 B 的 DDL 等待 MDL 写锁（被 A 阻塞）
会话 C 的查询也要申请 MDL 读锁，但排在 B 后面，所以也被阻塞

解决方案：

避免长事务
在业务低峰期执行 DDL

使用 ALTER TABLE ... NOWAIT 或 WAIT N（MySQL 8.0.1+）：

ALTER TABLE t ADD COLUMN c INT, WAIT 10;  -- 最多等待 10 秒

1.2.3 意向锁（Intention Lock）

作用：

表明事务想要在表的某些行上加锁
协调表锁和行锁的冲突
提高加表锁的效率

类型：

意向共享锁（IS）：表明事务想要对某些行加共享锁
意向排他锁（IX）：表明事务想要对某些行加排他锁

加锁规则：

-- 加行级共享锁前，先加表级意向共享锁
SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;
-- 自动加：表级 IS 锁 → 行级 S 锁

-- 加行级排他锁前，先加表级意向排他锁
SELECT * FROM t WHERE id = 1 FOR UPDATE;
-- 自动加：表级 IX 锁 → 行级 X 锁

为什么需要意向锁？

没有意向锁时，加表锁需要：

1. 遍历所有行，检查是否有行锁
2. 如果有冲突的行锁，则加表锁失败
3. 时间复杂度：O(n)

有意向锁时，加表锁只需：

1. 检查表级意向锁
2. 如果有冲突的意向锁，则加表锁失败
3. 时间复杂度：O(1)

锁兼容性矩阵：

✅

❌

✅

❌

✅

❌

✅

❌

注意：意向锁之间兼容，不会互相阻塞。

1.2.4 AUTO-INC 锁

作用：保证自增主键的连续性

加锁机制：

INSERT INTO t (name) VALUES ('Alice');
-- 在插入时，自动对 AUTO_INCREMENT 列加锁

参数控制：innodb_autoinc_lock_mode

0（traditional）：传统模式
- 所有 INSERT 都用表级 AUTO-INC 锁
- 语句执行完才释放
- 并发性能差
1（consecutive，默认）：连续模式
- 简单插入（INSERT）：使用轻量级互斥量
- 批量插入（INSERT ... SELECT）：使用表级锁
- 保证连续性，性能较好
2（interleaved）：交错模式
- 所有 INSERT 都用轻量级互斥量
- 不保证连续性（ID 可能有空洞）
- 并发性能最好
- binlog 必须是 row 格式

2. 按锁的模式分类

2.1 共享锁（Shared Lock，S 锁）

特点：

也叫读锁
多个事务可以同时持有
持有 S 锁的事务可以读数据
阻止其他事务获得 X 锁

加锁方式：

SELECT * FROM t WHERE id = 1 LOCK IN SHARE MODE;
-- MySQL 8.0+ 推荐使用：
SELECT * FROM t WHERE id = 1 FOR SHARE;

2.2 排他锁（Exclusive Lock，X 锁）

特点：

也叫写锁
只有一个事务可以持有
持有 X 锁的事务可以读写数据
阻止其他事务获得 S 锁和 X 锁

加锁方式：

-- 显式加锁
SELECT * FROM t WHERE id = 1 FOR UPDATE;

-- 隐式加锁
UPDATE t SET a = 1 WHERE id = 1;
DELETE FROM t WHERE id = 1;
INSERT INTO t VALUES (...);

锁兼容性：

S 锁

X 锁

S 锁

✅ 兼容

❌ 互斥

X 锁

❌ 互斥

3. 按锁的算法分类（InnoDB 行锁）

这是 MySQL 锁机制的核心，也是面试的重点。

3.1 记录锁（Record Lock）

定义：锁定单个索引记录。

特点：

总是锁定索引记录，而不是数据行本身
如果表没有索引，InnoDB 会创建隐藏的聚簇索引

示例：

-- 表结构
CREATE TABLE t (
  id INT PRIMARY KEY,
  a INT,
  b INT,
  KEY(a)
);

-- 唯一索引等值查询，记录存在
SELECT * FROM t WHERE id = 10 FOR UPDATE;
-- 加锁：id = 10 的记录锁（X 锁）

在 EXPLAIN 中的表现：

type: const 或 eq_ref

3.2 间隙锁（Gap Lock）

定义：锁定索引记录之间的"间隙"，但不包括记录本身。

作用：

防止其他事务在间隙中插入数据
防止幻读的关键机制

特点：

只在 REPEATABLE READ 隔离级别下有效
READ COMMITTED 级别下没有间隙锁
间隙锁之间不互斥（都是为了防止插入）

示例：

-- 假设表 t 中有记录：id = 1, 5, 10

-- 唯一索引等值查询，记录不存在
SELECT * FROM t WHERE id = 7 FOR UPDATE;
-- 加锁：间隙锁 (5, 10)，不包括 5 和 10

-- 此时其他事务：
INSERT INTO t VALUES (6, ...);  -- ⏳ 阻塞
INSERT INTO t VALUES (7, ...);  -- ⏳ 阻塞
INSERT INTO t VALUES (8, ...);  -- ⏳ 阻塞
INSERT INTO t VALUES (5, ...);  -- ✅ 成功（5 不在间隙内）
INSERT INTO t VALUES (10, ...); -- ✅ 成功（10 不在间隙内）

间隙锁不互斥的例子：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE id = 7 FOR UPDATE;  -- 加间隙锁 (5, 10)

-- 会话 B
BEGIN;
SELECT * FROM t WHERE id = 8 FOR UPDATE;  -- ✅ 成功！也加间隙锁 (5, 10)

-- 因为两个间隙锁都是为了防止插入，不冲突

3.3 临键锁（Next-Key Lock）

定义：记录锁 + 间隙锁，锁定一个左开右闭区间 (a, b]。

特点：

这是 InnoDB 的默认行锁算法
REPEATABLE READ 隔离级别下的标配
彻底解决幻读问题

示例：

-- 假设表 t 中有记录：id = 1, 5, 10, 15

-- 范围查询
SELECT * FROM t WHERE id >= 10 AND id < 15 FOR UPDATE;

-- 加锁：
-- 1. (5, 10] 的临键锁
-- 2. (10, 15] 的临键锁
-- 注意：15 这条记录也被锁了（右闭区间）

临键锁的组成：

临键锁 (5, 10] = 间隙锁 (5, 10) + 记录锁 [10]

为什么是左开右闭？

为了避免扫描时的重复加锁
简化加锁逻辑

3.4 插入意向锁（Insert Intention Lock）

定义：一种特殊的间隙锁，在插入时加的锁。

特点：

插入意向锁之间不互斥
但会被间隙锁阻塞

示例：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE id > 5 AND id < 10 FOR UPDATE;
-- 加锁：间隙锁 (5, 10)

-- 会话 B
BEGIN;
INSERT INTO t VALUES (7, ...);
-- 1. 申请插入意向锁（想在 (5, 10) 插入）
-- 2. ⏳ 被会话 A 的间隙锁阻塞

-- 会话 C
BEGIN;
INSERT INTO t VALUES (8, ...);
-- 1. 申请插入意向锁（想在 (5, 10) 插入）
-- 2. ⏳ 被会话 A 的间隙锁阻塞
-- 3. 但不会被会话 B 的插入意向锁阻塞

InnoDB 行锁详解

行锁的本质

核心原则：InnoDB 的行锁是加在索引上的，而不是加在数据行上。

没有索引 → 锁全表
有索引   → 锁索引记录

不同索引类型的加锁

1. 主键索引（聚簇索引）

-- 等值查询，记录存在
SELECT * FROM t WHERE id = 10 FOR UPDATE;
-- 加锁：id = 10 的记录锁

-- 等值查询，记录不存在
SELECT * FROM t WHERE id = 15 FOR UPDATE;
-- 加锁：间隙锁，例如 (10, 20)

2. 唯一索引

-- 等值查询，记录存在
SELECT * FROM t WHERE uniq_col = 'abc' FOR UPDATE;
-- 加锁：
-- 1. uniq_col = 'abc' 的记录锁
-- 2. 对应的主键记录锁（如果需要回表）

-- 等值查询，记录不存在
SELECT * FROM t WHERE uniq_col = 'xyz' FOR UPDATE;
-- 加锁：间隙锁

3. 普通索引

-- 等值查询
SELECT * FROM t WHERE normal_col = 10 FOR UPDATE;
-- 加锁：
-- 1. normal_col = 10 的所有记录锁
-- 2. normal_col = 10 前后的间隙锁
-- 3. 对应的主键记录锁（回表）

-- 范围查询
SELECT * FROM t WHERE normal_col >= 10 AND normal_col < 20 FOR UPDATE;
-- 加锁：
-- 1. [10, 20) 范围内的临键锁
-- 2. 可能还包括 20 之后的间隙锁
-- 3. 对应的主键记录锁

4. 无索引

-- 无索引条件
SELECT * FROM t WHERE non_indexed_col = 10 FOR UPDATE;
-- 加锁：全表扫描，所有记录都加锁（退化为表锁）

优化建议：

✅ 必须为 WHERE 条件的列建立索引
✅ 避免全表扫描锁全表

加锁规则分析

这是面试的重点，必须掌握！

加锁规则总结

两个原则：

加锁的基本单位是 Next-Key Lock（临键锁）
查找过程中访问到的对象才会加锁

两个优化：

等值查询，如果找到了唯一记录，Next-Key Lock 退化为 Record Lock
等值查询，没有找到记录，Next-Key Lock 退化为 Gap Lock

一个 bug（MySQL 8.0 已修复）：

唯一索引的范围查询会访问到不满足条件的第一个值为止

场景 1：唯一索引等值查询（记录存在）

-- 表结构和数据
CREATE TABLE t (
  id INT PRIMARY KEY,
  c INT,
  d INT,
  KEY(c)
) ENGINE=InnoDB;

INSERT INTO t VALUES 
(0, 0, 0), (5, 5, 5), (10, 10, 10), (15, 15, 15), (20, 20, 20), (25, 25, 25);

-- 查询
SELECT * FROM t WHERE id = 10 FOR UPDATE;

加锁分析：

查询使用主键索引
等值查询，找到了 id = 10 的记录
优化 1：Next-Key Lock 退化为 Record Lock

加锁结果：

主键索引：id = 10 的记录锁（X 锁）

验证：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE id = 10 FOR UPDATE;

-- 会话 B
UPDATE t SET d = 100 WHERE id = 10;  -- ⏳ 阻塞
UPDATE t SET d = 100 WHERE id = 9;   -- ✅ 成功
UPDATE t SET d = 100 WHERE id = 11;  -- ✅ 成功

场景 2：唯一索引等值查询（记录不存在）

SELECT * FROM t WHERE id = 7 FOR UPDATE;

加锁分析：

查询使用主键索引
等值查询，没有找到 id = 7 的记录
扫描到 id = 10（第一个大于 7 的记录）
优化 2：Next-Key Lock 退化为 Gap Lock

加锁结果：

主键索引：间隙锁 (5, 10)

验证：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE id = 7 FOR UPDATE;

-- 会话 B
INSERT INTO t VALUES (6, 6, 6);   -- ⏳ 阻塞
INSERT INTO t VALUES (8, 8, 8);   -- ⏳ 阻塞
INSERT INTO t VALUES (5, 5, 5);   -- ✅ 成功
INSERT INTO t VALUES (10, 10, 10); -- ✅ 成功
UPDATE t SET d = 100 WHERE id = 10; -- ✅ 成功（记录锁不冲突）

场景 3：唯一索引范围查询

SELECT * FROM t WHERE id >= 10 AND id < 15 FOR UPDATE;

加锁分析：

查询使用主键索引
范围查询，扫描 id = 10, 15
对扫描到的记录加 Record Lock 及 Gap Lock

加锁结果：

主键索引：
- 10 的记录锁
- (10, 15) 的间隙锁

验证：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE id >= 10 AND id < 15 FOR UPDATE;

-- 会话 B
INSERT INTO t VALUES (8, 8, 8);     -- ✅ 成功 (不在锁定范围)
INSERT INTO t VALUES (9, 9, 9);     -- ✅ 成功 (不在锁定范围)
INSERT INTO t VALUES (10, 10, 10);  -- ⏳ 阻塞 (记录锁:id=10已存在)
INSERT INTO t VALUES (11, 11, 11);  -- ⏳ 阻塞 (间隙锁:(10,15))
INSERT INTO t VALUES (12, 12, 12);  -- ⏳ 阻塞 (间隙锁:(10,15))
INSERT INTO t VALUES (14, 14, 14);  -- ⏳ 阻塞 (间隙锁:(10,15))
INSERT INTO t VALUES (15, 15, 15);  -- ✅ 成功 (15是边界,不在间隙内)
INSERT INTO t VALUES (20, 20, 20);  -- ✅ 成功 (不在锁定范围)

UPDATE t SET d = 100 WHERE id = 10; -- ⏳ 阻塞 (记录锁)
UPDATE t SET d = 100 WHERE id = 15; -- ✅ 成功 (15没被锁)
UPDATE t SET d = 100 WHERE id = 20; -- ✅ 成功 (20没被锁)

场景 4：非唯一索引等值查询

SELECT * FROM t WHERE c = 10 FOR UPDATE;

加锁分析：

查询使用二级索引 c
等值查询，找到 c = 10 的记录
对 c 索引加 Next-Key Lock
继续向右扫描，直到第一个不满足条件的记录
对主键索引加记录锁（回表）

加锁结果：

c 索引：
- (5, 10] 的临键锁
- (10, 15) 的间隙锁
主键索引：
- id = 10 的记录锁

为什么要锁 (10, 15) 的间隙？

防止其他事务插入 c = 10 的新记录
保证可重复读

验证：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE c = 10 FOR UPDATE;

-- 会话 B
-- INSERT 测试
INSERT INTO t VALUES (8, 10, 10);   -- ⏳ 阻塞 (c=10被锁)
INSERT INTO t VALUES (8, 5, 5);     -- ✅ 成功 (c=5不在锁范围)
INSERT INTO t VALUES (12, 10, 10);  -- ⏳ 阻塞 (c=10被锁)
INSERT INTO t VALUES (12, 11, 11);  -- ⏳ 阻塞 (间隙锁 (c=10, c=15))
INSERT INTO t VALUES (12, 14, 14);  -- ⏳ 阻塞 (间隙锁 (c=10, c=15))
INSERT INTO t VALUES (12, 15, 15);  -- ✅ 成功 (c=15是右边界,不在间隙内)
INSERT INTO t VALUES (12, 20, 20);  -- ✅ 成功 (c=20不在锁范围)

-- UPDATE 测试
UPDATE t SET d = 100 WHERE id = 10; -- ⏳ 阻塞 (主键id=10被锁)
UPDATE t SET d = 100 WHERE id = 8;  -- ✅ 成功 (id=8没被锁)
UPDATE t SET d = 100 WHERE c = 10;  -- ⏳ 阻塞 (二级索引c=10被锁)
UPDATE t SET d = 100 WHERE c = 15;  -- ✅ 成功 (c=15没被直接锁)

场景 5：非唯一索引范围查询

SELECT * FROM t WHERE c >= 10 AND c < 15 FOR UPDATE;

加锁分析：

查询使用二级索引 c
范围查询，扫描 c = 10, 15
对 c 索引加 Next-Key Lock
对主键索引加记录锁

加锁结果：

c 索引：
- (5, 10] 的临键锁
- (10, 15] 的临键锁
主键索引：
- id = 10, 15 的记录锁

场景 6：无索引条件

SELECT * FROM t WHERE d = 10 FOR UPDATE;
-- d 列没有索引

加锁分析：

全表扫描
所有记录都加 Next-Key Lock
退化为表锁

加锁结果：

主键索引：所有记录的 Next-Key Lock

验证：

-- 会话 A
BEGIN;
SELECT * FROM t WHERE d = 10 FOR UPDATE;

-- 会话 B
UPDATE t SET d = 100 WHERE id = 5;  -- ⏳ 阻塞
UPDATE t SET d = 100 WHERE id = 20; -- ⏳ 阻塞
INSERT INTO t VALUES (30, 30, 30);  -- ⏳ 阻塞
-- 所有操作都被阻塞！

优化建议：

✅ 必须为 WHERE 条件的列建立索引
❌ 避免无索引条件的加锁查询

加锁规则速查表

索引类型

查询类型

记录是否存在

加锁情况

唯一索引

等值查询

存在

记录锁

唯一索引

等值查询

不存在

间隙锁

唯一索引

范围查询

临键锁

普通索引

等值查询

临键锁 + 间隙锁

普通索引

范围查询

临键锁

无索引

任何查询

全表锁

死锁

什么是死锁？

死锁是指两个或多个事务互相持有对方需要的锁，导致所有事务都无法继续执行。

死锁的四个必要条件

互斥条件：资源不能被多个事务同时使用
请求与保持条件：事务持有资源的同时请求新资源
不可剥夺条件：已获得的资源不能被强制剥夺
循环等待条件：存在循环等待链

死锁案例 1：简单的两个事务

-- 事务 A                      事务 B
BEGIN;                         BEGIN;
UPDATE t SET a=1 WHERE id=1;   
-- 持有 id=1 的 X 锁
                               UPDATE t SET a=2 WHERE id=2;
                               -- 持有 id=2 的 X 锁
UPDATE t SET a=3 WHERE id=2;
-- ⏳ 等待 id=2 的 X 锁
                               UPDATE t SET a=4 WHERE id=1;
                               -- ⏳ 等待 id=1 的 X 锁
-- 💥 死锁！

死锁图示：

事务 A → 持有 id=1，等待 id=2
            ↓
事务 B → 持有 id=2，等待 id=1
            ↓
           循环等待

死锁案例 2：间隙锁与插入意向锁

-- 假设表中只有 id = 5 和 id = 10

-- 事务 A                           事务 B
BEGIN;                              BEGIN;
SELECT * FROM t WHERE id = 7 
FOR UPDATE;
-- 加间隙锁 (5, 10)
                                    SELECT * FROM t WHERE id = 8 
                                    FOR UPDATE;
                                    -- 加间隙锁 (5, 10)
                                    -- ✅ 成功（间隙锁不互斥）
INSERT INTO t VALUES (7, ...);
-- 申请插入意向锁
-- ⏳ 等待事务 B 释放间隙锁
                                    INSERT INTO t VALUES (8, ...);
                                    -- 申请插入意向锁
                                    -- ⏳ 等待事务 A 释放间隙锁
-- 💥 死锁！

原因：

间隙锁之间不互斥
但插入意向锁会被间隙锁阻塞
两个事务都持有间隙锁，都想插入，形成循环等待

死锁检测与处理

1. 死锁检测

参数：innodb_deadlock_detect

-- 查看死锁检测是否开启（默认开启）
SHOW VARIABLES LIKE 'innodb_deadlock_detect';
-- ON：开启，OFF：关闭

检测机制：

InnoDB 有专门的死锁检测线程
当事务等待锁时，检测是否形成循环等待
时间复杂度：O(n)，n 是事务数

高并发下的问题：

死锁检测本身消耗 CPU
1000 个并发事务，检测一次需要 100 万次计算
可能导致 CPU 使用率飙升

2. 超时机制

参数：innodb_lock_wait_timeout

-- 查看锁等待超时时间（默认 50 秒）
SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';

机制：

如果等待锁的时间超过设置值，事务回滚
不够智能（可能回滚了重要的事务）

3. 死锁处理策略

InnoDB 的处理：

检测到死锁
选择回滚代价最小的事务
回滚整个事务
释放该事务持有的所有锁
其他事务继续执行

回滚代价计算：

持有行级写锁最少的事务
事务权重（可通过 innodb_trx.trx_weight 查看）

4. 查看死锁信息

-- 查看最近一次死锁信息
SHOW ENGINE INNODB STATUS;

死锁日志示例：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-01-11 14:30:25
*** (1) TRANSACTION:
TRANSACTION 421234, ACTIVE 10 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 360, 2 row lock(s)
MySQL thread id 123, OS thread handle 0x7f8c8c8c8c8c, query id 456 localhost root updating
UPDATE t SET a = 1 WHERE id = 2

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

*** (2) TRANSACTION:
TRANSACTION 421235, ACTIVE 8 sec starting index read
mysql tables in use 1, locked 1
4 lock struct(s), heap size 1136, 3 row lock(s)
MySQL thread id 124, OS thread handle 0x7f8c8c8c8c9c, query id 457 localhost root updating
UPDATE t SET a = 2 WHERE id = 1

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

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

*** WE ROLL BACK TRANSACTION (1)

如何避免死锁

1. 按相同顺序访问资源

-- ❌ 错误示例：不同顺序
-- 事务 A: id=1 → id=2
-- 事务 B: id=2 → id=1

-- ✅ 正确示例：相同顺序
-- 事务 A: id=1 → id=2
-- 事务 B: id=1 → id=2
-- 第二个事务会等待第一个事务完成，不会死锁

2. 尽量使用索引访问数据

-- ❌ 避免全表扫描
UPDATE t SET a = 1 WHERE non_indexed_col = 10;

-- ✅ 使用索引
UPDATE t SET a = 1 WHERE id = 10;

3. 减小事务粒度，缩短事务时间

-- ❌ 长事务
BEGIN;
UPDATE t1 ...;
UPDATE t2 ...;
-- ... 复杂业务逻辑
UPDATE t3 ...;
COMMIT;

-- ✅ 拆分成多个小事务
BEGIN;
UPDATE t1 ...;
COMMIT;

BEGIN;
UPDATE t2 ...;
COMMIT;

4. 使用较低的隔离级别

-- RR 级别有间隙锁，容易死锁
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

-- RC 级别没有间隙锁，死锁概率降低
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

5. 为表添加合理的索引

-- 避免因为无索引导致的全表锁
ALTER TABLE t ADD INDEX idx_col(col);

6. 避免大事务

-- ❌ 批量操作在一个事务中
BEGIN;
UPDATE t SET ... WHERE id IN (1, 2, 3, ..., 10000);
COMMIT;

-- ✅ 分批处理
for i in range(0, 10000, 100):
    BEGIN;
    UPDATE t SET ... WHERE id IN (i, i+1, ..., i+99);
    COMMIT;

7. 使用 SELECT ... FOR UPDATE 要慎重

-- 只在真正需要加锁时才使用
SELECT * FROM t WHERE id = 1 FOR UPDATE;

-- 如果只是读取，使用快照读
SELECT * FROM t WHERE id = 1;

锁的监控与诊断

1. 查看当前锁等待

MySQL 5.7

-- 查看正在等待的锁
SELECT * FROM information_schema.INNODB_LOCKS;

-- 查看锁等待关系
SELECT * FROM information_schema.INNODB_LOCK_WAITS;

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

MySQL 8.0+

-- 查看数据锁
SELECT * FROM performance_schema.data_locks;

-- 查看锁等待关系
SELECT * FROM performance_schema.data_lock_waits;

-- 查看当前事务
SELECT * FROM information_schema.INNODB_TRX;

2. 查看详细的锁信息

-- 找出阻塞的会话
SELECT 
    r.trx_id waiting_trx_id,
    r.trx_mysql_thread_id waiting_thread,
    r.trx_query waiting_query,
    b.trx_id blocking_trx_id,
    b.trx_mysql_thread_id blocking_thread,
    b.trx_query blocking_query
FROM information_schema.INNODB_LOCK_WAITS w
INNER JOIN information_schema.INNODB_TRX b ON b.trx_id = w.blocking_trx_id
INNER JOIN information_schema.INNODB_TRX r ON r.trx_id = w.requesting_trx_id;

3. 分析锁等待时间

-- 查看长时间等待锁的事务
SELECT 
    trx_id,
    trx_state,
    trx_started,
    trx_wait_started,
    TIMESTAMPDIFF(SECOND, trx_wait_started, NOW()) AS wait_seconds,
    trx_mysql_thread_id,
    trx_query
FROM information_schema.INNODB_TRX
WHERE trx_state = 'LOCK WAIT'
ORDER BY wait_seconds DESC;

4. 杀死阻塞的会话

-- 查找阻塞的线程 ID
SELECT blocking_thread FROM ...;

-- 杀死线程
KILL 123;  -- 123 是 thread_id

面试高频问题

Q1: MySQL 有哪些锁？

回答要点：

按粒度分：

全局锁：FLUSH TABLES WITH READ LOCK
表级锁：表锁、MDL、意向锁、AUTO-INC 锁
行级锁：记录锁、间隙锁、临键锁

按模式分：

共享锁（S 锁）：LOCK IN SHARE MODE / FOR SHARE
排他锁（X 锁）：FOR UPDATE

按算法分（InnoDB 行锁）：

记录锁：锁单个索引记录
间隙锁：锁索引记录之间的间隙
临键锁：记录锁 + 间隙锁，左开右闭区间

Q2: 什么是间隙锁和临键锁？

间隙锁（Gap Lock）：

锁定索引记录之间的"间隙"
防止其他事务在间隙中插入数据
防止幻读
只在 RR 隔离级别下有效
间隙锁之间不互斥

临键锁（Next-Key Lock）：

记录锁 + 间隙锁
锁定一个左开右闭区间 (a, b]
InnoDB 的默认行锁算法
彻底解决幻读问题

示例：

-- 表中有：id = 1, 5, 10
SELECT * FROM t WHERE id = 7 FOR UPDATE;
-- 间隙锁：(5, 10)

SELECT * FROM t WHERE id >= 5 AND id < 10 FOR UPDATE;
-- 临键锁：(1, 5], (5, 10)

Q3: 不同 SQL 语句如何加锁？

唯一索引等值查询：

-- 记录存在
SELECT * FROM t WHERE id = 10 FOR UPDATE;
-- 加锁：记录锁

-- 记录不存在
SELECT * FROM t WHERE id = 7 FOR UPDATE;
-- 加锁：间隙锁

唯一索引范围查询：

SELECT * FROM t WHERE id >= 10 AND id < 20 FOR UPDATE;
-- 加锁：临键锁

非唯一索引查询：

SELECT * FROM t WHERE col = 10 FOR UPDATE;
-- 加锁：临键锁 + 间隙锁 + 主键记录锁

无索引查询：

SELECT * FROM t WHERE non_indexed = 10 FOR UPDATE;
-- 加锁：全表锁（所有记录的临键锁）

Q4: 如何避免死锁？

7 个方法：

按相同顺序访问资源
尽量使用索引（避免全表锁）
减小事务粒度（缩短持锁时间）
使用较低的隔离级别（RC 没有间隙锁）
合理设计索引
避免大事务（分批处理）
慎用 FOR UPDATE

Q5: 为什么 InnoDB 的锁是加在索引上的？

原因：

效率：通过索引快速定位数据
一致性：索引是有序的，便于范围锁定
设计：InnoDB 的数据是按索引组织的

后果：

没有索引 → 全表扫描 → 锁全表
有索引 → 快速定位 → 只锁部分行

示例：

-- 有索引
UPDATE t SET a = 1 WHERE id = 10;
-- 只锁 id = 10 这一行

-- 无索引
UPDATE t SET a = 1 WHERE col = 10;
-- 全表扫描，锁所有行！

Q6: 意向锁的作用是什么？

作用：协调表锁和行锁的冲突，提高加表锁的效率。

没有意向锁时：

加表锁需要：
1. 遍历所有行
2. 检查是否有行锁
3. O(n) 复杂度

有意向锁时：

加表锁只需：
1. 检查表级意向锁
2. O(1) 复杂度

机制：

-- 加行锁时，先加表级意向锁
SELECT * FROM t WHERE id = 1 FOR UPDATE;
-- 自动加：表级 IX 锁 → 行级 X 锁

-- 加表锁时，检查意向锁
LOCK TABLES t WRITE;
-- 检查是否有 IS 或 IX 锁

Q7: RC 和 RR 隔离级别的锁有什么区别？

READ COMMITTED（RC）：

✅ 没有间隙锁
✅ 只有记录锁
✅ 并发性能更好
❌ 有不可重复读和幻读问题

REPEATABLE READ（RR）：

✅ 有间隙锁和临键锁
✅ 防止幻读
✅ 保证可重复读
❌ 并发性能相对较差
❌ 更容易死锁（间隙锁冲突）

使用建议：

大多数场景：RC 更好（性能 + 死锁少）
需要可重复读：RR

总结

核心要点

锁的分类：
- 粒度：全局锁、表锁、行锁
- 模式：共享锁(S)、排他锁(X)
- 算法：记录锁、间隙锁、临键锁
行锁的本质：
- 锁加在索引上
- 没有索引 → 锁全表
临键锁：
- 记录锁 + 间隙锁
- 左开右闭区间 (a, b]
- 防止幻读的关键
加锁规则：
- 基本单位：临键锁
- 两个优化：退化为记录锁或间隙锁
死锁：
- 循环等待
- 按相同顺序访问资源
- 减小事务粒度

学习建议

动手实验：
- 搭建测试环境
- 验证每种加锁场景
- 观察锁等待和死锁
画图理解：
- 画出间隙锁的区间
- 画出临键锁的范围
- 画出死锁的循环等待图
分析真实案例：
- 查看生产环境的死锁日志
- 分析慢查询是否因为锁等待
- 优化加锁策略
掌握监控命令：
- SHOW ENGINE INNODB STATUS
- SELECT * FROM information_schema.INNODB_TRX
- SELECT * FROM performance_schema.data_locks

记住这些关键点

✅ 锁加在索引上
✅ 没有索引会锁全表
✅ 临键锁 = 记录锁 + 间隙锁
✅ 间隙锁防止幻读
✅ 按相同顺序避免死锁
✅ RC 级别没有间隙锁

下一步：学习 MySQL 事务，理解 ACID、隔离级别和 MVCC 原理。

Previous索引原理与优化 Next事务

Last updated 2 months ago

hashtag锁的基本概念

hashtag什么是锁？

hashtag为什么需要锁？

hashtag锁的分类

hashtag1. 按锁的粒度分类

hashtag1.1 全局锁（Global Lock）

hashtag1.2 表级锁

hashtag2. 按锁的模式分类

hashtag2.1 共享锁（Shared Lock，S 锁）

hashtag2.2 排他锁（Exclusive Lock，X 锁）

hashtag3. 按锁的算法分类（InnoDB 行锁）

hashtag3.1 记录锁（Record Lock）

hashtag3.2 间隙锁（Gap Lock）

hashtag3.3 临键锁（Next-Key Lock）

hashtag3.4 插入意向锁（Insert Intention Lock）

hashtagInnoDB 行锁详解

hashtag行锁的本质

hashtag不同索引类型的加锁

hashtag1. 主键索引（聚簇索引）

hashtag2. 唯一索引

hashtag3. 普通索引

hashtag4. 无索引

hashtag加锁规则分析

hashtag加锁规则总结

hashtag场景 1：唯一索引等值查询（记录存在）

hashtag场景 2：唯一索引等值查询（记录不存在）

hashtag场景 3：唯一索引范围查询

hashtag场景 4：非唯一索引等值查询

hashtag场景 5：非唯一索引范围查询

hashtag场景 6：无索引条件

hashtag加锁规则速查表

hashtag死锁

hashtag什么是死锁？

hashtag死锁的四个必要条件

hashtag死锁案例 1：简单的两个事务

hashtag死锁案例 2：间隙锁与插入意向锁

hashtag死锁检测与处理

hashtag1. 死锁检测

hashtag2. 超时机制

hashtag3. 死锁处理策略

hashtag4. 查看死锁信息

hashtag如何避免死锁

hashtag1. 按相同顺序访问资源

hashtag2. 尽量使用索引访问数据

hashtag3. 减小事务粒度，缩短事务时间

hashtag4. 使用较低的隔离级别

hashtag5. 为表添加合理的索引

hashtag6. 避免大事务

hashtag7. 使用 SELECT ... FOR UPDATE 要慎重

hashtag锁的监控与诊断

hashtag1. 查看当前锁等待

hashtagMySQL 5.7

hashtagMySQL 8.0+

hashtag2. 查看详细的锁信息

hashtag3. 分析锁等待时间

hashtag4. 杀死阻塞的会话

hashtag面试高频问题

hashtagQ1: MySQL 有哪些锁？

hashtagQ2: 什么是间隙锁和临键锁？

hashtagQ3: 不同 SQL 语句如何加锁？

hashtagQ4: 如何避免死锁？

hashtagQ5: 为什么 InnoDB 的锁是加在索引上的？

hashtagQ6: 意向锁的作用是什么？

hashtagQ7: RC 和 RR 隔离级别的锁有什么区别？

hashtag总结

hashtag核心要点

hashtag学习建议

hashtag记住这些关键点

锁的基本概念

什么是锁？

为什么需要锁？

锁的分类

1. 按锁的粒度分类

1.1 全局锁（Global Lock）

1.2 表级锁

2. 按锁的模式分类

2.1 共享锁（Shared Lock，S 锁）

2.2 排他锁（Exclusive Lock，X 锁）

3. 按锁的算法分类（InnoDB 行锁）

3.1 记录锁（Record Lock）

3.2 间隙锁（Gap Lock）

3.3 临键锁（Next-Key Lock）

3.4 插入意向锁（Insert Intention Lock）

InnoDB 行锁详解

行锁的本质

不同索引类型的加锁

1. 主键索引（聚簇索引）

2. 唯一索引

3. 普通索引

4. 无索引

加锁规则分析

加锁规则总结

场景 1：唯一索引等值查询（记录存在）

场景 2：唯一索引等值查询（记录不存在）

场景 3：唯一索引范围查询

场景 4：非唯一索引等值查询

场景 5：非唯一索引范围查询

场景 6：无索引条件

加锁规则速查表

死锁

什么是死锁？

死锁的四个必要条件

死锁案例 1：简单的两个事务

死锁案例 2：间隙锁与插入意向锁

死锁检测与处理

1. 死锁检测

2. 超时机制

3. 死锁处理策略

4. 查看死锁信息

如何避免死锁

1. 按相同顺序访问资源

2. 尽量使用索引访问数据

3. 减小事务粒度，缩短事务时间

4. 使用较低的隔离级别

5. 为表添加合理的索引

6. 避免大事务

7. 使用 SELECT ... FOR UPDATE 要慎重

锁的监控与诊断

1. 查看当前锁等待

MySQL 5.7

MySQL 8.0+

2. 查看详细的锁信息

3. 分析锁等待时间

4. 杀死阻塞的会话

面试高频问题

Q1: MySQL 有哪些锁？

Q2: 什么是间隙锁和临键锁？

Q3: 不同 SQL 语句如何加锁？

Q4: 如何避免死锁？

Q5: 为什么 InnoDB 的锁是加在索引上的？

Q6: 意向锁的作用是什么？

Q7: RC 和 RR 隔离级别的锁有什么区别？

总结

核心要点

学习建议

记住这些关键点