Dealing with deadlocks in postgresql

In an ideal production environment, we all would have faced a situation where we get a deadlock from our database. There might be multiple reasons behind your table(s) getting deadlocked. We shall go through couple of scenarios and potential fix to handle deadlock.

Scenario 1 : The most common scenario.

Thread 1 : update employee set name = ‘ram’ where id= 1

→ This statement locks the row where id=1

Thread 2: update employee set name = ‘akash’ where id = 2

→ This statement locks the row where id=2

Thread 1: update employee set name = ‘prasanna’ where id = 2

→ This statement will try to acquire lock on this row, but it cannot since the lock has been made already by thread2. So, it will wait for thread 2 to release the lock.

Thread 2: update employee set name = ‘prashant’ where id = 1

→ This statement will try to acquire lock on this row, but it can’t since the lock has been made already by thread1. So, it will wait for thread1 to release the lock, which will never happen since both of them are holding a lock against each other, creating a deadlock.

Scenario 2 :

While handling same request concurrently.

Thread 1 :

update employee set role= ‘dev’ where name in (‘ram’,’prashant’,’aakash’)

→ IN clause need not execute the update in the specified order. For instance, the above statement can execute the update in the following order ‘prashant’,’aakash’, ‘ram’.

Thread 2:

update employee set role= ‘dev’ where name in (‘ram’,’prashant’,’aakash’)

→ Same condition from thread 1 applies here and the above statement can execute the query in this order → ‘ram’,’prashant’,’aakash’

So, What’s the problem ?

Thread 1, first locks the row where name = ‘prashant’ and Thread2 , locks the row where name =’ram’, we would get a deadlock again as the next set of statements will depend on each of these transactions to release the lock.

How can we handle deadlock then ?

In PostgreSQL, we have an option for locking all the rows inside the transaction at once and release them all once after the transaction is done.

Let’s take an example to see how we actually can do that,

BEGIN;

SELECT 1 from Employee where id in (1,2,3) FOR UPDATE;

update employee set role= ‘dev’ where name in (‘ram’,’prashant’,’aakash’);

END;

using SELECT… FOR UPDATE will apply the lock on all the rows at once specified in the where condition, thereby releasing the lock once after the transaction is completed. Cool isn’t it:)

Please be cautious on using SELECT…FOR UPDATE as it has an impact on the foreign Key Column and the referential table. If in case, the referential table has a change specifically in the referenced field, it will be locked until the transaction is done.

Thanks for reading this blog. Hope it helps :)