Postgres documentation recommends avoiding these kinds of deadlocks by ensuring transactions acquire locks in a consistent order. T2 tries to access row X, but its locked by T1. T1 tries to access row Y, but its locked by T2.
#Postgres deadlock update
UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = 'X' Įxecution sequence: // T1 updates row X and acquires a lock. Transaction 2 (T2): UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = 'Y' UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = 'Y' Transaction 1 (T1): UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = 'X' While transactions are running, postgres will lock rows, which under certain scenarios leads to deadlock. Postgres can get into this state if two transactionsĬoncurrently modify a table. Postgres is telling us that process 1 is blocked by process 2 and process 2 is blocked by process 1. 15:24:23.326 UTC STATEMENT: UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = $6 15:24:23.326 UTC CONTEXT: while locking tuple (7,7) in relation "query-result-cache" 15:24:23.326 UTC HINT: See server log for query details. Process 2: UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = $6 Process 1: UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = $6 Process 2 waits for ShareLock on transaction 198232 blocked by process 1.
15:24:23.326 UTC DETAIL: Process 1 waits for ShareLock on transaction 198234 blocked by process 2. 15:24:23.326 UTC ERROR: deadlock detected The first thing to do is to look at the postgres logs. Recently we started running into deadlocks in our application.
0 kommentar(er)
