Skip to main content
PostgreSQL · lesson 5 of 7

Intermediate

6

Advanced

1

Also part of PostgreSQL

7
Open the PostgreSQL page →
PostgreSQL intermediate Lesson 5 of 7

Also part of: Databases SQL

A Real Deadlock, and Why Lock Order Fixed It

One error inside a transaction poisons every statement after it until ROLLBACK — and two transactions locking the same two rows in opposite order produces a real 'deadlock detected' error, not a hang.

By SkillByExample · Published

postgresql transactions locking deadlock concurrency

Postgres transactions fail in two very different ways — one where a single mistake poisons everything after it, and one where two transactions can trap each other completely.

A transaction aborts entirely on the first error

BEGIN;
SELECT 1/0;
SELECT 'still in transaction?';
ROLLBACK;
BEGIN
ERROR:  division by zero
ERROR:  current transaction is aborted, commands ignored until end of transaction block
ROLLBACK

The division by zero was expected to fail — what surprises people is the second error. Once a transaction hits an error, Postgres refuses to run anything else inside it, successful-looking statement or not, until ROLLBACK (or COMMIT, which behaves the same way on an already-dead transaction) explicitly ends it.

The bug: a real deadlock

Two transactions, each updating the same two rows in opposite order:

-- Session A
BEGIN;
UPDATE books SET title = title WHERE id = 1;
SELECT pg_sleep(3);
UPDATE books SET title = title WHERE id = 2;
COMMIT;
-- Session B, started while A is mid-sleep
BEGIN;
UPDATE books SET title = title WHERE id = 2;
UPDATE books SET title = title WHERE id = 1;
COMMIT;

Session A’s result:

BEGIN
UPDATE 1
 pg_sleep
----------

(1 row)

ERROR:  deadlock detected
DETAIL:  Process 408 waits for ShareLock on transaction 778; blocked by process 410.
Process 410 waits for ShareLock on transaction 777; blocked by process 408.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (3837,35) in relation "books"

Session B’s result:

BEGIN
UPDATE 1
UPDATE 1
COMMIT

A locked row 1, then tried to lock row 2 (held by B). B locked row 2, then tried to lock row 1 (held by A). Neither could proceed — a real circular wait. Postgres’s deadlock detector found the cycle, picked session A as the victim, and aborted it with a real error naming the exact processes and transaction IDs involved. B, freed from the cycle, completed normally.

The fix: always lock in the same order

Same two transactions, both now touching row 1 before row 2:

-- Session A (unchanged)
BEGIN;
UPDATE books SET title = title WHERE id = 1;
SELECT pg_sleep(3);
UPDATE books SET title = title WHERE id = 2;
COMMIT;
-- Session B, lock order now matches A's
BEGIN;
UPDATE books SET title = title WHERE id = 1;
UPDATE books SET title = title WHERE id = 2;
COMMIT;

Both sessions now complete successfully — B’s output:

BEGIN
UPDATE 1
 pg_sleep
----------

(1 row)

UPDATE 1
COMMIT

B simply waits for A to finish with row 1 rather than racing it for row 2 in the opposite direction. No cycle is possible when every transaction touching the same rows agrees on the order to touch them in — the wait is real, but it resolves on its own instead of requiring Postgres to kill a transaction.

Practice

1. Inspect pg_locks while a transaction holds a row lock. 
BEGIN;
UPDATE books SET title = title WHERE id = 1;
SELECT locktype, relation::regclass, mode, granted FROM pg_locks WHERE relation = 'books'::regclass;
BEGIN
UPDATE 1
 locktype | relation |       mode       | granted
----------+----------+------------------+---------
 relation | books    | RowExclusiveLock | t
(1 row)

RowExclusiveLock is the table-level lock an UPDATE takes automatically — it allows other transactions to still read or update different rows, but coordinates writes to the same table at a coarser level than just the individual row being changed.

2. Confirm that ROLLBACK, not just ending the session, is what releases a lock. 
BEGIN;
UPDATE books SET title = title WHERE id = 1;
ROLLBACK;
SELECT count(*) FROM pg_locks WHERE relation = 'books'::regclass;
BEGIN
UPDATE 1
ROLLBACK
 count
-------
     0
(1 row)

Zero locks remain immediately after ROLLBACK — locks are tied to the transaction’s lifetime, not to how long the connection stays open.

3. Use SELECT ... FOR UPDATE to explicitly lock a row before reading it, without changing it. 
BEGIN;
SELECT title FROM books WHERE id = 1 FOR UPDATE;
COMMIT;
BEGIN
     title
----------------
 Book 1 (A) (B)
(1 row)

COMMIT

FOR UPDATE takes the same row lock an UPDATE would, without actually changing anything — the standard way to read a row with the intent to modify it later in the same transaction, preventing another transaction from changing it out from underneath the read.

Next: the write-ahead log — what actually makes COMMIT durable.

Frequently Asked Questions

Why did the second SELECT fail with 'current transaction is aborted' instead of just running?
Postgres transactions are all-or-nothing at the statement level too — once one statement inside a transaction errors, every subsequent statement is refused until the transaction ends, because Postgres can no longer guarantee a consistent view to build on. ROLLBACK (or COMMIT, which behaves like ROLLBACK for an already-aborted transaction) is the only way out.
Does Postgres actually detect deadlocks, or does it just hang forever?
It actively detects them — a background check runs periodically (governed by deadlock_timeout, 1 second by default) looking for a cycle of transactions each waiting on a lock the other holds. When it finds one, it picks a victim transaction, aborts it with a 'deadlock detected' error, and lets the other one proceed. A real deadlock never hangs forever in Postgres; a transaction just waiting on a lock that will eventually be released does hang, and that's a different situation entirely.
Why did giving both transactions the same lock order fix it, rather than making them fail together?
With the same order, the second transaction to start simply waits for the first to finish with row 1 before it can even attempt to lock it — there's no cycle, just a queue. A deadlock specifically requires each transaction to be holding what the other one wants, and consistent ordering makes that circular condition impossible to construct.
How would this actually get fixed in application code, not just this lesson's SQL script?
Whatever business logic updates two rows in one transaction should always acquire them in the same order — by primary key ascending is the simplest universal rule, regardless of which order the application logic conceptually processes them in. A transfer-between-accounts function, for example, should lock the lower account ID first every time, never 'from account, then to account.'
Is a deadlock the same thing as one transaction just waiting a long time for a lock?
No — ordinary lock waiting is one transaction blocked on a lock that another transaction is actively using and will eventually release; it resolves on its own once that other transaction commits or rolls back. A deadlock is specifically a cycle where waiting is mutual and neither side can ever release what the other needs, which is why Postgres has to intervene by force rather than simply waiting it out.