高并发下PostgreSQL更新行返回原值时AccessExclusiveLock锁等待优化问题

问题根因 #

你遇到的AccessExclusiveLock等待报错核心来自两个问题：

• 原有SQL逻辑冗余：CTE先执行SELECT ... FOR UPDATE加行锁，后续INSERT ON CONFLICT执行更新时会再次申请行锁，拉长了锁持有时间，提升了冲突概率
• 并发请求针对同一主键时，FOR UPDATE默认会无限等待已持有锁的事务释放，高并发场景下极易产生锁等待队列，触发超时报错

优化方案 #

完全满足「并发冲突时跳过更新、必须返回更新前旧值」的要求，优化后语句如下：

WITH get_old AS (
    SELECT * FROM info WHERE id = $1
    FOR UPDATE SKIP LOCKED
),
do_upsert AS (
    INSERT INTO info (id, data) VALUES ($1, $2)
    ON CONFLICT ON CONSTRAINT info_pkey DO UPDATE
    SET data = $2
    WHERE EXISTS (SELECT 1 FROM get_old)
)
SELECT * FROM get_old
UNION ALL
SELECT * FROM info WHERE id = $1 AND NOT EXISTS (SELECT 1 FROM get_old);

核心优化逻辑 #

• 加锁时新增SKIP LOCKED关键字：如果目标行已被其他事务持有锁，不会等待直接跳过拿锁步骤，从根源上消除锁等待。注意：SKIP LOCKED 语法要求PostgreSQL版本 >= 9.5，符合当前主流使用的版本要求
• 给ON CONFLICT DO UPDATE添加WHERE EXISTS (SELECT 1 FROM get_old)限制：只有成功拿到行锁的请求才会执行更新操作，拿锁失败的请求直接跳过更新，符合允许跳过冲突更新的要求
• 返回逻辑兼容两种场景：成功拿锁的请求返回get_old中存储的更新前旧值；拿锁失败的请求直接返回当前表中该记录的最新值，满足必须返回旧值的要求

额外适配优化 #

你的场景几乎无新增插入操作，99%以上都是更新请求，可以直接去掉INSERT逻辑，改用纯UPDATE逻辑进一步降低开销：

WITH get_old AS (
    SELECT * FROM info WHERE id = $1
    FOR UPDATE SKIP LOCKED
),
do_update AS (
    UPDATE info SET data = $2
    WHERE id = $1 AND EXISTS (SELECT 1 FROM get_old)
)
SELECT * FROM get_old
UNION ALL
SELECT * FROM info WHERE id = $1 AND NOT EXISTS (SELECT 1 FROM get_old);

其他优化建议 #

• 保持PostgreSQL默认的读已提交事务隔离级别即可，不需要调高隔离级别，避免额外的锁开销
• 尽量缩短执行该SQL的事务长度，避免长事务持有锁导致后续请求堆积

内容的提问来源于stack exchange，提问作者a1-----