我正在使用Postgres 9.2。
有两个UPDATE语句,每个语句都在自己的事务中。其中一个看起来像这样:
UPDATE foo SET a=1 WHERE b IN (1,2,3,4);
另一个类似:
UPDATE foo SET a=2 WHERE b IN (1,2,3,4);
这些可能会同时运行,实际上在“IN”表达式中有500+个。 我有时会看到死锁。是否真的是“IN”表达式中项目的顺序可能不会影响真正的锁定顺序?
我正在使用Postgres 9.2。
有两个UPDATE语句,每个语句都在自己的事务中。其中一个看起来像这样:
UPDATE foo SET a=1 WHERE b IN (1,2,3,4);
UPDATE foo SET a=2 WHERE b IN (1,2,3,4);
这些可能会同时运行,实际上在“IN”表达式中有500+个。 我有时会看到死锁。是否真的是“IN”表达式中项目的顺序可能不会影响真正的锁定顺序?
在 UPDATE
命令中没有 ORDER BY
。
但对于 SELECT
是有的。在子查询中使用 FOR UPDATE
子句 进行 行级锁定:
UPDATE foo f
SET a = 1
FROM (
SELECT b FROM foo
WHERE b IN (1,2,3,4)
<b>ORDER BY b
FOR UPDATE</b>
) upd
WHERE f.b = upd.b;
当然,b
必须是UNIQUE
,否则您需要在ORDER BY
子句中添加更多表达式以使其明确无歧。UPDATE
、DELETE
和 SELECT .. FOR UPDATE
语句强制执行相同的顺序。IN
检查是否属于指定集合,但不会对UPDATE
进行任何排序,这反过来又意味着没有具体的锁定顺序。UPDATE
语句中,WHERE
子句基本上与SELECT
中的行为相同。例如,我经常使用SELECT
模拟UPDATE
以检查将要更新的内容,以确保它符合我的预期。SELECT
的示例演示了IN
本身并不具有排序功能:create table foo
(
id serial,
val text
);
insert into foo (val)
values ('one'), ('two'), ('three'), ('four');
select *
from foo
where id in (1,2,3,4);
select *
from foo
where id in (4,3,2,1);
产生完全相同的结果--按照id
从1到4的顺序排列行。
即使如此也不能保证,因为在选择中没有使用ORDER BY
。相反,在没有它的情况下,Postgres使用服务器决定最快速度的任何顺序(请参阅Postgres SELECT文档中关于ORDER BY
的第8点)。对于一个相当静态的表格,通常是插入的顺序(就像这里的情况一样)。然而,并没有什么保证,如果表格上有很多变化(很多死元组、删除的行等),它更不可能是这种情况。
我怀疑你的UPDATE
就是发生在这里的。有时候--甚至大多数情况下--它可能以数字顺序结束,如果那是插入行的方式,但没有任何保证,而且你看到死锁的情况很可能是数据已经发生了改变,使得一个更新与另一个不同。
sqlfiddle与上面的代码一起使用。
可能的修复/解决方法:
就该问题而言,您有各种选项可以选择,具体取决于您的要求。您可以在表上明确取出表锁,尽管这样会导致在那里更新的串行化效果,这可能会被证明是一个太大的瓶颈。
另一个选项,它仍然允许并发 - 就是在Python中使用动态SQL显式地迭代项目。这样,你将有一组总是按相同顺序发生的单行更新,在那里,由于你可以确保一致的顺序,正常的Postgres锁定应该能够处理并发而不会死锁。
那不会像纯SQL批量更新那样表现良好,但它应该解决锁问题。提高性能的建议是只在每隔一段时间后提交,并不是每个单独的行之后 -- 这可以节省很多开销。
另一个选择是在使用 PL/pgSQL 编写的 Postgres 函数中执行循环。该函数可以在外部调用,例如在 Python 中,但循环将在服务器端明确执行,这可能会节省一些开销,因为循环和 UPDATE 完全在服务器端执行,而不必在每个循环迭代中传输数据。
UPDATE ... ORDER BY
,这正是我们需要保证的。 - Craig RingerUPDATE
(和DELETE
)的ORDER BY
子句不受欢迎... - Erwin Brandstetter