Serializable and Predicate Locks

Concrete problem

A hospital rule says: at least one doctor must stay on call. The table doctors has two rows. Alice and Bob both have on_call = true.

Both doctors feel ill. Each doctor runs this transaction at the same moment:

BEGIN;
SELECT count(*) FROM doctors WHERE on_call;   -- returns 2, so it is safe to leave
UPDATE doctors SET on_call = false WHERE name = '<me>';
COMMIT;

Both transactions commit. Now zero doctors are on call. The rule is broken.

Write-Write Conflict and Row Lock does not help. Alice updates her own row. Bob updates his own row. No two writers touch the same row. No writer waits. Each transaction made a correct decision from its own snapshot. The two decisions together are wrong.

How can the database stop this without a lock on the rows?

Background terms

Isolation level: The isolation level of a transaction says how much the transaction sees of other transactions that run at the same time. Postgres has three levels that differ in behavior: READ COMMITTED (the default), REPEATABLE READ, and SERIALIZABLE. The first two show a snapshot of committed data. SERIALIZABLE adds a check on top of the snapshot.
Serializable: A set of transactions is serializable when the result equals the result of running them one at a time, in some order. Any order is acceptable. The database does not have to run them one at a time. It only has to give a result that one order could have given.
Write skew: Write skew is a wrong result that two transactions cause together. Each transaction reads some rows. Each transaction then writes a row that the other transaction read. The two writes touch different rows, so no write-write conflict occurs. The doctors example is write skew.
Read-write dependency: A read-write dependency from transaction $T_{1}$ to transaction $T_{2}$ exists when $T_{1}$ read data and $T_{2}$ wrote new data that $T_{1}$ did not see. $T_{1}$ used a snapshot that does not hold the write of $T_{2}$ . So in any one-at-a-time order, $T_{1}$ must run before $T_{2}$ . We write $T_{1} \to T_{2}$ .
SIREAD lock: An SIREAD lock is a record that says “this transaction read this data”. Postgres uses it only to find read-write dependencies. An SIREAD lock never blocks anyone. The name means “serializable read”.
Predicate lock: A predicate lock covers every row that matches a condition. It also covers rows that do not exist yet. An SIREAD lock is a predicate lock. This matters for a query that finds no rows. The query still read “the set of rows with this condition”, and a later INSERT can change that set.
Serialization failure: A serialization failure is an error with SQLSTATE 40001. Postgres raises it when it aborts a transaction to keep the result serializable. The transaction did nothing wrong by itself. The application must run the whole transaction again.

Intuition

The goal is a result that one order could give. So we ask: is there a valid one-at-a-time order for Alice and Bob?

Alice read Bob’s row. Bob then wrote Bob’s row. Alice did not see the write. So Alice must come before Bob: $A \to B$ .
Bob read Alice’s row. Alice then wrote Alice’s row. Bob did not see the write. So Bob must come before Alice: $B \to A$ .
Alice before Bob and Bob before Alice has no answer. This is a cycle.

A cycle of read-write dependencies means that no valid order exists. Then Postgres must abort one transaction. The other transaction commits. If Bob aborts and runs again, he sees that Alice is off call. He sees a count of $1$ and stays on call. The rule holds.

How does Postgres find the cycle without blocking anyone? It records and checks:

A transaction that reads takes an SIREAD lock on what it read. It does not wait for anything.
A transaction that writes looks for SIREAD locks on the data it writes. A match means a read-write dependency exists. Postgres notes the dependency. The writer does not wait.
Postgres looks for a dangerous pattern: transactions $T_{1} \to T_{2} \to T_{3}$ in a row, where $T_{3}$ commits first. The middle transaction $T_{2}$ is the pivot. Every cycle contains this pattern. When Postgres finds it, it aborts one transaction with 40001.

Postgres checks a pattern, not the full cycle. So it sometimes aborts a transaction when no real cycle exists. This is a false positive. The cost is a retry. A false negative cannot happen.

