Transaction Isolation Levels in Go With Real Anomalies

Two transactions that run at the same time can see each other's effects in surprising ways. The SQL standard names the anomalies and lets you choose how many to tolerate by picking an isolation level. The names are easy to memorise and hard to feel, so this article does what the textbook cannot: it runs two transactions against a real PostgreSQL 18 and shows the anomalies happening, on Go 1.25.6. At the end you will know what each level prevents in PostgreSQL, what it costs, and what your code must do at the strictest level.

The levels and the standard's anomalies

The SQL standard defines four levels by which anomalies are allowed:

PostgreSQL implements three distinct levels. Read Uncommitted behaves as Read Committed, so dirty reads never happen. Repeatable Read is snapshot isolation, which also prevents phantoms, but not every serialization anomaly. Go exposes the levels through sql.TxOptions{Isolation: sql.LevelReadCommitted} and its siblings (sql.LevelRepeatableRead, sql.LevelSerializable).

The lab

package main

import (
	"context"
	"database/sql"
	"fmt"
	"log"

	_ "github.com/jackc/pgx/v5/stdlib"
)

const dsn = "postgres://postgres:[email protected]:55432/lab?sslmode=disable"

func open() *sql.DB {
	db, err := sql.Open("pgx", dsn)
	if err != nil {
		log.Fatal(err)
	}
	return db
}

func must(err error) {
	if err != nil {
		log.Fatal(err)
	}
}

// nonRepeatable: T1 reads twice while T2 commits in between.
func nonRepeatable(db *sql.DB, level sql.IsolationLevel, name string) {
	ctx := context.Background()
	_, _ = db.Exec(`drop table if exists t586; create table t586 (id int primary key, bal int); insert into t586 values (1, 100)`)
	tx1, err := db.BeginTx(ctx, &sql.TxOptions{Isolation: level})
	must(err)
	var first, second int
	must(tx1.QueryRow(`select bal from t586 where id = 1`).Scan(&first))
	_, err = db.Exec(`update t586 set bal = 50 where id = 1`) // another transaction commits
	must(err)
	must(tx1.QueryRow(`select bal from t586 where id = 1`).Scan(&second))
	_ = tx1.Commit()
	fmt.Printf("%-16s first read=%d second read=%d  (non-repeatable read: %v)\n", name, first, second, first != second)
}

// writeSkew: two doctors on call; each checks that someone else is on call, then goes off call.
func writeSkew(db *sql.DB, level sql.IsolationLevel, name string) {
	ctx := context.Background()
	_, _ = db.Exec(`drop table if exists oncall; create table oncall (doctor text primary key, on_call bool); insert into oncall values ('ada', true), ('grace', true)`)
	tx1, _ := db.BeginTx(ctx, &sql.TxOptions{Isolation: level})
	tx2, _ := db.BeginTx(ctx, &sql.TxOptions{Isolation: level})
	var n1, n2 int
	must(tx1.QueryRow(`select count(*) from oncall where on_call`).Scan(&n1))
	must(tx2.QueryRow(`select count(*) from oncall where on_call`).Scan(&n2))
	_, err1 := tx1.Exec(`update oncall set on_call = false where doctor = 'ada'`)
	_, err2 := tx2.Exec(`update oncall set on_call = false where doctor = 'grace'`)
	c1 := tx1.Commit()
	c2 := tx2.Commit()
	var left int
	must(db.QueryRow(`select count(*) from oncall where on_call`).Scan(&left))
	fmt.Printf("%-16s doctors on call afterwards: %d  (tx1 commit=%v, tx2 commit=%.50v)\n", name, left, c1 == nil && err1 == nil, errStr(firstErr(err2, c2)))
}

func firstErr(a, b error) error {
	if a != nil {
		return a
	}
	return b
}

func errStr(err error) string {
	if err == nil {
		return "ok"
	}
	return err.Error()
}

func main() {
	db := open()
	defer db.Close()
	db.SetMaxOpenConns(5)
	nonRepeatable(db, sql.LevelReadCommitted, "ReadCommitted")
	nonRepeatable(db, sql.LevelRepeatableRead, "RepeatableRead")
	writeSkew(db, sql.LevelReadCommitted, "ReadCommitted")
	writeSkew(db, sql.LevelRepeatableRead, "RepeatableRead")
	writeSkew(db, sql.LevelSerializable, "Serializable")
	_, _ = db.Exec(`drop table t586; drop table oncall`)
}

go run . 2>&1

