Read committed vs repeatable read

Read committed and repeatable read are the isolation levels most production databases run: PostgreSQL, Oracle and SQL Server default to the first, MySQL’s InnoDB to the second. In a multi-version engine the difference fits in a line: a fresh snapshot per statement, or one per transaction. What each engine does when your write meets someone else’s newer row differs far more than the names suggest, and that is where migrations and payment code go wrong.

Picture it first: the live screen and the printed statement

You spend twenty minutes with a bank officer on a home loan while your salary lands and a UPI autopay goes out.

• Officer R checks the live screen for every question. Answers are current, but your balance and this month’s debits can straddle the salary credit and not add up. That is read committed.
• Officer S prints a statement at your first question and answers from it. Answers agree, though they age. That is repeatable read.

Then you say “withdraw ₹5,000 if my balance is still above ₹20,000”. Money moves on the live account. Officer R re-checks the condition on the live screen and proceeds if it holds (PostgreSQL’s read committed). Officer S at one branch says “your account changed since this printout; start again” (PostgreSQL’s repeatable read). Officer S at another withdraws from the live account although the printout disagrees (MySQL’s repeatable read).

Map it back: the live screen is the latest committed data, the printout a snapshot, each question a statement, the withdrawal an UPDATE. Most of this page is about the withdrawal: what a write does when it meets data newer than what you read.

The one-line difference

In a multi-version (MVCC) engine, read committed (RC) takes a new snapshot at the start of every statement, and repeatable read (RR) takes one snapshot per transaction and reuses it for every plain read.

In a locking engine, such as SQL Server with READ_COMMITTED_SNAPSHOT OFF, the same promises come from shared locks. Read committed holds one only while reading each row (SQL Server’s docs say row locks are released before the next row is processed). Repeatable read holds them on every row it read until commit, so nobody can change those rows, though anyone can insert new matching ones.

Beyond that, it depends on the engine. Each anomaly links to its own page; this table only compares.

Two rows cause most surprises: lost update, which PostgreSQL’s repeatable read catches and MySQL’s doesn’t, and range inserts, which only MySQL blocks.

When the snapshot is taken

The word “snapshot” hides a timing question: a snapshot as of when? Figure 1 runs one transaction with three statements while other sessions keep committing.

Three details decide real behaviour:

• The snapshot starts at the first statement, not at BEGIN. PostgreSQL takes it at the first statement that isn’t transaction control. InnoDB creates its read view at the first plain SELECT, so a transaction that opens with an UPDATE gets its snapshot later. START TRANSACTION WITH CONSISTENT SNAPSHOT pins it at the start, but only at REPEATABLE READ; at other levels MySQL ignores it with a warning.
• Your own writes are always visible, at both levels.
• A single SELECT is consistent with itself at both levels. The drift happens between statements. Writes are the exception, as the next section shows.

PostgreSQL’s read committed: wait, re-check, carry on

Read committed is PostgreSQL’s default. Its reads are simple: each SELECT sees what was committed before it began.

Writes are subtler. UPDATE, DELETE and SELECT FOR UPDATE/FOR SHARE find target rows with the statement’s snapshot, but a target row may since have been changed by a concurrent transaction. The PostgreSQL docs define what happens next:

1. If the other transaction is still running, wait for it.
2. If it rolled back, proceed with the row as found; if it committed a delete, skip the row.
3. If it committed an update, re-evaluate the WHERE clause against the new version. Still matches: apply the change to the new version (FOR UPDATE locks and returns it). No longer matches: skip the row, without error.

The executor calls this re-check EvalPlanQual. Figure 2 shows both outcomes.

Outcome 1 is why read committed is pleasant to program against. A guarded atomic write composes with concurrent writers with no errors and no retries:

-- PostgreSQL, READ COMMITTED (the default)
UPDATE wallets SET balance = balance - 300
WHERE id = 7 AND balance >= 300;
-- UPDATE 1: debited from the latest committed balance
-- UPDATE 0: the guard failed on the latest version; reply "insufficient balance"

Outcome 2 is the documented trap, with a website table holding two rows whose hits are 9 and 10:

-- Session A
BEGIN;
UPDATE website SET hits = hits + 1;     -- rows become 10 and 11, not yet committed
-- Session B
DELETE FROM website WHERE hits = 10;    -- its snapshot sees 9 and 10; waits on the 10 row
-- Session A
COMMIT;
-- Session B: DELETE 0

A row with hits = 10 existed before and after A’s update, yet nothing was deleted: B skipped the row that held 9 in its snapshot and re-checked only the row it waited for.

Why one statement can see two moments

