Elegance Never Fades
Posts Tags Categories Docs About
Elegance Never Fades

Contents

[MySQL] 2. 锁机制执行分析

 included in MySQL Chinese
   6331 words   30 minutes 

[MySQL] 2. 锁机制执行分析

1. 引言

  • 在高并发环境下，数据库锁机制是确保数据一致性和完整性的关键手段。MySQL作为广泛使用的关系型数据库，提供了多种锁类型和机制来管理并发访问。然而，锁的使用不当可能导致性能瓶颈、死锁等问题，影响系统的稳定性和响应速度。

2. 锁相关基本概念

  • 锁的定义：锁是一种机制，用于控制对共享资源的访问，防止多个事务同时修改同一数据，确保数据的一致性和完整性。
  • 锁的类型：
    • 表级锁：锁定整个表。
    • 共享锁（S锁）：允许多个事务同时读取数据，但不允许修改。
    • 排他锁（X锁）：允许一个事务修改数据，其他事务既不能读取也不能修改。
    • 意向锁（IS锁和IX锁）：用于表级别，表示事务打算在行级别上加锁。
    • 自增锁（AUTO-INC锁）：用于处理自增列的并发插入，防止冲突。
    • 间隙锁（Gap Lock）：锁定索引记录之间的间隙，防止幻读。
    • 临键锁（Next-Key Lock）：结合了记录锁和间隙锁，锁定索引记录及其前面的间隙。
    • 记录锁（Record Lock）：锁定具体的索引记录。
    • 行级锁：锁定具体的行。
    • 乐观锁：通过版本号或时间戳实现，适用于读多写少的场景。
    • 悲观锁：通过显式加锁实现，适用于写多读少的场景。
  • 死锁：多个事务互相等待对方释放锁，导致无法继续执行。
  • 锁的兼容性：不同类型的锁之间存在兼容性规则，决定了哪些锁可以同时存在。
  • 锁的粒度：锁定资源的范围，粒度越细，系统并发性越高，但管理开销也越大。

3. MySQL锁介绍

3.1 基本命令

  • 创建测试表
1
2
3
4
5
6
7
8
9

-- auto-generated definition
create table example_single_pk
(
    id      bigint                              not null comment 'id'
        primary key,
    created timestamp default CURRENT_TIMESTAMP not null comment 'create time',
    updated timestamp default CURRENT_TIMESTAMP not null on update CURRENT_TIMESTAMP comment 'update time'
)
    comment 'example_single_pk' charset = utf8mb4;
  • 执行命令
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13

SELECT id, created, updated FROM example_single_pk;

INSERT INTO example_single_pk (id) VALUES (1);

SELECT id, created, updated FROM example_single_pk;

UPDATE example_single_pk SET id = 6 WHERE id = 1;

SELECT id, created, updated FROM example_single_pk;

DELETE FROM example_single_pk WHERE id = 1 or id = 6;

SELECT id, created, updated FROM example_single_pk;
  • 结果
 1
 2
 3
 4
 5
 6
 7
 8
 9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37

mysql> SELECT id, created, updated FROM example_single_pk;
Empty set (0.00 sec)

mysql> 
mysql> INSERT INTO example_single_pk (id) VALUES (1);
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT id, created, updated FROM example_single_pk;
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  1 | 2025-09-27 11:14:40 | 2025-09-27 11:14:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> UPDATE example_single_pk SET id = 6 WHERE id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> 
mysql> SELECT id, created, updated FROM example_single_pk;
+----+---------------------+---------------------+
| id | created             | updated             |
+----+---------------------+---------------------+
|  6 | 2025-09-27 11:14:40 | 2025-09-27 11:14:40 |
+----+---------------------+---------------------+
1 row in set (0.00 sec)

mysql> 
mysql> DELETE FROM example_single_pk WHERE id = 1 or id = 6;
Query OK, 1 row affected (0.00 sec)

mysql> 
mysql> SELECT id, created, updated FROM example_single_pk;
Empty set (0.00 sec)

3.2 按粒度分

3.2.1 表级锁 - READ

3.2.1.1 加锁
1
2

mysql> LOCK TABLES example_single_pk READ;
Query OK, 0 rows affected (0.00 sec)

/images/12.%20mysql%20lock/1.1%20lock%20tables%20read.png

  • 此时，其他会话可以读取数据，但不能进行写操作。
3.2.1.2 解锁
1
2

mysql> UNLOCK tables;
Query OK, 0 rows affected (0.00 sec)

/images/12.%20mysql%20lock/1.2%20unlock%20tables%20read.png

  • 可以看出此时，其他会话可以正常读取但不能写入数据。

3.2.2 表级锁 - WRITE

3.2.2.1 加锁
1
2

mysql> LOCK TABLES example_single_pk WRITE;
query OK, 0 rows affected (0.00 sec)

/images/12.%20mysql%20lock/2.1%20lock%20tables%20write.png

3.2.2.2 解锁
1
2

mysql> UNLOCK tables;
Query OK, 0 rows affected (0.00 sec)

/images/12.%20mysql%20lock/2.2%20unlock%20tables%20write.png

  • 可以看出此时，其他会话既不能读取也不能写入数据。
  • 总体来看，表级别锁的粒度较大，适用于对整个表进行批量操作的场景，但会影响并发性能。
  • 不推荐使用。

3.2.3 行级锁 - SELECT … FOR SHARE

3.2.3.1 加锁
1
2
3
4
5

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
      
mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR SHARE;
Empty set (0.00 sec)

/images/12.%20mysql%20lock/3.1%20select%20for%20share.png

  • 此时影响的是另外会话内的锁。
  • 其他会话可以读取数据，但不能进行写操作，不在赘述并截图。
3.2.3.2 解锁
1
2

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

/images/12.%20mysql%20lock/3.2%20select%20for%20share%20commit.png

  • 解锁后可以正常加读锁和写锁。

3.2.4 行级锁 - SELECT … FOR UPDATE

3.2.4.1 加锁
1
2
3
4
5

mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
      
mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR UPDATE;
Empty set (0.00 sec)

/images/12.%20mysql%20lock/4.1%20select%20for%20update.png

  • 其实此时从查询数据的角度，与 FOR SHARE没有区别，影响的是另外会话内的锁。
  • FOR UPDATE会锁定所选行，其他事务不能读取或修改这些行。
3.2.4.2 解锁
1
2

mysql> COMMIT;
Query OK, 0 rows affected (0.00 sec)

/images/12.%20mysql%20lock/4.2%20select%20for%20update%20commit.png

  • 解锁后可以正常加读锁和写锁。

3.3 按属性分

3.3.1 共享锁 & 排他锁

序号锁名称触发方式锁类型作用域核心特点
1表级共享锁（S锁）LOCK TABLES tbl_name READ服务器层锁整表允许其他会话读，阻止写；需手动UNLOCK TABLES解锁。
2表级排他锁（X锁）LOCK TABLES tbl_name WRITE服务器层锁整表阻止其他会话读写；需手动UNLOCK TABLES解锁。
3行级共享锁（S锁）SELECT … FOR SHARE（事务内）InnoDB行锁单行允许其他会话读该行，阻止写；事务提交/回滚后自动解锁。
4行级排他锁（X锁）SELECT … FOR UPDATE（事务内）InnoDB行锁单行阻止其他会话读写该行；事务提交/回滚后自动解锁。

