Transactions & Isolation Levels

1.1Half a transfer

Let's reproduce the phone call. The script below uses SQLite, a small database that comes with Python, and keeps it in memory. It creates two accounts, Alice with 1,000 and Bob with 500, and then tries to move 100 from one to the other. Between the two UPDATE statements it raises an exception, which stands in for a crash. It does this twice: once with the two updates loose, and once with them wrapped in a transaction.

The new pieces are small. BEGIN starts a transaction. ROLLBACK ends it by undoing every write made since BEGIN. (The other way to end one is COMMIT, which makes the writes permanent, and it's the ending every later example aims for.) The argument isolation_level=None stops Python from quietly starting transactions on our behalf, so the BEGIN you see is the only one. In a real crash the program would be dead and couldn't call ROLLBACK at all; the database would clean up the half-finished transaction itself when it restarted. The script calls it explicitly so we can watch the result without killing a process.

import sqlite3
 
def fresh():
    db = sqlite3.connect(":memory:", isolation_level=None)      # we control BEGIN/COMMIT ourselves
    db.execute("CREATE TABLE acct (name TEXT PRIMARY KEY, balance INT)")
    db.execute("INSERT INTO acct VALUES ('alice', 1000), ('bob', 500)")
    return db
 
def show(label, db):
    rows = dict(db.execute("SELECT name, balance FROM acct"))
    print(f"{label:<28} alice={rows['alice']:>5}  bob={rows['bob']:>5}  total={sum(rows.values())}")
 
def transfer(db, use_txn):
    if use_txn: db.execute("BEGIN")
    db.execute("UPDATE acct SET balance = balance - 100 WHERE name = 'alice'")
    raise RuntimeError("crash between the two updates")
    db.execute("UPDATE acct SET balance = balance + 100 WHERE name = 'bob'")   # never reached
 
for use_txn in (False, True):
    db = fresh()
    try: transfer(db, use_txn)
    except RuntimeError:
        if use_txn: db.execute("ROLLBACK")
    show("with a transaction" if use_txn else "no transaction", db)

no transaction               alice=  900  bob=  500  total=1400
with a transaction           alice= 1000  bob=  500  total=1500

Both accounts started with 1,500 between them. In the first run, the crash left Alice at 900 and Bob unchanged, so the total is 1,400: 100 vanished, exactly as with the envelopes. In the second run the database rolled back the half-finished transfer, so Alice is back at 1,000 and the total is still 1,500.

1.2All or nothing

That promise is called atomicity: all of a transaction's writes happen, or none of them do. An atom was once thought to be the smallest piece of matter, something that couldn't be cut in half, and a transaction can't be cut in half either. SQLite is fine for one program on one machine, but most of this chapter uses Postgres (PostgreSQL), a widely used open-source database server that many programs connect to at once. Section 5 shows how Postgres keeps the all-or-nothing promise without doing anything that looks like undoing.

Atomicity covers one kind of trouble, a crash in the middle of our own transfer. It says nothing about another program looking at the two accounts while the transfer is half done, and that is the trouble the rest of the chapter is about. To talk about it properly we need to know what a transaction promises overall, and which of those promises is the hard one.

2.1ACID, letter by letter

Atomicity is the first of four promises that databases usually advertise together under the acronym ACID. The second, consistency, is about invariants, rules that must always be true of the data, such as "a transfer never changes the total money" or "a balance never goes below zero". Two of the rows below mention the write-ahead log, or WAL: a file where the database writes down every change before it touches the tables, so that after a crash it can tell what was finished and what wasn't. The durability row mentions fdatasync, a system call that forces data onto the disk and waits until the operating system confirms it got there.

The database can enforce some invariants itself. A constraint is a rule declared alongside the table, such as CHECK (balance >= 0), and the database rejects any write that breaks it; a foreign key is a constraint that a value must match a row in another table. "A transfer never changes the total" can't be written as a constraint on one row, though. It holds only because the transfer's code subtracts and adds the same amount, which is why the table says consistency is mostly your job.

Atomicity and durability are about crashes, and they're more or less yes-or-no: either the transfer happened or it didn't, either the commit survived or it didn't. Isolation is about what happens when transactions overlap in time. It comes in strengths, and it's where the surprises live.

2.2The gold standard: serializable

The strongest form of isolation is called serializable: the result of running transactions at the same time is the same as running them one at a time, in some order. If every transaction is correct on its own, a serializable database keeps them correct together. Go back to the program from the opening that adds up every balance while the transfer runs, and call it the auditor. Under serializable isolation the auditor sees either the whole transfer or none of it, and so always gets 1,500, because as far as the result goes the auditor ran entirely before the transfer or entirely after it.

?Why doesn't every database just do that?

Because the obvious ways to get it cost throughput, the number of transactions the database can finish per second. Running one transaction at a time works (Redis, an in-memory data store, and the VoltDB database do exactly that), but only if every transaction is short and its data is in memory, because everyone else waits while it runs. Making each transaction wait until nobody else is changing the rows it reads has the same effect on a smaller scale: readers and writers spend their time waiting for each other. So databases offer weaker settings that allow some odd outcomes in exchange for more concurrency, and leave it to you to know which ones you can live with. A transaction picks one of these settings, called its isolation level, which says which odd outcomes the database must rule out.

To choose a level sensibly we need a vocabulary for the odd outcomes themselves, and the transfer is a good place to find them.

3.1Watching the transfer from outside

Suppose that while the transfer from Alice to Bob is running, the auditor's transaction adds up every balance. The right answer is 1,500 whichever side of the transfer the auditor lands on. Here is how the auditor can get something else.

If the auditor can see changes the transfer has written but not yet committed, it might read Alice's balance after the debit (900) and Bob's before the credit (500). The total is 1,400. This is a dirty read: seeing another transaction's uncommitted writes. It's worse if the transfer then rolls back, because the auditor has reported balances that never existed.

Now suppose the database refuses dirty reads, so the auditor only ever sees committed data. The auditor reads Bob's balance first (500). Then the transfer commits. Then the auditor reads Alice's balance and gets 900. Every number the auditor saw was committed, and the total is still 1,400. The auditor read two related rows at two different moments, which is called read skew.

Two transfers can also collide with each other. Say two deposits of 100 arrive for Alice at once. Each reads her balance (1,000), adds 100 in the application, and writes back 1,100. The second write overwrites the first, and one deposit has vanished without any error. This is a lost update.

3.2The catalogue

Those three, and four more, are the ones people name. In the table, T1 and T2 are two transactions running at the same time, and to abort is to end a transaction by undoing it, as ROLLBACK did in section 1.

Write skew, the last row, is the strangest, and it's the hardest to prevent. Sections 6 and 7 reproduce lost update and write skew on Postgres, because those two are the ones application code runs into most.

Adya's 1999 thesis made these definitions precise. It draws each anomaly as a graph whose arrows say which transaction read or overwrote what another wrote, and gives each bad shape a short name: G0 for dirty write, G1 for dirty reads and their relatives, G2-item for write skew, and G2 for the wider family that also covers phantoms. Jepsen, a company that tests databases for correctness, and Hermitage, a set of scripted anomaly tests we'll meet in section 10, use those names, and so will the rest of this chapter where it matters.

Knowing the names, the next question is how a database can stop any of them from happening. There are two basic answers.

4.1Locks and two-phase locking

A lock is a marker on a row that says "I'm using this; wait your turn". A shared lock lets other readers in but keeps writers out, and an exclusive lock keeps everyone out. Classic databases use two-phase locking (2PL): a transaction takes a shared lock on everything it reads and an exclusive lock on everything it writes, and releases nothing until it commits. The "two phases" are growing (acquiring locks) and shrinking (releasing them). Holding every lock until the very end, as here, is called strict 2PL, and it's the form databases use.

Row locks alone leave one hole. A query such as "count the accounts with a balance over 1,000" can't lock a row that doesn't exist yet, so another transaction could insert a matching row halfway through, which is a phantom. Strict 2PL is serializable once it also locks ranges of an index (the gap where a matching row would go), so that nobody can insert a row a query would have matched. For our auditor, it means the transfer's exclusive locks on Alice's and Bob's rows block the auditor until the transfer commits, and the auditor's shared locks would block a transfer that tried to start halfway through the audit. Nobody sees a half-done transfer, and you pay for that: readers block writers, writers block readers, and transactions that take locks in different orders deadlock, each waiting for a lock the other holds.

Here's a real deadlock on Postgres. Transaction A moves money from Alice (account 1) to Bob (account 2), and at the same moment transaction B moves money from Bob to Alice. Each debits its own source first.

Postgres detects the cycle and aborts one transaction, so the waiting does end, but a full second has gone by and one transfer has to be run again. Postgres uses row locks like this for writes at every level. What it doesn't do is lock what you read, which is where the second mechanism comes in.

4.2Multi-version concurrency control

Locking readers is expensive because most transactions read far more than they write. So suppose we never overwrite a row. When the transfer changes Alice's balance, we keep the old row, 1,000, and add a new one, 900. A reader that started before the transfer committed can keep looking at the old row, and the transfer never has to wait for it.

This is multi-version concurrency control, or MVCC: the database keeps several versions of each row, and a writer creates a new version instead of overwriting the old one. Each reader picks a fixed moment when it starts looking and sees the versions that were committed as of that moment. That fixed view is the reader's snapshot. Readers never wait for writers, and writers never wait for readers. Writers still wait for each other, because two writers can't both create the next version of the same row.

"A snapshot" and "a version" sound vague, and the useful thing about Postgres is that both are small enough to follow all the way down. Let's do that.

5.1xmin, xmax and the commit log

Postgres calls the file holding a table's rows the heap, and one stored row version in it a heap tuple, or just a tuple. Every tuple has a header with two fields. xmin is the ID of the transaction that created this version, and xmax is the ID of the transaction that deleted or replaced it, or 0 if none has. They're hidden columns: SELECT * leaves them out, but you can ask for them by name. An UPDATE is therefore an insert of a new version plus setting xmax on the old one; chapter 18 shows both versions sitting in the same page of the table file.

Transaction IDs (XIDs) are 32-bit counters, handed out when a transaction first writes. Whether each XID committed or aborted is kept in a separate structure, the commit log (pg_xact), at two bits per transaction. That tiny record turns out to be all Postgres needs for atomicity. A session is one program's connection to the database, and every transaction runs inside one. Here is our transfer in one session, with T standing for its transaction ID:

The commit also writes a commit record to the WAL, which lets the entry survive a crash. So atomicity needs no undoing at all. A rolled-back or crashed transaction just never gets a committed entry, and every version it wrote stays invisible. The same log gives us the visibility rule for readers, and it needs one more idea: a way to freeze what "committed" means while a reader is in the middle of a scan.

5.2A snapshot is three numbers and a list

A reader can't just ask the log "did the creator of this version commit?" for each version as it goes. A transaction might commit halfway through the reader's scan, and the reader would see some rows from before the commit and some from after, which is read skew again. The reader needs to fix the answer once, at the start. It does that by recording which transactions count as finished for it. That record is the snapshot, and here is how Postgres defines it:

typedef struct SnapshotData
{
	SnapshotType snapshot_type; /* type of snapshot */
 
	/*
	 * An MVCC snapshot can never see the effects of XIDs >= xmax. It can see
	 * the effects of all older XIDs except those listed in the snapshot. xmin
	 * is stored as an optimization to avoid needing to search the XID arrays
	 * for most tuples.
	 */
	TransactionId xmin;			/* all XID < xmin are visible to me */
	TransactionId xmax;			/* all XID >= xmax are invisible to me */
 
	/*
	 * For normal MVCC snapshot this contains the all xact IDs that are in
	 * progress, unless the snapshot was taken during recovery ...
	 *
	 * note: all ids in xip[] satisfy xmin <= xip[i] < xmax
	 */
	TransactionId *xip;
	uint32		xcnt;			/* # of xact ids in xip[] */
	/* ... */

So a snapshot is a lower bound xmin (everything below it has finished), an upper bound xmax (everything from it up hadn't started yet), and a list xip of the transactions in between that were still running. The names clash with section 5.1: a tuple's xmin and xmax say who created and deleted that version, while a snapshot's xmin and xmax bound which transaction IDs count as finished. Let's watch one being taken in the middle of a crowd. We'll use a small acct table that starts with three accounts of 100 each and gains a fourth along the way. To keep the numbers readable, each writer here moves just 1 instead of 100.

The script below does exactly this on Postgres 16. slow, fast, auto and reader are four separate sessions, each its own connection to the database; auto is in autocommit mode, so each of its statements is its own transaction. The reader uses a setting that keeps its snapshot fixed for the whole transaction (section 5.4 gives it a name). pg_current_snapshot() prints the snapshot as xmin:xmax:xip, and xmin and xmax in the select are the hidden columns from section 5.1.

slow.execute("update acct set bal=bal-1 where id=2")    # xid 2051525, stays open
fast.execute("update acct set bal=bal-1 where id=1")    # xid 2051526
fast.commit()
auto.execute("insert into acct values (4, 100)")         # xid 2051527, autocommit
 
reader.execute("select pg_current_snapshot()")
reader.execute("select id, bal, xmin, xmax from acct order by id")
slow.commit()
reader.execute("select id, bal from acct order by id")  # same snapshot (repeatable read)

reader snapshot 2051525:2051528:2051525
reader sees [(1, 99,  '2051526', '0'),
             (2, 100, '2051524', '2051525'),
             (3, 100, '2051524', '0'),
             (4, 100, '2051527', '0')]
after slow writer commits, same snapshot: [(1, 99), (2, 100), (3, 100), (4, 100)]

The snapshot line reads xmin:xmax:xip, matching the fourth frame of the animation. In the rows, account 1 shows the fast writer's update (xid 2051526, not on the list), account 4 shows the insert, and account 2 still shows its old version: its xmax is the slow writer, which the snapshot treats as uncommitted. Even after the slow writer commits, this snapshot keeps seeing 100, and that fixed view is what the snapshot is for.

5.3The visibility check

For every row version a scan finds, Postgres has to decide whether this snapshot sees it. In simplified form, the function HeapTupleSatisfiesMVCC asks two questions: did the creator commit before my snapshot, and if someone deleted this version, did that commit before my snapshot? The steps below follow one row version through the check.

Step 2's snapshot test is short, because most XIDs are settled by the two range checks at the top and never reach the search through the list:

XidInMVCCSnapshot(TransactionId xid, Snapshot snapshot)
{
	/* Any xid < xmin is not in-progress */
	if (TransactionIdPrecedes(xid, snapshot->xmin))
		return false;
	/* Any xid >= xmax is in-progress */
	if (TransactionIdFollowsOrEquals(xid, snapshot->xmax))
		return true;
	/* ... then search snapshot->subxip[] and snapshot->xip[] ... */

?Why the hint bits?

Because checking pg_xact for every tuple on every scan would be slow and contended. Whichever reader first learns that a transaction committed records it on the tuple itself, in a bit that says "my creator committed". This is also why a plain SELECT straight after a bulk load can write to disk: it's setting hint bits on every page it reads.

5.4Read committed and repeatable read: when the snapshot is taken

Postgres lets each transaction choose among three isolation levels. (It also accepts the name read uncommitted, but treats it as read committed, so dirty reads never happen.) Read committed is the default, repeatable read is the next step up, and serializable is the top, which sections 8 and 9 cover. The difference between the two lower ones is a single decision: when the snapshot is taken.

You can check this with a counter that starts at 0. Read it, let another session change it to 42 and commit, and read it again. Under read committed the second read returns 42. Under repeatable read and serializable it returns 0 both times.

That matters for our auditor. At read committed, two separate SELECT statements, Bob's balance and then Alice's, take two snapshots, and a transfer that commits between them produces the 1,400 from section 3. A single SELECT sum(balance) takes one snapshot and gets 1,500. At repeatable read the whole transaction shares one snapshot, so the auditor can read the accounts in any order and any number of statements and always see a consistent state.

5.5The cost: versions that can't be cleaned up

Every UPDATE leaves an old version behind, as the transfer left Alice's 1,000. Those dead versions take up space and slow scans, so Postgres runs a cleanup job called VACUUM that removes versions no one can see any more. The catch is the phrase "no one": VACUUM can only remove a version if no running snapshot could still need it. So the oldest snapshot still open in the database sets the cutoff, and every version that died after that snapshot was taken has to stay.

This script keeps one repeatable-read transaction open, updates all 1,000 rows of a larger acct table so there are 1,000 dead versions, and runs vacuum (verbose), which reports what it removed. Then it ends the old transaction and vacuums again. old and auto are two sessions, as before.

old.execute("select count(*) from acct")    # repeatable read, snapshot now fixed
auto.execute("update acct set bal=bal+1")   # 1,000 rows -> 1,000 dead versions
auto.execute("vacuum (verbose) acct")
old.commit()
auto.execute("vacuum (verbose) acct")

tuples: 1 removed, 2000 remain, 1000 are dead but not yet removable
...
tuples: 1000 removed, 1000 remain, 0 are dead but not yet removable

In the first report, 2000 remain is the 1,000 current versions plus the 1,000 old ones, and those 1,000 are dead but not yet removable: the old transaction's snapshot predates the update, so as far as VACUUM can tell, that transaction might still want to read them. Once it committed, all 1,000 went on the next run.

With snapshots, readers see a steady picture, and the transfer's atomicity comes from one log entry. What snapshots don't do is settle a fight between two transactions that both want to change the same row, and that's the situation behind the lost deposit from section 3.

6.1Reproducing it

Think of the two deposits arriving for Alice at once. To keep the numbers small, the experiment uses a counter that starts at 0, and each session reads it, adds 1 in the application, writes the result back, and tries to commit. The UPDATE each session sends is SET n = 1, a value it computed itself.

Here is the same experiment at each level on Postgres 16:

40001 is the standard error code for a serialization failure, which means "your transaction conflicted with another and has been aborted; run it again". The table's last two rows end at 1 because B's attempt was thrown away, and the retry in the final frame is what brings the value to 2.

?Why does repeatable read catch it but read committed doesn't?

Both levels make B's UPDATE wait for A's row lock. The difference is what happens when A commits. Under read committed, B re-reads the newest version of the row, checks it still matches the WHERE clause, and applies its write, which was computed from a stale value. Under repeatable read, the newest version is newer than B's snapshot, so Postgres refuses: first updater wins, and the second gets a serialization failure.

Repeatable read in Postgres is a snapshot for the whole transaction plus this first-updater-wins rule. That combination has a name, snapshot isolation, and it's the level most multi-version databases implement under various names. Sections 7 and 10 say what it still allows.

6.2Four ways to not lose it

There are several fixes for the deposit problem, and which one fits depends on where the new value comes from. In the table, SELECT … FOR UPDATE reads rows and takes the same exclusive lock an UPDATE would, so rivals wait. The optimistic check adds a version column to the row: $seen is the version number the application read, and $new the value it computed.

For Alice's deposits, the first row is the natural one: UPDATE acct SET balance = balance + 100 WHERE name = 'alice' hands the database an instruction to add, not a number it computed from a stale read, and Postgres applies it to the newest version of the row.

Every one of these fixes works because two deposits write the same row, so they collide. A transaction that changes a different row from the one a rival changed gets no such help, and that's the next problem.

7.1Two doctors, one rule

Our bank can hit this. Suppose Alice and Bob share an overdraft rule: each account may go negative, but their two balances together must never go below zero. Alice (1,000) and Bob (500) each withdraw 1,000 at the same moment, from their own account. Each transaction adds up both balances, sees 1,500, decides a 1,000 withdrawal is safe, and subtracts it from its own row. Alice ends at 0, Bob at −500, and the total is −500. Neither transaction wrote the row the other wrote.

The same shape is usually taught with a hospital, as in Ports and Grittner's paper on Postgres's implementation, and that's the version we'll run. The hospital requires at least one doctor on call. Two doctors, who happen to be called Alice and Bob too (a new table, doctors, unrelated to the bank), are both on call, and both feel ill at the same moment. Each one's transaction checks the rule and takes themselves off call:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT count(*) FROM doctors WHERE on_call;          -- 2, so it's safe to leave
UPDATE doctors SET on_call = false WHERE name = 'alice';   -- Bob's says 'bob'
COMMIT;

BEGIN ISOLATION LEVEL REPEATABLE READ is how a transaction asks for a level other than the default. Each transaction reads the count, sees 2, and writes its own row. Neither writes the row the other writes.

?Why can't snapshot isolation see it?

Because its only conflict rule is about writes: two transactions that wrote the same row can't both commit. Here each transaction read a row the other one wrote. That's a read-write dependency in each direction, a cycle, and no serial order could produce the result. Snapshot isolation doesn't track reads, so the cycle is invisible to it.

It seems contrived, but the same shape turns up well outside hospitals, including in our bank:

7.2Fixing write skew without serializable

Since the lost-update fixes worked by making the two writers touch the same row, an obvious idea is to make the doctors touch the same rows too, by locking what each one reads.

?Why doesn't SELECT FOR UPDATE always fix it?

It fixes the doctors case: SELECT … FROM doctors WHERE on_call FOR UPDATE locks both rows, so the second transaction waits and then sees the change. But FOR UPDATE only locks rows that exist. In the meeting-room and username cases the check is for the absence of a row, so there's nothing to lock. That's a phantom, and row locks can't stop it.

The first three need you to recognise the shape of each rule and pick a fix for it. The fourth asks the database to find every such case itself. That is what serializable means, and how a database can do it is the next question.

8.1Three implementations

In MySQL, the strict-2PL row means that reads take locks: at SERIALIZABLE, InnoDB turns every plain SELECT into SELECT … FOR SHARE when autocommit is off (MySQL 8.4 manual). Snapshot isolation on its own isn't serializable, as the doctors showed, and the third row is how Postgres closes the gap without going back to locking every read.

8.2How SSI finds the cycle

SSI builds on a rw-conflict: transaction T1 read a row that a concurrent transaction T2 then wrote. Cahill, Röhm and Fekete's Serializable Isolation for Snapshot Databases (SIGMOD 2008) proved that every snapshot-isolation anomaly contains a particular shape, two consecutive rw-conflicts. Postgres's implementation notes put it like this:

SSI is based on the observation [2] that each snapshot isolation
anomaly corresponds to a cycle that contains a "dangerous structure"
of two adjacent rw-conflict edges:
 
      Tin ------> Tpivot ------> Tout
            rw             rw
 
SSI works by watching for this dangerous structure, and rolling
back a transaction when needed to prevent any anomaly. This means it
only needs to track rw-conflicts between concurrent transactions, not
wr- and ww-dependencies. It also means there is a risk of false
positives, because not every dangerous structure is embedded in an
actual cycle.

The quote's wr- and ww-dependencies are the other two kinds of arrow, where one transaction reads or overwrites what another has written, and SSI doesn't need to watch them. To see rw-conflicts, a serializable transaction records what it read as SIREAD locks. They never block anyone; they're markers that are checked when someone writes. Here's the two-doctors example under SSI. Two more Postgres words appear in the first message. Postgres calls a table a relation. A query that reads every row of a table from start to end, instead of finding rows through an index, is doing a sequential scan, and since it has read everything, its SIREAD lock covers the whole relation.

How much each SIREAD lock covers matters. Listing them (section 11.1 has the query) shows the difference between the scan above and a query that finds one doctor through the primary-key index, which locks only one page of the index and one row (a tuple):

count(*) with a seq scan:  [('relation', 'doctors', None, None)]
WHERE name = 'alice':      [('page', 'doctors_pkey', 1, None), ('tuple', 'doctors', 0, 1)]

?Why do false positives happen?

Two reasons. SSI aborts on the dangerous structure without checking that it closes into a real cycle. And coarse SIREAD locks (a whole relation, from a sequential scan, or promoted from many tuple locks once max_pred_locks_per_transaction fills) conflict with writes that didn't touch anything the reader used. Indexes on the columns you filter by keep the locks fine-grained.

8.3Serializable is only as good as the implementation

In 2020, Jepsen tested PostgreSQL 12.3 and found that "serializable" allowed G2-item, the write-skew class, during normal operation. The bug was in conflict detection with three concurrent transactions. Jepsen confirmed it in 9.5.22, 10.13, 11.8, 12.3 and 13, and it was fixed in the next minor releases. Jepsen's other point was about names: at the time the documentation didn't use the term "snapshot isolation", and saying that Postgres's repeatable read is snapshot isolation "would immediately clarify matters". We come back to that in section 10.

First, though, there's the price of all this tracking, and it turns out to be small in one place and large in another.

9.1The published numbers

Ports and Grittner's VLDB 2012 paper on the Postgres implementation puts SSI at "less than 7%" below snapshot isolation, and well ahead of strict 2PL on read-heavy workloads. One of their benchmarks is RUBiS, which imitates an auction website:

SSI's tracking costs about 3% of throughput here, and locking costs more than half. In your own system, though, the cost you're more likely to notice is retries.

9.2Retries on hot rows

A hot row is one that most transactions want to change. Every transaction that collides with another at repeatable read or serializable is aborted and has to run again, so a hot row turns into a pile of retries. pgbench, Postgres's built-in benchmark tool, comes with a banking-style transaction called TPC-B-like: like our transfer it changes an account balance, and it also adds the amount to one row of a small branches table, which makes that row hot. pgbench's scale factor sets how many branch rows there are, so scale 1 means a single branch that every transaction updates. The runs below used 8 clients, --max-tries=1000 so failed transactions retry, 15 seconds per run, median of three:

The throughput figures swing a lot from run to run and from machine to machine, while the retry percentages stayed within about three points. With a single client, nothing was ever retried at any level.

?Why does read committed win so clearly here?

Because on a write-write conflict it waits and then applies the update to the newest row version, while repeatable read and serializable throw the transaction away and start again. On this workload serializable cost roughly the same as repeatable read: the failures were almost all first-updater-wins conflicts, not SSI aborts.

9.3The retry loop is not optional

Because aborts are a normal result at the higher levels, the application has to expect them. At repeatable read and serializable, 40001 (serialization failure) is a normal outcome, not an error to log and give up on. Deadlocks (40P01) can happen at any level. Both mean "run the whole transaction again", from the first statement, because every read it did may be stale.

import psycopg2, random, time
from psycopg2 import errorcodes
 
RETRYABLE = {errorcodes.SERIALIZATION_FAILURE, errorcodes.DEADLOCK_DETECTED}
 
def run_tx(conn, work, attempts=10):
    for attempt in range(attempts):
        try:
            with conn:                         # BEGIN ... COMMIT, or ROLLBACK on error
                with conn.cursor() as cur:
                    return work(cur)           # all reads *and* writes, every time
        except psycopg2.Error as e:
            if e.pgcode not in RETRYABLE or attempt == attempts - 1:
                raise
            time.sleep(random.uniform(0, 0.01 * 2 ** attempt))   # jittered backoff

run_tx runs work inside a transaction. If Postgres reports a serialization failure or a deadlock, it waits a random, growing delay (so two colliding transactions don't retry in lockstep) and runs work again from the top, up to ten times. Any other error is raised immediately.

All of this assumed that "repeatable read" and "serializable" mean what Postgres says they mean. They don't mean that everywhere.

10.1The standard's four levels

SQL-92, the SQL standard of the early 1990s, defines four isolation levels by which of three phenomena they prevent: dirty reads, non-repeatable reads and phantoms. Berenson, Bernstein, Gray and colleagues showed in A Critique of ANSI SQL Isolation Levels (1995) that those definitions are ambiguous, miss lost updates and write skew entirely, and have no place for snapshot isolation, the level most MVCC databases implement.

?So what does "repeatable read" mean in your database?

Whatever the vendor decided. Martin Kleppmann's Hermitage project ran the same anomaly tests against each database and recorded what each level is. The table uses one name we haven't met. Monotonic atomic view is a level slightly stronger than read committed: once you see any of another transaction's writes, you see all of them, but two statements in your transaction can still see different sets of committed data. Read committed built on per-statement snapshots, as in section 5.4, gives exactly that.

11.1Where to look

Each question the chapter raised has a query that answers it on a running Postgres.

-- Who is holding back vacuum? Long-running and idle-in-transaction sessions,
-- oldest snapshot first (section 5.5)
select pid, state, xact_start, age(backend_xmin) as xmin_age, left(query, 60)
from pg_stat_activity where backend_xmin is not null order by xmin_age desc;
 
-- How often are deadlocks happening? (section 4.1)
-- Serialization failures (40001) aren't counted here; count them where you retry (section 9.3)
select datname, deadlocks from pg_stat_database;
 
-- Who is waiting on whom right now? (sections 4.1 and 6)
select pid, pg_blocking_pids(pid), wait_event_type, left(query, 60)
from pg_stat_activity where cardinality(pg_blocking_pids(pid)) > 0;
 
-- SIREAD locks held by serializable transactions (section 8.2)
select locktype, relation::regclass, page, tuple from pg_locks where mode = 'SIReadLock';

Set log_lock_waits = on so any wait longer than deadlock_timeout is logged, with the waiting query and the process IDs holding the lock.

11.2Rules that hold up

1. Do read-modify-write in SQL. UPDATE … SET n = n + 1 is correct at every level; "select, compute, update" is not (section 6).
2. Wrap every transaction at repeatable read or serializable in a retry loop that re-runs the reads and keeps side effects outside (section 9.3).
3. Keep transactions short, and never wait on a user or a network call inside one. An idle-in-transaction session holds back vacuum for the whole database, so set idle_in_transaction_session_timeout (section 5.5).
4. Lock rows in a consistent order, such as sorted by ID, so two transfers going opposite ways can't deadlock (section 4.1).
5. Prefer a constraint to a check in code for uniqueness and non-overlap (section 7.2).
6. Look for the hot row before raising the level. A global counter or per-tenant total costs retries at repeatable read and serializable (section 9.2).
7. Read the Hermitage row for your database before trusting a level's name (section 10).

11.3What you trade for what

11.4Symptom, cause, fix