PostgreSQL’s docs put it directly: an updating command sees concurrent updates on the rows it is trying to update, but not on any other row. So one statement can read its snapshot for most rows and newer versions for the rows it waited on. That is fine when each command touches a predetermined row and risky with complex search conditions, as in this tatkal-style “grab the first free berth”:

-- PostgreSQL READ COMMITTED: buggy under concurrency
UPDATE berths SET passenger_id = 502
WHERE id = (SELECT id FROM berths
            WHERE train_no = 12951 AND passenger_id IS NULL
            ORDER BY id LIMIT 1);

Two sessions’ subqueries both pick berth 17 from their snapshots. The second UPDATE waits, re-checks only id = 17, which still holds, and overwrites passenger 501; the subquery is not recomputed. Add AND passenger_id IS NULL to the outer WHERE so the re-check fails with UPDATE 0, or FOR UPDATE SKIP LOCKED in the subquery so the second session takes berth 18 (optimistic vs pessimistic locking).

PostgreSQL’s repeatable read: one snapshot, and an error on conflict

At repeatable read, every statement sees the snapshot taken at the first one. Later commits stay invisible, new rows included, so PostgreSQL’s version prevents phantoms too; the standard allows that because its levels are minimums.

Writes search the same snapshot. If a target row was changed by a transaction that committed after your snapshot (actually updated or deleted, not just locked), PostgreSQL won’t re-check and carry on, since that would act on data your snapshot says doesn’t exist. It waits if needed, then fails with ERROR: could not serialize access due to concurrent update, SQLSTATE 40001 (the MVCC page draws the timeline). Retry the whole transaction; the next snapshot includes the change.

Three facts shape how you use it:

• Read-only transactions never get serialization errors here, which makes this level the natural home for reports.
• Only same-row write conflicts are detected. Transactions that update different rows both commit (write skew), and the docs show a read-only anomaly too: a report can see a batch marked “closed” yet miss one of its detail rows.
• DEFERRABLE does nothing here. The SET TRANSACTION docs say it only works with SERIALIZABLE READ ONLY, which waits once for a safe snapshot and then cannot fail.

MySQL’s repeatable read: two kinds of read in one transaction

InnoDB defaults to REPEATABLE READ and runs two kinds of read inside one transaction:

• Consistent reads: plain SELECTs, served without locks from the read view created at the first one.
• Locking reads and writes: SELECT … FOR UPDATE/FOR SHARE, UPDATE and DELETE read the latest committed version and lock it, often called a “current read”; the manual says they “use the most recent state of the database”.

The manual’s consistent read page shows a SELECT COUNT returning 0, the next UPDATE with the same condition changing 10 rows another transaction just committed, and a final SELECT seeing 10, because rows you changed become visible to you. Here it is as a Swiggy-style job that expires pending orders:

-- MySQL 8.4, InnoDB, REPEATABLE READ (the default); orders holds ids 90, 105, 130, 210
-- Session A
START TRANSACTION;
SELECT COUNT(*) FROM orders WHERE id BETWEEN 100 AND 200;   -- 2: read view created here
-- Session B (autocommit)
INSERT INTO orders (id, status) VALUES (150, 'PENDING');
-- Session A
SELECT COUNT(*) FROM orders WHERE id BETWEEN 100 AND 200;   -- still 2
UPDATE orders SET status = 'EXPIRED'
WHERE id BETWEEN 100 AND 200;                               -- 3 rows affected
SELECT COUNT(*) FROM orders WHERE id BETWEEN 100 AND 200;   -- 3: its own change made 150 visible
COMMIT;

The manual’s advice is blunt: don’t mix locking statements and plain SELECTs in one REPEATABLE READ transaction, because they see different states; “typically in such cases you want SERIALIZABLE”. Hermitage shows the cost: two sessions can both read a balance and both write their own result, a lost update, with no error, and read skew returns once you write.

Django’s docs name a real symptom: at MySQL’s REPEATABLE READ, get_or_create() can raise an IntegrityError and then fail to find the object with get(). The insert’s duplicate-key check sees the latest committed row; the follow-up SELECT still reads the old snapshot.

Next-key and gap locks

Locking reads, UPDATE and DELETE lock what they scanned, not just what matched. An equality search on a unique index locks only the record found; any other search takes next-key locks, each scanned record plus the gap before it, so nobody can insert into the range until you commit. That keeps phantoms out of locking statements.

Per the InnoDB locking page, gap locks are “purely inhibitive”: several transactions can hold one on the same gap, and they block only inserts, whose insert intention lock must wait for everyone else’s gap lock. So the textbook “lock, check, then insert” upsert deadlocks:

-- MySQL REPEATABLE READ; orders has ids 90 and 130, nothing in between
-- Session A
START TRANSACTION;
SELECT * FROM orders WHERE id = 105 FOR UPDATE;   -- empty; locks the gap (90, 130)
-- Session B
START TRANSACTION;
SELECT * FROM orders WHERE id = 110 FOR UPDATE;   -- empty; locks the same gap, allowed
-- Session A
INSERT INTO orders (id, status) VALUES (105, 'PENDING');   -- waits for B's gap lock
-- Session B
INSERT INTO orders (id, status) VALUES (110, 'PENDING');   -- ERROR 1213: Deadlock found

At READ COMMITTED, neither SELECT … FOR UPDATE locks a gap, so both inserts succeed; had both used id 105, the unique key would stop the second.

MySQL’s read committed: fewer locks, row-based binlog

At READ COMMITTED, every consistent read takes a fresh snapshot, as in PostgreSQL. The bigger change is locking:

• Record locks only. Gap locking is off for searches and index scans (kept only for foreign-key and duplicate-key checks), so locking reads can see phantoms.
• Locks on non-matching rows are released once the WHERE clause has been evaluated.
• Semi-consistent reads for UPDATE. If a row is locked, InnoDB returns the latest committed version so MySQL can decide whether it matches; only if it does will MySQL read it again and lock it, or wait.

Figure 4 draws the manual’s example: a table with no index on the searched column, so every statement scans every row.

The manual says releasing those locks “greatly reduces the probability of deadlocks, but they can still happen”. With a good index both levels lock far fewer rows; the difference is widest on poorly indexed UPDATEs and DELETEs (database indexes).

One hard requirement: InnoDB at READ COMMITTED supports only row-based binary logging. With binlog_format=MIXED the server switches to row-based logging itself; forcing STATEMENT at runtime, the manual warns, leads very rapidly to errors. MySQL 8.4 defaults to ROW and deprecates binlog_format, so check this only on older servers.

Head to head: same scenario, four behaviours

The same five situations in PostgreSQL and MySQL InnoDB at each level; “another transaction” commits a change in between.

Three patterns stand out:

• Only PostgreSQL’s RR turns a write conflict into an error, and that makes it the only cell where the naive code in (b) is safe.
• MySQL’s two levels write the same way, against the latest committed rows. They differ in locking and in what plain SELECTs see.
• A guarded atomic UPDATE (c) is portable; read-modify-write in the application is not (lost update problem).

One more trap: innodb_lock_wait_timeout defaults to 50 seconds, and error 1205 rolls back only the statement, not the transaction, unless the server runs with --innodb-rollback-on-timeout. Code that catches 1205 and carries on can commit half a transaction.

Oracle and SQL Server in brief

Oracle

Oracle defaults to READ COMMITTED and has no REPEATABLE READ. Each query is consistent to the system change number (SCN) at which it opened. A read committed UPDATE that meets a row locked by an uncommitted transaction waits, then updates the newly changed row; Oracle’s concepts guide uses exactly this to illustrate a lost update. For one snapshot per transaction, choose SERIALIZABLE (snapshot isolation, failing with ORA-08177 when you modify a row someone committed after you began) or, for reports, READ ONLY.

SQL Server

Boxed SQL Server ships with READ_COMMITTED_SNAPSHOT OFF, so READ COMMITTED is the locking kind: readers wait behind writers. Azure SQL Database turns it ON, giving a per-statement snapshot like PostgreSQL’s. REPEATABLE READ holds shared locks on everything read until commit; others can still insert matching rows. Two read-modify-writes of one wallet row both hold shared locks, both need an exclusive one, and one becomes the deadlock victim (SQL Server’s error 1205): the lost update becomes a retry. The one-snapshot-per-transaction level is the separate SNAPSHOT, enabled with ALLOW_SNAPSHOT_ISOLATION.

Reports, exports and long transactions

At read committed, a report built from several queries can contradict itself: the order count from the first query and the revenue sum from the fifth come from different moments. Give the report one snapshot:

-- PostgreSQL: one consistent, read-only report; can never fail with 40001
BEGIN ISOLATION LEVEL REPEATABLE READ READ ONLY;
SELECT count(*) FROM orders WHERE created_at >= date '2026-10-01';
SELECT sum(amount) FROM payments WHERE created_at >= date '2026-10-01';
COMMIT;

