MySQL MVCC: REPEATABLE READ vs READ COMMITTED

secondary index · row versions · consistent read

MySQL 8.4.10captured evidence

Learning guide

Question for this Lab

After the writer commits, why do Session A under REPEATABLE READ and Session C under READ COMMITTED see different row versions for the same SELECT?

Execution engine: MySQL 8.4.10

Who runs it?

Sessions A and C run Q-01; Session B updates, deletes, and inserts, holds the transaction, then commits; and Session O observes a fresh result.

What stays controlled?

Q-01 fixes tenant_id = 7, state = 'READY', and FORCE INDEX (idx_tenant_state_order).

Executed SQL

SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

What changes?

The snapshot used by each statement and the visible row membership and payload change with the isolation level.

What stays fixed?

The SQL, fixture-backed version-chain explanation, index identifier, and committed writer changes stay fixed.

Predict before tracing

What appears in the second Q-01 after Session B commits?

Both Sessions A and C see only post-COMMIT rows.Session A keeps its transaction snapshot while Session C gets a fresh statement snapshot.Both nonlocking SELECT statements continue waiting for the writer lock.

Evidence mission

Open the second-read step after Session B commits in the READ COMMITTED lane and check which row versions and membership become visible.

Where do you look?: Read the C2 result and result diff in the row-version lens for row 102's payload and the membership of rows 103 and 106.
Screen to open: READ COMMITTED · second-read membership

What should you see?

Expected observation

In its fresh statement snapshot C2 sees row 102 at amount_cents 2500, revision 2, includes row 106=6600, and row 103 is absent from the result. At the same point the REPEATABLE READ reader keeps 102=2400 and row 103 from its existing snapshot. That result membership is captured evidence, while the displayed delete mark and undo connection are Model content.

Evidence boundary

Captured: Q-01 second-read result rows and transaction order
Model: row-version chain and undo movement representation

Checkpoint · explain from evidenceDoes the displayed undo connection represent captured engine pointers and traversal?Reveal answer and basis

No. Q-01 results and transaction order are captured evidence, while the row-version chain and undo movement are fixture-backed Model content. No internal DB_TRX_ID or DB_ROLL_PTR value is invented.

Evidence boundary

Captured: Q-01 results and transaction order
Model: row-version chain and undo movement

Core takeaway

MVCC is not about one latest row; it is about which committed version each statement's read view is allowed to see.

Next connection

The next Lab adds FOR UPDATE and compares the resulting record, gap, next-key locks, and waits with a regular consistent read.

Review terms

consistent readIntroduced in Lab 07: An MVCC-based nonlocking SELECT that returns the row version visible to the transaction's read view.
REPEATABLE READIntroduced in Lab 07: An isolation level where consistent reads in one transaction reuse a snapshot to keep repeated query results stable.
READ COMMITTEDIntroduced in Lab 07: An isolation level where each consistent read uses a fresh snapshot as of the start of that statement.
undo recordIntroduced in Lab 07: An InnoDB record that retains pre-change row information for rollback and reconstruction of older snapshot-visible versions.

Current commandQ-01 definition

A · RR readerRR reader · connection 12
not-startedREPEATABLE READ
B · RR writerWriter · connection 10
not-startedREPEATABLE READ
C · RC readerRC reader · connection 11
not-startedREAD COMMITTED
O · ObserverObserver · connection 13
not-applicableREAD COMMITTED

01 · SOURCE

Q-01 reference

5 reference fragmentsMySQLQ-01 definition · secondary index · non-covering · ORDER BY

SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

02 · FLOW

Native plan and actual iterator

01 / 16

Plan metricsReference metrics

Reference values, not metrics for Q-01 definition

cost: 0.35
estimated rows: 1
actual time · first row ms: 0.0348
actual time · last row ms: 0.0362
actual rows: 4
loops: 1

repeatable-read EXPLAIN FORMAT=TREE

-> Index lookup on mvcc_orders using idx_tenant_state_order (tenant_id=7, state='READY')  (cost=0.35 rows=1)

DATA PATH

Secondary entries, row versions, and visible result

Model + Captured

Model

not-creatednone

Model · not-created · none

INDEX

Secondary index entries

Derived · fixture

tenant_id	state	order_id · PK	origin
7	READY	101	seed
7	READY	102	seed
7	READY	103	seed
7	READY	105	seed
7	READY	106	writer-insert

PRIMARY

Clustered row versions

Derived · fixture

order_id	version	amount_cents	revision	note	origin
101	101-seed	1200	1	seed-101	seed
102	102-before	2400	1	seed-102	seed
102	102-after	2500	2	updated-by-b	writer-update
103	103-before-delete	3600	1	seed-103	seed
105	105-seed	6000	1	seed-105	seed
106	106-inserted	6600	1	inserted-by-b	writer-insert

Visible Q-01 result

Captured

Q-01 result has not run

query-shape

Why Q-01 reads both the secondary index and clustered rows

The active command is Q-01 definition. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

tenant_id = 7 and state = 'READY' bind the first two key parts of idx_tenant_state_order. Entry 101 carries PK 101, while amount_cents=1200 comes from the clustered row because it is not in the index. The first row set is 101, 102, 103, 105.

Q-01 context

active command: Q-01 definition
Q-01 role: reference-context
SQL spans: order_id, amount_cents, revision, noteFORCE INDEX (idx_tenant_state_order)tenant_id = 7state = 'READY'ORDER BY order_id

Native plan context

native plan role: reference-context
metric role: reference-context
index-lookup: repeatable-read EXPLAIN FORMAT=TREE

REPEATABLE READtransaction snapshot reusedREAD COMMITTEDstatement snapshot refreshed

1 / 16

