多实例部署下MySQL取唯一码的并发处理企业级落地方案

先直接回应你提到的几个思路的问题：

• 先update再select的方向没错，但绝大多数人写的SQL不对，要么全表扫，要么锁冲突严重，不是方案本身的问题。
• 乐观锁加重试确实不适合这个场景：并发高的时候大量请求更新失败重试，很容易把数据库连接打满，吞吐上不去。
• 把事务隔离级别改成Serializable纯纯是馊主意，并发性能直接掉一个数量级，生产环境没人这么用。
• 中心化阻塞队列的单点问题是硬伤，就算做高可用，切主时的重复发码、丢数据问题很难彻底解决，还要额外维护一套队列组件，运维成本太高。

数千级并发下的标准实现（纯MySQL，无额外依赖） #

这套方案是互联网企业发券、发激活码这类场景的通用实现，8核16G的MySQL实例就能轻松扛住3000~5000QPS的请求，不会重复发码，不会全表扫描，没有重试开销。

第一步：先补索引，从根上避免全表扫描 #

-- 建联合索引，筛选可用码、排序都走这个索引，不会扫全表
ALTER TABLE codes ADD INDEX idx_available_id (is_available, id);

第二步：用单条原子UPDATE完成码的抢占，不需要先查，不需要版本号 #

UPDATE codes 
SET is_available = false, user = #{userId}
WHERE is_available = true
-- 固定按id升序拿，避免优化器随机选记录导致间隙锁死锁
ORDER BY id ASC
-- 每次只抢1条
LIMIT 1;

核心原理：InnoDB执行这条UPDATE时，会顺着刚才建的联合索引按顺序遍历，找到第一条满足is_available=true的记录就加上行级排他锁直接更新。很多人担心这里会有锁等待，实际上当第一个事务把这条记录的is_available改成false之后，其他并发事务扫描到这条记录时，会发现它已经不满足where条件，直接跳过找下一条可用记录，根本不会阻塞在这条记录的锁上，并发效率非常高。

第三步：判断抢占结果，返回对应内容 #

JDBC、MyBatis这类框架执行UPDATE时会返回受影响的行数：

• 如果返回值为1，说明抢占成功，直接查这条刚更新的记录返回给用户即可：

  SELECT codes FROM codes WHERE user = #{userId} AND is_available = false ORDER BY id DESC LIMIT 1;
  

• 如果返回值为0，说明当前码池已经没有可用码，直接返回对应的业务提示就行。

整个逻辑不需要开长事务，UPDATE完立刻查立刻提交，事务持锁时间在毫秒级，几乎不会有锁冲突。

万级以上并发的优化方案 #

如果后续并发涨到万级以上，不用换架构，加个本地预占缓存就行，成本极低：

• 每个服务实例启动后，一次性批量从数据库预占一批码（比如一次拿1000条，UPDATE的时候把LIMIT改成1000，给这批码打上当前实例的预占标识），存在本地内存队列里。
• 当本地队列剩余码量低于阈值（比如剩100条），异步触发下一批预占，不要等队列空了再去数据库拿，避免接口等待。
• 接口请求直接从本地内存队列取码返回，完全不碰数据库，单实例就能扛住几万QPS，整个集群吞吐可以到十万级。
• 加个每天跑一次的定时任务，把预占超过24小时还没发出去的码重置为可用状态就行——就算某个实例宕机，最多损失1000个码，对于绝大多数业务来说完全可以接受。

几个必踩的坑提前说 #

• 写UPDATE的时候一定不要省略ORDER BY id ASC：不加排序的话MySQL优化器可能会随机选索引页里的记录，导致间隙锁范围变大，高并发下容易出现死锁。
• 不要给is_available字段建单独的索引：联合索引(is_available, id)就够，单独索引会让排序走文件排序，性能掉很多。
• 不要把这个逻辑放到长事务里：整个更新+查询的逻辑控制在10ms以内提交，不要在事务里加其他业务逻辑，避免锁持有时间太长导致阻塞。

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