如何在Postgres中避免三方死锁？

Question

我在Postgres中遇到了三方死锁，并且我不太明白是什么原因导致了这种情况。日志消息如下：

    Process 5671 waits for ExclusiveLock on tuple (33,12) of relation 31709 of database 16393; blocked by process 5652.
    Process 5652 waits for ShareLock on transaction 3382643; blocked by process 5670.
    Process 5670 waits for ExclusiveLock on tuple (33,12) of relation 31709 of database 16393; blocked by process 5671.
    Process 5671: UPDATE "user" SET
                    seqno = seqno + 1,
                    push_pending = true
                  WHERE user_id = $1
                  RETURNING seqno
    Process 5652: UPDATE "user" SET
                    seqno = seqno + 1,
                    push_pending = true
                  WHERE user_id = $1
                  RETURNING seqno
    Process 5670: UPDATE "user" SET
                    seqno = seqno + 1,
                    push_pending = true
                  WHERE user_id = $1
                  RETURNING seqno

关系31709是"user"表。在三个事务中，user_id是相同的。

这似乎不是你在文档中看到的普通死锁情况。我没有以错误顺序更新此表的多行。我怀疑RETURNING子句与此有关，但我无法确定原因。有什么想法可以解决这个问题或进一步调试吗？

更新Erwin在评论中的问题：这是Postgres 9.3版本。此事务中还有其他命令，尽管我不认为它们会触及“用户”表。表上有一个触发器，用current_timestamp()更新updated_at列：

CREATE OR REPLACE FUNCTION update_timestamp() RETURNS TRIGGER AS $$
BEGIN
    NEW.updated_at = now();
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

我会在能够重现此问题时查看交易细节；事后我正在分析日志。

更新#2：我使用LOCK_DEBUG重新构建了Postgres，并尝试使用trace_locks=on来理解锁定顺序。

更新的死锁消息如下：

Process 54131 waits for ShareLock on transaction 4774; blocked by process 54157.
Process 54157 waits for ExclusiveLock on tuple (1,16) of relation 18150 of database 18136; blocked by process 54131.

我可以清楚地看到ExclusiveLock的阻塞：