Compare this with N1 to N6. There, a lock made a transaction wait. A conflict was prevented by ordering. Here, no transaction waits for an SIREAD lock. Transactions run freely. Postgres detects a bad result and aborts one transaction. This is an optimistic method. It costs less when conflicts are rare. It costs a retry when they are not.

Ordinary row locks still exist at SERIALIZABLE. Two UPDATEs of one row still wait for each other. Only the SIREAD locks never wait.

Where do SIREAD locks live? In the shared memory lock table, as in Where Locks Live. pg_locks shows them with mode = 'SIReadLock'. They differ from other locks in one way: Postgres keeps an SIREAD lock after the transaction commits. A later writer can still form a dependency with a committed reader, as long as another transaction that overlapped with the reader still runs. Postgres removes the lock after all of those transactions end.

SIREAD locks start small and grow coarse. Postgres locks a row (tuple) first. When one transaction holds many tuple locks on a page, Postgres merges them into one page lock. Many page locks merge into one relation lock. This promotion saves memory in the lock table. A coarse lock covers more data than the transaction read. So promotion causes more false positives.

Numeric example

Table doctors: Alice true, Bob true. Both transactions use BEGIN ISOLATION LEVEL SERIALIZABLE.

Step	Alice’s transaction $T_{A}$	Bob’s transaction $T_{B}$	What Postgres records
1	`SELECT count(*) ... WHERE on_call` returns 2		$T_{A}$ takes an SIREAD lock on both rows.
2		`SELECT count(*) ... WHERE on_call` returns 2	$T_{B}$ takes an SIREAD lock on both rows.
3	`UPDATE ... WHERE name = 'Alice'`		$T_{A}$ writes a row that $T_{B}$ read. Dependency $T_{B} \to T_{A}$ .
4		`UPDATE ... WHERE name = 'Bob'`	$T_{B}$ writes a row that $T_{A}$ read. Dependency $T_{A} \to T_{B}$ . Now a cycle exists.
5	`COMMIT` succeeds
6		`COMMIT` fails with `40001` (or an earlier statement fails)	Postgres aborts $T_{B}$ .
7		Application runs $T_{B}$ again. `count(*)` returns 1. The rule stops the update.

At REPEATABLE READ, steps 1 to 6 all succeed. No SIREAD locks exist. The final result is zero doctors on call.

Phantom case. Two transactions book room 1 at 10:00. Each runs SELECT ... WHERE room = 1 AND slot = '10:00'. Each query finds no row. Each transaction then inserts its own booking. No row existed to lock, so FOR UPDATE would lock nothing. The predicate lock covers “room 1, slot 10:00” even though no row matches. Each INSERT hits the other transaction’s predicate lock. A cycle forms. One transaction aborts.

Visual

graph LR
  subgraph Locks["N1 to N6: lock makes a transaction wait"]
    W1["Writer 1 holds row lock"] -->|"Writer 2 waits"| W2["Writer 2 blocked"]
  end
  subgraph Ssi["N7: SIREAD lock only records a read"]
    TA["T_A: read both rows, write Alice"]
    TB["T_B: read both rows, write Bob"]
    TA -->|"read-write dependency"| TB
    TB -->|"read-write dependency"| TA
    TB --> X["Cycle: abort one with 40001"]
  end

graph TD
  A["Read by a transaction"] --> B["SIREAD lock on a tuple"]
  B -->|"many tuples on one page"| C["SIREAD lock on a page"]
  C -->|"many pages of one table"| D["SIREAD lock on the relation"]
  D --> E["Covers more than was read: more false positives"]

Decision chart: what happens to two overlapping transactions?

Start at the top. Answer each question. The box at the end gives the result.

