从根儿上理解MySQL | SQL语句加锁分析详解

目录

InnoDB存储引擎中的锁

InnoDB中的行级锁

InnoDB中的表级锁

MySQL语句加锁分析

普通的SELECT语句

锁定读语句

INSERT语句

---

InnoDB存储引擎中的锁

InnoDB中的行级锁

• Record Locks

官方的类型名称为：LOCK_REC_NOT_GAP，记录锁又分为S锁和X锁：

1. S锁：共享锁，英文名：Shared Locks。在事务要读取一条记录时，需要先获取该记录的S锁。
2. X锁：独占锁，也常称排他锁，英文名：Exclusive Locks。在事务要改动一条记录时，需要先获取该记录的X锁。

当一个事务获取了一条记录的S锁后，其他事务也可以继续获取该记录的S锁，但不可以继续获取X锁；当一个事务获取了一条记录的X锁后，其他事务既不可以继续获取该记录的S锁，也不可以继续获取X锁。也就是说，S锁和S锁是兼容的，S锁和X锁是不兼容的，X锁和X锁也是不兼容的。

• Gap Locks

官方的类型名称为：LOCK_GAP，间隙锁的提出仅仅是为了防止插入幻影记录，如果我们对一条记录加了gap锁，并不会限制其他事务对这条记录加记录锁或者继续加gap锁。假设我们把number值为8的那条记录加一个gap锁（如下图所示），这意味着不允许别的事务在number值为8的记录前边的间隙(3, 8)这个区间插入新记录；如果我们要阻止其他事务插入number值在(20, +∞)这个区间的新记录，可以在number值为20的那条记录所在页面的Supremum记录加上一个gap锁。

• Next-Key Locks

官方的类型名称为：LOCK_ORDINARY，next-key锁的本质就是一个记录锁和一个gap锁的合体，它既能保护该条记录，又能阻止别的事务将新记录插入被保护记录前边的间隙。

• Insert Intention Locks

官方的类型名称为：LOCK_INSERT_INTENTION。一个事务在插入一条记录时需要判断一下插入位置是不是被别的事务加了所谓的gap锁，如果有的话，插入操作需要等待，在等待时事务需要在内存中生成一个锁结构，表明有事务想在某个间隙中插入新记录，但是现在在等待，而这个锁结构就是插入意向锁。比方说现在T1为number值为8的记录加了一个gap锁，然后T2和T3分别想向hero表中插入number值分别为4、5的两条记录，所以现在为number值为8的记录加的锁的示意图就如下所示：

• 隐式锁

一个事务对新插入的记录可以不显式的加锁（生成一个锁结构），但是由于事务id的存在，相当于加了一个隐式锁。别的事务在对这条记录加S锁或者X锁时，由于隐式锁的存在，会先帮助当前事务生成一个锁结构，然后自己再生成一个锁结构后进入等待状态。

InnoDB中的表级锁

• 表级别的S锁、X锁

如果一个事务给表加了S锁，别的事务可以继续获得该表或表中某些记录的S锁，但不可以继续获得该表或表中某些记录的X锁；如果一个事务给表加了X锁，也就意味着该事务要独占这个表，别的事务既不可以继续获得该表的S锁，也不可以继续获得该表的X锁。

表级别的S锁、X锁了解即可，一般情况下不会使用。

• 表级别的IS锁、IX锁

1. IS锁：意向共享锁，当事务准备在某条记录上加S锁时，需要先在表级别加一个IS锁。
2. IX锁：意向独占锁，当事务准备在某条记录上加X锁时，需要先在表级别加一个IX锁。

IS、IX锁的提出仅仅为了在之后加表级别的S锁和X锁时可以快速判断表中的记录是否被上锁，以避免用遍历的方式来查看表中有没有上锁的记录，也就是说其实IS锁和IX锁是兼容的，IX锁和IX锁是兼容的。

• 表级别的AUTO-INC锁

如果插入语句在执行前不可以确定具体要插入多少条记录，一般是使用AUTO-INC锁为AUTO_INCREMENT修饰的列生成对应的值。一个事务在持有AUTO-INC锁的过程中，其他事务的插入语句都要被阻塞，这样可以保证一个语句中分配的递增值是连续的。

MySQL语句加锁分析

普通的SELECT语句

• 未提交读：不加锁，直接读取记录的最新版本，可能发生脏读、不可重复读和幻读问题。
• 提交读：不加锁，在每次执行普通的SELECT语句时都会生成一个ReadView，这样解决了脏读问题，但没有解决不可重复读和幻读问题。
• 可重复读：不加锁，只在第一次执行普通的SELECT语句时生成一个ReadView，这样把脏读、不可重复读和幻读问题都解决了。

注意：InnoDB中的MVCC并不能完完全全的禁止幻读。假设一个事务T1第一次执行普通的SELECT语句时生成了一个ReadView，之后T2向hero表中新插入了一条记录便提交了，ReadView并不能阻止T1执行UPDATE或者DELETE语句来对改动这个新插入的记录，但是这样一来这条新记录的trx_id隐藏列就变成了T1的事务id，之后T1中再使用普通的SELECT语句去查询这条记录时就可以看到这条记录。

• 串行化：如果系统变量autocommit=0（禁用自动提交），普通的SELECT语句会被转为SELECT ... LOCK IN SHARE MODE，也就是在读取记录前需要先获得记录的S锁，具体的加锁情况和REPEATABLE READ隔离级别下一样；如果系统变量autocommit=1（启用自动提交），不加锁，只是利用MVCC来生成一个ReadView去读取记录。

