第二十八章 锁机制深入
本章目标
通过本章学习，你将能够：
- 理解 MySQL 锁的分类体系：按粒度（全局/表/页/行）、按模式（S/X/IS/IX）、按算法（Record/Gap/Next-Key/Insert Intention）
- 掌握 InnoDB 行锁的工作原理，理解"锁加在索引上"这一关键概念
- 区分乐观锁和悲观锁的使用场景和实现方式
- 理解死锁的成因、检测机制和预防策略
- 掌握元数据锁（MDL）的概念以及它与 DDL 操作的交互
- 能够分析锁等待和死锁问题，使用相关工具定位锁冲突
- 在实际开发中选择正确的锁策略
前置知识
在学习本章之前，你需要：
- 深入理解 InnoDB 的索引结构（B+Tree、聚簇索引、二级索引）
- 理解事务的基本概念和 ACID 特性
- 了解事务隔离级别（特别是 REPEATABLE READ 和 READ COMMITTED）
- 理解并发编程中的基本概念（临界区、竞态条件、互斥）
- 了解上一章中 MVCC 的实现原理
28.1 为什么需要锁
28.1.1 并发访问的挑战
当多个客户端同时访问和修改同一份数据时，如果没有适当的并发控制，就会出现各种问题。
sql
-- 问题场景：两个用户同时购买同一件商品（库存为 1）
-- 时刻 T1：用户 A 的线程读取库存
SELECT quantity FROM inventory WHERE product_id = 100;
-- 结果: 1
-- 时刻 T2：用户 B 的线程读取库存（在用户 A 扣减之前）
SELECT quantity FROM inventory WHERE product_id = 100;
-- 结果: 1（也读到 1！）
-- 时刻 T3：用户 A 扣减库存
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;
-- 库存: 0
-- 时刻 T4：用户 B 也扣减库存（基于 T2 读到的 1）
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;
-- 库存: -1 ← 超卖了！
-- 锁的作用：在 T1 时锁定该行，T2 时 B 必须等待 A 完成操作后才能读取28.1.2 锁的本质
锁是数据库管理系统用来控制并发访问的机制：
- 读锁（共享锁）：允许多个读操作同时进行
- 写锁（排他锁）：写操作独占访问，阻止其他读写
- 意向锁：表级锁，表明事务在更细粒度（行）上加锁的意图
28.2 锁的粒度
28.2.1 全局锁
锁定整个数据库实例，使数据库处于只读状态。
sql
-- 加全局读锁
FLUSH TABLES WITH READ LOCK;
-- 此时整个数据库实例只允许读，所有写操作会被阻塞
-- 典型使用场景：全库逻辑备份（mysqldump --master-data 参数）
-- 确保在备份期间所有表的数据是一致的（基于同一个 binlog 位置）
-- 释放全局锁
UNLOCK TABLES;
-- 注意：
-- 1. 全局锁会阻塞所有写操作，对业务影响极大
-- 2. 在生产环境的主库上使用要极其谨慎
-- 3. 对于 InnoDB 表，mysqldump --single-transaction 使用 MVCC 快照，
-- 不会加全局锁，是更好的备份方式
-- 设置全局只读（另一种方式，更安全但限制更强）
SET GLOBAL read_only = ON; -- 非 SUPER 用户只读
SET GLOBAL super_read_only = ON; -- SUPER 用户也只读（MySQL 5.7.8+）28.2.2 表级锁
锁定整张表。MyISAM 存储引擎默认使用表级锁；InnoDB 在特定情况下也会使用。
sql
-- 显式表锁
LOCK TABLES
users READ, -- 对 users 表加读锁（其他会话可读，不可写）
orders WRITE; -- 对 orders 表加写锁（其他会话不可读写）
-- 执行需要表锁保护的操作
SELECT * FROM users;
INSERT INTO orders VALUES (...);
-- 释放表锁
UNLOCK TABLES;
-- InnoDB 中表锁的使用场景：
-- 1. ALTER TABLE（DDL 操作）
-- 2. LOCK TABLES 显式锁定
-- 3. 意向锁（IS/IX）：自动管理，用户不直接操作
-- 注意：
-- 1. LOCK TABLES 会隐式提交当前事务
-- 2. InnoDB 中应尽量使用行锁而非表锁
-- 3. 使用 LOCK TABLES 时，只能访问被锁定的表28.2.3 行级锁（InnoDB）
InnoDB 默认使用行级锁，锁定粒度最细，并发性能最好。
sql
-- 行锁通过以下方式获取：
-- 1. 当前读：SELECT ... FOR UPDATE / SELECT ... FOR SHARE
-- 2. DML 操作：INSERT / UPDATE / DELETE
-- 示例：行锁自动获取
START TRANSACTION;
UPDATE users SET email = 'new@test.com' WHERE id = 1;
-- InnoDB 自动对 id=1 的行加 X 锁
-- 其他事务对 id=1 的写操作将被阻塞
COMMIT; -- 提交时释放所有行锁锁粒度对比：
锁类型 粒度 并发度 开销 死锁风险 适用引擎
全局锁 整个DB 最低 最小 无 所有
表锁 表 低 小 低 MyISAM, InnoDB
页锁 页 中 中 中 BDB（已废弃）
行锁 行 高 大 高 InnoDB28.3 锁的模式
28.3.1 共享锁和排他锁
共享锁（S Lock / Shared Lock）：
- 允许事务读取一行数据
- 多个事务可同时持有同一行的 S 锁
- 持有 S 锁时，其他事务不能获取 X 锁（不能修改）
排他锁（X Lock / Exclusive Lock）：
- 允许事务更新或删除一行数据
- 一个事务持有 X 锁时，其他事务不能获取任何锁（S 或 X）
- 其他事务的读操作（快照读）不受影响sql
-- S 锁获取方式
SELECT * FROM users WHERE id = 1 FOR SHARE; -- MySQL 8.0+
SELECT * FROM users WHERE id = 1 LOCK IN SHARE MODE; -- MySQL 5.7/8.0
-- X 锁获取方式
SELECT * FROM users WHERE id = 1 FOR UPDATE;
UPDATE users SET name = 'Bob' WHERE id = 1;
DELETE FROM users WHERE id = 1;
INSERT INTO users VALUES (...); -- 插入的行本身被加 X 锁28.3.2 锁兼容性矩阵
| S (持有) | X (持有) | IS (持有) | IX (持有) |
--------|---------|---------|----------|----------|
请求 S | ✓ | ✗ | ✓ | ✗ |
请求 X | ✗ | ✗ | ✗ | ✗ |
请求 IS | ✓ | ✗ | ✓ | ✓ |
请求 IX | ✗ | ✗ | ✓ | ✓ |
关键规则：
- S + S = ✓（读读兼容）
- S + X = ✗（读写互斥）
- X + X = ✗（写写互斥）
- IS/IX 之间总是兼容（意向锁只是标记，不阻塞）28.3.3 意向锁（Intention Lock）
意向锁是 InnoDB 自动管理的表级锁，用于表示事务打算在表中的某些行上加锁。
意向共享锁（IS Lock）：
- 事务打算在表中某些行上加 S 锁
- SELECT ... FOR SHARE 会自动在表上加 IS 锁
意向排他锁（IX Lock）：
- 事务打算在表中某些行上加 X 锁
- SELECT ... FOR UPDATE / INSERT / UPDATE / DELETE 会自动在表上加 IX 锁
意向锁的作用：
- 允许表级锁请求（如 LOCK TABLES ... WRITE）快速判断表中是否有行锁
- 不需要扫描所有行来检查是否有行锁
- 意向锁之间总是兼容的
流程示例：
事务想要 LOCK TABLES t WRITE（表级 X 锁）：
1. 检查表 t 上是否有其他事务持有不相容的表锁
2. 如果没有 → 成功
3. 但还要确保没有行锁（行锁与表级 X 锁冲突）
4. 检查意向锁：如果有 IS 或 IX，说明有行锁 → 阻止表级 X 锁
5. 无需遍历所有行检查行锁状态28.4 InnoDB 行锁算法
28.4.1 Record Lock（记录锁）
锁定单个索引记录。这是最基本的行锁。
sql
-- Record Lock：精准锁定索引记录本身
SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- 锁定 id=10 这条索引记录（在聚簇索引上）
-- 在唯一索引上使用等值查询时，如果记录存在，使用 Record Lock
-- 不会锁定间隙28.4.2 Gap Lock（间隙锁）
锁定索引记录之间的间隙，但不锁定记录本身。只在 REPEATABLE READ 及以上隔离级别生效。
sql
-- 假设 id 值有：5, 10, 15, 20
-- 间隙为：(-∞, 5), (5, 10), (10, 15), (15, 20), (20, +∞)
-- Gap Lock 锁定的是这些间隙，阻止其他事务在间隙中插入新记录
-- 示例：锁定 id=10 前面的间隙
SELECT * FROM users WHERE id = 12 FOR UPDATE;
-- id=12 不存在 → 锁定间隙 (10, 15)
-- 其他事务不能插入 id=11, 12, 13, 14
-- 目的：防止幻读（防止其他事务在这个间隙插入符合条件的新行）
-- Gap Lock 的特点：
-- 1. 不锁定记录本身
-- 2. 多个事务可以同时持有同一个间隙的 Gap Lock（Gap Lock 之间不互斥）
-- 3. Gap Lock 的目的是防止 INSERT，而非防止 UPDATE/DELETE
-- 4. 在 READ COMMITTED 隔离级别下，Gap Lock 被禁用28.4.3 Next-Key Lock（临键锁）
Record Lock + 它前面的 Gap Lock 的组合。是 InnoDB 在 REPEATABLE READ 下的默认行锁算法。
sql
-- 假设 id 值有：5, 10, 15, 20
-- Next-Key Lock 区间：( -∞, 5], (5, 10], (10, 15], (15, 20], (20, +∞)
-- SELECT * FROM users WHERE id = 10 FOR UPDATE;
-- Next-Key Lock 锁定：(5, 10] -- 间隙 (5, 10) + 记录 id=10
-- SELECT * FROM users WHERE id BETWEEN 8 AND 12 FOR UPDATE;
-- 锁定：
-- (5, 10] (10, 15] -- 两个 Next-Key Lock
-- 以及间隙 (15, 20) -- Gap Lock（supremum 伪记录之前的间隙）
-- 为什么是"前面的"间隙？
-- InnoDB 的索引按升序排列，每个 Next-Key Lock 锁定的是：
-- "当前记录 + 当前记录与前一条记录之间的间隙"
-- 这样就覆盖了所有可能插入的位置28.4.4 Insert Intention Lock（插入意向锁）
一种特殊的 Gap Lock，在 INSERT 操作之前自动设置。
sql
-- 当多个事务尝试在同一个间隙中插入不同位置的记录时：
-- 事务 A：INSERT INTO t VALUES (13); → 在间隙 (10, 15) 中设置插入意向锁
-- 事务 B：INSERT INTO t VALUES (14); → 在间隙 (10, 15) 中设置插入意向锁
-- 插入意向锁之间不互斥！
-- 事务 A 插入 id=13 和事务 B 插入 id=14 可以并发进行
-- 只要它们插入的是不同的记录
-- 但是插入意向锁与 Gap Lock 互斥：
-- 如果事务 C 持有间隙 (10, 15) 的 Gap Lock
-- 事务 A 的 INSERT 会被阻塞（无法设置插入意向锁）28.4.5 加锁规则总结
sql
-- 加锁规则取决于：隔离级别 + 索引类型 + 查询类型
-- 核心原则：行锁加在索引上！
-- 如果没有合适的索引，InnoDB 会锁定所有行！
-- 规则 1：唯一索引 + 等值查询 + 记录存在 → Record Lock
SELECT * FROM t WHERE pk = 10 FOR UPDATE;
-- 只锁 pk=10 这一条记录
-- 规则 2：唯一索引 + 等值查询 + 记录不存在 → Gap Lock
SELECT * FROM t WHERE pk = 12 FOR UPDATE;
-- pk=12 不存在，锁间隙
-- 规则 3：唯一索引 + 范围查询 → Next-Key Lock
SELECT * FROM t WHERE pk > 10 FOR UPDATE;
-- 对所有符合条件的记录加 Next-Key Lock
-- 规则 4：非唯一索引 + 等值查询 → Next-Key Lock（可能多个）
SELECT * FROM t WHERE age = 25 FOR UPDATE;
-- age 是非唯一索引，age=25 可能有多行
-- 对每个 age=25 的行加 Next-Key Lock
-- 并锁定最后一个 age=25 行之后的下一个间隙
-- 规则 5：非唯一索引 + 范围查询 → Next-Key Lock
-- 规则 6：无索引 → 对所有行加 Next-Key Lock（实际效果接近表锁！）
-- 这是最常见的性能杀手：
SELECT * FROM users WHERE name = 'Alice' FOR UPDATE;
-- 如果 name 没有索引 → 全表所有行都被加锁！
-- 其他事务的任何 INSERT/UPDATE/DELETE 都可能被阻塞28.5 锁加在索引上 —— 关键理解
28.5.1 锁的是索引记录，不是数据行
sql
-- InnoDB 的行锁不是锁在"数据行"上，而是锁在"索引记录"上
-- 示例：通过主键更新
UPDATE users SET name = 'Bob' WHERE id = 10;
-- 锁的是：聚簇索引中 id=10 的索引记录
-- 示例：通过二级索引更新
UPDATE users SET name = 'Bob' WHERE email = 'alice@test.com';
-- 锁的是：idx_email 索引中 email='alice@test.com' 的记录
-- + 聚簇索引中对应的主键记录（确保数据行不会在回表时被修改）28.5.2 无索引时的灾难
sql
-- 创建测试表（无 name 索引）
CREATE TABLE test_lock (
id INT PRIMARY KEY,
name VARCHAR(50),
value INT
);
INSERT INTO test_lock VALUES (1, 'Alice', 100), (2, 'Bob', 200), (3, 'Charlie', 300);
-- 会话 A：
START TRANSACTION;
UPDATE test_lock SET value = 150 WHERE name = 'Alice';
-- name 上没有索引 → 全表扫描 → 锁住所有行！
-- 会话 B：
START TRANSACTION;
UPDATE test_lock SET value = 250 WHERE id = 2;
-- 被阻塞！虽然 id=2 与 name='Alice' 无关
-- 但因为会话 A 没有索引，锁了所有行的聚簇索引记录
-- 会话 C：
INSERT INTO test_lock VALUES (4, 'David', 400);
-- 也被阻塞！
-- 教训：WHERE 条件中的列必须有索引！
-- 否则 UPDATE/DELETE 可能锁住全表28.5.3 不同索引的锁范围
sql
-- 表结构：
-- id (PRIMARY KEY), name (INDEX), age (UNIQUE INDEX)
-- 通过主键更新 → 只锁一行
UPDATE users SET status = 'active' WHERE id = 10;
-- 只锁 id=10 的主键记录
-- 通过唯一索引更新 → 锁两处
UPDATE users SET status = 'active' WHERE email = 'a@test.com';
-- 锁：1) idx_email 索引中 email='a@test.com' 的记录
-- 2) 聚簇索引中对应的主键记录
-- 通过非唯一索引更新 → 锁多行
UPDATE users SET status = 'active' WHERE name = 'Alice';
-- 锁：1) idx_name 索引中所有 name='Alice' 的记录
-- 2) 聚簇索引中对应的所有主键记录
-- 如果 name='Alice' 有 100 行 → 锁 200 个索引记录28.6 元数据锁（MDL - Metadata Lock）
28.6.1 MDL 的概念
元数据锁用于保护表结构定义不被并发修改。当事务访问表时，自动获取 MDL 读锁；当执行 DDL 时，需要获取 MDL 写锁。
sql
-- MDL 读锁（MDL_SHARED_READ）：
-- 在事务中执行 SELECT / INSERT / UPDATE / DELETE 时自动获取
-- 多个事务可以同时持有同一张表的 MDL 读锁
-- MDL 写锁（MDL_EXCLUSIVE）：
-- 在 ALTER TABLE / DROP TABLE 时自动获取
-- 必须等待所有 MDL 读锁释放后才能获取
-- 这就是为什么 ALTER TABLE 会阻塞在长事务后面！
-- 场景复现：
-- 会话 A：开启了一个长时间运行的事务
START TRANSACTION;
SELECT * FROM orders WHERE id = 1; -- 获取了 orders 的 MDL 读锁
-- ... 没有 COMMIT，事务一直保持活跃 ...
-- 会话 B：尝试执行 DDL
ALTER TABLE orders ADD COLUMN new_field INT;
-- 被阻塞！等待会话 A 释放 MDL 读锁
-- 会话 C 及之后所有的 DML：
SELECT * FROM orders WHERE id = 2;
-- 也被阻塞！（等待在会话 B 的 MDL 写锁后面）
-- 整个表的所有操作都被阻塞了！28.6.2 MDL 锁等待排查
sql
-- MySQL 8.0+：查看 MDL 锁等待
SELECT * FROM performance_schema.metadata_locks
WHERE OBJECT_SCHEMA = 'mydb' AND OBJECT_NAME = 'orders';
-- 或使用 sys schema
SELECT * FROM sys.schema_table_lock_waits;
-- 找出阻塞源：
-- 通常是一个长时间未提交的事务持有 MDL 读锁
SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS duration_seconds,
trx_mysql_thread_id,
trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;28.6.3 MDL 问题的解决
sql
-- 1. 设置 lock_wait_timeout 防止无限等待
SET SESSION lock_wait_timeout = 10; -- 10 秒后超时
-- 2. 使用 pt-online-schema-change（Percona Toolkit）
-- 原理：创建新表 → 应用变更 → 通过触发器同步数据 → 原子切换
-- 不需要长时间持有 MDL 写锁
-- 3. 使用 gh-ost（GitHub 的在线 schema 迁移工具）
-- 原理：创建影子表 → 读取 binlog 同步 → 切换
-- 同样避免长时间 MDL 写锁
-- 4. 在业务低峰期执行 DDL
-- 5. 使用 ALGORITHM=INPLACE（如果支持）而非 ALGORITHM=COPY
ALTER TABLE orders ADD COLUMN new_field INT, ALGORITHM=INPLACE;
-- 6. 对于 MySQL 8.0，许多 DDL 支持 INSTANT 算法（立即生效）
ALTER TABLE orders ADD COLUMN new_field INT, ALGORITHM=INSTANT;28.7 死锁（Deadlock）
28.7.1 死锁的产生
死锁发生在两个或多个事务相互等待对方释放锁的情况，形成循环等待。
经典死锁场景：
事务 A： 事务 B：
锁定资源 1 锁定资源 2
等待资源 2 ←────────────────→ 等待资源 1
双方都在等待对方释放锁 → 死锁！sql
-- 死锁复现：
-- 会话 A：
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 锁定 id=1
-- 此时不提交
-- 会话 B：
START TRANSACTION;
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 锁定 id=2
-- 此时不提交
-- 会话 A：
UPDATE accounts SET balance = balance + 100 WHERE id = 2; -- 等待 id=2
-- 会话 A 等待会话 B 释放 id=2 的锁
-- 会话 B：
UPDATE accounts SET balance = balance - 100 WHERE id = 1; -- 等待 id=1
-- 会话 B 等待会话 A 释放 id=1 的锁
-- → 死锁！InnoDB 检测到后自动回滚其中一个事务
-- 通常回滚持有最少 undo log 的事务（"成本最小"的事务）28.7.2 死锁检测和监控
sql
-- 1. 查看死锁信息
SHOW ENGINE INNODB STATUS\G
-- 查找 "LATEST DETECTED DEADLOCK" 部分
-- 包含详细的死锁信息：涉及的事务、持有的锁、等待的锁、回滚的事务
-- 2. 启用死锁日志（写入 MySQL 错误日志）
SET GLOBAL innodb_print_all_deadlocks = ON;
-- 3. 查看当前锁等待（MySQL 8.0+）
SELECT
r.trx_id AS waiting_trx,
r.trx_mysql_thread_id AS waiting_thread,
TIMESTAMPDIFF(SECOND, r.trx_wait_started, NOW()) AS wait_sec,
r.trx_query AS waiting_query,
b.trx_id AS blocking_trx,
b.trx_mysql_thread_id AS blocking_thread,
b.trx_query AS blocking_query
FROM information_schema.innodb_lock_waits w
JOIN information_schema.innodb_trx r ON w.requesting_trx_id = r.trx_id
JOIN information_schema.innodb_trx b ON w.blocking_trx_id = b.trx_id;
-- 4. 使用 sys schema（更友好）
SELECT * FROM sys.innodb_lock_waits;28.7.3 SHOW ENGINE INNODB STATUS 死锁解读
------------------------
LATEST DETECTED DEADLOCK
------------------------
2024-10-15 14:30:25 0x7f8b2c001700
*** (1) TRANSACTION:
TRANSACTION 421512345678, ACTIVE 5 sec starting index read
mysql tables in use 1, locked 1
LOCK WAIT 2 lock struct(s), heap size 1136, 1 row lock(s)
MySQL thread id 42, OS thread handle 140215689873152, query id 1234 localhost root updating
UPDATE accounts SET balance = balance + 100 WHERE id = 2
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `mydb`.`accounts` trx id 421512345678 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000001; asc ;; ← id=1 (事务 1 持有)
...
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 123 page no 4 n bits 72 index PRIMARY of table `mydb`.`accounts` trx id 421512345678 lock_mode X locks rec but not gap waiting
Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
0: len 4; hex 80000002; asc ;; ← id=2 (事务 1 在等待)
*** (2) TRANSACTION:
TRANSACTION 421512345679, ACTIVE 3 sec starting index read
...
*** (2) HOLDS THE LOCK(S):
0: len 4; hex 80000002; asc ;; ← id=2 (事务 2 持有)
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
0: len 4; hex 80000001; asc ;; ← id=1 (事务 2 在等待)
*** WE ROLL BACK TRANSACTION (2)
-- 解读：
-- 事务 1：持有 id=1 的锁，等待 id=2 的锁
-- 事务 2：持有 id=2 的锁，等待 id=1 的锁
-- InnoDB 回滚了事务 228.7.4 死锁预防策略
sql
-- 策略 1：按固定顺序访问资源（最重要！）
-- 错误：不同事务以不同顺序锁定资源
-- 事务 A：锁账户 1 → 锁账户 2
-- 事务 B：锁账户 2 → 锁账户 1 ← 不同顺序，容易死锁
-- 正确：始终按账户 ID 升序锁定
-- 事务 A：锁 MIN(1,2) → 锁 MAX(1,2) → 锁账户 1 → 锁账户 2
-- 事务 B：锁 MIN(2,1) → 锁 MAX(2,1) → 锁账户 1 → 锁账户 2 ← 相同顺序！
-- 实现方式：在应用层排序后按序处理
-- 或使用 SELECT ... FOR UPDATE ORDER BY id
-- 策略 2：保持事务短小
-- 事务时间越长，持有锁的时间越长，与其他事务冲突的概率越大
-- 策略 3：使用合适的索引
-- 没有索引时，InnoDB 锁定所有行 → 锁范围扩大 → 死锁概率增加
-- 策略 4：使用乐观锁代替悲观锁（适用低冲突场景）
-- 不持有锁，通过版本号检测冲突
-- 策略 5：应用层重试
-- 死锁无法 100%避免，应用层必须捕获死锁错误并重试28.7.5 应用层死锁处理
python
# Python 示例：死锁重试
import time
import mysql.connector
from mysql.connector import Error
def execute_with_retry(cursor, sql, params=None, max_retries=3):
"""执行 SQL，遇到死锁时自动重试"""
for attempt in range(max_retries):
try:
if params:
cursor.execute(sql, params)
else:
cursor.execute(sql)
return True
except Error as e:
if e.errno == 1213: # ER_LOCK_DEADLOCK
if attempt < max_retries - 1:
wait_time = 0.1 * (2 ** attempt) # 指数退避
print(f"Deadlock detected, retrying in {wait_time}s...")
time.sleep(wait_time)
continue
raise # 重试次数用尽，抛出异常
return False
def transfer_money(from_id, to_id, amount):
conn = mysql.connector.connect(host='localhost', user='root',
password='pass', database='bank')
conn.autocommit = False
cursor = conn.cursor()
try:
# 按固定顺序锁定（先小后大，避免死锁）
first = min(from_id, to_id)
second = max(from_id, to_id)
execute_with_retry(cursor,
"SELECT balance FROM accounts WHERE id IN (%s, %s) FOR UPDATE",
(first, second))
# ... 业务逻辑 ...
conn.commit()
except Exception as e:
conn.rollback()
raise
finally:
cursor.close()
conn.close()28.8 乐观锁与悲观锁
28.8.1 悲观锁（Pessimistic Locking）
假设会发生冲突，在读取数据时就直接加锁。
sql
-- 悲观锁实现：使用 FOR UPDATE
START TRANSACTION;
-- 读取时加 X 锁，阻止其他事务修改
SELECT quantity FROM inventory WHERE product_id = 100 FOR UPDATE;
-- 业务判断
-- ...
-- 更新
UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 100;
COMMIT;
-- 优点：数据一致性强，实现简单
-- 缺点：锁持有期间其他事务被阻塞，并发度低
-- 适用：冲突概率高的场景（如秒杀、库存扣减）28.8.2 乐观锁（Optimistic Locking）
假设不会发生冲突，在提交时才检查数据是否被修改过。
sql
-- 方式一：版本号（推荐）
-- 表结构添加 version 列
ALTER TABLE inventory ADD COLUMN version INT DEFAULT 0;
-- 乐观锁更新
START TRANSACTION;
-- 读取数据（不加锁！使用普通 SELECT）
SELECT quantity, version FROM inventory WHERE product_id = 100;
-- 假设 quantity=10, version=5
-- 业务判断（在应用层）
-- ...
-- 更新时检查版本号
UPDATE inventory
SET quantity = quantity - 1,
version = version + 1
WHERE product_id = 100 AND version = 5;
-- 如果 version 已经变了（被其他事务修改），WHERE 条件不成立
-- ROW_COUNT() = 0 → 说明发生了冲突
IF ROW_COUNT() = 0 THEN
ROLLBACK;
-- 返回冲突信息，提示用户重试
ELSE
COMMIT;
END IF;
-- 方式二：时间戳
UPDATE inventory
SET quantity = quantity - 1,
updated_at = NOW()
WHERE product_id = 100 AND updated_at = '2024-10-15 14:30:25';
-- 方式三：全字段比较（不推荐，SQL 冗长）
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 100
AND quantity = 10
AND product_name = 'Product A'
AND ...;
-- 优点：无锁等待，并发度高
-- 缺点：需要应用层处理冲突和重试，可能出现大量重试降低吞吐
-- 适用：冲突概率低的场景（如用户修改个人信息）28.8.3 乐观锁 vs 悲观锁 对比
特性 乐观锁 悲观锁
------------------------------------------------------
实现方式 version 字段 + 条件更新 SELECT ... FOR UPDATE
冲突检测 提交时检测 读取时检测
锁持有时间 无锁 整个事务持有
并发度 高（读不阻塞） 低（写阻塞读/写）
资源消耗 低 高（锁管理开销）
冲突处理 应用层重试 数据库层等待或超时
适用场景 读多写少，冲突概率低 写多，冲突概率高
回滚率 可能较高（大量冲突重试） 低
复杂度 需要 version 字段和应用逻辑 数据库原生支持28.9 锁相关配置和调试
28.9.1 关键配置参数
sql
-- 查看 InnoDB 锁相关配置
SHOW VARIABLES LIKE '%lock%';
SHOW VARIABLES LIKE '%deadlock%';
-- innodb_lock_wait_timeout：锁等待超时时间（秒），默认 50
-- 生产环境建议设置为 5-10 秒
SET GLOBAL innodb_lock_wait_timeout = 10;
-- innodb_deadlock_detect：是否检测死锁，默认 ON
-- 高并发场景下死锁检测本身可能成为瓶颈（尤其是 1000+ 并发连接时）
-- 极端情况可考虑关闭死锁检测：SET GLOBAL innodb_deadlock_detect = OFF;
-- 但关闭后需要依赖 innodb_lock_wait_timeout 来解除锁等待
-- innodb_print_all_deadlocks：将所有死锁信息记录到错误日志
SET GLOBAL innodb_print_all_deadlocks = ON;
-- innodb_status_output / innodb_status_output_locks
-- 将 InnoDB 监控信息输出到错误日志
SET GLOBAL innodb_status_output = ON;
SET GLOBAL innodb_status_output_locks = ON;28.9.2 监控当前锁状态
sql
-- 1. 查看当前所有事务
SELECT * FROM information_schema.innodb_trx\G
-- 2. 查看当前锁信息（MySQL 8.0）
SELECT * FROM performance_schema.data_locks\G
-- 显示：ENGINE, ENGINE_LOCK_ID, ENGINE_TRANSACTION_ID,
-- THREAD_ID, EVENT_ID, OBJECT_SCHEMA, OBJECT_NAME,
-- INDEX_NAME, LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
-- 3. 查看锁等待（MySQL 8.0）
SELECT * FROM performance_schema.data_lock_waits\G
-- 4. 综合锁信息（sys schema，MySQL 8.0）
SELECT * FROM sys.innodb_lock_waits; -- 当前锁等待
-- 5. 查看表锁
SHOW OPEN TABLES WHERE In_use > 0;28.9.3 实战：分析锁等待问题
sql
-- 场景：应用出现大量超时，怀疑是锁等待导致
-- 步骤 1：查看当前锁等待情况
SELECT
waiting_pid AS waiting_connection,
waiting_query,
blocking_pid AS blocking_connection,
blocking_query,
waiting_age -- 已等待时长
FROM sys.innodb_lock_waits
ORDER BY waiting_age DESC;
-- 步骤 2：查看阻塞源事务的详细信息
SELECT
trx_id,
trx_state,
trx_started,
TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS running_seconds,
trx_mysql_thread_id,
trx_query,
trx_rows_locked,
trx_rows_modified
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = <blocking_pid>;
-- 步骤 3：查看阻塞事务持有的锁
SELECT
OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME,
LOCK_TYPE, LOCK_MODE, LOCK_STATUS, LOCK_DATA
FROM performance_schema.data_locks
WHERE ENGINE_TRANSACTION_ID = <trx_id>;
-- 步骤 4：决策
-- - 如果阻塞事务长时间未活动且无重要操作 → KILL 该连接
-- - 如果是正常业务 → 分析 SQL，添加索引，优化事务逻辑
-- - 如果频繁发生 → 需要考虑降低隔离级别或调整应用逻辑常见错误
无索引的 WHERE 条件导致全表加锁
- 错误：
UPDATE orders SET status='expired' WHERE user_id=100 AND status='pending';，user_id 列上没有索引。 - 后果：InnoDB 对所有行加 X 锁（通过聚簇索引全表扫描），阻塞所有其他写操作，甚至阻塞 INSERT。
- 解决：为 WHERE 条件中的列创建合适的索引。使用 EXPLAIN 确认查询使用了索引。
- 错误：
不同事务以不同顺序访问资源导致死锁
- 错误：事务 A 先锁账户 1 再锁账户 2，事务 B 先锁账户 2 再锁账户 1，形成死锁环。
- 后果：频繁死锁，事务回滚率增高，用户体验差。
- 解决：所有事务按固定顺序访问资源（如按账户 ID 升序），或使用带有 NOWAIT 的锁定方式。
长事务导致 MDL 锁等待和锁堆积
- 错误：一个事务中既有 SELECT 又有外部 API 调用，事务持续数分钟甚至更久。
- 后果：事务持有 MDL 读锁不释放，后续的 DDL 操作被阻塞，DDL 又阻塞后续所有 DML 操作，形成雪崩效应。
- 解决：事务中只包含数据库操作，耗时逻辑移到事务外。设置
innodb_lock_wait_timeout为较小值。
误认为 SERIALIZABLE 可以解决所有并发问题
- 错误：遇到数据不一致问题就把隔离级别设为 SERIALIZABLE。
- 后果：性能严重下降，但问题可能仍然存在（如应用层的竞态条件）。
- 解决：只在需要绝对串行化的特定关键事务中使用 SERIALIZABLE，大多数场景通过 REPEATABLE READ + FOR UPDATE 即可满足。
乐观锁忘记检查 ROW_COUNT
- 错误：使用
UPDATE ... WHERE version = ?但不检查ROW_COUNT()，继续执行后续操作。 - 后果：更新失败时应用层不知情，可能导致数据不一致。
- 解决：每次乐观锁更新后检查影响行数，如果为 0 则回滚或通知用户重试。
- 错误：使用
本章练习
Record/Gap/Next-Key Lock 实验：在 REPEATABLE READ 级别下，创建一个测试表（id PRIMARY KEY, value INT, INDEX(value)），使用不同查询（唯一索引等值、非唯一索引等值、范围查询、不存在的值）执行
SELECT ... FOR UPDATE，观察并记录加锁范围，绘制加锁示意图。死锁复现与分析：在两个会话中，模拟一个经典的死锁场景。使用
SHOW ENGINE INNODB STATUS查看死锁信息，解读死锁报告中的关键信息（持有锁、等待锁、回滚事务）。乐观锁实现：在 products 表中添加 version 列，编写存储过程实现乐观锁的库存扣减：读取库存和版本号 → 检查库存 → 使用 version 进行条件更新 → 检查 ROW_COUNT 判断是否冲突 → 冲突时返回提示。模拟并发扣减验证正确性。
MDL 锁阻塞实验：在一个会话中开启事务并执行 SELECT（不提交），另一个会话中尝试 ALTER TABLE，观察阻塞情况；再从第三个会话执行 SELECT，观察是否也被阻塞。解释 MDL 锁的排队机制。
索引对锁范围的影响：对比有索引和无索引情况下，
UPDATE ... WHERE col = ?的加锁范围差异。使用performance_schema.data_locks查看具体锁信息，理解为什么无索引的写操作可能导致全文锁。综合锁策略选择：设计一个电商秒杀系统的库存扣减方案，对比以下三种方式的优劣：(1) 纯悲观锁（FOR UPDATE）；(2) 乐观锁（version）；(3) Redis 异步扣减 + MySQL 异步同步。分析各自的吞吐量、一致性保证、实现复杂度。