PostgreSQL Deadlock between non-relation tables-CodePudding

2022-10-12 20:23:27 KST [40P01] [11983 (4)] ... ERROR:  deadlock detected
2022-10-12 20:23:27 KST [40P01] [11983 (5)] ... DETAIL:  Process 11983 waits for ShareLock on transaction 12179793; blocked by process
11893.
    Process 11893 waits for ShareLock on transaction 12179803; blocked by process 12027.
    Process 12027 waits for ExclusiveLock on tuple (1881,5) of relation 17109 of database 16384; blocked by process 11983.
    Process 11983: update B set ... where id=$1
    Process 11893: update A set ... where a=$1 and b=$2
    Process 12027: update B set ... where id=$1
2022-10-12 20:23:27 KST [40P01] [11983 (6)] ... HINT:  See server log for query details.
2022-10-12 20:23:27 KST [40P01] [11983 (7)] ... CONTEXT:  while locking tuple (1881,5) in relation "B"
2022-10-12 20:23:27 KST [40P01] [11983 (8)] ... STATEMENT:  update B set ... where id=$1

I found a deadlock log in PostgreSQL. A and B are unrelated tables. To solve this, we ask a few questions.

Why is process 11983 waiting for process 11893 ?
Does process 12027 use share lock even though it is an update statement?
How do I find the actual data for tuple(1881, 5) ?

CodePudding user response：

Your first question cannot be answered. Some locks were taken by prior statements in the same transactions, and locks are held until the end of the transaction. You have to look at your application code to find out what other statements are executed in these transactions.

Concerning your second question: the process is waiting for a share lock on a transaction, which is the customary thing that PostgreSQL does when it wait for a row lock. Row locks are not permanently stored in the lock table, but they are released when the transaction ends, so waiting for the transaction to end is the correct thing to do.

Concerning the third question:

SELECT * FROM b WHERE ctid = '(1881,5)';

That may find nothing, if the row has been updated or deleted in the meantime.