Transaction Isolation Levels, Write Skew and SSI

Transaction isolation levels decide what one transaction can see of another's work while both are running. Ideally, concurrent transactions would behave as if they ran one after another. Enforcing that is expensive, so the SQL standard defines four levels, each allowing a different set of anomalies in exchange for more concurrency.

Most bugs here come from not knowing which anomalies your current level still allows. This article covers each anomaly, how PostgreSQL 16 implements each level, and write skew: the anomaly that row locks don't catch and the main reason SERIALIZABLE exists.

The anomalies that transaction isolation levels allow

Dirty read. You read data another transaction hasn't committed. If it rolls back, you acted on a value that never existed.

Non-repeatable read. You read the same row twice and get different values, because another transaction updated it and committed in between.

Phantom read. The same query, such as WHERE amount > 100, returns a different set of rows the second time, because another transaction inserted or deleted matching rows.

Lost update. Two transactions read the same row, both compute a new value in application code, and both write it back. The second write silently overwrites the first:

-- T1 and T2 both run this at the same time; balance starts at 100
SELECT balance FROM accounts WHERE id = 1;       -- both see 100
-- app computes 100 + 50 (T1) and 100 - 30 (T2)
UPDATE accounts SET balance = 150 WHERE id = 1;  -- T1
UPDATE accounts SET balance = 70  WHERE id = 1;  -- T2 wins, T1's deposit is gone

Write skew. Two transactions read the same set of rows, each makes a decision that is valid on its own, and each writes a different row. Together, the writes break a rule that neither broke alone.

Transaction isolation levels in the SQL standard and PostgreSQL

The standard defines levels by which of the first three anomalies they forbid. PostgreSQL is stricter than the standard at two levels:

Read Committed

Each statement sees a fresh snapshot of data committed before it started, so two SELECTs in one transaction can disagree.

When an UPDATE or DELETE finds a row that a concurrent transaction is changing, it waits for that transaction to finish. Then it re-checks its WHERE clause against the newest version of the row. That is why a single conditional statement such as UPDATE ... SET balance = balance - 80 WHERE balance >= 80 is safe here, while a separate read and write is not.

Repeatable Read

PostgreSQL implements this level as snapshot isolation. The transaction takes one snapshot at its first statement (not at BEGIN) and sees only that snapshot until it ends. Re-running a query gives the same rows, so phantoms don't appear in reads either.

If a Repeatable Read transaction tries to update or lock a row that another transaction changed and committed after the snapshot was taken, PostgreSQL doesn't overwrite it. It raises:

ERROR:  could not serialize access due to concurrent update