ReadCommitted    first read=100 second read=50  (non-repeatable read: true)
RepeatableRead   first read=100 second read=100  (non-repeatable read: false)
ReadCommitted    doctors on call afterwards: 0  (tx1 commit=true, tx2 commit=ok)
RepeatableRead   doctors on call afterwards: 0  (tx1 commit=true, tx2 commit=ok)
Serializable     doctors on call afterwards: 1  (tx1 commit=true, tx2 commit=ERROR: could not serialize access due to read/writ)

The program runs two scenarios at three levels. It uses plain database/sql and orders the steps explicitly, with no sleeps, so the interleaving is the same every run.

Scenario one: reading twice

Transaction 1 reads a balance, then another statement, auto-committed outside it, changes the balance, then transaction 1 reads again.

• At Read Committed: first read=100 second read=50 (non-repeatable read: true). Each statement sees the data committed when it starts, so the second read sees the other transaction's commit. For many requests this is fine. For a report that reads the same row twice and assumes it did not change, it is a bug.
• At Repeatable Read: first read=100 second read=100 (non-repeatable read: false). The transaction took a snapshot at its first query and reads from it until it ends. Reads are consistent for the transaction's lifetime.

The cost of the snapshot is that the transaction will not see newer data, and that a conflicting write can be rejected: if transaction 1 tried to update the row that another transaction changed after the snapshot, PostgreSQL would abort it with could not serialize access due to concurrent update. That is the first hint that stricter levels require retries.

Scenario two: write skew

Two doctors are on call, and the rule is that at least one must be. Each doctor's transaction checks select count(*) from oncall where on_call, sees 2, and sets their own row to false. Neither writes the other's row, so there is no direct conflict.

The output:

ReadCommitted    doctors on call afterwards: 0  (tx1 commit=true, tx2 commit=ok)
RepeatableRead   doctors on call afterwards: 0  (tx1 commit=true, tx2 commit=ok)
Serializable     doctors on call afterwards: 1  (tx1 commit=true, tx2 commit=ERROR: could not serialize access due to read/write dependencies...)

At Read Committed and Repeatable Read, both transactions committed and the hospital has nobody on call. Each transaction was individually correct on the data it saw. Together they broke the invariant. This is write skew, a serialization anomaly: the outcome is impossible in any serial order of the two transactions. It is allowed by snapshot isolation, so PostgreSQL's Repeatable Read does not prevent it, and many people believe it does.

At Serializable, PostgreSQL detects the dangerous pattern of read-write dependencies between the two transactions and aborts one at commit: could not serialize access due to read/write dependencies among transactions. One doctor remains on call. The invariant held, at the price of an error the client has to deal with.

What this means in practice

Read Committed (the default) is the right level for most code, where each statement is correct on its own and invariants are enforced by constraints or single-statement updates. It is cheap, it does not abort, and most web applications live here. Know that a sequence of reads in one transaction is not consistent.

Repeatable Read suits reports and exports that must see one consistent snapshot across several queries. It is read-only friendly, and it is cheap. Do not mistake it for protection against check-then-act logic.

Serializable is the answer when correctness depends on invariants across rows that no constraint expresses: on-call rosters, seat allocation, balance rules over several accounts. You pay with aborted transactions, which are normal at this level, and a small overhead. The code must retry the whole transaction on a serialization failure. The next article shows the loop.

The lost update

A third anomaly deserves a mention because it is the one most application code actually suffers from. Two requests read a counter, both add one in Go, both write back: the second write overwrites the first. This is a lost update. The code shape is select n, n+1 in Go, update ... set n = $1.

What happens depends on the level. At Read Committed, nothing stops it: both updates succeed and one increment is lost. At Repeatable Read in PostgreSQL, the second update fails with could not serialize access due to concurrent update, because the row changed after the snapshot, and the client must retry. At Serializable it fails the same way. The cheapest fix does not involve levels at all: let the database do the arithmetic, update counter set n = n + 1 where id = $1, which takes the row lock and reads the current value atomically. Whenever a read-modify-write can be one statement, make it one statement.

The retry experiment in the next article shows the lost update with eight workers: without retries on Serializable, six of eight transactions failed and the counter ended at 2.

Phantoms

A phantom read is when a transaction runs the same range query twice and gets a different set of rows because another transaction inserted a row that matches. The standard allows phantoms at Repeatable Read. PostgreSQL's snapshot isolation prevents them there, so the second query in the same transaction does not see the new row. MySQL prevents many of them with gap locks and allows others. If your code counts rows to decide whether to insert one more, as in "at most five bookings per slot", the check-then-insert is a write-skew pattern and needs a constraint, a lock on the slot or Serializable.

