mysql 共享锁和排他锁意向锁记录锁 Gap Locks Next-Key Locks 插入意向锁介绍

原创

TvT~ 2023-02-27 09:44:40 博主文章分类：数据库 ©著作权

文章标签 mysql 数据库 database MySQL 记录锁 文章分类 JavaScript 前端开发

©著作权归作者所有：来自51CTO博客作者TvT~的原创作品，请联系作者获取转载授权，否则将追究法律责任

文章目录

前言：
共享锁和排它锁
LOCK TABLES 和 UNLOCK TABLES 语句
意向锁
记录锁Record Locks
间隙锁 Gap Locks
下一键锁定 next-key
插入意图锁

前言：

与sql标准不同的地方在于innodb存储引擎在可重读事务隔离级别下使用的是Next-Key Lock锁算法，因此可以避免幻读的产生，与其他数据库系统是不同的，所以innodb的可重复读已经可以完全保证事务的隔离性要求，即达到了sql标准的可串行化隔离级别。但是在分布式事务的情况下innodb一般用到可串行化隔离级别。
MySQL InnoDB 存储引擎的默认支持的隔离级别是 REPEATABLE-READ（可重读）。我们可以通过SELECT @@tx_isolation;命令来查看，MySQL 8.0 该命令改为SELECT @@transaction_isolation

共享锁和排它锁

InnoDB实现标准的行级锁定，其中有两种类型的锁：共享(S)锁和排他(X)锁。

共享(S)锁允许持有锁的事务读取一行。

排他(X)锁允许持有锁的事务更新或删除行。

如果事务T1在行r上持有共享(S)锁，则来自某些不同事务T2的对行r的锁请求将按以下方式处理：

T2对S锁定的请求可以立即获得批准。结果，T1和T2都在r上保持S锁定。

T2对X锁定的请求无法立即获得批准。

如果事务T1在行r上拥有排他(X)锁，则不能立即批准来自某个不同事务T2的对r上任一类型的锁的请求。相反，事务T2必须 await 事务T1释放对行r的锁定
隔离级别与锁的关系：

在Read Uncommitted级别下，读操作不加S锁；
在Read Committed级别下，读操作需要加S锁，但是在语句执行完以后释放S锁；
在Repeatable Read级别下，读操作需要加S锁，但是在事务提交之前并不释放S锁，也就是必须等待事务执行完毕以后才释放S锁。
在Serialize级别下，会在Repeatable Read级别的基础上，添加一个范围锁。保证一个事务内的两次查询结果完全一样，而不会出现第一次查询结果是第二次查询结果的子集

LOCK TABLES 和 UNLOCK TABLES 语句

LOCK TABLES
tbl_name [[AS] alias] lock_type
[, tbl_name [[AS] alias] lock_type] …
lock_type: {
READ [LOCAL] | [LOW_PRIORITY] WRITE }
UNLOCK TABLES

MySQL 使 Client 端会话能够显式地获取 table 锁，以便与其他会话协作访问 table，或者防止其他会话在会话需要互斥访问期间修改 table。会话只能为其自身获取或释放锁。一个会话无法获取另一会话的锁，也不能释放另一会话持有的锁。

在更新 table 时，锁可用于模拟事务或提高速度。 table 锁定限制和条件中对此进行了更详细的说明。

LOCK TABLES明确获取当前 Client 端会话的 table 锁。可以为基本 table 或视图获取 table 锁。您必须具有LOCK TABLES特权和SELECT特权才能锁定每个对象。

对于视图锁定，LOCK TABLES将视图中使用的所有基本 table 添加到要锁定的 table 集中，并自动锁定它们。从 MySQL 5.7.32 开始，LOCK TABLES检查视图定义器是否对基于视图的 table 具有适当的特权。

如果使用LOCK TABLES显式锁定 table，则触发器中使用的任何 table 也会隐式锁定，如锁定 table 和触发器中所述。

UNLOCK TABLES明确释放当前会话持有的所有 table 锁。 LOCK TABLES在获取新锁之前隐式释放当前会话持有的所有 table 锁。

意向锁

InnoDB支持多重粒度锁定，它允许行锁和 table 锁并存。例如，诸如锁 table（上面LOCK TABLES）之类的语句在指定的 table 上具有排他锁(X锁)。为了使在多个粒度级别上的锁定切实可行，InnoDB使用intention locks。意向锁是 table 级锁，指示事务稍后对 table 中的行需要哪种类型的锁(共享锁或排他锁)。有两种类型的意图锁：

