Chapter 27: Concurrency Control — What Happens When Two Users Touch the Same Data

Where you are: Part IV, Chapter 27 of 40. Chapter 26 gave you transactions and isolation; this chapter is the mechanics underneath — locks, deadlocks, and the strategies for letting thousands of users safely modify the same data. MVCC keeps readers and writers out of each other's way; this is about writers vs. writers.

Learning paths: 💻 🏗️ especially (building correct concurrent systems); 🔬 CS students (the classic problems); 📊 analysts can skim.

The problem MVCC doesn't solve by itself

MVCC (Chapter 26) means readers and writers don't block each other. But two transactions writing the same row genuinely conflict — and naive code gets it wrong. The canonical failure is the lost update:

   Two transactions both "add 1 to the counter":
   T1: read counter (= 10)
   T2: read counter (= 10)        ← both read the same starting value
   T1: write counter = 11
   T2: write counter = 11         ← T2's write overwrites T1's; one increment LOST

Two increments happened; the counter went up by one. This read-modify-write race lurks anywhere code reads a value, computes a new one in the application, and writes it back — inventory decrements, balance updates, counters, "claim the next job." It's invisible in single-user testing and corrupts data under concurrency (the Chapter 13 upsert race was a cousin of this). Concurrency control is how you prevent it.

Locks

A lock is a reservation a transaction holds on a resource (a row, a table) that controls what others can do with it concurrently. PostgreSQL uses locks (alongside MVCC) to coordinate conflicting writes.

