[转载]MySQL各类SQL语句的加锁机制

柔情只为你懂 2022-09-02 05:17 113阅读 0赞

> 本文系转载，方便自己整理和查看，源文地址 https://www.cnblogs.com/leohahah/p/8863422.html

**官网参考**:https://dev.mysql.com/doc/refman/5.6/en/innodb-locks-set.html

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

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

http://www.cnblogs.com/leohahah/p/8862216.html

http://www.cnblogs.com/leohahah/p/8857124.html

**第一部分：概述**

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

这些锁定添加的锁通常是next-key lock，这种锁既锁定扫描到的索引记录，也锁定索引间的gap。不过gap锁可以被显示的禁用，参考http://www.cnblogs.com/leohahah/p/8862216.html的Gap lock部分。

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

如果执行的SQL找不到合适的索引，InnoDB不得不去进行全表扫描，那么InnoDB会把表的每一个聚集索引记录都锁住，这可以看作是表级锁，同样参考http://www.cnblogs.com/leohahah/p/8862216.html的表锁部分。这种全表锁定会导致其他事务无法插入和更改（同样参考链接中的表锁兼容性部分），因此为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。

Ps:网上很多笔记中关于innnodb引擎下select会加锁的论调都不甚合理，我认为脱离了事务隔离级别的select加锁实验和介绍是不负责任的，官网明确说明了在Repeatable Read和Read Committed事物隔离级别下select是一致性非锁定读，其原理是利用undo镜像实现的，读不会阻塞写，即便是使用了索引也不会加锁。因此在讨论此类问题时应当以官网解释为准，即便是本文也不能保证完美诠释官网的相关解释，大家有疑问时可以通过官网来进行纠错查证。

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

3.**SELECT … FROM … FOR UPDATE**在扫描到的索引记录上依次添加X模式的Next-key行锁（不满足条件的范围上的锁会在下一个范围锁获取后释放），如果是扫描唯一索引来获取唯一值，那么最终只会添加X模式的Record lock。如果扫描的记录不足以构成next-key lock，例如select … from … where id >= … for update，那么若等值部分的记录存在就会添加X模式的record lock，之后的部分添加next-key lock，若等值记录不存在那么在上一个存在的记录到下一个存在的记录之间添加next-key lock。

一个很神奇的现象，假设你有如下表：

CREATE TABLE child (idint(11) NOT NULL, PRIMARY KEY(id)) ENGINE=InnoDB;
    INSERT INTO child (id) values (90),(102);
    --会话A执行：
    start transaction;
    INSERT INTO child (id) VALUES (101);
    --会话B执行：
    SELECT * FROM child WHERE id > 100 FOR UPDATE;

上述B会话显然会因为A会话在101上的行锁被阻塞，但是此时B会话本身的加锁状况呢？

不少人会认为B会话目前会持有三把行锁，其中一个是处于等待状态的(90,101\]next-key行锁，剩下两个是已经获取资源完毕的id=102的行锁以及(102,supremum\]的next-key行锁。但是实际上呢？show engine innodb status却显示会话B只持有2个行锁，且全部是next-key类型的。此时再开一个会话C查询id<50的范围会发现被阻塞了！！？？为何查询小于90的范围也会被阻塞呢，因为会话B的语句扫描主键索引时先在（0,90\]上加了next-key行锁，之后在(90,101\]的资源上加锁却被阻塞了，这导致前一个(0,90\]的锁未能释放，因为下一个范围锁未获取成功，这导致会话C查询小于90的范围失败，查询大于101的范围却能成功。

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。这种锁的作用是为添加行锁做锁冲突检测，具体示例参考http://www.cnblogs.com/leohahah/p/8862216.html的插入意向锁部分。

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

CREATE TABLE t1 (iINT,PRIMARY KEY (i)) ENGINE = InnoDB;
    --会话A执行：
    STARTTRANSACTION;
    INSERT INTO t1VALUES(1);
    --会话B执行：
    STARTTRANSACTION;
    INSERT INTO t1VALUES(1);
    --会话C执行：
    STARTTRANSACTION;
    INSERT INTO t1VALUES(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**参考http://www.cnblogs.com/leohahah/p/8862216.html的AUTO-INC Locks部分。

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

14.**LOCK TABLES**也会在表上设置表锁，默认情况下innodb可以识别到这些表锁，并把他们作为事务锁的一环，但是如果你设置innodb\_table\_locks = 0或者autocommit=1,那么innodb就会忽略此类表锁引发的死锁，此时你需要设置`innodb_lock_wait_timeout`参数来处理此种情况，此类表锁甚至可以加在正在使用行锁的InnoDB表上。不过这并不会危及到事务的完整性，具体说明详见：https://dev.mysql.com/doc/refman/5.6/en/innodb-deadlock-detection.html