postgres deadlock without explicit locking

问题:

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?

回答1:

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);

Session #1 does:

begin;
insert into a values(1);

Then session #2 does:

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

Then session #1 does:

insert into b values(1);

And then the deadlock occurs:

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.

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.

回答2:

I would suspect hash indexes first.

• Switch any hash-indexes you have to B-tree
• Use Serializable isolation level if it seems appropriate.