postgresql - 涉及外键约束的死锁

Question

我想更好地理解 postgres 中的锁定机制。

假设树可以有苹果(通过苹果表上的外键)。似乎在选择一棵树进行更新时，锁定是在苹果上获得的。但是，即使其他人已经持有此苹果的锁，该操作也不会被阻止。

为什么会这样？

附注请不要建议删除“select for update”。

场景

Transaction 1      Transaction 2
BEGIN              .
update apple;      .
.                  BEGIN
.                  select tree for update;
.                  update apple;
.                  --halts because of the other transaction locking an apple
update apple;      .
-- deadlock        .
                   COMMIT
                   --transaction succeeds

代码

如果你想在你的 postgres 中尝试它 - 这是你可以复制/粘贴的代码。

我有以下数据库模式

CREATE TABLE trees (
    id       integer primary key
);

create table apples (
    id       integer primary key,
    tree_id  integer references trees(id)
);

和非常简单的数据

insert into trees values(1);
insert into apples values(1,1);

有两个简单的事务。一个是更新苹果，第二个是锁定一棵树并更新一个苹果。

BEGIN;
    UPDATE apples SET id = id WHERE id = 1;
    -- run second transaction in paralell
    UPDATE apples SET id = id WHERE id = 1;
COMMIT;

BEGIN;
    SELECT id FROM trees WHERE id = 1 FOR UPDATE;
    UPDATE apples SET id = id WHERE id = 1;
COMMIT;

当我运行它们时 - 第一个事务的第二次更新发生死锁。

ERROR:  deadlock detected
DETAIL:  Process 81122 waits for ShareLock on transaction 227154; blocked by process 81100.
Process 81100 waits for ShareLock on transaction 227153; blocked by process 81122.
CONTEXT:  SQL statement "SELECT 1 FROM ONLY "public"."trees" x WHERE "id" OPERATOR(pg_catalog.=) $1 FOR SHARE OF x"

Answer 1

只是一个疯狂的猜测:你遇到了一个与实现细节相关的问题......

具体来说，您的select tree for update 语句获取对树的独占锁。并且 update apples 语句获得相关苹果的独占锁。

当您在苹果上运行更新时，Postgres 的每行外键相关触发器会触发，以确保 tree_id 存在。我不记得它们的确切名称，但它们在目录中，文档中有一些点点滴滴明确或隐含地引用它们，例如:

create constraint trigger ... on ... from ...

http://www.postgresql.org/docs/current/static/sql-createtrigger.html

无论如何，这些触发器将运行以下内容:

select exists (select 1 from trees where id = 1);

这就是您的问题所在:由于 select for update 导致的独占访问使其等待事务 2 释放对树的锁定以完成其对苹果的更新语句，但是事务 2正在等待事务 1 完成以获得对苹果的锁定，从而开始对苹果的更新语句。

结果，Postgres 陷入僵局。