Transactions and Concurrency
Understanding deadlocks
In the previous chapters, you learned that transactions can lock rows while they work with them.
If a transaction needs a row locked by another transaction, it usually waits until that transaction finishes.
But sometimes two transactions end up waiting for each other. Neither transaction can continue.
This is called a deadlock.
Setting up two orders
Create a pending order for customer 7:
1234567891011121314INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)VALUES ( 7, 'pending', 49.99, 'txn_deadlock_001', CURRENT_TIMESTAMP);You should see INSERT 0 1.
Now create another pending order for customer 8:
1234567891011121314INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)VALUES ( 8, 'pending', 79.99, 'txn_deadlock_002', CURRENT_TIMESTAMP);You should again see INSERT 0 1.
Check the orders:
12345678910SELECT id, status, payment_referenceFROM ordersWHERE payment_reference IN ( 'txn_deadlock_001', 'txn_deadlock_002')ORDER BY id;You should see both pending orders.
We'll use these rows to understand how a deadlock can occur.
How a deadlock happens
A deadlock requires at least two concurrent database sessions. The playground has one session, so follow the statements below rather than running them in the SQL editor.
Transaction A locks the first order
Transaction A starts and locks the first order:
123456BEGIN;
SELECT idFROM ordersWHERE payment_reference = 'txn_deadlock_001'FOR UPDATE;Transaction A now holds a row lock on txn_deadlock_001.
Transaction B locks the second order
At around the same time, Transaction B starts and locks the second order:
123456BEGIN;
SELECT idFROM ordersWHERE payment_reference = 'txn_deadlock_002'FOR UPDATE;Transaction B now holds a row lock on txn_deadlock_002.
So far, neither transaction is waiting:
123Transaction A Transaction Blocks txn_deadlock_001 locks txn_deadlock_002Transaction A tries to lock the second order
Transaction A now runs:
1234SELECT idFROM ordersWHERE payment_reference = 'txn_deadlock_002'FOR UPDATE;Transaction B already holds a lock on that row, so Transaction A waits.
1234567Transaction A Transaction Bholds txn_deadlock_001 holds txn_deadlock_002tries txn_deadlock_002→ waits for Transaction BTransaction B can still continue, so this isn't a deadlock yet.
Transaction B tries to lock the first order
Transaction B now runs:
1234SELECT idFROM ordersWHERE payment_reference = 'txn_deadlock_001'FOR UPDATE;Transaction A already holds a lock on that row, so Transaction B also waits.
The complete sequence now looks like this:
1234567Transaction A Transaction Blocks txn_deadlock_001 locks txn_deadlock_002tries txn_deadlock_002 tries txn_deadlock_001waits for B waits for ATransaction A can't continue until Transaction B releases its lock.
Transaction B can't continue until Transaction A releases its lock.
Neither transaction can make progress.
That's a deadlock.
Waiting isn't the same as a deadlock
A transaction waiting for a lock doesn't automatically mean a deadlock has occurred.
Consider ordinary waiting:
1234567891011Transaction A Transaction Blocks a row tries to lock the same row → waitsCOMMIT→ releases the lock continuesTransaction B waits, but Transaction A isn't waiting for Transaction B. Transaction A can finish and release its lock.
A deadlock contains a circular dependency:
1234Transaction A waits for B ↑ ↓Transaction B waits for ANeither transaction can finish without PostgreSQL intervening.
What does PostgreSQL do?
PostgreSQL automatically detects the deadlock and aborts one of the transactions.
The aborted transaction receives an error such as:
1ERROR: deadlock detectedThe SQLSTATE error code is:
140P01PostgreSQL doesn't guarantee which transaction it will abort. Your application mustn't rely on one particular transaction being selected.
Once PostgreSQL aborts one transaction, its locks are released. The other transaction can acquire the lock it was waiting for and continue.
The important point is:
PostgreSQL resolves the deadlock, but one transaction fails.
Retry the entire transaction
A deadlock can occur even when every SQL statement is valid.
When the application receives error code 40P01, it can retry the failed transaction.
Retry the entire transaction from BEGIN, not only the statement that received the error. Any earlier work performed by the aborted transaction has been rolled back.
123456789start transaction ↓perform work ↓deadlock detected? ↙ ↘ no yes ↓ ↓commit retry from BEGINUse a limited number of retry attempts rather than retrying forever.
Retries handle occasional deadlocks, but repeatedly retrying isn't a substitute for fixing code that consistently acquires locks in a conflicting order.
Reduce deadlocks with consistent lock ordering
The deadlock occurred because the transactions acquired the rows in opposite orders:
1234Transaction A Transaction Block order 1 lock order 2lock order 2 lock order 1You can reduce the chance of deadlocks by making every transaction acquire the same rows in the same order:
1234Transaction A Transaction Block order 1 lock order 1lock order 2 lock order 2Transaction B may need to wait for Transaction A to release order 1, but it doesn't hold order 2 while waiting. The circular dependency doesn't form.
When an operation needs to lock both orders, it can acquire them in ascending id order:
123456789101112BEGIN;
SELECT id, payment_referenceFROM ordersWHERE payment_reference IN ( 'txn_deadlock_001', 'txn_deadlock_002')ORDER BY idFOR UPDATE;Every code path that locks these rows should use the same ordering.
After acquiring the locks, the transaction can perform its updates and commit:
1COMMIT;Consistent lock ordering can't prevent every possible deadlock, but it removes a common cause.
Keep transactions short
Deadlocks and long lock waits become more likely when transactions hold locks for a long time.
Keep transactions open only for the work that must happen inside them.
Avoid holding a transaction open while waiting for:
user input;
an external API response;
unrelated application work.
Shorter transactions release their locks sooner and reduce the opportunity for conflicting lock dependencies to form.
What you need to remember
The core ideas are:
A transaction waiting for a lock isn't automatically deadlocked.
A deadlock occurs when transactions form a circular dependency while waiting for locks.
PostgreSQL detects the deadlock and aborts one transaction with error code
40P01.The application can retry the entire failed transaction.
Acquiring locks in a consistent order helps reduce deadlocks.
Keeping transactions short reduces the time locks are held.
In the final chapter, you'll learn how to roll back only part of a transaction using savepoints.