How InnoDB Isolation Works: MVCC and Undo Logs

Why can one transaction read a table while another one updates it, with no waiting? The answer is MVCC: multi-version concurrency control.

Instead of making readers wait for writers, InnoDB keeps several versions of a row. Each transaction sees the version that was current for its snapshot.

How it works

1. Every row has hidden columns: the id of the transaction that last changed it, and a pointer to the previous version in the undo log.
2. When a transaction updates a row, InnoDB changes the row in place, and stores the old version in the undo log.
3. When a transaction reads, it gets a read view: a description of which transactions had committed at that moment. For each row, InnoDB follows the chain of old versions until it finds one that the read view is allowed to see.
4. Writers lock only the rows they change. Plain SELECTs take no locks.

1
2
3
4
5
6
7
8
9
10
11

-- A:
START TRANSACTION WITH CONSISTENT SNAPSHOT;
SELECT balance FROM accounts WHERE id = 1;    -- 100

-- B (autocommit): two updates
UPDATE accounts SET balance = 111 WHERE id = 1;
UPDATE accounts SET balance = 112 WHERE id = 1;
SELECT balance FROM accounts WHERE id = 1;    -- 112

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

1
2
3
4
5

+---------+
| balance |
+---------+
|     100 |
+---------+

B changed the row twice. The row now holds 112, with the versions 111 and 100 in the undo log. A’s snapshot was taken when the row was 100, so A walks back through the undo chain and gets 100. Neither session waited for the other.

What this means in practice

• Readers never block writers, and writers never block readers (for plain SELECTs). That is a large part of why InnoDB scales well for web workloads.
• Writers do block writers on the same row (row locks, see Locking).
• Every transaction that reads sees a consistent picture, at the isolation level’s rules (Isolation Levels).

The cost: old versions must be kept

The undo log can only discard an old row version when no open transaction could still need it. A long-running transaction, even a read-only one, forces InnoDB to keep every version created since its snapshot. The effects:

• The undo log grows, and the “history list length” increases.
• Reads on frequently updated rows must walk longer version chains, so they get slower.
• The purge thread falls behind, disk use grows.
• In the worst case an idle transaction left open (an application that began a transaction and forgot to commit) degrades the entire server.

Find open transactions

1
2
3
4
5
6

START TRANSACTION;
SELECT COUNT(*) AS rows_seen FROM information_schema.tables WHERE table_schema = 'learn';
SELECT trx_state, TIMESTAMPDIFF(SECOND, trx_started, NOW()) >= 0 AS has_age
FROM information_schema.innodb_trx
WHERE trx_mysql_thread_id = CONNECTION_ID();
COMMIT;

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

+-----------+
| rows_seen |
+-----------+
|         0 |
+-----------+
1 row in set

+-----------+---------+
| trx_state | has_age |
+-----------+---------+
| RUNNING   |       1 |
+-----------+---------+
1 row in set

information_schema.innodb_trx lists every active transaction with its start time, state, the rows it locked and its query. To find old transactions on a real server:

1
2
3
4

SELECT trx_id, trx_started, TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_seconds, trx_mysql_thread_id, trx_rows_modified, LEFT(trx_query, 60) AS query
FROM information_schema.innodb_trx
ORDER BY trx_started
LIMIT 5;

1
2
3
4
5
6

+-----------------+---------------------+-------------+---------------------+-------------------+--------------------------------------------------------------+
| trx_id          | trx_started         | age_seconds | trx_mysql_thread_id | trx_rows_modified | query                                                        |
+-----------------+---------------------+-------------+---------------------+-------------------+--------------------------------------------------------------+
| 284147251288192 | 2026-10-05 17:27:59 |           0 |                5548 |                 0 | SELECT trx_state, TIMESTAMPDIFF(SECOND, trx_started, NOW())  |
+-----------------+---------------------+-------------+---------------------+-------------------+--------------------------------------------------------------+
1 row in set

The only row here is the query itself, which is a transaction too (its id and thread number differ on every run). On a real server, look at the other rows. A transaction that is minutes or hours old, especially one with no trx_query (idle), should be investigated. The sys.innodb_lock_waits and sys.processlist views help too (performance_schema and sys).

The history list length is visible in SHOW ENGINE INNODB STATUS (section TRANSACTIONS). Values in the tens of thousands or above usually mean a long transaction exists.

Why READ COMMITTED differs

Under READ COMMITTED, InnoDB creates a new read view for each statement, so each query sees the latest committed data. Under REPEATABLE READ, one read view is created at the first read of the transaction and reused. That is the whole technical difference between the two for plain reads (Isolation Levels).

Consistent snapshots for backups and reports

START TRANSACTION WITH CONSISTENT SNAPSHOT creates the read view immediately. mysqldump --single-transaction uses it to dump all tables at the same instant, without locking them, by relying on MVCC (mysqldump).

Rules that follow from MVCC

1. Keep transactions short. Commit as soon as the work is done.
2. Never leave transactions open while waiting for a person or a slow external call.
3. Close connections and statements properly. A connection pool that hands back a connection with an open transaction is a classic source of mystery slowdowns.
4. Run long reports on a replica, or in short chunks.
5. Monitor the age of the oldest transaction, and alert on it.