Transactions and Concurrency
Understanding deadlocks
In the previous chapters, you learned that transactions can lock rows while they work with them.
Usually, if a transaction needs a row locked by another transaction, it waits until that transaction finishes.
But sometimes two transactions can end up waiting for each other.
Neither can continue.
This is called a deadlock.
Setting up two orders
Let's create two orders for the example.
Type the following statement into the SQL editor and run it:
12345678910111213141516INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)SELECT id, 'pending', 49.99, 'txn_deadlock_001', CURRENT_TIMESTAMPFROM customersORDER BY idLIMIT 1;Create another:
12345678910111213141516INSERT INTO orders ( customer_id, status, total_amount, payment_reference, placed_at)SELECT id, 'pending', 79.99, 'txn_deadlock_002', CURRENT_TIMESTAMPFROM customersORDER BY idLIMIT 1;Check them:
1234567SELECT id, status, payment_referenceFROM ordersWHERE payment_reference IN ( 'txn_deadlock_001', 'txn_deadlock_002')ORDER BY id;We now have two rows that concurrent transactions could try to modify.
How a deadlock happens
Your practice workspace has one PostgreSQL session, so the following example is conceptual.
Transaction A updates the first order:
123456Transaction ABEGINUPDATE order 1→ order 1 lockedAt around the same time, Transaction B updates the second:
123456Transaction BBEGINUPDATE order 2→ order 2 lockedSo far, there is no problem.
Now Transaction A tries to update order 2:
1234567Transaction Aholds order 1tries order 2 ↓waits for Transaction BThen Transaction B tries to update order 1:
1234567Transaction Bholds order 2tries order 1 ↓waits for Transaction ANow:
123456789Transaction A Transaction Blocks order 1 locks order 2tries order 2 tries order 1 ↓ ↓waits for B waits for A ↓ ↓ └──────── neither can continue ──────┘Transaction A can't continue until B releases order 2.
Transaction B can't continue until A releases order 1.
That's a deadlock.
The source describes a deadlock as a circular dependency between transactions waiting for one another.
What does PostgreSQL do?
PostgreSQL detects the deadlock.
It can't leave both transactions stuck forever, so it aborts one of them.
The aborted transaction receives an error such as:
1ERROR: deadlock detectedOnce one transaction is aborted, its locks can be released and the other transaction can continue.
The important point is:
PostgreSQL can break the deadlock, but one of your transactions will fail.
Your application must handle the failure
Conceptually:
123456789101112Request A Request BBEGIN BEGINperform work perform workdeadlock occurs ↓ PostgreSQL aborts transactionCOMMIT transaction failsYour Node.js backend can't assume that a transaction will always succeed simply because every SQL statement is valid.
The source explicitly notes that applications should be prepared to replay a transaction when PostgreSQL rolls it back because of a deadlock.
Reducing the chance of deadlocks
As practical application guidance, one common way to reduce deadlocks is to acquire locks in a consistent order.
Our deadlock looked like this:
1234Transaction A Transaction Block order 1 lock order 2lock order 2 lock order 1If both transactions instead try to acquire the rows in the same order:
1234Transaction A Transaction Block order 1 lock order 1lock order 2 lock order 2one transaction can wait before creating the circular dependency.
This doesn't guarantee that every possible deadlock disappears, but consistent lock ordering can reduce them.
Waiting isn't the same as a deadlock
Normal waiting:
12345678910Transaction A Transaction Blocks row tries same row → waitsCOMMIT continuesTransaction B waits, but Transaction A can still finish.
A deadlock is different:
1234Transaction A waits for B ↑ ↓Transaction B waits for ANeither transaction can make progress without PostgreSQL intervening.
What you need to remember
A deadlock occurs when transactions form a circular dependency while waiting for locks.
PostgreSQL detects the deadlock and aborts one of the transactions.
From a full-stack application perspective:
transactions can fail even when their SQL is valid;
appropriate failed transactions may need to be retried;
consistent lock ordering can help reduce deadlocks.
In the final chapter, you'll learn how to roll back only part of a transaction using savepoints.