VisaSpark
Sign in
advanced22 min

Concurrent-Update Problems and Isolation Levels

The specific anomalies that happen when two transactions overlap in time, and how PostgreSQL's isolation levels trade off which of those anomalies each one still permits.

What you'll learn

  • Describe the lost-update and non-repeatable-read anomalies concretely
  • Explain what PostgreSQL's default Read Committed isolation level does and does not prevent
  • Choose an appropriate isolation level (or explicit locking) for a scenario with a concurrent-update risk

Prerequisites

Explanation

This lesson's scenarios require two genuinely separate, concurrent database connections to demonstrate — something a single-connection browser sandbox cannot honestly show. This module's guided local lab has you reproduce these exact anomalies yourself, with two real psql sessions running side by side against a real PostgreSQL server.

A lost update happens when two concurrent transactions both read the same row, both compute a new value based on what they read, and both write it back — the second write silently overwrites the first, and one of the two updates is lost entirely, with no error or warning. Two transactions both doing "read stock = 10, then UPDATE SET stock = 9" (each independently decrementing by one) can both succeed, leaving stock = 9 — even though two units were actually sold and the correct final value was 8. This is a real, common bug pattern, not a rare edge case: any "read a value, compute based on it, write it back" sequence run from application code (rather than as a single atomic SQL statement) is vulnerable to it.

A non-repeatable read happens when a transaction reads the same row twice and gets two different values, because another transaction committed a change to that row in between the two reads — the same query, run twice within what's supposed to be one consistent transaction, disagrees with itself. Phantom reads are the equivalent problem for a range of rows rather than a single row: a query re-run within the same transaction returns a different set of rows because another transaction inserted or deleted a matching row in between.

