Postgres通过ORDER BY进行UPDATE，如何操作？

Question

Postgres通过ORDER BY进行UPDATE，如何操作？

37

我需要对一组记录进行Postgres更新，并尝试防止在压力测试中出现死锁。

通常的解决方法是按照某种顺序更新记录，例如按ID排序 - 但似乎Postgres不允许使用ORDER BY进行UPDATE。

假设我需要进行更新，例如：

UPDATE BALANCES WHERE ID IN (SELECT ID FROM some_function() ORDER BY ID);

当您同时运行200个查询时，会导致死锁情况。应该怎么办？

我正在寻找一般解决方案，而不是像使用ORDER BY的UPDATE那样特定于情况的解决方法。

感觉应该有比编写游标函数更好的解决办法。如果没有更好的方法，那么这个游标函数应该如何优化呢？逐条更新记录。

- bbozo

你尝试过使用UPDATE... FROM...语法吗？ - stas.yaranov

是的，我尝试过UPDATE....FROM....SELECT...FOR UPDATE，但堆栈跟踪中没有任何变化。实际上，这使问题更加普遍。 - bbozo

2个回答

4

一般来说，并发编程是很困难的。特别是当有200个语句（我假设你不只是查询=SELECT）或者甚至是事务时（实际上，如果它不在事务中，每个单独的语句都会被包装成一个事务）。

解决方案的一般概念是这些（组合）：

要意识到死锁可能会发生，在应用程序中捕获它们，检查错误代码，对于class 40或40P01并重试事务。
保留锁定。使用SELECT ... FOR UPDATE。尽可能避免显式锁定。锁定将强制其他事务等待锁定释放，这会损害并发性，但可以防止事务陷入死锁。在第13章的死锁示例中检查A等待B并且B等待A的情况（银行账户问题）。
选择不同的隔离级别，例如更弱的READ COMMITED。在READ COMMITED模式下注意LOST UPDATE。使用REPEATABLE READ预防它们。

以相同的顺序按表名字母顺序编写每个事务中的锁语句。

LOCK / USE A  -- Transaction 1 
LOCK / USE B  -- Transaction 1
LOCK / USE C  -- Transaction 1
-- D not used -- Transaction 1

-- A not used -- Transaction 2
LOCK / USE B  -- Transaction 2
-- C not used -- Transaction 2
LOCK / USE D  -- Transaction 2

使用一般的锁定顺序A B C D。这样，事务可以以任何相对顺序交错，并仍有很好的机会避免死锁（取决于您的语句，您可能会遇到其他串行化问题）。事务的语句将按照它们指定的顺序运行，但是可能是事务1运行其前两个，然后xact 2运行第一个，然后1完成，最后xact 2完成。

此外，您应该意识到，在并发情况下涉及多行的语句不是原子执行的。换句话说，如果您有涉及多行的两个语句A和B，则它们可以按此顺序执行：

a1 b1 a2 a3 a4 b2 b3

但不是一堆a后面跟着b的块。对于带有子查询的语句也适用。您是否使用EXPLAIN查看了查询计划？

在您的情况下，您可以尝试

UPDATE BALANCES WHERE ID IN (
 SELECT ID FROM some_function() FOR UPDATE  -- LOCK using FOR UPDATE 
 -- other transactions will WAIT / BLOCK temporarily on conc. write access
);

如果可能，您也可以使用SELECT ... FOR UPDATE SKIP LOCK，这将跳过已锁定的数据以恢复并发性，这是通过等待另一个事务释放锁（FOR UPDATE）而丢失的。但是，这不会对已锁定的行应用UPDATE，这可能需要您的应用程序逻辑。因此，请稍后运行它（参见第1点）。

还要阅读LOST UPDATE关于LOST UPDATE和SKIP LOCKED关于SKIP LOCKED。在您的情况下，队列可能是一个好主意，在SKIP LOCKED参考中完美地解释了这一点，尽管关系型DBMS不是队列的目的。

希望对您有所帮助。

- flutter

谢谢，我会试试这个。在选择时是否有锁定正在进行？我猜没有，但我已经疯了。 - bbozo

3

我认为你的 FOR UPDATE 没有起到作用，因为这个 SELECT 没有引用到任何表格... - Nick Barnes

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- Nick Barnes · Accepted Answer

据我所知，没有直接通过UPDATE语句实现这一点的方法；确保锁定顺序的唯一方法是使用SELECT ... ORDER BY ID FOR UPDATE明确地获取锁，例如：

SELECT * FROM mytable WHERE id = 1 FOR UPDATE;

UPDATE Balances
SET Balance = 0
WHERE ID IN (
  SELECT ID FROM Balances
  WHERE ID IN (SELECT ID FROM some_function())
  ORDER BY ID
  FOR UPDATE
)

这样做的缺点是在Balances表上重复查找ID索引。在您的简单示例中，您可以通过在锁定查询期间获取物理行地址（由ctid系统列表示）来避免此开销，并使用它来驱动UPDATE：

UPDATE Balances
SET Balance = 0
WHERE ctid = ANY(ARRAY(
  SELECT ctid FROM Balances
  WHERE ID IN (SELECT ID FROM some_function())
  ORDER BY ID
  FOR UPDATE
))

使用ctid时要小心，因为该值是瞬时的。我们在这里是安全的，因为锁将阻止任何更改。

不幸的是，规划器只在一组狭窄的情况下利用ctid（您可以通过查看EXPLAIN输出中的"Tid Scan"节点来确定它是否有效）。如果要在单个UPDATE语句中处理更复杂的查询，例如如果您的新余额与ID一起由some_function()返回，则需要回退到基于ID的查找：

UPDATE Balances
SET Balance = Locks.NewBalance
FROM (
  SELECT Balances.ID, some_function.NewBalance
  FROM Balances
  JOIN some_function() ON some_function.ID = Balances.ID
  ORDER BY Balances.ID
  FOR UPDATE
) Locks
WHERE Balances.ID = Locks.ID

如果性能开销是一个问题，你需要使用游标来解决，具体代码如下：

DO $$
DECLARE
  c CURSOR FOR
    SELECT Balances.ID, some_function.NewBalance
    FROM Balances
    JOIN some_function() ON some_function.ID = Balances.ID
    ORDER BY Balances.ID
    FOR UPDATE;
BEGIN
  FOR row IN c LOOP
    UPDATE Balances
    SET Balance = row.NewBalance
    WHERE CURRENT OF c;
  END LOOP;
END
$$