如何确保UPDATE FROM执行顺序避免死锁？大表更新求最优解

问题背景 #

并发执行计数器累加时，原UPDATE语句会以任意顺序更新counters表行，不同事务的锁顺序相反导致死锁。虽然CTE里对id排了序，但UPDATE本身不遵循该顺序，用户尝试的SELECT FOR UPDATE方案可行但可以进一步优化，尤其适配counters表数据量大的场景。

优化方案：简化顺序锁逻辑，精准锁定目标行 #

以下方案通过按固定顺序提前锁定需要更新的行，彻底消除死锁风险，同时避免冗余扫描，适配大数据量表：

WITH data (id, delta) AS (
    VALUES(1,1.2),(2,2.0),(2,0.5)
), agg_data AS (
    SELECT
        id::bigint,
        SUM(delta::numeric) AS sum_delta
    FROM data
    GROUP BY id
    ORDER BY id
), lock_target_rows AS (
    -- 仅锁定需要更新的行，严格按id升序获取锁
    SELECT 1
    FROM counters
    WHERE id IN (SELECT id FROM agg_data)
    ORDER BY id FOR NO KEY UPDATE
)
UPDATE counters
SET val = counters.val + agg_data.sum_delta
FROM agg_data
WHERE counters.id = agg_data.id;

方案优势 #

1. 彻底避免死锁：所有事务都按id升序获取行锁，锁顺序完全一致，不会出现交叉锁的情况
2. 高效适配大数据量：仅锁定需要更新的行，不会扫描全表——前提是counters.id上有主键或唯一索引（必须保证这一点，否则大数据量下性能会暴跌）
3. 简化逻辑：相比原方案，lock_target_rows仅做锁操作，无需冗余字段，逻辑更清晰

关键注意事项 #

• 必须保证counters.id有索引：主键或唯一索引是快速定位目标行的核心，没有索引的话，WHERE id IN (...)会变成全表扫描，完全无法适配大数据量场景
• 选择合适的锁类型：用FOR NO KEY UPDATE而非FOR UPDATE，因为我们只更新val字段，不修改主键/唯一键，锁粒度更轻，减少锁竞争
• 批量更新优化：如果单次更新的id数量极多，可以拆分成分批次按id范围更新，降低单事务锁定的行数，减少并发冲突概率

备选方案（特殊场景） #

如果业务允许跳过已锁定的行（非严格必须累加所有数据），可以用FOR UPDATE SKIP LOCKED，适合高并发下的流量削峰场景：

WITH data (id, delta) AS (
    VALUES(1,1.2),(2,2.0),(2,0.5)
), agg_data AS (
    SELECT
        id::bigint,
        SUM(delta::numeric) AS sum_delta
    FROM data
    GROUP BY id
    ORDER BY id
)
UPDATE counters
SET val = counters.val + agg_data.sum_delta
FROM agg_data
WHERE counters.id = agg_data.id
AND counters.id IN (
    SELECT id FROM counters
    WHERE id IN (SELECT id FROM agg_data)
    ORDER BY id FOR NO KEY UPDATE SKIP LOCKED
);

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