Advanced Track

Locking & Deadlocks

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.

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

Before you start: finish Advanced Transactions so you're comfortable with BEGIN/COMMIT, isolation levels, and what a transaction actually guarantees. The in-browser editor lets you write and edit SQL; to truly feel a lock you need two sessions at once, so open two tabs of a free playground like sqliteonline.com or a Postgres sandbox such as db-fiddle.com. Examples below use PostgreSQL syntax and describe the expected behaviour so you can check yourself.

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.

Result:

id	owner	balance
1	Alice	500
2	Bob	300

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.

🚪 Real-world analogy

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.

Shared (FOR SHARE) vs. exclusive (FOR UPDATE)

See which locks can coexist and which block.

Try it Yourself »

SQL

-- 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".
-- 
...

Result — lock compatibility:

Holding ↓ / Wants →	Shared	Exclusive
Shared	✅ allowed	⛔ waits
Exclusive	⛔ waits	⛔ waits

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.

Watch out for lock escalation. If one statement ends up holding a huge number of row locks, some engines (notably SQL Server) automatically escalate them into a single table lock to save memory. A query you thought was surgical suddenly blocks the whole table. Updating in smaller batches keeps locks fine-grained.

Row vs. table locks (and escalation)

How small or large a lock's footprint can be.

Try it Yourself »

SQL

-- 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
...

Result:

Granularity	Covers	Concurrency	Bookkeeping
Row	the rows you touch	highest	more
Page	a block of nearby rows	medium	medium
Table	the whole table	lowest	least

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.

SELECT … FOR UPDATE

Lock the row you read so the write is safe.

Try it Yourself »

SQL

-- 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 y
...

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: add FOR UPDATE

Fill in the clause that locks the row for a safe read-modify-write.

Try it Yourself »

SQL

-- 🎯 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
--    
...

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.

🚶 Real-world analogy

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:

Time	Session A	Session B
t1	BEGIN;	BEGIN;
t2	UPDATE … WHERE id = 1; locks row 1	UPDATE … WHERE id = 2; locks row 2
t3	UPDATE … WHERE id = 2; waits for row 2	(still running)
t4	(blocked)	UPDATE … WHERE id = 1; waits for row 1
t5	☠️ Circular wait — A holds 1 wants 2, B holds 2 wants 1. Deadlock.

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:

ERROR:  deadlock detected
DETAIL:  Process 8412 waits for ShareLock on transaction 990;
         blocked by process 8377.

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.

Detection vs. timeout. Postgres checks for a real cycle after deadlock_timeout (default 1s). A separate lock_timeout caps how long any single wait may last, even when there's no cycle — handy so a stuck query fails fast instead of hanging your app.

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.

NOWAIT and SKIP LOCKED

Tune how (or whether) you wait for a locked row.

Try it Yourself »

SQL

-- 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 
...

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: consistent lock order

Make both sessions lock id = 1 before id = 2.

Try it Yourself »

SQL

-- 🎯 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

...

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.

📘 Quick Reference

Syntax / Idea	What it does
FOR SHARE	Shared (read) lock — others may read-lock, not write
FOR UPDATE	Exclusive (write) lock on the rows read
FOR UPDATE NOWAIT	Take the lock or error instantly — no waiting
FOR UPDATE SKIP LOCKED	Ignore rows already locked (job queues)
ORDER BY id FOR UPDATE	Lock in a consistent order → no deadlocks
SET lock_timeout = '5s'	Fail a lock wait after 5 seconds
deadlock_timeout	How long before the cycle check runs (default 1s)
ROLLBACK + retry	Standard response to a deadlock victim

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

Claim one pending job with FOR UPDATE SKIP LOCKED, mark it processing, commit.

Try it Yourself »

SQL

-- 🎯 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 deadloc
...

🎉 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

Locking & Deadlocks

What You'll Learn

Our Sample Table: accounts

1. Two Kinds of Lock: Shared vs. Exclusive

Shared (FOR SHARE) vs. exclusive (FOR UPDATE)

2. Lock Granularity: Row, Page, Table

Row vs. table locks (and escalation)

3. Safe Read-Modify-Write with FOR UPDATE

SELECT … FOR UPDATE

Your Turn: lock the row you read

🎯 Your Turn: add FOR UPDATE

4. How a Deadlock Forms

5. How the Engine Detects & Kills a Victim

6. Preventing Deadlocks

NOWAIT and SKIP LOCKED

Your Turn: reorder to avoid a deadlock

🎯 Your Turn: consistent lock order

Common Errors (and the fix)

📘 Quick Reference

Frequently Asked Questions

Mini-Challenge: A Contention-Free Dispatcher

🎯 Mini-Challenge

🎉 Lesson Complete

Cookie & Privacy Settings

Our Sample Table: `accounts`