MySQL 8.0 事务隔离级别与一致性模型：REPEATABLE READ、Gap Lock 与可复现实验

本文围绕工程实践中最易混淆的 REPEATABLE READ 语义展开，结合可执行的表结构与并发场景，演示 Next-Key/Gap Lock 如何保障一致性并给出写法建议与性能权衡。

## 适用版本与前提

• MySQL 8.0.18+（支持 `EXPLAIN ANALYZE` 与更完善的 InnoDB 行锁）。
• `innodb_next_key_locks=ON`（默认），用于避免幻读。

## 实验表与数据

CREATE TABLE accounts (
  id BIGINT PRIMARY KEY AUTO_INCREMENT,
  user_id BIGINT NOT NULL,
  balance DECIMAL(10,2) NOT NULL,
  created_at TIMESTAMP NOT NULL,
  KEY idx_user_created (user_id, created_at)
) ENGINE=InnoDB;

INSERT INTO accounts(user_id, balance, created_at) VALUES
 (1001, 100.00, '2024-01-01 10:00:00'),
 (1001, 50.00,  '2024-01-01 11:00:00'),
 (1002, 80.00,  '2024-01-01 12:00:00');

## 并发场景 1：避免幻读的 Next-Key/Gap Lock

会话 A（事务内范围读）：

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT id, balance FROM accounts
WHERE user_id=1001 AND created_at BETWEEN '2024-01-01 09:00:00' AND '2024-01-01 13:00:00'
FOR UPDATE;

会话 B（试图插入落在范围内的行）：

INSERT INTO accounts(user_id, balance, created_at)
VALUES(1001, 20.00, '2024-01-01 10:30:00');
-- 期望：阻塞，直到会话 A 提交；Next-Key/Gap Lock 生效

解读：范围带锁读在复合索引上形成 Next-Key/Gap Lock，避免幻读与并发插入破坏一致性。

## 并发场景 2：非索引列范围读导致锁范围扩大

START TRANSACTION;
SELECT * FROM accounts WHERE balance BETWEEN 10 AND 200 FOR UPDATE;

注意：若缺乏对 `balance` 的合适索引，InnoDB 可能退化为更广的锁范围甚至表锁，建议基于访问模式设计索引。

## 读写分离一致性注意事项

• 事务性读取（含 `FOR UPDATE`）应在写库执行；配合中间件进行事务粘滞，避免在从库产生不可预期的锁行为。
• 在强一致读场景（读取后立即更新/决策）显式走写库。

## 验证与可视化

SHOW ENGINE INNODB STATUS\G
SELECT * FROM performance_schema.events_transactions_current\G

结合 `SHOW ENGINE INNODB STATUS` 观察锁等待与死锁信息，验证并发控制是否符合预期。

## 写法与索引建议

• 范围锁尽量绑定在高选择性的复合索引上，避免扩大锁范围。
• 使用主键或唯一索引进行点锁（`WHERE id=? FOR UPDATE`）降低冲突概率。
• 减少长事务，缩短锁持有时间；将慢计算移出事务。

## 相关文章（同分类热门）

• [MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南](./MySQL 慢查询诊断与 EXPLAIN ANALYZE 实战指南.md)
• [MySQL InnoDB 事务与锁的深度解析与最佳实践](./MySQL InnoDB 事务与锁的深度解析与最佳实践.md)
• [ProxySQL 读写分离与连接池优化：MySQL 8.0 生产可验证实战指南](./ProxySQL 读写分离与连接池优化：MySQL 8.0 生产可验证实战指南.md)

## 结语

理解并正确使用 REPEATABLE READ 与 Next-Key/Gap Lock，是在高并发环境下保障一致性与性能的关键。通过索引设计与事务边界优化，可显著降低冲突与幻读风险。