Deadlocks are a common issue in database systems, particularly in high-concurrency environments. In this post, we’ll explore a typical Postgres deadlock scenario, understand why it occurs, and explore one possible way to prevent it using the SQL SELECT FOR UPDATE clause.

Reproducing a deadlock Link to heading

Let’s start by setting up a simple scenario to reproduce a deadlock in Postgres. We’ll use psql to connect to a database in Postgres, and we’ll create a test table called usr, and insert two rows into it:

postgres=# CREATE TABLE usr (uid int, name varchar(32));
CREATE TABLE
postgres=# INSERT INTO usr VALUES (1, 'jai');
INSERT 0 1
postgres=# INSERT INTO usr VALUES (2, 'kai');
INSERT 0 1
postgres=#

Now, let’s simulate a deadlock using two psql sessions, started in two terminals. We’ll follow the steps below in exact order:

In terminal 1, start a transaction and update row with uid 1 Link to heading

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE usr SET name = 'tim' WHERE uid = 1; -- Locks row with uid 1
UPDATE 1
postgres=*#

In terminal 2, start a transaction and update row with uid 2 Link to heading

postgres=# BEGIN;
BEGIN
postgres=*# UPDATE usr SET name = 'godot' WHERE uid = 2; -- Locks row with uid 2
UPDATE 1
postgres=*#

In terminal 1, update row with uid 2 Link to heading

postgres=*# UPDATE usr SET name = 'kim' WHERE uid = 2; -- Tries to lock row with uid 2
UPDATE 1

You will notice that the transaction is blocked right now and the control does not return to the psql prompt. That’s because the transaction in terminal 2 has already locked the row with uid 2.

Finally, in terminal 2, try to update row with uid 1 Link to heading

postgres=*# UPDATE usr SET name = 'walter' WHERE uid = 1; -- Tries to lock row with uid 1

At this point the two transactions are in a deadlock state. Postgres takes a short while to detect the deadlock condition. After it does detect it, it will choose to terminate one of the transactions, and spit out the following error on the console -

ERROR:  deadlock detected
DETAIL:  Process 345757 waits for ShareLock on transaction 606605; blocked by process 345734.
Process 345734 waits for ShareLock on transaction 606606; blocked by process 345757.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "usr"
postgres=!#

Postgres tries to resolve the deadlock by failing a transaction. You will notice, that the transaction in terminal 1 has proceeded, and the control has been returned back to the user, and the psql prompt is visible again. At this point you have a choice of rolling back both transactions, or choosing to commit the transaction in terminal 1 and retrying the transaction in terminal 2.

Understanding the deadlock Link to heading

The deadlock occurs due to the following sequence of events:

  • Terminal 1 acquires a RowExclusiveLock on the row with uid = 1.
  • Terminal 2 acquires a RowExclusiveLock on the row with uid = 2.
  • Terminal 1 attempts to update the row with uid = 2, but is blocked by Terminal 2’s lock.
  • Terminal 2 attempts to update the row with uid = 1, but is blocked by Terminal 1’s lock.

At this point, both transactions are waiting for each other to release their locks, resulting in a deadlock. Postgres detects this situation and terminates one of the transactions to resolve the deadlock.

Preventing deadlocks with SELECT FOR UPDATE Link to heading

One possible way to prevent such deadlocks is to use the SELECT FOR UPDATE clause to explicitly lock all the rows we intend to update. Here’s how to modify the approach:

Terminal 1 Link to heading

BEGIN;
SELECT uid, name FROM usr WHERE uid IN (1, 2) FOR UPDATE;
UPDATE usr SET name = 'tim' WHERE uid = 1;
UPDATE usr SET name = 'kim' WHERE uid = 2;
COMMIT;

Terminal 2 Link to heading

BEGIN;
UPDATE usr SET name = 'godot' WHERE uid = 2;
-- This will wait until Terminal 1's transaction is committed

By using SELECT FOR UPDATE, terminal 1’s transaction acquires RowShareLock on both rows (uid = 1 and uid = 2) at the beginning. This prevents terminal 2’s transaction from acquiring a lock on the row with uid = 2, effectively eliminating the potential for a deadlock.

Conclusion Link to heading

This deadlock is a common problem with Postgres, esp. with shared database system designs, typical of monoliths, where concurrent transactions from different services are trying to update the same data sets. There are other possible ways to mitigate the issue. One other possible way that can be explored is to order the updates. For example, if an update set has a unique key (say, a primary key field, or a field with a UNIQUE constraints), the update set could be ordered on that key. That would ensure that the transactions are avoiding circular waits like shown in the example. There is a lot more to be said about mitigation tecniques, including the interesting topic of optimistic concurrency control, but that can be the subject of a future post.