SELECT FOR UPDATE语句深度解析

SELECT FOR UPDATE语句深度解析

  MysqlSELECT ... FOR UPDATE 语句是日常使用较多的用于锁定资源,确保在多个事务读取数据时始终能够读取到最新版本的数据的有效语句。那么它是怎么实现呢?在经过官网文档以及大量实践的验证之后发现网上存在大量不严谨甚至错误的信息,因此通过本文对 SELECT FOR UPDATE 语句作出以下总结。

在具体介绍之前,先对目前网上教程或博客中会提到的几个常见误区进行纠正:

  • SELECT FOR UPDATE 在xx情况下会添加表级锁。

    请注意,在任何情况下 SELECT FOR UPDATE 都不会添加表级锁。事实上,在大部分情况下(DQL语句,DML语句,DDL语句)都不会添加表锁,取而代之的是各种类型的行锁。

      那么我们如何获取表锁呢?语句如下:

    1
    2
    LOCK TABLES xx READ; # 为xx表添加表级S锁
    LOCK TABLES xx WRITE; # 为xx表添加表级X锁

    然后我们可以通过以下语句来检测当前Mysql有哪些表获取了表级锁

    1
    SHOW OPEN TABLES WHERE In_use > 0

    更多的表级锁相关知识请参考官网介绍

  • SELECT FOR UPDATE 在未使用索引时会”锁表”。

    SELECT FOR UPDATE 确实可以通过 Next-key lock 锁住所有记录和间隙来实现和表锁类似的效果。但未使用索引并非充分条件,我们判断 SELECT FOR UPDATE 是否锁住了所有数据和间隙还需要看它的隔离级别。

那么影响我们判断 SELECT FOR UPDATE 语句持有什么锁的因素有哪些呢?在这里列出以下几点:

  • 隔离级别(RC/RR)
  • 执行计划(聚簇索引/唯一索引/二级索引/无索引)
  • 过滤条件(等值条件/范围条件)

以下分析内容均建立在已经了解Mysql的行级锁的类型和作用范围的基础上,同时列出几点必要的前提论据:

  • 一般情况下,RC级别是无法使用 Gap Lock 的,但在检查外键约束或者duplicate key检查时还是会用到的

    Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to READ COMMITTED. Under these circumstances, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.

  • 一般情况下,执行计划根据某个索引查询后,会将过滤完的记录加锁后返回给MySQL Server进行过滤。在RC隔离级别下,当记录不满足条件时MySQL Server会调用 handler::unlock_row() 告诉存储引擎释放锁(破坏了2PL规则),RR隔离级别下则会保持到事务提交

    • 2PL(两阶段加锁协议)是数据库中保证事务并发的控制方法,即保证多个事务在并发的情况下等同于串行的执行。它将加锁和解锁分为两个阶段。而为了在事务中能够明确的判断什么是加锁阶段,什么是解锁阶段,引入了S2PL(Strict-2PL),即在事务中只有提交(commit)或者回滚(rollback)时才是解锁阶段,其余时间为加锁阶段。
    • ICP(索引条件下推):是一种减少server 层和 engine 层之间交互的次数的优化方式。上面提到一般情况下对于根据索引查询返回的记录将交由MySQL Server进行过滤,而如果过滤条件是联合索引且无法走联合索引时,如:

      1
      2
      3
      # 联合索引:(index1, index2, index3)
      # 根据最左匹配原则无法走联合索引
      select x from xx where index1 = ‘xx’ and index3 like ‘%xxxx%’

      正常情况下在对index1进行筛选后的记录就要返回。而经过ICP优化,由于where的查询列属于该联合索引,那么会将对该where条件记录过滤后才返回给 server 层

    参考:

RC级别下的SELECT FOR UPDATE

虽然Mysql默认的事务隔离级别是RR,但是在大多数互联网应用中Mysql的隔离级别会设置为RC,因此我们也首先讨论RC隔离级别下的 SELECT FOR UPDATE

  • 在执行计划不走索引时,将只会为满足条件的记录添加 Record Lock

    执行计划不走索引代表sql会走聚簇索引的全扫描,对所有记录加锁后返回给MySQL Server进行过滤。过滤过程中不满足条件的记录的锁会被释放,因此最终只锁住了满足条件的记录

  • 在执行计划走聚簇索引时,将只为满足条件的记录添加 Record Lock

  • 在执行计划走唯一索引或二级索引时,将会为满足条件的记录所在的聚簇索引和二级索引添加 Record Lock

    为什么还需要在聚簇索引加锁呢?因为如果不锁聚簇索引意味着别的事务可以使用 update/delete,那么就失去了锁定资源的作用了