PostgreSQL's default isolation level is Read Committed: each individual statement within a transaction sees a fresh snapshot of committed data as of when that specific statement starts — meaning it never sees another transaction's uncommitted, in-progress work (this much is guaranteed at every isolation level), but it genuinely is vulnerable to non-repeatable reads and lost updates, because two different statements in the same transaction can see two different committed snapshots. Repeatable Read (PostgreSQL's stricter level) fixes non-repeatable reads by giving the entire transaction one consistent snapshot taken at its start, and also prevents most lost-update patterns — at the cost of PostgreSQL sometimes needing to abort and force a retry of a transaction that would otherwise violate that consistency (a "serialization failure," which application code must be prepared to catch and retry). The honest, practical takeaway: the strongest guarantee, and the one specific problem it solves, must be chosen deliberately for the actual scenario — Read Committed's default is the right choice for many ordinary queries, but a genuinely concurrent read-modify-write sequence (the stock-decrement example) needs either a stricter isolation level or an explicit row lock (SELECT ... FOR UPDATE) to be correct.

Example

Real PostgreSQL transaction interleaving, shown for reading -- genuinely demonstrating this requires two separate psql sessions running concurrently, which this module's guided local lab has you do for real.

-- Session A                              -- Session B
BEGIN;
SELECT stock FROM inventory WHERE id = 1;  -- reads 10
                                            BEGIN;
                                            SELECT stock FROM inventory WHERE id = 1; -- ALSO reads 10
UPDATE inventory SET stock = 9 WHERE id = 1;
COMMIT;                                    -- A's change is now committed
                                            UPDATE inventory SET stock = 9 WHERE id = 1; -- B computed 9 from its OWN read of 10
                                            COMMIT;
-- Final stock = 9, but TWO units were actually sold -- one update was silently lost.

-- The fix: an explicit row lock forces B to wait for A's transaction, then re-read the ALREADY-UPDATED value:
BEGIN;
SELECT stock FROM inventory WHERE id = 1 FOR UPDATE; -- locks the row; a concurrent FOR UPDATE on
                                                       -- the same row blocks until this transaction ends
UPDATE inventory SET stock = stock - 1 WHERE id = 1;
COMMIT;

Guided exercise

Guided exercise

Write simulateLostUpdate(initialStock, decrementCount) modeling the lost-update anomaly: 'decrementCount' concurrent transactions ALL read the SAME initial value before any of them writes, then all independently write (initialStock - 1) -- return the resulting stock (which will be wrong if decrementCount > 1, demonstrating the anomaly), plus the count of updates that were 'lost'.

Checks: correctly models the lost-update anomaly for 2 concurrent transactions · correctly scales the lost-update count for more concurrent transactions · a single, non-concurrent transaction has zero lost updates

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Independent exercise

Independent exercise

Write decrementWithLock(stock, decrementCount) modeling the FOR UPDATE fix: each of the decrementCount 'transactions' now reads the LATEST value (as if serialized one at a time by a lock, not the same stale initial value), so the result is correct. Then write recommendIsolationApproach(hasReadModifyWritePattern) returning 'row-lock-or-stricter-isolation' if true, otherwise 'default-read-committed-is-fine'.

Checks: correctly models a locked, sequential decrement producing the right result · scales correctly for more sequential decrements · recommends a stronger approach for a read-modify-write pattern · recommends the default for a pattern with no concurrent-update risk

Code editor. Press Escape then Tab to leave the editor if keyboard focus becomes trapped. Press Control+Shift+M inside the editor to toggle Tab-key focus trapping.

Loading editor…

Stuck? Get a hint.

Guided local lab

Add Transactions, Constraints, and Useful Indexes Locally

Runs on your computer
This lab runs on your own computer, in your own terminal and editor — not in your browser. VisaSparkSchools does not execute, run, or verify these commands for you. Follow the verification steps yourself to confirm your result.

Reproduce the lost-update anomaly for real, using two concurrent psql sessions against your local PostgreSQL install, fix it with FOR UPDATE, and add an index that measurably changes a query's execution plan.

Required tools

  • PostgreSQL server (16 or newer)
  • psql (matching your server version)
  • Two terminal windows (for two concurrent sessions) (any)

Setup

  1. Using the schema.sql from the earlier guided local lab (or a fresh learning_platform_lab database), add an inventory table: `CREATE TABLE inventory (course_id INTEGER PRIMARY KEY REFERENCES course(id), stock INTEGER NOT NULL CHECK (stock >= 0));` then seed one row with a starting stock.
  2. Open TWO separate terminal windows, each running `psql -d learning_platform_lab`, so you have two independent, concurrent sessions (Session A and Session B) against the same database.

Project structure

learning-platform-lab/
  inventory.sql
  concurrency-notes.md

Starter files

inventory.sql

-- TODO: CREATE TABLE inventory (course_id INTEGER PRIMARY KEY REFERENCES course(id),
--   stock INTEGER NOT NULL CHECK (stock >= 0))
-- TODO: seed one row, e.g. course_id = 1, stock = 10
-- TODO: add an index on enrollment(course_id) -- a column you'll frequently filter/join on

concurrency-notes.md

# Concurrency lab notes

Record what you observe here as you run the steps below.

## Step 1: Reproduce the lost update (WITHOUT a lock)
TODO: run the two-session interleaving from this lesson's explanation (both SELECT stock
before either UPDATEs), and record the WRONG final stock value you observe.

## Step 2: Fix it with FOR UPDATE
TODO: repeat the interleaving, but have Session A use
"SELECT stock FROM inventory WHERE course_id = 1 FOR UPDATE;" -- record what Session B's
SELECT ... FOR UPDATE does while Session A's transaction is still open (does it return
immediately, or wait?), and confirm the final stock is now correct.

## Step 3: EXPLAIN a query before and after your index
TODO: run EXPLAIN on a query filtering enrollment by course_id BEFORE adding the index,
then again AFTER -- record what changed in the plan's output.

Requirements

  • inventory.sql creates the inventory table with a CHECK (stock >= 0) constraint and seeds one row.
  • concurrency-notes.md documents the WRONG final stock value observed from the unprotected, two-session interleaving.
  • concurrency-notes.md documents that Session B's SELECT ... FOR UPDATE blocks (waits) while Session A's transaction holding the same row's lock is still open.
  • concurrency-notes.md documents the CORRECT final stock value observed once FOR UPDATE is used.
  • An index exists on enrollment(course_id), and concurrency-notes.md records a visible difference in EXPLAIN output before and after it existed.

