MySQL各类SQL语句的加锁机制

115次阅读

共计 3522 个字符，预计需要花费 9 分钟才能阅读完成。

MySQL 把读操作分为两大类：锁定读和非锁定读（即 locking read 和 nonlocking read），所谓非锁定读就是不对表添加事务锁的读操作，如 Repeatable Read 和 Read Committed 隔离级别下的 select 语句（可能脏读也算？）。MySQL 的一致性非锁定读是通过 MVCC 机制实现的。锁定读是指添加事务锁的读操作，例如 select for update 和 select lock in share mode 语句。

官网参考：这里

关于 MySQL 的锁机制和事务隔离级别，参考以下两篇博客：

https://www.linuxidc.com/Linux/2018-04/151914.htm
https://www.linuxidc.com/Linux/2018-04/151923.htm

第一部分：概述

锁定读、update 和 delete，这些操作通常会在扫描到的索引记录上添加 record locks，InnoDB 不关心这些行是否会被 where 条件过滤，因为 InnoDB 不记得具体的 where 条件，它只知道哪个索引范围被扫描过。

这些锁定添加的锁通常是 next-key lock，这种锁既锁定扫描到的索引记录，也锁定索引间的 gap。不过 gap 锁可以被显示的禁用，参考 https://www.linuxidc.com/Linux/2018-04/151914.htm 的 Gap lock 部分。

如果在 SQL 执行时你需要对次级索引记录加 X 模式的行锁，那么 InnoDB 也会检索相应的主键索引并加锁。

如果执行的 SQL 找不到合适的索引，InnoDB 不得不去进行全表扫描，那么 InnoDB 会把表的每一个聚集索引记录都锁住，这可以看作是表级锁，同样参考 https://www.linuxidc.com/Linux/2018-04/151923.htm 的表锁部分。这种全表锁定会导致其他事务无法插入和更改（同样参考链接中的表锁兼容性部分），因此为 SQL 创建合适的索引是很有必要的，因为表锁（非意向锁）会导致 DML 操作阻塞。

对于 select…for update 和 select…lock in share mode 这种锁定读来说，开始时 InnoDB 会锁定所有扫描的索引记录，但是最后会释放那些不符合条件的索引记录上的锁（例如被 where 语句过滤掉的行）。但是在某些情况下由于结果行与源表的联系丢失，导致这些行锁不会被释放，例如：union 操作，被扫描的中间结果行会被插入到一个临时表中以便形成最终的结果集，在这种情况下锁定行与原表之间的联系丢失，那么剩余的扫描行直到整个 SQL 执行结束才会被释放（不是事务执行结束）。

第二部分：InnoDB 中 SQL 语句的加锁类型

1. 在 Repeatable Read 和 Read Committed 事物隔离级别下，SELECT … FROM语句是一种一致性非锁定读。而在 SERIALIZABLE 隔离级别下是锁定读，会在扫描的索引记录范围内添加 Next-key 行锁，不过如果扫描的是唯一索引，那么只会添加 Record lock。

2.SELECT … FROM … LOCK IN SHARE MODE在扫描到的索引记录上添加 S 模式的 Next-key 行锁，同样的如果扫描的是唯一索引，那么只会添加 S 模式的 Record lock。

3.SELECT … FROM … FOR UPDATE在扫描到的索引记录上添加 X 模式的 Next-key 行锁，同样的如果扫描的是唯一索引，那么只会添加 X 模式的 Record lock。

4.UPDATE … WHERE …语句会在扫描到的所有记录上添加 X 模式的 next-key lock（即便被删的行不存在），同样的如果扫描的是唯一索引，那么只会添加 X 模式的 Record lock。

5. 当 UPDATE 语句修改的是主键索引时，InnoDB 会隐式的将所有的次级索引锁定（二级索引都是用主键做书签的，因此修改主键索引是很耗资源的操作）。在插入二级索引记录或者为插入二级索引做重复性检查扫描时（unique index），update 也会把受影响的二级索引锁定。