从上面的分析可以看出,在RC级别下任何情况下都不会出现”锁表”效果。但是请注意即使 SELECT FOR UPDATE 的目标记录没有被锁住,也是有可能造成阻塞的。原因在于Mysql对非索引过滤(即是由Mysql Server过滤)的记录加锁返回的过程是不会省略的,因此如果 SELECT FOR UPDATE 不走索引,那么Mysql会为聚簇索引的所有数据行尝试添加 Record Lock ,而一旦有任何一行已经被锁定,那么当前查询就会被阻塞。

RR级别下的SELECT FOR UPDATE

Mysql的RR级别为了解决幻读引入了 Gap Lock,这也为 SELECT FOR UPDATE 的加锁增加了很多可能性

  • 在执行计划不走索引时,将会聚簇索引中的所有记录添加 Next-key Lock,相当于”锁表”

    RR级别下非索引过滤的记录即使不符合过滤条件,锁也不会被释放。同时为了解决幻读,记录添加 Next-key Lock 来锁定间隙

  • 在执行计划走聚簇索引时,若是能够命中的等值查询,将只为满足条件的记录添加 Record Lock;否则将覆盖范围包含过滤范围的记录添加 Next-key Lock

    为什么只有在等值查询是才有可能添加 Record Lock ?因为范围查询内的数据存在幻读问题

  • 在执行计划走唯一索引时,锁住唯一索引的方式和聚簇索引相似,同时使用 Record Lock 锁住命中的聚簇索引

    为什么只需要使用 Record Lock 锁住聚簇索引?因为通过唯一索引可以保证过滤范围间无法插入数据(与插入意向锁互斥),因此只需要 Record Lock 锁来确定目标记录不被 update/delete 即可

  • 在执行计划走二级索引时,无论是否为等值查询都会为覆盖范围包含过滤范围的记录添加 Next-key,同时使用 Record Lock 锁住命中的聚簇索引

    为什么二级索引不区分等值查询呢?因为即使是等值查询也不能唯一定位二级索引中的数据,在一棵二级索引的B+树中,叶子结点由 二级索引列值 + 主键值 确定的,仅仅依靠二级索引列值还是相当于范围查询

Serializable下的SELECT FOR UPDATE

Serializable级别下 SELECT FOR UPDATE 的加锁方式基本和RR级别相同。比较特殊的是,Serializable下是不存在快照读的,即使查询语句不添加 for update 也会为记录添加共享锁

锁分析工具

Mysql提供了语句来查询当前持有锁的状态和类型等等,是验证我们的判断的利器。语句如下:

1
SELECT * FROM performance_schema.data_locks

它提供几个关键信息:

  • LOCK_TYPE:锁类型,RECORD 代表行锁,TABLE 代表表锁
  • LOCK_MODE:锁模式,X,REC_NOT_GAP 代表 Record Lock , X, GAP 代表 Gap Lock , X 代表 Next-key Lock
  • INDEX_NAME:锁定索引的名称
  • LOCK_DATA:与锁相关的数据,比如锁在主键上就是主键值

更多的字段解释参考 MySQL 8.0 Reference Manual 26.12.12.1 The data_locks Table

除此之外,Mysql还提供了查询当前正在执行的每个事务(不包括只读事务)的信息,比如隔离级别,内存中此事务的锁结构占用的总大小等等。语句如下:

1
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX

它提供几个关键信息:

  • TRX_ID:如果是非锁定的只读事务是没有该id的
  • TRX_REQUESTED_LOCK_ID:当前事务正在等待的锁id
  • TRX_TABLES_LOCKED:当前SQL语句具有行锁定的表的数量
  • TRX_LOCK_MEMORY_BYTES:内存中此事务的锁结构占用的总大小。
  • TRX_ISOLATION_LEVEL:当前事务的隔离级别

更多的字段解释参考 MySQL 8.0 Reference Manual 25.39.29 The INFORMATION_SCHEMA INNODB_TRX Table

