没有显式锁定的 postgres 死锁 [英] postgres deadlock without explicit locking

问题描述

我使用 PostgreSQL 9.2，我没有在任何地方使用显式锁定，也没有 LOCK 语句和 SELECT ... FOR UPDATE.但是，最近我收到 ERROR: 40P01: deadlock detected.但是，检测到死锁的查询包含在事务块中.无论如何，这是怎么回事?

I use PostgreSQL 9.2, and I do not use explicit locking anywhere, neither LOCK statement nor SELECT ... FOR UPDATE. However, recently I got ERROR: 40P01: deadlock detected. The query where deadlock was detected is wrapped in transaction block though. Anyway, how comes it?

推荐答案

您不需要任何明确的 LOCK 即可陷入僵局.这是一个从头开始的非常简单的演示，只有插入:

You don't need any explicit LOCK to go into a deadlock. Here's a very simple demo from scratch with only INSERTs:

create table a(i int primary key);
create table b(i int primary key);

会话 #1:

begin;
insert into a values(1);

然后会话 #2 会:

begin;
insert into b values(1);
insert into a values(1);
-- here it goes into waiting for session #1 to finish its transaction

然后会话 #1 会:

insert into b values(1);

然后发生死锁:

错误:检测到死锁
详细信息:进程 9571 等待 ShareLock 打开交易 4150；被进程 9501 阻止.
进程 9501 等待ShareLock 交易 4149；被进程 9571 阻止.
提示:见查询详情的服务器日志.

ERROR: deadlock detected
DETAIL: Process 9571 waits for ShareLock on transaction 4150; blocked by process 9501.
Process 9501 waits for ShareLock on transaction 4149; blocked by process 9571.
HINT: See server log for query details.

同样的情况也可能发生在简单的 UPDATE 或 UPDATE 和 INSERT 的组合中.这些操作采用隐式锁，如果它们以不同的顺序发生在不同的会话中，它们可能会死锁.

The same could happen with simple UPDATEs or a combination of UPDATEs and INSERTs. These operations take implicit locks, and if they happen in different sessions in different orders, they may deadlock.

这篇关于没有显式锁定的 postgres 死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！