• Row-level locks — lock specific rows. The common, fine-grained kind: lock just the rows you're modifying so others can work on different rows freely. Taken automatically by UPDATE/DELETE (on the rows they touch), and explicitly by SELECT ... FOR UPDATE.
• Table-level locks — lock a whole table. Taken by DDL (ALTER TABLE, Chapter 14's ACCESS EXCLUSIVE) and, in lighter modes, by ordinary operations. Coarser; mostly you don't request these directly.
• Lock modes range from shared (many can hold it — for reading-with-intent) to exclusive (only one — for modifying). Two exclusive requests on the same row conflict; one waits for the other.

The key principle: lock as little as possible for as short as possible. Fine-grained, brief locks maximize concurrency; coarse, long-held locks serialize everything.

Pessimistic locking: SELECT ... FOR UPDATE

The pessimistic strategy assumes conflicts will happen, so it locks the row up front. SELECT ... FOR UPDATE reads a row and takes an exclusive lock on it, so any other transaction trying to update (or also FOR UPDATE) that row must wait until you commit. This solves the lost update cleanly:

BEGIN;
SELECT quantity FROM inventory
WHERE product_id = 1 AND warehouse_id = 1
FOR UPDATE;                        -- lock this row; others wait
-- now safely read-modify-write: no one else can change it until we commit
UPDATE inventory SET quantity = quantity - 1
WHERE product_id = 1 AND warehouse_id = 1;
COMMIT;                            -- releases the lock

Between the FOR UPDATE and the COMMIT, no other transaction can modify that inventory row — so two concurrent "decrement stock" transactions are serialized on that row: one waits for the other, and no update is lost. Pessimistic locking is simple and correct; the cost is that waiters block, reducing concurrency on hot rows.

Aside: an even simpler fix for a pure increment/decrement is to let the database do the arithmetic atomically in one statement — UPDATE inventory SET quantity = quantity - 1 WHERE ... is atomic on its own row (the UPDATE locks the row for its duration). The lost update happens when you read the value into the application, compute, and write back. Prefer doing arithmetic in SQL when you can; use FOR UPDATE when you must read-then-decide across statements.

Optimistic concurrency control

The optimistic strategy assumes conflicts are rare, so it doesn't lock up front — it checks at write time whether anything changed, and retries if so. The common implementation is a version column (or a timestamp): read the row and its version; when writing, require the version to be unchanged, and bump it:

-- read: version = 7
UPDATE products
SET price = 99.00, version = version + 1
WHERE product_id = 1 AND version = 7;     -- only succeeds if version is still 7
-- if 0 rows updated, someone else changed it first → re-read and retry

If another transaction updated the row in between, its version is now 8, the WHERE version = 7 matches nothing, 0 rows are updated, and the application knows it lost the race and retries with fresh data. No locks are held between read and write — great for low-contention data and stateless web apps. The cost is the retry logic and wasted work when conflicts do happen.

Pessimistic vs. optimistic: lock-up-front (simple, blocks, good for high contention) vs. check-at-write-and-retry (no blocking, good for low contention). Choose by how often the same row is contended.

The lost update, in depth

The lost update is the canonical concurrency bug, and understanding it deeply — why it happens, why it's so insidious, and the full range of fixes — is the foundation of writing correct concurrent code. The bug arises from a read-modify-write sequence performed across separate steps: read a value into the application, compute a new value from it, write the new value back. When two transactions do this concurrently on the same data, one update can silently overwrite the other.

Trace it precisely. Two transactions both want to add 1 to a counter currently at 10. Transaction A reads 10. Transaction B reads 10 (both read before either writes). Transaction A computes 11 and writes it. Transaction B computes 11 (from its stale read of 10) and writes it, overwriting A's update. Two increments occurred, but the counter went from 10 to 11 — one increment was lost. The data is now wrong, and nothing errored; both transactions succeeded, both committed, and the result is silently incorrect. This pattern lurks everywhere application code reads-then-writes: decrementing inventory, updating an account balance, incrementing a view count, claiming the next item from a queue. Any time the new value depends on the old value, and the old value is read into the application before the write, the lost-update race is possible.

What makes the lost update so dangerous is that it's invisible in normal testing. Run the code single-threaded — one user, one request at a time — and it works perfectly: read 10, write 11, read 11, write 12, all correct. The bug only manifests under concurrency, when two transactions interleave their read-modify-write at just the wrong moment, which happens rarely enough to pass testing but regularly enough to corrupt data in production under load. This is why concurrency bugs are notorious: they pass every single-user test, ship to production, and then cause mysterious data corruption that's hard to reproduce because it depends on precise timing. The lost update is the archetype of this whole class of bug, and recognizing the read-modify-write pattern as dangerous is the first line of defense.

The fixes fall into three families, each developed in the sections that follow. The simplest, when applicable, is to avoid the read-modify-write entirely by doing the arithmetic in a single SQL statement — UPDATE counter SET value = value + 1 is atomic on its row (the database reads, computes, and writes as one locked operation), so no interleaving is possible. When the logic is more complex than arithmetic and you must read-then-decide across statements, pessimistic locking (SELECT ... FOR UPDATE) locks the row so the second transaction waits. And for low-contention cases in stateless applications, optimistic concurrency (a version column with compare-and-swap) detects the conflict at write time and retries. Each is the right tool for a different situation, but they all address the same fundamental problem: coordinating concurrent writes to the same data so no update is lost. Understanding the lost update — its mechanism, its invisibility in testing, and these three fix families — is the core competence of concurrency control, because nearly every concurrency bug is a variant of it.

Locks, in depth

Locks are the mechanism PostgreSQL uses (alongside MVCC) to coordinate conflicting access, and understanding their granularity and modes lets you reason about what blocks what — essential for both correctness and avoiding contention. A lock is a reservation a transaction holds on a resource, controlling what other transactions can do with it concurrently. The two dimensions that matter are granularity (what's locked) and mode (how strong the lock is).

Granularity ranges from fine to coarse. Row-level locks lock specific rows — the fine-grained, high-concurrency kind, because locking only the rows you're modifying lets other transactions work freely on different rows. These are taken automatically by UPDATE and DELETE (on the rows they affect) and explicitly by SELECT ... FOR UPDATE. Table-level locks lock an entire table — coarse, serializing access to the whole table — taken by DDL operations (ALTER TABLE takes a strong ACCESS EXCLUSIVE lock, the Chapter 14 lesson) and, in lighter modes, by ordinary operations to coordinate with DDL. The principle is to lock as finely as possible: row locks for normal operations (so concurrency stays high), reserving table locks for the operations that genuinely need them (DDL). Fine-grained locking is what lets many transactions modify different rows of the same table simultaneously without blocking each other.

Lock modes range from shared to exclusive. A shared lock can be held by many transactions at once — used when you want to read with the intent of preventing modification, but don't mind other readers. An exclusive lock can be held by only one transaction — used when modifying, so no other transaction can read-with-intent or modify the same resource. The compatibility between modes determines what blocks what: two shared locks on the same resource coexist (many readers), but an exclusive lock conflicts with everything (one writer excludes all others on that resource). PostgreSQL has a richer set of modes than just shared/exclusive (several intermediate modes for different operations), with a compatibility matrix governing which can coexist, but the shared-versus-exclusive distinction captures the essential idea: reads-with-intent can share, writes are exclusive, and conflicting requests wait.

The key practical principle, restated because it governs all concurrency performance, is lock as little as possible, for as short as possible. Every lock held is potential contention — another transaction that might have to wait. Fine-grained locks (rows, not tables), held briefly (short transactions), maximize the concurrency the database can sustain; coarse locks (tables) held long (long transactions) serialize work and create bottlenecks. Most of the time, you don't request locks explicitly — UPDATE/DELETE take the row locks they need automatically, and PostgreSQL manages the rest. You reach for explicit locking (SELECT ... FOR UPDATE) only when you need to lock rows you're about to modify across multiple statements, and you reach for table locks essentially never (DDL takes them for you). Understanding the granularity and modes lets you reason about why one transaction is waiting for another (they want conflicting locks on the same resource) and how to reduce contention (finer locks, shorter transactions) — which is the diagnostic skill behind concurrency performance.

Pessimistic and optimistic concurrency, compared

The two strategies for preventing lost updates — pessimistic and optimistic concurrency control — represent opposite assumptions about how often conflicts occur, and choosing between them is a real engineering decision worth understanding deeply. The names describe their attitudes: pessimistic assumes conflicts will happen and prevents them up front; optimistic assumes conflicts are rare and detects them after the fact.

Pessimistic concurrency locks the row before modifying it, so conflicting transactions wait. SELECT ... FOR UPDATE reads a row and takes an exclusive lock on it in one step, so any other transaction trying to update that row (or also lock it FOR UPDATE) must wait until the first commits and releases the lock. This serializes access to the contended row — two concurrent "decrement the last unit of stock" transactions become sequential: one locks and decrements, the other waits and then sees the updated value. Pessimistic locking is simple and always correct — the lock guarantees no interleaving — and it's the right choice for high-contention data (where conflicts are frequent, so preventing them up front avoids wasted retries) and for read-then-decide logic that's too complex for a single SQL statement. Its cost is that waiters block, reducing concurrency on hot rows, and that held locks can cause deadlocks (next section) if not ordered consistently.

Optimistic concurrency takes no lock; instead it checks at write time whether the row changed since it was read, using a version column (or timestamp). You read the row and its version; when you write, you require the version to be unchanged (WHERE version = <the value you read>) and bump it. If another transaction modified the row in between, its version changed, your WHERE matches nothing, zero rows are updated, and your application detects it lost the race and retries with fresh data. No locks are held between read and write, so there's no blocking — excellent for low-contention data and stateless web applications (where holding a lock across a stateless request is awkward). Its cost is the retry logic you must write, and the wasted work when conflicts do occur (you did the work, found you lost, and redo it). Optimistic concurrency shines when conflicts are rare (so retries are rare) and blocking is undesirable; it degrades when contention is high (frequent retries waste work).

The choice, then, is by contention level: high-contention rows favor pessimistic locking (prevent conflicts up front, avoid retry storms); low-contention rows favor optimistic concurrency (no blocking, rare retries). A hot inventory row that thousands contend for wants pessimistic locking; a user-profile row that its owner occasionally edits wants optimistic. Many systems use both — pessimistic for the genuinely hot, contended operations, optimistic for the common low-contention updates. And underlying both is the simplest option when it applies: do the modification in a single atomic SQL statement (SET x = x - 1), which needs neither strategy because the database handles the locking internally for that one statement. The decision tree: can it be one atomic statement? Use that. Otherwise, is the row highly contended? Pessimistic. Lightly contended in a stateless app? Optimistic. Understanding the two strategies, their costs, and the contention-based choice between them is what lets you handle concurrent modification correctly and performantly, rather than either corrupting data (no strategy) or needlessly serializing everything (pessimistic everywhere).

Deadlocks

A deadlock is a cycle of transactions each waiting for a lock the other holds — so none can proceed:

   T1: locks row A,  then wants row B
   T2: locks row B,  then wants row A
                ┌──────────────┐
        T1 ───► waits for B ───► held by T2
        T2 ───► waits for A ───► held by T1     ← cycle: neither can move

PostgreSQL detects deadlocks automatically (it periodically checks the wait-for graph) and breaks them by aborting one transaction with a deadlock error; the application should catch it and retry. Deadlocks aren't catastrophic, but frequent ones hurt.

Prevention is mostly about consistent lock ordering: if every transaction acquires locks in the same order (e.g., always lock the lower account id before the higher), cycles can't form. The classic transfer deadlock — T1 locks A then B, T2 locks B then A — vanishes if both always lock the lower id first. Other mitigations: keep transactions short (hold locks briefly), and lock as few rows as possible.

Common mistake. Acquiring locks (or updating rows) in inconsistent order across code paths — a recipe for intermittent deadlocks under load. Establish a canonical lock order (e.g., by primary key) and follow it everywhere.

Advisory locks

Sometimes you need to lock something that isn't a row — a "only one worker should run this job at a time" mutex, or a named resource. PostgreSQL's advisory locks are application-defined locks keyed by an integer you choose:

SELECT pg_advisory_lock(12345);   -- acquire a named lock (blocks until free)
-- ...do the exclusive work...
SELECT pg_advisory_unlock(12345);

They're "advisory" because the database doesn't tie them to any data — your application decides what the number means. Useful for cross-session coordination, leader election, and serializing a job without a real row to lock on.

Connection pooling

Concurrency isn't only about locks — it's also about connections. Each PostgreSQL connection consumes memory and a backend process; a server handles a bounded number well (often a few hundred). A web app that opens a new connection per request can exhaust the server with thousands of mostly-idle connections, degrading everyone.

A connection pool (PgBouncer, pgpool, or an in-app pool) maintains a small set of reusable connections shared across many client requests. Requests borrow a connection, use it briefly, and return it. This keeps the server's connection count sane while serving high request volume — essential for any production web application (and revisited in Chapters 29, 38).

Why this matters. "We added more app servers and the database got slower" is often a connection-exhaustion story: more app servers × per-request connections = thousands of connections overwhelming the database. A pooler fixes it by capping and reusing connections. Concurrency at scale is as much about connection management as about locks.

Designing for high concurrency

Pulling it together — to let many users safely modify the same data:

• Do arithmetic in SQL (SET x = x - 1) rather than read-modify-write in the app, when possible (atomic on the row).
• Use FOR UPDATE (pessimistic) for read-then-decide logic on contended rows; version columns (optimistic) for low-contention updates in stateless apps.
• Acquire locks in a consistent order to prevent deadlocks; retry on deadlock and serialization failures.
• Keep transactions short — hold locks (and snapshots) briefly.
• Pool connections so the server isn't overwhelmed.
• Lean on the right isolation level (Chapter 26) — Serializable can prevent some anomalies without manual locking, at the cost of retries.

A worked scenario: the last item in stock, three ways

Let's solve the canonical concurrency problem — two customers racing for the last unit of stock — three different ways, because comparing the solutions cements when to use each. The setup: inventory has quantity = 1 for a product, and two checkout transactions arrive simultaneously, each wanting to buy one unit. The wrong outcome (overselling) is both succeeding and decrementing to 0, selling two units of one. The right outcome is exactly one succeeding.

Solution 1 — atomic SQL with a guard. The simplest fix avoids the read-modify-write entirely by doing the decrement and the check in one atomic statement: UPDATE inventory SET quantity = quantity - 1 WHERE product_id = 1 AND quantity > 0. This is atomic on the row — the database locks the row for the statement's duration, so the two concurrent updates are serialized: the first decrements 1 to 0 and reports one row updated; the second finds quantity > 0 false (it's now 0) and updates zero rows, which the application reads as "out of stock." No overselling, no application-side locking, no retry — just a guarded atomic update. This is the best solution when the logic fits a single statement, which a simple decrement-with-check does.

Solution 2 — pessimistic locking. When the logic is more complex (check stock, apply business rules, then decide), you lock the row first: SELECT quantity FROM inventory WHERE product_id = 1 FOR UPDATE, which locks the row so the second transaction waits. The first transaction reads 1, decides to fulfill, decrements to 0, commits (releasing the lock); the second transaction's FOR UPDATE then proceeds, reads 0, and correctly declines. The two are serialized on the row by the lock. This handles arbitrary read-then-decide logic correctly, at the cost of the second buyer waiting — fine for this case, and the right choice when you can't compress the logic into one statement.

Solution 3 — Serializable isolation. Set both transactions to Serializable (Chapter 26), and PostgreSQL detects that they can't both be valid under any serial ordering (both read quantity 1, both decrement — impossible serially) and aborts one with a serialization failure. The application retries the aborted one, which now reads quantity 0 and declines. This requires no explicit locking — the isolation level catches the anomaly — but requires retry logic and works best when such conflicts are rare. Comparing the three: atomic SQL is simplest and best when the logic fits one statement; pessimistic locking handles complex logic with waiting; Serializable handles it declaratively with retries. All three prevent overselling; the choice depends on the logic's complexity (one statement or not?), the contention level (high favors pessimistic, low favors optimistic/Serializable), and whether you prefer waiting (pessimistic) or retrying (Serializable). This single scenario, solved three ways, captures the whole decision space of concurrency control — and the meta-lesson that there's usually more than one correct approach, chosen by the situation's specifics.

MVCC and locks, working together

A point that ties Chapters 26 and 27 together: PostgreSQL uses both MVCC and locks, and understanding how they divide the labor clarifies the whole concurrency picture. They're not competing mechanisms — they handle different kinds of conflict, and together they let PostgreSQL achieve high concurrency while maintaining correctness.

MVCC (Chapter 26) handles the reader-versus-writer interaction without locks. Because writers create new row versions rather than overwriting, readers can see the old version from their snapshot while a writer modifies the row — so readers never block writers and writers never block readers. This is the common case, and MVCC makes it contention-free: a long report doesn't block updates, ongoing updates don't block the report. Locks (this chapter) handle the writer-versus-writer interaction, which MVCC alone can't resolve. When two transactions want to modify the same row, there's a genuine conflict — they can't both win — so PostgreSQL uses row locks to serialize them: the second writer waits for the first to commit. MVCC handles "many readers and writers on different rows, or readers and writers on the same row"; locks handle "multiple writers on the same row." Together they cover all the cases.

This division explains PostgreSQL's concurrency behavior precisely. The reason mixed read/write workloads run smoothly is MVCC (readers and writers don't contend). The reason concurrent writes to the same row are correctly serialized is locking (writers contend, and the lock orders them). The reason a SELECT doesn't wait for an UPDATE is MVCC (the select reads its snapshot's version); the reason two UPDATEs of the same row do serialize is locking (the second waits for the first's row lock). When you understand that MVCC handles reader/writer concurrency through versioning and locks handle writer/writer conflicts through serialization, the database's concurrency behavior — what blocks what, and why — becomes fully predictable. A query that's blocked is blocked because it wants a lock another transaction holds (writer/writer); a query that proceeds despite concurrent activity does so because MVCC gave it a snapshot (reader, or non-conflicting writer). This combined model — MVCC for reader/writer, locks for writer/writer — is the complete picture of PostgreSQL concurrency, and it's why the database can sustain high concurrency (most interactions are the contention-free MVCC kind) while still guaranteeing correctness (the genuine writer/writer conflicts are properly serialized by locks).

Designing for high concurrency

Pulling the chapter's techniques into a coherent design approach: building systems where many users safely and performantly modify the same data is a synthesis of everything covered, applied with judgment. The goal is correctness and concurrency — neither corrupting data nor needlessly serializing everything.

The design principles, in priority order: First, prefer atomic SQL — when an operation can be expressed as a single statement (SET x = x - 1, an UPDATE ... WHERE guard), do that, because it's correct without application-side locking or retry, and the database handles the row locking internally. Second, lock minimally and correctly when you must — use SELECT ... FOR UPDATE for read-then-decide logic on contended rows (pessimistic), or a version column with retry for low-contention updates in stateless apps (optimistic), choosing by contention level. Third, prevent deadlocks with consistent lock ordering — establish a canonical order (by primary key) for acquiring multiple locks and follow it everywhere, and retry the rare deadlock that still occurs. Fourth, keep transactions short — hold locks and snapshots briefly, so contention windows are small and VACUUM isn't blocked (Chapter 28). Fifth, retry recoverable failures — deadlocks and serialization failures are expected under concurrency, and the application must catch and retry them rather than surfacing errors to users. Sixth, pool connections — so high request volume doesn't exhaust the server's bounded connections.

Underlying all of these is a mindset: design for concurrency from the start, because concurrency bugs don't appear in single-user testing. The lost update, the deadlock, the connection exhaustion — none manifests when you test alone; all appear under production load. So you can't discover them by testing the way you discover ordinary bugs; you have to reason about what happens when two transactions interleave, and design defensively. This is what makes concurrency a distinct competence: it requires thinking about simultaneity — what if two of these run at once? — which ordinary sequential reasoning doesn't surface. The practitioner who asks "what happens if two users do this at the same time?" for every state-modifying operation, and applies the techniques above, builds systems that stay correct under load. The one who tests single-user and ships builds systems that corrupt data mysteriously in production. Concurrency control, like the rest of Part IV, is basic competence (theme #5) — not arcane, but systematic: recognize the read-modify-write races, apply the right coordination (atomic SQL, locking, or retry), order locks consistently, keep transactions short, pool connections. Master that systematic approach, and many users safely sharing the same data — the property that most distinguishes a database from a spreadsheet — becomes something you design for confidently rather than something that bites you in production.

The final chapter of Part IV pulls back the curtain entirely, showing how PostgreSQL implements the MVCC, locking, and durability you've been using — the pages and heap where rows physically live, the buffer cache that keeps hot data in memory, the write-ahead log behind durability and crash recovery, and the VACUUM process that cleans up MVCC's dead tuples. Understanding the internals explains why everything in Part IV behaves as it does, completing the journey from "performance is basic competence" to genuine mastery of how the database works beneath your queries.

Concurrency control, then, is the bridge between the performance focus of Part IV's first chapters and the correctness focus of its transactional ones — it's where speed and correctness meet, because the techniques that keep concurrent data correct (locks, retries, short transactions) are also the techniques that keep a concurrent system fast (fine-grained locks, minimal blocking, pooled connections). A system that handles concurrency well is both correct and performant under load; one that handles it poorly is neither. That dual payoff — correctness and performance together — is why concurrency control is among the most valuable skills a database practitioner develops, and why it sits at the heart of building systems that hold up when real users arrive in numbers.

Common mistakes

• Read-modify-write in the application without locking → lost updates (the canonical bug).
• Inconsistent lock ordering → intermittent deadlocks.
• Not retrying deadlock/serialization failures → errors surfaced to users for a recoverable condition.
• A connection per request with no pool → connection exhaustion at scale.
• Long transactions holding locks → contention and blocked maintenance (Chapter 28).

Deadlocks, in depth

Deadlocks are the concurrency problem people most fear, but they're actually well-understood and preventable, and demystifying them removes a lot of anxiety about concurrent systems. A deadlock is a cycle of transactions, each waiting for a lock that another holds — so none can proceed, and without intervention they'd wait forever. The simplest case: transaction A locks row 1 and then tries to lock row 2, while transaction B locks row 2 and then tries to lock row 1. A waits for B (to release row 2), B waits for A (to release row 1), and the cycle is closed — neither can move.

PostgreSQL detects deadlocks automatically. It periodically checks the "wait-for graph" (which transaction is waiting for which), and when it finds a cycle, it breaks the deadlock by aborting one of the transactions (the victim) with a deadlock error, releasing its locks so the others can proceed. The aborted transaction's application should catch the error and retry — on retry, the conflicting transaction has usually finished, so it succeeds. This is important to internalize: a deadlock is not a catastrophe or a hang; PostgreSQL resolves it automatically within a second or so by sacrificing one transaction, and the correct application response is simply to retry the victim. Deadlocks become a problem only when they're frequent (lots of retries wasting work) or when the application doesn't retry (surfacing a deadlock error to the user for a recoverable condition).

The key to prevention is consistent lock ordering. A deadlock cycle can only form when transactions acquire locks in different orders — A locks 1-then-2 while B locks 2-then-1. If every transaction acquires locks in the same order — always the lower id before the higher, say — then a cycle is impossible: whoever gets the lower-id lock first will also get the higher-id lock first, and the other simply waits in line rather than forming a cycle. The classic transfer deadlock (A transfers from account 1 to 2, B transfers from 2 to 1, each locking the source then the destination) vanishes entirely if both transactions always lock the lower-numbered account first. So the rule is: establish a canonical order for acquiring locks (by primary key, by some consistent attribute) and follow it everywhere in your code. Inconsistent lock ordering across different code paths is the usual cause of intermittent production deadlocks — one code path locks A-then-B, another locks B-then-A, and under load they collide.

Beyond consistent ordering, the other deadlock mitigations are the general concurrency hygiene: keep transactions short (locks held briefly are less likely to overlap into a cycle), lock as few rows as possible (fewer locks, fewer chances for cycles), and retry on the deadlock error (so the rare deadlock that does occur is handled gracefully rather than surfaced). With consistent lock ordering as the primary prevention and retry as the safety net, deadlocks shift from a scary mystery to a managed, rare, automatically-resolved event. The mental model to carry: deadlocks come from inconsistent lock ordering, PostgreSQL detects and breaks them by aborting a victim, and you prevent them by ordering locks consistently and handle the rare survivor by retrying. That understanding turns deadlocks from a source of dread into just another concurrency consideration you design for.

Advisory locks, queues, and connection pooling

A few more concurrency tools round out the chapter, each solving a specific coordination problem that rows-and-transactions don't directly address. Advisory locks let you lock something that isn't a database row — a named resource, a job, an abstract mutex. pg_advisory_lock(key) takes an application-defined lock keyed by an integer you choose; it's "advisory" because the database doesn't associate it with any data — your application decides what the key means. This is invaluable for coordination that has no natural row to lock: "only one worker should run this nightly job at a time" (lock a well-known key before the job, so a second worker's attempt blocks or skips), leader election among application instances, or serializing access to an external resource. Advisory locks give you the database's robust locking mechanism for coordination beyond row-level data.

A closely related pattern is the job queue built with SELECT ... FOR UPDATE SKIP LOCKED. When multiple workers pull jobs from a queue table, you want each to grab a different job without blocking on each other. FOR UPDATE SKIP LOCKED locks the rows it selects but skips rows already locked by another transaction — so worker A grabs job 1, worker B (running concurrently) skips the locked job 1 and grabs job 2, and so on. Each worker gets a distinct job with no contention, turning a queue table into a concurrent work distributor. This pattern is the basis of many database-backed job queues, and it's a elegant use of row locking for work distribution.

Connection pooling addresses a different concurrency dimension: not locks, but connections. Each PostgreSQL connection consumes server memory and a backend process, and a server handles only a bounded number well (often a few hundred). A web application that opens a new connection per request can, under load, open thousands of connections and overwhelm the server — the classic "we added app servers and the database got slower" incident, where more app servers times per-request connections exhausts the database. A connection pool (PgBouncer, pgpool, or an in-application pool) maintains a small set of reusable connections shared across many requests: requests borrow a connection, use it briefly, and return it, keeping the server's connection count bounded while serving high request volume. Connection pooling is essential for any production web application, and it's a concurrency concern because it's about managing the concurrent connections hitting the database — a bottleneck as real as lock contention, and one that catches teams who scale their application tier without considering the database's connection limits. Together, advisory locks (coordinate non-row resources), SKIP LOCKED queues (distribute work concurrently), and connection pooling (manage concurrent connections) complete the concurrency toolkit beyond the row-locking core — each a tool for a coordination problem that arises in real concurrent systems at scale.

Progressive project: handle concurrent modification

In your domain, find an operation where two users could collide on the same row:

1. Identify a read-modify-write (decrement stock, claim the last seat, increment a counter, update a balance).
2. Make it safe — either do the arithmetic in one SQL statement, or use SELECT ... FOR UPDATE to lock the row first.
3. (Optional) Add optimistic locking — a version column and a compare-and-swap UPDATE, with retry-on-zero-rows.
4. Reason about deadlocks — if your operation locks multiple rows, define a consistent lock order.
5. Note where you'd need connection pooling as the app scales.

Summary

MVCC keeps readers and writers apart, but two transactions writing the same row conflict — the canonical bug being the lost update (read-modify-write race). Locks coordinate conflicting writes: row-level (fine-grained, preferred) and table-level (coarse), in shared/exclusive modes. Pessimistic control (SELECT ... FOR UPDATE) locks the row up front (simple, blocks waiters); optimistic control (a version column + compare-and-swap + retry) avoids locks and retries on conflict — choose by contention. Deadlocks (cyclic waits) are detected and one transaction aborted (retry); prevent them with consistent lock ordering and short transactions. Advisory locks coordinate non-row resources. Connection pooling (PgBouncer) keeps a high request volume from exhausting the server's bounded connections. Designing for concurrency = do arithmetic in SQL, lock minimally and consistently, retry recoverable failures, keep transactions short, and pool connections.

You can now: - Recognize and prevent the lost-update (read-modify-write) race. - Use row locks and SELECT ... FOR UPDATE (pessimistic) appropriately. - Implement optimistic concurrency with a version column and retry. - Explain deadlocks and prevent them with consistent lock ordering. - Use advisory locks and explain why connection pooling matters at scale.

What's next. Chapter 28 — Database Internals — how PostgreSQL actually stores and retrieves your data: pages and the heap, the buffer pool, the write-ahead log (WAL) behind durability, VACUUM and MVCC cleanup, and checkpoints. The machinery that explains why everything in Part IV works as it does.

Practice in exercises.md, test yourself with the quiz, apply it in the case studies, review the key takeaways, and go deeper with further reading.