-- MySQL: pin the snapshot at the start, even if the session default is READ COMMITTED
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION WITH CONSISTENT SNAPSHOT, READ ONLY;
SELECT COUNT(*) FROM orders WHERE created_at >= '2026-10-01';
SELECT SUM(amount) FROM payments WHERE created_at >= '2026-10-01';
COMMIT;

The standard dump tools rely on the same idea:

• pg_dump makes consistent exports without blocking readers or writers; parallel dumps (-j) share one synchronized snapshot across connections, as you can with pg_export_snapshot() and SET TRANSACTION SNAPSHOT. --serializable-deferrable also rules out the “batch closed, item missing” anomaly.
• mysqldump --single-transaction sets REPEATABLE READ and starts a transaction before dumping. Only InnoDB tables come out consistent, and DDL on a dumped table during the run can corrupt the output.

The bill: a snapshot pins every row version it might need, so a two-hour report on a busy primary holds back VACUUM or purge (vacuum and bloat). Run long reports on a replica.

Migrating between MySQL and PostgreSQL

Applications inherit an isolation level and quietly depend on it. Moving databases changes it underneath them.

From MySQL (RR) to PostgreSQL (RC)

• Multi-query reads lose their stable snapshot. An order header and its line items, read in two queries, can now disagree. Wrap such reads in REPEATABLE READ READ ONLY.
• Gap locks no longer guard inserts. “SELECT … FOR UPDATE on an empty range, then INSERT” was a crude reservation on MySQL. On PostgreSQL, FOR UPDATE on zero rows locks nothing, so two tatkal bookings both succeed. Use a unique or EXCLUDE constraint (database constraints), lock a parent row, or use SERIALIZABLE (phantom read).
• PostgreSQL’s RR is not “the same level”. Where MySQL silently wrote over newer rows, it raises 40001: lost-update protection, paid for with retry code.

From PostgreSQL (RC) to MySQL (RR)

• SELECT and UPDATE stop agreeing: counts differ from rows affected, and get_or_create-style code hits duplicate keys it can’t see.
• More waits and deadlocks from gap locks on range writes, queue tables and inserts; and INSERT … SELECT locks the source rows it reads, which READ COMMITTED avoids.
• Lost updates return if you relied on PostgreSQL’s RR raising 40001. Use FOR UPDATE, an atomic UPDATE or a version column.

Many teams simply run MySQL at READ COMMITTED: fewer gap-lock waits and deadlocks and behaviour closer to PostgreSQL, paid for with a row-based binlog and phantoms in locking reads.

Frameworks and poolers pick the level for you

• Django defaults MySQL connections to READ COMMITTED, not MySQL’s REPEATABLE READ, warning that data loss is possible at repeatable read; it uses READ COMMITTED on PostgreSQL too, so a Django app keeps its level across the move.
• Stacks that use the server default, such as Spring’s Isolation.DEFAULT, run the same code at RR on MySQL and RC on PostgreSQL.
• Session-level SET leaks through poolers and persistent connections into the next request; set the level per transaction (connection pooling).

Check what you are actually running:

-- PostgreSQL
SHOW default_transaction_isolation;       -- the default for new transactions
SHOW transaction_isolation;               -- inside a transaction: the one in force
-- MySQL
SELECT @@global.transaction_isolation, @@session.transaction_isolation;

How to choose

1. OLTP writes on PostgreSQL: READ COMMITTED. Put the arithmetic and the guard in one UPDATE … WHERE … AND balance >= :amt, check rows affected, and keep complex subqueries out of UPDATE conditions.
2. OLTP writes on MySQL: READ COMMITTED if you see gap-lock deadlocks or blocked inserts, the binlog is row-based, and no code relies on gap locks. If something does, keep RR and plan to replace it with a constraint.
3. Reads that must agree (reports, exports, multi-query pages): REPEATABLE READ, READ ONLY, short, ideally on a replica. Oracle: READ ONLY; SQL Server: SNAPSHOT.
4. A read-modify-write that can’t be one statement: PostgreSQL RR plus a retry loop on 40001; on MySQL, SELECT … FOR UPDATE or a version check, because its RR won’t notice.
5. A rule that spans rows (one doctor on call, no overlapping bookings): neither level. Use a constraint or serializable isolation.

Set the level per transaction for the code that needs it. Changing the server default changes every transaction in the application, including ones nobody has read in years.

Cheat sheet

How to say it in the interview

“Read committed gives each statement a fresh snapshot; repeatable read gives the whole transaction one, so only read committed allows non-repeatable reads and read skew. The bigger difference is writes. PostgreSQL’s read committed waits for a concurrent writer, re-checks my WHERE clause on the new row and carries on; its repeatable read throws 40001, so I need a retry loop. MySQL’s repeatable read reads a snapshot but writes the latest rows under gap locks: no errors, but lost updates still happen and deadlocks are likelier. For a wallet I’d use read committed with UPDATE … WHERE balance >= amount and a rows-affected check, and run reconciliation as a read-only repeatable read transaction on a replica.”

