skip to content W3C SVG

MySQL 锁和事务模型 1

Oct 79, 7129
Oct 219, 21029

Shared and Exclusive Locks

InnoDB 实现了两种标准的 行级别 的锁，即：共享锁 和 排他锁

  • 共享锁（S 锁） 允许 持有该锁的 事务 读取 行
  • 排他锁（X 锁） 允许 持有该锁的 事务 更新或删除 行

如果一个事务 T1 持有行 r 上的共享锁，另一个事务 T2 在行 r 上的操作如下：

  • T2 持有共享锁，允许对行 r 操作，T1 和 T2 都持有行 r 上的共享锁
  • T2 持有排他锁，不允许对行 r 操作

如果一个事务 T1 持有行 r 上的排他锁，另一个事务 T2 只能等待其释放锁

Intention Locks

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

比如，使用如下语句可以在具体表上加上排他锁：

LOCK TABLES ... WRITE

InnoDB 使用 意向锁 来支持多粒度的锁

意向锁 是 表级别 的锁，指一个事务之后操作表中的行时需要排他锁还是共享锁

有两种意向锁：

  • 意向共享锁（IS 锁），指一个事务打算在表中的单个行上设置共享锁
  • 意向排他锁（IX 锁），指一个事务打算在表中的单个行上设置排他锁

使用 SELECT ... LOCK in SHARE MODE 设置的是 IS 锁

使用 SELECT ... FOR UPDATE 设置的是 IX 锁

意向锁的协议如下：

事务在获得表中行上的 S 锁之前，首先得持有表上的 IS 锁或更高级别锁

事务在获得表中行上的 X 锁之前，首先得持有表上的 IX 锁

表级锁类型的兼容性如下：

- X IX S IS
X 冲突 冲突 冲突 冲突
IX 冲突 兼容 冲突 兼容
S 冲突 冲突 兼容 兼容
IS 冲突 兼容 兼容 兼容

事务在获取锁时，需要保证 要获取的锁 和 已持有的锁 能够兼容，才能成功获取

发生锁冲突时，事务需要等待 直到 已持有的锁 被释放掉

当锁请求与 所持有的锁 发生冲突，不能被同意（同意会引发死锁）时，报错

意向锁不会堵塞，除非是对于全表的请求（如 LOCK TABLES ... WRITE）

目的：表明某个事务正在锁定一行或者将要锁定一行

意向锁的事务数据如下：

TABLE LOCK table `test`.`t` trx_id 10080 lock mode IX

与执行 SHOW ENGINE INNODB STATUS 后，InnoDB Monitor 的输出类似

Record Locks

记录锁，作用在索引记录上

例如执行如下语句，能防止其他的事务插入、更新和删除 c1 = 10 的这行记录

SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE;

记录锁永远只锁定索引记录，即使一个表没有定义索引。在这种情况下，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

间隙锁是一种作用于索引记录之间的锁，也可以作用于第一条索引记录前或最后一条索引记录后

例如下面语句，可以防止其他事务插入数据到 c=10 和 c=20 的记录之间

SELECT c1 FROM t WHERE c1 BETWEEN 10 and 20 FOR UPDATE;

这个范围内已有数据的间隙被锁住了，因此其他事务无法插入数据

一个间隙：可能跨越单条索引值，多条索引值，甚至没有索引值

间隙锁 是保证性能和并发的一种折衷做法，只能在部分事务隔离级别下使用

在使用唯一索引查找唯一行时，不需要使用间隙锁锁定行

例如下面语句，id 列有一个唯一索引，那么下面语句仅使用索引记录锁，不管该记录之前的间隙

SELECT * FROM child WHERE id = 100;

如果 id 不是唯一索引的话，该语句会锁定 id = 100 之前的记录

这里还有一点需要注意，不同事务可以持有作用于一个 gap 上的冲突的锁

例如，事务 A 持有 gap 上的间隙共享锁(gap S-lock)，同时另一个事务 B 可以持有相同 gap 上的排他锁

锁冲突了但是可以共存的原因是：

如果一条记录从索引中清除，那么不同事务持有的该条记录上的间隙锁必须合并