.mvcc-consistent-read-page { overflow: auto !important; background: #f4f6f7; } .mvcc-consistent-read-page main { height: auto !important; min-height: 100dvh; } .mvcc-workbench-shell { display: none !important; } .mvcc-noscript { width: min(100%, 96rem); margin: 0 auto; padding: 28px 20px 72px; color: #171a1d; overflow-wrap: anywhere; } .mvcc-noscript > header { padding-bottom: 24px; border-bottom: 1px solid #d8dde1; } .mvcc-noscript > section, .mvcc-noscript > nav { padding-block: 24px; border-bottom: 1px solid #d8dde1; } .mvcc-noscript h1 { margin: 8px 0 12px; font-size: 32px; line-height: 1.25; letter-spacing: 0; } .mvcc-noscript h2 { margin: 0 0 14px; font-size: 22px; letter-spacing: 0; } .mvcc-noscript h3 { margin: 22px 0 10px; font-size: 17px; letter-spacing: 0; } .mvcc-noscript h4 { margin: 0 0 8px; font-size: 15px; letter-spacing: 0; } .mvcc-noscript p, .mvcc-noscript li, .mvcc-noscript dt, .mvcc-noscript dd, .mvcc-noscript td, .mvcc-noscript th { font-size: 14px; line-height: 1.6; } .mvcc-noscript a { color: #174ca7; } .mvcc-noscript-eyebrow { margin: 0; color: #0d6d4d; font-weight: 800; } .mvcc-noscript-variant-links { display: flex; flex-wrap: wrap; align-items: center; gap: 10px 20px; } .mvcc-noscript-variant-links strong { margin-right: auto; font-size: 15px; } .mvcc-noscript-code { max-width: 100%; margin: 10px 0; padding: 16px; overflow-x: auto; border: 1px solid #d8dde1; background: #fff; font: 15px/1.65 "SFMono-Regular", Consolas, monospace; white-space: pre-wrap; } .mvcc-noscript-table-scroll { max-width: 100%; overflow-x: auto; } .mvcc-noscript-table-scroll table { width: 100%; min-width: 760px; border-collapse: collapse; background: #fff; } .mvcc-noscript-table-scroll th, .mvcc-noscript-table-scroll td { padding: 8px 10px; border: 1px solid #d8dde1; text-align: left; vertical-align: top; } .mvcc-noscript-table-scroll th { background: #f2f5f6; } .mvcc-noscript-table-scroll code { display: block; max-width: 78ch; overflow-wrap: anywhere; white-space: normal; } .mvcc-noscript-facts { display: grid; grid-template-columns: repeat(3, minmax(0, 1fr)); gap: 1px; margin: 12px 0; padding: 1px; background: #d8dde1; } .mvcc-noscript-facts > div { display: grid; min-width: 0; gap: 4px; padding: 11px 13px; background: #fff; } .mvcc-noscript-facts dt { color: #657078; font-weight: 800; } .mvcc-noscript-facts dd { min-width: 0; margin: 0; } .mvcc-noscript-facts code { overflow-wrap: anywhere; } .mvcc-noscript-result, .mvcc-noscript-barrier, .mvcc-noscript-variant { padding-block: 10px 22px; border-bottom: 1px solid #edf0f2; } .mvcc-noscript-result:last-child, .mvcc-noscript-barrier:last-child, .mvcc-noscript-variant:last-child { border-bottom: 0; } .mvcc-noscript-steps { display: grid; grid-template-columns: repeat(2, minmax(0, 1fr)); gap: 1px; margin: 0; padding: 1px; background: #d8dde1; list-style-position: inside; } .mvcc-noscript-steps > li { min-width: 0; padding: 14px; background: #fff; } .mvcc-noscript-steps p { color: #46525a; } .mvcc-noscript-model { margin: 10px 0; padding: 10px 12px; border-left: 3px solid #6d3f91; background: #faf7fc; } .mvcc-noscript-model ul, .mvcc-noscript-refs { margin: 8px 0 0; padding-left: 18px; } .mvcc-noscript-checksum { word-break: break-all; } @media (max-width: 760px) { .mvcc-noscript { padding-inline: 14px; } .mvcc-noscript h1 { font-size: 26px; } .mvcc-noscript-facts, .mvcc-noscript-steps { grid-template-columns: minmax(0, 1fr); } }

MySQL 8.4.10 · arm64 · Captured

MySQL MVCC: REPEATABLE READ vs READ COMMITTED

Trace why the same secondary-index SELECT returns different row versions after COMMIT under REPEATABLE READ and READ COMMITTED.

Fixture and exact SQL

schema.sql

DROP DATABASE IF EXISTS engine_atlas_mvcc;
CREATE DATABASE engine_atlas_mvcc
  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;

USE engine_atlas_mvcc;

CREATE TABLE mvcc_orders (
  order_id INT UNSIGNED NOT NULL,
  tenant_id INT UNSIGNED NOT NULL,
  state VARCHAR(12) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  amount_cents INT UNSIGNED NOT NULL,
  revision TINYINT UNSIGNED NOT NULL,
  note VARCHAR(32) CHARACTER SET ascii COLLATE ascii_bin NOT NULL,
  PRIMARY KEY (order_id),
  KEY idx_tenant_state_order (tenant_id, state, order_id)
) ENGINE=InnoDB
  ROW_FORMAT=DYNAMIC
  STATS_PERSISTENT=1;

INSERT INTO mvcc_orders VALUES
  (101, 7, 'READY', 1200, 1, 'seed-101'),
  (102, 7, 'READY', 2400, 1, 'seed-102'),
  (103, 7, 'READY', 3600, 1, 'seed-103'),
  (104, 7, 'PAID', 4800, 1, 'seed-104'),
  (105, 7, 'READY', 6000, 1, 'seed-105'),
  (201, 8, 'READY', 7100, 1, 'other-201'),
  (202, 8, 'PAID', 8200, 1, 'other-202'),
  (301, 9, 'READY', 9300, 1, 'other-301');

ANALYZE TABLE mvcc_orders;

Q-01 · query.sql

SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

query SHA-256: 77c4c5ec5eaad8c659f41caaa27a0d8b89e8d7d1ee43d05fa55a64a945cb93fa
index: idx_tenant_state_order
key parts: tenant_id → state → order_id
relevant index entries: 5
old result IDs: 101, 102, 103, 105
new result IDs: 101, 102, 105, 106

A/B/C/O session scripts

session	connection	role	isolation	autocommit
A	12	RR reader	REPEATABLE READ	true
B	10	RR writer	REPEATABLE READ	true
C	11	RC reader	READ COMMITTED	true
O	13	Observer	READ COMMITTED	true

Session A

SET SESSION autocommit = 1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION READ ONLY;

-- A1: baseline
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

-- Run after B COMMIT: A2
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

EXPLAIN FORMAT=TREE
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;
EXPLAIN FORMAT=JSON
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;
COMMIT;

Session B

SET SESSION autocommit = 1;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;

UPDATE mvcc_orders
SET amount_cents = 2500,
    revision = 2,
    note = 'updated-by-b'
WHERE order_id = 102;
SELECT ROW_COUNT();

INSERT INTO mvcc_orders
VALUES (106, 7, 'READY', 6600, 1, 'inserted-by-b');
SELECT ROW_COUNT();

DELETE FROM mvcc_orders
WHERE order_id = 103;
SELECT ROW_COUNT();

COMMIT;

Session C

SET SESSION autocommit = 1;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION READ ONLY;

-- C1: baseline
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

-- Run after B COMMIT: C2
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;

EXPLAIN FORMAT=TREE
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;
EXPLAIN FORMAT=JSON
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;
EXPLAIN ANALYZE FORMAT=TREE
SELECT order_id, amount_cents, revision, note
FROM mvcc_orders FORCE INDEX (idx_tenant_state_order)
WHERE tenant_id = 7
  AND state = 'READY'
ORDER BY order_id;
COMMIT;

Session O

SET SESSION autocommit = 1;
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT '__EA_INNODB_TRX__';
SELECT
  TRX_MYSQL_THREAD_ID,
  TRX_STATE,
  TRX_ISOLATION_LEVEL,
  TRX_IS_READ_ONLY,
  TRX_AUTOCOMMIT_NON_LOCKING
FROM information_schema.innodb_trx
ORDER BY TRX_MYSQL_THREAD_ID;

SELECT '__EA_EVENTS_TRANSACTIONS__';
SELECT
  threads.PROCESSLIST_ID,
  transactions.THREAD_ID,
  transactions.STATE,
  transactions.ACCESS_MODE,
  transactions.ISOLATION_LEVEL,
  transactions.AUTOCOMMIT
FROM performance_schema.events_transactions_current AS transactions
JOIN performance_schema.threads AS threads
  ON threads.THREAD_ID = transactions.THREAD_ID
WHERE threads.PROCESSLIST_ID IS NOT NULL
ORDER BY
  threads.PROCESSLIST_ID,
  transactions.THREAD_ID;

SELECT '__EA_DATA_LOCKS__';
SELECT
  ENGINE,
  ENGINE_LOCK_ID,
  ENGINE_TRANSACTION_ID,
  THREAD_ID,
  EVENT_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA
FROM performance_schema.data_locks
WHERE OBJECT_SCHEMA = 'engine_atlas_mvcc'
ORDER BY
  THREAD_ID,
  EVENT_ID,
  OBJECT_SCHEMA,
  OBJECT_NAME,
  INDEX_NAME,
  LOCK_TYPE,
  LOCK_MODE,
  LOCK_STATUS,
  LOCK_DATA,
  ENGINE_LOCK_ID;

SELECT '__EA_DATA_LOCK_WAITS__';
SELECT COUNT(*)
FROM performance_schema.data_lock_waits;

Native plans and captured iterators

REPEATABLE READ · Session A

access type: ref
key: idx_tenant_state_order
estimated cost: 0.35
estimated rows: 1
actual time: 0.0348..0.0362 ms
actual rows / loops: 4 / 1

EXPLAIN FORMAT=TREE

-> Index lookup on mvcc_orders using idx_tenant_state_order (tenant_id=7, state='READY')  (cost=0.35 rows=1)

EXPLAIN ANALYZE FORMAT=TREE

-> Index lookup on mvcc_orders using idx_tenant_state_order (tenant_id=7, state='READY')  (cost=0.35 rows=1) (actual time=0.0348..0.0362 rows=4 loops=1)

EXPLAIN FORMAT=JSON

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "mvcc_orders",
        "access_type": "ref",
        "possible_keys": [
          "idx_tenant_state_order"
        ],
        "key": "idx_tenant_state_order",
        "used_key_parts": [
          "tenant_id",
          "state"
        ],
        "key_length": "18",
        "ref": [
          "const",
          "const"
        ],
        "rows_examined_per_scan": 1,
        "rows_produced_per_join": 1,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "0.25",
          "eval_cost": "0.10",
          "prefix_cost": "0.35",
          "data_read_per_join": "64"
        },
        "used_columns": [
          "order_id",
          "tenant_id",
          "state",
          "amount_cents",
          "revision",
          "note"
        ]
      }
    }
  }
}