flowchart TD
  S["Two transactions overlap"] --> Q1{"Do both WRITE the same row?"}
  Q1 -->|"Yes"| W["The second writer WAITS for the first to end"]
  W --> C{"The first one commits. What is the isolation level of the second writer?"}
  C -->|"READ COMMITTED"| RC["Takes a new snapshot. Updates the new version. No error."]
  C -->|"REPEATABLE READ or SERIALIZABLE"| E1["Snapshot is old. Fails with 40001."]
  Q1 -->|"No"| Q2{"Does a transaction WRITE a row that the other one READ?"}
  Q2 -->|"No"| OK0["No link. Both commit."]
  Q2 -->|"Yes, one direction only"| OK1["One dependency. A valid order exists. Both commit. Nobody waits."]
  Q2 -->|"Yes, in both directions"| Q3{"What is the isolation level?"}
  Q3 -->|"SERIALIZABLE"| E2["Cycle. No valid order. One fails with 40001."]
  Q3 -->|"REPEATABLE READ or lower"| BAD["Both commit. Write skew. The data is wrong."]

Three facts to keep:

Fact	Meaning
Only a writer of the same row makes you wait	A read never makes a writer wait.
One arrow is safe	$T_{1} \to T_{2}$ means “run $T_{1}$ first”.
Two opposite arrows are a cycle	No order works. One transaction fails.

Notation

Start a serializable transaction:

BEGIN ISOLATION LEVEL SERIALIZABLE;

A read-only transaction that never fails with 40001:

BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;

DEFERRABLE makes the transaction wait at its start until Postgres can give it a snapshot that no cycle can involve. After that, it takes no SIREAD locks and cannot fail.

See SIREAD locks:

SELECT locktype, relation::regclass, page, tuple, pid
FROM pg_locks
WHERE mode = 'SIReadLock';

The locktype column shows tuple, page, or relation. This shows the granularity.

The error to catch: SQLSTATE 40001, message could not serialize access due to read/write dependencies among transactions.

Plain-English translation

" $T_{1} \to T_{2}$ " means ” $T_{1}$ read something that $T_{2}$ then changed. $T_{1}$ must come first in any one-at-a-time order.”
“A cycle” means “no valid one-at-a-time order exists”.
“Pivot” means “the middle transaction of a chain of two dependencies”.
“Take an SIREAD lock” means “write down what I read. Do not stop anyone.”
“Serialization failure” means “run the whole transaction again”.

What changes when parameters change

Lower isolation level. At REPEATABLE READ, write skew is possible. Postgres takes no SIREAD locks. Write skew does not raise an error. The data is wrong.
Writers touch the same row. An ordinary row lock applies. The second writer waits, as in Write-Write Conflict and Row Lock. After the first writer commits, the second writer gets a serialization failure in place of a silent re-read.
Many tuples read. SIREAD locks promote to page locks and then to a relation lock. The lock table stays small. False positives rise. The server settings max_pred_locks_per_transaction, max_pred_locks_per_relation, and max_pred_locks_per_page control the promotion thresholds.
A long transaction. It keeps its SIREAD locks, and the locks of committed overlapping readers, for a long time. More dependencies form. More aborts occur. Keep serializable transactions short.
A mix of levels. The check works only among SERIALIZABLE transactions. A REPEATABLE READ transaction takes no SIREAD locks and can still cause write skew.
A read-only transaction. It can fail with 40001 when it is the start of a dependency chain. DEFERRABLE removes this risk and costs a wait at the start.
No retry code. The application sees random 40001 errors. Retry code is part of the feature, not an extra.

Practice problem

Try these on a later review. Do not open the answers first.

