Start with six course-mapped learning units · New: both transactions commit, but the business invariant fails

01 / MYSQL 8.4.13 · INNODB

See what the database
actually did.

Two sessions. Real lock waits. A rolled-back transaction. Inspect saved evidence, then reproduce the experiment locally.

Run the local Lab ↗

Asession / transaction

Bsession / transaction

↓ SQL / InnoDB / a + b + events ↑

Observer → data_locks + data_lock_waits
Coordinator → explicit execution barriers
Website → saved JSON, no database connection

Saved real run, not a live server. 2026-11-13T12:17:55.647612+00:00

01 Locks & deadlock 02 Isolation & reads 03 Indexes & plans Version & evidence

1 / 2transactions rolled back by deadlock detection

100 → 200snapshot vs locking read under RR

60,000seeded synthetic rows · 7 samples per query

EXPERIMENT 01

A wait becomes a cycle.

A updates a; B updates b. A requests b and waits. Only after the observer sees A → B in the real wait table does B request a. InnoDB chooses a victim. The consistent-order control still waits, but both transactions commit.

Observed outcome

Case	A	B
Cycle	committed	rolled_back
Same order	committed	committed

Victim identity is an observation, not a fixed API contract. Final values: cycle 101/101; ordered control 102/102.

Actual latest-deadlock excerpt

*** (1) TRANSACTION:
UPDATE b SET value=value+1 WHERE id=1
*** (1) HOLDS THE LOCK(S):
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `mysql_lab`.`a` trx id 1828 lock_mode X locks rec but not gap
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `mysql_lab`.`b` trx id 1828 lock_mode X locks rec but not gap waiting
*** (2) TRANSACTION:
UPDATE a SET value=value+1 WHERE id=1
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 3 page no 4 n bits 72 index PRIMARY of table `mysql_lab`.`b` trx id 1831 lock_mode X locks rec but not gap
*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 2 page no 4 n bits 72 index PRIMARY of table `mysql_lab`.`a` trx id 1831 lock_mode X locks rec but not gap waiting
*** WE ROLL BACK TRANSACTION (2)

Allowlisted excerpt of SHOW ENGINE INNODB STATUS. Raw connection and environment lines stay local.

Step through executed statements

JavaScript enables step inspection; all original events are in the downloadable JSON.

Captured lock wait

A → B / b / PRIMARY / X,REC_NOT_GAP

This snapshot precedes cycle closure. The detector resolves the complete cycle immediately; the deadlock excerpt supplies its evidence. Thread IDs are mapped to session A/B; memory-address lock IDs are excluded.

Actor	Table	Type	Mode	Status
A	a	RECORD	X,REC_NOT_GAP	GRANTED
A	a	TABLE	IX	GRANTED
A	b	RECORD	X,REC_NOT_GAP	WAITING
A	b	TABLE	IX	GRANTED
B	b	RECORD	X,REC_NOT_GAP	GRANTED
B	b	TABLE	IX	GRANTED

EXPERIMENT 02

One transaction. Two read rules.

A establishes a plain-read snapshot at 100. B changes the row to 200 and commits. A repeats the plain read, then reads FOR UPDATE, then repeats the plain read again. A does not modify the row.

Isolation	First plain read	After B commits	FOR UPDATE	Plain read again
READ COMMITTED	100	200	200	200
REPEATABLE READ	100	100	200	100

Both locking reads observe 200 and block B’s later update to 300 until A commits. Under RR, the following plain read still sees the original 100. This exact-row experiment does not establish gap-lock, phantom, DML or SERIALIZABLE behavior.

A: START TRANSACTION;
A: SELECT value FROM a WHERE id=1;             -- 100
B: UPDATE a SET value=200 WHERE id=1; COMMIT;
A: SELECT value FROM a WHERE id=1;             -- RC: 200 / RR: 100
A: SELECT value FROM a WHERE id=1 FOR UPDATE;  -- 200 in both
A: SELECT value FROM a WHERE id=1;             -- RC: 200 / RR: 100
B: UPDATE a SET value=300 WHERE id=1;          -- waits for A
A: COMMIT;                                  -- B can finish

EXPERIMENT 03

A plan is a claim. Execute it.

Fixed seed 20261113. Compare a full scan, a composite index, covering projection and a month-expression rewrite. Result hashes prove equivalence within each comparison; timing is machine-specific evidence.

Client wall time / median ms

One warm-up + seven measured fetches per variant. Warm shared buffer pool; no cache flush. Fixed sequential order, no randomized crossover. p95 is nearest-rank, with only seven samples.

Keep comparison boundaries clear

