HDUO Lab

标签： Gap Lock

  • 深入 MySQL 陷阱排查：微型 Redo Log 引发的 Checkpoint 同步刷新与间隙锁雪崩实战

    近期处理了一起极为典型的数据库生产事故。某核心交易系统的 MySQL 集群在业务高峰期，呈现出诡异的“心跳骤停”现象：TPS 周期性从 4000 瞬间跌零，持续 3-5 秒后恢复，期间伴随海量的 Lock wait timeout exceeded; try restarting transaction 报错，上游微服务大面积阻塞。

    直接抛出结论：这是一起由极不合理的 Redo Log 容量配置引发的连锁雪崩。底层原因是 innodb_log_file_size 依然保持着陈旧的 128MB 默认值，高并发批量更新瞬间撑爆了 Redo Log 的 Checkpoint Age，触发了 InnoDB 最严厉的 Sync Flush（同步刷新）机制。用户线程被强制挂起等待脏页刷盘，而这些被挂起的线程手中正死死捏着二级索引的 Next-Key Lock（间隙锁），最终导致全表范围的插入和更新操作被全部阻塞，演变成全局锁雪崩。

    案发现场与“不可原谅”的默认值

    排查过程中，通过监控面板看到服务器的 NVMe SSD 处于半闲置状态（IOPS 峰值不到 15k），但 MySQL 的响应时间 P99 却飙升到了秒级。

    登入主机，抓取 SHOW ENGINE INNODB STATUS\G，核心日志段暴露了致命问题：

    ---
LOG
---
Log sequence number          28394058291
Log flushed up to            28394058291
Pages flushed up to          28393801000
Last checkpoint at           28393798000
Max checkpoint age           204800000
Checkpoint age target        198000000
Modified age                 260291    
Checkpoint age               260291    -- 注意这里的差值

    稍微懂点 InnoDB 内核机制的人，看到这里血压就上来了。给一台 32 核 128G、配着企业级 NVMe 盘的物理机部署 MySQL，居然连 innodb_log_file_size 都不改，留着 2 个 128MB 的文件。这就好比给法拉利装了个拖拉机的排气管，油门踩到底只能憋死发动机。

    底层原理解析：当 Redo Log 变成系统瓶颈

    InnoDB 的 Redo Log 是一个环形缓冲区（Ring Buffer）。系统必须保证在这个环里，写入新日志覆盖老日志之前，老日志对应的 Buffer Pool 脏页已经刷入磁盘。这个安全距离的标尺，就是 Checkpoint Age。

    Checkpoint Age = Log sequence number (LSN) - Last checkpoint at

    当高并发的 DML 语句涌入，LSN 飞速推进，而后台 page_cleaner 线程刷脏页的速度跟不上时，Checkpoint Age 就会不断膨胀。InnoDB 为此设定了三道防线：

    1. Async Watermark (75%)：超过此阈值，后台线程开始拼命（异步）刷脏页。

    2. Sync Watermark (90%)：一旦触及这条红线，灾难降临。InnoDB 会触发同步刷新（Sync Flush），直接挂起当前执行 DML 的用户线程，强行参与脏页刷盘，直到 Checkpoint Age 回落。

    在这个阶段，你的 TPS 会呈现断崖式下跌，直逼 0。更要命的是，用户线程是在持有锁的状态下被挂起的。

    间隙锁（Gap Lock）的推波助澜

    如果仅仅是 TPS 跌零几秒，上游服务顶多重试。但这套系统里，开发写了一个非常经典的“防御性”状态更新逻辑：

    UPDATE orders SET status = 2, update_time = NOW() 
WHERE status = 1 AND type = 'promotion' LIMIT 1000;

    status 字段是一个区分度极低的非唯一索引。在可重复读（RR）隔离级别下，InnoDB 为了防止幻读，会对命中的记录加 Record Lock，并对记录前后的间隙加 Gap Lock，统称 Next-Key Lock。

    当这个 UPDATE 语句跑到一半，刚好触发了 Redo Log 的 Sync Flush，该线程被无情挂起。此时，它持有了一个覆盖极大范围的间隙锁。接下来的 3-5 秒内：

    • 任何试图插入 status = 1 附近间隙的新订单请求，全部被阻塞在 WAITING FOR LOCK。

    • 其他业务流程因为拿不到锁，锁等待队列瞬间堆积，迅速打满 innodb_thread_concurrency，引发系统雪崩。

    破局与调优：拒绝花里胡哨，回归物理本质

    解决这种问题，不需要什么高深的分布式改造，只需要把 InnoDB 的 I/O 管道打通。

    1. 扩容 Redo Log 容量 确保 Redo Log 能够支撑业务高峰期至少 1 小时的写入量。在旧版本中需要优雅停机重建日志文件，如果是 MySQL 8.0.30+，可以直接动态调整：

    -- MySQL 8.0.30 及以上版本动态扩容至 4GB
SET GLOBAL innodb_redo_log_capacity = 4294967296;

    如果是旧版本，配置 my.cnf：

    innodb_log_file_size = 2G
innodb_log_files_in_group = 2

    2. 匹配硬件的 I/O 吞吐能力 SSD 时代，默认的 innodb_io_capacity = 200 就是在自废武功。让 page_cleaner 能够放开手脚刷脏页，避免 Checkpoint LSN 滞后：

    # 根据 NVMe 性能压测结果调整
innodb_io_capacity = 10000
innodb_io_capacity_max = 20000
# 调整刷脏线程数，通常与 Buffer Pool Instances 保持一致
innodb_page_cleaners = 8
innodb_buffer_pool_instances = 8

    3. 改造高危的 DML 语句 严禁在 RR 隔离级别下，通过非唯一索引进行大范围的批量更新。将批量操作改为通过主键驱动： 先 SELECT id FROM orders WHERE status = 1 LIMIT 1000，再 UPDATE orders SET status = 2 WHERE id IN (...)。这样只锁具体的行，彻底避开 Gap Lock 的踩踏。

    排查清单：InnoDB I/O 与锁雪崩速查

    1. 查 LSN 推进速率与 Checkpoint 延迟：使用 SHOW ENGINE INNODB STATUS 观察 Log sequence number 与 Last checkpoint at 的差值，若接近总 Log 容量的 80% 以上，存在极高 Sync Flush 风险。

    2. 查 Page Cleaner 状态：若 Error Log 中频繁出现 page_cleaner: 1000ms intended loop took XXXX ms，说明刷脏线程已成为瓶颈，检查 I/O 延迟或调大 innodb_io_capacity。

    3. 查 锁等待链与行锁升级：通过 SELECT * FROM performance_schema.data_locks;（MySQL 8.0）确认阻塞源头，重点排查持有 X, GAP 锁且事务持续时间较长的僵尸线程。

    4. 查 脏页水位线：监控 Buffer pool dirty pages 占比，若长期逼近 innodb_max_dirty_pages_pct（默认 90%），意味着 Buffer Pool 成为 I/O 抖动的蓄水池，需结合 I/O 调优或扩大 Buffer Pool 内存。