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
初期多线程测试偶发报名人数未达上限的问题,将删除操作和取消报名的后续逻辑合并到同一事务后,问题最终得到解决。
优化方案参考
你目前的实现已经解决了核心并发问题,还可以从以下几个方向做更优调整:
- 冗余字段性能优化
在services表中明确新增quota int(服务报名总配额)和applied_count int(当前已报名成功人数)两个字段,完全替换COUNT统计逻辑,事务内判断applied_count < quota即可,大流量场景下性能提升非常明显。每次状态更新(报名成功、取消报名)同步维护applied_count的增减,不需要额外扫表统计。 - 原子操作简化
取消报名后的等待用户转正逻辑可以合并为单条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;
- 索引优化
新增联合部分索引覆盖常用查询场景,进一步降低查询耗时:
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