Why does MySQL default to repeatable read and PostgreSQL to read committed? Neither project documents the reason, so give the usual explanation and say so. MySQL replication was long statement-based: replicas re-run the source’s SQL in commit order, which is only safe if nobody can insert into a range another statement scanned, and InnoDB’s next-key locks guarantee exactly that. The evidence: READ COMMITTED still requires row-based logging, and the old option that turned gap locking off, removed in 8.0, was named innodb_locks_unsafe_for_binlog. PostgreSQL ships its physical write-ahead log instead, and its re-checking read committed gives good concurrency without demanding retry code from every application.

Will switching MySQL to read committed break anything? Check three things: the binlog is row-based (the 8.4 default; MIXED switches itself; STATEMENT fails); no code relies on SELECT … FOR UPDATE over a range to keep inserts out (add unique constraints); and reports that need one snapshot run in an explicit REPEATABLE READ transaction. In return you get fewer gap-lock deadlocks and smaller lock footprints.

Does PostgreSQL’s repeatable read prevent lost updates? Yes, when the read and the write share one repeatable read transaction: the second writer fails with 40001 and retries on fresh data. Not when the read happened in an earlier request (use a version column or ETag), and not for write skew across rows. MySQL’s repeatable read offers no such protection.

Which one should a payment service use? Read committed for the money path, made safe by the write: a guarded atomic UPDATE or SELECT … FOR UPDATE on the wallet row, ledger rows with a unique idempotency key, and a rows-affected check. Read-only repeatable read for reconciliation. Serializable, with retries, only for a cross-row invariant no constraint can express. See design a digital wallet.

Where to go next

• Transaction isolation levels: the full anomaly catalogue, every level and how to set it.
• MVCC: how snapshots, versions and undo logs make both levels possible.
• Lost update problem: every fix for read-modify-write races, with code.
• Phantom read: why gap locks exist and what PostgreSQL does instead.
• Serializable isolation: the next step up when a rule spans rows.
• Optimistic vs pessimistic locking: FOR UPDATE, SKIP LOCKED and version columns.
• PostgreSQL vs MySQL: the other engineering differences behind the two defaults.

References and further reading

1. PostgreSQL documentation: Transaction Isolationpostgresql.orgRead committed’s wait-and-recheck rule for UPDATE and DELETE, the website.hits example, repeatable read’s snapshot timing and “could not serialize access due to concurrent update”.
2. PostgreSQL documentation: pg_dumppostgresql.orgConsistent exports without blocking readers or writers, synchronized snapshots for parallel dumps, and --serializable-deferrable.
3. MySQL 8.4 Reference Manual: Transaction Isolation Levelsdev.mysql.comNext-key locking at REPEATABLE READ, record-only locks and semi-consistent reads at READ COMMITTED, the five-row lock example and the row-based binlog rule.
4. MySQL 8.4 Reference Manual: Consistent Nonlocking Readsdev.mysql.comWhen the read view is created, and the SELECT-returns-0, UPDATE-affects-10 example of snapshot reads versus DML.
5. MySQL 8.4 Reference Manual: InnoDB Lockingdev.mysql.comRecord, gap, next-key and insert intention locks; gap locks that coexist; gap locking disabled for searches at READ COMMITTED.
6. MySQL 8.4 Reference Manual: Setting The Binary Log Formatdev.mysql.comROW as the default format, and why InnoDB at READ COMMITTED can only use row-based logging.
7. SQL Server documentation: SET TRANSACTION ISOLATION LEVEL (Transact-SQL)learn.microsoft.comLocking versus row-versioned READ COMMITTED, and REPEATABLE READ holding shared locks to commit while still allowing phantoms.
8. Oracle Database Concepts: Data Concurrency and Consistencydocs.oracle.comStatement-level versus transaction-level read consistency, read committed’s conflicting-write rule, and SERIALIZABLE with ORA-08177.
9. Django documentation: Databases (MySQL isolation level)docs.djangoproject.comDjango defaults MySQL connections to READ COMMITTED and warns of get_or_create() anomalies at REPEATABLE READ.
10. Hermitage: testing transaction isolation levels (Kleppmann)github.comHand-run tests showing PostgreSQL’s repeatable read stopping lost updates and read skew, and MySQL’s repeatable read allowing both once it writes.