MySQL InnoDB高负载下读写同表生成自增余额触发死锁问题求助

从InnoDB死锁日志可以明确死锁是间隙锁（Gap Lock）和插入意向锁冲突导致，具体触发流程如下：

1. 你的业务逻辑为「查询balance表最后一行余额→余额+1→插入新行」，在InnoDB默认可重复读（RR）隔离级别下，若查询最后一行时使用了当前读（SELECT ... LOCK IN SHARE MODE 或隐式当前读逻辑），会触发两个锁：
   • 给最后一行主键索引加S（共享）记录锁
   • 给最后一行到主键上界（supremum伪记录）的间隙加S型间隙锁，用于避免幻读
2. S型间隙锁是共享的，多个并发事务可以同时持有同一间隙的S锁，不会互斥，因此高并发下会出现多个事务同时拿到该间隙S锁的情况。
3. 所有事务后续执行INSERT操作时，都需要申请对应间隙的插入意向锁（一种特殊的X型间隙锁），而插入意向锁和已存在的S型间隙锁互斥。
4. 此时形成循环等待：事务A持有间隙S锁，等待事务B释放S锁以拿到插入意向锁；事务B持有同间隙S锁，等待事务A释放S锁以拿到插入意向锁，死锁触发，和日志中两个事务均持有S锁、同时等待插入意向X锁的表现完全吻合。

可根据业务场景选择以下任意一种方案解决：

• 调整事务隔离级别：将执行该逻辑的事务隔离级别临时设置为读已提交（RC），RC级别下会关闭间隙锁，仅保留记录锁，从根源上消除间隙锁冲突，同时RC级别已能满足绝大多数业务的一致性要求。
• 改用数据库原子操作：拆分逻辑为「原子更新总余额→插入流水」：新增一张存储当前总余额的表，先执行UPDATE balance_total SET bal = bal + 1 WHERE id = 1，该操作是数据库原子操作，不会出现并发脏写，再用更新后的余额值插入balance流水表，完全规避间隙锁问题。
• 查询时加排他锁：将查询最后一行的语句改为SELECT bal FROM balance ORDER BY id DESC LIMIT 1 FOR UPDATE，查询时直接给间隙加X型间隙锁，后续其他事务查询时会被阻塞，不会出现多事务同时持有S锁的情况，避免循环等待。
• 业务层加并发控制：在业务逻辑入口加分布式锁，同一时间仅允许一个请求执行该余额查询+插入逻辑，适合并发量不高的场景，实现简单。

内容的提问来源于stack exchange，提问作者Akeed Hussain Bhat