MVCC, transactions, isolation

Four letters, three of which are straightforward and one of which is where all the interesting engineering lives.

A read view is InnoDB's snapshot: a small structure captured at a moment in time that decides, for any row version, whether your transaction is allowed to see it. Four fields:

The visibility algorithm

For a row version stamped with trx_id, in order:

if trx_id == creator_trx_id   → VISIBLE -- you wrote it
if trx_id <  up_limit_id      → VISIBLE -- committed before you started
if trx_id >= low_limit_id     → INVISIBLE -- started after you
if trx_id in m_ids            → INVISIBLE -- was in flight when you started
else                          → VISIBLE -- committed in the gap

When a version is invisible, InnoDB follows that row's DB_ROLL_PTR into the undo log and tests the previous version, and the one before that, until one passes. That walk is the subject of chapter 44.

Every InnoDB row carries columns you did not declare. From chapter 20's row format, here they are in full:

Thirteen bytes per row, on every table, forever. On a billion-row table that is 13GB of pure MVCC bookkeeping — a real cost, and the reason a table of narrow rows is never as small as the column widths suggest.

When a transaction updates a row, InnoDB does not keep two copies in the page. It overwrites the row in place with the new values, and writes the old values into the undo log. The row's DB_ROLL_PTR then points at that undo record, which itself points at any earlier one. The result is a linked list running backwards through time.

Two kinds of undo record, and the difference matters:

• Insert undo — only needed for rollback. Once the inserting transaction commits, nobody can ever need the "before" state, because there was none. Discarded immediately at commit.
• Update undo — needed for rollback and for MVCC reads by older transactions. Cannot be discarded until no read view could possibly need it. This is what accumulates.

Undo records cannot be kept forever. Background purge threads delete undo records that no active read view could need, and physically remove rows that were delete-marked.

Purge can only advance to the oldest active read view. One transaction that opened a snapshot and never committed pins that point, and everything after it accumulates. This is the single most common serious InnoDB incident, and it has a clean signature.

-- 1. How far behind is purge? This is the number that matters.
--    Healthy: a few thousand. Trouble: millions.
SELECT name, count FROM information_schema.innodb_metrics
WHERE name = 'trx_rseg_history_len';

-- (also shown as "History list length" here)
SHOW ENGINE INNODB STATUS\G

-- 2. Who is holding it open? Longest-running transactions first.
SELECT trx_id, trx_state,
       TIMESTAMPDIFF(SECOND, trx_started, NOW()) AS age_s,
       trx_mysql_thread_id AS conn_id,
       trx_rows_modified, trx_query
FROM information_schema.innodb_trx
ORDER BY trx_started;

-- Note: trx_query is often NULL — the transaction is idle, which
-- is exactly the problem. Join to find what it last ran:
SELECT t.trx_id,
       TIMESTAMPDIFF(SECOND, t.trx_started, NOW()) AS age_s,
       p.user, p.host, p.db, p.command, p.time,
       COALESCE(t.trx_query, '-- IDLE IN TRANSACTION --') AS q
FROM information_schema.innodb_trx t
JOIN information_schema.processlist p ON p.id = t.trx_mysql_thread_id
ORDER BY age_s DESC;

-- 3. Undo tablespace size on disk.
SELECT tablespace_name, file_name,
       ROUND(file_size/1024/1024/1024,2) AS gb
FROM information_schema.files
WHERE file_type = 'UNDO LOG';

-- 4. The fix, once you have identified the culprit:
--    KILL <conn_id>;

-- 5. The prevention:
SHOW VARIABLES LIKE 'innodb_max_undo_log_size';
SHOW VARIABLES LIKE 'innodb_undo_log_truncate';   -- ON since 8.0
--    and, in the app: never hold a transaction across a network call.

The level determines when a read view is created, which is the entire mechanism. Everything else follows.

Reproduce it yourself

Open two terminals. The left one is transaction A, the right is B. Run the lines in the order shown by the timestamps.

t1 SET SESSION TRANSACTION
     ISOLATION LEVEL REPEATABLE READ;
t2 BEGIN;
t3 SELECT bal FROM acct WHERE id=1;
     -- 100   ← read view created HERE




t7 SELECT bal FROM acct WHERE id=1;
     -- 100   still! repeatable.
t8 COMMIT;
t9 SELECT bal FROM acct WHERE id=1;
     -- 500   new transaction, new view

t4 BEGIN;
t5 UPDATE acct SET bal=500
     WHERE id=1;
t6 COMMIT;
     -- committed and visible
     -- to everyone EXCEPT A

Now change A's first line to READ COMMITTED and run it again. At t7 you will see 500, because READ COMMITTED takes a fresh read view for every statement. That one difference is the whole distinction between the two levels.

CREATE TABLE acct (id INT PRIMARY KEY, bal INT);
INSERT INTO acct VALUES (1, 100);

