Engine Atlas

MySQL MVCC: REPEATABLE READ vs READ COMMITTED

secondary index · row versions · consistent read

MySQL 8.4.10captured evidence

English

학습 안내

이 Lab에서 답할 질문

Writer가 COMMIT한 뒤 REPEATABLE READ의 Session A와 READ COMMITTED의 Session C가 같은 SELECT에서 왜 다른 row version을 보나요?

실행 엔진: MySQL 8.4.10

누가 실행하나요?

Session A와 C가 Q-01을 읽고, Session B가 UPDATE·DELETE·INSERT 뒤 transaction을 hold했다가 COMMIT하며, Session O가 fresh result를 관찰합니다.

무엇을 고정하나요?

Q-01은 tenant_id = 7, state = 'READY', FORCE INDEX (idx_tenant_state_order)를 고정합니다.

실행 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;

무엇이 달라지나요?

isolation level에 따라 statement가 사용하는 snapshot과 결과 row membership 및 payload가 달라집니다.

무엇이 그대로인가요?

실행 SQL, fixture의 version chain 설명, index identifier와 writer의 committed 변경은 동일합니다.

먼저 예측해 보세요

Session B COMMIT 뒤 두 번째 Q-01에서 무엇이 보일까요?

Session A와 C 모두 post-COMMIT rows만 본다.Session A는 기존 snapshot을, Session C는 새 statement snapshot을 본다.일반 SELECT이므로 Session A와 C가 writer lock을 계속 기다린다.

증거 미션

READ COMMITTED lane에서 Session B COMMIT 이후의 두 번째 read 단계를 열어 어떤 row version과 membership이 보이는지 확인하세요.

어디를 보나요?: Row version lens의 C2 결과와 result diff에서 row 102의 payload와 row 103·106의 membership을 봅니다.
이동할 화면: READ COMMITTED · second-read membership

무엇이 보여야 하나요?

확인할 관찰

C2는 fresh statement snapshot에서 row 102를 amount_cents 2500, revision 2로 보고 row 106=6600을 포함하며 row 103은 결과에서 빠집니다. 같은 시점에 REPEATABLE READ reader는 기존 snapshot의 102=2400과 103을 유지합니다. 결과 membership은 captured evidence이지만 화면에 표시되는 delete mark와 undo 연결은 Model 내용입니다.

근거 경계

Captured: Q-01 두 번째 read 결과 rows와 transaction 순서
Model: row-version chain과 undo 이동 표현

Checkpoint · evidence로 설명하기화면의 undo version 연결을 실제 engine 내부 pointer와 traversal capture로 보아도 되나요?답과 근거 확인

아닙니다. Q-01 결과와 transaction 순서는 captured evidence이지만 row version chain과 undo 이동은 fixture에 근거한 Model입니다. 내부 DB_TRX_ID나 DB_ROLL_PTR 값을 꾸며내지 않습니다.

근거 경계

Captured: Q-01 결과와 transaction 순서
Model: row-version chain과 undo 이동

이번 Lab의 핵심

MVCC의 핵심은 최신 row 하나가 아니라 각 statement의 read view가 어떤 committed version을 볼 수 있는지입니다.

다음 연결

다음 Lab에서는 일반 consistent read 대신 FOR UPDATE를 붙였을 때 record·gap·next-key lock과 wait가 어떻게 생기는지 비교합니다.

용어 확인

consistent read처음 다루는 Lab 07: transaction의 read view에 보이는 row version을 반환하는 MVCC 기반 일반 SELECT 읽기입니다.
REPEATABLE READ처음 다루는 Lab 07: 같은 transaction의 consistent read가 하나의 snapshot을 재사용해 반복 조회 결과를 안정적으로 유지하는 isolation level입니다.
READ COMMITTED처음 다루는 Lab 07: 각 consistent read가 statement 시작 시점의 새 snapshot을 사용하는 isolation level입니다.
undo record처음 다루는 Lab 07: 변경 전 row 정보를 보관해 rollback과 snapshot에 맞는 이전 version 복원을 지원하는 InnoDB 기록입니다.

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과 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 entry, row version, 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가 아직 없습니다

query-shape

Q-01이 secondary index와 clustered row를 함께 읽는 이유