3.3.2 自增锁 (AUTO-INC Lock)

  • 触发方式：INSERT语句操作AUTO_INCREMENT列时自动触发
  • 特点：AUTO-INC锁，保证自增值的连续性和唯一性。与行锁不同，但是行为结果接近。

/images/12.%20mysql%20lock/5.1%20auto%20inc%20double%20insert%20success.png

  • 可以看到，此时自增锁之间没有冲突，两个会话都成功插入数据。

/images/12.%20mysql%20lock/5.2%20auto%20inc%20double%20insert%20same%20id.png

  • 而很明显，当确定了id之后，则与行锁行为接近，第二个会话插入失败。

/images/12.%20mysql%20lock/5.3%20auto%20inc%20double%20insert%20same%20id%20fail.png

  • 提交之后，可以看到数据插入失败，因为id冲突。

3.3.3 意向锁 (Intention Lock)

  • 意向锁是 InnoDB 存储引擎为协调「服务器层（MySQL 主进程）表锁」与「存储引擎层行锁」的冲突，设计的服务器级表级锁。它的核心是传递“存储引擎中有行锁存在”的信号，让服务器层能快速判断表锁与行锁的兼容性，避免高成本冲突检测。以下从层级定位、设计目标、联动机制、轻量原因展开说明：

  • 意向锁是服务器层的“行锁信号灯”——用表级锁的极小开销，传递存储引擎的行锁状态，让两层锁机制高效协同，既正确又高性能。

3.3.3.1. 层级定位：服务器层的“行锁信号员”

• 服务器层：MySQL 主进程负责管理服务器级锁（如 LOCK TABLES），这是独立于存储引擎的原生锁机制。

• 存储引擎层：InnoDB 负责管理行级锁（如 SELECT … FOR UPDATE 的 X 锁），控制具体数据行的访问。

• 意向锁的角色：意向锁属于服务器层的表级锁，但它不直接控制数据行——而是作为“翻译官”，将存储引擎中的行锁状态（“有事务在操作某行”）转化为服务器层能理解的“信号”（表级的 IS/IX 锁）。

3.3.3.2. 核心设计目标：解决分层架构的“信息差”

MySQL 的分层架构（服务器层 vs 存储引擎层）导致锁状态天然隔离：

• 服务器层想加表锁时，无法直接感知存储引擎里是否有行锁（比如事务 A 已锁定某行）；

• 存储引擎的行锁，也不关心服务器层是否有表锁。

若没有意向锁，服务器层加表锁时，必须遍历存储引擎的所有行（O(n) 时间复杂度），检查是否有行锁冲突——这对大表来说是灾难性的性能损耗。

意向锁的出现，将这种“遍历检查”优化为O(1) 的信号判断：

• 存储引擎加行锁时，自动向服务器层注册对应的意向锁（IS=行读意图，IX=行写意图）；

• 服务器层加表锁时，只需检查表级的意向锁状态，就能立刻判断是否冲突。

3.3.3.3. 与行锁/表锁的联动机制

意向锁的生命周期完全依附于行锁，是行锁的“影子”：

• 行锁触发意向锁：当事务对某行加行锁（S/X）时，InnoDB 引擎会自动通知服务器层，在表级加对应的意向锁（IS 或 IX）。

• 例：SELECT * FROM t WHERE id=1 FOR UPDATE（加行 X 锁）→ 服务器层加表级 IX 锁（意向排他锁）。

• 表锁检查意向锁：当事务尝试加服务器层表锁时，服务器层会检查表级的意向锁：

• 若表级有 IX 锁（意向排他锁），说明存储引擎中有行锁在活动，表锁（如 LOCK TABLES … WRITE）会被阻塞；

• 若表级有 IS 锁（意向共享锁），说明存储引擎中有行读锁，表读锁（LOCK TABLES … READ）可兼容，表写锁仍被阻塞。

3.3.3.4. 为什么意向锁“轻量”？

意向锁的“轻量”源于其极小的状态空间和自动同步机制：

• 锁范围有限：仅锁定“整张表”的概念（表级），不涉及具体数据行，无需维护每行的锁状态（如行锁的 heap_no 或 trx_id）。

• 状态极简：仅需记录两种“意图”——IS（有事务要读行）、IX（有事务要写行），逻辑复杂度远低于行锁。

• 自动同步：由 InnoDB 引擎在加/解锁行锁时自动触发，无需手动管理，无额外的人工或系统开销。

3.3.3.5. 价值总结：用最小开销换最大正确性

意向锁的本质是用表级锁的“轻量状态”，连接服务器层与存储引擎层的锁机制：

• 对服务器层：快速判断表锁是否与行锁冲突，避免遍历所有行的高成本；

• 对存储引擎：无需关心服务器层的表锁，专注管理行锁；

• 对整体并发：既保证了数据一致性（避免表锁与行锁冲突），又维持了高并发性能。

3.4 按算法分 (InnoDB引擎)

1 记录锁 (Record Lock)

2 间隙锁 (Gap Lock)

3 临键锁 (Next-key Lock)

3.4.1 实操说明

操作类型常见场景锁类型锁范围隔离级别依赖主要冲突对象备注
SELECT普通查询（无 FOR UPDATE/SHARE）无锁无无无读不加锁（快照读）
SELECT FOR UPDATE等值查询（记录存在）记录锁（LOCK_REC_NOT_GAP）具体记录（如 id=3）RR/RC同记录的记录锁、临键锁锁住目标行，阻止修改/删除
SELECT FOR UPDATE等值查询（记录不存在，RR）间隙锁（LOCK_GAP）相邻记录的间隙（如 (1,5)）RR同间隙的间隙锁、插入意向锁防其他事务插入缺失的记录（幻读）
SELECT FOR UPDATE等值查询（记录不存在，RC）无锁无RC无RC 无间隙锁，仅读不加锁
SELECT FOR UPDATE范围查询（如 id>2，RR）临键锁（LOCK_NEXT_KEY）记录+前驱间隙（如 (3,3]、(3,5]）RR同记录的记录锁、间隙锁锁住范围所有记录及间隙（防幻读）
SELECT FOR UPDATE范围查询（如 id>2，RC）记录锁符合条件的记录（如 id=3,5）RC同记录的记录锁RC 无间隙锁，仅锁存在的记录
INSERT插入新记录（任意场景）插入意向锁（LOCK_INSERT_INTENTION）相邻记录的间隙（如 (1,5)）无（始终加）同间隙的普通间隙锁、记录锁协调插入互斥，与普通锁冲突
DELETE等值删除（记录存在）记录锁（LOCK_REC_NOT_GAP）具体记录（如 id=3）RR/RC同记录的记录锁、临键锁锁住目标行，阻止修改/插入
DELETE范围删除（如 id>2，RR）临键锁（LOCK_NEXT_KEY）记录+前驱间隙（如 (3,3]、(3,5]）RR同记录的记录锁、间隙锁锁住范围所有记录及间隙（防幻读）
DELETE范围删除（如 id>2，RC）记录锁符合条件的记录（如 id=3,5）RC同记录的记录锁RC 无间隙锁，仅锁存在的记录
UPDATE等值更新（记录存在）记录锁（LOCK_REC_NOT_GAP）具体记录（如 id=3）RR/RC同记录的记录锁、临键锁锁住目标行，阻止修改/插入
UPDATE范围更新（如 id>2，RR）临键锁（LOCK_NEXT_KEY）记录+前驱间隙（如 (3,3]、(3,5]）RR同记录的记录锁、间隙锁锁住范围所有记录及间隙（防幻读）
UPDATE范围更新（如 id>2，RC）记录锁符合条件的记录（如 id=3,5）RC同记录的记录锁RC 无间隙锁，仅锁存在的记录
3.4.1.1 INSERT
  • 插入使用的是插入意向锁
