Isolation, locks, and concurrency

A transaction can be correct on its own and still be wrong when someone else writes at the same time. Isolation is the rule that says your work should not be silently undone or mixed with half-finished work. Locks and version checks are two ways to keep that promise.

Isolation in one sentence

Last lesson grouped Ada's debit and credit so they commit together. Isolation answers a different question: what may Grace see and write while Ada's transaction is open, or right after it commits?

Think of a printed bank slip. If two cashiers copy the same balance and each write a new total, the last cashier to save wins. The first cashier's deposit never happened in the stored number. That is the problem this lesson names and then fixes.

A lost update is last writer wins

Ada has 500 cents. Grace adds 100 and stores 600. Ada had also read 500 and adds 50, so she stores 550. Grace's 100 is gone. Nobody rolled back. Both statements “succeeded.”

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO wallets VALUES (1, 'Ada', 500);

-- Grace reads 500 and writes 500 + 100.
UPDATE wallets SET cents = 600 WHERE wallet_id = 1;

-- Ada also read 500 earlier and writes 500 + 50.
UPDATE wallets SET cents = 550 WHERE wallet_id = 1;

SELECT wallet_id, owner, cents
FROM wallets;

Edit the query, predict the rows it will return, then run it.

Let the database add, do not store a remembered total

Write the change, not the answer you calculated in the application. SET cents = cents + 50 uses whatever is in the row now. After +100 and +50, the wallet is 650. Same two deposits, no lost update.

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL
);

INSERT INTO wallets VALUES (1, 'Ada', 500);

UPDATE wallets SET cents = cents + 100 WHERE wallet_id = 1;
UPDATE wallets SET cents = cents + 50 WHERE wallet_id = 1;

SELECT wallet_id, owner, cents
FROM wallets;

Edit the query, predict the rows it will return, then run it.

Optimistic locking: “update if nobody else did”

Sometimes the new value is not a simple +n. You read a row, think, then write. Add a version column. Your UPDATE must see the version you read. If it matches zero rows, someone else got there first. Retry: read again, think again.

That is optimistic because you do not lock while thinking. You check at write time. It is the same idea as last lesson's changes() guard, stored on the row.

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL,
  version INTEGER NOT NULL
);

INSERT INTO wallets VALUES (1, 'Ada', 500, 1);

UPDATE wallets
SET cents = cents + 100,
    version = version + 1
WHERE wallet_id = 1 AND version = 1;

SELECT wallet_id, owner, cents, version
FROM wallets;

Edit the query, predict the rows it will return, then run it.

CREATE TABLE wallets (
  wallet_id INTEGER PRIMARY KEY,
  owner TEXT NOT NULL,
  cents INTEGER NOT NULL,
  version INTEGER NOT NULL
);

INSERT INTO wallets VALUES (1, 'Ada', 500, 1);

UPDATE wallets
SET cents = cents + 100,
    version = version + 1
WHERE wallet_id = 1 AND version = 1;

UPDATE wallets
SET cents = cents + 50,
    version = version + 1
WHERE wallet_id = 1 AND version = 1;

SELECT wallet_id, owner, cents, version
FROM wallets;

Edit the query, predict the rows it will return, then run it.

Four surprises, in plain words

• Lost update. Two writes from the same old value; one disappears. You just ran this.
• Dirty read. You read a change that is not committed. If the other person ROLLBACKs, you acted on a draft. Default PostgreSQL READ COMMITTED does not allow this.
• Non-repeatable read. You SELECT Ada's balance, Grace commits a deposit, you SELECT again and see a new number in the same transaction.
• Phantom. You count rows that match a filter, someone inserts a new matching row, your second count is higher.