현재 command는 Q-01 definition입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

tenant_id = 7과 state = 'READY'가 idx_tenant_state_order의 앞 두 key part를 고정합니다. entry 101은 PK 101을 운반하지만 amount_cents=1200은 index에 없으므로 clustered row에서 읽습니다. 첫 row set은 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

같은 secondary index SELECT가 COMMIT 이후 서로 다른 row version을 반환하는 과정을 실제 결과, transaction state, native plan으로 추적합니다.

Fixture와 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 plan과 captured iterator

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"
        ]
      }
    }
  }
}

네 번의 SELECT 결과

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과 lock barrier

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`

32단계 전체 설명

REPEATABLE READ

Q-01이 secondary index와 clustered row를 함께 읽는 이유

active command · Q-01 definition

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

현재 command는 Q-01 definition입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

tenant_id = 7과 state = 'READY'가 idx_tenant_state_order의 앞 두 key part를 고정합니다. entry 101은 PK 101을 운반하지만 amount_cents=1200은 index에 없으므로 clustered row에서 읽습니다. 첫 row set은 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가 REPEATABLE READ read-only transaction을 시작

active command · A START TRANSACTION READ ONLY

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

현재 command는 A START TRANSACTION READ ONLY입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

A(connection 12)는 START TRANSACTION READ ONLY를 실행했지만 A1은 아직 실행하지 않았습니다. row 101의 amount_cents=1200은 fixture에 존재하나, A의 첫 consistent-read boundary는 아직 만들어지지 않았습니다.
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가 READ COMMITTED read-only transaction을 시작

active command · C START TRANSACTION READ ONLY

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

현재 command는 C START TRANSACTION READ ONLY입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

C(connection 11)도 START TRANSACTION READ ONLY를 실행합니다. C1 이전에는 statement snapshot이 없으며, row 102의 현재 fixture 값은 amount_cents=2400, revision 1입니다.
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이 RR transaction의 첫 row set을 고정

active command · A1 Q-01

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

현재 command는 A1 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

A1은 101=1200, 102=2400/revision 1, 103=3600, 105=6000을 반환합니다. row IDs 101, 102, 103, 105와 old checksum 0f6e4ce02300c310ea47dee95ba35ac7e0a74a7dfc5851e4246235c65b5ff171가 A의 첫 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도 writer 이전의 old row set을 반환

active command · C1 Q-01

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

현재 command는 C1 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

C1은 101=1200, 102=2400/revision 1, 103=3600, 105=6000을 반환합니다. A의 transaction snapshot과 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가 row 102의 payload를 갱신

active command · B UPDATE order_id=102

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

현재 command는 B UPDATE order_id=102입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

B의 UPDATE는 row 102를 amount_cents=2400, revision 1, note=seed-102에서 amount_cents=2500, revision 2, note=updated-by-b로 바꾸고 ROW_COUNT()=1을 반환했습니다. 102-after에서 102-before로 이어지는 version link는 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가 새로운 secondary entry 106을 삽입

active command · B INSERT order_id=106

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

현재 command는 B INSERT order_id=106입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

INSERT는 row 106을 tenant_id=7, state=READY, amount_cents=6600, revision 1로 만들고 ROW_COUNT()=1을 반환했습니다. A1과 C1의 old row set에는 106이 없으며, writer commit 전 reader 결과도 바뀌지 않습니다.
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가 row 103을 삭제

active command · B DELETE order_id=103

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

현재 command는 B DELETE order_id=103입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

DELETE는 amount_cents=3600, revision 1인 row 103에 대해 ROW_COUNT()=1을 반환했습니다. 103 delete-mark와 103-before-delete payload의 연결을 Model로 표시하고, A1/C1 결과의 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
B를 COMMIT 전 hold하고 transaction과 lock을 함께 관찰

active command · B transaction open

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

현재 command는 B transaction open입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

A=12, B=10, C=11가 ACTIVE입니다. B에는 granted locks 3, waiting locks 0이 있고 A/C grants는 0이며 data_lock_waits edge는 0입니다. B가 만든 row 102=2500, row 103 delete, 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-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 이후 fresh snapshot이 볼 수 있는 row set 변경

active command · B COMMIT

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

현재 command는 B COMMIT입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

B COMMIT이 acknowledged=true가 되면서 writer locks는 0으로 닫힙니다. A의 기존 snapshot은 row 102=2400을 유지하고, C의 다음 statement는 committed row 102=2500과 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가 row 102의 prior version을 선택

active command · A2 Q-01

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

현재 command는 A2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

A2는 row 102에서 current payload 2500/revision 2를 지나 prior payload 2400/revision 1을 선택합니다. Captured A2 row IDs는 101, 102, 103, 105이며 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는 103을 포함하고 106을 제외

active command · A2 Q-01

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

현재 command는 A2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

A2의 reused snapshot은 prior row 103=3600을 포함하고 새 row 106=6600을 제외합니다. 결과 101, 102, 103, 105는 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
C2가 실행돼도 A2의 old payload는 유지

active command · C2 Q-01

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

현재 command는 C2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

C2는 row 102=2500/revision 2를 반환하지만 A lane은 reused snapshot의 102=2400/revision 1을 계속 표시합니다. A2 row IDs 101, 102, 103, 105에는 변화가 없습니다.
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
C2의 membership 변화와 A2의 고정 row set 비교

active command · C2 Q-01

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

현재 command는 C2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

C2는 row 103=3600을 제외하고 row 106=6600을 포함하지만 A2는 103을 포함하고 106을 제외합니다. A2는 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
native EXPLAIN이 보여주는 index lookup 경계

active command · A EXPLAIN ANALYZE Q-01

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

현재 command는 A EXPLAIN ANALYZE Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 이 EXPLAIN ANALYZE의 수치입니다.

A의 native plan은 access type ref, key idx_tenant_state_order, cost 0.35, estimated rows 1, actual rows 4, loops=1를 기록했습니다. row 102는 2400/revision 1로 보이지만 별도 MVCC operator는 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
A COMMIT 후에도 마지막 A2 결과를 보존

active command · A COMMIT

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

현재 command는 A COMMIT입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

A가 COMMIT되어 transaction state는 closed가 되지만 마지막 Captured A2 결과 101, 102, 103, 105와 row 102=2400/revision 1은 그대로 남습니다.
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

Q-01이 secondary index와 clustered row를 함께 읽는 이유

active command · Q-01 definition

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

현재 command는 Q-01 definition입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

tenant_id = 7과 state = 'READY'가 idx_tenant_state_order의 앞 두 key part를 고정합니다. entry 101은 PK 101을 운반하지만 amount_cents=1200은 index에 없으므로 clustered row에서 읽습니다. 첫 row set은 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가 REPEATABLE READ read-only transaction을 시작

active command · A START TRANSACTION READ ONLY

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

현재 command는 A START TRANSACTION READ ONLY입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

A(connection 12)는 START TRANSACTION READ ONLY를 실행했지만 A1은 아직 실행하지 않았습니다. row 101의 amount_cents=1200은 fixture에 존재하나, A의 첫 consistent-read boundary는 아직 만들어지지 않았습니다.
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가 READ COMMITTED read-only transaction을 시작

active command · C START TRANSACTION READ ONLY

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

현재 command는 C START TRANSACTION READ ONLY입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

C(connection 11)도 START TRANSACTION READ ONLY를 실행합니다. C1 이전에는 statement snapshot이 없으며, row 102의 현재 fixture 값은 amount_cents=2400, revision 1입니다.
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
A1이 실행된 시점에도 C1은 아직 대기

active command · A1 Q-01

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

현재 command는 A1 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

global phase에서 A1은 101=1200과 102=2400/revision 1을 반환했지만 C는 Q-01을 아직 실행하지 않았습니다. C lane에는 C1 결과나 statement snapshot을 미리 만들지 않습니다.
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이 첫 statement snapshot으로 old row set을 읽음

active command · C1 Q-01

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

현재 command는 C1 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

C1은 101=1200, 102=2400/revision 1, 103=3600, 105=6000을 반환합니다. C1의 statement snapshot과 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가 row 102의 payload를 갱신

active command · B UPDATE order_id=102

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

현재 command는 B UPDATE order_id=102입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

B의 UPDATE는 row 102를 amount_cents=2400, revision 1, note=seed-102에서 amount_cents=2500, revision 2, note=updated-by-b로 바꾸고 ROW_COUNT()=1을 반환했습니다. 102-after에서 102-before로 이어지는 version link는 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가 새로운 secondary entry 106을 삽입

active command · B INSERT order_id=106

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

현재 command는 B INSERT order_id=106입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

INSERT는 row 106을 tenant_id=7, state=READY, amount_cents=6600, revision 1로 만들고 ROW_COUNT()=1을 반환했습니다. A1과 C1의 old row set에는 106이 없으며, writer commit 전 reader 결과도 바뀌지 않습니다.
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가 row 103을 삭제

active command · B DELETE order_id=103

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

현재 command는 B DELETE order_id=103입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

DELETE는 amount_cents=3600, revision 1인 row 103에 대해 ROW_COUNT()=1을 반환했습니다. 103 delete-mark와 103-before-delete payload의 연결을 Model로 표시하고, A1/C1 결과의 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
B를 COMMIT 전 hold하고 transaction과 lock을 함께 관찰

active command · B transaction open

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

현재 command는 B transaction open입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

A=12, B=10, C=11가 ACTIVE입니다. B에는 granted locks 3, waiting locks 0이 있고 A/C grants는 0이며 data_lock_waits edge는 0입니다. B가 만든 row 102=2500, row 103 delete, 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-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 이후 fresh snapshot이 볼 수 있는 row set 변경

active command · B COMMIT

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

현재 command는 B COMMIT입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

B COMMIT이 acknowledged=true가 되면서 writer locks는 0으로 닫힙니다. A의 기존 snapshot은 row 102=2400을 유지하고, C의 다음 statement는 committed row 102=2500과 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
A2 단계에서 C는 여전히 C1 결과를 유지

active command · A2 Q-01

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

현재 command는 A2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

A2가 row 102=2400/revision 1을 반환한 이 phase에도 C2는 아직 실행되지 않았습니다. C lane의 마지막 Captured 결과는 C1 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
A2 membership 단계에서도 C2 결과는 만들지 않음

active command · A2 Q-01

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

현재 command는 A2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

A2는 row 103=3600을 포함하고 106=6600을 제외했지만 C는 아직 두 row의 C2 visibility를 평가하지 않았습니다. C1 결과 101, 102, 103, 105만 유지됩니다.
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가 fresh statement snapshot에서 current 102를 선택

active command · C2 Q-01

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

현재 command는 C2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

C2는 row 102의 current committed payload amount_cents=2500, revision 2, note=updated-by-b를 선택합니다. C1의 2400/revision 1과 비교하면 amount_cents, revision, note가 바뀝니다.
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는 103을 제외하고 106을 포함

active command · C2 Q-01

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

현재 command는 C2 Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 plan-boundary에서 확인하는 참고 수치입니다.

fresh statement snapshot에서 delete-marked row 103=3600은 결과에서 빠지고 current row 106=6600이 들어옵니다. C2 row IDs는 101, 102, 105, 106이고 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
native EXPLAIN이 보여주는 index lookup 경계

active command · C EXPLAIN ANALYZE Q-01

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

현재 command는 C EXPLAIN ANALYZE Q-01입니다. Q-01과 native iterator가 현재 단계의 실행 또는 분석 대상입니다. actual time, rows, loops는 이 EXPLAIN ANALYZE의 수치입니다.

C의 native plan은 access type ref, key idx_tenant_state_order, cost 0.35, estimated rows 1, actual rows 4, loops=1를 기록했습니다. row 102는 2500/revision 2로 보이지만 별도 MVCC operator는 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
C COMMIT 후에도 마지막 C2 결과를 보존

active command · C COMMIT

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

현재 command는 C COMMIT입니다. Q-01은 현재 단계에서 실행되지 않습니다. SQL span과 native iterator는 참고 문맥이며 actual time, rows, loops도 현재 command의 수치가 아닙니다.

C가 COMMIT되어 transaction state는 closed가 되지만 마지막 Captured C2 결과 101, 102, 105, 106와 row 102=2500/revision 2, row 106=6600은 그대로 남습니다.
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