Skip to content

Transactions and isolation levels

A transaction makes several steps commit or fail as one. Its isolation level sets what it may see of other transactions running at the same time, trading anomalies such as stale rereads against blocking and retries.

On this page

What it is

A transaction groups several statements into one unit that either takes effect completely or not at all. The database is meant to hide its partial state from other transactions while it runs (the isolation levels below set how firmly); when it commits, its changes become visible together, and a transactional database writes them to permanent storage before it reports success. In SQL you open one with BEGIN, finish with COMMIT, or cancel the updates made so far with ROLLBACK. The textbook case is a transfer: debit one account, credit another, so a crash between the two doesn't leave money created or destroyed.

Isolation is the I in ACID. It decides how much of one transaction's work another running transaction may observe. A lower level lets more transactions touch the same data at once but exposes them to more concurrency effects; a higher level removes those effects at the price of more resources and more chance that one transaction blocks another. The SQL standard defines serializable as behaving like running the transactions one at a time, and the other three levels by which of three read phenomena they must rule out:

  • Dirty read: reading a row another transaction has changed but not yet committed.
  • Non-repeatable read: reading the same row twice and finding a committed change in between.
  • Phantom read: running the same search twice and finding that rows have appeared or disappeared because another transaction committed in between.
Level (SQL standard)Dirty readNon-repeatable readPhantom read

Read uncommitted

possible

possible

possible

Read committed

ruled out

possible

possible

Repeatable read

ruled out

ruled out

possible

Serializable

ruled out

ruled out

ruled out

Databases reach these levels in two broad ways. A lock-based engine makes a conflicting transaction wait for a lock, and the level decides how long locks are held: at read committed a read lock is released as soon as the SELECT finishes, while at repeatable read read and write locks stay until the transaction ends. A multiversion engine (MVCC) has each query read a snapshot taken at some point, per statement at read committed in PostgreSQL 17 and per transaction at repeatable read, and lets writers commit without waiting for readers. At serializable, an MVCC engine adds checks and rolls back a transaction whose commit would match no serial order, reporting a serialization failure. The standard sets a floor, not a ceiling, so real engines are often stricter than the table above. In PostgreSQL 17, repeatable read also rules out phantom reads, and asking for read uncommitted gets you read committed behavior, because that is the natural mapping onto its MVCC design.

The anomaly the three phenomena miss is write skew, the simplest serialization anomaly: two transactions each read an overlapping set of rows, each decides what to write based on that read, and each writes rows the other didn't touch. Both commits succeed under repeatable read, yet the combined result could not have come from running them one after the other. The PostgreSQL wiki's overdraft example shows it: a bank lets a customer withdraw up to the total across all accounts, two sessions each check that two accounts of 500 add up to 1000, and each withdraws 900 from a different account. Under serializable, PostgreSQL detects the dangerous read/write pattern, lets the first committer win and rolls the other back so the application can retry it. PostgreSQL 17 implements this as Serializable Snapshot Isolation, which adds anomaly checks on top of the repeatable read snapshot without adding blocking.

Defaults differ by product, which matters when you say "the database handles it". PostgreSQL 17 runs transactions at read committed unless told otherwise, where each statement sees data committed before that statement began. MySQL 8.4's InnoDB engine offers all four standard levels and defaults to repeatable read, and lets you change the level for a session, for all new connections, or for the whole server.

When to use it

Use a transaction whenever two or more writes must land together: an order row and a stock decrement, a transfer out and a transfer in, a parent row and its children. For the common case where each statement changes a predetermined row relative to its current value, such as UPDATE accounts SET balance = balance + 100 WHERE acctnum = 12345, read committed does the right thing in PostgreSQL 17: a second updater waits for the first to commit, then applies its change to the new version of the row after rechecking the WHERE clause. It's fast, simple, and adequate for many applications.

