skip to content

Should a team switch MySQL's transaction_isolation from InnoDB's default REPEATABLE-READ to READ-COMMITTED fleet-wide, and what does that trade in locking and correctness?

level: principalimportance: should knowfreq 30%

answer

  1. fewer gap locks, fresher snapshots
  2. check-then-insert loses protection
  3. binary log must be row-based
  4. audit invariants before flipping

basics

~20 s

Sometimes: READ COMMITTED drops InnoDB's gap locking for searches and releases locks on non-matching rows, cutting lock waits and gap-related deadlocks, but it takes a fresh snapshot per statement and stops protecting range checks against concurrent inserts.

solid answer

~40 s

It is a trade, not a free win. At `READ COMMITTED`, InnoDB disables gap locking for searches and index scans (keeping it for foreign-key and duplicate-key checks), releases record locks on rows that fail the `WHERE` condition, and uses semi-consistent reads for `UPDATE`, so lock waits and gap-and-insert deadlocks drop. In return every consistent read takes a fresh snapshot, so multi-statement reports can disagree with themselves, and any code that relied on next-key locks to stop a concurrent insert silently loses that protection. It also requires row-based binary logging, which MySQL 8.4 uses by default. I would audit invariants first, move them onto unique constraints or parent-row locks, then roll the change out per service with deadlock and lock-wait metrics, keeping `REPEATABLE READ` per session where a job needs one snapshot.

code

sql · 10 lines
sql
-- Opt one service's connections in, instead of changing the global default
SET SESSION transaction_isolation = 'READ-COMMITTED';

-- Keep one snapshot for a multi-statement report on that same connection
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION WITH CONSISTENT SNAPSHOT;
-- ... report queries ...
COMMIT;

SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

go deeper

for a junior

Know that InnoDB defaults to REPEATABLE READ and that READ COMMITTED is a common alternative some teams choose.

for a middle

Explain which locks and snapshots change at READ COMMITTED in InnoDB, including gap locks and semi-consistent reads.

for a senior

Find the code paths whose correctness depends on next-key locks, and show how a switch would break them under concurrency.

for a principal

Own the default as a written standard: measure the locking pain, audit invariants, choose global versus per-service scope and roll out with metrics.

## Why the question comes up MySQL's InnoDB defaults to `REPEATABLE READ` (`transaction_isolation` defaults to `REPEATABLE-READ`), while PostgreSQL and SQL Server default to read committed. Teams that hit gap-lock deadlocks or lock waits, or that port code from those engines, ask whether to make `READ-COMMITTED` the fleet default. There is no universally right answer; the choice moves both locking behaviour and correctness guarantees. ## What READ COMMITTED changes in InnoDB | Behaviour | `REPEATABLE READ` (default) | `READ COMMITTED` | |---|---|---| | Plain `SELECT` snapshot | one per transaction, fixed at the first consistent read | a fresh one for every consistent read | | Gap locks on searches and index scans | yes, next-key locks | no; kept only for foreign-key and duplicate-key checks | | Locks on rows that fail the `WHERE` | kept until commit | released after the condition is evaluated | | `UPDATE` meeting a locked row | waits | semi-consistent read of the latest committed version first; waits only if the row matches | | `INSERT ... SELECT` source rows | shared next-key locks | read as a consistent read, no locks | | Binary log format | any | row-based only; `MIXED` is logged as row automatically | The manual says that releasing locks on non-matching rows greatly reduces the probability of deadlocks, but they can still happen. The deadlocks that disappear are mostly the gap-lock and insert-intention kind and the ones caused by scanning rows that were never going to change. ## What you give up - **Range-check invariants.** Code that runs `SELECT ... FOR UPDATE` over a range and inserts only if the range looks right (no double booking of a slot, no oversold stock) is protected at `REPEATABLE READ` because next-key locks block concurrent inserts into the range. At `READ COMMITTED` those gaps are open: two transactions can both see an empty range and both insert. Nothing errors; the data is just wrong. - **Multi-statement consistency.** A report that issues several plain `SELECT`s inside one transaction gets a fresh snapshot for each, so totals and details can disagree. `START TRANSACTION WITH CONSISTENT SNAPSHOT` does not help: it is ignored, with a warning, at any level other than `REPEATABLE READ`. - **Statement-based logging.** Only row-based binary logging is supported at `READ COMMITTED`, so a server that still logs statements is not a candidate. MySQL 8.4 enables binary logging by default in row format, and `binlog_format` is deprecated, so for most 8.4 fleets this is a check rather than a blocker. ## How a lead decides 1. **Measure the pain first.** Is there a real problem: a rising `lock_deadlocks` counter in `INFORMATION_SCHEMA.INNODB_METRICS`, frequent 1205 timeouts, deadlock dumps (via `innodb_print_all_deadlocks`) dominated by gap and insert intention locks? If not, the change buys little. 2. **Inventory the invariants.** Find every place that relies on a locking range read to keep a rule true. Move each onto something that does not depend on gap locks: a unique constraint, or a `FOR UPDATE` on one parent row that serialises the entity. 3. **Find multi-statement readers.** Reports, exports and reconciliation jobs that need one snapshot should keep `REPEATABLE READ` per session (`SET SESSION transaction_isolation = 'REPEATABLE-READ'`), or be rewritten. 4. **Choose the scope.** The level can be set globally, per session or for the next transaction only with `SET TRANSACTION`. A service-by-service rollout, where a connection setting changes the level, is much safer than flipping the global default for every application at once. 5. **Roll out and watch.** Compare deadlock and lock-wait rates before and after, and add tests that run the invariant paths concurrently. ## A reasonable position - For a write-heavy OLTP service whose invariants already sit in constraints and parent-row locks, `READ COMMITTED` is often the better default and is what teams arriving from other engines expect. - For a mixed estate with unknown code relying on InnoDB's default, the cheaper move is to keep `REPEATABLE READ` globally and opt specific hot services into `READ COMMITTED` after the audit. - Either way, the standard should be written down: which level each service runs, why, and which invariants rely on locks. What each isolation level permits in general belongs with the engine-neutral material on isolation levels and anomalies; the decision here turns on InnoDB's gap locking and MySQL's binary logging rules.

  • Which application pattern silently breaks when InnoDB moves from REPEATABLE READ to READ COMMITTED?
    A check-then-insert that relies on a locking range read, such as `SELECT ... WHERE slot_id = ? FOR UPDATE` followed by an `INSERT` when nothing is found. At `REPEATABLE READ` the gap lock stops a concurrent insert; at `READ COMMITTED` searches take no gap locks, so two transactions can both insert. A unique constraint or a parent-row lock keeps the rule under either level.
  • Does READ COMMITTED eliminate deadlocks in InnoDB?
    No. It removes gap locking for searches and releases locks on non-matching rows, which the manual says greatly reduces the probability of deadlocks, but two transactions that lock the same rows in opposite order still deadlock, and duplicate-key and foreign-key checks still use gap locks. Retry handling for error 1213 stays mandatory.

  • READ COMMITTED makes InnoDB deadlocks impossible.
  • InnoDB at READ COMMITTED still blocks inserts into a locked range.
  • Switching the isolation level changes nothing for application correctness.
  • READ COMMITTED works with statement-based binary logging.
  • SET GLOBAL transaction_isolation changes the level of connections already open.

Assumes MySQL 8.4, where binary logging is on by default in row format and binlog_format is deprecated, so READ COMMITTED's row-logging requirement is normally already met.