How Does a Database Keep Transactions Out of Each Other's Way?
Isolation levels in PostgreSQL, told as one story. Why Read Committed, Repeatable Read and Serializable are really one rule about when your snapshot is taken; the anomalies each level allows; what actually happens when two transactions write the same row; and why one level quietly re-checks while the other says "start over".
Two posts ago, in How Does a Database Let Everyone Read and Write at Once?, we saw the trick behind MVCC: Postgres never overwrites a row, it keeps old versions around, and every transaction carries a tiny snapshot that decides which version it's allowed to see. That post left two threads hanging. It said the snapshot is taken "the instant your transaction begins" — which is a simplification, and the exact truth is the whole subject here. And it said that two transactions writing the same row "still have to take turns", without ever saying what taking turns means.
This post picks up both. You don't need to have read the earlier one — everything it needs is rebuilt below in a paragraph — but the ideas do stack, so if xmin and xmax are new to you, it's the friendlier place to start.
Here's the plan. First the problem, and the one rule that separates the three isolation levels (it's about when the snapshot is taken). Then each level, one at a time, looking only at reads. Then the second half: what happens when two transactions write the same row — and why one level quietly fixes things up while the other stops you with an error.
The problem: two people, one row
Picture a small online shop. Product 42 is a lamp, and there are 5 in stock. Right now two things are happening at once:
- T1 is a report that reads the stock of product 42.
- T2 is a customer buying one — it changes the stock from 5 to 4 and commits.
Somebody has to decide what T1 sees. There's a whole range of possible answers:
- Fastest: let T1 see whatever the latest value is, even if it changes halfway through the report.
- Safest: pretend T1 and T2 ran one after the other, never at the same time.
An isolation level is your choice of where to sit on that range. One sentence to hold on to:
An isolation level decides how much your transaction is allowed to ignore the other transactions running at the same time.
Higher isolation means fewer strange results. It also means that, more often, Postgres will stop a transaction with an error that says "start over", and your code has to handle it. That's the trade. Everything below is the mechanics.
A quick recap: what a snapshot is
Postgres never edits a row in place. An UPDATE writes a new version of the row and leaves the old one on disk, so one row can have several physical versions at once. Each version carries two hidden numbers: xmin, the ID of the transaction that created it, and xmax, the ID of the transaction that replaced or deleted it (0 if nobody has yet).
A snapshot is a note of which transactions had already committed at one particular moment. When your query looks at a row, it walks the versions and keeps the one whose creator had committed by that moment and whose deleter had not. Exactly one version passes; that's the one you see. (The MVCC post works through this rule with real numbers, if you want the long version.)
Two facts fall straight out of that rule, and they're true at every isolation level:
- A plain read never waits. If a version's
xmaxbelongs to a transaction that's still running, that deletion simply hasn't happened yet from your snapshot's point of view. You read the old version and move on. Nobody waits for a reader, and a reader waits for nobody. - You can never see another transaction's uncommitted data. A version written by a still-running transaction has an
xminthat hasn't committed, so it fails the test. Reading someone's half-finished work has a name — a dirty read — and Postgres makes it impossible at every level. You get this for free and never think about it again.
So the snapshot is not what changes between levels. What changes is when you take it.
The one rule that separates the levels
Every isolation level uses snapshots. The difference between them is almost entirely how often a new snapshot is taken:
| Level | When is the snapshot taken? |
|---|---|
| Read Committed (default) | A fresh one before every statement |
| Repeatable Read | Once, at the first statement, then reused for the whole transaction |
| Serializable | Same as Repeatable Read — once — plus a referee (more on that below) |
Two details trip people up, so let's settle them now.
BEGIN does not take a snapshot. Not at any level. The snapshot is taken by the first statement that reads data. This is easy to see for yourself. Start a Repeatable Read transaction, don't run anything, have another session change the stock, and then run your first query:
BEGIN ISOLATION LEVEL REPEATABLE READ;
-- nothing yet. Meanwhile another session runs:
-- UPDATE products SET stock = 4 WHERE id = 42; COMMIT;
SELECT stock FROM products WHERE id = 42; -- 4 ← snapshot taken HERE, so it is visible
-- another session commits stock = 3
SELECT stock FROM products WHERE id = 42; -- 4 ← same snapshot reused; this one is not
The transaction is "frozen" from its first query onward, not from BEGIN onward.
So the level must be set before your first query. Once a snapshot exists, it's too late, and Postgres says so:
BEGIN;
SELECT stock FROM products WHERE id = 42;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- ERROR: SET TRANSACTION ISOLATION LEVEL must be called before any query
If you have never set it anywhere, you are running at Read Committed — that's the default. (Where you would set it — per transaction, per connection, or for the whole server — is a question for the next post.)
One more way to hold the three levels in your head, since we'll use it for the rest of the post:
- Read Committed — you take a fresh look before answering each question. Always recent, but two questions can get two different answers.
- Repeatable Read — you take one look at the start and answer every question from it. Perfectly consistent with itself, possibly out of date.
- Serializable — the same single look, plus a referee watching every transaction and cancelling any one that would produce an impossible result.
Now each level, one at a time. This whole first half is only about reading. Writing has its own rules, and keeping the two apart is the key to not getting confused.
Read Committed: a fresh look before every statement
The promise: every statement sees everything that was committed before that statement started.
Notice it's a promise about each statement, not about the transaction. Fresh snapshot every time. Back to the shop:
BEGIN; -- no snapshot yet
SELECT stock FROM products WHERE id = 42; -- snapshot A taken now → 5
-- meanwhile T2 runs: UPDATE products SET stock = 4 WHERE id = 42; COMMIT;
SELECT stock FROM products WHERE id = 42; -- snapshot B taken now → 4
COMMIT;
Two identical queries, two different answers, inside one transaction. This is not a bug — it's exactly what Read Committed promises. The second statement took a new snapshot, and by then T2 had committed.
Three things can surprise you at this level. They all come from the same cause — a fresh snapshot per statement — and they each have a name worth knowing:
- Non-repeatable read — you read the same row twice and get different values, because someone committed a change in between. That's the 5-then-4 above.
- Phantom read — you run the same
WHEREtwice and get a different set of rows, because someone committed anINSERTorDELETEin between.SELECT count(*) FROM productssays 3, then says 4 — a new lamp appeared. - Read skew — you read row X, someone commits a change that touches both X and Y, then you read row Y. Say the lamps sit in two warehouses: 5 in the first, 0 in the second. You read warehouse 1 (5). A transfer of 2 lamps commits. You read warehouse 2 (2). Your report says 7 lamps. There were never 7 lamps. Each number was correct when it was read; together they describe a moment that never existed.
Read Committed allows all three. In return it's cheap — no bookkeeping — and in ordinary INSERT / UPDATE / DELETE work it effectively never asks you to retry anything.
One small detail worth knowing: one statement is one snapshot, however big the statement. A SELECT with six joins uses a single consistent snapshot from start to finish. "Fresh look every time" happens at statement boundaries, never in the middle of one.
Repeatable Read: one look, and the world stands still
The promise: the whole transaction sees the database exactly as it was at the moment of my first query. Nothing moves.
One snapshot, reused for every statement. The same example, one level up:
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM products WHERE id = 42; -- snapshot taken now → 5
-- meanwhile T2 runs: UPDATE products SET stock = 4 WHERE id = 42; COMMIT;
SELECT stock FROM products WHERE id = 42; -- SAME snapshot → still 5
COMMIT;
-- a new transaction after this sees 4
T2's change is real and committed. It's simply not visible to this transaction. Your transaction lives in the past, consistently.
All three surprises from the last section disappear, and for the same reason: nothing is ever re-read, so nothing can change.
- Non-repeatable read — gone. Same row, same value, every time.
- Phantom read — gone. A row inserted after your snapshot simply doesn't exist for you.
- Read skew — gone. Every row you read comes from the same instant.
"But my textbook says Repeatable Read allows phantoms!"
The level names come from the SQL standard — the written spec that Postgres, MySQL, Oracle and the rest all follow. For each level the standard sets a minimum: a level called Repeatable Read must at least stop non-repeatable reads. It doesn't require stopping phantoms. So a database may call something Repeatable Read even if phantoms still slip through, and that minimum is what textbooks describe.
Postgres goes further than the minimum. Its Repeatable Read is one snapshot for the whole transaction, and a single snapshot can't show rows that didn't exist when it was taken — so phantoms are stopped too, as a free side effect. (What Postgres calls Repeatable Read is what the research papers call snapshot isolation.)
The catch
Living in the past is fine as long as you only read. The moment your frozen transaction tries to write a row that somebody else changed and committed after your snapshot, Postgres has a problem: it can't honour both "the world is frozen for you" and "your write is based on current data". So it gives up:
ERROR: could not serialize access due to concurrent update
Your transaction is dead — every further statement in it fails until you roll back — and your application has to retry it from the top.
How Postgres knows that somebody else changed the row, without ever looking at the new version, is the second half of this post. For now, just hold on to the cost: Repeatable Read means writing retry logic.
Serializable: the same look, plus a referee
The promise: the final result is identical to some order in which these transactions ran one after another, never overlapping.
Serializable does everything Repeatable Read does — same single snapshot, same guarantees — and adds a referee. To see why one is needed, we have to find a problem the snapshot alone can't catch.
The problem it exists for: write skew
Repeatable Read protects each row you touch. It does not protect a rule that spans several rows. Here's the shop again. There are two staff on shift, Alice and Bob, and the rule is at least one person must stay on shift. Both are tired, and both check at the same moment:
T1 (Alice) T2 (Bob)
BEGIN ISOLATION LEVEL REPEATABLE READ; BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM staff
WHERE on_shift; → 2
SELECT count(*) FROM staff
WHERE on_shift; → 2
-- "2 on shift, safe to leave"
UPDATE staff SET on_shift = false
WHERE name = 'alice';
COMMIT;
-- "2 on shift, safe to leave"
UPDATE staff SET on_shift = false
WHERE name = 'bob';
COMMIT;
Step through what Repeatable Read checks. Did T1 and T2 write the same row? No — T1 wrote Alice's row, T2 wrote Bob's. So there's no row conflict, nobody waits, and no error. Both commit happily.
Result: nobody on shift. The rule is broken, and no error was ever raised. (This isn't hypothetical — run it, and the final count is 0.)
Write skew — two transactions each read an overlapping set of rows, each makes a decision based on what it read, then each writes a different row. Each one is consistent on its own. Together they break a rule that spans the rows. Read Committed and Repeatable Read both allow this. Only Serializable catches it.
How the referee works
Postgres implements Serializable as SSI — Serializable Snapshot Isolation. As a Serializable transaction reads rows, Postgres quietly notes "this transaction's behaviour depended on this data". Those notes are called predicate locks (internally, SIREAD locks), and the important thing about them is that they never block anybody. They're pure bookkeeping.
What the bookkeeping is for: Postgres watches for a dangerous pattern between running transactions — roughly, "your decision depended on data I then changed, and my decision depended on data you then changed". When it sees that pattern, it cancels one of them:
ERROR: could not serialize access due to read/write dependencies among transactions
DETAIL: Reason code: Canceled on identification as a pivot, during write.
HINT: The transaction might succeed if retried.
Run the Alice-and-Bob example at Serializable and that's exactly what Bob's UPDATE gets. Alice's change goes through, Bob's transaction is cancelled, and one person stays on shift.
Three things about Serializable that surprise people
1. The error can arrive at COMMIT. Repeatable Read's error always lands on the conflicting write. Serializable's can land after every statement already succeeded, because the dangerous pattern may only become visible when the last transaction tries to commit. If Alice and Bob had both run their UPDATE before either committed, Bob's UPDATE would succeed and his COMMIT would fail instead (Canceled on identification as a pivot, during commit attempt).
2. It can cancel a transaction that only read. A transaction that ran nothing but SELECTs can still be part of the dangerous pattern, and Postgres will cancel it rather than let it commit a view that fits no serial order. It's correct — but it means retry logic is needed even for readers.
3. It only works if everyone is Serializable. The referee reasons about transactions it's tracking. If one session touching the same data runs at Read Committed, Postgres has no notes for it, and the write-skew guarantee is gone. Serializable is an all-or-nothing agreement among the transactions that share data.
The cost, and two ways around it
Those notes take memory. When a transaction holds too many, Postgres swaps them for coarser ones — a whole page instead of a few rows, a whole table instead of a few pages — and coarser notes mean more false alarms: transactions cancelled for conflicts that weren't real. So Serializable gets more expensive and more retry-prone as transactions touch more rows.
For long reports there's an escape hatch:
BEGIN ISOLATION LEVEL SERIALIZABLE READ ONLY DEFERRABLE;
This waits at the start until it can get a snapshot that's guaranteed to be safe, and from then on can never be cancelled. It may wait to begin, but it will never make you retry — ideal for a big read-only report that needs a perfectly consistent view.
And Serializable isn't the only cure for write skew. You can give the rule a physical row for both transactions to fight over — say, one shifts row for today — and have both lock it before they count. Now they collide on the same row, one waits for the other, and the second one sees the real count of 1. Uglier, but it works at Read Committed with no retry loop. That trick uses SELECT … FOR UPDATE, which belongs to the next post.
The three levels side by side
| Surprise | What it is | Read Committed | Repeatable Read | Serializable |
|---|---|---|---|---|
| Dirty read | Seeing uncommitted data | Prevented | Prevented | Prevented |
| Non-repeatable read | Same row read twice, different value | Allowed | Prevented | Prevented |
| Phantom read | Same WHERE run twice, different rows | Allowed | Prevented | Prevented |
| Read skew | Two rows read from two different moments | Allowed | Prevented | Prevented |
| Lost update | Read-then-write in app code silently overwritten | Allowed | Error → retry | Error → retry |
| Write skew | A rule across several rows broken by two single-row writes | Allowed | Allowed | Prevented |
Read across: each level prevents everything the level to its left prevents, plus more. The price of each step to the right is more could not serialize access errors your code has to catch. (The lost update row is the one thing in this table we haven't met yet — it's the subject of the next post.)
That's the first half. Everything so far was about reading, and reading never waits for anything. Now for writing — where most of the confusion lives, because two completely different things happen, and people blur them together.
Now the writes: what "taking turns" actually means
Here is the rule underneath everything, true at every isolation level:
Two transactions can never both hold an uncommitted change to the same row. The second writer has to wait and find out whether the first one commits or aborts.
This isn't a feature of any isolation level. Read Committed obeys it. Serializable obeys it. It's the raw rule the levels sit on top of. So let's look at how a write actually happens.
Every write aims at the head
Recall that one row is really a chain of versions: each version's xmax is the next version's xmin. Exactly one version, at the end of the chain, has xmax = 0 — nobody has replaced it yet. Call that version the head.
A write always aims at the head. Only the head can be updated or deleted; the older versions are history, and history is never edited.
This answers a very natural question: "if two transactions see different versions through their different snapshots, can't each one just update its own version and never collide?" No. Each transaction reads whichever version its snapshot picks. But to write, it has to claim the head, and there's only one head. That's exactly where they collide.
The row version is the lock
There's no separate "row lock" object sitting in memory for every row. The version itself is the lock, through its xmax. When a transaction — say it has ID 502 — runs an UPDATE on product 42, three steps happen:
Step 1 — find the version. 502 walks the versions using its snapshot, exactly like a SELECT would, and picks the one it can see.
Step 2 — look at that version's xmax. This is the check that decides everything. There are four possibilities:
The version's xmax is… | What it means | What 502 does |
|---|---|---|
0 | Nobody has touched it. It's the head. | Claim it: write 502 into xmax. No wait, no error. |
501, and 501 is still running | 501 has claimed it but hasn't decided commit or abort. | Wait for 501. |
501, and 501 aborted | 501's claim never counted. | Treat xmax as 0. Claim it. No error. |
501, and 501 committed | This version is no longer the head. 501 replaced it. | The isolation level decides. Read Committed follows the chain to the new head and re-checks. Repeatable Read and Serializable raise the error. |
Step 3 — write the new version, with xmin = 502. The old head now has xmax = 502, and the new version is the head.
Two things to notice, because the rest of the post rests on them.
The check is the same at every level. Only the reaction in the last row differs. That's the whole difference between Read Committed and Repeatable Read on the write side — one row of one table.
Step 1 and step 2 ask different sources. Step 1 asks the snapshot: "which version can I see?" The snapshot can be minutes old. Step 2 asks reality, right now: "what's the current status of the transaction whose ID sits in this xmax?" — by looking it up in the commit log, the small structure where every transaction's final outcome is recorded once. The snapshot is a photo from earlier; the commit log is live.
And that is how Postgres knows somebody changed the row. If the version you can see has a committed transaction in its xmax, then somebody replaced it — and that somebody must have committed after your snapshot, because if they'd committed before, your snapshot would have treated the version as already deleted and never picked it in step 1. A visible version with a committed xmax always means "someone committed a replacement after my snapshot was taken." Nothing more exotic than that, and no need to look at the new version at all.
"If 501 aborts, does Postgres erase the
501it wrote intoxmax?" No — that's what makesROLLBACKinstant even after a million updates. All that happens is one entry in the commit log: "501: aborted". Whoever meetsxmax = 501afterwards looks it up, finds "aborted", and treats it as0.
What "waiting" is
The second row of the table says "wait for 501". Here's what that means, because it's more specific than it sounds.
502 found 501 in the xmax, so it knows exactly who to wait for. Every transaction holds a lock on its own transaction ID for its whole life, and releases it the instant it commits or aborts. So 502 goes to sleep on 501's transaction-ID lock. You can watch this happen: while T2 holds an uncommitted UPDATE and T1 tries to write the same row, pg_stat_activity shows T1 with wait_event = transactionid. It's not waiting on the row. It's waiting on the other transaction.
When 501 finishes, 502 wakes up and re-runs step 2 against the same version. xmax is still 501, but now 501 is committed (last row of the table) or aborted (third row), and the table says what happens next.
Plain readers never enter this picture. A SELECT runs step 1 and stops. It looks at xmax only to decide whether the version is visible, never to decide whether to wait. A version whose xmax is a still-running transaction is, from the reader's snapshot, simply not deleted yet — so the reader returns it and moves on. That's why readers never block writers and writers never block readers. The only statements that ever wait are the ones that want to claim the head: UPDATE, DELETE, and SELECT … FOR UPDATE.
One more fact: the claim on the head is held until the transaction ends. It's never released early. This is why a long-running transaction that touched a row makes everyone else who wants that row queue up behind it.
The moment the first writer finishes
Let's run the whole thing as two events. Two customers try to buy the last lamps at the same time.
Event 1 — the wait. T2 runs UPDATE products … WHERE id = 42, writing its ID into the head's xmax, and hasn't committed. T1 runs the same UPDATE, finds T2's ID in xmax, sees T2 is still running, and goes to sleep. Nothing interesting has happened yet. This is identical under Read Committed, Repeatable Read and Serializable.
Event 2 — T2 finishes. T1 wakes up. What happens next depends on two things: what T2 did, and what level T1 is at.
If T2 aborted: its change never existed. T1 re-runs the check, finds an aborted xmax, treats it as 0, claims the head. No error, at any level. (Measured: T1 sat waiting for exactly as long as T2 held the row — 1.5 seconds in the test — then completed normally.)
If T2 committed, and T1 is at Read Committed: T1 re-runs the check and lands in the last row. Its reaction is to follow the chain to the new head, re-check its own WHERE clause against that version, and apply its update on top of it. No error. T1 never even finds out that a re-check happened. The mechanism has a name — EvalPlanQual — and it gets its own section below.
If T2 committed, and T1 is at Repeatable Read or Serializable: T1 runs the exact same check and lands in the exact same row — but its reaction is to refuse. It does not follow the chain. It could; the pointer to the next version is right there. But that new head was written by a transaction outside T1's snapshot, and acting on it would mean seeing fresh data mid-transaction, which is precisely what Repeatable Read promised not to do. So it stops:
ERROR: could not serialize access due to concurrent update
Why the two levels have to behave differently
This is the heart of it, so it's worth reading twice.
Repeatable Read promised T1: "the world is frozen at your snapshot." T1's snapshot says stock = 5. Reality is now 4. T1 wants to write 4 (computed as 5 - 1). Postgres has exactly three options, and two of them are lies:
- Write
4. But that4was computed from a5that no longer exists. It silently destroys T2's committed purchase — a lost update in disguise. Not allowed. - Quietly swap in the real value
4and write3. But then T1 saw fresh data mid-transaction, which breaks the frozen-world promise it was given. Not allowed. - Stop and say "the ground moved under you — start over." The only honest option.
Read Committed has no such dilemma, because it never promised a frozen world. Its promise was only "each statement sees what was committed when that statement started". Reading the newest version is perfectly consistent with that weaker promise, so option 2 is legal for it.
In one line: the Repeatable Read error is the price of the frozen-world guarantee. Read Committed doesn't pay it because it never made that guarantee.
Waiting and erroring are two different events
This is the part most explanations get wrong, so it gets its own heading. People assume the Repeatable Read error happens because T1 had to wait. It doesn't. Waiting and erroring are decided by two different questions:
| Question | Decided by |
|---|---|
| Does T1 have to wait? | Is the other writer still running at the moment T1 tries to write? (Row 2 of the table.) |
| Does T1 get the error? (RR / SER only) | Did the other writer commit after T1's snapshot was taken? (Row 4 of the table.) |
Work through the three possible timings of T2's commit. T1 is at Repeatable Read, and its snapshot was taken at its first SELECT:
| When did T2 commit? | Did T1 wait? | Did T1 error? | Why |
|---|---|---|---|
Before T1's first SELECT (so before T1's snapshot) | No | No | T1's snapshot already includes T2's change. T2's new version is the one T1 sees, its xmax is 0, T1 claims it. |
| After T1's snapshot, and T2 was still running when T1 tried to write | Yes | Yes | T1 finds T2's ID in xmax, sleeps until T2 commits, wakes, re-checks: committed xmax on the version it sees → error. |
| After T1's snapshot, but T2 had already committed before T1 tried to write | No | Yes | Nothing to wait for — T2's lock is long gone. But the version T1 sees already has a committed xmax → error at once. |
The third row is the one to remember: you can get the error without ever waiting. Measured, T1's UPDATE in that case fails in about a millisecond. And the abort case gives the reverse: you can wait without ever getting the error. The error is about snapshot staleness, not about waiting.
A common objection to the third row: "if T2 committed before T1 even tried to write, T1's snapshot can't see T2's change — so where's the conflict?" T1 is never asked to see T2's new version. Step 1 finds the old version through the snapshot, as always; step 2 looks at that old version's xmax in the live commit log, where T2 is now marked committed. That's enough.
EvalPlanQual: how Read Committed stays quiet
The name means "re-evaluate the plan's qualifications" — re-check your WHERE clause against a newer version of the row. It runs only at Read Committed. At Repeatable Read and Serializable, the same discovery — a committed xmax on the version you see — is the end of the road. EvalPlanQual is Read Committed's alternative to that error, and it's available only because Read Committed never promised a frozen world.
The problem it solves. You ran:
UPDATE products SET stock = stock - 1
WHERE id = 42 AND stock > 0;
Postgres picked the row using your snapshot's version. By the time it goes to write, another transaction has committed a newer version. Your chosen version is stale. Now what?
What EvalPlanQual does: follow the chain forward to the newest committed version — the head — and re-run your WHERE conditions against that version:
- Still matches (
id = 42 AND stock > 0is still true) → apply the update to the new version. Yourstock - 1runs against the fresh value. - No longer matches (the other transaction took the last lamp,
stockis now0) → skip the row. The statement reportsUPDATE 0.
This is the entire reason Read Committed "just works" without retry logic. Postgres quietly reconciles on your behalf, and the stock > 0 guard still holds — measured, with one lamp left and two buyers, the second UPDATE waited for the first, re-checked, and returned UPDATE 0.
Two limits, and the second one matters most.
It re-checks the target row only. The rest of the statement still uses your old snapshot. In an UPDATE … FROM other_table …, the target row is re-fetched fresh but other_table comes from the old snapshot, and the result can correspond to no serial order at all. This is exactly why Read Committed can't be patched into Repeatable Read — the mechanism that makes it convenient is the same mechanism that makes it weaker.
It protects a single SQL statement. The moment your read and your write are two separate round trips with application code in between — SELECT the stock, subtract one in Python, UPDATE with the result — there is no WHERE to re-check and no arithmetic to redo. That protection simply doesn't exist. This is the gap the next post is about.
Two traces, with real numbers
Let's watch the whole thing with the actual xmin and xmax values. Product 42, stock = 5, two customers buying at almost the same instant. Both traces were run against a real Postgres 18; the numbers below are the shape of what it printed, with round transaction IDs for readability.
Trace A — T2 commits before T1 issues its write (no waiting)
T1 (customer A) T2 (customer B)
-------------------------------------- --------------------------------------
BEGIN ISOLATION LEVEL REPEATABLE READ
SELECT stock FROM products WHERE id=42
→ 5
BEGIN
UPDATE products SET stock = stock - 1
WHERE id = 42
COMMIT -- stock is now 4
UPDATE products SET stock = stock - 1
WHERE id = 42 AND stock > 0
-- no lock is held any more, so NO WAIT happens here
Start with one version of the row on disk:
| Version | xmin | xmax | stock |
|---|---|---|---|
| V1 | 100 (committed long ago) | 0 | 5 |
-
T1 runs its
SELECT. T1 has written nothing, so it has no transaction ID of its own yet. Its snapshot is taken now and says, in effect: "everything up to 200 that has committed is visible to me; anything from 201 up hadn't started." Walking the versions: V1 hasxmin = 100(committed, in my snapshot) andxmax = 0→ V1 is my version.stock = 5. -
T2 runs its
UPDATE. T2 is given ID 201. Step 1: only V1 exists, and T2 sees it. Step 2: V1'sxmax = 0→ claim it. T2 writes201into V1'sxmaxand creates V2:Version xminxmaxstockV1 100 201 5 V2 201 0 4 -
T2 commits. The commit log now records "201: committed". T2's transaction-ID lock is released — nobody is holding anything.
-
T1 runs its
UPDATE. Step 1 — find the version using T1's snapshot (still the one from step 1; Repeatable Read, one snapshot):- V2:
xmin = 201. My snapshot says 201 hadn't started → this version doesn't exist for me. Skip. - V1:
xmin = 100→ visible.xmax = 201— my snapshot says 201 hadn't started, so this deletion hasn't happened for me → V1 is still my version.
Step 2 — look at V1's
xmaxin reality:201. Is 201 running? Check the commit log: committed. Last row of the table. - V2:
-
Error, immediately.
could not serialize access due to concurrent update. There was no wait in step 4 because 201 was already finished — nothing to sleep on. And T1 never looked at V2: it didn't need to see the new value to know V1 had been replaced. T1 must retry the whole transaction.
Compare with Read Committed at step 4: the UPDATE takes a new snapshot, which includes 201, so step 1 finds V2 directly (xmax = 0) and the update proceeds on stock = 4. Final stock 3, both purchases counted, no error, and T1 never knows anything happened. Same bytes on disk, different snapshot, different outcome.
Trace B — T2 is still uncommitted when T1 writes (waiting)
Now the other timing. T2 runs its UPDATE but hasn't committed when T1 issues its own. T1 finds 201 in V1's xmax, sees that 201 is still running, and goes to sleep on 201's transaction-ID lock. Then T2 commits, and T1 wakes up.
Under Read Committed: T1 runs EvalPlanQual, sees 4, re-checks > 0, writes 3. Same correct outcome as Trace A, just with a pause first. (Measured: T1's UPDATE returned UPDATE 1 after the 1.5 seconds T2 held the row.)
Under Repeatable Read: T1 re-runs the xmax check on V1 — the version it saw. xmax = 201, and 201 is now committed, but that commit isn't in T1's snapshot. Last row of the table: the same error as Trace A, just after a wait. T1 never re-read the row and never learned the new value; a committed xmax on the version it can see is all the evidence it needs.
The lesson from the two traces together: the waiting and the error are separate events. Trace A had an error with no wait. Trace B had both. And if T2 had rolled back instead of committing, T1 would have waited and then carried on with no error at all.
See it on your own machine
Open two psql sessions on any scratch database. In the first:
CREATE TABLE products (id int PRIMARY KEY, stock int);
INSERT INTO products VALUES (42, 5);
BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT stock FROM products WHERE id = 42; -- 5, and the snapshot is taken now
In the second, buy one lamp and commit:
UPDATE products SET stock = stock - 1 WHERE id = 42; -- autocommit: committed at once
SELECT xmin, xmax, ctid, stock FROM products WHERE id = 42;
-- xmin = 776, xmax = 0, ctid = (0,2), stock = 4 ← the new head (your IDs will differ)
Back in the first session, the old version is still the one your snapshot sees — and it now has a committed transaction in its xmax:
UPDATE products SET stock = stock - 1 WHERE id = 42 AND stock > 0;
-- ERROR: could not serialize access due to concurrent update
SELECT 1;
-- ERROR: current transaction is aborted, commands ignored until end of transaction block
ROLLBACK;
No waiting happened — the second session had already committed. If you want to see the two versions side by side, the pageinspect extension shows the raw page:
CREATE EXTENSION IF NOT EXISTS pageinspect;
SELECT lp, t_xmin, t_xmax, t_ctid FROM heap_page_items(get_raw_page('products', 0));
-- lp | t_xmin | t_xmax | t_ctid
-- ----+--------+--------+--------
-- 1 | 775 | 776 | (0,2) ← old version: replaced by 776, points at (0,2)
-- 2 | 776 | 0 | (0,2) ← the head
Repeat the first session's transaction with a plain BEGIN instead, and the same UPDATE returns UPDATE 1 and leaves stock = 3.
Bringing it all together
- An isolation level decides how much a transaction may ignore the others running at the same time. Higher means fewer surprises and more "start over" errors.
- The snapshot is the same at every level. What differs is when it's taken: Read Committed takes a fresh one before every statement; Repeatable Read and Serializable take one at the first statement (not at
BEGIN) and keep it. - Read Committed allows non-repeatable reads, phantoms and read skew, all from the same cause. Repeatable Read stops all three with one snapshot — in Postgres, phantoms included. Serializable adds a referee that also catches write skew, where two transactions break a multi-row rule without ever touching the same row.
- Dirty reads are impossible at every level, and plain reads never wait.
- A write always aims at the head of the version chain, and checks the head's
xmaxagainst the live commit log:0→ claim; running → wait on that transaction; aborted → claim; committed → the level decides. - Waiting and erroring are separate. Waiting depends on whether the other writer is still running. The Repeatable Read error depends on whether the version you see was replaced by a commit after your snapshot — you can get it with no wait at all.
- Read Committed reacts to a committed
xmaxwith EvalPlanQual: follow the chain, re-check theWHERE, apply on top. Repeatable Read refuses, because re-reading would break its frozen-world promise. Three options, two are lies. - EvalPlanQual protects one statement. It cannot protect a read in one statement and a write in the next.
Next up: that last bullet is a hole, and it's the one most production bugs fall through. Your app reads the stock, does the arithmetic in Python, and writes the result back — two statements, nothing for Postgres to re-check, and the second customer's purchase silently overwrites the first. That's a lost update, and the next post is about the four ways to stop it, how to write a retry loop that actually works, and where an isolation level is even set: How Do You Stop a Database From Losing Your Update?