Two bank accounts $A$ and $B$ each hold 50. The rule is: the sum must stay at or above 0, so each account alone may go to $- 40$ . Transaction $T_{1}$ reads both balances, then withdraws 90 from $A$ . Transaction $T_{2}$ reads both balances, then withdraws 90 from $B$ . They overlap. What is the final sum at REPEATABLE READ? What happens at SERIALIZABLE?
A report transaction only runs SELECT. The team sees it fail with 40001 once a week. Why can a read-only transaction fail? Which syntax stops it?
Transaction $T$ commits at SERIALIZABLE. You run the pg_locks query for SIReadLock and still see locks that belong to $T$ . Is this a bug?
Your service sets SERIALIZABLE for every transaction. The error rate for 40001 rises when a query scans a whole large table. Why? Give one fix at the application level and one at the schema level.

Answers

At REPEATABLE READ, both withdrawals succeed. The balances are $- 40$ and $- 40$ , so the sum is $- 80$ . The rule is broken. At SERIALIZABLE, each transaction read a row that the other wrote. This is a cycle. Postgres aborts one with 40001. The application runs it again. The retry reads the new balances and the rule check stops it.

A read-only transaction can be the first link of a chain $T_{r o} \to T_{2} \to T_{3}$ . If $T_{3}$ commits before $T_{r o}$ takes its snapshot, the snapshot is not consistent with any order. Postgres aborts. BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE stops the failure. It waits at the start for a safe snapshot.

This is not a bug. Postgres keeps the SIREAD locks of a committed transaction until all transactions that overlapped with it have ended. A later writer can still form a dependency with $T$ .

A whole-table scan reads many tuples. The SIREAD locks promote to a relation lock. Any write to the table now hits this lock and forms a dependency. False positives rise. Application fix: do the scan in a READ ONLY DEFERRABLE transaction, or run it in a lower isolation level if it needs no guarantee. Schema fix: add an index so the query reads fewer tuples and takes finer locks.

Quiz

Asked on 2026-10-05 after the lesson. Two answers were correct and one was a miss. A re-check followed and was also a miss.

Quiz (2026-10-05)

Q1. Under SERIALIZABLE, T1 holds an SIREAD lock on row X. T2 runs an UPDATE on row X. No other conflict exists. What happens?

T2 waits until T1 ends

T2 fails at once with SQLSTATE 55P03

T2 runs without waiting

T2 fails at once with SQLSTATE 40001

Answer and reasoning

Your answer: T2 fails at once with SQLSTATE 40001 (miss). Correct answer: T2 runs without waiting.

Why: an SIREAD lock only records a read. T2 writes, and Postgres notes one read-write dependency. One dependency is not an error. Postgres aborts only when a dangerous pattern of two dependencies forms. The first option tests the idea that SIREAD locks block like row locks. The second option tests the idea that a conflict gives a lock error. The fourth option tests the idea that one dependency is already a failure.

Q2. Under SERIALIZABLE, each transaction finds its rows by primary key. Which pair of overlapping transactions can end with a 40001 error?

T1 reads row 1 and updates row 1. T2 reads row 2 and updates row 2.

T1 only reads rows 1 and 2. T2 only reads rows 1 and 2.

T1 updates row 1 without a read. T2 updates row 2 without a read.

T1 reads row 1 and updates row 2. T2 reads row 2 and updates row 1.

Answer and reasoning

Your answer: T1 reads row 1 and updates row 2. T2 reads row 2 and updates row 1 (correct). Correct answer: the same.

Why: each transaction writes the row that the other read. This makes two dependencies in a cycle. The first option has no shared rows, so no dependency forms. The second option has no writes. The third option has writes but no reads, so no SIREAD lock exists to hit.

Q3. A serializable transaction fails with SQLSTATE 40001. What must the application do?

Run only the failed statement again

Run the whole transaction again from BEGIN

Continue the transaction and commit

Switch to REPEATABLE READ and run it again

Answer and reasoning

Your answer: Run the whole transaction again from BEGIN (correct). Correct answer: the same.

Why: the transaction is aborted. Its decisions came from an old snapshot. A retry takes a new snapshot and can decide again. The first option assumes a statement can be repeated inside an aborted transaction. The third option treats the error as a warning. The fourth option removes the check, so write skew returns without an error.

