Locking & Deadlocks

Reviewed & published by Brayan K

By the end of this lesson you'll know exactly which lock each statement takes, why two well-behaved transactions can freeze each other in a deadlock, and the handful of habits — consistent lock ordering, short transactions, and FOR UPDATE — that keep a busy database flowing instead of grinding to a halt.

Part of the free SQL course at LearnCodingFast — hands-on lessons with examples you run in your browser, plus practice exercises and a quick quiz.

What You'll Learn

• Tell shared (read) locks from exclusive (write) locks
• Reason about lock granularity: row vs. page vs. table
• Trace how two sessions deadlock by locking in opposite order
• Understand how the engine detects and kills a deadlock victim
• Prevent deadlocks with consistent ordering and short transactions
• Use FOR UPDATE, NOWAIT, and SKIP LOCKED for safe concurrent access

Our Sample Table: accounts

Most examples move money between these two bank accounts. Two rows is all you need to create — and prevent — a deadlock.

1. Two Kinds of Lock: Shared vs. Exclusive

A lock is a temporary claim a transaction places on a piece of data so others can't trample it mid-change. There are two flavours. A shared lock (also called a read lock) means "I'm reading this, please don't change it yet" — and crucially, many transactions can hold a shared lock on the same row simultaneously. An exclusive lock (a write lock) means "I'm about to change this, everyone else wait" — and only one transaction can hold it.

A shared lock is a museum exhibit behind glass: any number of people can look at once, but nobody may touch. An exclusive lock is the restorer taking that piece into a private workshop — the glass comes down, and until they're finished, no one else gets to look or touch.

The compatibility rule is short enough to memorise: shared is compatible with shared; exclusive is compatible with nothing. Locks are released automatically when the transaction ends with COMMIT or ROLLBACK — which is exactly why short transactions matter so much later.

-- Two kinds of lock, and the one rule that ties them together.

-- A SHARED (read) lock says "I'm reading this — don't change it yet".
-- Many sessions can hold a shared lock on the same row AT THE SAME TIME.
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR SHARE;   -- shared lock on row 1
-- Another session can also FOR SHARE row 1 → both succeed.
-- But anyone trying to UPDATE row 1 must WAIT until you COMMIT.
COMMIT;

-- An EXCLUSIVE (write) lock says "I'm about to change this — back off".
-- Only ONE session can hold it; everyone else (readers that want a lock
-- AND writers) must wait.
BEGIN;
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;  -- exclusive lock on row 1
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
COMMIT;   -- the lock is released here

-- The rule:  SHARED is compatible with SHARED,
--            EXCLUSIVE is compatible with NOTHING.

2. Lock Granularity: Row, Page, Table

Granularity is simply how big a slice of data a single lock covers. A row lock guards just the rows you touch — the everyday case, and the most concurrent because everyone else can still use the rest of the table. A page lock covers a fixed block of nearby rows (often 8 KB); you never request these, the engine may pick them internally. A table lock covers the entire table at once — cheap for the engine to track, but it stops every other session cold.

-- Lock GRANULARITY = how big a slice of data a lock covers.
-- Smaller locks = more sessions can work at once, but more bookkeeping.

-- ROW lock — locks just the rows you touch (the everyday case).
UPDATE orders SET status = 'shipped' WHERE id = 42;
-- Only row 42 is locked. Every other order stays fully available.

-- PAGE lock — some engines lock an 8 KB page (a block of nearby rows).
-- You don't ask for these; the engine may choose them internally.

-- TABLE lock — locks the WHOLE table at once.
-- Cheap to track, but it stops everyone else dead. Schema changes need it:
ALTER TABLE orders ADD COLUMN tracking_no TEXT;   -- locks the entire table

-- LOCK ESCALATION: if one statement locks too many rows, the engine may
-- "escalate" thousands of row locks into ONE table lock to save memory.
-- That sudden table lock can freeze unrelated queries — watch for it.

3. Safe Read-Modify-Write with FOR UPDATE

A plain SELECT takes no lasting lock, so two sessions can both read quantity = 1, both subtract one, and both write 0 — you've sold the same item twice. Adding FOR UPDATE to the SELECT takes an exclusive lock on the rows it returns, so the second session blocks until the first commits, then reads the already-updated value. This read-lock-then-write pattern is the bread and butter of pessimistic concurrency.

-- A safe read-modify-write needs FOR UPDATE.
-- Goal: sell one unit of stock without two sessions overselling.

BEGIN;

