死锁现场还原

业务：点赞操作需原子性更新文章热度。

START TRANSACTION;
INSERT INTO user_praise(uid, plan_id, stage_id) VALUES (123456, 14456, 10023);
UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = 14456;
COMMIT;

上线后报错：

Deadlock found when trying to get lock; try restarting transaction
SQL: UPDATE plan_hot SET hot = hot + 1 WHERE plan_id = ?

根因深度剖析

表结构

CREATE TABLE `coupon` (
    `id` INT PRIMARY KEY AUTO_INCREMENT,
    `spec_id` CHAR(20) NOT NULL,
    `coup_usr` CHAR(11) DEFAULT NULL,
    `coup_num_usr` INT UNSIGNED NOT NULL DEFAULT 0,
    KEY `coup_usr_idx` (`coup_usr`),
    KEY `spec_idx` (`spec_id`)
) ENGINE=InnoDB;

死锁形成链条

1. 事务 A 执行 UPDATE ... WHERE coup_usr=? AND spec_id=?，命中二级索引 coup_usr_idx，加 X 锁锁定二级索引记录。
2. 事务 A 需回表聚集索引更新行，请求主键索引 X 锁。
3. 事务 B 近同时执行相同 SQL，但优化器选择 spec_idx，先锁二级索引 spec_idx，再请求主键锁。
4. 事务 A 持有 coup_usr_idx 锁等主键锁，事务 B 持有 spec_idx 锁等主键锁 → 循环等待 → 死锁。

核心规律：InnoDB 行锁加锁顺序 = 索引扫描顺序。不同索引扫描顺序不一致，极易形成交叉等待。

解决方案实战

方案一：拆分“查主键”+“按主键更新”（推荐）

-- 步骤 1：仅查主键，无锁（快照读）
SELECT id FROM coupon
WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr < ?;

-- 步骤 2：按主键逐条更新，加锁顺序固定为主键顺序
UPDATE coupon SET coup_num_usr = coup_num_usr + 1
WHERE id = ? AND coup_num_usr < ?;

• SELECT 默认 MVCC 快照读，不加锁，消除第一阶段锁竞争。
• UPDATE ... WHERE id=? 直接走聚集索引，所有事务按相同主键顺序加锁，彻底杜绝死锁。
• 应用层循环执行，批量时可拼 IN (id1,id2...)。

方案二：乐观锁（版本号/时间戳）

ALTER TABLE coupon ADD COLUMN version INT UNSIGNED NOT NULL DEFAULT 0;

UPDATE coupon
SET coup_num_usr = coup_num_usr + 1, version = version + 1
WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr < ? AND version = ?;

• 更新时校验版本号，冲突则 rowcount=0，应用层重试。
• 适合冲突率低、可容忍重试的场景。

方案三：强制索引统一加锁顺序

UPDATE coupon FORCE INDEX (coup_usr_idx)
SET coup_num_usr = coup_num_usr + 1
WHERE coup_usr = ? AND spec_id = ? AND coup_num_usr < ?;

• 强制所有事务走同一二级索引，加锁顺序一致。
• 风险：优化器若判断全表扫描更优会忽略 FORCE INDEX，不够稳健。

并发更新最佳实践清单

1. 高并发更新必须带主键，优先获取聚集索引锁。
2. 避免多二级索引同时出现在 WHERE 中，导致优化器选择不确定。
3. 大批量更新分批提交，每批 500-1000 行，减少锁持有时间。
4. 死锁不可完全避免，应用层实现指数退避重试（如 10ms, 50ms, 200ms...）。
5. 监控 Innodb_deadlocks 指标，定期 SHOW ENGINE INNODB STATUS 分析死锁日志。