READ COMMITTED · Session C

access type: ref
key: idx_tenant_state_order
estimated cost: 0.35
estimated rows: 1
actual time: 0.019..0.02 ms
actual rows / loops: 4 / 1

EXPLAIN FORMAT=TREE

-> Index lookup on mvcc_orders using idx_tenant_state_order (tenant_id=7, state='READY')  (cost=0.35 rows=1)

EXPLAIN ANALYZE FORMAT=TREE

-> Index lookup on mvcc_orders using idx_tenant_state_order (tenant_id=7, state='READY')  (cost=0.35 rows=1) (actual time=0.019..0.02 rows=4 loops=1)

EXPLAIN FORMAT=JSON

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "0.35"
    },
    "ordering_operation": {
      "using_filesort": false,
      "table": {
        "table_name": "mvcc_orders",
        "access_type": "ref",
        "possible_keys": [
          "idx_tenant_state_order"
        ],
        "key": "idx_tenant_state_order",
        "used_key_parts": [
          "tenant_id",
          "state"
        ],
        "key_length": "18",
        "ref": [
          "const",
          "const"
        ],
        "rows_examined_per_scan": 1,
        "rows_produced_per_join": 1,
        "filtered": "100.00",
        "cost_info": {
          "read_cost": "0.25",
          "eval_cost": "0.10",
          "prefix_cost": "0.35",
          "data_read_per_join": "64"
        },
        "used_columns": [
          "order_id",
          "tenant_id",
          "state",
          "amount_cents",
          "revision",
          "note"
        ]
      }
    }
  }
}

