Understand Deadlock in Postgres and how to fix it

In this we will understand what a deadlock is in Postgres and how we can possibly solve this at the application level.

Deadlock in Postgres

if you have seen a log like this in Postgres :

< 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;

you have seen a deadlock. Now let’s understand what a deadlock is. Deadlock logically signifies person A is waiting for person B and in turn person B is waiting on Person A , now both of them cannot move forward, so a deadlock.

Now lets try to decode the deadlock in postgres log above:

  • In this i have created two transaction
  • transaction A first update table <test> where column <abc> value is 1 taking lock on all rows with value abc = 1.
  • then transaction B update table <test> where column <abc> value is 3 taking lock on all rows with value abc = 3.
  • now transaction A wants to update table <test> where column <abc> value is 3 but cannot do it as transaction B is having a lock on rows with <abc> column value 3.
  • now transaction B wants to update table <test> where column <abc> value is 1 but cannot do it as transaction A is having a lock on rows with <abc> column value 1.
  • now these two transactions are part of a deadlock and no one can move forward.
  • as soon a postgres detect this it will try to break the deadlock and rollback one of the transactions.

There are various kinds of lock which are there is postgres, following picture shows you which locks are there and their blocking type:

We will not go in much detail of the locks type in here.

How to resolve deadlock

  • Order the transaction is such a way so that locks are always taken in the same order (very common basic solution for this)
    • Eg : your application lets say need to work on 100 objects at a time in a single transaction and multiple such threads can work at the same time
    • In this case sort those 100 objects on some common identifier present in that same.
    • Now do the transactions , now these threads will not create the deadlock as shown.
  • Normally do not work on lots of objects in single transaction, this is not just bad for deadlock but also it might be a bigger transaction which can slow down the DB.
  • If you cannot order or finalize you can create a pipeline in the application level only – normally one should not do this unless no other solution is feasible.

Now if in your case you are still not able to resolve the deadlock in your system , do let us know on the comments section we will try to help you out.