I am using Postgresql 11.
I have found an error similar to following in my logs:
org.postgresql.util.PSQLException: ERROR: deadlock detected
Detail: Process 332091 waits for ShareLock on transaction 99896257; blocked by process 332093.
Process 332093 waits for ShareLock on transaction 99923910; blocked by process 332091.
Hint: See server log for query details.
Where: while locking tuple (884208,33) in relation "tab_1"
SQL statement "SELECT 1 FROM ONLY "sch"."tab_1" x WHERE "tab_1_key" OPERATOR(pg_catalog.=) $1 FOR KEY SHARE OF x"
SQL statement "INSERT INTO sch.tab_2 (col1, col2, ..., coln)
SELECT
col1,
col2,
...
coln
FROM
sch.tab_3
LEFT JOIN sch.tab_4
ON tab_4.id = tab_3.id
LEFT JOIN sch.tab_5
ON tab_5.id = tab_3.id
So basically the first query looks like some DB internal query, nothing from my code.
And it seems like it is making deadlock with my query. But they are not related by any table.
Any ideas about how to track the reason for the deadlock in such a case?
Best Answer
These queries are generated internally by the triggers that implement foreign key constraints.
For example, adding a row to a table with a foreign key constraint will add such a key share lock on the corresponding row in the referenced table, so that nobody can delete it or modify the primary key until the inserting transaction is done.
This can cause deadlocks between transactions that modify both tables.
When searching for the statements at fault, don't forget that it was not necessarily the last statement in the transaction (that is shown in the log message) that took the problematic lock.
One way to proceed is to set
log_min_duration_statement
to 0, and when the deadlock strikes again, examine all statements run by the affected transactions. The other way is to review the application code to find out which statements were executed in the transactions.