主页 / dba / 问题 / 257217
Accepted
John Bachir
Asked: 2020-01-13 11:19:04 +0800 CST

为什么单个 UPDATE 查询会出现死锁？

  • 772

我有两个进程并行执行这样的代码：

begin;
update foos set unread=false where owner_id=123 and unread=true;
commit;

这会导致死锁。

我对导致死锁的原因的理解就像这个问题中描述的场景一样，“交织”的 UPDATE 语句以不同的顺序更新两个不同的行。我不明白单个 UPDATE 语句如何导致死锁。我无法在我的开发环境中使用两个并行 psql 会话来复制死锁场景。我对为什么无法复制它的猜测：

  1. 我误解了创建死锁错误的代码，实际上每个事务中有多个 UPDATE 语句
  2. “交织”方面正在发生，但在覆盖多行的 UPDATE 语句“内部”，因此很难复制。

这个单一的 UPDATE 是否有可能造成死锁？

postgresql update

2 个回答

  1. Best Answer

    您的语句修改了几行。这些行中的每一行在更新时都被锁定。

    并发事务中的语句很可能已经锁定了这些行之一，从而阻塞了您的UPDATE. 如果并发事务随后尝试锁定您UPDATE已锁定的行之一，则会出现死锁。

    • 12

  2. Laurenz 解释了可能导致死锁的机制，您自己已经包含了一个链接，指向 Kevin 的更详细解释：

    以下是如何复制死锁的分步说明 - 使用方式与普通UPDATE方式相同SELECT .. FOR UPDATE：

    现在，如何避免这个问题？
    如果您要更新大部分或全部表 - 而且您有能力 - 只需对表进行写锁定。通常，这不是要走的路。否则，三种不同的方法：

    1. 一致的顺序

    该手册在有关死锁的章节中有此建议：

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

    不知道为什么仍然没有ORDER BYfor UPDATE。但这就是我们必须解决的问题。而是将行锁定SELECT ... FOR UPDATE在同一事务中-就像您已经尝试过的那样，正如您之前的问题所表明的那样。您只是忘记了基本的确定性ORDER BY：

    开始;
SELECT FROM foos WHERE owner_id = 123 AND 未读
订购者？？？  -- 任何确定性顺序，PK 将是一个明显的候选者
更新；

UPDATE foos SET unread = false WHERE owner_id = 123 AND unread;
结尾;

    显然，所有潜在的竞争事务都必须以相同的顺序获取锁。

    2.跳过锁定的行

    仅处理未锁定的行：

    开始;
SELECT FROM foos WHERE owner_id = 123 AND 未读
——订购者？？？-- 在这种情况下是可选的
对于更新跳过锁定；

UPDATE foos SET unread = false WHERE owner_id = 123 AND unread;
结尾;

    如果您确定跳过的行已被执行相同操作的竞争事务处理，那么您就完成了。（你确定吗？）
    否则，为了确保，请跟进检查：

    SELECT EXISTS (SELECT FROM foos WHERE owner_id = 123 AND unread);

    写入器不会阻塞读取器，读取器不会阻塞写入器，所以这会返回TRUE，直到最后一行都成功更新。循环上面的UPDATE块，然后循环（有适当的延迟），直到你得到FALSE. 然后你就完成了。

    ORDER BY对于会增加大量成本的大型套装来说可能更便宜。ORDER BYOTOH，如果有匹配的索引，添加仍然有意义......

    3.一次一个

    与上面类似，只是一次只更新一行。通常更昂贵，但任何死锁的可能性都会被消除——如果做得好的话。当处理单行已经需要很长时间时，请考虑这一点。

    详解（大多也适用于上述）及说明：

    • 12

相关问题