6.DELETE FROM … WHERE …语句会在扫描到的所有记录上添加 X 模式的 next-key lock（即便被删的行不存在），同样的如果扫描的是唯一索引，那么只会添加 X 模式的 Record lock。

7.INSERT语句会在插入的行上添加 Record lock，Insert 语句不会阻止其他事务在同一个 gap 上插入行。

虽然 Insert 语句不使用 gap 行锁，但是会使用一种叫插入意向锁的 gap 锁，即 Insert Inrention Locks。这种锁的作用是为添加行锁做锁冲突检测，具体示例参考 https://www.linuxidc.com/Linux/2018-04/151914.htm
的插入意向锁部分。

此外 INSERT 语句还涉及到主键的重复性检测，示例说明如下：

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;
– 会话 A 执行：
START TRANSACTION;
INSERT INTO t1 VALUES(1);
– 会话 B 执行：
START TRANSACTION;
INSERT INTO t1 VALUES(1);
– 会话 C 执行：
START TRANSACTION;
INSERT INTO t1 VALUES(1);
– 最后会话 A 在执行：
ROLLBACK;
– 最后发现会话 B 和 C 形成了死锁。

因为开始时会话 A 在 i = 1 上添加了 X 模式的行锁，会话 BC 在做重复性检测时发现已有 i =1, 于是在各自请求行上的一个 S 行锁，当 A 会话 rollback 后，BC 的 S 行锁都获取到了，此时 B 和 C 都需要把 S 行锁转化为 X 行锁，但是都不愿意放弃自己的 S 锁，而 S 和 X 是互斥的，因此形成死锁。这个问题其实和 SQL Server 的更新锁出现的原因一样，只不过 SQL Server 通过 U 锁解决了此问题，即重复性检测使用的是 U 锁，而 U 锁只能有一个会话获取。

8.INSERT … ON DUPLICATE KEY UPDATE，这种插入语句和普通的 INSERT 语句区别在于，他会在发生重复性键值错误时向索引记录上添加 X 行锁，如果是主键那添加 X 模式的 record lock 行锁，如果是普通的唯一索引那添加 X 模式的 next-key 行锁。这姑且算是对 7 的死锁问题的一种解决办法吧。

9.REPLACE语句可以看做是 INSERT … ON DUPLICATE KEY UPDATE 的简写。

10.INSERT INTO T SELECT … FROM S WHERE …语句会在 T 表的每个被插入的行上添加 X 模式的 record lock（无 gap 锁）。

如果事务隔离级别被设置为 READ COMMITTED，或者 innodb_locks_unsafe_for_binlog 设为 1 而且事物隔离级别不是 SERIALIZABLE，那么这两种情况下 InnoDB 对 S 表执行一致性非锁定读。否则 InnoDB 会对 S 表上的每个行都添加 S 模式的 next-key lock。

11.CREATE TABLE … SELECT …语句的加锁机制与 INSERT INTO T SELECT … FROM S WHERE … 完全一致。

REPLACE INTO t SELECT … FROM s WHERE …或者 UPDATE t … WHERE col IN (SELECT … FROM s …) 这两种 SQL 语句对 s 表的行添加 S 模式的 next-key 行锁。

12. 关于 AUTO-INC Locks 参考 https://www.linuxidc.com/Linux/2018-04/151914.htm
的 AUTO-INC Locks 部分。

13. 如果表上有外键约束，那么任何需要做外键约束检测的 DML 语句都会在相应的外键上添加 S 模式的行锁。即便约束失败也会设置这些行锁。

14.LOCK TABLES也会在表上设置表锁，只是这种表锁并非是 InnoDB 层的表锁，而是 MySQL 层的表锁。因此如果死锁涉及到这些表锁时，InnoDB 的死锁自动检测机制无法检测到这些表锁。而且由于 MySQL 层对 InnoDB 层的行锁机制并不清楚，因此此类表锁甚至可以加在正在使用行锁的 InnoDB 表上。不过这并不会危及到事务的完整性，具体说明详见：https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html

本文永久更新链接地址：https://www.linuxidc.com/Linux/2018-05/152411.htm

正文完

星哥说事-微信公众号