Two Updates, One Row, No Agreement
Two application servers receive the same customer action at the same instant. Both read the customer's balance, both add a credit, both write the result. One credit disappears. Neither server did anything wrong in its own view — each read a consistent value, each wrote a new one, and the database accepted both writes without complaint.
This is a lost update, and it is not an application bug in the usual sense. The application code was correct for a world with one writer. The database, by default, does not promise that world. It promises that each individual statement sees a consistent snapshot, and that promise is strictly weaker than what the code assumed. The gap between the two is the entire subject of transaction isolation levels.
Isolation levels are the contract between the application and the database about what concurrent activity a transaction is allowed to observe. The SQL standard defines four of them, and every serious database implements at least three. The practical question is not which level is "best" — it is what each level actually guarantees, because the guarantees are narrower than most developers believe, and the default level is not the safest one.
The Four Anomalies That Isolation Is Supposed to Prevent
The standard defines isolation levels by the anomalies they allow or prevent. Four anomalies matter in practice.
A dirty read is reading a row written by a transaction that has not committed yet. The danger is not the read itself — it is that the writing transaction may roll back, and the reader has already built logic on top of a value that never existed.
A non-repeatable read is reading the same row twice in one transaction and getting different values, because another transaction committed a change in between. The first read was of a committed value; so was the second. They simply disagree with each other.
A phantom read is the row-count version of the same problem: a query with a filter returns a different set of rows on its second execution, because another transaction inserted or deleted rows that match the filter between the two runs.
A lost update happens when two transactions read the same value, both modify it based on what they read, and the second write silently overwrites the first. No error is raised. No constraint is violated. The database simply keeps the last write, and the earlier transaction's work evaporates.
Each isolation level is a position on a spectrum: which of these four anomalies the database is allowed to let through. The safest level prevents all four. The weakest prevents only the first. Everything between is a trade.
Read Uncommitted: The Level Postgres Refuses to Ship
The weakest standard level, read uncommitted, permits dirty reads. A transaction at this level may see rows that another transaction has modified but not committed — including rows that will be rolled back moments later.
PostgreSQL does not implement it. It is not an oversight; it is an architectural consequence. PostgreSQL's concurrency control is built on snapshots taken at the moment a statement begins, and a snapshot by construction only contains rows that were committed before the snapshot was taken. There is no code path that shows a transaction an uncommitted row, because there is no code path that reads outside its snapshot.
Setting the level to read uncommitted in PostgreSQL is legal and silently equivalent to read committed. This is worth knowing because it changes how you read documentation and advice from other databases: in MySQL, read uncommitted is real and reachable; in PostgreSQL it is a label with no effect. Code that depends on seeing uncommitted data to work around a locking problem will not work here, and the fix is not to find a lower level — there is none.
Read Committed: The Default You Already Run On
Read committed is PostgreSQL's default, and it is the level most applications use without thinking. Each statement gets its own snapshot, taken when the statement starts. The statement sees every row committed before that moment, and nothing committed after.
The guarantee is per-statement, not per-transaction. Two SELECTs in the same transaction can return different results if another transaction commits between them. This is the source of non-repeatable reads, and for most applications it is invisible, because most transactions are short and most applications do not re-read the same row with the same filter inside one transaction.
Read committed also does not prevent lost updates. Two transactions can both read a row under read committed, both compute a new value, and both write it; the writes serialize, but the reads do not, and the earlier computation is discarded. This is the default behavior of almost every PostgreSQL installation, which means the opening scenario — two credits, one balance — is the ordinary outcome, not a misconfiguration.
The mitigation for lost updates under read committed is not a different isolation level. It is making the update itself conditional; the statement re-reads the row inside its own snapshot and computes from the current value, which closes the read-modify-write gap for this specific pattern:
UPDATE accounts
SET balance = balance + 50
WHERE account_id = 42;
The same trick extends to the upsert form, which atomically decides between insert and update based on the row state at statement time:
INSERT INTO counters (key, value)
VALUES ('visits', 1)
ON CONFLICT (key)
DO UPDATE SET value = counters.value + 1;
Neither form needs a transaction boundary to be safe against lost updates, because the conflict check and the write happen in one atomic statement. The moment the application splits the operation into a SELECT followed by a separate UPDATE, it is back on the read-modify-write path, and the isolation level decides what happens next.
Repeatable Read: The Snapshot That Outlives the Statement
Repeatable read is where the semantics change qualitatively. PostgreSQL implements it by taking the snapshot once, at the first statement of the transaction, and reusing it for the whole transaction. Every statement in the transaction sees the same world, including the same set of committed rows and the same versions of those rows.
Non-repeatable reads disappear: reading the same row twice in one transaction returns the same value, because there is only one snapshot. Phantoms are also prevented for the same reason — the row set is fixed when the snapshot is taken, so a filtered query returns the same rows every time, regardless of what other transactions commit in between.
Lost updates, however, are not prevented; they are detected. When a transaction at repeatable read tries to update a row that a concurrent committed transaction also updated, PostgreSQL raises a serialization failure instead of silently overwriting. The application receives an error and must retry the transaction. This is a crucial distinction: the anomaly is not allowed to pass silently, but the transaction is also not made safe automatically. Code that never handles a serialization failure will crash at exactly the moment the data was about to be corrupted.
Applications that genuinely need repeatable-read semantics — reporting queries that must aggregate a consistent point-in-time view, or batch jobs that read a stable row set while users keep writing — should set the level deliberately and add retry logic around serialization failures, because under the default level those queries quietly mix data from different moments.
Serializable: Where the Database Starts Saying No
Serializable is the strongest standard level: transactions must behave as if they ran one after another, in some order, even though they actually ran concurrently. PostgreSQL implements it with serializable snapshot isolation, which runs transactions under repeatable-read snapshots and tracks read-write dependencies between them. When the dependency graph gains a cycle — meaning the concurrent schedule could not be linearized — the database aborts one of the transactions with a serialization failure.
The practical shape is identical to repeatable read plus stricter failure detection. The application still must retry aborted transactions; the difference is that the set of abortable schedules is larger, because serializable also catches interleavings where two transactions read and write overlapping ranges in ways that could not happen in any serial order.
The honest trade is throughput. Serializable transactions abort more often, and every abort forces the application to replay work. Databases that default to serializable are choosing correctness over concurrency; PostgreSQL defaults the other way, and the operator who wants serializable behavior must set it per session or per transaction and build the retry loop that makes it livable.
Choosing serializable is right when the correctness of a business invariant depends on the absence of any interleaving: seat allocation, inventory decrements, balance transfers across accounts. For those cases, the retry loop is cheap next to the audit that follows a silently lost update.
Choosing a Level Without Guessing
The four levels form a practical decision tree, and the answer is usually determined by two questions: does the transaction re-read data it has already read, and does it compute a new value from a value it read?
For short transactions that read once and write once, read committed is correct, and the update should use the atomic conditional forms that close the read-modify-write gap. For transactions that read the same rows or the same filtered set more than once and must see a consistent world, repeatable read is the level — with a retry handler around serialization failures. For transactions whose interleaving with other transactions would corrupt a business invariant, serializable is the level — with the same retry handler, applied more often.
Set the level in the connection string or session configuration, not by sprinkling per-statement hints, because the level is a property of the transaction, and a transaction is a property of the connection:
BEGIN;
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- all statements in this transaction share one snapshot
COMMIT;
A mixed application where different transactions run at different levels is correct only if every code path sets its own level before the first statement of its transaction; the moment one path forgets, it silently inherits the default.
A Test That Shows Which Level You Are On
The fastest way to internalize the difference is to observe it. Two sessions, one table, one row, and a deliberate pause between read and write demonstrate each guarantee directly.
In session one, begin a transaction and read a row. In session two, update that row and commit. In session one, read again. Under read committed, the second read shows the new value; under repeatable read, it shows the old one — the snapshot wins. Run the same sequence with both sessions updating the same row from a read value, and the second commit under repeatable read raises a serialization failure, while under read committed it silently succeeds and discards the first update.
That contrast — an error versus silence — is the whole point of moving up the isolation spectrum. The database cannot make concurrent logic correct; it can only refuse to execute interleavings that would break the contract. Lower levels let the interleaving happen and keep the result. Higher levels abort the transaction and ask the application to try again. The isolation level you choose is not a performance knob. It is a statement about whether the database is allowed to be quietly wrong, and the application's retry loop is the price of answering no.
Originally published on Dispatch.
Top comments (0)