Postgres高并发下如何高效更新单条匹配行实现一次性验证码分配

• 保留原有核心字段，可根据数据规模调整字段类型避免溢出：
  • MappingId：优先用BIGINT自增主键，适配千万级以上验证码存储需求
  • Code：按验证码规则设置对应字符串/数值类型即可
  • DeviceId：按设备ID规则设置类型，默认值设为NULL表示未被领取
• 新增部分索引，仅索引未被领取的验证码记录，大幅缩小索引体积、提升查询效率，避免全表/全索引扫描：

CREATE INDEX idx_mapping_unclaimed ON "MappingTable" ("MappingId") WHERE "DeviceId" IS NULL;

如果需要进一步避免回表开销，可创建覆盖索引：

CREATE INDEX idx_mapping_unclaimed_cover ON "MappingTable" ("MappingId") INCLUDE ("Code") WHERE "DeviceId" IS NULL;

核心使用PostgreSQL原生的SKIP LOCKED语法，直接跳过已被其他事务锁定的行，完全消除锁等待问题，单条语句保证原子性，不会出现重复领取、竞态条件问题：

UPDATE "MappingTable" 
SET "DeviceId" = $1 
WHERE "MappingId" = (
    SELECT "MappingId" 
    FROM "MappingTable" 
    WHERE "DeviceId" IS NULL
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
RETURNING "Code";

其中$1为传入的待绑定设备ID参数。如果偏好CTE写法，也可以使用如下等价语句，性能差异极小：

WITH unclaimed_code AS (
    SELECT "MappingId"
    FROM "MappingTable"
    WHERE "DeviceId" IS NULL
    LIMIT 1
    FOR UPDATE SKIP LOCKED
)
UPDATE "MappingTable" m
SET "DeviceId" = $1
FROM unclaimed_code
WHERE m."MappingId" = unclaimed_code."MappingId"
RETURNING m."Code";

该方案默认使用PostgreSQL标准的读已提交隔离级别即可，无需调整更高隔离级别带来额外开销。

原有写法未加行锁跳过逻辑，高并发场景下大量请求会同时查询到同一条未被领取的MappingId，后续更新时多个事务争抢同一行锁，导致90%以上的请求处于锁等待状态，因此QPS无法提升。加入SKIP LOCKED后，每个请求会自动跳过被其他事务持有的行，不会产生等待。

如果生产环境峰值QPS超过2万，可在此基础上做分桶优化：

• 给MappingTable新增bucket_id字段，预生成验证码时随机分配0_{N的桶号（N可按并发规模设置为10}100）
• 部分索引加上桶号维度
• 领取时随机生成一个桶号作为查询条件，进一步缩小查询范围、降低行冲突概率

优化后常规场景压测QPS可轻松达到数千到数万级别，完全满足百万级设备的验证码领取需求。

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