MySQL存储过程更新user_sessions表触发1213死锁问题求助

死锁根因分析 #

从死锁日志可以明确核心是同一行资源的S锁转X锁循环等待：
两个并发事务操作user_sessions表同一个session_key对应的行：

1. 两个事务都先执行了对该行的普通读/共享读，各自持有该行的共享锁（S锁）
2. 之后两个事务都要执行UPDATE语句，需要申请该行的排他锁（X锁）
3. 事务1的X锁申请被事务2持有的S锁阻塞，事务2的X锁申请又被事务1持有的S锁阻塞，形成循环等待触发死锁

额外诱因：死锁日志显示两个事务都持有7702个行锁，说明存储过程中之前的查询没有命中索引，扫描了全表/大量行，持有了很多不必要的行锁，大幅提升了锁冲突概率。

排查步骤 #

• 对存储过程中所有对user_sessions、user_logins的查询语句执行EXPLAIN校验，确认所有查询都命中了索引，尤其是过滤条件是否正确用到了session_key主键或者user_id普通索引，避免全表扫描产生不必要的行锁
• 确认存储过程的事务范围，是否将无关的查询逻辑也包在了事务中，拉长了事务持有锁的时间
• 检查是否存在同一用户的多个请求同时携带相同session_key触发并发更新的业务场景

修复方案 #

1. 调整锁获取逻辑，避免S锁转X锁冲突 #

把存储过程中对session_key对应行的首次查询，从普通查询/共享查询直接改成SELECT ... FOR UPDATE，必须确保WHERE条件明确指定session_key = _token走主键索引，让事务一开始就直接拿X锁，从根源上消除S锁和X锁的竞争：

-- 存储过程中操作该session行的第一步先拿X锁
SELECT 1 FROM user_sessions WHERE session_key = _token FOR UPDATE;
-- 后续再执行其他查询逻辑和UPDATE操作

2. 缩小事务范围，减少锁持有时间 #

把存储过程中不需要原子性保证的查询逻辑移到事务之外，只把需要一致性保证的操作（查询+更新）包在事务中，尽可能缩短锁的持有时长，降低冲突概率。

3. 优化UPDATE语句 #

你的表结构已经定义了updated_at自动更新规则：DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP，可以去掉UPDATE语句中对updated_at的显式赋值，简化语句减少开销：

UPDATE user_sessions SET guid = @guid WHERE session_key = _token;

4. 调整死锁重试策略 #

session更新的并发冲突概率比普通业务高，可以针对该存储过程单独把重试次数调高到3_{5次，每次重试前增加10}50ms的随机延迟，避免重试时再次发生冲突。

5. 关闭外键约束（可选） #

如果业务层面已经能保证user_id的合法性，可以去掉user_sessions表上的外键约束，避免外键校验带来的额外锁开销。

内容的提问来源于stack exchange，提问作者amarynets