意向共享锁(IS) 表示事务打算对 table 中的各个行设置“共享”锁。
如果对一个数据对象加IS锁，表示它的后裔结点拟（意向）加 S锁。例如，要对某个元组加 S锁，则要首先对关系和数据库加 IS锁。
意向排他锁(IX) 表示事务打算对 table 中的各个行设置排他锁
如果对一个数据对象加 IX锁，表示它的后裔结点拟（意向）加 X锁。例如，要对某个元组加 X锁，则要首先对关系和数据库加 IX锁。
例如：
update new_table set user_id = 911 where id = 1;
假设我们执行这么一条语句，innodb除了在id=1的这条记录上增加了行级X锁之前，还对所在表添加了一个意向排它锁。

这个时候如果我们有针对 new_table 的表级锁操作，如：alter table、drop table、lock table 的操作时，会先检查对应表是否存在意向排它锁，若存在则等待锁释放。

为什么这么做？

没有意向锁，你做表锁的话，要挨行去扫描行锁吧。

同时意向锁之间、意向锁和行锁也不存在冲突。

比如上面第一条sql不提交，我在另外事务中执行：

update new_table set user_id = 119 where id = 2;

相当于又给new_table加了一个意向排它锁，但是因为意向X锁之间也不冲突，所以不会被锁住。（id=1,2 要切实存在）.

锁之间的相容与互斥表：

mysql 共享锁和排他锁意向锁记录锁 Gap Locks Next-Key Locks 插入意向锁介绍_记录锁

如果锁与现有锁兼容，则将其授予请求的事务，但如果与现有锁冲突，则不授予该请求。事务 await 直到冲突的现有锁被释放。如果锁定请求与现有锁定发生冲突并且由于会导致deadlock而无法被授予，则会发生错误。

记录锁Record Locks

记录锁定是对索引记录的锁定。例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;阻止任何其他事务插入，更新或删除t.c1的值为10的行。

记录锁始终锁定索引记录，即使没有定义索引的 table 也是如此。在这种情况下，InnoDB将创建一个隐藏的聚集索引，并将该索引用于记录锁定。
记录锁定的事务数据在显示引擎的 INNODB 状态和InnoDB monitor输出中看起来类似于以下内容：

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

间隙锁 Gap Locks

间隙锁定是对索引记录之间的间隙的锁定，或者是对第一个或最后一个索引记录之前的间隙的锁定。例如，SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;防止其他事务将15的值插入到t.c1列中，无论该列中是否已经有这样的值，因为该范围中所有现有值之间的间隙都被锁定。

间隙可能跨越单个索引值，多个索引值，甚至为空。

间隙锁是性能和并发性之间权衡的一部分，并且在某些事务隔离级别而非其他级别中使用。

对于使用唯一索引来锁定唯一行来锁定行的语句，不需要间隙锁定。 (这不包括搜索条件仅包含多列唯一索引的某些列的情况；在这种情况下，会发生间隙锁定.)例如，如果id列具有唯一索引，则以下语句仅使用具有id值 100 的行的索引记录锁，其他会话是否在前面的间隙中插入行都没有关系：

SELECT * FROM child WHERE id = 100;
如果id未构建索引或索引不唯一，则该语句会锁定前面的间隙。

在这里还值得注意的是，可以通过不同的事务将冲突的锁保持在间隙上。例如，事务 A 可以在间隙上保留一个共享的间隙锁(间隙 S 锁)，而事务 B 可以在同一间隙上保留排他的间隙锁(间隙 X 锁)。允许冲突的间隙锁的原因是，如果从索引中清除记录，则必须合并由不同事务保留在记录上的间隙锁。

InnoDB中的间隙锁是“完全禁止的”，这意味着它们的唯一目的是防止其他事务插入间隙。间隙锁可以共存。一个事务进行的间隙锁定不会阻止另一事务对相同的间隙进行间隙锁定。共享和独占间隙锁之间没有区别。它们彼此不冲突，并且执行相同的功能。