锁定读语句

先介绍两种特殊的SELECT语句：

1. 对读取的记录加S锁：

SELECT ... LOCK IN SHARE MODE;

2. 对读取的记录加X锁：

SELECT ... FOR UPDATE;

以上两种特殊的SELECT语句就是锁定读，另外UPDATE语句和DELETE语句在执行过程需要首先定位到被改动的记录并给记录加锁，也可以被认为是一种锁定读。注意：采用加锁方式解决并发事务带来的问题时，脏读和不可重复读在任何一个隔离级别下都不会发生。

• 未提交读/提交读隔离级别下

对聚簇索引中的记录（和二级索引中的记录）加S锁 / X锁。注意：对于DELETE语句和UPDATE语句（更新了二级索引时），如果是利用主键进行等值查询，是先为聚簇索引记录加X锁，再为对应的二级索引记录加X锁；而如果使用二级索引进行等值查询，是先对二级索引记录加S / X锁，然后再给对应的聚簇索引记录加S / X锁。另外，如果进行范围查询，比如利用主键进行范围查询，会先在聚簇索引中定位到满足该范围的第一条记录，然后沿着由记录组成的单向链表一路向后找，每找到一条记录，就会为其加上S锁 / X锁，然后判断该记录符不符合范围查询的边界条件，不符合就结束查询。

• 可重复读隔离级别下

采用加锁的方式解决并发事务产生的问题时，可重复读隔离级别与未提交读和提交读这两个隔离级别相比，最主要的就是要解决幻读问题，而解决幻读问题靠的是间隙锁。

1. 使用主键进行等值查询：如果主键值存在，由于主键的唯一性，不可能发生幻读，所以只要为该记录加S锁 / X锁；如果主键值不存在，就需要在第一个大于该值的主键值所在的记录加一个间隙锁。

2. 使用主键进行范围查询：以SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE语句为例，加锁情况如下所示：

该语句会为1、3、8、15这4条记录都加上S型next-key锁，特别注意的是，REPEATABLE READ隔离级别下，在判断number值为15的记录不满足边界条件 number <= 8 后，并不会去释放加在该记录上的锁（注意和未提交读、可提交读区分）。

使用SELECT ... FOR UPDATE语句只是将上述S型next-key锁替换成X型next-key锁。对于DELETE语句和UPDATE语句（更新了二级索引时），以UPDATE hero SET name = 'cao曹操' WHERE number <= 8；语句为例：

会对number值为1、3、8、15的聚簇索引记录加X型next-key锁，相应的为number值为1、3、8的聚簇索引记录对应的idx_name二级索引记录加X锁，需要注意的是并不会对number值为15的记录对应的二级索引记录加锁。

3.使用唯一二级索引进行等值/范围查询：与使用主键进行等值/范围查询类似，不同的是先在二级索引加间隙锁/next-key锁（后在聚簇索引加S锁/X锁）。

4.使用普通二级索引进行等值查询：以SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;语句为例：对所有name值为'c曹操'的二级索引记录加S型next-key锁，它们对应的聚簇索引记录加S锁（值不存在自然就不加了）；然后对最后一个name值为'c曹操'的二级索引记录的下一条二级索引记录加间隙锁。

5.使用普通二级索引进行范围查询：与使用唯一二级索引的加锁情况类似。

6.全表扫描：存储引擎每读取一条聚簇索引记录，就会为这条记录加锁一个S型next-key锁，然后返回给server层判断条件是否成立，如果成立则将其发送给客户端，否则会向InnoDB存储引擎发送释放掉该记录上的锁的消息，但在可重复读隔离级别下，InnoDB存储引擎并不会真正的释放掉锁，所以聚簇索引的全部记录都会被加锁，并且在事务提交前不释放。

INSERT语句

INSERT语句一般情况下不加锁，不过当前事务在插入一条记录前需要先定位到该记录在B+树中的位置，如果该位置的下一条记录已经被加了间隙锁，那么当前事务会在该记录上加上插入意向锁，并且事务进入等待状态。下面讨论INSERT语句可能遇到的两种特殊情况：

• 遇到重复键

在定位到新记录应该插入到B+树的位置时，如果发现有已存在记录的主键或者唯一二级索引列，那么此时是会报错的。但在生成报错信息之前，如果是主键值重复，会对聚簇索引中相应的记录加锁，在未提交读/提交读隔离级别下，会加S锁，在可重复读隔离级别下加S型next-key锁；而如果是唯一二级索引列值重复，无论是哪种隔离级别，会对已经在B+树中的唯一二级索引记录加next-key锁。

另外，如果我们使用的是INSERT ... ON DUPLICATE KEY ...这样的语法来插入记录时，如果遇到主键或者唯一二级索引列值重复的情况，会对B+树中已存在的相同键值的记录加X锁。

• 外键检查

假设我们有一个父表和一个子表，我们需要在子表中插入一条记录，如果待插入记录的外键值能在父表中找到，不管哪种隔离级别，只需要直接给父表中相应的记录加S锁；如果待插入记录的外键值在父表中找不到，在未提交读/提交读隔离级别下不会加锁，但在可重复读隔离级别下，会加间隙锁。

声明：本博客纯粹为读书笔记，如想详细了解MySQL相关知识请访问《MySQL是怎么运行的：从根儿上理解MySQL》原作者撰写资料