The way we can avoid deadlocks in this scenario is to tell postgres to explicitly lock the rows before the update. With two concurrent update queries, postgres can end up in a deadlock in the same way that an application could cause postgres to deadlock. BUT! Postgres updates rows in arbitrary order, postgres has no ORDER BY in the UPDATE command. Potentially worse scenarios exist if the update affects multiple rows e.g: UPDATE table_a SET x = 'value' WHERE id IN ($1, $2, $3)Īs before postgres will lock rows in the update table and the locks will be held until the transaction commits or rolls back. Transaction 2 (T2): UPDATE "query-result-cache" SET "identifier" = $1 WHERE "query" = 'X' 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. 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" SQL Sentry gives DBAs the ability to share information with the team via email and provides recommendations on ways to resolve the deadlock. 15:24:23.326 UTC HINT: See server log for query details. Depending on the type of deadlock, a database admin may need to share deadlock information with developers to come up with an optimal resolution. 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 Comments
Leave a Reply. |
AuthorWrite something about yourself. No need to be fancy, just an overview. ArchivesCategories |