sdlc.dev

SQL and PostgreSQL path · Many writers at once · Topic 8 of 13

Transactions and isolation levels

Not started

A transaction groups statements so that they all take effect or none do (BEGIN ... COMMIT, or ROLLBACK to undo). That is atomicity, and it is the easy part. The harder question is what a transaction sees while others run at the same time. That is the isolation level, and PostgreSQL offers three that behave differently: read committed (the default), repeatable read and serializable.

Why it matters: Most concurrency bugs in database code are anomalies the chosen isolation level permits; knowing which ones lets you pick a level or a lock on purpose.

  1. 1Read committed is the default

    Each statement sees data committed before that statement began, so two reads in one transaction can disagree.

  2. 2Repeatable read is a snapshot

    The whole transaction sees one snapshot, and an update of a row someone else changed meanwhile fails with a serialization error.

  3. 3Serializable detects the rest

    PostgreSQL's serializable level aborts transactions whose combined effect could not have happened one at a time, including write skew.

  4. 4Retries are part of the deal

    Under repeatable read and serializable, be ready to retry the whole transaction on SQLSTATE 40001.

1

How it works

The explanation, with examples you can run.

The anomalies

  • Dirty read: seeing another transaction's uncommitted changes. Never happens in PostgreSQL.
  • Non-repeatable read: reading a row twice and getting different values, because another transaction committed a change in between.
  • Phantom read: running the same query twice and getting a different set of rows.
  • Lost update: two transactions read a value, both write a new value based on it, and one write silently replaces the other.
  • Write skew / serialization anomaly: each transaction is fine alone, but together they break a rule neither could see being broken.

Read committed

Each statement gets a fresh snapshot. If an UPDATE finds a row that another transaction is changing, it waits; when that commits, it re-evaluates its condition against the new version and applies the change to it. So SET balance_cents = balance_cents - 100 is safe: both decrements land. The lost update happens when the new value is computed in the application from an earlier read:

-- Transaction A (read committed)BEGIN;SELECT balance_cents FROM accounts WHERE id = 1;   -- 10000-- ... meanwhile B commits: UPDATE accounts SET balance_cents = balance_cents - 500 WHERE id = 1;UPDATE accounts SET balance_cents = 9900 WHERE id = 1;  -- computed from the stale 10000COMMIT;-- final balance: 9900, B's -500 is lost

Fixes: do the arithmetic in SQL, lock the row when reading it with SELECT ... FOR UPDATE, or use a stronger level.

Repeatable read

The transaction sees one snapshot, taken at its first statement. Running the same lost-update script under BEGIN ISOLATION LEVEL REPEATABLE READ, A's update no longer overwrites B:

ERROR:  could not serialize access due to concurrent update

The application must retry the transaction from the start, reading the new balance. Repeatable read in PostgreSQL also prevents phantoms. What it does not prevent is write skew. In the on-call example, both transactions count two doctors on duty and each takes a different doctor off; there is no conflicting write, so both commit and nobody is on call.

Serializable

Serializable uses Serializable Snapshot Isolation: transactions run on snapshots as in repeatable read, while PostgreSQL tracks which transactions read data that others wrote. If the pattern could produce a result no serial order could, one transaction is aborted. The on-call example:

ERROR:  could not serialize access due to read/write dependencies among transactionsDETAIL:  Reason code: Canceled on identification as a pivot, during commit attempt.HINT:  The transaction might succeed if retried.

All serialization failures use SQLSTATE 40001. Code that uses repeatable read or serializable needs a retry loop around the whole transaction, including the reads, and should keep transactions short, because the longer they run the more they conflict. Declaring read-only transactions as READ ONLY helps the serializable checks.

The next topic shows the machinery that makes all of this possible without readers blocking writers: row versions.

2

Check yourself

3 questions. Get them all right to finish this topic.

  1. 1.Under read committed, transaction A reads a balance of 10000, computes 9900 in the application and writes SET balance = 9900. Meanwhile B runs SET balance = balance - 500 and commits. What is the final balance?
  2. 2.Two doctors are on call. Each transaction checks that at least two are on duty, then takes a different doctor off. Under repeatable read, what happens?
  3. 3.Which isolation levels does PostgreSQL actually implement differently?

0 of 3 right so far. Get all three to finish this topic.

Go deeper