09-04 ended with an open question: if SERIALIZABLE isn't the answer for confirming an order, what is? And with an unexplained observation: when session B tried to update a row A had already touched, B was left waiting. This lesson explains what was holding it, and with it comes the tool e-commerce systems actually use to stop two customers buying the same unit: the lock.

You'll see the implicit row locks you were already causing without knowing it; the complete SELECT ... FOR UPDATE family and the safe read-modify-write pattern, which is the correct solution to the lost update; SKIP LOCKED for sharing out a queue of orders across several warehouse operators without treading on each other; optimistic locking against pessimistic; table locks, what an ALTER TABLE locks and why CREATE INDEX CONCURRENTLY had to exist; and deadlocks, with their demonstration, their real message, how the engine resolves them and the rules for not causing them. All reproducible with two psql terminals.

1. Implicit row locks
2. The SELECT ... FOR ... family
3. The safe read-modify-write pattern
4. SKIP LOCKED: the job queue
5. NOWAIT: failing fast
6. Optimistic against pessimistic locking
7. Table locks, ALTER TABLE and CREATE INDEX CONCURRENTLY
8. Deadlocks
9. Diagnosis: who's blocking whom
10. The four timeouts
11. Sequences, ROLLBACK and invoice numbers
12. Common Mistakes and Tips
13. Exercises
14. Module conclusion

You don't have to ask for a lock: every UPDATE and every DELETE locks the row it touches until the end of the transaction. Let's go back to the matcha tea (product 15, stock 40), with two terminals:

Four lessons in nine instants:

• t4: reading doesn't wait. That's MVCC (09-02): readers don't block against writers. B reads the old version and carries on working.
• t5: writing does wait. B's terminal hangs, with no message and no cursor. It isn't a freeze: it's in a queue.
• t7: the lock lasts until the end of A's transaction, not until the end of its UPDATE. That's why a long transaction is a harmful transaction (09-01).
• t8: it comes out as 38, not 39. On unblocking, PostgreSQL reread the already updated row and recomputed stock - 1 over 39. It's exactly what 09-04 explained about why the relative form is safe.

And if A had done a ROLLBACK at t6, B would have carried on just the same, but computing over 40 and leaving the stock at 39. In both cases, the result is correct.

Sometimes you need to lock a row before writing it, because between the read and the write there's a decision to make. That's what explicit locks are for, four of them, ordered from strongest to weakest:

And the compatibility table: ✗ means the second one waits for the first.

That table, which looks like bureaucracy, solves a real and very frequent problem: FOR KEY SHARE is what PostgreSQL takes when checking a foreign key. Thanks to it being compatible with FOR NO KEY UPDATE, inserting a line for order 21 (which needs to check that order 21 exists) doesn't wait for another session to update that order's status. Before PostgreSQL 9.3 it did wait, and it was a classic source of cascading locks.

In day-to-day work you'll use FOR UPDATE in 95 % of cases and FOR SHARE in the odd integrity check. The other two you'll see in diagnostics, you won't ask for them yourself.

This is the correct solution to 09-04's lost update, and the lesson's most important pattern:

And now both sessions at once, the same scenario that lost a sale in 09-04:

Thirty-eight. The key is at t2: B's FOR UPDATE waits for A to finish, and when it does, B reads 39, not 40. The window between reading and writing has disappeared, because throughout it the row was B's.

Compare it with the other two solutions you already know, because all three are valid and they're chosen on different criteria:

Solution	When	Cost
UPDATE ... WHERE id = 15 AND stock >= 1 (09-02)	The logic fits in one statement	None. It's the fastest and the first option
SELECT ... FOR UPDATE + UPDATE	You have to decide between reading and writing	The other sessions wait. Predictable
SERIALIZABLE with a retry (09-04)	The invariant spans several rows	Aborts and a retry loop

Two warnings about FOR UPDATE: it can't be used with GROUP BY, DISTINCT, UNION or window functions, because the engine wouldn't know which physical rows to lock; and it locks every row the query returns, so a SELECT * FROM products FOR UPDATE with no WHERE locks the whole catalogue.

Dialect note: SELECT ... FOR UPDATE exists in PostgreSQL, MySQL/InnoDB and Oracle with the same syntax. SQL Server doesn't have it: it uses table hints, SELECT ... WITH (UPDLOCK, ROWLOCK). And SQLite accepts it syntactically but does nothing, because it only allows one writer at a time and the problem doesn't arise for it.

