February 1, 2024
Summary: in this tutorial, you will learn how to troubleshooting the deadlocks in PostgreSQL.
Table of Contents
Getting ready
In any database management systems, deadlocks can occur due to concurrent resource locking. It is the database engines responsibility to detect the deadlocks, and its applications responsibility to prevent the deadlocks. The PostgreSQL engine has the ability to detect the deadlocks; it also provides a few features to the developers to prevent the deadlocks in their application code.
How to do it…
Let’s produce some simple deadlock situations, and we will see all the options that PostgreSQL provides to troubleshoot them:
Have two different database sessions and execute the SQL statements as follows:
| Session 1 | Session 2 |
|---|---|
| BEGIN; UPDATE test SET t=1 WHERE t=1; UPDATE test SET t=2 WHERE t=2; -- Waiting for the record 2 which is locked in session 2 ERROR: deadlock detected DETAIL: Process 10417 waits for ShareLock on transaction 452459; blocked by process 8913. Process 8913 waits for ShareLock on transaction 452458; blocked by process 10417. ROLLBACK; |
BEGIN; UPDATE test SET t=2 WHERE t=2; UPDATE test SET t=1 WHERE t=1; -- Waiting for the record 1 which is locked in session 1 END; |
From the preceding example, in session 1 we got the deadlock error due to mutual locking between session 1 and 2. From the preceding DETAIL message, it clearly says that the process 8913 waits for the transaction that holds by the 10417 process, and the 10417 process is waiting for the transaction that holds by the 8913 process.
As you see from the preceding example, deadlocks will not cause any data loss and it will only cause a transaction failure. To avoid these deadlock situations, we have to use the prelocking techniques, as mentioned in the following section.
Using FOR UPDATE
This is an approach that tries to avoid the deadlock issues, by pre-locking all the required records that it is going to update in that session. To pre-lock all the required tuples, we have to use the FOR UPDATE clause in the SELECT statement. Let’s see how we are going to fix the preceding problem using this approach:
| Session 1 | Session 2 |
|---|---|
| BEGIN; SELECT * FROM test WHERE t IN(1, 2) FOR UPDATE; UPDATE test SET t=1 WHERE t=1; UPDATE test SET t=2 WHERE t=2; END; |
BEGIN; SELECT * FROM test WHERE t IN(1, 2) FOR UPDATE; -- Waiting for the session to release the lock on records 1, 2 UPDATE test SET t=2 WHERE t=2; UPDATE test SET t=1 WHERE t=1; END; |
From the preceding example, session 2 transaction will be in waiting until session 1 transaction is complete. Here, we just made the two transactions to run in serializable fashion. That means that the transactions will not conflict with each other. This approach cannot be implemented for the SQL queries, which deal with SET operations. That is, usage of FOR UPDATE is restricted in SQL queries that do UNION/INTERSECT/EXCEPT operations.
Advisory locks
PostgreSQL provides advisory locks, which is an external locking mechanism that we can enforce from the application level, to achieve the concurrent data access. Let’s see how we will be avoiding deadlocks using advisory locks:
| Session 1 | Session 2 |
|---|---|
| BEGIN SELECT pg_advisory_lock(t) FROM test WHERE t IN (1,2); UPDATE test SET t=1 WHERE t=1; UPDATE test SET t=2 WHERE t=2; SELECT pg_advisory_unlock(t) FROM test WHERE t IN (1,2); END; |
BEGIN SELECT pg_advisory_lock(t) FROM test WHERE t IN (1,2); –Waiting for the session1 to release lock UPDATE test SET t=2 WHERE t=2; UPDATE test SET t=1 WHERE t=1; SELECT pg_advisory_unlock(t) FROM test WHERE t IN (1,2); END; |
In the preceding example, we are making the transactions as serialize, by using the advisory locks. The only disadvantage of using advisory locks is, we need an application to enforce the lock and unlock behavior. Also, the session level advisory locks will not release the lock, even if the transaction failed or rollbacked. However, when a session is closed, then all the associated advisory locks within that session will be released automatically.
Note: Refer to the following URL for more information about advisory locks: Explicit Locking.