-- Step 1: read the CURRENT quantity AND lock the row in one go.
SELECT quantity FROM inventory
WHERE product_id = 100
FOR UPDATE;            -- exclusive lock held until COMMIT/ROLLBACK
-- Returns: quantity = 1

-- Any other session that now runs the same FOR UPDATE simply WAITS here,
-- so it can never read the same "1" and sell the same unit twice.

-- Step 2: act on what you just read.
UPDATE inventory
SET quantity = quantity - 1
WHERE product_id = 100;

COMMIT;               -- lock released; the waiting session reads quantity = 0

Your Turn: lock the row you read

Add the two-word clause that takes an exclusive lock so this seat can't be sold twice. The expected behaviour is in the comments so you can check yourself.

-- 🎯 YOUR TURN — make this read-modify-write safe under concurrency.
-- Goal: deduct 1 seat, but only if a seat is actually free.

BEGIN;

-- Add the clause that locks the row while you read it 👇
SELECT seats_left FROM events
WHERE event_id = 7
___ ___;            -- 👉 two words that take an exclusive row lock
-- Returns: seats_left = 1

UPDATE events SET seats_left = seats_left - 1 WHERE event_id = 7;

COMMIT;

-- ✅ Expected: a second booking session BLOCKS on the locked row until you
--    COMMIT, then reads seats_left = 0 — so the seat is never sold twice.

4. How a Deadlock Forms

A deadlock is a circular wait: each transaction holds a lock the other needs, so neither can move. It almost always comes from two sessions acquiring the same locks in the opposite order. Follow the timeline below row by row — by the last step both sessions are stuck waiting for each other forever.

Two people meet in a one-lane corridor. Each steps aside expecting the other to pass first, and they both freeze — politely waiting forever. The fix is a rule everyone follows: always keep to the left. In databases that rule is "always lock the lower id first".

Deadlock timeline — Session A vs. Session B:

5. How the Engine Detects & Kills a Victim

You don't have to escape a deadlock yourself — the database does it for you. A background deadlock detector periodically builds a "who-waits-for-whom" graph; if it finds a cycle, the engine picks one transaction as the deadlock victim, rolls it back, and frees its locks so the other can finish. In PostgreSQL the victim receives:

The engine usually chooses the transaction that's cheapest to undo (the one that has done the least work). Because a victim will happen occasionally on a busy system, application code should catch the deadlock error and simply retry the whole transaction — on the retry the timing is different and it almost always succeeds.

6. Preventing Deadlocks

Deadlocks are far easier to prevent than to debug. Five habits cover almost every case:

• Consistent lock ordering. If every code path locks rows in the same order (e.g. ascending id), a cycle is impossible. ORDER BY id FOR UPDATE bakes the order right into the query.
• Keep transactions short. Locks live until COMMIT; the less you do while holding them, the smaller the window for conflict. Never wait on user input or a network call inside a transaction.
• Lower the isolation level where it's safe. READ COMMITTED holds far fewer locks than SERIALIZABLE, so it deadlocks less often.
• Lock up front with SELECT … FOR UPDATE so every row you'll need is claimed in one ordered step, instead of grabbing locks piecemeal as you go.
• Use NOWAIT / SKIP LOCKED to fail fast or step around contended rows rather than queueing behind them.

The deadlock from Section 4 vanishes the instant both sessions run SELECT * FROM accounts WHERE id IN (1, 2) ORDER BY id FOR UPDATE; first — they now line up for the same lock in the same order, so one simply waits and both commit.

-- Tuning HOW you wait for a locked row.

-- Default: wait patiently (possibly forever) until the lock frees.
SELECT * FROM accounts WHERE id = 1 FOR UPDATE;

-- NOWAIT: don't wait at all — fail instantly if the row is locked.
-- Great when "try again later" is better than blocking a web request.
SELECT * FROM accounts WHERE id = 1 FOR UPDATE NOWAIT;
-- If locked → ERROR: could not obtain lock on row in relation "accounts"

-- SKIP LOCKED: silently ignore rows other sessions have locked.
-- The classic job-queue trick — every worker grabs a DIFFERENT row.
BEGIN;
SELECT id FROM job_queue
WHERE status = 'pending'
ORDER BY created_at
LIMIT 1
FOR UPDATE SKIP LOCKED;     -- returns the first UNLOCKED pending job
UPDATE job_queue SET status = 'processing' WHERE id = :picked_id;
COMMIT;
-- Ten workers running this at once each get their own job — zero contention.

Your Turn: reorder to avoid a deadlock

Session B grabs rows in the opposite order to Session A. Reorder its two UPDATEs (and fill the blanks) so both sessions lock the lower id first. The expected behaviour is in the comments.