By adding SKIP LOCKED, the query doesn't wait: it skips the locked rows and carries on with the next ones. It's exactly what's needed to share work out across several processes.

The GreenStore case: several warehouse operators prepare the paid orders —today they're 18 and 19— and none of them should pick up the same one as another.

At t1, A's query returns the row 18 | 5 | 2026-01-27; at t2, B's returns 19 | 6 | 2026-02-09. Without SKIP LOCKED, at t2 operator 2 would have been left waiting for operator 1 only to end up picking up the same order that was already done. With it, the query becomes a work dispatcher, and it's the canonical way of implementing a task queue over a table —sending emails, generating invoices, syncing with the marketplace— with no additional infrastructure at all.

The third option: neither wait nor skip, but give up immediately. SELECT stock FROM products WHERE id = 15 FOR UPDATE NOWAIT; returns, if the row is taken, ERROR: could not obtain lock on row in relation "products".

Everything above is pessimistic locking: you assume there will be a conflict and reserve the row in advance. The alternative is to assume there won't be and check on writing.

Optimistic locking is implemented with a version column incremented on every write:

The cycle has three steps, and between the first and the third no open transaction is needed — which is the whole point:

And in the session that arrives second, with the same version = 0 read, the answer is UPDATE 0.

Zero rows affected: that's the signal. There's no error, no exception, no aborted transaction: there's a counter worth 0 that your code has to check. If it's 0, somebody got there before you and you have to reread, recompute and try again — or show the user "this data has changed while you were editing".

The risk of optimism: if you don't check the number of affected rows, an UPDATE 0 goes completely unnoticed and the change is silently lost. It's the same UPDATE N 05-03 asked you to always look at, now turned into the entire mechanism. The ORMs implementing this pattern —Hibernate with @Version, Django with select_for_update as an alternative— throw an exception precisely so it can't be ignored.

As well as row locks, there are eight table lock modes. You don't have to memorise them; what you have to know is which one each operation takes and which clashes with which:

They can also be asked for by hand, inside a transaction — BEGIN; LOCK TABLE products IN SHARE MODE; … COMMIT; lets you do a coherent stocktake with nobody writing meanwhile.

LOCK TABLE requires privileges. The modes above ROW EXCLUSIVE demand write or maintenance permissions on the table; SELECT isn't enough. Privileges and roles are 11-03.

And here two of the course's promises are closed. The first is 05-06's: most ALTER TABLEs take ACCESS EXCLUSIVE, the mode that blocks everything, queries included. That's why an apparently innocuous migration can bring a site down: not because of how long it takes to run, but because it first has to wait for every in-flight transaction to finish and, while it waits, it queues everybody arriving behind it. A 10 ms ALTER TABLE behind a 40-minute report stops the shop for 40 minutes. Hence 05-06's two rules: lock_timeout always before a migration, and variants that don't rewrite the table (ADD COLUMN with a DEFAULT is instantaneous since PostgreSQL 11; ADD CONSTRAINT ... NOT VALID followed by VALIDATE CONSTRAINT avoids the long lock).

The second is 08-02's: a normal CREATE INDEX takes SHARE, which blocks all the table's writes for the whole time it takes to build — minutes or hours on a large table. CREATE INDEX CONCURRENTLY only takes SHARE UPDATE EXCLUSIVE, which blocks neither reads nor writes. The price is the one 09-03 explained: it walks the table twice and waits between passes for the old transactions to finish, so it takes longer, it can't run inside a transaction and, if it fails, it leaves an invalid index that has to be dropped by hand (spotted with indisvalid = false in pg_index). In production, always CONCURRENTLY.

A deadlock happens when two transactions wait for each other: A waits for a resource B holds, and B waits for one A holds. With no outside intervention, they'd wait forever.

The textbook demonstration, with two products:

That cycle in the wait graph is the formal definition of a deadlock, and it's what PostgreSQL looks for. The real message:

How the engine resolves it. Every time a transaction has been waiting for longer than deadlock_timeout (1 second by default), PostgreSQL builds the wait graph and looks for cycles. If it finds one, it picks a victim and aborts its transaction with code 40P01. The other carries on and finishes normally. It isn't a setting you have to enable: it ships switched on and works by itself.

The four rules for not causing them:

