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.

Learn Python, JavaScript, Java and more with free interactive lessons, real projects and AI-powered help. Beginner-friendly.

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.

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

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.

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.

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.

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.

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

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.

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

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.

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

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

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.

Practice quiz

• Browse all free courses
• Cheat sheets & quizzes
• Coding dictionary