Four SELECT results

Captured result hashes · Captured

OLD SHA-256: 0f6e4ce02300c310ea47dee95ba35ac7e0a74a7dfc5851e4246235c65b5ff171
NEW SHA-256: 337bc0a8f7e81857db4cbcc23ae8bb234c91eb6e8a292699585258a4342e277c

REPEATABLE READ · A1 · 4 rows

sha256 0f6e4ce02300c310ea47dee95ba35ac7e0a74a7dfc5851e4246235c65b5ff171

order_id	amount_cents	revision	note
101	1200	1	seed-101
102	2400	1	seed-102
103	3600	1	seed-103
105	6000	1	seed-105

REPEATABLE READ · A2 · 4 rows

sha256 0f6e4ce02300c310ea47dee95ba35ac7e0a74a7dfc5851e4246235c65b5ff171

order_id	amount_cents	revision	note
101	1200	1	seed-101
102	2400	1	seed-102
103	3600	1	seed-103
105	6000	1	seed-105

READ COMMITTED · C1 · 4 rows

sha256 0f6e4ce02300c310ea47dee95ba35ac7e0a74a7dfc5851e4246235c65b5ff171

order_id	amount_cents	revision	note
101	1200	1	seed-101
102	2400	1	seed-102
103	3600	1	seed-103
105	6000	1	seed-105

READ COMMITTED · C2 · 4 rows

sha256 337bc0a8f7e81857db4cbcc23ae8bb234c91eb6e8a292699585258a4342e277c

order_id	amount_cents	revision	note
101	1200	1	seed-101
102	2500	2	updated-by-b
105	6000	1	seed-105
106	6600	1	inserted-by-b

Membership diff · Derived · capture

membership change: 102 updated · 103 deleted · 106 inserted

Transaction and lock barriers

connectionsReady

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	false · n/a	false · n/a	n/a
B	10	false · n/a	false · n/a	n/a
C	11	false · n/a	false · n/a	n/a

session	connection	wait edges
A	12	0
B	10
C	11

readerBaselineComplete

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	true · ACTIVE	false · n/a	REPEATABLE READ
B	10	false · n/a	false · n/a	n/a
C	11	true · ACTIVE	false · n/a	READ COMMITTED

session	connection	wait edges
A	12	0
B	10
C	11

writerHold

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	true · ACTIVE	false · n/a	REPEATABLE READ
B	10	true · ACTIVE	false · n/a	REPEATABLE READ
C	11	true · ACTIVE	false · n/a	READ COMMITTED

session	connection	granted locks	wait edges
A	12	0	0
B	10	3
C	11	0

writerCommitClosed

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	true · ACTIVE	false · n/a	REPEATABLE READ
B	10	true · COMMITTED	false · n/a	REPEATABLE READ
C	11	true · ACTIVE	false · n/a	READ COMMITTED

session	connection	wait edges
A	12	0
B	10
C	11

readerSecondReadsComplete

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	true · ACTIVE	false · n/a	REPEATABLE READ
B	10	true · COMMITTED	false · n/a	REPEATABLE READ
C	11	true · ACTIVE	false · n/a	READ COMMITTED

session	connection	wait edges
A	12	0
B	10
C	11

plansCaptured

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	true · ACTIVE	false · n/a	REPEATABLE READ
B	10	true · COMMITTED	false · n/a	REPEATABLE READ
C	11	true · ACTIVE	false · n/a	READ COMMITTED

session	connection	wait edges
A	12	0
B	10
C	11

readersCommitted

session	connection	P_S present / state	INNODB_TRX present / state	isolation
A	12	true · COMMITTED	false · n/a	REPEATABLE READ
B	10	true · COMMITTED	false · n/a	REPEATABLE READ
C	11	true · COMMITTED	false · n/a	READ COMMITTED

session	connection	wait edges
A	12	0
B	10
C	11

Capture provenance

capture ID: capture:mvcc-consistent-read-mysql-8-4-10
raw frames: 60
evidence references: 27
capture SHA-256: 6a3579799a76ea2b0f2e3113bd4102e90934036910847a75e13b12bd6335fef6
captured SHA-256: 1815b0b2d58aa1056b60d6bf1236833d129a0923f30335071204d2d5428dfc09
derived SHA-256: e3608655aabc9689ace0897fbbf5501b028496d6effb63fa08fc7a80061dd619

