MSQL 参考——15.7.1 InnoDB 锁 (QFY译版)

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的锁。

意向锁

InnoDB支持多粒度锁,允许行锁和表锁共存。 例如,LOCK TABLES …​ WRITE 之类的语句在指定表上取得独占锁(X锁)。为了使多粒度级别的锁切实可行,InnoDB 利用意向锁。意向锁是表级锁,用于指示事务稍后对表中的行需要哪种类型的锁(共享或独占)。意向锁有两种类型:

  • 意向共享锁(IS)表示事务打算对表中的独立行设置共享锁。

  • 意向排它锁(IX)表示事务打算对表中的独立行设置排它锁。

例如 SELECT …​ FOR SHARE 设置一个 IS 锁, SELECT …​ FOR UPDATE 设置一个 IX 锁. 意向锁定协议如下:

  • 在事务可以获取表中某一行的共享锁之前,必须首先获取表上的IS锁或更强的锁。

  • 在事务可以获取表中某一行的排它锁之前,必须首先获取表上的 IX 锁。 表级锁类型兼容性总结如下。

X

IX

S

IS

X

冲突

冲突

冲突

冲突

IX

冲突

Compatible

冲突

Compatible

S

冲突

冲突

Compatible

Compatible

IS

冲突

兼容

兼容

兼容

如果一个请求事务与现有的锁兼容,那么它将被授予一个锁,但如果它与现有的锁冲突,则不会授予该锁。事务等待,直到冲突的现有锁被释放。如果锁请求与现有锁冲突,并且由于会导致死锁而无法授予,则会发生错误。 意向锁不会造成阻塞除了完整表锁请求(例如,LOCK TABLES …​ WRITE )。意向锁的主要目的是显示有人正在锁定一行,或将要锁定表中的一行。 意向锁的事务数据与以下显示引擎 SHOW ENGINE INNODB STATUS 和 InnoDB 监视器输出中的数据类似:

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

记录锁

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

记录锁始终锁定索引记录,即使定义的表没有索引。对于这种情况,InnoDB 创建一个隐藏的聚集索引,并使用该索引进行记录锁定。见第15.6.2.1节 “ 聚集索引和次要索引(Clustered and Secondary Indexes) ”。

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

间隙锁

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

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

间隙锁是性能和并发性之间权衡的一部分,用于某些事务隔离级别,而不是其他级别。

对于使用唯一索引锁定行以搜索唯一行的语句,不需要使用间隙锁定。 (这不包括搜索条件只包含多列唯一索引的某些列的情况;在这种情况下,会发生间隙锁定。) 例如,如果ID列具有唯一索引,则以下语句只对ID值为100的行使用索引记录锁,而不管其他事务是否插入前一间隙中的行:

SELECT * FROM child WHERE id = 100;

如果ID没有被索引或具有非唯一索引,那么语句将锁定前面的间隙。

这里也值得注意的是,冲突的锁可以通过不同的事务保持在间隙上。( It is also worth noting here that conflicting locks can be held on a gap by different transactions.)例如,事务A可以在间隙上持有共享间隙锁(gap S-lock),而事务B可以在同一间隙上持有独占间隙锁(gap X-lock)。允许间隙锁冲突的原因是,如果从索引中清除某个记录,则必须合并由不同事务保留在该记录上的间隙锁。

InnoDB中的间隙锁是“完全禁止的(purely inhibitive)”,这意味着它们的唯一目的是防止其他事务插入到间隙中。间隙锁可以共存。一个事务获取的间隙锁不会阻止另一个事务在同一间隙上获取间隙锁。共享和独占的间隙锁没有区别。它们不会相互冲突,并且执行相同的功能。

可以显式禁用间隙锁定。如果将事务隔离级别更改为“读取已提交(READ COMMITTED)”,则会发生这种情况。在这些情况下,搜索和索引扫描将禁用间隙锁定,并且仅用于外键约束检查和重复键检查。

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

Next-Key 锁

Next-Key锁是索引记录上的记录锁和索引记录之前的间隙锁的组合。

InnoDB 执行行级锁定的方式是,当它搜索或扫描表索引时,它会对遇到的索引记录设置共享或独占锁。因此,行级锁实际上是索引记录锁。索引记录上的 next-key 锁也会影响该索引记录之前的“间隙”。也就是说,next-key 是索引记录锁加上索引记录前面的间隙锁。如果一个会话在索引中的记录 r 上有一个共享或独占的锁,则另一个会话不能在 r 前面的间隙中按索引顺序插入新的索引记录。

假设索引包含值10、11、13和20。此索引可能的 next-key 锁包括以下间隔,其中圆括号表示不包括间隔端点,方括号表示包含端点:

(负无穷, 10]
(10, 11]
(11, 13]
(13, 20]
(20, 正无穷)

对于最后一个间隔,下一个键锁会将间隙锁定在索引中最大值的上方,“supremum”伪记录的值高于索引中实际的任何值。supremum不是一个真正的索引记录,因此,实际上,这个 next-key 锁只锁定最大索引值之后的间隙。

默认情况下,InnoDB在 REPEATABLE READ 事务隔离级别下运行。在这种情况下,InnoDB使用 next-key 锁进行搜索和索引扫描,以防止出现幻象行(参见第15.7.4节 “幻象行” )。

next-key 锁的事务数据与 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和7的索引记录。尝试分别插入值5和6的单独事务,每个事务在获取插入行的排他锁之前使用插入意图锁锁定4和7之间的间隙,但因为行不冲突所以不会相互阻塞。

下面的示例演示一个事务,它在获取插入记录的排他锁之前获取一个插入意向锁。这个例子涉及两个客户端(clients),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开始一个事务,将一个记录插入GAP中。事务在等待获取排他锁时接受插入意向锁。

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

插入意图锁的事务数据与 SHOW ENGINE INNODB STATUS 和innodb监视器输出中的以下类似:

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

AUTO-INC 锁

AUTO-INC 锁是一种特殊的表级锁,由插入到含自增列的表事务获取。在最简单的情况下,如果一个事务正在向表中插入值,则任何其他插入事务都必须等待插入,以便第一个事务插入的行接收连续的主键值。

innodb_autoinc_lock_mode 配置选项控制用于 AUTO-INC 锁定的算法。它允许您选择如何在自动递增值的可预测序列和插入操作的最大并发性之间进行权衡。

有关更多信息,请参见第15.6.1.4节“ InnoDB中的自增列处理”。

Predicate Locks for Spatial Indexes

InnoDB supports SPATIAL indexing of columns containing spatial columns (see Section 11.5.9, “Optimizing Spatial Analysis”). To handle locking for operations involving SPATIAL indexes, next-key locking does not work well to support REPEATABLE READ or SERIALIZABLE transaction isolation levels. There is no absolute ordering concept in multidimensional data, so it is not clear which is the “next” key. To enable support of isolation levels for tables with SPATIAL indexes, InnoDB uses predicate locks. A SPATIAL index contains minimum bounding rectangle (MBR) values, so InnoDB enforces consistent read on the index by setting a predicate lock on the MBR value used for a query. Other transactions cannot insert or modify a row that would match the query condition.

数码
沪ICP备19006215号-4