Commands to run

  • Apply the inventory table and seed data

    psql -d learning_platform_lab -f inventory.sql

  • Session A

    psql -d learning_platform_lab

  • Session B (a second terminal, run concurrently with Session A)

    psql -d learning_platform_lab

  • Check query plan before/after indexing

    EXPLAIN SELECT * FROM enrollment WHERE course_id = 1;

Expected behavior

Without FOR UPDATE, two overlapping sessions decrementing the same row from a shared initial read produce a final stock one higher than correct (a lost update). With FOR UPDATE, Session B's SELECT visibly blocks until Session A's transaction commits or rolls back, and the final stock is correct. EXPLAIN's output changes from a Seq Scan (before the index) to an Index Scan (after it) for a query filtering on the indexed column.

Verify it yourself

  • (Session A) BEGIN; SELECT stock FROM inventory WHERE course_id = 1;

    Expected: Returns the current stock value, transaction left open

  • (Session B, while A is still open) BEGIN; SELECT stock FROM inventory WHERE course_id = 1 FOR UPDATE;

    Expected: Hangs/blocks if Session A also used FOR UPDATE and hasn't committed yet -- confirming the lock is real

  • EXPLAIN SELECT * FROM enrollment WHERE course_id = 1;

    Expected: Shows "Index Scan" (not "Seq Scan") once the index on enrollment(course_id) exists

Troubleshooting

  • Session B's FOR UPDATE doesn't seem to block — Confirm Session A's transaction is still open (no COMMIT or ROLLBACK issued yet) and that Session A also used FOR UPDATE (or an UPDATE) on the same row, not just a plain SELECT.
  • EXPLAIN still shows a Seq Scan after creating the index — Confirm the index was actually created (`\d enrollment` in psql lists indexes on the table) and that the table has enough rows for the planner to consider an index worthwhile — on a very small table, PostgreSQL may correctly still choose a sequential scan as genuinely faster.
  • `ERROR: new row for relation "inventory" violates check constraint` — This is the CHECK (stock >= 0) constraint correctly rejecting an attempt to decrement stock below zero — working as intended, not a bug to fix.

Stuck? Get a hint.

Extension challenge

Repeat Step 1 and Step 2, but with THREE concurrent sessions instead of two, and confirm FOR UPDATE still serializes all three correctly (the final stock should be exactly 3 less than the starting value).

When you've verified this locally, use the "Mark lesson complete" button below to record your progress.

Common mistakes

  • Assuming the default Read Committed isolation level automatically prevents a lost update in a read-modify-write sequence -- it doesn't; Read Committed only guarantees each statement sees committed data, not that two concurrent read-then-write sequences can't race.
  • Adding FOR UPDATE to the wrong statement (the later UPDATE instead of the earlier SELECT that reads the value being modified) -- the lock must be acquired at read time, before the decision based on that read is made, or the race condition remains.
  • Testing concurrency behavior with only one database session/connection -- a genuine lock-contention or isolation-anomaly scenario requires at least two independent, concurrent connections to actually overlap in time.

Knowledge check

Knowledge check

1. Two transactions both read stock=10, then both compute and write stock=9 (each independently decrementing by 1). What is this anomaly called?
2. Does PostgreSQL's default Read Committed isolation level prevent a transaction from ever reading another transaction's UNCOMMITTED changes?
3. What does SELECT ... FOR UPDATE do that a plain SELECT does not?

Takeaway

PostgreSQL's default isolation level guarantees you never see another transaction's uncommitted work, but does NOT by itself prevent a lost update in a read-modify-write sequence — that specific, common pattern needs an explicit row lock (FOR UPDATE) or a stricter isolation level, chosen deliberately for the scenario that actually needs it.

Summary

A lost update happens when two transactions read the same value and both write based on it, silently overwriting each other. Non-repeatable reads and phantom reads are related anomalies for a single row and a row range, respectively. Read Committed (PostgreSQL's default) prevents dirty reads but not lost updates; SELECT ... FOR UPDATE or Repeatable Read isolation are the tools that close that specific gap.

References

Your notes

Notes save automatically.

Finished this lesson?

Mark it complete to track your progress and schedule a future review.

Next: Indexes, Composite Indexes, and Reading EXPLAIN →