Learn what to do when PostgreSQL Error Deadlock Detected. Our PostgreSQL Support team is here to help you with your questions and concerns.

PostgreSQL Error Deadlock Detected | How to fix it

Have you been coming across a log message like the one below?

< 2021-09-10 11:25:59.843 IST >ERROR: deadlock detected
< 2021-09-10 11:25:59.843 IST >DETAIL: Process 6819 waits for ShareLock on transaction 8139; blocked by process 6711.
Process 6711 waits for ShareLock on transaction 8141; blocked by process 6819.
Process 6819: update test set abc = 12 where abc = 1;
Process 6711: update test set abc = 12 where abc =3;

According to our experts, this message indicates that we have run into a deadlock.

Today, we will look at deadlocks in Postgres applications and how to resolve them.

A deadlock is a situation where two or more processes or transactions are stuck in a circular wait, each waiting for the other(s) to release a resource, making it impossible for any of them to proceed.

In other words, it is like a classic stand-off situation.

Now, let’s try to decode the deadlock scenario in the Postgres log snippet above:

Transaction A attempts to update a table called “test” where the column “abc” has a value of 1, acquiring locks on all rows with “abc” = 1.

At the same time, Transaction B tries to update the same “test” table, but for rows with “abc” = 3, thereby acquiring locks on these rows.

Transaction A wishes to update rows with “abc” = 3, but it can’t proceed because Transaction B has locked those rows.

Likewise, Transaction B wants to update rows with “abc” = 1, but it’s stuck because Transaction A holds locks on those rows.

This deadlock situation results in a stalemate where neither transaction can make progress.

Postgres attempts to detect and resolve deadlocks automatically by rolling back one of the conflicting transactions.

Types of Locks in Postgres

Postgres uses various types of locks, each for a different purpose.

• Simple Deadlock
• Circular Wait Deadlock
• Resource Deadlock
• Serializable Deadlock
• Lock Timeout Deadlock
• Application-Level Deadlock

How to Prevent Deadlocks

An effective solution is to ensure that transactions always acquire locks in the same order. In other words, we have to define a consistent order in which our application accesses resources or rows. This is a practical way to prevent deadlocks.

• Limit the Number of Objects:

  Furthermore, avoid working with a large number of objects in a single transaction. Excessive objects can lead to deadlocks and also slow down the database.
• Implement a Pipeline:

  If ordering transactions is not a possibility, we can implement a pipeline at the application level. However, this should be the last resort, as it introduces complexity.

[Need assistance with a different issue? Our team is available 24/7.]

Conclusion

In brief, our Support Techs demonstrated what to do when a PostgreSQL error deadlock is detected.