evidence ID	basis	checksum	logical pointer
`evidence:mvcc-consistent-read-captured-connection-a`	captured	`sha256:3a32c3606ea16bb72c265649c0307986285a26290135d36a117c65be057408f8`	`capture.json/captured/connections/A`
`evidence:mvcc-consistent-read-captured-connection-b`	captured	`sha256:2fb613e9ec62843bd21cb0c637bb5cf590a8836acc4cbafab24dac92caea05a7`	`capture.json/captured/connections/B`
`evidence:mvcc-consistent-read-captured-connection-c`	captured	`sha256:350fc7af6d2e92f11798f18f1558fe821d550dcb2193f2a2605617544d85a756`	`capture.json/captured/connections/C`
`evidence:mvcc-consistent-read-captured-connection-o`	captured	`sha256:4a79d41566c12e8f731c496b27d7d8242e53e0cbf642e204fcf5c8f7ed33278d`	`capture.json/captured/connections/O`
`evidence:mvcc-consistent-read-captured-plan-a`	captured	`sha256:79a6cc8d45175bab9f5a0cd141990683546cedd8af5ab5d1f8d2c8099d7730f6`	`capture.json/captured/plans/A`
`evidence:mvcc-consistent-read-captured-plan-c`	captured	`sha256:cccb3d55397d08d7862a956ea04d9c281fe9f5b498e5f513c34a8f6a1d389a95`	`capture.json/captured/plans/C`
`evidence:mvcc-consistent-read-captured-result-a-baseline`	captured	`sha256:13ea01430841986970f8acf31fa7fce9d9fa694005959d8170f815fdf27766d1`	`capture.json/captured/results/aBaseline`
`evidence:mvcc-consistent-read-captured-result-a-second`	captured	`sha256:13ea01430841986970f8acf31fa7fce9d9fa694005959d8170f815fdf27766d1`	`capture.json/captured/results/aSecond`
`evidence:mvcc-consistent-read-captured-result-c-baseline`	captured	`sha256:13ea01430841986970f8acf31fa7fce9d9fa694005959d8170f815fdf27766d1`	`capture.json/captured/results/cBaseline`
`evidence:mvcc-consistent-read-captured-result-c-second`	captured	`sha256:7130e096c32d4199ce2823538c519f221658f39c6ab89c646615391a93987483`	`capture.json/captured/results/cSecond`
`evidence:mvcc-consistent-read-captured-server`	captured	`sha256:9d60b8070e922fad5e0170f9d6c90c36df4716b601a6d7213f0634a73e86a6d7`	`capture.json/captured/server`
`evidence:mvcc-consistent-read-captured-transaction-connections-ready`	captured	`sha256:7e416c634a0ecbbe4f91983369736a89b5949efb6355b00b7f3430c63773059c`	`capture.json/captured/transactionSamples/connectionsReady`
`evidence:mvcc-consistent-read-captured-transaction-plans-captured`	captured	`sha256:1af96c744aa882f19ad40bef580cbcce0a6536409e6fc67c2c9e2948c2f065f7`	`capture.json/captured/transactionSamples/plansCaptured`
`evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete`	captured	`sha256:01b3a714ba742cee72ed3993c355aade97f8d37a02764f500d61514aa2e0791f`	`capture.json/captured/transactionSamples/readerBaselineComplete`
`evidence:mvcc-consistent-read-captured-transaction-reader-second-reads-complete`	captured	`sha256:1af96c744aa882f19ad40bef580cbcce0a6536409e6fc67c2c9e2948c2f065f7`	`capture.json/captured/transactionSamples/readerSecondReadsComplete`
`evidence:mvcc-consistent-read-captured-transaction-readers-committed`	captured	`sha256:459d53d41d0ef8b8ce93ca807ccdfa6cacee5a355a924a5d9a6ba9588d95f710`	`capture.json/captured/transactionSamples/readersCommitted`
`evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed`	captured	`sha256:1af96c744aa882f19ad40bef580cbcce0a6536409e6fc67c2c9e2948c2f065f7`	`capture.json/captured/transactionSamples/writerCommitClosed`
`evidence:mvcc-consistent-read-captured-transaction-writer-hold`	captured	`sha256:ea9ae3934656662070f27d010dfd6d95210d569df28b5f5be1a3c776eb564f0e`	`capture.json/captured/transactionSamples/writerHold`
`evidence:mvcc-consistent-read-captured-writer-commit`	captured	`sha256:649860795b687b9869ab3db5d5b1675b3b979bf32791e2c2523445e56ee3c9ed`	`capture.json/captured/writer/commit`
`evidence:mvcc-consistent-read-captured-writer-delete-order103`	captured	`sha256:fee4cff054b6be05889b8c7ead8e7183a5b7312f439cbe33220bc082a345529a`	`capture.json/captured/writer/deleteOrder103`
`evidence:mvcc-consistent-read-captured-writer-insert-order106`	captured	`sha256:fee4cff054b6be05889b8c7ead8e7183a5b7312f439cbe33220bc082a345529a`	`capture.json/captured/writer/insertOrder106`
`evidence:mvcc-consistent-read-captured-writer-update-order102`	captured	`sha256:fee4cff054b6be05889b8c7ead8e7183a5b7312f439cbe33220bc082a345529a`	`capture.json/captured/writer/updateOrder102`
`evidence:mvcc-consistent-read-derived-plan-facts`	derived-capture	`sha256:ec6fe9c9032ece7a35f035d0bcd08988fc6a9fc451c758081937376431dc3c0f`	`capture.json/derived/planFacts`
`evidence:mvcc-consistent-read-derived-result-diffs`	derived-capture	`sha256:b594e6c1291e49115655087f259b7f04c4ddcea11a4a825d3e6481312db0352d`	`capture.json/derived/resultDiffs`
`evidence:mvcc-consistent-read-derived-result-digests`	derived-capture	`sha256:e3690102f1177a097ee0ebcabc7d0fd1705eb59ec30e688e77882de0ddc09872`	`capture.json/derived/resultDigests`
`evidence:mvcc-consistent-read-derived-session-labels`	derived-capture	`sha256:384a516b6794544fff541a87a5c99656b68c38b97983996cebb0567f3fd25b07`	`capture.json/derived/sessionLabels`
`evidence:mvcc-consistent-read-fixture-bindings`	derived-fixture	`sha256:7c97f689edc829e884cef901cea649f851742b14a323a9ebbadc3b06ea69d3be`	`capture.json/fixtureBindings`

