Deadlocks

A deadlock happens when two (or more) transactions are each waiting for a lock that the other one holds. Neither can proceed. They’re stuck forever — unless the database intervenes.

Think of it like two cars meeting on a narrow one-lane bridge from opposite sides. Neither can go forward, and neither wants to reverse. Someone has to back up.

The Classic Deadlock Example

-- Transaction A:
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks row 1
-- Now waiting to lock row 2...
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- BLOCKED!

-- Transaction B (at the same time):
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;   -- locks row 2
-- Now waiting to lock row 1...
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- BLOCKED!

Transaction A holds the lock on row 1 and wants row 2. Transaction B holds the lock on row 2 and wants row 1. Neither can finish. Deadlock.

How Databases Detect Deadlocks

Databases use a wait-for graph. Every time a transaction waits for a lock, the database adds an edge to this graph (Transaction A → waits for → Transaction B). If the graph has a cycle, there’s a deadlock.

Most databases check for deadlocks either:

• Periodically (MySQL checks every few seconds)
• Immediately when a wait begins (PostgreSQL checks instantly)

What Happens When a Deadlock Is Detected

The database picks one transaction as the victim and rolls it back. The other transaction can then proceed.

How does it pick the victim?

• PostgreSQL rolls back the transaction that caused the deadlock detection
• MySQL InnoDB rolls back the transaction that has done the least amount of work (fewest rows modified)

-- When our transaction is the victim, we get an error:
-- PostgreSQL: ERROR: deadlock detected
-- MySQL: ERROR 1213: Deadlock found when trying to get lock

-- Our application MUST catch this and retry
-- try {
--     await executeTransfer();
-- } catch (error) {
--     if (error.code === '40P01') {  // PostgreSQL deadlock code
--         await executeTransfer();   // retry
--     }
-- }

Prevention Strategies

1. Always Access Rows in the Same Order

The simplest and most effective strategy. If every transaction locks rows in the same order (e.g., always by ascending ID), circular waits can’t happen.

-- Bad: Transaction A locks 1 then 2, Transaction B locks 2 then 1
-- Transaction A:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B:
UPDATE accounts SET balance = balance - 50 WHERE id = 2;  -- opposite order!
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

-- Good: both transactions lock in the same order (ascending ID)
-- Transaction A:
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

-- Transaction B:
UPDATE accounts SET balance = balance + 50 WHERE id = 1;   -- same order!
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

2. Keep Transactions Short

The shorter the transaction, the less time locks are held, the less chance of deadlocks. Get in, do the work, get out.

-- Bad: long transaction holding locks
BEGIN;
SELECT * FROM products WHERE id = 1 FOR UPDATE;
-- ... 2 seconds of application processing ...
UPDATE products SET stock = stock - 1 WHERE id = 1;
COMMIT;

-- Better: do processing outside the transaction
-- ... process in application code first ...
BEGIN;
UPDATE products SET stock = stock - 1 WHERE id = 1 AND stock > 0;
COMMIT;

3. Use Lock Timeouts

Set a maximum time to wait for a lock. If the lock isn’t available within that time, the statement fails instead of waiting forever.

-- PostgreSQL: timeout after 5 seconds
SET lock_timeout = '5s';

-- MySQL: timeout after 5 seconds
SET innodb_lock_wait_timeout = 5;

4. Use NOWAIT or SKIP LOCKED

-- Don't wait at all — fail immediately if locked
SELECT * FROM products WHERE id = 1 FOR UPDATE NOWAIT;

-- Skip rows that are locked (great for job queues)
SELECT * FROM jobs WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;

5. Reduce Lock Scope

Lock only what we need. Don’t use FOR UPDATE on rows we’re only reading.

-- Bad: locking rows we don't need to update
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.status = 'pending'
FOR UPDATE;  -- locks both orders AND customers rows!

-- Better: only lock the rows we're updating
SELECT * FROM orders
JOIN customers ON orders.customer_id = customers.id
WHERE orders.status = 'pending'
FOR UPDATE OF orders;  -- only locks orders rows

Monitoring Deadlocks

-- PostgreSQL: check for current locks
SELECT pid, relation::regclass, mode, granted
FROM pg_locks
WHERE NOT granted;

-- PostgreSQL: check deadlock count (since server start)
SELECT deadlocks FROM pg_stat_database WHERE datname = current_database();

-- MySQL: show current lock waits
SHOW ENGINE INNODB STATUS;  -- look for "LATEST DETECTED DEADLOCK" section

In simple language, deadlocks are an inevitable side effect of concurrent access. We can’t eliminate them entirely, but we can minimize them by locking in a consistent order, keeping transactions short, and always having retry logic in our application code. The database will detect and resolve deadlocks — our job is to handle the error gracefully.