A transaction is a unit of work that either fully happens or does not

Transactions give four guarantees, conventionally abbreviated ACID.

Atomicity: all statements in the transaction take effect, or none do. A failure partway leaves no trace of the partial work. Consistency: the transaction moves the database from one valid state to another, where "valid" means satisfying every declared constraint. Note this is about declared constraints — the database cannot know your business rules unless you express them. Isolation: concurrent transactions do not see each other's incomplete work. How completely they are isolated is the isolation level, and it is the interesting part. Durability: once a commit is acknowledged, the change survives a crash. This is what write-ahead logging provides: the change is written to a durable log before the commit returns.

The first three are relatively easy to state. Isolation is where the real complexity is, because full isolation is expensive and every database offers you a choice.

The anomalies

Isolation levels are defined by which anomalies they permit.

Dirty read: seeing another transaction's uncommitted changes. If that transaction rolls back, you acted on data that never existed.

Non-repeatable read: reading a row twice in one transaction and getting different values, because another transaction committed an update in between.

Phantom read: running the same query twice and getting different rows, because another transaction inserted or deleted rows matching your predicate.

Lost update: two transactions read a value, each computes a new value from it, and both write. The second overwrites the first, and the first update is silently gone. This is the anomaly most application bugs actually are.

Write skew: two transactions each read an overlapping set, each check a condition that holds, and each write — and the combination violates an invariant neither transaction could see being broken. The classic example is an on-call rota: two doctors each check that another is on duty, each sees the other, and each goes off duty. Both checks were valid; the result is nobody on call. This is the anomaly that survives at repeatable read and surprises people most.

The levels

Read uncommitted permits dirty reads. In PostgreSQL it behaves as read committed — there is no way to see uncommitted data at all.

Read committed is PostgreSQL's default. Each statement sees a snapshot taken at the moment that statement began, so it never sees uncommitted data, but two statements in the same transaction can see different data. Non-repeatable reads and phantoms are both possible.

There is a subtlety worth knowing: under read committed, if an UPDATE finds a row that another transaction has modified and committed since the statement started, it re-reads that row and re-evaluates its WHERE clause against the new version. This prevents some lost updates but not all, and it can produce genuinely surprising results in a statement whose condition depends on the value being changed.

Repeatable read gives the whole transaction one snapshot, taken at its first statement. Every read sees the same data, so non-repeatable reads and phantoms disappear. In PostgreSQL this is implemented as snapshot isolation, which is stronger than the standard requires — but it still permits write skew, because two transactions can read overlapping data and write disjoint rows without either seeing the other. If a transaction tries to update a row another transaction has already changed, it gets a serialization failure and must retry.

Serializable guarantees the outcome is equivalent to some serial ordering of the transactions. PostgreSQL implements this with serializable snapshot isolation, which monitors read/write dependencies between transactions and aborts one when a cycle would produce a non-serializable outcome. This eliminates write skew. The cost is more aborted transactions under contention, plus tracking overhead — and, critically, every transaction must be prepared to be retried.

Retry is not optional

This is the part most often missed. At repeatable read and serializable, a transaction can fail with a serialization error through no fault of its own — it did nothing wrong, it simply lost a race. The database is telling you to run it again.

So any code using these levels needs a retry loop: catch the serialization failure, and re-execute the whole transaction from the beginning. Not just the failed statement — the entire transaction, because its earlier reads are now stale.

For that to be safe, the transaction body must be idempotent with respect to the outside world: it must not have sent an email, charged a card, or published a message before the commit. Side effects belong after a successful commit, or behind an outbox pattern. A retry loop around a transaction that already sent the email sends it twice.

Retries also need a bounded count and backoff, because retrying forever under sustained contention makes the contention worse.

Locking, when snapshots are not enough

Snapshot isolation prevents you from seeing inconsistent data. It does not by itself stop two transactions making conflicting decisions.

SELECT ... FOR UPDATE takes a row lock, so a second transaction attempting the same lock waits. This is the standard fix for a lost update in read committed: read the row with FOR UPDATE, compute, write, commit. The lock serialises the read-modify-write.

SKIP LOCKED makes a queue work: several workers each claim different rows rather than queueing behind one another. NOWAIT fails immediately rather than waiting, which suits interactive code that should report contention rather than hang.

Advisory locks let you lock something that is not a row — a business process, a scheduled job — using an arbitrary key.

But the best fix for a lost update is usually neither: express the change as a single atomic statement. UPDATE accounts SET balance = balance + 100 WHERE id = 1 has no read-modify-write window at all, because the read and write happen inside one statement. Where the new value can be computed from the old in SQL, that is both simpler and faster than locking.

Deadlocks and long transactions

A deadlock is two transactions each holding a lock the other wants. The database detects the cycle and aborts one with a deadlock error. The durable fix is consistent lock ordering — if every transaction acquires locks in the same order, a cycle cannot form. Since deadlocks are also possible under normal operation, the retry loop handles them too.

Long-running transactions are the operational hazard that catches teams out. Under MVCC, an old row version cannot be cleaned up while any transaction might still need it. So a single forgotten transaction — a leaked connection, an idle psql session, a stuck job — holds back vacuum database-wide, and bloat accumulates for as long as it lives. It does not matter that the transaction is idle; what matters is that it is open. Monitoring the oldest transaction age is one of the highest-value database alerts there is.

The related discipline: keep transactions short, never hold one open across a network call to an external service, and never open one and wait for user input.

Worked example: two SELECT-then-UPDATE lose ten dollars

Account 7 starts at $100. Two checkouts each subtract $10 under read committed.

implementation	final balance
T1 and T2 SELECT 100, then UPDATE SET balance = 90	$90
UPDATE SET balance = balance - 10	$80
SELECT FOR UPDATE, then write	$80

The lost update is silent. An atomic expression, or a row lock, closes the window; a second SELECT does not.