涉及外键约束的死锁问题

Question

涉及外键约束的死锁问题

postgresqldeadlockdatabase-deadlocks

13

我希望更好地理解Postgres中的锁定机制。

假设一棵树可以有苹果（通过对苹果表的外键）。当选择更新一棵树时，似乎会获取对一个苹果的锁定。但是，即使其他人已经持有该苹果上的锁定，该操作也不会被阻塞。

为什么会这样呢？

附注：请不要建议删除“选择进行更新”。

场景

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"

- Kuba

这是哪个版本？它看起来最新版本中没有使用ShareLock。第二个事务中的UPDATE是否必要？ - John Tseng

2个回答

-3

看起来索引锁并没有在整个事务期间被持有。我认为主要问题是事务1执行了两次相同的UPDATE，但需要获取更多的锁来执行第二个UPDATE。

根据文档，索引锁仅被短暂持有。与数据锁不同，它们不会一直持有直到事务完成。让我们更详细地看一下时间轴。

事务1执行第一个UPDATE。这将在apples中的行级别上获取行级别锁。在操作期间，它还会在trees中的索引上获取锁。该事务尚未提交，因此行级别数据锁仍由事务1持有。但是，trees上的索引锁立即释放。不确定为什么Postgres对所有索引类型都这样做。

事务2出现并锁定了trees进行更新。这将同时锁定数据和索引。由于事务1已经释放了索引锁，因此这不会阻塞。这次，两个锁都持有到事务结束。不确定为什么保留了这个索引锁而释放了另一个锁。

事务1回来并尝试再次UPDATE。对apples的锁定是正常的，因为它已经拥有它。然而，对trees的锁定会被阻塞，因为事务2已经拥有它。

在事务2中添加UPDATE使其等待事务1，导致死锁。

编辑：

我回来调查一下，现在我安装了Postgres。实际上非常奇怪。我在提交事务2后查看了pg_locks。

事务1具有以下锁定：

apples_pkey和apples上的RowExclusive
其transactionid和virtualxid上的Exclusive

事务2具有以下锁定（以及许多其他无关的锁定）：

在trees_pkey上进行AccessShare
在trees上进行RowShare
在其transactionid和virtualxid上进行Exclusive
在apples_pkey和apples上进行RowExclusive
在apples中的一个tuple上进行Exclusive

事务2也在等待获取事务1的Share锁。

有趣的是，两个事务可以在同一张表上持有RowExclusive锁。然而，Exclusive锁与Share冲突，因此事务2正在等待事务1的事务ID。文档提到了事务锁作为等待其他事务的一种方式。因此，看起来事务2虽然已提交，但仍在等待事务1。

当事务1继续时，它想在事务2上获取共享锁，这就导致了死锁。为什么它要在事务2上获取共享锁？我不太确定。文档暗示pg_locks中没有此信息。我猜这可能与MVCC有关，但对我来说仍然是个谜。

- John Tseng

1

这是由于外键锁而不是索引锁，参见正确的Denis答案。 - RichVel

@RichVel 感谢您提出这个问题。我进一步调查了一下，看起来我们都错了。我对这些事务锁的工作原理感到非常困惑。 - John Tseng

1

真正的困惑更多地属于提问而不是回答的领域。另外，“Postgre”对于Postgres来说明确是不正确的。（http://wiki.postgresql.org/wiki/Identity_Guidelines） - Erwin Brandstetter

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

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

- Denis de Bernardy · Accepted Answer

我猜想您可能遇到了与具体实现相关的问题...

具体而言，您的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释放对trees的锁，以便完成对apples的更新语句；但是事务2正在等待事务1完成以便获取对apples的锁，以便开始其对apples的更新语句。

因此，Postgres退出了死锁。