That error prevents the lost update above, but your application has to catch it and retry the whole transaction. Snapshot isolation does not prevent write skew, because there the two transactions never touch the same row. (MySQL's InnoDB also defaults to Repeatable Read, with different semantics, so test rather than assume.)

Write skew: the anomaly row locks don't catch

Take a bank that lets a customer hold a savings and a checking account. The rule: either account may go negative, but the sum of both must never drop below zero.

CREATE TABLE accounts (
  id          bigint PRIMARY KEY,
  customer_id bigint NOT NULL,
  kind        text   NOT NULL CHECK (kind IN ('savings', 'checking')),
  balance     numeric(14, 2) NOT NULL
);

-- customer 7 holds 100 in each account, 200 in total

Two withdrawals of 200 arrive at once, one from each account. Each checks the rule, then withdraws:

BEGIN ISOLATION LEVEL REPEATABLE READ;
SELECT sum(balance) FROM accounts WHERE customer_id = 7;  -- 200, rule OK
UPDATE accounts SET balance = balance - 200
 WHERE customer_id = 7 AND kind = 'savings';              -- the other one uses 'checking'
COMMIT;

Here is how they interleave:

The customer now has -200 in total. Each transaction read a consistent snapshot where the total was 200, and each decision was correct in isolation. Combined, they break the rule.

Notice what didn't help:

• Repeatable Read. A writes savings and B writes checking. Since they update different rows, the "concurrent update" check never fires.
• SELECT ... FOR UPDATE on the row you're about to change. A locks savings and B locks checking. The locks never conflict.
• An atomic conditional update. WHERE balance >= 200 only looks at one row. The rule spans two.

The same shape appears elsewhere: two doctors going off call because each saw the other was on, or two bookings for the same room slot. In the booking case the conflicting row doesn't exist yet, so there's nothing to lock.

How PostgreSQL's SSI stops write skew

At SERIALIZABLE, PostgreSQL uses Serializable Snapshot Isolation (SSI). Instead of heavy read locks that make readers and writers block each other, it works like this:

1. Every transaction still runs on its own snapshot, exactly like Repeatable Read. Reads and writes don't block each other.
2. PostgreSQL also records what each transaction read, using predicate locks (SIReadLock in pg_locks). These block no one. They're bookkeeping.
3. When a transaction writes data that a concurrent transaction read, PostgreSQL records a read-write dependency between them.
4. Every serialization anomaly contains a specific pattern: a transaction with both an incoming and an outgoing read-write dependency among concurrent transactions. When PostgreSQL detects that "dangerous structure", it cancels one of the transactions involved.

Run the savings/checking example with BEGIN ISOLATION LEVEL SERIALIZABLE. The first transaction commits, and the other fails:

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

On retry, it sees a total of 100, the rule check fails, and the withdrawal is correctly rejected.

The check is conservative: it can abort transactions that would have been fine, but it never lets a real anomaly through. The error (SQLSTATE 40001) can arrive on any statement or on COMMIT.

Using SERIALIZABLE correctly

• Every transaction involved must run at SERIALIZABLE. A Read Committed transaction writing the same tables can still create anomalies.
• Retry the whole transaction on 40001, and treat deadlocks (40P01) the same way.
• Keep transactions short. Long ones get cancelled more often.
• Declare read-only work. SERIALIZABLE READ ONLY DEFERRABLE lets a long report wait for a safe snapshot instead of risking cancellation.
• Index your predicates. A sequential scan takes a predicate lock on the whole table, which means more false positives. See the practical guide to database indexing.
• Don't act on data read inside a serializable transaction until it commits.

A retry wrapper in Go 1.22 with pgx v5:

package txutil

import (
	"context"
	"errors"
	"fmt"
	"math/rand/v2"
	"time"

	"github.com/jackc/pgx/v5"
	"github.com/jackc/pgx/v5/pgconn"
	"github.com/jackc/pgx/v5/pgxpool"
)

func isRetryable(err error) bool {
	var pgErr *pgconn.PgError
	return errors.As(err, &pgErr) &&
		(pgErr.Code == "40001" || pgErr.Code == "40P01")
}

// Serializable runs fn in a SERIALIZABLE transaction and retries on
// serialization failures. fn must not have side effects outside the database.
func Serializable(ctx context.Context, pool *pgxpool.Pool, fn func(pgx.Tx) error) error {
	const maxAttempts = 5
	opts := pgx.TxOptions{IsoLevel: pgx.Serializable}

	var err error
	for attempt := range maxAttempts {
		err = pgx.BeginTxFunc(ctx, pool, opts, fn)
		if !isRetryable(err) {
			return err
		}
		backoff := time.Duration(1<<attempt) * 10 * time.Millisecond
		jitter := time.Duration(rand.Int64N(int64(backoff)))
		select {
		case <-ctx.Done():
			return ctx.Err()
		case <-time.After(backoff + jitter):
		}
	}
	return fmt.Errorf("serializable transaction failed after %d attempts: %w", maxAttempts, err)
}

Because fn can run more than once, keep HTTP calls and queue publishes out of it and do them after commit.

Alternatives when SERIALIZABLE is too expensive

When write skew is confined to one known rule, there are cheaper fixes than retries everywhere:

• Materialize the conflict. Keep the customer's total in one row that every withdrawal must also update, so ordinary row locking catches the conflict.
• Lock a parent row such as the customer with FOR UPDATE before checking the rule.
• Let a constraint enforce the rule. CHECK, UNIQUE, partial unique indexes and EXCLUDE hold no matter how transactions interleave.

These are covered with working code in preventing race conditions in payment systems.

Choosing a transaction isolation level

A sensible setup is Read Committed by default, raising the level per transaction only where a multi-row rule needs it. For how isolation relates to MVCC row versions, see ACID and MVCC explained.

FAQ

What is the default transaction isolation level in PostgreSQL?

Read Committed. You can change it per transaction with BEGIN ISOLATION LEVEL ..., or globally with the default_transaction_isolation setting.

Does Repeatable Read prevent phantom reads in PostgreSQL?

For reads, yes. PostgreSQL's Repeatable Read uses one snapshot for the whole transaction, so re-running a query returns the same rows. It still allows write skew, including write skew caused by rows another transaction inserts.

What is the difference between a lost update and write skew?

In a lost update, two transactions write the same row and one overwrites the other. Row locks or Repeatable Read catch it. In write skew, they write different rows based on a shared read. Only Serializable, a materialized conflict, a parent lock or a constraint prevents it.

Does SELECT FOR UPDATE prevent write skew?

Only if you lock every row the decision depends on, or one parent row that all such transactions lock. It can't lock rows that don't exist yet.

Is SERIALIZABLE isolation slow in PostgreSQL?

It doesn't make readers and writers block each other. The costs are bookkeeping and retried transactions. For short transactions with indexed access, that is often acceptable, but measure it on your workload.

Takeaway

1. Know which anomalies your level still allows. At PostgreSQL's default, that's everything except dirty reads.
2. Replace read-then-write in application code with single conditional statements where you can.
3. For each rule that spans rows, decide what protects it: a constraint, a materialized row, a parent lock, or SERIALIZABLE.
4. If you use SERIALIZABLE, run every participating transaction at that level, inside a retry loop.

If you'd like a second pair of eyes on a transactional design, Vectorkub builds and reviews backend systems like these.