Most production MySQL systems rely on InnoDB, but many applications use its transaction isolation levels without really understanding the trade-offs. That often leads to confusing bugs (phantoms, lost updates) or unnecessary contention and timeouts.

This article walks through InnoDB isolation levels in a practical way, focusing on how they behave, how to test them, and how to choose and tune them for typical workloads.

1. The four ANSI isolation levels in MySQL

MySQL supports the standard four isolation levels:

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

You can see the global default with:

SHOW VARIABLES LIKE 'tx_isolation';   -- older MySQL
SHOW VARIABLES LIKE 'transaction_isolation';  -- newer MySQL

And the current session value with:

SELECT @@transaction_isolation;

To change isolation for a single session:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
-- or
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

Changes at SESSION scope are safe to experiment with and do not affect other connections.

2. How InnoDB implements isolation

InnoDB combines two core mechanisms:

• Row-level locks (record and gap locks)
• MVCC (Multi-Version Concurrency Control) using undo logs

Conceptually, each row has multiple versions in time:

Time ───>

┌───────────┬───────────┬───────────┐
│ Version 1 │ Version 2 │ Version 3 │
└───────────┴───────────┴───────────┘
    t0          t1          t2

Each transaction sees a consistent snapshot depending on
its isolation level and start time.

At higher isolation levels, InnoDB uses more locking (including gaps between rows) to prevent anomalies. At lower levels, it relies more on MVCC snapshots and allows more concurrent writes.

3. READ UNCOMMITTED: rarely appropriate

READ UNCOMMITTED allows dirty reads (seeing uncommitted changes from other transactions). In practice, InnoDB still uses MVCC, so the behaviour is closer to READ COMMITTED, but dirty reads are not guaranteed to be prevented.

Typical issues:

• Reading data that is later rolled back.
• Inconsistent calculations across multiple queries.

Recommended usage: avoid in OLTP and reporting. If you think you need it, you almost always want READ COMMITTED instead.

4. READ COMMITTED: good for mixed workloads

READ COMMITTED guarantees:

• No dirty reads.
• Each statement sees only committed data at its start time.

However, you can still see non-repeatable reads and phantoms (rows appearing/disappearing between statements in the same transaction).

4.1 Step-by-step example: non-repeatable read

Set up a simple table:

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

INSERT INTO account VALUES (1, 100);

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -- returns 100

Session B:

START TRANSACTION;
UPDATE account SET balance = 200 WHERE id = 1;
COMMIT;

Back to Session A:

SELECT balance FROM account WHERE id = 1;  -- now returns 200
COMMIT;

Within a single transaction, Session A saw two different balances. This is acceptable for many applications (e.g. dashboards), but not for others (e.g. financial consistency checks).

4.2 READ COMMITTED and locking

• SELECTs use a fresh snapshot per statement.
• UPDATE/DELETE lock only the matching rows (record locks), not gaps, for simple equality predicates on indexed columns.
• Lock contention is typically lower than REPEATABLE READ for concurrent writers.

Best suited for:

• APIs with many short transactions and concurrent updates.
• Reporting queries that tolerate small inconsistencies.

5. REPEATABLE READ: the InnoDB default

REPEATABLE READ guarantees:

• No dirty reads.
• All reads in a transaction see the same consistent snapshot (based on the time of START TRANSACTION).

InnoDB also uses next-key locking (record + gap) to avoid phantom reads for many common queries, making its behaviour closer to SERIALIZABLE than the ANSI definition suggests.

5.1 Step-by-step example: repeatable read

Reset table:

TRUNCATE TABLE account;
INSERT INTO account VALUES (1, 100);

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM account WHERE id = 1;  -- returns 100

Session B:

START TRANSACTION;
UPDATE account SET balance = 200 WHERE id = 1;
COMMIT;

Back to Session A:

SELECT balance FROM account WHERE id = 1;  -- still returns 100
COMMIT;

Session A sees a stable snapshot for all reads in the transaction, even though Session B has committed a change.

5.2 Next-key locking and gaps

Consider a table with an index on value:

CREATE TABLE t (
  id    INT PRIMARY KEY,
  value INT NOT NULL,
  KEY (value)
) ENGINE=InnoDB;