Re-check (2026-10-05)

Re-check. Under SERIALIZABLE, T1 updated row X and has not committed. T2 now updates row X. Then T1 commits. What does T2 see?

T2 fails at once, before T1 commits

T2 waits, then fails with 40001 after T1 commits

T2 waits, then updates the new row version

T2 does not wait and both updates stay

Answer and reasoning

Your answer: T2 waits, then updates the new row version (miss). Correct answer: T2 waits, then fails with 40001 after T1 commits.

Why: the row lock makes T2 wait, as in Write-Write Conflict and Row Lock. After T1 commits, T2 holds a snapshot that does not contain the new version. At REPEATABLE READ and SERIALIZABLE, T2 cannot build on a version it cannot see. So it fails with 40001. The third option is the READ COMMITTED rule: T2 re-reads the newest version and goes on. The first option tests the idea that SIREAD locks cause an early error. The fourth option tests the old idea that writers do not wait.

Review quiz (2026-10-06)

R-Q1. Under REPEATABLE READ, T1 updates row X and has not committed. T2 then updates row X. Then T1 commits. What happens to T2?

T2 waits, then updates the new row version

T2 waits, then fails with 40001 after T1 commits

T2 does not wait and both updates stay

T2 fails at once, before T1 commits

Answer and reasoning

Your answer: T2 waits, then updates the new row version (miss). Correct answer: T2 waits, then fails with 40001 after T1 commits.

Why: the snapshot of T2 is fixed. It does not contain the new version. An update on top of it would erase a change that T2 never saw. The first option is the READ COMMITTED rule again. The third option tests the idea that writers do not wait. The fourth option tests the idea of an early error.

R-Q2. Under SERIALIZABLE, T1 reads row 1 only. T2 updates row 1 and commits. T1 then updates row 3, which no other transaction reads. What happens?

T1 fails with 40001 because T1 read a row that T2 changed

T2 waits for T1 to end

Both transactions commit

T2 fails with 40001 at its commit

Answer and reasoning

Your answer: T1 fails with 40001 because T1 read a row that T2 changed (miss). Correct answer: Both transactions commit.

Why: one dependency $T_{1} \to T_{2}$ allows the order ” $T_{1}$ , then $T_{2}$ ”. No cycle exists. The first option tests the idea that one dependency is an error. The second option tests the idea that a read blocks a writer. The fourth option tests the idea that the writer pays for a dependency.

R-Q3. Under SERIALIZABLE, three transactions overlap. T1 reads row 1 and updates row 2. T2 reads row 2 and updates row 3. T3 reads row 3 and updates row 1. No two transactions update the same row. What happens?

All three commit because no two update the same row

The three transactions wait for each other and deadlock

All three fail with 40001

At least one fails with 40001

Answer and reasoning

Your answer: At least one fails with 40001 (correct). Correct answer: the same.

Why: the dependencies $T_{3} \to T_{1} \to T_{2} \to T_{3}$ form a cycle. Postgres aborts enough transactions to break it, often one. The first option ignores read-write dependencies. The second option confuses a dependency with a wait. The third option assumes that all members of the cycle fail.

Re-checks (2026-10-06)

Re-check 1. Under REPEATABLE READ, T2 runs its first SELECT. T1 then updates row X and commits. Then T2 updates row X. What happens?

T2 fails with 40001

T2 waits for a lock, then updates the new version

T2 updates the new version without waiting

T2 updates its old version and both versions stay

Answer and reasoning

Your answer: T2 fails with 40001 (correct). Correct answer: the same.

Why: the fixed snapshot of T2 does not contain the version that T1 wrote. The second and third options are the READ COMMITTED rule. The fourth option tests the idea that writers do not wait or conflict.

Re-check 2. Under SERIALIZABLE, T1 reads row 1 and updates row 2. T2 reads row 3 and updates row 1. No other transaction touches these rows. They overlap. What happens?