InnoDB 中的间隙锁纯粹是禁止的，意味着使用它们的唯一目的就是防止其他事务向 gap 中插入

间隙锁可以共存，一个事务持有间隙锁不会防止另一个事务持有相同 gap 上的间隙锁

共享间隙锁和排他间隙锁没有区别，它们互不冲突，执行相同的功能

可以显式地禁用间隙锁：

  1. 只要将事务的隔离级别修改为 读提交
  2. 启用 innodb_locks_unsafe_for_binlog 系统变量（已被废弃）

在这些情况下，间隙锁对于查找和索引扫描就没用了，只能用于外键约束和重复键检查

使用 读提交 隔离级别 或 启用 innodb_locks_unsafe_for_binlog 还有其他效果：

  1. 不匹配行上的记录锁在 MySQL 评估完 WHERE 条件后就会释放
  2. 对于 更新语句，InnoDB 采取了 “半一致性” 读，即它返回最新提交的记录版本，由 MySQL 决定该行是否匹配更新语句的 WHERE 条件

Next-Key Locks

next-key 锁结合了索引记录上的记录锁和索引记录之前 gap 上的间隙锁

InnoDB 执行行级锁定的过程是这样的：

当搜索或扫描表索引时，会为每一条索引记录设置一个共享锁或排他锁

因此，行级锁 实际上就是 索引记录锁。

一条索引记录上的 next-key 锁也会影响该记录之前的 gap

即 next-key 锁 = 索引记录锁 + 索引记录之前的间隙锁

表现在：

当一个会话持有一条索引记录 R 上的共享锁或排他锁时，

其他会话不能在 R 的索引顺序前的间隙中插入新的索引记录

考虑一种情况，索引含有值 10，11，13，20。那么该索引可能的 next-key 锁会覆盖下面区间，圆括号表示不包含，方括号表示包含：

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

在最后一个区间上，next-key 锁锁定了 索引中已有最大值 到 索引中伪上确界最大值 的 gap。上确界不是一条真实的索引记录，所以实际上 next-key 锁只是锁定了最大索引值之后的 gap

InnoDB 的默认事务隔离级别是 可重复读，这种情况下，InnoDB 使用 next-key 锁进行搜索和索引扫描，以防止幻读行

执行 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

插入意向锁是间隙锁的一种，由行插入之前的 插入操作 设置。

这个锁表示这么一个意图：

多个事务在插入相同的索引 gap 时，只要它们不是插入 gap 中相同的位置，它们就不必相互等待

假设现在有值为 4 和 7 的索引记录

  1. 不同的事务试图分别插入 5 和 6，
  2. 每个事务在获取插入行的 排他锁 之前使用 插入意向锁 锁定 4 和 7 之间的 gap
  3. 但是它们不互相阻塞因为它们插入的行不冲突。

下面的例子展示了一个事务在获取插入行上的排他锁之前正在获取 插入意向锁

该例子涉及 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 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  ;;...

AUTO-INC Locks

自动增长锁 是一种特殊的表级别锁，由插入表中 自动增长列 的事务持有

最简单的情况是，如果一个事务正在向表中插入数据，任何其他事务必须等待去执行它们自己的表插入操作

因而，第一个事务插入的行可以得到连续的主键值

配置项 innodb_autoinc_lock_mode 控制 自动增长锁 的算法。它允许你在 自动增长值可预见的顺序性 和 插入操作的最大并发度 之间做取舍

Predicate Locks for Spatial Indexes

InnoDB 支持包含空间列的列的空间索引。

为了处理涉及空间索引上操作的锁定，next-key 锁在 可重复读 或 串行化 隔离级别下 工作得不好。在多维度数据上没有绝对的顺序概念，所以 next 语义不明确。

为了支持包含空间索引的表的隔离级别，InnoDB 使用谓词锁。一个空间索引包括最小边界矩形(MBR)值，所以 InnoDB 查询时通过在 MBR 值上设置谓词锁来保证索引上的一致性读。其他的事务不能在匹配查询条件的行上插入或更新。

原文

MySQL官方文档