Complete 32-step explanation

REPEATABLE READ

Why Q-01 reads both the secondary index and clustered rows

active command · Q-01 definition

Q-01 · reference-context · native plan · reference-context

The active command is Q-01 definition. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

tenant_id = 7 and state = 'READY' bind the first two key parts of idx_tenant_state_order. Entry 101 carries PK 101, while amount_cents=1200 comes from the clustered row because it is not in the index. The first row set is 101, 102, 103, 105.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-server
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
A begins a REPEATABLE READ read-only transaction

active command · A START TRANSACTION READ ONLY

Q-01 · reference-context · native plan · reference-context

The active command is A START TRANSACTION READ ONLY. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

A (connection 12) has run START TRANSACTION READ ONLY, but A1 has not run. Row 101 has amount_cents=1200 in the fixture; A has not established its first consistent-read boundary yet.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-connection-a
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C begins a READ COMMITTED read-only transaction

active command · C START TRANSACTION READ ONLY

Q-01 · reference-context · native plan · reference-context

The active command is C START TRANSACTION READ ONLY. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

C (connection 11) also runs START TRANSACTION READ ONLY. Before C1 there is no statement snapshot; row 102 currently has amount_cents=2400 and revision 1 in the fixture.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-connection-c
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
A1 fixes the first row set for the RR transaction

active command · A1 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A1 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

A1 returns 101=1200, 102=2400/revision 1, 103=3600, and 105=6000. Row IDs 101, 102, 103, 105 and old checksum 0f6e4ce02300c310ea47dee95ba35ac7e0a74a7dfc5851e4246235c65b5ff171 bind to A's first consistent-read boundary.
Model · first-read · transaction-snapshot-established
- row 101 · 101-seed · no prior · visible-head · not-followed
- row 102 · 102-before · no prior · visible-head · not-followed
- row 103 · 103-before-delete · no prior · visible-head · not-followed
- row 105 · 105-seed · no prior · visible-head · not-followed
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C1 also returns the pre-writer row set

active command · C1 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C1 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

C1 returns 101=1200, 102=2400/revision 1, 103=3600, and 105=6000. The view pairs A's transaction snapshot with row IDs 101, 102, 103, 105.
Model · first-read · transaction-snapshot-established
- row 101 · 101-seed · no prior · visible-head · not-followed
- row 102 · 102-before · no prior · visible-head · not-followed
- row 103 · 103-before-delete · no prior · visible-head · not-followed
- row 105 · 105-seed · no prior · visible-head · not-followed
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B updates the payload of row 102

active command · B UPDATE order_id=102

Q-01 · reference-context · native plan · reference-context

The active command is B UPDATE order_id=102. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

B changes row 102 from amount_cents=2400, revision 1, note=seed-102 to amount_cents=2500, revision 2, note=updated-by-b; affected rows=1. The version link from 102-after to 102-before appears in the Model band.
Model · first-read · transaction-snapshot-established
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-connection-b
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-captured-writer-update-order102
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B inserts the new secondary entry for row 106

active command · B INSERT order_id=106

Q-01 · reference-context · native plan · reference-context

The active command is B INSERT order_id=106. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

INSERT creates row 106 with tenant_id=7, state=READY, amount_cents=6600, and revision 1; affected rows=1. Row 106 is absent from the A1 and C1 old row sets, which remain unchanged before the writer commits.
Model · first-read · transaction-snapshot-established
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-captured-writer-insert-order106
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B deletes row 103

active command · B DELETE order_id=103

Q-01 · reference-context · native plan · reference-context

The active command is B DELETE order_id=103. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

DELETE reports affected rows=1 for row 103, whose prior payload is amount_cents=3600 and revision 1. The Model band connects the 103 delete mark to 103-before-delete while the A1/C1 results still retain row 103.
Model · first-read · transaction-snapshot-established
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-captured-writer-delete-order103
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
Hold B before COMMIT and inspect transactions with locks

active command · B transaction open

Q-01 · reference-context · native plan · reference-context

The active command is B transaction open. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

A=12, B=10, and C=11 are ACTIVE. B has 3 granted locks and 0 waiting locks; A/C grants and data_lock_waits edges are 0. The same view connects B's row 102=2500 update, row 103 delete, and row 106=6600 insert.
Model · first-read · transaction-snapshot-established
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-connection-o
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-writer-hold
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B COMMIT changes what a fresh snapshot can see

active command · B COMMIT

Q-01 · reference-context · native plan · reference-context

The active command is B COMMIT. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

B COMMIT is acknowledged=true and the writer lock count closes to 0. A's existing snapshot retains row 102=2400; C's next statement can see committed row 102=2500 and row 106=6600.
Model · first-read · transaction-snapshot-established
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-baseline
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-captured-writer-commit
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
A2 selects the prior version of row 102

