如何确定查询是否可能导致Postgres中的死锁？

Question

在实现数据库查询时，我经常想知道当许多客户端并行执行时，我正在编写的查询是否会出现潜在的死锁。通常，我只能通过与大量客户机并行运行查询来发现，以查看是否在实践中得到了臭名昭著的“检测到死锁”。

我想知道是否有一种更正式的方法来确定查询是否有死锁的可能性？我所说的“更正式”的意思是：

• 是否有可能检查/解释查询在引擎盖下正在做什么，即使在隔离执行时也是如此，并从一些内部日志判断是否有可能出现死锁？例如，如果我能够看到所需的确切锁及其顺序，我可能会预测它是否会死锁。实际上，我只是不知道查询是锁定在表上还是在行级别上。在这种情况下，假设是单个表锁很好，但是非原子地锁定多个行会带来死锁的可能性，因为查询的另一个实例可能会以不同的顺序锁定行。
• 如果通过检查日志是不可能的，那么在可能出现死锁的查询中是否有一些通用模式可供查找呢？

Answer 1

我不认为对此有一种有原则的正式办法。此外，尽管可能发生这种情况，但在第二个会话中运行同一个语句的单个语句死锁并不常见。通常，在同一事务中运行并同时接受锁的其他语句也同样重要。

减少死锁的可能性的简单技巧是：

• 尽可能缩短交易时间
• 不要在单个事务中绑定更多的数据修改，这是保证数据一致性所必需的。

这样做后，您可以通过确保资源总是按照一定的顺序锁定，从而争取更多的资源。这个顺序通常很难确定，并且将取决于语句所选择的执行计划(您可以在EXPLAIN中找到)。举个例子：

UPDATE tab
SET col = 42
WHERE val = 'changeme';

不能保证按一定顺序锁定行。相反，您可以尝试以下一些比较昂贵的方法，但在这方面会做得更好：

WITH myrows AS (
   SELECT id
   FROM tab
   WHERE val = 'changeme'
   ORDER BY id
   FOR NO KEY UPDATE
)
UPDATE tab
SET col = 42
FROM myrows
WHERE tab.id = myrows.id;

我并不是说，后一种声明永远不会与任何东西陷入僵局，但与前一种声明相比，不与其自身陷入僵局的可能性更大。