1. Always access resources in the same order. It's the golden rule and it solves 90 % of cases. If every transaction touching several products walks them sorted by id, the cycle is impossible: whoever holds 1 will ask for 15, and whoever doesn't hold 1 will be waiting for it, having taken nothing. In practice: ORDER BY id in the SELECT ... FOR UPDATE preceding the writes.
2. Short transactions. The less time a lock is held, the lower the probability of crossing over.
3. No human interaction and no external calls inside. A user thinking with two rows locked is a deadlock factory (09-01, 09-03).
4. Touch the rows in a single UPDATE when you can. An UPDATE ... WHERE id IN (1, 15) locks in a deterministic order and leaves no gap.

And as a final net: the 40P01 is retried just like the 40001, with the same exponential-backoff loop from 09-04. That's why that pattern filtered on both codes.

When something "has hung", this is the query to keep saved:

pg_blocking_pids() is the key function: it returns directly the processes blocking a given one. With the guilty pid (41287), you look at what it's doing in pg_stat_activity —very often, nothing: idle in transaction— and, if necessary, you cut it off:

For fine detail there's pg_locks, which answers "what exact mode is it asking for and on which object?" — useful above all with section 7's table locks:

Four parameters, four different jobs, and it's worth not confusing them:

If the table is busy, the statement fails in two seconds with ERROR: canceling statement due to lock timeout instead of queueing the whole shop up behind it. You retry later and nothing has happened. Without lock_timeout, that ALTER TABLE is an outage waiting to happen.

You've seen it three times already —in 05-02, in exercise 2 of 09-01 and in 09-03's batch— and now it's time for the why and the consequence:

nextval() isn't transactional. A ROLLBACK doesn't give the consumed value back, and that's why sequences leave gaps.

And it's a deliberate decision, not an oversight. If nextval respected transactions, it would have to lock the sequence until the COMMIT, and every session wanting to insert into that table would line up single file behind the first: every concurrent INSERT would be a bottleneck. PostgreSQL trades continuity for guaranteed uniqueness and maximum concurrency, which is the right deal for a surrogate key. And from that follows a blunt consequence:

Never use the primary key as an invoice number. A legal invoice numbering has to be consecutive and gapless; a sequence guarantees it's unique and increasing, which isn't the same. A ROLLBACK, a failed INSERT or an upsert ending in a conflict (05-05) opens a gap, and that gap is a problem with the tax authorities, not an aesthetic detail.

The three ways of getting a gapless numbering, with their price:

The first is the usual one, and its implementation is exactly section 3's pattern:

(The invoice_counters table is an example from this lesson and isn't part of GreenStore's canonical schema.) Notice what you're doing: deliberately giving up concurrency at a specific point because a legal requirement demands it. Knowing when to do that is, at bottom, what the whole module has been about.

• Believing a normal SELECT locks anything. It locks nothing, and that's why reading never waits. If you need the row not to change, you have to ask for it with FOR UPDATE or FOR SHARE. And reading without FOR UPDATE and writing afterwards is 09-04's lost update, now with no excuse.
• Putting FOR UPDATE on a query with no selective WHERE. You lock every row it returns; with the whole catalogue, you've stopped the shop.
• Forgetting to check the number of affected rows with optimistic locking. An UPDATE 0 means "somebody got there first", and if you don't look at it the change is silently lost.
• Firing an ALTER TABLE in production with no lock_timeout. It takes ACCESS EXCLUSIVE, waits for the longest transaction around and queues everybody behind it. And CREATE INDEX without CONCURRENTLY blocks writes for the whole build.
• Touching several rows in a different order in each part of the code. It's the recipe for a deadlock. ORDER BY id in the SELECT ... FOR UPDATE, always.
• Lowering deadlock_timeout "so it detects them sooner". It isn't a wait limit: it only makes the cycle check run more often and burn more CPU. And the 40P01 isn't a programming error: it's a temporal conflict that gets retried, just like the 40001.
• Using the PK as an invoice number. Sequences leave gaps by design and legal numbering doesn't allow them.
• Tip: keep the pg_blocking_pids query saved. It's the first thing you run when something hangs, and it saves half an hour of guesswork.
• Tip: SKIP LOCKED turns a table into a work queue. Before setting up messaging infrastructure, check whether this isn't enough for you.
• Tip: choose pessimistic where there's real contention and optimistic where there's human waiting. Stock: FOR UPDATE. Edit form: version column.

