Undo logs: where old row versions live

For MySQL 8.4 InnoDB, the multi-versioning manual describes the clustered record's DB_TRX_ID and DB_ROLL_PTR. Undo records supply information to reconstruct an earlier version. This differs from PostgreSQL's heap tuple history; secondary indexes have their own behavior. The physical mechanism is a Documented contract, not directly observed by the SELECTs below.

Reading rows that no longer exist

At REPEATABLE READ, R establishes a read view before A deletes and commits all three rows. A counts zero current rows, R still reads its original three, and R counts zero after ending its transaction. Those visible results are the Demonstrated behavior.

R

A

reads 3 rows — snapshot opens

DELETE all 3 (+ commit)

table now empty

still sees 3 rows (from undo)

COMMIT — snapshot released

now sees 0 too

R> BEGIN;
Query OK

R> SELECT id, balance FROM accounts ORDER BY id; -- the read view opens here
 id | balance 
----+---------
  1 |     100 
  2 |     200 
  3 |     300 
(3 rows)

A deletes and commits all three rows; its current SELECT counts zero, without inspecting physical storage.

A> DELETE FROM accounts;
Query OK, 3 rows affected

A> SELECT count(*) AS remaining FROM accounts;
 remaining 
-----------
         0 
(1 row)

R's SELECT still returns three rows. The MySQL multi-versioning manual explains this using undo reconstruction; the SELECT asserts visibility, not a count of internal reconstruction operations.

R> SELECT id, balance FROM accounts ORDER BY id; -- the prior read view still returns the original rows
 id | balance 
----+---------
  1 |     100 
  2 |     200 
  3 |     300 
(3 rows)

R> COMMIT;
Query OK

R> SELECT count(*) AS remaining FROM accounts; -- the read view is gone — and with it, R's claim on that history
 remaining 
-----------
         0 
(1 row)

Verified against MySQL 8.4.11 · Run it yourself · Scenario source

DELETE is an UPDATE in disguise

The manual describes DELETE marking a record, with physical removal deferred until purge can discard its update undo. The current query result being empty does not mean the physical records or all undo have already disappeared. The transcript does not inspect physical pages.

Consistent nonlocking reads reconstruct a visible version without taking record locks. That is not a promise that every reader and writer avoids waits: locking reads, SERIALIZABLE, and metadata locks have different rules. Undo can remain needed by a read view or for rollback. Next: read views and purge.

Further reading

• MySQL docs: Undo Logs

The reconstructed support assessment records the exact manual support and execution limits for this lesson.