Isolation levels are a menu of which surprises you accept. READ UNCOMMITTED can dirty-read. READ COMMITTED sees other committed work (PostgreSQL's usual default). REPEATABLE READ keeps your snapshot of existing rows. SERIALIZABLE aims for a result that could have happened if everyone had gone one at a time. Higher isolation can mean more waiting or more retries. There is no free setting.

A lock is a “please wait” sign

A shared lock means many people may read. An exclusive lock means one person may write, and readers of that row (or file) may have to wait. Pessimistic locking takes the exclusive sign before you think: PostgreSQL SELECT ... FOR UPDATE. SQLite is coarser: a writer often locks the whole database file (BEGIN IMMEDIATE takes the write lock up front instead of waiting until the first write).

If Ada locks row 1 then wants row 2, and Grace locks row 2 then wants row 1, they wait forever. That is a deadlock. The database aborts one transaction. You prevent it by locking rows in the same order every time—always wallet 1 before wallet 2.

The same bug on a last seat

Grace decrements with seats_left = seats_left - 1. Ada still believes there is one seat, so she SET seats_left = 0 and inserts herself. Two learners, one workshop, zero seats. The seat number looks fine. The registrations table tells the truth.

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  seats_left INTEGER NOT NULL
);

CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL,
  learner TEXT NOT NULL
);

INSERT INTO workshops VALUES (10, 'Isolation in practice', 1);

-- Grace takes the last seat the safe way.
UPDATE workshops
SET seats_left = seats_left - 1
WHERE workshop_id = 10 AND seats_left >= 1;
INSERT INTO registrations (workshop_id, learner) VALUES (10, 'Grace');

-- Ada still believes seats_left is 1, so she writes 0 and registers anyway.
UPDATE workshops SET seats_left = 0 WHERE workshop_id = 10;
INSERT INTO registrations (workshop_id, learner) VALUES (10, 'Ada');

SELECT w.seats_left, r.learner
FROM workshops AS w
JOIN registrations AS r ON r.workshop_id = w.workshop_id
ORDER BY r.registration_id;

Edit the query, predict the rows it will return, then run it.

Fix it the way the transfer lesson did: decrement only when a seat exists, and insert only when that update applied. Ada's booking then matches zero rows.

CREATE TABLE workshops (
  workshop_id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  seats_left INTEGER NOT NULL
);

CREATE TABLE registrations (
  registration_id INTEGER PRIMARY KEY,
  workshop_id INTEGER NOT NULL,
  learner TEXT NOT NULL
);

INSERT INTO workshops VALUES (10, 'Isolation in practice', 1);

BEGIN;
UPDATE workshops
SET seats_left = seats_left - 1
WHERE workshop_id = 10 AND seats_left >= 1;
CREATE TEMP TABLE grace_ok AS SELECT changes() AS applied;
INSERT INTO registrations (workshop_id, learner)
SELECT 10, 'Grace'
WHERE (SELECT applied FROM grace_ok) = 1;
COMMIT;

BEGIN;
UPDATE workshops
SET seats_left = seats_left - 1
WHERE workshop_id = 10 AND seats_left >= 1;
CREATE TEMP TABLE ada_ok AS SELECT changes() AS applied;
INSERT INTO registrations (workshop_id, learner)
SELECT 10, 'Ada'
WHERE (SELECT applied FROM ada_ok) = 1;
COMMIT;

SELECT w.seats_left, r.learner
FROM workshops AS w
LEFT JOIN registrations AS r ON r.workshop_id = w.workshop_id
ORDER BY r.registration_id;

Edit the query, predict the rows it will return, then run it.

Two correct decisions can still empty a rule

A clinic needs at least one doctor on call. Ada and Grace each see two on call, so each thinks they may leave. If both updates use the same old count of 2, both leave. Each decision was locally fine. Together they break the rule. That pattern is called write skew.

CREATE TABLE on_call (
  doctor TEXT PRIMARY KEY,
  is_on INTEGER NOT NULL CHECK (is_on IN (0, 1))
);

INSERT INTO on_call VALUES ('Ada', 1), ('Grace', 1);

CREATE TEMP TABLE count_before AS
SELECT COUNT(*) AS n FROM on_call WHERE is_on = 1;

UPDATE on_call SET is_on = 0
WHERE doctor = 'Ada' AND (SELECT n FROM count_before) >= 2;

