Transaction isolation levels: the anomalies each level allows

The overnight report shows eleven reservations for a ten-seat event. Nobody wrote a bug on purpose. The handler counts the reservations, compares the count to the capacity, and inserts a row if there is room. Two customers clicked in the same hundred milliseconds, both counts said nine, both inserts went through, and the database did exactly what each session asked.

The first suggestion in the incident channel is "raise the isolation level". That treats isolation as a dial with "safe" at one end. It is closer to a menu. Each level is a list of concurrency anomalies you agree to tolerate in exchange for throughput, and the default level on every mainstream database tolerates most of them.

Five anomalies, one running example

Keep the example fixed: an events row with capacity = 10, a reservations table with one row per seat taken, and two sessions, A and B, arriving for the last seat.

Dirty read. A inserts a reservation and has not committed. B's count already includes it, so B turns a customer away. Then A rolls back. B made a decision on a row that never existed. Only the standard's Read Uncommitted permits this, and PostgreSQL does not implement it.

Non-repeatable read. B reads the event's capacity and sees 10. An organizer in session A lowers it to 8 and commits. B reads the same row again inside the same transaction and sees 8. A row B had already read changed underneath it.

Phantom read. B counts nine reservations. A commits a tenth. B runs the same count again in the same transaction and gets ten. No row B read was modified; a new row appeared that matches the predicate.

Lost update. Store the free seats in a seats_left column and decrement it. A reads 2, B reads 2, both compute 1, both write 1. Two reservations were sold and the column moved by one. Any read-modify-write where the application computes the new value is exposed to this.

Write skew. Both sessions count nine, both see room, both insert, both commit. Neither wrote a row the other read, so there is no row conflict for the database to notice. The invariant "reservations never exceed capacity" spans two rows and lived only in the application's head. Each session saw a perfectly consistent snapshot, and the combination broke the rule. This is the eleven-reservation report.

Two sessions, one seat left

Write skew commits at Repeatable Read

capacity 10, reserved 9

1. A BEGIN
2. A count = 9
3. B BEGIN
4. B count = 9
5. A INSERT ana
6. A COMMIT ok
7. B INSERT ben
8. B COMMIT ok
9. 11 of 10 seats

Neither session wrote a row the other one read, so no row conflict exists for the database to detect.

What each level lets through

Dirty readNon-repeatable readPhantom readLost updateWrite skew

Read Uncommitted

PostgreSQL runs it as Read Committed

Dirty read: allowed

Non-repeatable read: allowed

Phantom read: allowed

Lost update: allowed

Write skew: allowed

Read Committed

the PostgreSQL default

Dirty read: prevented

Non-repeatable read: allowed

Phantom read: allowed

Lost update: allowed

Write skew: allowed

Repeatable Read

one snapshot per transaction

Dirty read: prevented

Non-repeatable read: prevented

Phantom read: prevented in PostgreSQL

Lost update: prevented

Write skew: allowed

Serializable

SSI, retry on SQLSTATE 40001

Dirty read: prevented

Non-repeatable read: prevented

Phantom read: prevented

Lost update: prevented

Write skew: prevented

can commit cannot happen standard allows it, PostgreSQL does not

What each level promises, and what PostgreSQL does

The SQL standard defines four levels by which of the first three anomalies they forbid: Read Uncommitted forbids none, Read Committed forbids dirty reads, Repeatable Read also forbids non-repeatable reads, and Serializable forbids phantoms too. Lost update and write skew are not on the standard's list. The 1995 critique of the ANSI isolation levels by Berenson and colleagues added them, because the three named anomalies were never the whole story.

PostgreSQL's transaction isolation documentation describes an implementation that is stricter than the standard in some places and surprising in others:

• Read Committed is the default. Every statement sees a snapshot of what had committed when that statement started, so a transaction with two counts can get two answers.
• Repeatable Read is one snapshot for the whole transaction, taken at its first statement. That rules out phantoms as a side effect, which the standard would allow. If the transaction tries to update a row another transaction changed and committed after the snapshot, PostgreSQL aborts it with "could not serialize access due to concurrent update", so the lost update becomes an error instead of a silent overwrite. Write skew still commits.
• Serializable is Repeatable Read plus serializable snapshot isolation. The database tracks read/write dependencies between concurrent transactions and aborts one when the pattern could not have arisen in any serial order. The abort surfaces as SQLSTATE 40001, and the documentation is explicit that applications using this level must be prepared to retry.
• Read Uncommitted is accepted and behaves as Read Committed.

SQL Server takes the other road. Its Read Committed takes shared locks unless READ_COMMITTED_SNAPSHOT is on, so readers wait for writers, and its Repeatable Read and Serializable are lock-based, so the same conflicts surface as blocking and deadlocks rather than serialization failures. Its SNAPSHOT level is the one that behaves like PostgreSQL's Repeatable Read.

An isolation level is not a safety dial. It is the list of anomalies you have agreed to tolerate, and the default level agrees to most of them.

Watching write skew commit

Nine reservations exist. Both sessions run at Repeatable Read, the level many teams believe is safe enough:

/* A */ BEGIN ISOLATION LEVEL REPEATABLE READ;
/* A */ SELECT count(*) FROM reservations WHERE event_id = 7;   -- 9

/* B */ BEGIN ISOLATION LEVEL REPEATABLE READ;
/* B */ SELECT count(*) FROM reservations WHERE event_id = 7;   -- 9