-- Check what level you are actually on:
SELECT @@transaction_isolation, @@global.transaction_isolation;

This chapter exists because MySQL's REPEATABLE READ differs from both the standard and from Postgres's, in ways that produce real bugs.

1. The snapshot starts at the first read, not at BEGIN

BEGIN does not create a read view. The first consistent read does. So a transaction that begins, waits ten seconds, then selects, sees the data as of the select — not as of the begin. If you need the snapshot pinned at the start, you must say so:

START TRANSACTION WITH CONSISTENT SNAPSHOT;   -- view created now

2. Current reads see the present, ignoring your snapshot

This is the big one. A plain SELECT is a consistent read and obeys the read view. But SELECT ... FOR UPDATE, SELECT ... FOR SHARE, UPDATE and DELETE are current reads: they read the latest committed version, because locking a stale version would be meaningless.

So a single transaction can see two different values for the same row:

t1 BEGIN;
t2 SELECT bal FROM acct WHERE id=1;
     -- 100   (consistent read)


t5 SELECT bal FROM acct WHERE id=1;
     -- 100   snapshot holds
t6 SELECT bal FROM acct WHERE id=1
     FOR UPDATE;
     -- 500   ← CURRENT read. different answer,
     --       same transaction, same row.
t7 UPDATE acct SET bal = bal + 10
     WHERE id=1;
     -- 510, not 110

t3 UPDATE acct SET bal=500
     WHERE id=1;
t4 COMMIT;

3. Phantoms are mostly, but not entirely, prevented

Standard REPEATABLE READ allows phantom rows. MySQL's mostly prevents them — consistent reads use the snapshot, and current reads take gap locks (chapter 54) that block inserts into the ranges you read. The gap is that a current read can still see rows inserted and committed after your snapshot, as in the demo above.

Precise definitions, because these get used loosely.

Write skew, concretely

The classic: a hospital rota requiring at least one doctor on call. Two doctors are on call. Both try to go off call simultaneously.

-- A                                  -- B
SELECT COUNT(*) FROM oncall           SELECT COUNT(*) FROM oncall
 WHERE on_call = TRUE;               WHERE on_call = TRUE;
-- 2, fine to leave                    -- 2, fine to leave
UPDATE oncall SET on_call=FALSE UPDATE oncall SET on_call=FALSE
WHERE id = 1;                       WHERE id = 2;
COMMIT;                              COMMIT;

-- invariant broken: nobody is on call, and neither transaction did
-- anything wrong by itself. They wrote DIFFERENT rows, so no lock
-- conflict occurred and REPEATABLE READ did not help.

Fixes, in order of preference: lock the rows you read (SELECT ... FOR UPDATE), use SERIALIZABLE, or express the invariant as a database constraint so it cannot be violated.

The first row is why MySQL handles read-heavy workloads well: plain reads take no locks at all and never block, and are never blocked by writers. Readers and writers do not contend. That property is the entire point of MVCC.

Two modifiers worth knowing

SELECT ... FOR UPDATE NOWAIT;
  -- fail immediately with ER_LOCK_NOWAIT rather than waiting.
-- use when a stale answer is worse than an error.
SELECT ... FOR UPDATE SKIP LOCKED;
  -- silently skip rows someone else has locked.
-- this is a queue primitive — next chapter.

A genuinely useful pattern, and one you can reach for directly. Before 8.0, building a job queue on MySQL meant either a status-flag dance with a race condition, or every worker serializing on the same lock. SKIP LOCKED makes it clean.

CREATE TABLE jobs (
  id          BIGINT AUTO_INCREMENT PRIMARY KEY,
  status      ENUM('queued','running','done','failed') NOT NULL DEFAULT 'queued',
  run_after   DATETIME(3) NOT NULL DEFAULT CURRENT_TIMESTAMP(3),
  attempts    INT NOT NULL DEFAULT 0,
  payload     JSON,
  -- the index that makes the claim query a range scan, not a table scan
  INDEX idx_claim (status, run_after, id)
);

-- Each worker, in a loop:
BEGIN;

  -- Claim: lock rows nobody else has, skipping any that are taken.
  SELECT id, payload
    FROM jobs
   WHERE status = 'queued'
     AND run_after <= NOW(3)
   ORDER BY run_after, id
   LIMIT 10
   FOR UPDATE SKIP LOCKED;

  -- Mark them running so they are not re-claimed after we commit.
  UPDATE jobs
     SET status = 'running', attempts = attempts + 1
   WHERE id IN (...);

COMMIT;   -- release the locks BEFORE doing the slow work

-- ... do the actual job outside any transaction ...

UPDATE jobs SET status = 'done' WHERE id = ?;

MVCC explains what you can see. Part 5 explains what you can touch: the lock hierarchy, gap locks, and how two well-written transactions deadlock each other.