UPDATE on_call SET is_on = 0
WHERE doctor = 'Grace' AND (SELECT n FROM count_before) >= 2;

SELECT doctor, is_on
FROM on_call
ORDER BY doctor;

Edit the query, predict the rows it will return, then run it.

Count again before the second leave. After Ada leaves, the live count is 1, so Grace's WHERE fails. SERIALIZABLE isolation on a real server is trying to catch this class of bug without you writing the second count by hand—often by aborting one transaction so the application retries.

CREATE TABLE on_call (
  doctor TEXT PRIMARY KEY,
  is_on INTEGER NOT NULL CHECK (is_on IN (0, 1))
);

INSERT INTO on_call VALUES ('Ada', 1), ('Grace', 1);

CREATE TEMP TABLE count_ada AS
SELECT COUNT(*) AS n FROM on_call WHERE is_on = 1;
UPDATE on_call SET is_on = 0
WHERE doctor = 'Ada' AND (SELECT n FROM count_ada) >= 2;

CREATE TEMP TABLE count_grace AS
SELECT COUNT(*) AS n FROM on_call WHERE is_on = 1;
UPDATE on_call SET is_on = 0
WHERE doctor = 'Grace' AND (SELECT n FROM count_grace) >= 2;

SELECT doctor, is_on,
       (SELECT COUNT(*) FROM on_call WHERE is_on = 1) AS on_call_count
FROM on_call
ORDER BY doctor;

Edit the query, predict the rows it will return, then run it.

A concurrency checklist

1. Name the row (or count) two people might change at once.
2. Prefer SET col = col + n over writing a total you computed after a SELECT.
3. If the new value needs thinking time, add a version (or another predicate) and treat changes() = 0 as “retry.”
4. For a scarce resource (seats, stock), put the rule in the UPDATE ... WHERE, not only in the application.
5. If you lock, lock in a fixed order and keep the transaction short.
6. If the database returns a serialization or deadlock error, retry the whole unit of work. Do not retry a single statement blindly.

Independent lab: two checkouts, one version

Stock starts at 3, version 1. The first checkout takes 2 and bumps the version. The second checkout still uses version 1. Predict stock 1 and version 2, not stock 1 from two successful takes (which would need 3 − 2 − 2). Run it. Then change the second WHERE to version = 2 and confirm both takes apply (stock 0, version 3) only when the second client has re-read.

CREATE TABLE products (
  sku TEXT PRIMARY KEY,
  name TEXT NOT NULL,
  stock INTEGER NOT NULL CHECK (stock >= 0),
  version INTEGER NOT NULL
);

INSERT INTO products VALUES ('NB-1', 'SQL notebook', 3, 1);

-- Checkout A: take 2 using the version that was read.
UPDATE products
SET stock = stock - 2,
    version = version + 1
WHERE sku = 'NB-1' AND version = 1 AND stock >= 2;

-- Checkout B: still thinks version is 1 and stock is 3.
UPDATE products
SET stock = stock - 2,
    version = version + 1
WHERE sku = 'NB-1' AND version = 1 AND stock >= 2;

SELECT sku, name, stock, version
FROM products;

Edit the query, predict the rows it will return, then run it.

Common mistakes to avoid

• Reading a number, adding in the app, writing the total back.
• Treating “the UPDATE did not error” as “my WHERE matched a row.”
• Holding BEGIN open across a user prompt or HTTP call.
• Locking rows in different orders in different code paths.
• Catching a deadlock and continuing as if the first statement had committed.
• Assuming SQLite's one-writer file lock is the same as PostgreSQL row locks.
• Raising isolation to SERIALIZABLE and never handling retry errors.

Lesson review

• I can explain a lost update in one example with numbers.
• I can prefer col = col + n over a stale absolute SET.
• I can use a version column and interpret changes() = 0.
• I can describe dirty reads, non-repeatable reads, and phantoms in plain words.
• I can say what a lock is for and how a deadlock starts.
• I can guard a last seat or last item in the UPDATE itself.