All variants select the same 55 IDs and dates. full_scan and composite_noncover also return amount_cents; covering projection deliberately omits that field. composite_cover and expression_month have identical full results.

Secondary InnoDB indexes contain the primary key. (tenant_id, status, created_at) therefore covers id and created_at, but not amount_cents. DATE_FORMAT requires filtering more candidate index rows than the half-open date range in this dataset.

SQL

SELECT id,created_at,amount_cents FROM events WHERE tenant_id=7 AND status='paid' AND created_at>='2026-10-01' AND created_at<'2026-11-01' ORDER BY created_at,id

EXPLAIN ANALYZE

-> Sort: `events`.created_at, `events`.id  (cost=6099 rows=59624) (actual time=15.9..15.9 rows=55 loops=1)
    -> Filter: ((`events`.`status` = 'paid') and (`events`.tenant_id = 7) and (`events`.created_at >= DATE'2026-10-01') and (`events`.created_at < DATE'2026-11-01'))  (cost=6099 rows=59624) (actual time=0.24..15.8 rows=55 loops=1)
        -> Table scan on events  (cost=6099 rows=59624) (actual time=0.212..10.6 rows=60000 loops=1)

EXPLAIN FORMAT=JSON

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "6164.88"
    },
    "ordering_operation": {
      "using_filesort": true,
      "cost_info": {
        "sort_cost": "66.24"
      },
      "table": {
        "table_name": "events",
        "access_type": "ALL",
        "rows_examined_per_scan": 59624,
        "rows_produced_per_join": 66,
        "filtered": "0.11",
        "cost_info": {
          "read_cost": "6092.03",
          "eval_cost": "6.62",
          "prefix_cost": "6098.65",
          "data_read_per_join": "37K"
        },
        "used_columns": [
          "id",
          "tenant_id",
          "status",
          "created_at",
          "amount_cents"
        ],
        "attached_condition": "((`mysql_lab`.`events`.`status` = 'paid') and (`mysql_lab`.`events`.`tenant_id` = 7) and (`mysql_lab`.`events`.`created_at` >= DATE'2026-10-01') and (`mysql_lab`.`events`.`created_at` < DATE'2026-11-01'))"
      }
    }
  }
}

Seven measured samples and full returned rows

