Quantum Ink

Writing about the future as it unfolds.

Diagram illustrating different InnoDB transaction isolation levels and their effects on data consistency in MySQL.

Understanding InnoDB Transaction Isolation Levels in MySQL

Transaction isolation levels are one of the most important – and most misunderstood – parts of MySQL InnoDB behaviour. Choosing the right level affects consistency, performance, and how your application sees concurrent data changes.

This article explains InnoDB isolation levels, the anomalies they allow or prevent, and how to test and configure them safely.

1. Why isolation levels matter

Every transaction sees a particular “view” of the data. With multiple clients reading and writing at the same time, you must decide:

  • How fresh should reads be?
  • Is it acceptable to see uncommitted or rolled-back changes?
  • Can the same query in one transaction return different results?
  • Can two transactions both think a resource is free and allocate it?

InnoDB uses MVCC (multi-version concurrency control) and row-level locks to implement isolation. Different levels control which anomalies are allowed.

2. The four SQL isolation levels

Standard SQL defines four isolation levels. InnoDB supports all of them:

  • READ UNCOMMITTED
  • READ COMMITTED
  • REPEATABLE READ (MySQL default)
  • SERIALIZABLE

Each level trades consistency for concurrency and performance.

2.1 Common concurrency anomalies

Key anomalies to know:

  • Dirty read: A transaction reads data another transaction has written but not committed yet.
  • Non-repeatable read: A transaction reads the same row twice and sees different committed values.
  • Phantom read: A transaction re-runs a range query and sees new rows that match the search condition.

Summary of which anomalies are possible:

Level            Dirty   Non-repeatable   Phantom
READ UNCOMMITTED  Yes         Yes           Yes
READ COMMITTED    No          Yes           Yes
REPEATABLE READ   No          No            *
SERIALIZABLE      No          No           No

* InnoDB prevents most phantoms via next-key locking for locking reads.

3. How InnoDB implements isolation (MVCC basics)

InnoDB stores multiple versions of a row in the undo log. A transaction sees a consistent snapshot based on its start time (or statement start time, depending on isolation level).

Time ───────────▶

T1: BEGIN
    SELECT ...;   ──┐  sees snapshot S1
                    │
T2:       UPDATE row;  -- commits new version V2
                    │
T1: SELECT same row; ─┘  might still see old version V1

┌─────────┐
│ Row V1  │  <─ visible to T1 (older snapshot)
├─────────┤
│ Row V2  │  <─ visible to newer transactions
└─────────┘

Locking reads (e.g. SELECT ... FOR UPDATE) use locks instead of snapshots to guarantee stronger consistency when needed.

4. READ UNCOMMITTED

Characteristics:

  • Can see uncommitted changes (dirty reads).
  • Almost never appropriate for OLTP systems.
  • Writes are still fully ACID; only reads are weaker.

Example:

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- not committed yet

-- Session A might see the reduced balance, even if B later rolls back.

When to use: very rarely. Possibly for non-critical analytics on a replica where occasional inconsistencies are acceptable. Prefer READ COMMITTED instead.

5. READ COMMITTED

Characteristics:

  • Each statement sees only committed data at the time it starts.
  • No dirty reads.
  • Non-repeatable reads and phantoms are still possible.

Statement-level snapshots mean every SELECT gets its own view of the data.

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- sees 100

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = 50 WHERE id = 1;
COMMIT;

-- Session A again
SELECT balance FROM accounts WHERE id = 1;  -- now sees 50

This is a non-repeatable read: the same query in the same transaction returns different values.

Use cases:

  • High-concurrency OLTP systems where occasional re-reads are acceptable.
  • Applications designed to handle changed data between reads (e.g. optimistic locking with version columns).

6. REPEATABLE READ (MySQL default)

Characteristics:

  • Transaction-level snapshot: all plain SELECTs see a consistent view from the time of START TRANSACTION.
  • No dirty or non-repeatable reads for snapshot queries.
  • InnoDB uses next-key locks for locking reads to avoid most phantom issues.

Snapshot behaviour:

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM orders WHERE customer_id = 10;  -- snapshot S1

-- Session B
INSERT INTO orders (customer_id, ...) VALUES (10, ...);
COMMIT;

