Dealing with deadlocks

Using this recipe, we will be troubleshooting the deadlocks in PostgreSQL.

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 a simple deadlock situation 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...