间隙锁定可以显式禁用。如果将事务隔离级别更改为READ COMMITTED或启用innodb_locks_unsafe_for_binlog系统变量(现已弃用)，则会发生这种情况。在这种情况下，将禁用间隙锁定进行搜索和索引扫描，并且仅将其用于外键约束检查和重复键检查。

使用READ COMMITTED隔离级别或启用innodb_locks_unsafe_for_binlog还有其他效果。 MySQL 评估WHERE条件后，将释放不匹配行的记录锁。对于UPDATE语句，InnoDB进行“半一致”读取，以便将最新的提交版本返回给 MySQL，以便 MySQL 可以确定该行是否与UPDATE的WHERE条件匹配

下一键锁定 next-key

下一键锁定是索引记录上的记录锁定和索引记录之前的间隙上的间隙锁定的组合。

InnoDB以这种方式执行行级锁定：即当它搜索或扫描 table 索引时，它将在遇到的索引记录上设置共享或互斥锁。因此，行级锁实际上是索引记录锁。索引记录上的下一键锁定也会影响该索引记录之前的“间隙”。即，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话在索引中的记录R上具有共享或排他锁，则另一会话无法按照索引 Sequences 在R之前的间隙中插入新的索引记录。

假定索引包含值 10、11、13 和 20.此索引的可能的下一键锁定涵盖以下间隔，其中，圆括号 table 示排除区间端点，方括号 table 示包括端点：

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)
对于最后一个时间间隔，next-key 锁定将间隙锁定在索引中的最大值之上，并且“ supremum”伪记录的值高于索引中实际的任何值。最高不是 true 的索引记录，因此，实际上，此下一键锁定仅锁定跟随最大索引值的间隙。

默认情况下，InnoDB以REPEATABLE READ事务隔离级别运行。在这种情况下，InnoDB使用 next-key 锁定进行搜索和索引扫描，这可以防止幻读

为了防止产生幻读，InnoDB使用了称为下一键锁定的算法，该算法将索引行锁定与间隙锁定结合在一起。 InnoDB执行行级锁定的方式是，当它搜索或扫描 table 索引时，会在遇到的索引记录上设置共享或互斥锁。因此，行级锁实际上是索引记录锁。此外，索引记录上的下一键锁定也会影响该索引记录之前的“间隙”。即，下一键锁定是索引记录锁定加上索引记录之前的间隙上的间隙锁定。如果一个会话在索引中的记录R上具有共享或排他锁，则另一会话不能按索引 Sequences 在R之前的间隙中插入新的索引记录。

插入意图锁

插入意图锁定是一种在行插入之前通过INSERT操作设置的间隙锁定。此锁发出插入意图的 signal 是，如果多个事务未插入间隙中的相同位置，则无需 await 彼此插入的多个事务。假设有索引记录，其值分别为 4 和 7，单独的事务分别尝试插入值 5 和 6，在获得插入行的排他锁之前，每个事务都使用插入意图锁来锁定 4 和 7 之间的间隙，但不要互相阻塞，因为行是无冲突的。

下面的示例演示了在获得对插入记录的排他锁之前，使用插入意图锁的事务。该示例涉及两个 Client 端 A 和 B。

Client 端 A 创建一个包含两个索引记录(90 和 102)的 table，然后启动一个事务，该事务将排他锁放置在 ID 大于 100 的索引记录上。排他锁在记录 102 之前包括一个间隙锁：

mysql> CREATE TABLE child (id int(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
mysql> INSERT INTO child (id) values (90),(102);

mysql> START TRANSACTION;
mysql> SELECT * FROM child WHERE id > 100 FOR UPDATE;

±----+
| id |
±----+
| 102 |
±----+
ClientB 开始 Transaction 以将记录插入空白。事务在 await 获得排他锁的同时获取插入意图锁。

mysql> START TRANSACTION;
mysql> INSERT INTO child (id) VALUES (101);

插入意图锁定的事务数据在显示引擎的 INNODB 状态和InnoDB monitor输出中看起来类似于以下内容：

RECORD LOCKS space id 31 page no 3 n bits 72 index PRIMARY of table
test.child trx id 8731 lock_mode X locks gap before rec insert
intention waiting Record lock, heap no 3 PHYSICAL RECORD: n_fields 3;
compact format; info bits 0 0: len 4; hex 80000066; asc f;; 1:
len 6; hex 000000002215; asc " ;; 2: len 7; hex 9000000172011c;
asc r ;;…