With two psql terminals and the freshly reloaded database.

Solve the lost update with explicit locking and compare.

1. Reproduce section 3 in two sessions and check that product 15's final stock is 38. Note at which exact instant session B is left waiting and what value it reads on unblocking.
2. Repeat it, changing B's FOR UPDATE for FOR UPDATE NOWAIT. What message comes out and how long does it take?
3. Repeat it with FOR UPDATE SKIP LOCKED. How many rows does B's query return and why is that a dangerous result in this particular case?
4. Write the three valid solutions to this problem that you already know (09-02, 09-04 and this lesson) and say which you'd choose for GreenStore's basket and why.

Set up the warehouse's order-picking queue.

1. Write the query that takes the oldest paid order without treading on anybody, and run it in two sessions at once. Check that one takes 18 and the other 19.
2. Add a third session with the same query. What does it return and why?
3. Without SKIP LOCKED, what would the second session have done? And what would have happened on unblocking, exactly?
4. Design the variant that also serves to retry orders left half-done (an operator whose process died). What has to be added to orders and why is that column not part of the canonical schema?

Trigger a deadlock and diagnose it.

1. Reproduce section 8 with products 1 and 15. Copy the full error message and note which session was the victim.
2. While both are waiting (before the second), run the pg_blocking_pids query from a third session and describe what you see.
3. Rewrite both transactions so the deadlock is impossible, without using LOCK TABLE and without changing the isolation level.
4. If it still happened under load, what should the application do? State the SQLSTATE code and the exact pattern.

Solution 1

1. The final stock is 38. B is left waiting at its own SELECT ... FOR UPDATE, not at the UPDATE: that's the change from section 1. On unblocking, after A's COMMIT, it reads 39 —the already updated value— and computes 38 from it. The window between reading and writing has disappeared.

2. The error is immediate, in milliseconds: ERROR: could not obtain lock on row in relation "products". And it's a perfectly valid response for a web request: better to return "try again" instantly than to leave the user staring at a spinner for thirty seconds.

3. B's query returns 0 rows, and that's extremely dangerous here. SKIP LOCKED is designed for "give me any free row"; but B doesn't want any product: it wants that one. An empty result would make the application believe that product 15 doesn't exist, or —worse— carry on without deducting anything. SKIP LOCKED only makes sense when the rows are interchangeable, as in a work queue.

4. The three solutions:

For GreenStore's basket: the first, and if the deduction needs intermediate logic (checking reservations, applying promotions), the third. The second is ruled out because it would force implementing retries on the shop's hottest path and would abort exactly at the traffic peaks, which is when it's least welcome.

Solution 2

1. The query is section 4's: SELECT ... WHERE status = 'paid' ORDER BY order_date FOR UPDATE SKIP LOCKED LIMIT 1. Session A takes order 18 (2026-01-27) and B, without waiting, 19 (2026-02-09).

2. The third returns 0 rows, because GreenStore's only two paid orders are locked by A and B. It's the correct behaviour of an empty queue: operator 3 doesn't wait, sees there's no work and asks again later.

3. Without SKIP LOCKED, the second session would be left waiting for A to finish. And on unblocking the worst would happen: PostgreSQL rereads the row, re-evaluates the WHERE and discovers that order 18 no longer satisfies status = 'paid' (A moved it to shipped), so it discards it and returns… 0 rows. It doesn't even take 19, because the LIMIT 1 had already been resolved. Operator 2 would have waited for nothing.

4. You need to mark the orders somebody is picking and since when, so the abandoned ones can be recovered:

The query becomes WHERE status = 'paid' AND (picking_since IS NULL OR picking_since < now() - INTERVAL '15 minutes'), and the process writes picking_since = now() when it takes one. That way, if an operator dies halfway, their order goes back into the queue after fifteen minutes. It isn't canonical because 01-06's schema models a shop, not a work queue: the column exists only for this mechanism and no other module uses it.

Solution 3

1. The message is section 8's, with deadlock detected and code 40P01. The victim is the session that triggers the detection, that is, the one that had been waiting for longer than deadlock_timeout when the cycle was found — normally the second to become blocked (B in the script). The other carries on and commits normally.

2. From the third session you see two rows, each blocked by the other: A's pid has B's pid in blocked_by, and B's has A's. That reciprocity is the wait graph's cycle, visible in a query's output. It's the module's most satisfying diagnosis, and you have to be quick: PostgreSQL resolves it in about a second.