[
  {
    "id": 32268,
    "created_at": "2026-10-02",
    "amount_cents": 62794
  },
  {
    "id": 34452,
    "created_at": "2026-10-02",
    "amount_cents": 13142
  },
  {
    "id": 48530,
    "created_at": "2026-10-03",
    "amount_cents": 8789
  },
  {
    "id": 52649,
    "created_at": "2026-10-03",
    "amount_cents": 36250
  },
  {
    "id": 16722,
    "created_at": "2026-10-04",
    "amount_cents": 23861
  },
  {
    "id": 51382,
    "created_at": "2026-10-05",
    "amount_cents": 39752
  },
  {
    "id": 22407,
    "created_at": "2026-10-06",
    "amount_cents": 21978
  },
  {
    "id": 40969,
    "created_at": "2026-10-06",
    "amount_cents": 72828
  },
  {
    "id": 19171,
    "created_at": "2026-10-08",
    "amount_cents": 39927
  },
  {
    "id": 57425,
    "created_at": "2026-10-08",
    "amount_cents": 17278
  },
  {
    "id": 23847,
    "created_at": "2026-10-09",
    "amount_cents": 52268
  },
  {
    "id": 29652,
    "created_at": "2026-10-10",
    "amount_cents": 20804
  },
  {
    "id": 17720,
    "created_at": "2026-10-11",
    "amount_cents": 69919
  },
  {
    "id": 3478,
    "created_at": "2026-10-12",
    "amount_cents": 69533
  },
  {
    "id": 21702,
    "created_at": "2026-10-12",
    "amount_cents": 17322
  },
  {
    "id": 24711,
    "created_at": "2026-10-12",
    "amount_cents": 80004
  },
  {
    "id": 46281,
    "created_at": "2026-10-13",
    "amount_cents": 3983
  },
  {
    "id": 700,
    "created_at": "2026-10-15",
    "amount_cents": 77941
  },
  {
    "id": 48356,
    "created_at": "2026-10-15",
    "amount_cents": 69247
  },
  {
    "id": 57077,
    "created_at": "2026-10-16",
    "amount_cents": 49996
  },
  {
    "id": 4127,
    "created_at": "2026-10-17",
    "amount_cents": 98423
  },
  {
    "id": 32752,
    "created_at": "2026-10-17",
    "amount_cents": 40638
  },
  {
    "id": 14959,
    "created_at": "2026-10-18",
    "amount_cents": 28238
  },
  {
    "id": 53976,
    "created_at": "2026-10-18",
    "amount_cents": 19207
  },
  {
    "id": 4600,
    "created_at": "2026-10-19",
    "amount_cents": 4839
  },
  {
    "id": 10950,
    "created_at": "2026-10-19",
    "amount_cents": 32883
  },
  {
    "id": 43845,
    "created_at": "2026-10-19",
    "amount_cents": 82655
  },
  {
    "id": 13847,
    "created_at": "2026-10-20",
    "amount_cents": 23738
  },
  {
    "id": 14192,
    "created_at": "2026-10-20",
    "amount_cents": 47278
  },
  {
    "id": 17371,
    "created_at": "2026-10-20",
    "amount_cents": 45353
  },
  {
    "id": 27104,
    "created_at": "2026-10-20",
    "amount_cents": 94365
  },
  {
    "id": 54372,
    "created_at": "2026-10-20",
    "amount_cents": 92325
  },
  {
    "id": 5601,
    "created_at": "2026-10-21",
    "amount_cents": 93479
  },
  {
    "id": 10856,
    "created_at": "2026-10-21",
    "amount_cents": 69502
  },
  {
    "id": 39296,
    "created_at": "2026-10-21",
    "amount_cents": 89953
  },
  {
    "id": 109,
    "created_at": "2026-10-22",
    "amount_cents": 10254
  },
  {
    "id": 1140,
    "created_at": "2026-10-22",
    "amount_cents": 83529
  },
  {
    "id": 34350,
    "created_at": "2026-10-22",
    "amount_cents": 68809
  },
  {
    "id": 36368,
    "created_at": "2026-10-22",
    "amount_cents": 16834
  },
  {
    "id": 52664,
    "created_at": "2026-10-22",
    "amount_cents": 37877
  },
  {
    "id": 10843,
    "created_at": "2026-10-23",
    "amount_cents": 9121
  },
  {
    "id": 39357,
    "created_at": "2026-10-23",
    "amount_cents": 51123
  },
  {
    "id": 2704,
    "created_at": "2026-10-24",
    "amount_cents": 52699
  },
  {
    "id": 9410,
    "created_at": "2026-10-24",
    "amount_cents": 87433
  },
  {
    "id": 48438,
    "created_at": "2026-10-24",
    "amount_cents": 84379
  },
  {
    "id": 1287,
    "created_at": "2026-10-25",
    "amount_cents": 65700
  },
  {
    "id": 32048,
    "created_at": "2026-10-25",
    "amount_cents": 49619
  },
  {
    "id": 27847,
    "created_at": "2026-10-26",
    "amount_cents": 66022
  },
  {
    "id": 44542,
    "created_at": "2026-10-26",
    "amount_cents": 13959
  },
  {
    "id": 32275,
    "created_at": "2026-10-27",
    "amount_cents": 41203
  },
  {
    "id": 3723,
    "created_at": "2026-10-28",
    "amount_cents": 59128
  },
  {
    "id": 24768,
    "created_at": "2026-10-28",
    "amount_cents": 31129
  },
  {
    "id": 45032,
    "created_at": "2026-10-28",
    "amount_cents": 1683
  },
  {
    "id": 11650,
    "created_at": "2026-10-30",
    "amount_cents": 32703
  },
  {
    "id": 46823,
    "created_at": "2026-10-31",
    "amount_cents": 33090
  }
]

Schema and index DDL

CREATE TABLE a (id INT PRIMARY KEY, value INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE b (id INT PRIMARY KEY, value INT NOT NULL) ENGINE=InnoDB;
CREATE TABLE events (id INT PRIMARY KEY, tenant_id INT NOT NULL, status VARCHAR(12) NOT NULL,
created_at DATE NOT NULL, amount_cents INT NOT NULL, payload VARCHAR(128) NOT NULL) ENGINE=InnoDB;
CREATE INDEX idx_tenant_status_date ON events(tenant_id,status,created_at);

PROVENANCE / LIMITS

Evidence you can inspect and rerun.

Executed failure paths

Bad SQL → 1064. Real blocked update → lock wait timeout 1205. Reserved non-listening loopback port → connection error 2003. Explicit rollback, joined workers and zero remaining Lab locks are asserted.

Lifecycle & verification record ↗

Version and experiment limits

MySQL 8.4.13 · Docker / Linux ARM64 · 2 CPU / 1 GiB container limit · 128 MiB buffer pool.

Synthetic data only. No remote database, model calls or uploaded records. Website controls inspect saved executions; they do not execute SQL.

Read methods, scope and official references →

See what the databaseactually did.