据我所知,没有办法直接通过UPDATE
声明来实现这一点;确保锁顺序的唯一方法是使用SELECT ... ORDER BY ID 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
system column表示)并使用该地址驱动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扫描" node 来判断它是否有效).要在单个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
$$