MySQL InnoDB锁全攻略_mysql innodb写锁-CSDN博客

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

快速导航

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

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

InnoDB 实现标准行级锁定，

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

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

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

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

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

小结：

事务T1持有某行数据的共享锁，则另一个事务T2最多只能申请到该行数据的共享锁
事务T1持有某行数据的独占锁，则另一个事务T2不能申请到该行数据的任何锁

意向锁（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没有被索引或有一个非唯一的索引，该语句会锁定前面的间隙。

这里还值得注意的是，不同的事务可以在间隙上持有冲突的锁。

例如，事务A可以在一个间隙上持有一个共享间隙锁(gap S-lock)，而事务B可以在同一个间隙上持有一个独占间隙锁(gap X-lock)。允许冲突间隙锁的原因是，如果从索引中清除记录，则不同事务在记录上持有的间隔锁必须合并。间隙锁在InnoDB中是“纯粹的抑制性的”，这意味着它们的唯一目的是防止其他事务插入到间隙中。

间隙锁可以共存。

一个事务获得的间隙锁不会阻止另一个事务获得同一个间隙上的间隙锁。共享间隙锁和独占间隙锁没有区别。它们彼此不冲突，并且执行相同的功能。

可以显式禁用间隙锁。

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

使用“读已提交”隔离级别还有其他影响。

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

下一键锁（Next-Key Locks）

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

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

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

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

(-无穷大 , 10]
(10, 11]
(11, 13]
(13, 20]
(20, 无穷大)

对于最后一个区间，下一键锁锁定了索引中最大值以上的空隙，以及比索引中任何实际值都高的“上确界”伪记录。上确界并不是一个真正的索引记录，因此下一键锁实际上只锁住了最大索引值之后的间隙。

默认情况下，InnoDB以可重复读取事务隔离级别操作。

在这种情况下，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 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 monitor 输出中 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  ;;...