验证

数据来源:Employees Sample Database,以employees表作为测试表,数据30万+,为first_name增加二级索引,新增uni_id字段以及唯一索引,最后表结构如下:

1
2
3
4
5
6
7
8
9
10
11
12
CREATE TABLE `employees` (
`emp_no` int(11) NOT NULL,
`birth_date` date NOT NULL,
`first_name` varchar(14) NOT NULL,
`last_name` varchar(16) NOT NULL,
`gender` enum('M','F') NOT NULL,
`hire_date` date NOT NULL,
`uni_id` int(10) NOT NULL AUTO_INCREMENT,
PRIMARY KEY (`emp_no`),
UNIQUE KEY `uk_uni_id` (`uni_id`) USING BTREE,
KEY `k_first_name` (`first_name`) USING BTREE
) ENGINE=InnoDB;

当然为了测试方便,也会额外插入一些数据

READ COMMITTED

以下测试SQL均包裹于事务中

1
2
3
4
set session transaction isolation level READ COMMITTED;
begin;
...
commit;

无索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 未命中,不锁定任何行
select * from employees where last_name = '1' for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6428
THREAD_ID: 57
EVENT_ID: 625
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)
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
# 命中,`Record Lock`锁定命中行聚簇索引
select * from employees where last_name = 'last_test' for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6434
THREAD_ID: 57
EVENT_ID: 792
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:21:7:313:140616331827224
ENGINE_TRANSACTION_ID: 6434
THREAD_ID: 57
EVENT_ID: 792
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331827224
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
2 rows in set (0.00 sec)

聚簇索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 未命中,不锁定任何行
select * from employees where emp_no = '1' for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6441
THREAD_ID: 57
EVENT_ID: 916
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)
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
# 命中,`Record Lock`锁定命中行聚簇索引
select * from employees where emp_no = '111' for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6440
THREAD_ID: 57
EVENT_ID: 894
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:21:7:313:140616331827224
ENGINE_TRANSACTION_ID: 6440
THREAD_ID: 57
EVENT_ID: 894
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331827224
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
2 rows in set (0.00 sec)

唯一索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 未命中,不锁定任何行
select * from employees where uni_id = 0 for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6443
THREAD_ID: 57
EVENT_ID: 960
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)
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
# 命中,`Record Lock`同时锁定命中行聚簇索引和唯一索引
select * from employees where uni_id = 1 for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6444
THREAD_ID: 57
EVENT_ID: 982
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:21:2074:604:140616331827224
ENGINE_TRANSACTION_ID: 6444
THREAD_ID: 57
EVENT_ID: 982
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: uk_uni_id
OBJECT_INSTANCE_BEGIN: 140616331827224
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:21:7:313:140616331827568
ENGINE_TRANSACTION_ID: 6444
THREAD_ID: 57
EVENT_ID: 982
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331827568
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
3 rows in set (0.00 sec)

二级索引

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
# 未命中,不锁定任何行
select * from employees where first_name = '1' for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6445
THREAD_ID: 57
EVENT_ID: 1004
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
1 row in set (0.00 sec)
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
# 命中,`Record Lock`同时锁定命中行聚簇索引和二级索引
select * from employees where first_name = 'first_test' for update
mysql> SELECT * FROM performance_schema.data_locks\G
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:1078:140616286645464
ENGINE_TRANSACTION_ID: 6446
THREAD_ID: 57
EVENT_ID: 1026
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286645464
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:21:1510:467:140616331827224
ENGINE_TRANSACTION_ID: 6446
THREAD_ID: 57
EVENT_ID: 1026
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: k_first_name
OBJECT_INSTANCE_BEGIN: 140616331827224
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'first_test', 111
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867761968:21:7:313:140616331827568
ENGINE_TRANSACTION_ID: 6446
THREAD_ID: 57
EVENT_ID: 1026
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331827568
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
3 rows in set (0.00 sec)

REPEATABLE READ

以下测试SQL均包裹于事务中

1
2
3
4
set session transaction isolation level REPEATABLE READ;
begin;
...
commit;

无索引