3.4.1.1 insert into -1
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
0.0SESSION 2 START - Waiting for Session1…
0.3SESSION 1 START - INSERT=== Round 0: Testing SELECT & INSERT ===
~mysql> SELECT * FROM example_single_pk
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~3 rows (0.03s)
~=== Round -1: Testing INSERT ID=-1 ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> INSERT INTO example_single_pk (id) VALUES (-1)
~Query OK, 1 row affected (0.03s)
  • Session 1 插入 ID=-1 的记录，并保持事务未提交，持有插入意向锁。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
0.7mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE
3.7ERROR: SELECT timeout (3.04s) (3.04s)
4.1mysql> INSERT INTO example_single_pk (id) VALUES (-1)
7.1ERROR: INSERT timeout (3.03s) (3.03s)
  • Session 2 查询 ID=-1 的记录，发现记录存在，但被 Session 1 持有插入意向锁，导致查询阻塞等待锁释放，最终超时失败。
  • Session 2 尝试插入 ID=-1 的记录，同样因为冲突而超时失败。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
7.4mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
7.5SELECT returned 0 rows (0.03s)
7.7mysql> INSERT INTO example_single_pk (id) VALUES (0)
~INSERT success (0.02s)
8.0mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR UPDATE
8.1SELECT returned 1 rows (0.03s)
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
8.4mysql> INSERT INTO example_single_pk (id) VALUES (1)
~ERROR: INSERT duplicate (0.03s) (0.03s)
8.7mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
~SELECT returned 0 rows (0.03s)
9.1mysql> INSERT INTO example_single_pk (id) VALUES (2)
~INSERT success (0.03s)
9.4mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
9.7mysql> INSERT INTO example_single_pk (id) VALUES (3)
~INSERT success (0.03s)
10.1mysql> SELECT * FROM example_single_pk WHERE id = 4 FOR UPDATE
~SELECT returned 1 rows (0.03s)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
10.4mysql> INSERT INTO example_single_pk (id) VALUES (4)
~ERROR: INSERT duplicate (0.03s) (0.03s)
10.8mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE
~SELECT returned 1 rows (0.02s)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
11.1mysql> INSERT INTO example_single_pk (id) VALUES (5)
~ERROR: INSERT duplicate (0.03s) (0.03s)
11.5mysql> SELECT * FROM example_single_pk WHERE id = 6 FOR UPDATE
~SELECT returned 0 rows (0.03s)
11.9mysql> INSERT INTO example_single_pk (id) VALUES (6)
~INSERT success (0.06s)
12.3mysql> SELECT * FROM example_single_pk WHERE id = 7 FOR UPDATE
~SELECT returned 0 rows (0.04s)
12.6mysql> INSERT INTO example_single_pk (id) VALUES (7)
~INSERT success (0.03s)
12.7mysql> rollback
~Query OK, 0 rows affected
  • 后续无问题
3.4.1.3 insert into 0
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
12.9=== Round 0: Testing INSERT ID=0 ===
~mysql> start transaction
~Query OK, 0 rows affected
13.0mysql> INSERT INTO example_single_pk (id) VALUES (0)=== Round 1: Testing SELECT & INSERT ===
~Query OK, 1 row affected (0.02s)
  • Session 1 插入 ID=0 的记录，并保持事务未提交，持有插入意向锁。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
13.2mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE
13.3SELECT returned 0 rows (0.03s)
13.5mysql> INSERT INTO example_single_pk (id) VALUES (-1)
13.6INSERT success (0.03s)
13.8mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
16.9ERROR: SELECT timeout (3.04s) (3.04s)
17.1mysql> INSERT INTO example_single_pk (id) VALUES (0)
20.2ERROR: INSERT timeout (3.04s) (3.04s)
  • Session 2 查询 ID=0 的记录，发现记录存在，但被 Session 1 持有插入意向锁，导致查询阻塞等待锁释放，最终超时失败。
  • Session 2 尝试插入 ID=0 的记录，同样因为冲突而超时失败。
  • 后续皆如此，不再做赘述。
3.4.1.2 SELECT FOR UPDATE
3.4.1.2.1 select for update -1
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
0.0SESSION 2 START - Waiting for Session1…
0.3SESSION 1 START - SELECT_FOR_UPDATE
~mysql> SELECT * FROM example_single_pk
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~3 rows (0.03s)
~=== Round -1: Testing SELECT_FOR_UPDATE ID=-1 ===
~mysql> start transaction
~Query OK, 0 rows affected
0.4mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE=== Round 0: Testing SELECT & INSERT ===
~Query returned 0 rows (0.03s)
  • 数据库内数据为 ID=1,4,5
  • Session1 ID=-1 进行 SELECT FOR UPDATE，持有间隙锁 [-∞, 1) 范围内没有记录，锁定间隙 [-∞, 1)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
0.7mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE
0.8SELECT returned 0 rows (0.03s)
1.1mysql> INSERT INTO example_single_pk (id) VALUES (-1)
4.1ERROR: INSERT timeout (3.03s) (3.03s)
4.4mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
~SELECT returned 0 rows (0.03s)
4.7mysql> INSERT INTO example_single_pk (id) VALUES (0)
7.7ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.2 select for update 0
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
12.9=== Round 0: Testing SELECT_FOR_UPDATE ID=0 ====== Round 1: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
~Query returned 0 rows (0.03s)
  • Session1 ID=0 进行 SELECT FOR UPDATE，持有间隙锁 [-∞, 1) 范围内没有记录，锁定间隙 [-∞, 1)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
13.2mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE
~SELECT returned 0 rows (0.03s)
13.6mysql> INSERT INTO example_single_pk (id) VALUES (-1)
16.6ERROR: INSERT timeout (3.03s) (3.03s)
16.9mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
~SELECT returned 0 rows (0.03s)
17.2mysql> INSERT INTO example_single_pk (id) VALUES (0)
20.2ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.3 select for update 1
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
25.3=== Round 1: Testing SELECT_FOR_UPDATE ID=1 ===
~mysql> start transaction
~Query OK, 0 rows affected
25.4mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR UPDATE=== Round 2: Testing SELECT & INSERT ===
~Query returned 1 rows (0.02s)
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • Session1 ID=1 进行 SELECT FOR UPDATE，持有记录锁 ID=1，防止其他事务修改或删除该记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
27.3mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR UPDATE
30.4ERROR: SELECT timeout (3.05s) (3.05s)
30.7mysql> INSERT INTO example_single_pk (id) VALUES (1)
33.7ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.4 select for update 2
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
38.5=== Round 2: Testing SELECT_FOR_UPDATE ID=2 ===
~mysql> start transaction
~Query OK, 0 rows affected
38.6mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE=== Round 3: Testing SELECT & INSERT ===
~Query returned 0 rows (0.05s)
  • Session1 ID=2 进行 SELECT FOR UPDATE，持有间隙锁 (1,4) 范围内没有记录，锁定间隙 (1,4)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