active command · A2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

For row 102, A2 follows past the current 2500/revision 2 payload and selects the prior 2400/revision 1 payload. Captured A2 row IDs remain 101, 102, 103, 105 with the old checksum.
Model · reused · transaction-snapshot-reused
- row 102 · 102-after · 102-before · visible-prior · followed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-second
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
A2 includes 103 and excludes 106

active command · A2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

A2's reused snapshot includes prior row 103=3600 and excludes new row 106=6600. Result IDs 101, 102, 103, 105 remain identical to A1.
Model · reused · transaction-snapshot-reused
- row 103 · 103-delete-mark · 103-before-delete · visible-prior · followed
- row 106 · 106-inserted · no prior · not-visible · not-followed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-second
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
A2 keeps the old payload after C2 runs

active command · C2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

C2 returns row 102=2500/revision 2, while A continues to display 102=2400/revision 1 from its reused snapshot. A2 row IDs 101, 102, 103, 105 do not change.
Model · reused · transaction-snapshot-reused
- row 102 · 102-after · 102-before · visible-prior · followed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-second
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
Compare C2 membership changes with A2's fixed row set

active command · C2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

C2 excludes row 103=3600 and includes row 106=6600, whereas A2 includes 103 and excludes 106. A2 retains 101, 102, 103, 105.
Model · reused · transaction-snapshot-reused
- row 103 · 103-delete-mark · 103-before-delete · visible-prior · followed
- row 106 · 106-inserted · no prior · not-visible · not-followed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-second
- evidence:mvcc-consistent-read-captured-transaction-reader-second-reads-complete
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
The boundary exposed by native EXPLAIN

active command · A EXPLAIN ANALYZE Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A EXPLAIN ANALYZE Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops belong to this EXPLAIN ANALYZE.

A's native plan records access type ref, key idx_tenant_state_order, cost 0.35, estimated rows 1, actual rows 4, and loops=1. Row 102 resolves to 2400/revision 1; no separate MVCC operator is added to the native plan.
Model · reused · transaction-snapshot-reused
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-second
- evidence:mvcc-consistent-read-captured-transaction-plans-captured
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
Keep the last A2 result after A COMMIT

active command · A COMMIT

Q-01 · reference-context · native plan · reference-context

The active command is A COMMIT. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

A is committed and its transaction state is closed, while the last Captured A2 result 101, 102, 103, 105 and row 102=2400/revision 1 remain visible.
Model · closed · closed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-a
- evidence:mvcc-consistent-read-captured-result-a-second
- evidence:mvcc-consistent-read-captured-transaction-readers-committed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings

READ COMMITTED

Why Q-01 reads both the secondary index and clustered rows

active command · Q-01 definition

Q-01 · reference-context · native plan · reference-context

The active command is Q-01 definition. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

tenant_id = 7 and state = 'READY' bind the first two key parts of idx_tenant_state_order. Entry 101 carries PK 101, while amount_cents=1200 comes from the clustered row because it is not in the index. The first row set is 101, 102, 103, 105.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-server
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
A begins a REPEATABLE READ read-only transaction

active command · A START TRANSACTION READ ONLY

Q-01 · reference-context · native plan · reference-context

The active command is A START TRANSACTION READ ONLY. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

A (connection 12) has run START TRANSACTION READ ONLY, but A1 has not run. Row 101 has amount_cents=1200 in the fixture; A has not established its first consistent-read boundary yet.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-connection-a
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C begins a READ COMMITTED read-only transaction

active command · C START TRANSACTION READ ONLY

Q-01 · reference-context · native plan · reference-context

The active command is C START TRANSACTION READ ONLY. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

C (connection 11) also runs START TRANSACTION READ ONLY. Before C1 there is no statement snapshot; row 102 currently has amount_cents=2400 and revision 1 in the fixture.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-connection-c
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C1 is still pending when A1 runs

active command · A1 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A1 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

At this global phase A1 has returned 101=1200 and 102=2400/revision 1, while C has not executed Q-01. The C lane contains neither a C1 result nor a statement snapshot yet.
Model · not-created · none
Visible result · not-run · 0 rows
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-transaction-connections-ready
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C1 reads the old row set with its first statement snapshot

active command · C1 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C1 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

C1 returns 101=1200, 102=2400/revision 1, 103=3600, and 105=6000. The view pairs C1's statement snapshot with row IDs 101, 102, 103, 105.
Model · per-statement · statement-snapshot
- row 101 · 101-seed · no prior · visible-head · not-followed
- row 102 · 102-before · no prior · visible-head · not-followed
- row 103 · 103-before-delete · no prior · visible-head · not-followed
- row 105 · 105-seed · no prior · visible-head · not-followed
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B updates the payload of row 102

active command · B UPDATE order_id=102

Q-01 · reference-context · native plan · reference-context

The active command is B UPDATE order_id=102. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

B changes row 102 from amount_cents=2400, revision 1, note=seed-102 to amount_cents=2500, revision 2, note=updated-by-b; affected rows=1. The version link from 102-after to 102-before appears in the Model band.
Model · per-statement · statement-snapshot
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-connection-b
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-captured-writer-update-order102
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B inserts the new secondary entry for row 106

active command · B INSERT order_id=106

Q-01 · reference-context · native plan · reference-context

The active command is B INSERT order_id=106. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

INSERT creates row 106 with tenant_id=7, state=READY, amount_cents=6600, and revision 1; affected rows=1. Row 106 is absent from the A1 and C1 old row sets, which remain unchanged before the writer commits.
Model · per-statement · statement-snapshot
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-captured-writer-insert-order106
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B deletes row 103

active command · B DELETE order_id=103

Q-01 · reference-context · native plan · reference-context

