一、线上问题：更新一个不存在的值，竟锁住“未来数据”？ #
最近朋友遇到一个匪夷所思的线上故障：一条看似普通的 SQL UPDATE user SET status=1 WHERE biz_id=999（假设表中当前最大 biz_id=300），竟然导致后续所有 biz_id > 300 的插入请求全部阻塞！更夸张的是，这个操作甚至锁住了 biz_id=300（存在的记录）的插入请求。明明更新的值不存在，为何会触发“锁扩散”？
问题复现 #
假设表中 biz_id 为非唯一索引，现有数据如下：
| id（主键） | biz_id |
|---|---|
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
执行以下操作：
-- 事务1（RR隔离级别）
BEGIN;
UPDATE user SET status=1 WHERE biz_id=999; -- 更新一个不存在的值
-- 此时未提交事务！
-- 事务2
INSERT INTO user (biz_id) VALUES (400); -- 阻塞！
INSERT INTO user (biz_id) VALUES (500); -- 阻塞！
UPDATE user SET status=2 WHERE biz_id=300; -- 阻塞！
UPDATE user SET status=2 WHERE biz_id=200; -- 成功（不阻塞）
现象解释：
- 更新
biz_id=999（不存在且大于表中最大值）时，MySQL 会锁定biz_id的 [300, +∞)** 区间（即“无穷大锁”）。 - 由于
biz_id是非唯一索引，所有更大的biz_id插入操作被阻塞。 - 但
UPDATE biz_id=200仍能成功，因为该记录存在且未被间隙锁覆盖。
二、原理剖析：可重复读的“锁黑洞”从何而来？ #
1. 间隙锁（Gap Lock）的“左开右闭”原则 #
MySQL 的间隙锁遵循 Next-Key Lock 机制，锁定范围是 左开区间，右闭区间。例如：
- 表中存在
biz_id=100, 200, 300； - 查询
WHERE biz_id=150（不存在），会锁定区间 (100, 200)； - 查询
WHERE biz_id > 200，会锁定 (200, 300] + (300, +∞)**。
2. 无穷大锁：索引末尾的“禁区” #
当查询条件 超过表中最大值 时，MySQL 会在索引末尾生成一个 (max_value, +∞) 的锁区间，即“无穷大锁”。例如：
- 表中最大
biz_id=300，查询WHERE biz_id=999； - 锁定区间为 (300, +∞)，导致所有
biz_id > 300的插入被阻塞。
3. 非唯一索引的“锁扩散”陷阱 #
若使用 非唯一索引，MySQL 无法精准定位目标行，只能通过间隙锁“暴力”锁定整个区间。例如：
-- biz_id 是非唯一索引
UPDATE user SET status=1 WHERE biz_id=200;
即使 biz_id=200 存在，MySQL 仍会锁定 (100, 200] 和 (200, 300] 区间（具体范围取决于数据分布），导致相邻区间的插入操作被阻塞，但 更新同一记录的操作仍可正常执行。
三、避坑指南：如何绕过“锁黑洞”？ #
1. 索引设计的黄金法则 #
| 操作 | 错误姿势 | 正确姿势 |
|---|---|---|
| 按业务ID更新 | WHERE biz_id=200（非唯一） |
WHERE id=3（主键） |
| 按范围查询 | WHERE biz_id > 200 |
拆分为多次主键查询 + 内存过滤 |
| 软删除 | 硬删除导致索引空洞 | is_deleted=1 + 定期归档 |
2. 硬删除 vs 软删除：生死抉择 #
-
硬删除的代价：
DELETE FROM user WHERE biz_id=200; -- 删除记录 -- 后续查询 WHERE biz_id=200 会锁定 (100, 300) 区间！ -
软删除的优势：
UPDATE user SET is_deleted=1 WHERE biz_id=200; -- 保留索引记录 -- 后续查询命中索引，仅加行锁注意：需为
is_deleted建立复合索引（如(biz_id, is_deleted)），避免全表扫描。
3. 隔离级别：壮士断腕的终极方案 #
-
切换到读已提交（RC）：
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;- 优势：彻底禁用间隙锁，解决锁扩散问题；
- 代价：需业务层处理幻读（如通过版本号或分布式锁）。
-
主从复制配置：
-- 必须使用 ROW 格式 SET GLOBAL binlog_format = 'ROW';
像我司就是默认采用读已提交，幻读交给程序自己处理，对高并发核心资源（如库存），可引入 Redis 锁
四、终极拷问：为什么 MySQL 默认用可重复读？ #
1. 历史原因：主从复制的“旧伤” #
-
MySQL 5.1 之前：主从复制仅支持 Statement 格式（记录 SQL 语句）。若使用 RC 级别，主从可能因幻读导致数据不一致。
-- 主库执行： DELETE FROM user WHERE age > 20; -- 从库重放时，若中间插入新记录，删除范围可能不一致 -
现代方案：MySQL 5.1+ 支持 Row-Based 复制（记录数据变更），但默认隔离级别未调整。
2. 一致性优先的保守设计 #
RR 级别通过间隙锁在数据库层面保证强一致性，适合传统业务场景。但对互联网高并发应用，这种“宁可错杀一千”的锁机制往往成为性能瓶颈。
五、总结：可重复读的正确打开方式 #
| 场景 | 策略 | 示例 |
|---|---|---|
| 高频更新的非核心数据 | RC + 业务层防幻读 | 用户浏览记录、日志流水 |
| 金融交易类核心数据 | RR + 唯一索引 + 软删除 | 账户余额、订单状态 |
| 范围查询频繁的大表 | 拆分子表 + 避免全表扫描 | 按月份分表存储操作日志 |
| 分布式系统 | RC + 分布式锁 + 异步补偿 | 电商库存扣减（Redis 锁控制并发，MQ 消息确保最终一致） |
最后的忠告：
- 永远不要用非唯一索引作为更新条件（除非你能接受锁扩散）；
- 硬删除是间隙锁的“最佳助攻”——重要数据务必软删除；
- 监控锁争用：定期检查
SHOW ENGINE INNODB STATUS，关注LATEST DETECTED DEADLOCK日志。
附录：亲手体验“无穷大锁”的威力
CREATE TABLE `user` (
`id` BIGINT UNSIGNED NOT NULL AUTO_INCREMENT COMMENT '主键ID',
`biz_id` BIGINT UNSIGNED NOT NULL COMMENT '业务ID（非唯一索引）',
`status` TINYINT NOT NULL DEFAULT '0' COMMENT '状态：0-未处理,1-已处理',
`is_deleted` TINYINT NOT NULL DEFAULT '0' COMMENT '软删除标记：0-未删除,1-已删除',
`gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '创建时间',
`gmt_modified` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT '修改时间',
PRIMARY KEY (`id`),
KEY `idx_biz_id` (`biz_id`) COMMENT '非唯一业务ID索引',
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='用户表（演示间隙锁问题）';
INSERT INTO user (biz_id) VALUES (100),(200),(300);
```
```sql
-- 会话1（RR隔离级别）
BEGIN;
SELECT * FROM user WHERE biz_id=999 FOR UPDATE; -- 锁定 (300, +∞)
-- 会话2（尝试插入更大值，观察阻塞）
INSERT INTO user (biz_id) VALUES (400); -- 被阻塞！
INSERT INTO user (biz_id) VALUES (999); -- 被阻塞！
通过这个实验，你会发现：在可重复读级别下，一个不存在的查询可能让数据库的“未来”彻底冻结。