41.0mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
41.1SELECT returned 0 rows (0.04s)
41.3mysql> INSERT INTO example_single_pk (id) VALUES (2)
44.4ERROR: INSERT timeout (3.04s) (3.04s)
44.7mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
45.0mysql> INSERT INTO example_single_pk (id) VALUES (3)
48.0ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.5 select for update 3
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
51.2=== Round 3: Testing SELECT_FOR_UPDATE ID=3 ====== Round 4: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~Query returned 0 rows (0.03s)
  • Session1 ID=3 进行 SELECT FOR UPDATE，持有间隙锁 (1,4) 范围内没有记录，锁定间隙 (1,4)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
53.5mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
~SELECT returned 0 rows (0.03s)
53.8mysql> INSERT INTO example_single_pk (id) VALUES (2)
56.9ERROR: INSERT timeout (3.03s) (3.03s)
57.2mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
57.5mysql> INSERT INTO example_single_pk (id) VALUES (3)
60.5ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.6 select for update 4
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
63.6=== Round 4: Testing SELECT_FOR_UPDATE ID=4 ====== Round 5: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id = 4 FOR UPDATE
~Query returned 1 rows (0.03s)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • Session1 ID=4 进行 SELECT FOR UPDATE，持有记录锁 ID=4，防止其他事务修改或删除该记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
67.4mysql> SELECT * FROM example_single_pk WHERE id = 4 FOR UPDATE
70.4ERROR: SELECT timeout (3.03s) (3.03s)
70.7mysql> INSERT INTO example_single_pk (id) VALUES (4)
73.8ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.7 select for update 5
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
~Query OK, 0 rows affected
76.2=== Round 5: Testing SELECT_FOR_UPDATE ID=5 ===
~mysql> start transaction
~Query OK, 0 rows affected
76.3mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE=== Round 6: Testing SELECT & INSERT ===
~Query returned 1 rows (0.02s)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • Session1 ID=5 进行 SELECT FOR UPDATE，持有记录锁 ID=5，防止其他事务修改或删除该记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
80.9mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE
84.0ERROR: SELECT timeout (3.03s) (3.03s)
84.3mysql> INSERT INTO example_single_pk (id) VALUES (5)
87.3ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.2.8 select for update 6
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
89.0=== Round 6: Testing SELECT_FOR_UPDATE ID=6 ====== Round 7: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id = 6 FOR UPDATE
~Query returned 0 rows (0.04s)
  • Session1 ID=6 进行 SELECT FOR UPDATE，持有间隙锁 [6, ∞) 范围内没有记录，锁定间隙 [6, ∞)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
94.6mysql> INSERT INTO example_single_pk (id) VALUES (6)
97.7ERROR: INSERT timeout (3.03s) (3.03s)
97.9mysql> SELECT * FROM example_single_pk WHERE id = 7 FOR UPDATE
98.0SELECT returned 0 rows (0.03s)
98.3mysql> INSERT INTO example_single_pk (id) VALUES (7)
101.4mysql> rollbackERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.3 SELECT FOR UPDATE RANGE
3.4.1.3.1 SELECT FOR UPDATE RANGE [-1, 1)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
0.0SESSION 2 START - Waiting for Session1…
0.2SESSION 1 START - SELECT_FOR_UPDATE_RANGE
0.3mysql> SELECT * FROM example_single_pk=== Round 0: Testing SELECT & INSERT ===
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~3 rows (0.03s)
~=== Round -1: Testing SELECT_FOR_UPDATE_RANGE ID=-1 ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= -1 AND id < 1 FOR UPDATE
~Range query returned 0 rows (0.03s)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [-1, 1) 进行 SELECT FOR UPDATE，持有间隙锁 [-∞, 1) 范围内没有记录，锁定间隙 [-∞, 1)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
0.6mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE
0.7SELECT returned 0 rows (0.03s)
1.0mysql> INSERT INTO example_single_pk (id) VALUES (-1)
4.1ERROR: INSERT timeout (3.04s) (3.04s)
4.4mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
~SELECT returned 0 rows (0.03s)
4.8mysql> INSERT INTO example_single_pk (id) VALUES (0)
7.8ERROR: INSERT timeout (3.03s) (3.03s)
  • 间隙锁 [-∞, 1) 范围内没有记录，锁定间隙 [-∞, 1)，防止其他事务插入该范围内的记录。
  • Session2 对ID=-1和ID=0的查询操作，未阻塞，因为共同持有了间隙锁。
  • Session2 对ID=-1和ID=0的插入操作被阻塞。
