Postgresql deadlock from not directly related tables

Question

Running postgresql 11.2

I've got 3 tables, Table1, Table2 and Table3.

Table2 and Table3 are linked to Table1.

So, both have a foreign key and a field for this:

"fk38dc51d86836z0e5" FOREIGN KEY (table1_id) REFERENCES table1(id)

I recently got a deadlock on these 2 tables, despite the involved queries not using the foreign key field.

Process 19819 waits for ShareLock on transaction 254244062; blocked by process 19930.
    Process 19930 waits for ShareLock on transaction 254244063; blocked by process 19819.
    Process 19819: update Table1 set lastUpdated=$1, user_id=$2 where id=$3
    Process 19930: update Table2 set lastUpdated=$1, version=$2, content=$3, extra=$4 where id=$5 and version=$6

The only link between these 2 tables is their link to Table1. They are not directly linked to each other.

But neither query uses the foreign key to Table1 as part of their query.

What is going on here? Why the deadlock at all?

Conflicting locks may have been taken by other statements earlier in the same transaction. — Laurenz Albe

pifor pifor · Accepted Answer · 2020-03-02T16:53:18

Deadlocks can happen without foreign keys: the most likely cause is that 2 concurrent transactions take the same locks on the same rows but in different order.

For example in session 1:

postgres=# begin;
BEGIN
postgres=# update t1 set x=1 where x=0;
UPDATE 1
postgres=# update t2 set x=1 where x=0;
UPDATE 1
postgres=#

And in session 2:

postgres=# begin;
BEGIN
postgres=# update t2 set x=2 where x=0;
UPDATE 1
postgres=# update t1 set x=2 where x=0;
ERROR:  deadlock detected
DETAIL:  Process 26871 waits for ShareLock on transaction 191533; blocked by process 26777.
Process 26777 waits for ShareLock on transaction 191534; blocked by process 26871.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,3) in relation "t1"
postgres=#

Postgresql deadlock from not directly related tables

1 Answers