使用SELECT FOR UPDATE和IN列表时出现死锁。

Question

使用SELECT FOR UPDATE和IN列表时出现死锁。

sqlpostgresqlconcurrencytransactionsdatabase-deadlocks

3

考虑以下示例：

-- Transaction 1 -> T1
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
UPDATE table1 set col1 = 'abcd' where id = 1;
COMMIT;

-- Transaction 2 -> T2
BEGIN;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
UPDATE table1 set col1 = 'defg' where id = 2;
COMMIT;

在这个例子中，很明显如果两个事务同时执行，死锁可能会发生，因为如果T1锁定id=1的行，然后T2锁定id=2的行，那么T1和T2都无法执行第二个SELECT FOR UPDATE查询，从而导致死锁。

现在，为了解决这个问题，我们可以按照相同的顺序执行SELECT FOR UPDATE查询：

-- Transaction 1 -> T1
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
UPDATE table1 set col1 = 'abcd' where id = 1;
COMMIT;

-- Transaction 2 -> T2
BEGIN;
SELECT * FROM table1 WHERE id = 1 FOR UPDATE;
SELECT * FROM table1 WHERE id = 2 FOR UPDATE;
UPDATE table1 set col1 = 'defg' where id = 2;
COMMIT;

我们解决了这个例子的死锁问题。

现在，我的问题是，如果你考虑以下类似的例子：

-- Transaction 1 -> T1
BEGIN;
SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;
UPDATE table1 set col1 = 'abcd' where id = 1;
COMMIT;

-- Transaction 2 -> T2
BEGIN;
SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;
UPDATE table1 set col1 = 'defg' where id = 2;
COMMIT;

我的问题：

最后一个例子中是否可能出现死锁？
换句话说，Postgres 是否会原子地同时锁定与 WHERE 条件匹配的所有行？
如果是，则我们能否还说 WHERE 子句的顺序不重要？因此在 T1 中我们可以使用：

SELECT * FROM table1 WHERE id IN (1, 2) FOR UPDATE;

在 T2 中我们可以使用：

SELECT * FROM table1 WHERE id IN (2, 1) FOR UPDATE;

如何避免发生死锁？

- radar155

1

你可以通过这种方式获得死锁。 - Laurenz Albe

请问您能否解释一下为什么？如果您能够附上相关的PostgreSQL文档信息，我将不胜感激。 - radar155

1个回答

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

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

- Erwin Brandstetter · Accepted Answer

最后一个例子容易出现死锁。

锁并不是“原子性”地获取，也就是说它们不会在同一事务（甚至同一语句）中几乎同时发生。锁是逐个获取的，并在事务结束时释放。

关键是，在IN子句中的项目列表并不一定规定了锁定行的顺序。您需要一个ORDER BY子句来实现这一点。或者像您已经成功尝试过的那样使用单独的语句。

单独的语句冗长且更昂贵。所以：

BEGIN;
SELECT FROM table1
WHERE  id IN (1,2)
ORDER  BY id             -- !
FOR    UPDATE;

UPDATE table1 set col1 = 'abcd' WHERE id = 1;
COMMIT;

只要对同一张表的所有写入访问都可靠地遵循相同的顺序，就不会发生死锁（来自此交互）。

手册：

最好的防御死锁的方法通常是通过确保使用数据库的所有应用程序以一致的顺序获取多个对象上的锁来避免它们。