3.4.1.3.2 SELECT FOR UPDATE RANGE [0, 2)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
12.9=== Round 0: Testing SELECT_FOR_UPDATE_RANGE ID=0 ===
~mysql> start transaction
~Query OK, 0 rows affected
13.0mysql> SELECT * FROM example_single_pk WHERE id >= 0 AND id < 2 FOR UPDATE=== Round 1: Testing SELECT & INSERT ===
~Range query returned 1 rows (0.02s)
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [0, 2) 进行 SELECT FOR UPDATE，持有间隙锁 [-∞, 3) 范围内没有记录，锁定间隙 [-∞, 3)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
13.3mysql> SELECT * FROM example_single_pk WHERE id = -1 FOR UPDATE
~SELECT returned 0 rows (0.03s)
13.6mysql> INSERT INTO example_single_pk (id) VALUES (-1)
16.6ERROR: INSERT timeout (3.03s) (3.03s)
16.8mysql> SELECT * FROM example_single_pk WHERE id = 0 FOR UPDATE
16.9SELECT returned 0 rows (0.03s)
17.2mysql> INSERT INTO example_single_pk (id) VALUES (0)
20.2ERROR: INSERT timeout (3.03s) (3.03s)
20.5mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR UPDATE
23.5ERROR: SELECT timeout (3.04s) (3.04s)
23.8mysql> INSERT INTO example_single_pk (id) VALUES (1)
26.9ERROR: INSERT timeout (3.03s) (3.03s)
27.1mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
27.2SELECT returned 0 rows (0.03s)
27.5mysql> INSERT INTO example_single_pk (id) VALUES (2)
30.5ERROR: INSERT timeout (3.03s) (3.03s)
30.8mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
31.1mysql> INSERT INTO example_single_pk (id) VALUES (3)
34.1ERROR: INSERT timeout (3.03s) (3.03s)
34.4mysql> SELECT * FROM example_single_pk WHERE id = 4 FOR UPDATE
~SELECT returned 1 rows (0.03s)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
34.8mysql> INSERT INTO example_single_pk (id) VALUES (4)
~ERROR: INSERT duplicate (0.03s) (0.03s)
35.1mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE
35.2SELECT returned 1 rows (0.03s)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
35.4mysql> INSERT INTO example_single_pk (id) VALUES (5)
~ERROR: INSERT duplicate (0.03s) (0.03s)
35.7mysql> SELECT * FROM example_single_pk WHERE id = 6 FOR UPDATE
35.8SELECT returned 0 rows (0.03s)
36.1mysql> INSERT INTO example_single_pk (id) VALUES (6)
~INSERT success (0.03s)
36.5mysql> SELECT * FROM example_single_pk WHERE id = 7 FOR UPDATE
~SELECT returned 0 rows (0.03s)
36.8mysql> INSERT INTO example_single_pk (id) VALUES (7)
36.9mysql> rollbackINSERT success (0.03s)
~Query OK, 0 rows affected
3.4.1.3.3 SELECT FOR UPDATE RANGE [1, 3)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
37.2=== Round 1: Testing SELECT_FOR_UPDATE_RANGE ID=1 ====== Round 2: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= 1 AND id < 3 FOR UPDATE
~Range query returned 1 rows (0.02s)
~(1, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [1, 3) 进行 SELECT FOR UPDATE，持有间隙锁 [1, 3) 范围内没有记录，锁定间隙 [1, 3)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
39.0mysql> SELECT * FROM example_single_pk WHERE id = 1 FOR UPDATE
42.0ERROR: SELECT timeout (3.03s) (3.03s)
42.4mysql> INSERT INTO example_single_pk (id) VALUES (1)
45.4ERROR: INSERT timeout (3.04s) (3.04s)
45.7mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
45.8SELECT returned 0 rows (0.04s)
46.1mysql> INSERT INTO example_single_pk (id) VALUES (2)
49.1ERROR: INSERT timeout (3.03s) (3.03s)
49.5mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
49.8mysql> INSERT INTO example_single_pk (id) VALUES (3)
52.9ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.3.4 SELECT FOR UPDATE RANGE [2, 4)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
55.9=== Round 2: Testing SELECT_FOR_UPDATE_RANGE ID=2 ====== Round 3: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= 2 AND id < 4 FOR UPDATE
~Range query returned 0 rows (0.04s)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [2, 4) 进行 SELECT FOR UPDATE，持有间隙锁 [2, 4) 范围内没有记录，锁定间隙 [2, 4)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
58.4mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
58.5SELECT returned 0 rows (0.03s)
58.8mysql> INSERT INTO example_single_pk (id) VALUES (2)
61.8ERROR: INSERT timeout (3.03s) (3.03s)
62.1mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
62.4mysql> INSERT INTO example_single_pk (id) VALUES (3)
65.4ERROR: INSERT timeout (3.03s) (3.03s)
3.4.1.3.5 SELECT FOR UPDATE RANGE [3, 5)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
68.6=== Round 3: Testing SELECT_FOR_UPDATE_RANGE ID=3 ====== Round 4: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= 3 AND id < 5 FOR UPDATE
~Range query returned 1 rows (0.02s)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [3, 5) 进行 SELECT FOR UPDATE，持有间隙锁 [2, 5) 范围内没有记录，锁定间隙 [2, 5)，防止其他事务插入该范围
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
70.9mysql> SELECT * FROM example_single_pk WHERE id = 2 FOR UPDATE
71.0SELECT returned 0 rows (0.03s)
71.3mysql> INSERT INTO example_single_pk (id) VALUES (2)
74.3ERROR: INSERT timeout (3.04s) (3.04s)
74.7mysql> SELECT * FROM example_single_pk WHERE id = 3 FOR UPDATE
~SELECT returned 0 rows (0.03s)
75.0mysql> INSERT INTO example_single_pk (id) VALUES (3)
78.0ERROR: INSERT timeout (3.03s) (3.03s)
78.3mysql> SELECT * FROM example_single_pk WHERE id = 4 FOR UPDATE
81.3ERROR: SELECT timeout (3.03s) (3.03s)
81.6mysql> INSERT INTO example_single_pk (id) VALUES (4)
84.6ERROR: INSERT timeout (3.03s) (3.03s)
84.9mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE
~SELECT returned 1 rows (0.03s)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
85.2mysql> INSERT INTO example_single_pk (id) VALUES (5)
~ERROR: INSERT duplicate (0.03s) (0.03s)
3.4.1.3.6 SELECT FOR UPDATE RANGE [4, 6)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
86.8=== Round 4: Testing SELECT_FOR_UPDATE_RANGE ID=4 ====== Round 5: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= 4 AND id < 6 FOR UPDATE
~Range query returned 2 rows (0.03s)
~(4, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [4, 6) 进行 SELECT FOR UPDATE，持有间隙锁 [4, +∞) 范围内没有记录，锁定间隙 [4, +∞)，防止其他事务插入该范围内的记录。
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
90.5mysql> SELECT * FROM example_single_pk WHERE id = 4 FOR UPDATE
93.5ERROR: SELECT timeout (3.03s) (3.03s)
93.9mysql> INSERT INTO example_single_pk (id) VALUES (4)
96.9ERROR: INSERT timeout (3.03s) (3.03s)
97.2mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE
100.3ERROR: SELECT timeout (3.03s) (3.03s)
100.6mysql> INSERT INTO example_single_pk (id) VALUES (5)
103.6ERROR: INSERT timeout (3.03s) (3.03s)
103.9mysql> SELECT * FROM example_single_pk WHERE id = 6 FOR UPDATE
~SELECT returned 0 rows (0.03s)
104.3mysql> INSERT INTO example_single_pk (id) VALUES (6)
107.3ERROR: INSERT timeout (3.03s) (3.03s)
107.6mysql> SELECT * FROM example_single_pk WHERE id = 7 FOR UPDATE
107.7SELECT returned 0 rows (0.03s)
108.0mysql> INSERT INTO example_single_pk (id) VALUES (7)
111.0mysql> rollbackERROR: INSERT timeout (3.03s) (3.03s)
~Query OK, 0 rows affected
3.4.1.3.7 SELECT FOR UPDATE RANGE [5, 7)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
111.3=== Round 5: Testing SELECT_FOR_UPDATE_RANGE ID=5 ====== Round 6: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= 5 AND id < 7 FOR UPDATE
~Range query returned 1 rows (0.03s)
~(5, ‘2025-09-27 11:22:48’, ‘2025-09-27 11:22:48’)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [5, 7) 进行 SELECT FOR UPDATE，持有间隙锁 [5, +∞) 范围内没有记录，锁定间隙 [5, +∞)，防止其他事务插入该范围
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
116.0mysql> SELECT * FROM example_single_pk WHERE id = 5 FOR UPDATE
119.0ERROR: SELECT timeout (3.03s) (3.03s)
119.3mysql> INSERT INTO example_single_pk (id) VALUES (5)
122.3ERROR: INSERT timeout (3.03s) (3.03s)
122.7mysql> SELECT * FROM example_single_pk WHERE id = 6 FOR UPDATE
~SELECT returned 0 rows (0.03s)
123.0mysql> INSERT INTO example_single_pk (id) VALUES (6)
126.1ERROR: INSERT timeout (3.03s) (3.03s)
126.4mysql> SELECT * FROM example_single_pk WHERE id = 7 FOR UPDATE
~SELECT returned 0 rows (0.03s)
126.8mysql> INSERT INTO example_single_pk (id) VALUES (7)
129.8mysql> rollbackERROR: INSERT timeout (3.03s) (3.03s)
~Query OK, 0 rows affected
3.4.1.3.8 SELECT FOR UPDATE RANGE [6, 8)
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
130.1=== Round 6: Testing SELECT_FOR_UPDATE_RANGE ID=6 ====== Round 7: Testing SELECT & INSERT ===
~mysql> start transaction
~Query OK, 0 rows affected
~mysql> SELECT * FROM example_single_pk WHERE id >= 6 AND id < 8 FOR UPDATE
~Range query returned 0 rows (0.03s)
  • 数据库内数据为 ID=1,4,5
  • Session1 对范围 [6, 8) 进行 SELECT FOR UPDATE，持有间隙锁 [6, +∞) 范围内没有记录，锁定间隙 [6, +∞)，防止其他事务插入该范围
TimeSESSION 1 (LEFT)SESSION 2 (RIGHT)
135.3mysql> SELECT * FROM example_single_pk WHERE id = 6 FOR UPDATE
135.4SELECT returned 0 rows (0.03s)
135.6mysql> INSERT INTO example_single_pk (id) VALUES (6)
138.7ERROR: INSERT timeout (3.11s) (3.11s)
139.1mysql> SELECT * FROM example_single_pk WHERE id = 7 FOR UPDATE
~SELECT returned 0 rows (0.03s)
139.5mysql> INSERT INTO example_single_pk (id) VALUES (7)
142.5mysql> rollbackERROR: INSERT timeout (3.03s) (3.03s)
~Query OK, 0 rows affected
~SESSION 2 COMPLETE
142.8SESSION 1 COMPLETE

附录

  • python脚本，用于数据获取
  1
  2
  3
  4
  5
  6
  7
  8
  9
 10
 11
 12
 13
 14
 15
 16
 17
 18
 19
 20
 21
 22
 23
 24
 25
 26
 27
 28
 29
 30
 31
 32
 33
 34
 35
 36
 37
 38
 39
 40
 41
 42
 43
 44
 45
 46
 47
 48
 49
 50
 51
 52
 53
 54
 55
 56
 57
 58
 59
 60
 61
 62
 63
 64
 65
 66
 67
 68
 69
 70
 71
 72
 73
 74
 75
 76
 77
 78
 79
 80
 81
 82
 83
 84
 85
 86
 87
 88
 89
 90
 91
 92
 93
 94
 95
 96
 97
 98
 99
100
101
102
103
104
105
106
107
108
109
110
111
112
113
114
115
116
117
118
119
120
121
122
123
124
125
126
127
128
129
130
131
132
133
134
135
136
137
138
139
140
141
142
143
144
145
146
147
148
149
150
151
152
153
154
155
156
157
158
159
160
161
162
163
164
165
166
167
168
169
170
171
172
173
174
175
176
177
178
179
180
181
182
183
184
185
186
187
188
189
190
191
192
193
194
195
196
197
198
199
200
201
202
203
204
205
206
207
208
209
210
211
212
213
214
215
216
217
218
219
220
221
222
223
224
225
226
227
228
229
230
231
232
233
234
235
236
237
238
239
240
241
242
243
244
245
246
247
248
249
250
251
252
253
254
255
256
257
258
259
260
261
262
263
264
265
266
267
268
269
270
271
272
273
274
275
276
277
278
279
280
281
282
283
284
285
286
287
288
289
290
291
292
293
294
295
296
297
298
299
300
301
302
303
304
305
306
307
308
309
310
311
312
313
314
315
316
317
318
319
320
321
322
323
324
325
326
327
328
329
330
331
332
333
334
335
336
337
338
339
340
341
342
343
344
345
346
347
348
349
350
351
352
353
354
355
356
357
358
359
360
361
362
363
364
365
366
367
368
369
370
371
372
373
374
375
376
377
378
379
380
381
382
383
384
385
386
387
388
389
390
391
392
393
394
395
396
397
398
399
400
401
402
403
404
405
406
407
408
409
410
411
412
413
414
415
416
417
418
419
420
421
422
423
424
425
426
427
428
429
430
431
432
433
434
435
436
437
438
439
440
441
442
443
444
445
446
447
448
449
450
451
452
453
454
455
456
457
458
459
460
461
462
463
464
465
466
467
468
469
470
471
472
473
474
475
476
477
478
479
480
481
482
483
484
485
486
487

#!/usr/bin/env python3
"""
MySQL Lock Testing - Timeline Analysis Version
"""

import pymysql
import threading
import time
from datetime import datetime
from collections import defaultdict

DB_CONFIG = {
    'host': 'localhost',
    'user': 'haotian',
    'password': 'qwe123qwe123',
    'database': 'toy',
    'autocommit': False
}

# Test Configuration - Modify as needed
TEST_CONFIG = {
    # Session1 Configuration
    'session1_operation': 'select_for_update_range',  # 'insert', 'select_for_update', 'select_for_update_range'
    'insert_start': -1,     # Operation start ID
    'insert_end': 7,        # Operation end ID (exclusive)
    'range_size': 2,        # Range size (for select_for_update_range only)

    # Session2 Test Configuration
    'test_ids': [-1, 0, 1, 2, 3, 4, 5, 6, 7],  # ID list to test
    'lock_`timeout`': 3       # Lock wait `timeout` in seconds
}

# Global log collector
timeline_log = []
log_lock = threading.Lock()

def log_event(session, event_type, sql, result=None, error=None, duration=None):
    """Record event to timeline"""
    with log_lock:
        timestamp = datetime.now()
        timeline_log.append({
            'timestamp': timestamp,
            'session': session,
            'type': event_type,  # 'sql', 'info', 'error'
            'sql': sql,
            'result': result,
            'error': error,
            'duration': duration
        })

        # Real-time progress display
        time_str = timestamp.strftime("%H:%M:%S.%f")[:-3]
        if event_type == 'sql':
            progress_text = f"mysql> {sql}"
            if duration:
                progress_text += f" ({duration:.2f}s)"
        elif event_type == 'error':
            progress_text = f"ERROR: {error}"
            if duration:
                progress_text += f" ({duration:.2f}s)"
        else:
            progress_text = result or sql

        print(f"[{time_str}] [{session}] {progress_text}")

def session1():
    """Session1: Loop operations (INSERT or SELECT FOR UPDATE)"""
    conn = pymysql.connect(**DB_CONFIG)
    cursor = conn.cursor()

    operation_type = TEST_CONFIG['session1_operation']
    log_event('S1', 'info', '', f'SESSION 1 START - {operation_type.upper()}')

    # Check table status
    start = time.time()
    cursor.execute("SELECT * FROM example_single_pk")
    results = cursor.fetchall()
    duration = time.time() - start
    log_event('S1', 'sql', 'SELECT * FROM example_single_pk', results, duration=duration)

    # Loop operations
    try:
        for target_id in range(TEST_CONFIG['insert_start'], TEST_CONFIG['insert_end']):
            log_event('S1', 'info', '', f'=== Round {target_id}: Testing {operation_type.upper()} ID={target_id} ===')

            try:
                # Start transaction
                log_event('S1', 'sql', 'start transaction', 'Query OK, 0 rows affected')
                cursor.execute("START TRANSACTION")

                if operation_type == 'insert':
                    # INSERT operation
                    sql = f"INSERT INTO example_single_pk (id) VALUES ({target_id})"
                    start = time.time()
                    try:
                        cursor.execute(sql)
                        duration = time.time() - start
                        log_event('S1', 'sql', sql, 'Query OK, 1 row affected', duration=duration)
                    except pymysql.err.IntegrityError as e:
                        duration = time.time() - start
                        log_event('S1', 'error', sql, None, str(e), duration)

                elif operation_type == 'select_for_update':
                    # SELECT FOR UPDATE operation
                    sql = f"SELECT * FROM example_single_pk WHERE id = {target_id} FOR UPDATE"
                    start = time.time()
                    try:
                        cursor.execute(sql)
                        results = cursor.fetchall()
                        duration = time.time() - start
                        log_event('S1', 'sql', sql, None)
                        log_event('S1', 'info', '', f'Query returned {len(results)} rows ({duration:.2f}s)')
                        if results:
                            for row in results:
                                formatted_row = []
                                for item in row:
                                    if hasattr(item, 'strftime'):
                                        formatted_row.append(item.strftime('%Y-%m-%d %H:%M:%S'))
                                    else:
                                        formatted_row.append(item)
                                log_event('S1', 'info', '', str(tuple(formatted_row)))
                    except Exception as e:
                        duration = time.time() - start
                        log_event('S1', 'error', sql, None, str(e), duration)

                elif operation_type == 'select_for_update_range':
                    # SELECT FOR UPDATE range operation
                    range_size = TEST_CONFIG['range_size']
                    end_id = target_id + range_size
                    sql = f"SELECT * FROM example_single_pk WHERE id >= {target_id} AND id < {end_id} FOR UPDATE"
                    start = time.time()
                    try:
                        cursor.execute(sql)
                        results = cursor.fetchall()
                        duration = time.time() - start
                        log_event('S1', 'sql', sql, None)
                        log_event('S1', 'info', '', f'Range query returned {len(results)} rows ({duration:.2f}s)')
                        if results:
                            for row in results:
                                formatted_row = []
                                for item in row:
                                    if hasattr(item, 'strftime'):
                                        formatted_row.append(item.strftime('%Y-%m-%d %H:%M:%S'))
                                    else:
                                        formatted_row.append(item)
                                log_event('S1', 'info', '', str(tuple(formatted_row)))
                    except Exception as e:
                        duration = time.time() - start
                        log_event('S1', 'error', sql, None, str(e), duration)

                # Notify Session2 and wait
                session2_event.set()
                session1_event.wait()
                session1_event.clear()

                # ROLLBACK
                log_event('S1', 'sql', 'rollback', 'Query OK, 0 rows affected')
                cursor.execute("ROLLBACK")

            except Exception as e:
                log_event('S1', 'error', 'transaction', None, str(e))
                cursor.execute("ROLLBACK")

            time.sleep(0.2)

    except Exception as e:
        log_event('S1', 'error', 'main_loop', None, f'Session1 main loop error: {str(e)}')
    finally:
        log_event('S1', 'info', '', 'SESSION 1 COMPLETE')
        session2_event.set()  # Final notification
        conn.close()

def session2():
    """Session2: SELECT FOR UPDATE testing"""
    log_event('S2', 'info', '', 'SESSION 2 START - Waiting for Session1...')

    round_num = 0

    total_rounds = TEST_CONFIG['insert_end'] - TEST_CONFIG['insert_start']
    while round_num < total_rounds:
        session2_event.wait()
        session2_event.clear()

        if round_num >= total_rounds:
            break

        log_event('S2', 'info', '', f'=== Round {round_num}: Testing SELECT & INSERT ===')

        # Test all configured IDs, testing both SELECT FOR UPDATE and INSERT for each ID
        for test_id in TEST_CONFIG['test_ids']:
            # Test 1: SELECT FOR UPDATE
            conn1 = pymysql.connect(**DB_CONFIG)
            cursor1 = conn1.cursor()

            try:
                cursor1.execute(f"SET innodb_lock_wait_`timeout` = {TEST_CONFIG['lock_`timeout`']}")
                cursor1.execute("START TRANSACTION")

                # SELECT FOR UPDATE
                sql = f"SELECT * FROM example_single_pk WHERE id = {test_id} FOR UPDATE"
                log_event('S2', 'sql', sql, None)

                start = time.time()
                try:
                    cursor1.execute(sql)
                    results = cursor1.fetchall()
                    duration = time.time() - start
                    log_event('S2', 'info', '', f'SELECT returned {len(results)} rows ({duration:.2f}s)')
                    if results:
                        for row in results:
                            formatted_row = []
                            for item in row:
                                if hasattr(item, 'strftime'):
                                    formatted_row.append(item.strftime('%Y-%m-%d %H:%M:%S'))
                                else:
                                    formatted_row.append(item)
                            log_event('S2', 'info', '', str(tuple(formatted_row)))

                except pymysql.err.OperationalError as e:
                    duration = time.time() - start
                    if "Lock wait `timeout`" in str(e):
                        log_event('S2', 'error', '', result=None, error=f'SELECT `timeout` ({duration:.2f}s)', duration=duration)
                    else:
                        log_event('S2', 'error', '', result=None, error=f'SELECT error: {str(e)}', duration=duration)

                cursor1.execute("ROLLBACK")

            except Exception as e:
                log_event('S2', 'error', 'connection', None, f'SELECT connection error: {str(e)}')
            finally:
                conn1.close()

            # Test 2: INSERT
            conn2 = pymysql.connect(**DB_CONFIG)
            cursor2 = conn2.cursor()

            try:
                cursor2.execute(f"SET innodb_lock_wait_`timeout` = {TEST_CONFIG['lock_`timeout`']}")
                cursor2.execute("START TRANSACTION")

                # INSERT
                sql = f"INSERT INTO example_single_pk (id) VALUES ({test_id})"
                log_event('S2', 'sql', sql, None)

                start = time.time()
                try:
                    cursor2.execute(sql)
                    duration = time.time() - start
                    log_event('S2', 'info', '', f'INSERT success ({duration:.2f}s)')

                except pymysql.err.OperationalError as e:
                    duration = time.time() - start
                    if "Lock wait `timeout`" in str(e):
                        log_event('S2', 'error', '', result=None, error=f'INSERT `timeout` ({duration:.2f}s)', duration=duration)
                    else:
                        log_event('S2', 'error', '', result=None, error=f'INSERT error: {str(e)}', duration=duration)
                except pymysql.err.IntegrityError as e:
                    duration = time.time() - start
                    log_event('S2', 'error', '', result=None, error=f'INSERT duplicate ({duration:.2f}s)', duration=duration)

                cursor2.execute("ROLLBACK")

            except Exception as e:
                log_event('S2', 'error', 'connection', None, f'INSERT connection error: {str(e)}')
            finally:
                conn2.close()

        round_num += 1
        session1_event.set()

    log_event('S2', 'info', '', 'SESSION 2 COMPLETE')

def print_round_analysis(round_num):
    """Print single round analysis"""
    print(f"\n{'='*80}")
    print(f"Round {round_num} Analysis")
    print(f"{'='*80}")

    # Find events for this round
    round_events = []
    for event in timeline_log:
        if (event['session'] == 'S1' and f'Round {round_num}:' in str(event.get('result', ''))) or \
                (event['session'] == 'S2' and f'Round {round_num}:' in str(event.get('result', ''))):
            round_start_time = event['timestamp']
            break
    else:
        return

    # Collect all events for this round
    for event in timeline_log:
        relative_time = (event['timestamp'] - round_start_time).total_seconds()
        if 0 <= relative_time <= 20:  # Assume max 20 seconds per round
            round_events.append((relative_time, event))

    # Display by time columns
    operation_type = TEST_CONFIG['session1_operation'].upper()
    if operation_type == 'SELECT_FOR_UPDATE_RANGE':
        range_size = TEST_CONFIG['range_size']
        s1_header = f"SESSION 1 (RANGE+{range_size})"
    else:
        s1_header = f"SESSION 1 ({operation_type})"
    print(f"{'Time':>6} | {s1_header:^35} | {'SESSION 2 (SELECT & INSERT)':^35}")
    print("-" * 80)

    grouped = defaultdict(list)
    for rel_time, event in round_events:
        time_bucket = round(rel_time, 1)
        grouped[time_bucket].append(event)

    for time_bucket in sorted(grouped.keys()):
        events = grouped[time_bucket]
        s1_events = [e for e in events if e['session'] == 'S1']
        s2_events = [e for e in events if e['session'] == 'S2']

        max_events = max(len(s1_events), len(s2_events))
        for i in range(max_events):
            s1_text = ""
            s2_text = ""

            if i < len(s1_events):
                e = s1_events[i]
                if e['type'] == 'sql':
                    s1_text = f"mysql> {e['sql']}"
                    if e.get('duration'):
                        s1_text += f" ({e['duration']:.2f}s)"
                elif e['type'] == 'info':
                    s1_text = e['result'] or e['sql']

            if i < len(s2_events):
                e = s2_events[i]
                if e['type'] == 'sql':
                    s2_text = f"mysql> {e['sql']}"
                elif e['type'] == 'info':
                    if 'Query returned' in str(e['result']):
                        s2_text = e['result']
                    elif e['result'] and '(' in str(e['result']):
                        s2_text = e['result']
                elif e['type'] == 'error':
                    s2_text = f"ERROR: {e['error']}"

            time_str = f"{time_bucket:6.1f}" if i == 0 else ""
            s1_text = s1_text[:33]
            s2_text = s2_text[:33]
            print(f"{time_str:>6} | {s1_text:<35} | {s2_text:<35}")

    print("=" * 80)

def print_timeline_analysis():
    """Analyze and print timeline"""
    # Print analysis for each round first
    total_rounds = TEST_CONFIG['insert_end'] - TEST_CONFIG['insert_start']
    for round_num in range(total_rounds):
        print_round_analysis(round_num)

    print(f"\n{'='*150}")
    print("Complete Timeline Analysis")
    print("="*150)

    if not timeline_log:
        print("No events recorded")
        return

    # Group by time, find simultaneous events
    grouped_events = defaultdict(list)
    base_time = timeline_log[0]['timestamp']

    for event in timeline_log:
        # Calculate relative time (seconds)
        relative_time = (event['timestamp'] - base_time).total_seconds()
        # Group by 0.1 second intervals
        time_bucket = round(relative_time, 1)
        grouped_events[time_bucket].append(event)

    # Print column format
    print(f"{'Time':>6} | {'SESSION 1 (LEFT)':^70} | {'SESSION 2 (RIGHT)':^70}")
    print("-" * 150)

    for time_bucket in sorted(grouped_events.keys()):
        events = grouped_events[time_bucket]
        s1_events = [e for e in events if e['session'] == 'S1']
        s2_events = [e for e in events if e['session'] == 'S2']

        max_events = max(len(s1_events), len(s2_events))

        for i in range(max_events):
            s1_text = ""
            s2_text = ""

            if i < len(s1_events):
                e = s1_events[i]
                if e['type'] == 'sql':
                    s1_text = f"mysql> {e['sql']}"
                    if e['result'] and isinstance(e['result'], str):
                        s1_text += f"\n{e['result']}"
                    elif e['result'] and hasattr(e['result'], '__iter__'):
                        if len(e['result']) == 0:
                            s1_text += f"\nEmpty set"
                        else:
                            # 显示所有数据，格式化datetime
                            for row in e['result']:
                                formatted_row = []
                                for item in row:
                                    if hasattr(item, 'strftime'):  # datetime对象
                                        formatted_row.append(item.strftime('%Y-%m-%d %H:%M:%S'))
                                    else:
                                        formatted_row.append(item)
                                s1_text += f"\n{tuple(formatted_row)}"
                            s1_text += f"\n{len(e['result'])} rows"
                    if e['duration']:
                        s1_text += f" ({e['duration']:.2f}s)"
                elif e['type'] == 'error':
                    s1_text = f"ERROR: {e['error']}"
                    if e['duration']:
                        s1_text += f" ({e['duration']:.2f}s)"
                else:  # info
                    s1_text = e['result'] or e['sql']

            if i < len(s2_events):
                e = s2_events[i]
                if e['type'] == 'sql':
                    s2_text = f"mysql> {e['sql']}"
                    if e['result'] and isinstance(e['result'], str):
                        s2_text += f"\n{e['result']}"
                    elif e['result'] and hasattr(e['result'], '__iter__'):
                        if len(e['result']) == 0:
                            s2_text += f"\nEmpty set"
                        else:
                            # 显示所有数据，格式化datetime
                            for row in e['result']:
                                formatted_row = []
                                for item in row:
                                    if hasattr(item, 'strftime'):  # datetime对象
                                        formatted_row.append(item.strftime('%Y-%m-%d %H:%M:%S'))
                                    else:
                                        formatted_row.append(item)
                                s2_text += f"\n{tuple(formatted_row)}"
                            s2_text += f"\n{len(e['result'])} rows"
                    if e['duration']:
                        s2_text += f" ({e['duration']:.2f}s)"
                elif e['type'] == 'error':
                    s2_text = f"ERROR: {e['error']}"
                    if e['duration']:
                        s2_text += f" ({e['duration']:.2f}s)"
                else:  # info
                    s2_text = e['result'] or e['sql']

            # Handle multi-line text
            s1_lines = s1_text.split('\n') if s1_text else ['']
            s2_lines = s2_text.split('\n') if s2_text else ['']
            max_lines = max(len(s1_lines), len(s2_lines))

            for j in range(max_lines):
                time_str = f"{time_bucket:6.1f}" if i == 0 and j == 0 else ""
                s1_line = s1_lines[j] if j < len(s1_lines) else ""
                s2_line = s2_lines[j] if j < len(s2_lines) else ""

                # Increase display width, reduce truncation
                s1_line = s1_line[:80] if s1_line.startswith('mysql>') else s1_line[:70]
                s2_line = s2_line[:80] if s2_line.startswith('mysql>') else s2_line[:70]

                print(f"{time_str:>6} | {s1_line:<70} | {s2_line:<70}")

    print("=" * 150)

# Event objects
session1_event = threading.Event()
session2_event = threading.Event()

if __name__ == "__main__":
    operation_type = TEST_CONFIG['session1_operation'].upper()
    print("MySQL Lock Test - Timeline Collection Mode")
    print(f"Session1: {operation_type} operations")
    print(f"Session2: SELECT FOR UPDATE & INSERT tests")
    print("Collecting all events with timestamps...")

    t1 = threading.Thread(target=session1)
    t2 = threading.Thread(target=session2)

    t2.start()
    time.sleep(0.1)
    t1.start()

    t1.join()
    t2.join()

    print_timeline_analysis()
    print("\nAnalysis Complete!")
Updated on 2025-09-28  07f4212
Read Markdown
 MySQL, Chinese, Lock
Back | Home