The active command is B DELETE order_id=103. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

DELETE reports affected rows=1 for row 103, whose prior payload is amount_cents=3600 and revision 1. The Model band connects the 103 delete mark to 103-before-delete while the A1/C1 results still retain row 103.
Model · per-statement · statement-snapshot
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-reader-baseline-complete
- evidence:mvcc-consistent-read-captured-writer-delete-order103
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
Hold B before COMMIT and inspect transactions with locks

active command · B transaction open

Q-01 · reference-context · native plan · reference-context

The active command is B transaction open. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

A=12, B=10, and C=11 are ACTIVE. B has 3 granted locks and 0 waiting locks; A/C grants and data_lock_waits edges are 0. The same view connects B's row 102=2500 update, row 103 delete, and row 106=6600 insert.
Model · per-statement · statement-snapshot
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-connection-o
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-writer-hold
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
B COMMIT changes what a fresh snapshot can see

active command · B COMMIT

Q-01 · reference-context · native plan · reference-context

The active command is B COMMIT. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

B COMMIT is acknowledged=true and the writer lock count closes to 0. A's existing snapshot retains row 102=2400; C's next statement can see committed row 102=2500 and row 106=6600.
Model · per-statement · statement-snapshot
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-captured-writer-commit
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C still retains its C1 result during the A2 phase

active command · A2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

A2 has returned row 102=2400/revision 1, but C2 has not executed at this phase. The last Captured result in the C lane is C1 with row IDs 101, 102, 103, 105.
Model · per-statement · statement-snapshot
- row 102 · 102-after · 102-before · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
No C2 result is created during A2 membership

active command · A2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is A2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

A2 includes row 103=3600 and excludes row 106=6600, while C has not evaluated either row for C2. Only the C1 result 101, 102, 103, 105 remains.
Model · per-statement · statement-snapshot
- row 103 · 103-delete-mark · 103-before-delete · not-evaluated · not-evaluated
- row 106 · 106-inserted · no prior · not-evaluated · not-evaluated
Visible result · first · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2400 · revision 1 · seed-102
- 103 · 3600 · revision 1 · seed-103
- 105 · 6000 · revision 1 · seed-105
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-baseline
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C2 selects current row 102 in a fresh statement snapshot

active command · C2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

C2 selects the current committed payload of row 102: amount_cents=2500, revision 2, note=updated-by-b. Compared with C1 at 2400/revision 1, amount_cents, revision, and note have changed.
Model · per-statement · statement-snapshot
- row 102 · 102-after · 102-before · visible-head · not-followed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2500 · revision 2 · updated-by-b
- 105 · 6000 · revision 1 · seed-105
- 106 · 6600 · revision 1 · inserted-by-b
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-second
- evidence:mvcc-consistent-read-captured-transaction-writer-commit-closed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
C2 excludes 103 and includes 106

active command · C2 Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C2 Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops remain reference metrics until plan-boundary.

In the fresh statement snapshot, delete-marked row 103=3600 leaves the result and current row 106=6600 enters it. C2 row IDs are 101, 102, 105, 106 with new checksum 337bc0a8f7e81857db4cbcc23ae8bb234c91eb6e8a292699585258a4342e277c.
Model · per-statement · statement-snapshot
- row 103 · 103-delete-mark · 103-before-delete · not-visible · not-followed
- row 106 · 106-inserted · no prior · visible-head · not-followed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2500 · revision 2 · updated-by-b
- 105 · 6000 · revision 1 · seed-105
- 106 · 6600 · revision 1 · inserted-by-b
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-second
- evidence:mvcc-consistent-read-captured-transaction-reader-second-reads-complete
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
The boundary exposed by native EXPLAIN

active command · C EXPLAIN ANALYZE Q-01

Q-01 · execution-target · native plan · execution-target

The active command is C EXPLAIN ANALYZE Q-01. Q-01 and its native iterator are the execution or analysis target in this phase. Actual time, rows, and loops belong to this EXPLAIN ANALYZE.

C's native plan records access type ref, key idx_tenant_state_order, cost 0.35, estimated rows 1, actual rows 4, and loops=1. Row 102 resolves to 2500/revision 2; no separate MVCC operator is added to the native plan.
Model · per-statement · statement-snapshot
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2500 · revision 2 · updated-by-b
- 105 · 6000 · revision 1 · seed-105
- 106 · 6600 · revision 1 · inserted-by-b
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-second
- evidence:mvcc-consistent-read-captured-transaction-plans-captured
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings
Keep the last C2 result after C COMMIT

active command · C COMMIT

Q-01 · reference-context · native plan · reference-context

The active command is C COMMIT. Q-01 is not executed in this phase. Its SQL span and native iterator remain reference context; actual time, rows, and loops are not metrics for the active command.

C is committed and its transaction state is closed, while the last Captured C2 result 101, 102, 105, 106 and row 102=2500/revision 2 plus row 106=6600 remain visible.
Model · closed · closed
Visible result · second · 4 rows
- 101 · 1200 · revision 1 · seed-101
- 102 · 2500 · revision 2 · updated-by-b
- 105 · 6000 · revision 1 · seed-105
- 106 · 6600 · revision 1 · inserted-by-b
- evidence:mvcc-consistent-read-captured-plan-c
- evidence:mvcc-consistent-read-captured-result-c-second
- evidence:mvcc-consistent-read-captured-transaction-readers-committed
- evidence:mvcc-consistent-read-derived-plan-facts
- evidence:mvcc-consistent-read-derived-result-diffs
- evidence:mvcc-consistent-read-derived-result-digests
- evidence:mvcc-consistent-read-derived-session-labels
- evidence:mvcc-consistent-read-fixture-bindings