MySQL 事务与锁实战:隔离级别选择、死锁模式识别与高争用写路径
业务库最常见的两类事故是"读到了不该读的数据"和"更新互相卡死"。MySQL InnoDB 的 MVCC + 两阶段锁模型能力足够,但默认 RR(可重复读)+ 间隙锁的组合在电商库存、计数器等高争用场景会制造大量意外等待。本文按"隔离级别怎么选 → 锁到底锁了什么 → 死锁怎么定位与预防 → 热点写路径怎么改造"的顺序给一套实操方案。
一、隔离级别:默认 RR 未必是你的最优解
InnoDB 四个级别里生产只用两个:**RR(默认)**与 RC(读提交)。差异的实际后果:
| 维度 | RR | RC |
|---|---|---|
| 一致性读快照 | 事务首次读建立,整事务一致 | 每条语句一个新快照 |
| 间隙锁/next-key | 有(防幻读) | 基本无(仅外键/唯一检查) |
| binlog 兼容性 | 任意格式 | 需 ROW 格式(5.1+ 默认已是) |
| 典型事故 | 间隙锁导致并发插入互相阻塞 | 不可重复读引发业务逻辑错 |
判据很简单:需要"范围扫描后写回"一致性的(对账、聚合更新)用 RR;OLTP 点查点写 + 高并发插入的用 RC 更顺——阿里系大规模跑 RC 正是因为间隙锁在分库分表场景制造太多死锁。切换是全局参数,必须连同应用测试一起评估:
SET GLOBAL transaction_isolation = 'READ-COMMITTED'; -- 动态可改;写入 my.cnf 持久化
SERIALIZABLE 在生产基本不该出现;READ UNCOMMITTED 只在报表脏读可接受时考虑。
二、锁到底锁了什么:行锁、间隙锁、隐式锁
三个高频误判点:
UPDATE ... WHERE 非索引列→ 全表扫描,所有扫过的行都加锁(RR 下还有间隙)。更新语句的 WHERE 必须命中索引,写进代码规范;- 二级索引更新会连带锁主键与相关索引记录,改一个 indexed 列的代价远高于直觉;
- 唯一键冲突插入拿 S 锁:并发
INSERT撞唯一键时失败方可能持共享锁等回滚,形成死锁的经典原料。
观察当前锁等待(8.0+):
SELECT waiting_trx_id, blocking_trx_id,
wt.trx_query AS waiting_sql, bt.trx_query AS blocking_sql
FROM performance_schema.data_lock_waits w
JOIN information_schema.innodb_trx wt ON wt.trx_id = w.requesting_engine_transaction_id
JOIN information_schema.innodb_trx bt ON bt.trx_id = w.blocking_engine_transaction_id;
SHOW ENGINE INNODB STATUS 的 LATEST DETECTED DEADLOCK 段仍是死锁分析主入口:两个事务各自持有/等待的锁与 SQL 都在。
三、死锁的四种典型模式与拆解
模式一:交叉更新顺序。T1 更新 A→B,T2 更新 B→A。对策:全局固定加锁顺序(按主键升序处理多行),把"批量更新先排序"写进 DAO 层。
模式二:gap 插入冲突。两个事务向同一间隙插入再升级写锁。对策:改 RC、或插入用 INSERT IGNORE/唯一键幂等化缩短持锁窗口。
模式三:外键级联的隐藏加锁。子表操作隐式锁父表行。对策:外键约束在高并发核心链路可评估改为应用层校验 + 索引保证。
模式四:死锁发生在"读后写"间隙。SELECT ... FOR UPDATE 在二级索引上扫描时顺带 gap 锁,两个事务互相卡住范围。对策:FOR UPDATE 收窄到主键点查;或 NOWAIT/SKIP LOCKED(8.0+)快速失败:
-- 任务队列消费的标准写法:跳过被占用的行,永不互等
SELECT * FROM job_queue WHERE status='pending'
ORDER BY id LIMIT 10 FOR UPDATE SKIP LOCKED;
SKIP LOCKED 是队列型负载(异步任务、订单处理)的最优解——没有它,并发 worker 会互相排队甚至死锁。
四、热点行写路径改造:计数器与库存
"每个请求都 UPDATE 同一行"是 InnoDB 的物理上限问题(单行串行化)。三层对策按侵入性排序:
1. 合并更新:应用侧攒批,把 N 次 +1 合成一次 SET c = c + ?。浏览量、点赞数天然适合(本项目的 view_count 走事件接口就是这个思路的变体)。
2. 拆分热点:一行拆 K 行(shard_key = rand()%K),读时 SUM。库存扣减常用,代价是"读变贵 + 需要跨行事务"。
CREATE TABLE stock_shards (
sku_id BIGINT, shard TINYINT, qty INT,
PRIMARY KEY(sku_id, shard)
);
-- 扣 3:随机选足够分片 UPDATE,读 = SELECT SUM(qty)
3. 队列化串行:Redis/消息队列单线程消费扣减,DB 只做最终落库。一致性最弱(需补偿),吞吐最高。
选择判据:读多写少 → 合并;强一致扣减 → 拆分;允许异步收敛 → 队列。
五、长事务与锁等待的参数护栏
死锁自动检测会回滚一方,但锁等待超时默认 50s 足以拖垮线程池。参数基线:
innodb_lock_wait_timeout = 5 # 业务侧应快速失败重试,而不是等 50s
transaction_isolation = READ-COMMITTED
# 8.0.28+:事务空闲自动回滚防"开事务忘了提交"
information_schema 监控: SELECT * FROM information_schema.innodb_trx
WHERE NOW() - trx_started > INTERVAL 10 SECOND; # 长事务告警
应用层配套:事务边界最小化(RPC/HTTP 调用绝不放事务内)、连接池的 idle_in_transaction 检测、重试只对死锁错误码(1213)与锁超时(1205)做,且带退避上限。
六、验证方法:把锁行为测出来
上线前用两个并发会话模拟争用:
# session A
mysql> BEGIN; UPDATE stock SET qty=qty-1 WHERE sku_id=100; # 持锁不放
# session B(5s 后)
mysql> UPDATE stock SET qty=qty-1 WHERE sku_id=100; # 观察等待/超时行为
sysbench 的 oltp_update_index + 自定义热点分布(zipf)压计数器场景,对比"单行 vs 拆分行"的 TPS 曲线。锁问题的验证没有捷径:构造争用、观察 data_lock_waits、量化超时与吞吐拐点。
小结
InnoDB 锁工程的要点:隔离级别按负载选(OLTP 高并发插入考虑 RC)、更新必须命中索引、队列型消费用 SKIP LOCKED、热点写按"合并→拆分→队列"三层解;参数上把 innodb_lock_wait_timeout 压到秒级并配长事务告警,应用层只对 1205/1213 有限重试。死锁不是异常而是并发系统的常态——目标是让它在可检测、可回滚、可重放的范围内发生,而不是幻想消灭它。
评论(0)
登录 后参与评论。