INSERT INTO t VALUES (1, 10), (2, 20), (3, 30);

Session A:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT * FROM t WHERE value BETWEEN 10 AND 20 FOR UPDATE;

InnoDB locks:

• The rows with value = 10 and value = 20.
• The gaps before 10, between 10 and 20, and after 20 (depending on index structure).

Session B trying to insert a row with value = 15 will block until Session A commits or rolls back:

INSERT INTO t VALUES (4, 15);  -- blocks

This prevents phantoms inside the range but can increase contention for range-based workloads.

6. SERIALIZABLE: maximum isolation, minimum concurrency

SERIALIZABLE adds a requirement that concurrent transactions behave as if they ran one after another. InnoDB achieves this mostly by converting plain SELECT statements into SELECT ... LOCK IN SHARE MODE (or equivalent), acquiring shared locks.

Consequences:

• Readers can block writers and vice versa.
• Throughput can drop sharply under concurrent load.

Recommended usage: narrow, critical operations where correctness outweighs concurrency, and transactions are short and well-bounded.

7. Choosing an isolation level

There is no universal best choice. Use the isolation level that matches your consistency requirements and workload:

• READ COMMITTED
  • Good default for general web APIs and mixed OLTP/reporting.
  • Reduces lock contention compared to REPEATABLE READ.
• REPEATABLE READ
  • Good when transactions read the same rows multiple times and expect stable results.
  • Common for financial or inventory transactions.
• SERIALIZABLE
  • Use sparingly for narrow, high-value invariants.

READ UNCOMMITTED is almost never justifiable in production.

8. Practical tuning and best practices

8.1 Keep transactions short and scoped

• Open transactions only when needed: delay START TRANSACTION until just before the first statement that must be atomic.
• Avoid holding transactions open during user interaction or network calls.
• Commit or roll back promptly.

Long-running transactions keep old row versions alive and can bloat undo logs and slow down purge operations.

8.2 Use explicit locking only when necessary

• SELECT ... FOR UPDATE for rows you intend to modify.
• SELECT ... LOCK IN SHARE MODE when you need a stable view but no write.

Example: safely transferring funds between two accounts:

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

SELECT balance FROM account WHERE id IN (1, 2) FOR UPDATE;

-- perform checks and updates
UPDATE account SET balance = balance - 50 WHERE id = 1;
UPDATE account SET balance = balance + 50 WHERE id = 2;

COMMIT;

This ensures no concurrent transaction can change the involved rows mid-transfer.

8.3 Monitor and handle deadlocks

Higher isolation levels and explicit locks increase the chance of deadlocks. InnoDB automatically detects and rolls back one transaction, returning:

ERROR 1213 (40001): Deadlock found when trying to get lock

Best practices:

• Ensure application code can retry failed transactions with backoff.
• Keep locking order consistent across code paths (e.g. always lock rows in ascending primary key order).
• Inspect deadlocks with SHOW ENGINE INNODB STATUS\G or the InnoDB status output in your environment.

8.4 Set default isolation deliberately

On RHEL/Rocky Linux, you can set the default in /etc/my.cnf or a file under /etc/my.cnf.d/:

[mysqld]
transaction_isolation = READ-COMMITTED

After changing, restart MySQL using your service manager (for example):

sudo systemctl restart mysqld

Always test the impact of changing the global default. Some application code may rely (explicitly or implicitly) on REPEATABLE READ semantics.

8.5 Use session-level overrides for specific workloads

You can keep a conservative global default and override per connection pool or job type. For example:

• OLTP API pool: READ COMMITTED
• Background reporting jobs: READ COMMITTED with long-running snapshots but careful batching
• Critical financial operations: REPEATABLE READ or SERIALIZABLE for the specific stored procedures

Set isolation level immediately after connecting, before starting any transactions.

9. Conclusion

InnoDB isolation levels are not just theoretical switches; they directly affect lock behaviour, contention, and the correctness of your application logic. By understanding how each level interacts with MVCC and locking, you can choose sensible defaults, tune specific workloads, and avoid common anomalies and deadlocks. Start with clear requirements for consistency, then select the lowest isolation level that safely meets them, combined with short, well-structured transactions and explicit locking only where it is truly needed.

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