-- 🎯 YOUR TURN — fix the deadlock by reordering, then press "Try it Yourself".
-- Two transfers run at the same time. As written they grab rows in OPPOSITE
-- orders, which deadlocks. Make BOTH lock the LOWER id first.

-- Session A (transfer 100 from acct 1 → acct 2):
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;  -- locks 1
UPDATE accounts SET balance = balance + 100 WHERE id = 2;  -- locks 2
COMMIT;

-- Session B (transfer 50 from acct 2 → acct 1): REORDER these two lines
-- so it touches id = 1 BEFORE id = 2, matching Session A.
BEGIN;
UPDATE accounts SET balance = balance ___ 50 WHERE id = ___;  -- 👉 do id = 1 first
UPDATE accounts SET balance = balance ___ 50 WHERE id = ___;  -- 👉 then id = 2
COMMIT;

-- ✅ Expected: with both sessions locking id = 1 first, one simply waits for
--    the other and BOTH commit. No "deadlock detected" error.

Common Errors (and the fix)

• "deadlock detected" out of nowhere: two code paths lock the same rows in different orders. Sort the rows (e.g. ORDER BY id FOR UPDATE) so every path locks them in one consistent order.
• Long transactions holding locks: a BEGIN that waits on user input or an API call keeps its locks for seconds or minutes, blocking everyone. Do the slow work outside the transaction; open it only to read-lock, write, and commit.
• Lock escalation freezing a table: a bulk UPDATE/DELETE touches so many rows the engine swaps them for one table lock. Update in smaller batches (e.g. a few thousand rows at a time) to keep locks at row level.
• Forgot FOR UPDATE on a read-modify-write: a plain SELECT takes no lasting lock, so two sessions read the same value and overwrite each other (a lost update). Lock the row you read with SELECT … FOR UPDATE.
• "could not obtain lock … NOWAIT": that's NOWAIT working as designed — the row was already locked. Catch the error and retry, or drop NOWAIT if waiting is acceptable.

Frequently Asked Questions

Q: Is a deadlock the same as a query just being slow or blocked?

No. A blocked query is waiting for a lock that will eventually free — it resolves on its own. A deadlock is a circular wait that can never resolve, so the engine has to kill one transaction to break it.

Q: Which transaction gets chosen as the deadlock victim?

Typically the one that's cheapest to roll back — usually the transaction that has done the least work so far. You don't control it directly, which is why your app should be ready to retry whichever one is killed.

Q: Does SELECT … FOR UPDATE block plain SELECTs too?

In PostgreSQL, no — ordinary reads use MVCC and see the last committed version without taking a lock. FOR UPDATE only blocks other lock attempts (another FOR UPDATE, an UPDATE, or a DELETE) on those rows.

Q: How is NOWAIT different from SKIP LOCKED?

NOWAIT raises an error the moment a wanted row is locked. SKIP LOCKED raises nothing — it just leaves the locked rows out of the result and returns the ones it could lock, which is what makes it perfect for parallel job queues.

Mini-Challenge: A Contention-Free Dispatcher

Put it all together — a brief, a blank canvas, and the expected behaviour in the comments. Write it, then run it from several sessions at once to confirm each claims a different job.

-- 🎯 MINI-CHALLENGE — a contention-free job dispatcher.
-- Using ONLY what this lesson covered (BEGIN, FOR UPDATE, SKIP LOCKED, UPDATE,
-- consistent ordering):
--   1. In one transaction, pick the single oldest 'pending' job from job_queue
--      WITHOUT waiting on jobs other workers have already grabbed.
--   2. Mark that job 'processing'.
--   3. COMMIT so the lock releases.
--
-- ✅ Expected: run it in 5 sessions at once and each claims a DIFFERENT job,
--    with no blocking and no deadlocks.

-- your query here

🎉 Lesson Complete

• ✅ Shared locks coexist; exclusive locks are compatible with nothing
• ✅ Granularity runs row → page → table, trading concurrency for bookkeeping
• ✅ Deadlocks come from locking the same rows in opposite order
• ✅ The engine detects the cycle and rolls back a victim — so catch and retry
• ✅ Consistent ordering, short transactions, and FOR UPDATE prevent most trouble
• ✅ Next: Partitioning — split massive tables for speed and easier maintenance

Practice quiz

Continue this course

• Previous: Transactions, Isolation Levels & Concurrency Control (ACID Deep Dive)
• Next: Partitioning: Range, Hash, List & Composite Strategies — Partition large tables for query performance and maintenance efficiency
• Quick reference: SQL cheat sheet