跳至正文
Lynn LeeMYSQL / LEARNING LAB

从六个课程对应学习单元开始 · 新增：事务都提交，业务不变量仍失败

01 / MYSQL 8.4.13 · INNODB

看清数据库
实际做了什么。

两个会话、真实锁等待、被回滚的事务。先检查保存证据，再在本地复现实验。

运行本地实验 ↗
A会话 / 事务
B会话 / 事务
↓ SQL / InnoDB / a + b + events ↑
观察者 → data_locks + data_lock_waits
编排器 → 明确执行屏障
网页 → 保存的 JSON，不连接数据库
真实运行的保存记录，并非在线数据库。 2026-11-13T12:17:55.647612+00:00
下载运行 JSON
01 锁与死锁02 隔离与读取03 索引与计划版本与证据
1 / 2死锁检测回滚的事务
100 → 200RR 下快照读与锁定读的差异
60,000固定种子合成行 · 每种查询 7 次采样
EXPERIMENT 01

等待如何形成环。

A 更新 a，B 更新 b。A 请求 b 并等待；观察者在真实锁等待表看到 A → B 后，才允许 B 请求 a。InnoDB 选择一个事务回滚。同顺序对照仍会等待，但两个事务均提交。

实际事务结果

CaseAB
Cyclecommittedrolled_back
Same ordercommittedcommitted

被回滚的是哪一方属于本次观察，不是固定接口保证。终值：死锁 101/101，同顺序对照 102/102。

最近死锁记录的实际摘录

*** (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)

摘自 SHOW ENGINE INNODB STATUS 的白名单行。原始连接与环境行仅留本地。

逐步检查真实执行的语句

  1. 启用 JavaScript 后可逐步检查；全部原始事件均可从 JSON 下载。

采集到的锁等待

A → B / b / PRIMARY / X,REC_NOT_GAP

此快照位于死锁环闭合之前。检测器会立即解除完整环；完整环的证据来自死锁摘录。线程 ID 映射为会话 A/B，不公开内存地址形式的锁 ID。

ActorTableTypeModeStatus
AaRECORDX,REC_NOT_GAPGRANTED
AaTABLEIXGRANTED
AbRECORDX,REC_NOT_GAPWAITING
AbTABLEIXGRANTED
BbRECORDX,REC_NOT_GAPGRANTED
BbTABLEIXGRANTED
EXPERIMENT 02

同一事务，两种读取规则。

A 先普通读取 100，建立读视图。B 将该行改为 200 并提交。A 再普通读，再 FOR UPDATE，再普通读。A 在此期间不修改该行。

隔离级别首次普通读B 提交后普通读锁定读再一次普通读
READ COMMITTED100200200200
REPEATABLE READ100100200100

两种隔离级别的锁定读都看到 200，并阻塞 B 随后改为 300 的写入，直到 A 提交。RR 下之后的普通读仍看到原来的 100。本实验只检验主键精确行读取，不能推广到间隙锁、幻读、DML 或 SERIALIZABLE。

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

查看计划，也检查实际执行。

固定种子 20261113。比较全表扫描、联合索引、覆盖投影及月份表达式改写。每组比较以结果哈希验证等价，耗时仅代表此次机器实测。

客户端耗时 / 中位数 ms

每种查询 1 次预热、7 次实际取回结果采样。共享暖缓冲池，不清缓存。固定顺序，无随机交叉设计。p95 为最近秩法，仅 7 个样本。

明确比较的边界

所有方案返回相同的 55 个 ID 和日期。full_scan 与 composite_noncover 还读取 amount_cents；覆盖投影有意省略该字段。composite_cover 与 expression_month 的完整结果一致。

InnoDB 二级索引包含主键。因此 (tenant_id, status, created_at) 可覆盖 id 与 created_at，但无法覆盖 amount_cents。在此数据中，DATE_FORMAT 比半开日期范围需要筛选更多索引候选行。

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'))"
      }
    }
  }
}
7 次耗时采样与全部返回行
[
  {
    "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
  }
]
表结构与索引 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

可检查，也可重新运行的证据。

实际执行的故障路径

错误 SQL → 1064；真实阻塞更新 → 锁等待超时 1205；保留但未监听的回环端口 → 连接错误 2003。显式回滚、工作线程退出、Lab 锁数归零均有断言。

生命周期与验收记录 ↗

版本与实验边界

MySQL 8.4.13 · Docker / Linux ARM64 · 容器上限 2 核 / 1 GiB · 128 MiB buffer pool.

仅使用合成数据。没有远程数据库、模型调用或上传记录。网页控件用于检查保存的执行，不会执行 SQL。

阅读方法、范围与官方参考 →