2014-08-05 10:35:15 EDT apiary [54131] 2/316 4773 LOG:  00000: LockAcquire: new: lock(0x10f039f88) id(18136,18150,1,16,3,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(ExclusiveLock)
2014-08-05 10:35:15 EDT apiary [54131] 2/316 4773 LOG:  00000: GrantLock: lock(0x10f039f88) id(18136,18150,1,16,3,1) grantMask(80) req(0,0,0,0,0,0,1)=1 grant(0,0,0,0,0,0,1)=1 wait(0) type(ExclusiveLock)
2014-08-05 10:35:15 EDT apiary [54157] 3/188 4774 LOG:  00000: LockAcquire: found: lock(0x10f039f88) id(18136,18150,1,16,3,1) grantMask(80) req(0,0,0,0,0,0,1)=1 grant(0,0,0,0,0,0,1)=1 wait(0) type(ExclusiveLock)
2014-08-05 10:35:15 EDT apiary [54157] 3/188 4774 LOG:  00000: WaitOnLock: sleeping on lock: lock(0x10f039f88) id(18136,18150,1,16,3,1) grantMask(80) req(0,0,0,0,0,0,2)=2 grant(0,0,0,0,0,0,1)=1 wait(0) type(ExclusiveLock)

（格式为日期 程序 [pid] 虚拟txid txid 消息）

但我不知道ShareLock在哪里创建，或者事务4773为什么会阻塞事务4774。当查询pg_locks表时，我看到类似的结果：始终有一个事务正在等待另一个事务的ShareLock，而该事务正阻塞于第一个事务的元组。有没有建议可以挖掘ShareLock源代码？

更新3：我需要更新LOCK_DEBUG_ENABLED行内函数以无条件返回true才能看到ShareLock的创建。一旦我这样做了，我开始看到它们的创建：

2014-08-05 12:53:22 EDT apiary [76705] 2/471 6294 LOG:  00000: LockAcquire: lock [6294,0] ExclusiveLock
2014-08-05 12:53:22 EDT apiary [76705] 2/471 6294 LOG:  00000: LockAcquire: new: lock(0x115818378) id(6294,0,0,0,4,1) grantMask(0) req(0,0,0,0,0,0,0)=0 grant(0,0,0,0,0,0,0)=0 wait(0) type(ExclusiveLock)
2014-08-05 12:53:22 EDT apiary [76705] 2/471 6294 LOG:  00000: GrantLock: lock(0x115818378) id(6294,0,0,0,4,1) grantMask(80) req(0,0,0,0,0,0,1)=1 grant(0,0,0,0,0,0,1)=1 wait(0) type(ExclusiveLock)
2014-08-05 12:53:22 EDT apiary [76706] 4/153 6295 LOG:  00000: LockAcquire: lock [6294,0] ShareLock
2014-08-05 12:53:22 EDT apiary [76706] 4/153 6295 LOG:  00000: LockAcquire: found: lock(0x115818378) id(6294,0,0,0,4,1) grantMask(80) req(0,0,0,0,0,0,1)=1 grant(0,0,0,0,0,0,1)=1 wait(0) type(ShareLock)
2014-08-05 12:53:22 EDT apiary [76706] 4/153 6295 LOG:  00000: WaitOnLock: sleeping on lock: lock(0x115818378) id(6294,0,0,0,4,1) grantMask(80) req(0,0,0,0,1,0,1)=2 grant(0,0,0,0,0,0,1)=1 wait(0) type(ShareLock)

但我仍然不确定为什么会创建那个ShareLock，以及为什么6295（在这种情况下）必须等待6294。

- Joe Shaw

---

显然，您的Postgres版本是什么？这些命令在更大的事务上下文中运行吗？表上有触发器吗？此外，“transaction 3382643”的详细信息至关重要。 - Erwin Brandstetter

感谢 @ErwinBrandstetter。我已经添加了更多细节到问题中。 - Joe Shaw

你正在使用哪个隔离级别？ - jjanes

表中是否有外键？另外，您使用的是9.3的哪个小版本？ - jjanes

@JoeShaw：指向更新表的FK约束也是候选项。相关行可能需要等待，以确保提交后引用的键仍然存在。 - Erwin Brandstetter

Answer 1

这可能是死锁发生的原因。每个表都有一个对用户表的user_id的外键。当您插入具有外键约束的表时，Postgres需要锁定被引用表的行，以确保在插入引用该行的行（并在提交时违反FK约束）时不会删除它。现在应该是共享锁。

看起来所有涉及用户的表的插入/更新也会在主表上插入后在用户表上更新用户seq。这些更新需要独占锁，并被不属于当前事务的任何共享锁阻塞。如果两个同时发生，它们就会死锁。

例如，两个并发插入到media_size和source的事务可能会死锁，如下所示：

T1 T2 ----------------------------------------------------------------------- 1. 插入媒体大小 1a. 排他锁定媒体大小行 1b. 用户行（FK）上的共享锁定 2. 插入来源 2a. 排他锁定源行 2b. 用户行（FK）上的共享锁定

3. 更新用户序列 3a. 用户行上的排他锁定（在2b上阻塞） 4. 更新用户序列 4a. 用户行上的排他锁定（在1b上阻塞） 5. 死锁

我认为将更新用户seq步骤切换为首先执行是有意义的，因为它将在尝试获取共享锁之前强制T1和T2阻塞（它不需要共享锁，因为它已经拥有一个排他锁）。

- Jason Wilder

Answer 2

假设默认的事务隔离级别读取已提交。 UPDATE语句总是锁定更新的行。尝试更新相同行的并发事务无法继续，直到第一个事务回滚或提交。 RETURNING子句与该问题无关。

死锁也可能发生在没有Process 5671的情况下。这只是另一个排队等待相同行的事务，介于其间。 Process 5670和Process 5652实际上正在死锁，很可能是由于同一事务中的其他命令引起的。较不可能的候选者将是表上的触发器。

尝试将事务分解为更小的部分，以按相同的顺序更新表的行。然后它们不能交错。

外键

由于您在后面的评论中提到了外键：它们也可能在死锁中发挥作用。 Postgres 9.3引入了新的锁定级别来解决这个问题：

FOR KEY SHARE和FOR NO KEY UPDATE

请参阅Michael Paquier在此博客文章中的详细信息。

这应该使外键约束问题变得不那么严重。尽管如此，仍未排除。

最新的补丁版本

自9.3.0版本以来，锁定机制已经进行了一些小修复。升级到最新的补丁版本，可能会有所帮助。

查看当前锁定

回答您的评论： 您可以在系统目录视图pg_locks中找到（大多数）当前持有的锁定。确保在得出结论之前阅读手册并可能更多内容。

- Erwin Brandstetter

---

谢谢关于RETURNING子句的信息。我会看看是否可以找到以不同顺序更新行的项目。但在这些情况下，每个事务中运行的代码完全相同。如果同一行在同一事务中多次更新，会发生死锁吗？这似乎比表和行以不同的顺序被访问更有可能发生。 - Joe Shaw

@JoeShaw：一个事务永远不会阻塞自己，一旦一个事务锁定了一行，它将不会在完成之前释放。所以不会。 - Erwin Brandstetter

有没有办法在日志中查看哪些锁被占用了？我只能在死锁和https://wiki.postgresql.org/wiki/Lock_Monitoring的查询中看到原始问题的这些UPDATE语句。我不明白为什么两个相同的事务的锁会出现顺序混乱的情况。 - Joe Shaw

@JoeShaw：如果您发布您的代码，也许其他人可以看到它可能发生了什么... - Erwin Brandstetter

@JoeShaw：事务可能不会完全相同，即使它们是相同的，一旦涉及多个表和它们之间的外键约束，情况可能会变得复杂。我在答案中添加了更多内容。 - Erwin Brandstetter

@ErwinBrandsetter：谢谢。我添加了一些LOCK_DEBUG跟踪来查看锁的获取时间和位置，但是我目前卡住的地方是一个事务的ShareLock对另一个事务的影响：这是我在pg_locks和死锁检测器中看到的，但它从未出现在日志中。有可能找到该锁的来源吗？ - Joe Shaw