You need to enable JavaScript to run this app. Postgres实现服务等待队列死锁及超量报名问题求解
优惠活动
大模型
产品
解决方案
定价
更多

Postgres实现服务等待队列死锁及超量报名问题求解

Postgres服务报名等待队列实现优化咨询

基础建表逻辑

最小建表代码如下:

CREATE TABLE IF NOT EXISTS applies(
    created TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    user_id SMALLINT REFERENCES users(id) ON DELETE CASCADE,
    service_id SMALLINT REFERENCES services(id) ON DELETE CASCADE,
    status SMALLINT DEFAULT 0, --0: new 1: waitlisted, 2: applied
    PRIMARY KEY (user_id, service_id)
    -- ...
);

CREATE INDEX ON applies (service_id);

status字段用于区分报名状态,便于给从等待队列转为报名成功的用户发送通知,避免给从未进入等待队列的前n位用户误发通知。每个服务有固定的可报名人数上限,默认采用先到先得的分配规则,因此设计了两阶段状态流转,该逻辑不影响核心使用场景。

支持的业务请求

  • 新增报名:用户提交服务报名申请
    • 插入记录:user_id、service_id、CURRENT TIMESTAMP、默认状态0(新建)
    • 根据同service_id下的申请记录COUNT(*)结果,更新状态为1(等待中)或2(报名成功)
  • 取消报名:用户撤销服务申请
    • 删除对应用户的报名记录
    • 若存在等待中的用户,按申请时间先后将最早提交的用户转为报名成功,并发送通知

初始实现问题

假设服务报名上限为10人,最初朴素实现如下:

新增报名逻辑

UPDATE applies
SET status = (
    SELECT CASE WHEN COUNT(*) <= 10 THEN 2 ELSE 1 END
    FROM applies
    WHERE service_id = 7918
    AND created <= '2021-08-16 16:20:34.161274+00:00:00'
)
WHERE user_id = 5070
AND service_id = 7918
RETURNING status

取消报名逻辑

先查询待转正用户:

SELECT user_id
FROM applies
WHERE status = 1
AND service_id = 7915
ORDER BY created
LIMIT 1

再执行状态更新:

UPDATE applies
SET status = 2
WHERE status = 1
AND user_id = 5063
AND service_id = 7915

该方案单线程顺序测试正常,但多线程并发测试中出现报名成功状态的记录数超过10人的问题。
先后尝试将事务隔离级别设置为SERIALIZABLE、REPEATABLE READ,会大量抛出ERROR #40001 could not serialize access due to concurrent update错误;改用READ COMMITTED级别后效果明显改善,但仍存在超量报名问题。
之后尝试在SELECT语句中添加FOR NO KEY UPDATE锁,但很快就触发死锁,查阅了大量死锁相关资料仍未找到有效解决方案。

第一次迭代实现

调整逻辑:插入记录时默认状态改为等待中,新增和取消报名的SQL逻辑基本一致,仅在选择user_id的逻辑上有差异,修改后代码如下:

新增报名逻辑

UPDATE applies
SET status = 2
WHERE service_id = 7860
AND 10 > (
    SELECT COUNT(*)
    FROM (
        SELECT service_id, user_id
        FROM applies
        WHERE service_id = 7860
        AND status = 2
        FOR NO KEY UPDATE
    ) as newstate)
AND user_id = 5012 RETURNING status

取消报名逻辑

UPDATE applies
SET status = 2
WHERE service_id = 7863
AND 10 > (
    SELECT COUNT(*)
    FROM (
        SELECT service_id, user_id
        FROM applies
        WHERE service_id = 7863
        AND status = 2
        FOR NO KEY UPDATE
    ) as newstate)
AND user_id = (
    SELECT user_id
    FROM applies
    WHERE service_id = 7863
    And status = 1
    ORDER BY created
    LIMIT 1
)
RETURNING user_id

但多线程测试依然出现死锁问题。

最终落地实现

参考社区建议,没有新增单独表,而是在services表中添加了对应计数字段代替COUNT统计。
在新增和取消报名的事务开头先对对应服务行加锁:

SELECT *
FROM services
WHERE id = ?
FOR NO KEY UPDATE

初期多线程测试偶发报名人数未达上限的问题,将删除操作和取消报名的后续逻辑合并到同一事务后,问题最终得到解决。


优化方案参考

你目前的实现已经解决了核心并发问题,还可以从以下几个方向做更优调整:

  1. 冗余字段性能优化
    在services表中明确新增quota int(服务报名总配额)和applied_count int(当前已报名成功人数)两个字段,完全替换COUNT统计逻辑,事务内判断applied_count < quota即可,大流量场景下性能提升非常明显。每次状态更新(报名成功、取消报名)同步维护applied_count的增减,不需要额外扫表统计。
  2. 原子操作简化
    取消报名后的等待用户转正逻辑可以合并为单条SQL,减少事务执行耗时,避免中间状态异常:
WITH selected_wait_user AS (
    SELECT user_id FROM applies 
    WHERE service_id = ? AND status = 1 
    ORDER BY created LIMIT 1
    FOR NO KEY UPDATE
)
UPDATE applies SET status = 2
WHERE service_id = ? 
AND user_id = (SELECT user_id FROM selected_wait_user)
RETURNING user_id;
  1. 索引优化
    新增联合部分索引覆盖常用查询场景,进一步降低查询耗时:
CREATE INDEX idx_applies_service_status_created ON applies (service_id, status, created);

同时可以给applies表加CHECK约束避免非法状态值:CHECK (status IN (0,1,2)),防止脏数据写入。
4. 死锁兜底优化
保持所有操作统一遵循「先锁services对应行,再操作applies表」的加锁顺序,完全规避加锁顺序不一致导致的死锁问题,当前你已经在做这一点,只要全链路保持该规则即可。


内容的提问来源于stack exchange,提问作者vendelin

相关产品推荐
方舟 Agent Plan

超全模态模型 × Harness 升级,最新支持 Deepseek-V4.1-Flash、GLM-5.3 系列、Doubao-Seedream-5.0-pro、Kimi-K3 (部分), 限时 9.9 元起

最近更新时间:2026.10.08 02:39:03