1
2
3
4
5
6
7
8
9
10
11
# 未命中,`Next-key Lock`锁定所有行
select * from employees where last_name = '1' for update
SELECT count(*) FROM performance_schema.data_locks
# 这里太多行了,就只能count看看
mysql> SELECT count(*) FROM performance_schema.data_locks;
+----------+
| count(*) |
+----------+
| 300997 |
+----------+
1 row in set (1.14 sec)

这里我们输出前五行锁观察下

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
mysql> SELECT * FROM performance_schema.data_locks limit 0, 5\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:1078:140616286647432
ENGINE_TRANSACTION_ID: 6447
THREAD_ID: 58
EVENT_ID: 885
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286647432
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:1:140616331831832
ENGINE_TRANSACTION_ID: 6447
THREAD_ID: 58
EVENT_ID: 885
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: supremum pseudo-record
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:3:140616331831832
ENGINE_TRANSACTION_ID: 6447
THREAD_ID: 58
EVENT_ID: 885
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 10001
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:4:140616331831832
ENGINE_TRANSACTION_ID: 6447
THREAD_ID: 58
EVENT_ID: 885
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 10003
*************************** 5. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:5:140616331831832
ENGINE_TRANSACTION_ID: 6447
THREAD_ID: 58
EVENT_ID: 885
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 10004
5 rows in set (0.83 sec)

在第二行中可以看到锁定了页中的最大行,我们再统计下锁定了多少页的最大行

1
2
3
4
5
6
7
mysql> SELECT count(*) FROM performance_schema.data_locks where lock_data = 'supremum pseudo-record';
+----------+
| count(*) |
+----------+
| 965 |
+----------+
1 row in set (1.22 sec)

而实际上该表被占用的页数和被锁定的最大行数是一致的,我们也可以查看锁结构个数来验证

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
mysql> SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX\G
*************************** 1. row ***************************
trx_id: 6447
trx_state: RUNNING
trx_started: 2019-06-18 14:58:05
trx_requested_lock_id: NULL
trx_wait_started: NULL
trx_weight: 966
trx_mysql_thread_id: 19
trx_query: NULL
trx_operation_state: NULL
trx_tables_in_use: 0
trx_tables_locked: 1
trx_lock_structs: 966
trx_lock_memory_bytes: 139472
trx_rows_locked: 300996
trx_rows_modified: 0
trx_concurrency_tickets: 0
trx_isolation_level: REPEATABLE READ
trx_unique_checks: 1
trx_foreign_key_checks: 1
trx_last_foreign_key_error: NULL
trx_adaptive_hash_latched: 0
trx_adaptive_hash_timeout: 0
trx_is_read_only: 0
trx_autocommit_non_locking: 0
1 row in set (0.00 sec)

一共有966个锁结构,而被锁住的就有965个页的最大行

聚簇索引

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
# 等值查询命中,范围查询未命中
# 等值查询由`Record Lock`锁定目标行(10001)
# 范围查询由`Next-key Lock`锁定覆盖该范围的二级索引的真实行(111)
select * from employees where emp_no = '10001' or emp_no < '1' for update
mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:1078:140616286647432
ENGINE_TRANSACTION_ID: 6452
THREAD_ID: 58
EVENT_ID: 972
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286647432
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:313:140616331831832
ENGINE_TRANSACTION_ID: 6452
THREAD_ID: 58
EVENT_ID: 972
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 111
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:3:140616331832176
ENGINE_TRANSACTION_ID: 6452
THREAD_ID: 58
EVENT_ID: 972
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331832176
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 10001
3 rows in set (0.00 sec)
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
# 等值查询未命中,范围查询命中
# 虽然等值查询未命中,但为了防止幻读,由 `Next-key Lock`锁定emp_no='111'的数据行
# 由于范围区间在(10000, 10002),因此锁定emp_no = '10001'和 emp_no='10003' 的数据行(此时数据库不存在emp_no='10002',存在emp_no='10003')
select * from employees where emp_no = '1' or (emp_no > '10000' and emp_no <= '10002') for update
mysql> mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:1078:140616286647432
ENGINE_TRANSACTION_ID: 6455
THREAD_ID: 58
EVENT_ID: 1068
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286647432
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:313:140616331831832
ENGINE_TRANSACTION_ID: 6455
THREAD_ID: 58
EVENT_ID: 1068
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:3:140616331832176
ENGINE_TRANSACTION_ID: 6455
THREAD_ID: 58
EVENT_ID: 1068
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331832176
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 10001
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:4:140616331832176
ENGINE_TRANSACTION_ID: 6455
THREAD_ID: 58
EVENT_ID: 1068
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331832176
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 10003
4 rows in set (0.00 sec)

