Search code examples
postgresqltransactionsdatabase-deadlocks

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?

Solution

  • 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.