Choosing by scenario

A good default policy is to run at Read Committed, express invariants as constraints, and reserve Serializable for the handful of places that need it. A service that runs everything at Serializable pays for aborts everywhere and needs retries everywhere.

What the strict levels cost

Serializable in PostgreSQL uses predicate locks that do not block anyone; they only record read dependencies so conflicts can be detected. The overhead is memory for the lock table, some CPU, and a higher abort rate under contention. Transactions that touch many rows may be promoted to coarser locks, which increases false positives. The practical guidance from the PostgreSQL documentation is to keep serialisable transactions short, to retry on 40001, and to prefer read-only transactions for reporting. Aborts are not failures of the database; they are the mechanism, and a client that does not expect them is the thing that is wrong.

Putting it in a test

An isolation bug is invisible to a single-threaded test. The reliable way to test is to run two transactions on two connections with explicit ordering, exactly like the lab: open both, do the reads, then do the writes, then commit both, and assert the outcome. Use a pool of at least two connections, db.BeginTx for each, and channels or plain sequential code to fix the order. The test fails today at Read Committed and passes after the fix, which is the signature of a good regression test. Keep it in the integration tier, against the real database, since a fake cannot reproduce the behaviour of a real concurrency control implementation.

Also test the retry path: with Serializable, force a conflict and check that the code ran the transaction twice and ended with the correct state. The lab for retries is in retrying serialization failures.

ORMs and defaults

Frameworks and ORMs often set their own level or open transactions implicitly. Check what yours does: whether it wraps each request in a transaction, which level it uses and whether it retries on serialization failures. A library that retries silently can run non-idempotent code twice; one that does not retry turns a normal abort into a user-facing error. Read its documentation, and prefer an explicit wrapper such as the InTx function in the transaction article for anything that needs more than Read Committed.

Other ways to prevent the anomaly

Serializable is not the only tool, and it is not always the best.

• A constraint or a single statement. update oncall set on_call = false where doctor = $1 and (select count(*) from oncall where on_call) > 1 makes the check and the change atomic in one statement, though under Read Committed even that can interleave unless the rows are locked.
• Explicit locks. select ... for update on the rows you read makes the second transaction wait, at the cost of serialising access. Lock a single row that stands for the invariant, such as the ward, so both doctors queue.
• Advisory locks for rules that do not map to a row.
• Unique indexes and exclusion constraints for rules about overlap, such as two bookings of one room.

Pick the lightest tool that makes the invariant hold: a constraint if it fits, a lock if it is cheap, SERIALIZABLE if the logic is complicated, and write down in the code which tool protects which rule.

MySQL differs

InnoDB's default is Repeatable Read, with different mechanics: it uses next-key and gap locks, so some phantoms are blocked by locking, and select ... lock in share mode reads the latest committed data instead of the snapshot. Its Serializable level converts plain reads to locking reads, so it blocks instead of aborting; deadlocks and lock wait timeouts are the errors to retry. The same sequence of statements can therefore behave differently across the two databases, and a portable application must test on its production database. See connecting to MySQL for the setup. SQLite has one writer at a time, so write skew cannot happen the way it does here.

Pitfalls in Go

• The level applies per transaction, passed in BeginTx. A plain db.Query outside a transaction runs at the session default.
• Not every driver supports every level. database/sql returns an error if the driver rejects the option. pgx supports all three PostgreSQL levels; Read Uncommitted maps to Read Committed.
• Read-only transactions (ReadOnly: true) at Serializable can use a cheaper path in PostgreSQL (deferrable read-only), so reporting queries do not cause aborts.
• Long transactions at strict levels hold snapshot state and increase conflicts. Keep them short.
• Autocommit statements are their own transactions, each with a fresh snapshot, which is why the "other transaction" in scenario one could change the data between two reads.

Checking your own code

For each place where code reads, decides and writes in the same transaction, ask what happens if two requests run it together. If the answer is "an invariant breaks", write the test: two transactions on two connections with a channel to order the steps, as this lab does. The deterministic technique is explained in forcing interleavings in tests. Then choose a constraint, a lock or a serialisable transaction. The PostgreSQL manual's chapter on transaction isolation has the full table and the details of serialization failures, and the Go side is in the sql.TxOptions reference.