T2 waits for T1 to end

Both commit

One fails with 40001

Both wait for each other and deadlock

Answer and reasoning

Your answer: T2 waits for T1 to end (miss). Correct answer: Both commit.

Why: T1 only read row 1. T2 writes row 1. An SIREAD lock never makes a writer wait. The only edge is $T_{1} \to T_{2}$ . No cycle exists. The first option tests the idea that a read blocks a writer. The third option tests the idea that one dependency is an error. The fourth option confuses a dependency with a wait.

Re-check 3. Under SERIALIZABLE, T1 runs SELECT count(*) FROM orders and has not ended. T2 then runs INSERT INTO orders. No other transaction exists. What happens at the INSERT?

T2 fails with 40001 at once

T2 waits until T1 ends

T2 runs without waiting

T2 waits, then fails with 40001 after T1 ends

Answer and reasoning

Your answer: T2 runs without waiting (correct). Correct answer: the same.

Why: the count query took a predicate SIREAD lock on the table. The insert hits it and adds one dependency. One dependency is no error and no wait. The first option tests the idea that one dependency is an error. The second and fourth options test the idea that a read blocks a writer.

Correction after the 2026-10-06 review

The review missed the same two gaps again. Each gap has one test question.

Gap 1: can a writer build on a version it cannot see?

A snapshot is the set of versions that a transaction can see.
At READ COMMITTED, each statement takes a new snapshot. After $T_{1}$ commits, $T_{2}$ sees the new version. So $T_{2}$ updates it.
At REPEATABLE READ and SERIALIZABLE, the snapshot is fixed at the first statement. The new version is not in it.
An update of that row would erase a change that $T_{2}$ never saw. This is a lost update. Postgres refuses and fails with 40001.

Test question: “Does the transaction’s snapshot contain the newest version of the row?” If no, and the transaction wants to write it, the result is 40001.

Gap 2: one dependency is not an error.

A read-write dependency $T_{1} \to T_{2}$ says only: ” $T_{1}$ must come before $T_{2}$ ”.
One such order is valid. Run $T_{1}$ , then $T_{2}$ . The result is the same.
An error is needed only when no order exists. That needs a cycle. Two opposite dependencies make a cycle.

Test question: “Can I write the transactions in one order that gives the same result?” If yes, no error.

Gap 3: a read never makes a writer wait.

An SIREAD lock is a note: “this transaction read this”. It is not a row lock.
A writer waits only for another writer of the same row (or for a table lock that conflicts).
A reader that holds an SIREAD lock never makes anyone wait. At most it adds one dependency. See Gap 2.

Test question: “Is the other transaction a writer of this row?” If it only read the row, nobody waits.

My reasoning

The Q1 miss came from mixing two sources of 40001. The user named it: the write-write case. The re-check showed a narrow gap inside that case. The user applied the READ COMMITTED rule (re-read the newest version) to SERIALIZABLE. Rules to keep:

Same row, second writer: waits. At READ COMMITTED it then continues. At REPEATABLE READ and SERIALIZABLE it fails with 40001.
Different rows, read-write cycle: no wait. One transaction fails with 40001. Only SERIALIZABLE checks this.
One dependency alone is never an error.

Session history

2026-10-05 — First taught. See 2026-10-05 Databases - Postgres Locking.
2026-10-05 — Quiz: 2 of 3, plus a missed re-check. Q1 miss: mixed two sources of 40001. Re-check miss: applied the READ COMMITTED same-row rule to SERIALIZABLE. Level 1. Revisit. See 2026-10-05 Databases - Postgres Locking.
2026-10-06 — Review. Misses on the same-row case at REPEATABLE READ, on one dependency, and on a read that blocks a writer. Correct on the cycle of three and on the re-checks for gaps 1 and 3. Level stays 1. Revisit. See 2026-10-06 Databases - Serializable Review.

Ohara

Explorer