3. It's enough to always access the rows in the same order, for example by ascending id: both sessions run BEGIN; → UPDATE ... WHERE id = 1; → UPDATE ... WHERE id = 15; → COMMIT;, in that order. Now the cycle is impossible: whoever gets product 1 will end up getting 15, and whoever doesn't get it will be waiting with nothing locked, so it can't block anybody. It's section 8's golden rule. The general version, when the rows are chosen at run time, is to sort the list before walking it: SELECT ... WHERE id = ANY($1) ORDER BY id FOR UPDATE.

4. It should retry the whole transaction. The code is 40P01 (deadlock_detected), and the pattern is exactly 09-04's loop: rollback(), a wait with exponential backoff and jitter, up to five attempts, without retrying any other kind of error. That's why that pseudocode filtered on '40001' and '40P01': they're the module's two errors that mean "try again", not "you did it wrong".

You close the last lesson with the tools that were missing:

• Every UPDATE and DELETE locks its row until the end of the transaction, and that's why session B was waiting. Reading never waits; writing does. And on unblocking, PostgreSQL rereads and recomputes, which is why SET stock = stock - 1 gave 38.
• The SELECT ... FOR UPDATE / FOR NO KEY UPDATE / FOR SHARE / FOR KEY SHARE family with its compatibility table, and the safe read-modify-write pattern: the correct solution to 09-04's lost update, and the one shops actually use.
• SKIP LOCKED, which turns a query into a work dispatcher —two operators, orders 18 and 19, without treading on each other— and NOWAIT for failing in milliseconds instead of hanging a user.
• Optimistic against pessimistic: locking in advance where there's real contention (the stock), or checking on writing with a version column where there's human waiting — always looking at the UPDATE 0, because there's no error there to warn you.
• The table locks and their eight modes, with the two promises they close: most ALTER TABLEs take ACCESS EXCLUSIVE and queue the whole database behind them (05-06), and CREATE INDEX blocks writes while CREATE INDEX CONCURRENTLY doesn't (08-02). In production, lock_timeout and CONCURRENTLY, always.
• Deadlocks: the cycle in the wait graph, the deadlock detected with its 40P01, the victim picked by the engine after a second, and the golden rule that makes them impossible — always access resources in the same order.
• The diagnosis: pg_blocking_pids() as the first query when something hangs, pg_locks for the detail, pg_cancel_backend and pg_terminate_backend to cut things off, and the four timeouts headed by lock_timeout.
• And why a sequence isn't undone by a ROLLBACK: because making it transactional would turn every INSERT into a bottleneck. Hence the gaps, and hence the rule: the primary key isn't an invoice number.

And with that module 9 closes. In five lessons you've gone from "there's a statement and I run it" to understanding the whole system: you know that every standalone statement is already a transaction and that confirming an order is four operations that have to go together; you know the four ACID guarantees one by one, with the WAL that makes a COMMIT survive a power cut and with MVCC, which finally explains the dead rows, the bloat and the VACUUM left pending in module 8; you handle the complete TCL, including the SAVEPOINTs that rescue a batch from a failed load; you can name the four concurrency anomalies, you've watched a matcha tea sale get lost across two sessions and you know that PostgreSQL doesn't implement READ UNCOMMITTED and its REPEATABLE READ allows no phantoms; and you can choose between a conditional statement, a FOR UPDATE and a SERIALIZABLE with a retry, knowing what you pay in each case.

You already know how to read (modules 2 to 4 and 7), write (module 5), transform (module 6), optimize (module 8) and coordinate (this one). What's missing isn't a new capability: it's the arsenal that makes everything above maintainable, and that separates a query that works from a system you can live with. In module 10, Advanced, you'll see views, to give a name to a complex query and stop copying it everywhere; CTEs with WITH, which turn a forty-line nested subquery into readable steps and let you write recursive queries to walk GreenStore's employee hierarchy or its referral chain; window functions, with which you compute rankings, moving averages and running totals without losing the detail; stored procedures, where the order confirmation you built in 09-03 will end up living; triggers, which automatically apply those business rules 09-02 left outside a CHECK's reach; and the JSON type, for what doesn't fit in a fixed schema. It's the toolbox of somebody who's no longer learning SQL, but using it.