Isolation Levels

When several transactions run at the same time, each one may or may not see the others’ changes. The isolation level controls how much. It is a trade-off between correctness and concurrency.

MySQL (InnoDB) supports the four standard levels:

The three phenomena:

• Dirty read: reading data another transaction has changed but not committed.
• Non-repeatable read: reading the same row twice in one transaction and getting different values, because someone committed a change in between.
• Phantom read: running the same query twice and getting different sets of rows, because someone inserted or deleted rows in between.

Look at your current level:

1

SELECT @@transaction_isolation AS current_level;

1
2
3
4
5
6

+-----------------+
| current_level   |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set

How to read the examples

The examples below use two connections, A and B, running at the same time against this table:

1
2

CREATE TABLE accounts (id INT PRIMARY KEY, owner VARCHAR(10), balance INT) ENGINE = InnoDB;
INSERT INTO accounts VALUES (1, 'asha', 100), (2, 'ben', 100), (3, 'chitra', 100);

They are real runs against MySQL (8.0, 8.4 and 9.7 behave the same). To try them, open two mysql client windows.

READ UNCOMMITTED: dirty reads

1
2
3
4
5
6
7
8
9
10
11

-- A:
SET SESSION transaction_isolation = 'READ-UNCOMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 1;     -- 100

-- B:
START TRANSACTION;
UPDATE accounts SET balance = 999 WHERE id = 1;     -- not committed

-- A:
SELECT balance FROM accounts WHERE id = 1;

1
2
3
4
5

+---------+
| balance |
+---------+
|     999 |
+---------+

Session A saw 999, a value B has not committed. Then B runs ROLLBACK, and the value never existed. A decision based on it would be wrong. Do not use this level for anything except rough monitoring.

READ COMMITTED: no dirty reads, but values change

1
2
3
4
5
6
7
8
9
10

-- A:
SET SESSION transaction_isolation = 'READ-COMMITTED';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 2;     -- 100

-- B:
UPDATE accounts SET balance = 175 WHERE id = 2;   -- autocommit: committed

-- A:
SELECT balance FROM accounts WHERE id = 2;

1
2
3
4
5

+---------+
| balance |
+---------+
|     175 |
+---------+

Inside the same transaction, A read 100 and then 175. That is a non-repeatable read. Each statement sees the latest committed data at the moment it starts. And new rows that B commits also appear (a phantom):

1
2
3
4
5
6

-- A (READ COMMITTED, open transaction):
SELECT COUNT(*) AS rows_seen FROM accounts;      -- 3
-- B:
INSERT INTO accounts VALUES (5, 'esha', 10);
-- A:
SELECT COUNT(*) AS rows_seen FROM accounts;      -- 4 or 5: the new row is visible

REPEATABLE READ (the default): a stable snapshot

With REPEATABLE READ, a transaction reads from a snapshot taken at its first read. Changes committed by others after that are invisible to its plain SELECTs:

1
2
3
4
5
6
7
8
9
10
11
12

-- A:
SET SESSION transaction_isolation = 'REPEATABLE-READ';
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 2;     -- 100

-- B:
UPDATE accounts SET balance = 250 WHERE id = 2;
INSERT INTO accounts VALUES (4, 'dev', 40);

-- A:
SELECT balance FROM accounts WHERE id = 2;
SELECT COUNT(*) AS rows_seen FROM accounts;

1
2
3
4
5
6
7
8
9
10

+---------+
| balance |
+---------+
|     100 |
+---------+
+-----------+
| rows_seen |
+-----------+
|         3 |
+-----------+

A still sees 100 and 3 rows, although B committed a change and a new row. The reads are repeatable, and no phantoms appear in plain SELECTs. That is the big advantage: a report that runs many queries sees one consistent picture.

The exception: locking reads see the latest data

SELECT ... FOR UPDATE, SELECT ... FOR SHARE, UPDATE and DELETE do not use the snapshot. They read (and lock) the latest committed rows:

1
2
3

-- A (still in the same transaction):
SELECT balance FROM accounts WHERE id = 2 FOR UPDATE;
SELECT COUNT(*) AS rows_seen_by_locking_read FROM accounts FOR UPDATE;

1
2
3
4
5
6
7
8
9
10

+---------+
| balance |
+---------+
|     250 |
+---------+
+---------------------------+
| rows_seen_by_locking_read |
+---------------------------+
|                         4 |
+---------------------------+

Now A sees 250 and 4 rows. This is correct, since you cannot lock or update an old version of a row. But it means a transaction can mix a snapshot read and a latest-data write. If you read a value with a plain SELECT and then update based on it, you may act on stale data. For read-modify-write, use SELECT ... FOR UPDATE or an atomic UPDATE (Updating Under Concurrency).

SERIALIZABLE: as if one at a time

SERIALIZABLE turns plain SELECTs into SELECT ... FOR SHARE (when autocommit is off), so readers block writers and the reverse. It prevents all the phenomena at the price of much less concurrency and more lock waits and deadlocks. It is rare in practice.

1
2
3

SET SESSION transaction_isolation = 'SERIALIZABLE';
SELECT @@transaction_isolation AS level_now;
SET SESSION transaction_isolation = 'REPEATABLE-READ';

1
2
3
4
5
6

+--------------+
| level_now    |
+--------------+
| SERIALIZABLE |
+--------------+
1 row in set

Which level to use?

Many large MySQL deployments (and other databases like PostgreSQL and Oracle, which default to read-committed behaviour) run application traffic at READ COMMITTED to reduce locking. Others stay on the default REPEATABLE READ. Either is a legitimate choice. Know which one you use, and test concurrency-sensitive code under it.

Setting the level

1
2
3

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;   -- this session
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;             -- next transaction only
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;    -- new connections

The server option is transaction_isolation in the configuration file. Your connection library may also set it per connection.