/* A */ INSERT INTO reservations (event_id, customer_id) VALUES (7, 'ana');
/* A */ COMMIT;                                                 -- ok

/* B */ INSERT INTO reservations (event_id, customer_id) VALUES (7, 'ben');
/* B */ COMMIT;                                                 -- ok: 11 rows for 10 seats

Change both BEGIN lines to SERIALIZABLE and session B fails instead. In an example run the error reads:

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.

That is the whole contract. Serializable did not make B wait for A. It let both transactions run and then refused to commit a history with no serial equivalent. The refusal is the feature. What happens next is your code's problem.

Serializable in EF Core means writing the retry

EF Core exposes the level through BeginTransactionAsync. It does not supply the retry. A 40001 raised inside SaveChangesAsync arrives wrapped in a DbUpdateException; one raised during a query arrives as the raw PostgresException, so the filter checks both:

using System.Data;
using Microsoft.EntityFrameworkCore;
using Npgsql;

public sealed class ReservationService(EventsDbContext db)
{
    public async Task<bool> TryReserveAsync(
        int eventId,
        Guid customerId,
        CancellationToken cancellationToken)
    {
        const int maxAttempts = 3;

        for (var attempt = 1; ; attempt++)
        {
            await using var transaction = await db.Database.BeginTransactionAsync(
                IsolationLevel.Serializable,
                cancellationToken);

            try
            {
                var capacity = await db.Events
                    .Where(e => e.Id == eventId)
                    .Select(e => e.Capacity)
                    .SingleAsync(cancellationToken);

                var reserved = await db.Reservations
                    .CountAsync(r => r.EventId == eventId, cancellationToken);

                if (reserved >= capacity)
                {
                    return false;
                }

                db.Reservations.Add(new Reservation
                {
                    EventId = eventId,
                    CustomerId = customerId
                });

                await db.SaveChangesAsync(cancellationToken);
                await transaction.CommitAsync(cancellationToken);
                return true;
            }
            catch (Exception ex) when (IsSerializationFailure(ex) && attempt < maxAttempts)
            {
                db.ChangeTracker.Clear();
            }
        }
    }

    private static bool IsSerializationFailure(Exception ex) =>
        ex is PostgresException { SqlState: PostgresErrorCodes.SerializationFailure }
        || ex.InnerException is PostgresException { SqlState: PostgresErrorCodes.SerializationFailure };
}

Two details carry the correctness. ChangeTracker.Clear() drops the Reservation the failed attempt added, otherwise the retry inserts it twice. And the attempt counter lives in the when filter, so the last failure propagates instead of being swallowed. If the context uses EnableRetryOnFailure, the execution strategy refuses a user-initiated transaction; wrap the whole body in CreateExecutionStrategy().ExecuteAsync instead of hand-rolling the loop.

What the levels cost

The bill for stricter isolation is not paid in CPU inside the database. It arrives as retries and waits:

• Retries repeat the whole unit of work. Fifty customers hitting one hot event in the same second means one winner, forty-nine transactions that ran to commit and failed, and a second round in which most of them fail again. Work grows with contention, and contention is exactly when latency matters.
• Sequential scans widen the conflict. Serializable tracks what each transaction read. A count that scanned the whole reservations table registers a predicate lock on the table, so a commit for a different event can abort yours. Index the predicate or the false-positive rate climbs.
• Lock-based levels pay in waiting. On SQL Server, or with SELECT ... FOR UPDATE, sessions queue on the row while holding a pooled connection. If the locked section takes 5 ms, one event row can hand out at most 200 reservations per second, and every queued request is a lease the connection pool cannot give to anyone else.

The fixes that usually win

Most web workloads do not need Serializable. They need one invariant protected at one row, and there are three cheaper ways to do that.

A guard in the UPDATE. Keep the counter in the row and let the database compare:

var rows = await db.Events
    .Where(e => e.Id == eventId && e.SeatsLeft > 0)
    .ExecuteUpdateAsync(
        setters => setters.SetProperty(e => e.SeatsLeft, e => e.SeatsLeft - 1),
        cancellationToken);

return rows == 1;

One statement at Read Committed, nothing to retry, and it cannot oversell, because PostgreSQL re-checks the WHERE clause against the newest committed version of the row before writing. The ExecuteUpdate guide covers what that call skips.

A concurrency token. Give the row a version, send it back with the update, and treat zero affected rows as a conflict. That is optimistic concurrency in EF Core, and it protects a read-modify-write that will not fold into one expression.

A row lock on the parent. SELECT ... FOR UPDATE on the event row serializes every reservation for that event and nothing else. Fine for a genuinely rare hot row; a queue if every request touches it.

Serializable earns its place when the invariant spans rows you cannot fold into one statement and you have written the retry. Both conditions, not one.

Practicing on a real database

You cannot learn this from the C#. The broken handler and the fixed one read as equally reasonable, and the difference lives in what two sessions do to each other. The instinct comes from watching the SQL your code sends and asking what a second session would see between those statements. Katabench's EF Core practice track grades LINQ against real PostgreSQL, with the actual query plans rather than an in-memory stand-in, so the habit of reading your own SQL gets built on the database that will run it; the track overview lists what each track checks. The eleven-reservation report is cheaper to read on a practice run than on a Monday morning.