MVCC

MVCC

MVCC, Undo Logs, and Consistent Reads

Readers and writers share rows without blocking each other through multi-version concurrency control (MVCC). Each clustered-index record (Clustered Indexes) carries a hidden DB_TRX_ID, the last transaction that changed it, and DB_ROLL_PTR, a pointer to the previous version in the undo log. A plain SELECT is a consistent read: it walks back along the pointers to the newest version its read view, the set of transactions committed when the view was made, may see.

One row and its undo chain: each reader rebuilds the version its read view allows (transaction IDs illustrative)

Under REPEATABLE READ the first read creates the view, which lasts until commit; under READ COMMITTED each statement gets a fresh one. Writes and locking reads, however, act on the latest committed version, because that is the one they lock:

A snapshot read versus the row the UPDATE changesSQL

Session A> START TRANSACTION WITH CONSISTENT SNAPSHOT;
Session B> UPDATE products SET stock = stock - 2 WHERE id = 5;
Session B: Query OK, 1 row affected
Session A> SELECT stock FROM products WHERE id = 5\G
Session A: *************************** 1. row ***************************
Session A: stock: 7
Session A> UPDATE products SET stock = stock - 1 WHERE id = 5;
Session A: Query OK, 1 row affected
Session A> SELECT stock FROM products WHERE id = 5\G
Session A: *************************** 1. row ***************************
Session A: stock: 4
Session A> COMMIT;

A saw 7, subtracted 1 and read back 4: the UPDATE worked on B's committed 5, and a transaction always sees its own changes. Never compute a value from a snapshot read and write it back; let the UPDATE do the arithmetic or lock the row first (Row and Gap Locks). Old versions are purged only when no read view needs them, so one idle open transaction makes the undo log grow.

MENU