postgresql 在UPDATE FROM中保证顺序以避免死锁

dly7yett 于 2023-08-04 发布在 PostgreSQL

关注(0)|答案(1)|浏览(194)

查询并发执行，以原子地用聚合数值递增一些计数器：

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;

字符串
设置了CTE agg_data中id列的顺序，但UPDATE语句并不遵循它，而是以任意顺序更新counters中的行，这会导致死锁。不幸的是，ORDER BY在UPDATE中不受支持（不知道是否有原因）。
虽然我可以添加一个子查询SELECT FOR UPDATE，但感觉我把事情复杂化了：

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
), locked_counters AS ( -- add this part to lock in sequential order
   SELECT 
   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;

型
考虑到我的counters表可能非常大，并且这个查询需要完全优化以提高效率和完整性-有更好的方法吗？

postgresql

来源：https://stackoverflow.com/questions/76829803/guarantee-order-in-update-from-to-avoid-deadlocks

1条答案

按热度按时间

v1l68za41#

遗憾的是，没有ORDER BY的UPDATE。我们必须使用SELECT FOR UPDATE系列工具中的一个来获取显式行级锁。FOR NO KEY UPDATE在ORDER BY之后在单独的CTE中似乎是最佳选择。你的思路是对的。
自从...
此查询需要针对效率和完整性进行充分优化
我有几个建议：

WITH data (id, delta) AS (
   VALUES (null::bigint, null::numeric)  -- ①
   UNION ALL
   VALUES (1,1.2), (2,2.0), (2,0.5)
   )
, agg_data AS ( -- ②
   SELECT d.*
   FROM  (
      SELECT id, sum(delta) AS sum_delta
      FROM   data
      GROUP  BY id
      ORDER  BY id   -- optional
      ) d
   JOIN   counters USING (id)  -- ③
   ORDER  BY id
   FOR    NO KEY UPDATE OF counters  -- ② 
   )
UPDATE counters c
SET    val = c.val + a.sum_delta
FROM   agg_data a
WHERE  c.id = a.id;

字符串
①不要让输入值默认为某种数据类型，以后再转换。这是浪费精力（可能会引入舍入误差）。相反，请立即将输入转换为正确的类型。可以通过转换VALUES表达式中第一行（或任意行）的值来实现此目的：

VALUES (bigint '1', numeric '1.2'), (2,2.0), (2,0.5)

型
这可能会造成不便，因为它会强制您编辑输入。下一个最好的事情是像我上面做的事情。然后，您就有了一个不可变的前导行，其中包含了正确数据类型的null值，从而强制集合的其余部分保持一致。在后续的连接中，将自动消除虚拟行。如需详细信息，请参阅：

更新多行时强制转换NULL类型

②您有一个CTE agg_data来具体化聚合的结果，还有一个CTE locked_counters按顺序获取行锁。您可以在一个CTE中同时执行这两种操作。然而，引用手册：
目前，FOR NO KEY UPDATE、FOR UPDATE、FOR SHARE和FOR KEY SHARE不能与GROUP BY一起指定。
Posterre在意识到锁无论如何都不适用于该派生表之前会引发异常。这是一个缺点，但我们可以使用**FOR NO KEY UPDATE OF counters**来解决它。
③ JOIN通常比IN (SELECT ...)更快。IN尝试折叠子查询中得重复项.在GROUP BY id之后，不可能有任何欺骗，但Posterre不会知道，仍然遵循更昂贵的代码路径。此外，JOIN会立即删除目标中没有匹配项的输入行，如果有很多匹配项，这将是一个显著的优势。
理论上这应该快一点。你得先测试一下。

赞(0）回复(0）举报 2023-08-04

我来回答

postgresql 在UPDATE FROM中保证顺序以避免死锁

1条答案

相关问题

热门标签

最新问答