-- Session A
SELECT * FROM orders WHERE customer_id = 10;  -- still sees S1, no new row

Both SELECTs in Session A see the same set of rows, even though a new row was committed by Session B. This is the core guarantee of REPEATABLE READ.

Locking reads with FOR UPDATE or LOCK IN SHARE MODE acquire next-key locks (index record + gap) to prevent phantoms for that range:

SELECT * FROM orders
WHERE customer_id = 10
FOR UPDATE;

InnoDB will block concurrent inserts into the locked range, which can reduce write concurrency but ensures consistency for critical operations such as allocating unique resources.

7. SERIALIZABLE

Characteristics:

  • Strongest isolation level; emulates serial execution of transactions.
  • Plain SELECTs are treated as SELECT ... LOCK IN SHARE MODE.
  • More locking, more blocking, lower concurrency.

Example effect:

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM inventory WHERE sku = 'X';  -- locks matching rows & gaps

-- Another transaction trying to INSERT a new 'X' may block.

Use cases:

  • Short, critical transactions where correctness is more important than throughput.
  • Complex financial operations where weaker levels are hard to reason about.

Use cautiously, measure contention, and keep transactions small.

8. Checking and setting isolation levels

8.1 View current settings

-- Current session
SELECT @@transaction_isolation;

-- Global default
SELECT @@global.transaction_isolation;

8.2 Change isolation level per session

Preferred way for applications:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
-- your statements
COMMIT;

Or combined:

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

8.3 Change global default (requires restart for persistence)

Warning: Changing the global default affects all new connections. Test first.

-- Temporary (until restart)
SET GLOBAL transaction_isolation = 'READ-COMMITTED';

To make it persistent on RHEL/Rocky Linux, edit your MySQL configuration (usually /etc/my.cnf or /etc/my.cnf.d/server.cnf):

[mysqld]
transaction-isolation = READ-COMMITTED

Then restart MySQL via systemd:

sudo systemctl restart mysqld

9. Practical testing: simulate anomalies

Before changing isolation levels, reproduce behaviours in a lab environment using two sessions.

9.1 Setup

CREATE TABLE accounts (
  id        INT PRIMARY KEY,
  balance   INT NOT NULL
) ENGINE=InnoDB;

INSERT INTO accounts VALUES (1, 100);

9.2 Test non-repeatable read (READ COMMITTED)

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 100

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = 80 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;  -- 80 (changed)
COMMIT;

9.3 Compare with REPEATABLE READ

-- Session A
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;  -- 80

-- Session B
START TRANSACTION;
UPDATE accounts SET balance = 60 WHERE id = 1;
COMMIT;

-- Session A
SELECT balance FROM accounts WHERE id = 1;  -- still 80 (snapshot)
COMMIT;

These simple tests help engineers see concrete differences and validate application assumptions.

10. Best practices for choosing isolation levels

  • Start with REPEATABLE READ (MySQL default) unless you have a reason to change.
  • Use READ COMMITTED if you see excessive locking or deadlocks and your application can handle non-repeatable reads.
  • Use SERIALIZABLE only for small, critical sections where other levels are too hard to reason about.
  • Avoid READ UNCOMMITTED for OLTP. It rarely provides enough benefit to justify the risk.
  • Keep transactions short: fewer rows touched, fewer locks held, less contention.
  • Use explicit locking reads (SELECT ... FOR UPDATE) only around code that truly needs them.
  • Design for retries: deadlocks can still happen; handle ER_LOCK_DEADLOCK by retrying the transaction.
  • Document assumptions: if business logic relies on a specific isolation level, capture that in code comments and runbooks.

This article offers general technical guidance. Validate all configurations in a safe environment before applying them to production.

<!– Image ideas: – Diagram of isolation levels vs anomalies allowed – MVCC timeline showing snapshots and row versions Social captions: – Understand how MySQL InnoDB isolation levels really behave under concurrency – with concrete examples and diagrams. – Choosing the right MySQL isolation level can prevent subtle data bugs. Here is a practical guide for engineers. –>

Smart reads for curious minds

We don’t spam! Read more in our privacy policy

Comments

Leave a Reply

Your email address will not be published. Required fields are marked *