MySQL InnoDB锁全攻略

Coder.Ren

已于 2024-07-14 16:35:42 修改

阅读量276

点赞数 6

分类专栏： MySQL 文章标签： mysql 数据库

于 2024-07-14 15:59:44 首次发布

本文链接：https://blog.csdn.net/qq_22129643/article/details/140417913

版权

MySQL 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

快速导航

共享锁和独占锁（Shared and Exclusive Locks）
意向锁（Intention Locks）
记录锁（Record Locks）
间隙锁（Gap Locks）
Next-Key 锁（Next-Key Locks）
插入意向锁（Insert Intention Locks）
AUTO-INC 锁（AUTO-INC Locks）
空间索引的谓词锁（Predicate Locks for Spatial Indexes）

共享锁和独占锁（Shared and Exclusive Locks）

InnoDB 实现标准行级锁定，

其中有两种类型的锁，共享（ S ）锁和独占（ X ）锁。

共享（ S ）锁允许持有该锁的事务读取一行。
独占（ X ）锁允许持有该锁的事务更新或删除行。

如果事务T1持有行r上的一个共享锁，那么来自不同事务T2的对行r上的一个锁的请求将被处理如下:

T2对S锁的请求可以立即被授予。因此，T1和T2都对r持有S锁。
T2对X锁的请求不能立即被授予。

如果事务T1持有行r上的排他(X)锁，那么某个不同事务T2对r上任何一种类型的锁的请求都不能立即被授予。相反，事务T2必须等待事务T1释放对行r的锁。

意向锁（Intention Locks）

InnoDB支持多粒度锁，允许行锁和表锁共存。

例如，像 LOCK TABLES…WRITE 接受指定表上的排他锁(X锁)。

为了使多粒度级别的锁变得实用，InnoDB使用了意向锁。

意向锁是表级别的锁，表明事务稍后需要对表中的一行使用哪种类型的锁(共享或互斥)。

有两种类型的意图锁:

意向共享锁(IS) ：表示事务打算在表中的单个行上设置共享锁。
意向排他锁(IX) ：表示事务打算对表中的每一行设置排他锁。

例如：

SELECT… FOR SHARE 设置IS锁
SELECT… FOR UPDATE 设置IX锁

意图锁定协议如下:

在一个事务获得表中某一行的共享锁之前，它必须首先获得表上的IS锁或更强的锁。
在事务获得表中某一行的排它锁之前，它必须首先获得表上的IX锁。

表级别的锁类型兼容性总结在下面的矩阵中

	`X`	`IX`	`S`	`IS`
`X`	冲突	冲突	冲突	冲突
`IX`	冲突	相容	冲突	相容
`S`	冲突	冲突	相容	相容
`IS`	冲突	相容	相容	相容

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

意图锁不会阻塞除了全表请求(例如，LOCK TABLES ... WRITE )。意图锁的主要目的是表明有人正在锁定某一行，或者将要锁定表中的某一行。

意图锁的事务数据在 SHOW ENGINE INNODB STATUS 和INNODB monitor输出中类似如下:

TABLE LOCK table `test`.`t` trx id 10080 lock mode IX

记录锁（Record Locks）

记录锁是索引记录上的锁。

例如，SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; 防止任何其他事务插入、更新或删除t.c1值为10的行。

记录锁总是锁定索引记录，即使定义的表没有索引。对于这种情况，InnoDB会创建一个隐藏的聚集索引，并使用这个索引来锁定记录。

在 SHOW ENGINE INNODB STATUS 和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没有被索引或有一个非唯一的索引，该语句会锁定前面的间隙。

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

Gap锁在InnoDB中是“纯粹的抑制性的”，这意味着它们的唯一目的是防止其他事务插入到Gap中。间隙锁可以共存。一个事务获得的间隙锁不会阻止另一个事务获得同一个间隙上的间隙锁。共享间隙锁和互斥间隙锁没有区别。它们彼此不冲突，并且执行相同的功能。

可以显式禁用间隙锁。如果将事务隔离级别更改为“读已提交”，则会发生此情况。在这种情况下，间隙锁对搜索和索引扫描是禁用的，只用于外键约束检查和重复键检查。

使用READ COMMITTED隔离级别还有其他影响。不匹配行的记录锁会在MySQL执行WHERE条件之后释放。对于UPDATE语句，InnoDB会进行“半一致”的读取，这样它就会返回最新的已提交版本到MySQL，这样MySQL就可以确定这一行是否符合更新的WHERE条件。

Next-Key 锁（Next-Key Locks）

next-key 锁是索引记录上的记录锁和索引记录前间隙上的间隙锁的组合。

InnoDB以这样的方式执行行级锁，当它搜索或扫描表索引时，它会在遇到的索引记录上设置共享或排他锁。

因此，行级锁实际上是索引-记录锁。索引记录上的next-key锁也会影响该索引记录之前的“间隔”。也就是说，next-key锁是索引记录锁加上索引记录之前的间隙锁。如果一个会话对索引中的记录R有一个共享或排他锁，那么另一个会话就不能在索引顺序中紧靠R之前的空隙中插入新的索引记录。

假设索引包含值10、11、13和20。该索引可能的next-key 锁涵盖以下区间，其中圆括号表示排除该区间端点，方括号表示包含该端点:

(negative infinity, 10]
(10, 11]
(11, 13]
(13, 20]
(20, positive infinity)

对于最后一个时间间隔，next-key lock锁定了索引中最大值以上的空隙，以及比索引中任何实际值都高的“supremum”伪记录。supremum并不是一个真正的索引记录，因此next-key lock实际上只锁住了最大索引值之后的空隙。

默认情况下，InnoDB以可重复读取事务隔离级别操作。在这种情况下，InnoDB对搜索和索引扫描使用下一键锁(next-key lock)，这可以防止出现幻影行(参见17.7.4节)。

下一键锁的事务数据在 SHOW ENGINE INNODB STATUS 和INNODB monitor输出中如下所示:

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        ;;

插入意向锁（Insert Intention Locks）

插入意向锁定是一种在插入行之前由 INSERT 操作设置的间隙锁定类型。

此锁表示插入的意向，即插入同一索引间隙的多个事务如果未在间隙内的同一位置插入，则无需相互等待。

假设存在值为 4 和 7 的索引记录。尝试分别插入值 5 和 6 的单独事务，在获得插入行的独占锁之前，每个事务都使用插入意向锁锁定 4 和 7 之间的间隙，但不会相互阻塞，因为行不冲突。

下面的示例演示了在获取插入记录的独占锁之前采用插入意向锁的事务。该示例涉及两个客户端，A 和 B。

客户端 A 创建一个包含两个索引记录（90 和 102）的表，然后启动一个事务，该事务对 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 |
+-----+

客户端 B 开始事务以将记录插入到间隙中。事务在等待获得独占锁时采用插入意向锁。

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

插入意向锁的事务数据在InnoDB监视器输出中 SHOW ENGINE INNODB STATUS 显示如下：

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  ;;...