Move up to repeatable read when one transaction reads several times and needs those reads to agree, such as a report that totals one table and then reads detail rows, or a multi-step calculation. In PostgreSQL 17 the snapshot is taken at the first statement after BEGIN that isn't itself transaction control, so later statements don't see concurrent commits; the cost is that an update to a row another transaction changed since the snapshot fails with a serialization error you have to retry.

Move up to serializable when a decision depends on a read of data that another transaction may be changing at the same time: an invariant across several rows (a customer's total balance, the number of seats left in a section, at least one person on call). Build the retry loop at the same time, since a serializable transaction may be rolled back and any results it read must be thrown away and taken from the retry instead. The PostgreSQL documentation's advice for keeping that cheap: declare read-only transactions READ ONLY, cap active connections with a pool, keep each transaction to what integrity needs, and don't leave connections idle inside a transaction.

When not to

Don't set everything to serializable as a default. The anomaly monitoring has overhead, contention produces rollbacks, and callers then need retry logic. The PostgreSQL 17 documentation notes that read committed is sufficient for many applications, and that at repeatable read it's the updating transactions that may need a retry. For a single-statement update on a known row, the default level with a careful WHERE clause is enough.

Don't use a higher level when a narrower tool fits. If the contention is on one or two known rows, SELECT ... FOR UPDATE locks just those rows and lets the rest of the transaction stay at the default; see Optimistic and pessimistic locking. And going down can be right too: MySQL 8.4's documentation suggests read committed or even read uncommitted for bulk reporting, where exact and repeatable results matter less than avoiding lock overhead.

An isolation level is a setting on one database session or server, so it does nothing for a flow that spans two services with their own databases. Reserving stock in one service and charging a card in another needs a different design: Sagas and two-phase commit cover that, and Idempotency keys keep a retried step from being applied twice.

Compared

LevelWhat a read seesStill possibleCostWhen it's the right pick

Read uncommitted

Other transactions' uncommitted writes

Dirty reads, non-repeatable reads, phantoms

Least locking

Bulk reporting where approximate is fine (MySQL 8.4 docs); PostgreSQL 17 treats it as read committed

Read committed

Data committed when each statement started

Non-repeatable reads, phantoms

Low; PostgreSQL 17 default

Single-statement changes to known rows; the simple, common case

Repeatable read

A row reread in the transaction returns the same value (one transaction-wide snapshot in PostgreSQL 17)

Phantoms per the standard (not in PostgreSQL 17); write skew

Updates to rows changed since the snapshot fail and need a retry (PostgreSQL 17); MySQL 8.4 InnoDB default

Several reads that must agree; reports; multi-step calculations

Serializable

As if transactions ran one at a time

Nothing, but a transaction may be rolled back

Monitoring overhead plus retries

Invariants across rows; write skew; when correctness beats throughput

In a picture

Two accounts: 500eachTxn A reads total1000Txn B reads total1000

Step 1 of 3

Two transactions read the same rows

A customer has two accounts of 500. Transactions A and B each read both rows and see a total of 1000, enough to cover a 900 withdrawal.

How to say it in an interview

The common mistake

Quiz

A transaction reads a row, another transaction updates that row and commits, and the first transaction reads the same row again and sees the new value. Which anomaly is this?
Two transactions each read a customer's two accounts, see a total of 1000, and each withdraws 900 from a different account. Both commit under repeatable read. What happened, and which level catches it?
Your serializable transaction fails with a serialization error. What should the application do?
A transaction runs one statement: UPDATE accounts SET balance = balance + 100 WHERE id = 1. Which level is enough?

Questions that use this

Related

Sources

  1. PostgreSQL 17 documentation: 13.2. Transaction Isolation
  2. PostgreSQL 17 documentation: 3.4. Transactions
  3. MySQL 8.4 Reference Manual: InnoDB Transaction Isolation Levels
  4. Wikipedia: Isolation (database systems)
  5. PostgreSQL wiki: SSI (Serializable Snapshot Isolation)
Sign in to report a mistake