MySQL OCP888题解060-记录锁、间隙锁和临键锁

本文链接：https://blog.csdn.net/oddrock/article/details/129730911

文章目录

1、原题
- 1.1、英文原题
- 1.2、答案
2、题目解析
- 2.1、题干解析
- 2.2、选项解析
3、知识点
4、实验
- 4.1、实验1
5、总结

1、原题

1.1、英文原题

Examine the mydata table and SELECT statements:

You issue:
Mysql> begin;
Mysql> update mydata set a=0 where b=3;
How many rows are now protected by locks with the default InnoDB configuration?
A、one
B、one row and next-key lock for supremum
C、one row and a gap-lock
D、five

1.2、答案

2、题目解析

2.1、题干解析

本题考察InnoDB在可重复读的事务隔离级别下，没有索引的列的锁的情况。

2.2、选项解析

本题中的查询语句列没有建立索引，所以会使用临键锁，就是锁定从(negative infinity, 3]的这些记录，一共5条，所以选项D正确，其他选项错误。

3、知识点

3.1、知识点1：UPDATE … WHERE …时的锁

如果WHERE条件里的列有唯一索引，且只搜索唯一一条记录，则只上一个索引记录锁，就是只锁定这一行。
如果WHERE条件里的列不是唯一索引，或者不是只搜索唯一一条记录，则在搜索遇到的每一条记录上设置一个独占的next-key锁。

官方参考文档

3.2、知识点2：Record Locks(记录锁)

记录锁是对某行记录加的锁。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止任何其他事务插入、更新或删除t.c1的值为10这一行的记录。
记录锁的事务数据在SHOW ENGINE INNODB STATUS和InnoDB监控输出中显示类似于以下内容：

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10078 lock_mode X locks rec but not gap
Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

官方参考文档
 参考文档
 最重要参考文档

3.3、Gap Locks(间隙锁)

间隙锁基于非唯一索引，它锁定一段范围内的索引记录。间隙锁是对索引记录之间的间隙的锁，或者对第一条或最后一条索引记录之后的间隙的锁。当我们使用索引，无论是等值还是范围查询，没有命中一条记录时候，加的就是间隙锁。
例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE; 阻止其他事务向列t.c1插入一个15的值，无论该列中是否已经有这样的值，因为该范围中所有现有值之间的空隙被锁定。
对于使用唯一索引锁定行以搜索唯一行的语句，不需要间隙锁。(这不包括搜索条件包括多列的情况；在这种情况下，间隙锁定会发生。)例如，如果id列有一个唯一索引，下面的语句只对id值为100的行使用索引-记录锁。但如果id没有索引或有非唯一索引，该语句确实锁定了前面的间隙：SELECT * FROM child WHERE id = 100;
InnoDB中的间隙锁是 “纯抑制性的”，这意味着它们的唯一目的是防止其他事务插入到间隙。间隙锁可以同时存在。一个事务取得的间隙锁并不妨碍另一个事务在同一间隙上取得间隙锁。

官方参考文档

3.4、Next-Key Locks(下一键锁、临键锁)

当我们使用索引进行范围查询，命中了记录的情况下，就是使用了临键锁，他相当于记录锁+间隙锁。两种退化的情况：唯一性索引，等值查询匹配到一条记录的时候，退化成记录锁。没有匹配到任何记录的时候，退化成间隙锁。

假设一个索引包含10、11、13和20的值。这个索引可能的下一个键锁覆盖了以下的区间，其中圆括号表示排除了区间的端点，方括号表示包含了该端点。
(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
对于最后一个区间，下一个键锁住了索引中最大的值和 "supremum "伪记录之间的空隙，该伪记录的值高于索引中的任何实际值。supremum不是一个真正的索引记录，所以，实际上，这个下一个键锁只锁定了最大索引值之后的空隙。
默认情况下，InnoDB运行在REPEATABLE READ事务隔离级别。在这种情况下，InnoDB在搜索和索引扫描中使用下一个键锁，这可以防止幽灵行的出现。

在SHOW ENGINE INNODB STATUS和InnoDB监控输出中，下一个键锁的事务数据类似于以下内容。

RECORD LOCKS space id 58 page no 3 n bits 72 index `PRIMARY` of table `test`.`t`
trx id 10080 lock_mode X
Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0
 0: len 8; hex 73757072656d756d; asc supremum;;

Record lock, heap no 2 PHYSICAL RECORD: n_fields 3; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 00000000274f; asc     'O;;
 2: len 7; hex b60000019d0110; asc        ;;

官方参考文档

4、实验

4.1、实验1

4.1.1、实验目的

实验目的是为了证明针对没有索引的列作为WHERE条件进行UPDATE，且匹配到一条记录时，会使用临键锁。

4.1.2、实验前准备

MySQL5.7已安装并运行。

4.1.3、实验步骤

会话1，创建专用于本次测试的表，并插入数据：

mysql> CREATE TABLE t1.mydata (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a_idx` (`a`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
mysql> INSERT INTO d1.mydata VALUES(1,1,1),(2,1,1),(3,2,2),(4,2,2),(5,2,3);

会话1，启动事务，用如下语句更新数据，用没有索引的b列作为筛选条件：

mysql> BEGIN;
mysql> UPDATE d1.mydata SET a=0 WHERE b=3;

会话2，启动一个新的MySQL会话，如下更新语句都会失败：

mysql> UPDATE d1.mydata SET a=0 WHERE b=1;
^C^C -- query aborted
mysql> UPDATE d1.mydata SET a=0 WHERE b=2;
^C^C -- query aborted
mysql> UPDATE d1.mydata SET a=0 WHERE b=3;
^C^C -- query aborted