唯一索引

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
# 范围查询命中uni_id=1的数据
# 查询范围由`Next-key Lock`锁定uni_id=1和uni_id=2的唯一索引数据行
# 命中的聚簇索引数据行由`Record Lock`锁定
select * from employees where uni_id >= 1 and uni_id < 2 for update
mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:1078:140616286647432
ENGINE_TRANSACTION_ID: 6459
THREAD_ID: 58
EVENT_ID: 1163
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286647432
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:2074:604:140616331831832
ENGINE_TRANSACTION_ID: 6459
THREAD_ID: 58
EVENT_ID: 1163
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: uk_uni_id
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:2074:605:140616331831832
ENGINE_TRANSACTION_ID: 6459
THREAD_ID: 58
EVENT_ID: 1163
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: uk_uni_id
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 2
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:313:140616331832176
ENGINE_TRANSACTION_ID: 6459
THREAD_ID: 58
EVENT_ID: 1163
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331832176
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
4 rows in set (0.00 sec)
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
# 等值查询命中
# 为唯一索引和聚簇索引命中行添加`Record Lock`锁定
select * from employees where uni_id = 1 for update
mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:1078:140616286647432
ENGINE_TRANSACTION_ID: 6460
THREAD_ID: 58
EVENT_ID: 1200
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286647432
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:2074:604:140616331831832
ENGINE_TRANSACTION_ID: 6460
THREAD_ID: 58
EVENT_ID: 1200
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: uk_uni_id
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 1
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:313:140616331832176
ENGINE_TRANSACTION_ID: 6460
THREAD_ID: 58
EVENT_ID: 1200
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331832176
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
3 rows in set (0.00 sec)

二级索引

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
# 等值查询命中
# 由`Next-key Lock`锁定first_name = 'first_test'下的二级索引
# 而因为只有一行('first_test', 111),因此为了锁定first_test中大于('first_test', 111)的数据,同时将包含该范围的('Flemming', 10987)进行了`Gap Lock`锁定
# 聚簇索引命中行添加`Record Lock`锁定
select * from employees where first_name = 'first_test' for update
mysql> SELECT * FROM performance_schema.data_locks\G;
*************************** 1. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:1078:140616286647432
ENGINE_TRANSACTION_ID: 6461
THREAD_ID: 58
EVENT_ID: 1229
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: NULL
OBJECT_INSTANCE_BEGIN: 140616286647432
LOCK_TYPE: TABLE
LOCK_MODE: IX
LOCK_STATUS: GRANTED
LOCK_DATA: NULL
*************************** 2. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:1510:467:140616331831832
ENGINE_TRANSACTION_ID: 6461
THREAD_ID: 58
EVENT_ID: 1229
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: k_first_name
OBJECT_INSTANCE_BEGIN: 140616331831832
LOCK_TYPE: RECORD
LOCK_MODE: X
LOCK_STATUS: GRANTED
LOCK_DATA: 'first_test', 111
*************************** 3. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:7:313:140616331832176
ENGINE_TRANSACTION_ID: 6461
THREAD_ID: 58
EVENT_ID: 1229
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: PRIMARY
OBJECT_INSTANCE_BEGIN: 140616331832176
LOCK_TYPE: RECORD
LOCK_MODE: X,REC_NOT_GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 111
*************************** 4. row ***************************
ENGINE: INNODB
ENGINE_LOCK_ID: 4867762872:21:1510:186:140616331832520
ENGINE_TRANSACTION_ID: 6461
THREAD_ID: 58
EVENT_ID: 1229
OBJECT_SCHEMA: employees
OBJECT_NAME: employees
PARTITION_NAME: NULL
SUBPARTITION_NAME: NULL
INDEX_NAME: k_first_name
OBJECT_INSTANCE_BEGIN: 140616331832520
LOCK_TYPE: RECORD
LOCK_MODE: X,GAP
LOCK_STATUS: GRANTED
LOCK_DATA: 'Flemming', 10987
4 rows in set (0.00 sec)
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×