简介：作者：iceman1952 来源：https://blog.csdn.net/iceman1952/article/details/85504278 本文中，我们详细介绍MySQL InnoDB存储引擎各种不同类型的锁，以及不同SQL语句分别会加什么样的锁。阅读提示1. 本文所参考的MyS ...
作者：iceman1952来源：https://blog.csdn.net/iceman1952/article/details/85504278 本文中，我们详细介绍MySQL InnoDB存储引擎各种不同类型的锁，以及不同SQL语句分别会加什么样的锁。 阅读提示 1. 本文所参考的MySQL文档版本是8.0，做实验的MySQL版本是8.0.13 2. 本文主要参考了MySQL官方文档 InnoDB锁定和事务机制 3. 本文还参考了何登成的 MySQL加锁处理分析、一个最不可思议的MySQL死锁分析 以及阿里云RDS-数据库内核组的 常用SQL语句的MDL加锁源码分析 4. MySQL是插件式的表存储引擎，数据库的锁是和存储引擎相关的，本文讨论的锁都是InnoDB存储引擎的锁 文章正文开始 “加什么样的锁”与以下因素相关 1. 当前事务的隔离级别 2. SQL是一致性非锁定读(consistent nonlocking read)还是DML(INSERT/UPDATE/DELETE)或锁定读(locking read) 3. SQL执行时是否使用了索引，所使用索引的类型（主键索引，辅助索引、唯一索引） 我们先分别介绍这几个因素 一、隔离级别(isolation level) 数据库事务需要满足ACID原则，“I”即隔离性，它要求两个事务互不影响，不能看到对方尚未提交的数据。数据库有4种隔离级别(isolation level)，按着隔离性从弱到强（相应的，性能和并发性从强到弱）分别是 1. Read Uncommitted。下面简称RU 2. Read Committed。下面简称RC 3. Repeatable Read（MySQL的默认隔离级别）。下面简称RR 4. Serializable “I”即隔离性正是通过锁机制来实现的。提到锁就会涉及到死锁，需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。 -- 查看事务的 全局和session 隔离级别（ MySQL 5.7.19及之前使用tx_isolation） select @@global.transaction_isolation, @@session.transaction_isolation; -- 设置 全局 事务隔离级别为repeatable read set global transaction isolation level repeatable read -- 设置 当前session 事务隔离级别为read uncommitted set session transaction isolation level read uncommitted 二、一致性非锁定读和锁定读 InnoDB有两种不同的SELECT，即普通SELECT 和 锁定读SELECT。锁定读SELECT 又有两种，即SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE；锁定读SELECT 之外的则是 普通SELECT 。 不同的SELECT是否都需要加锁呢？ 1. 普通SELECT 时使用一致性非锁定读，不加锁； 2. 锁定读SELECT 使用锁定读，加锁； 3. 此外，DML(INSERT/UPDATE/DELETE)时，需要先查询表中的记录，此时也使用锁定读，加锁； FOR SHARE 语法是 MySQL 8.0 时加入的，FOR SHARE 和 LOCK IN SHARE MODE 是等价的，但，FOR SHARE 用于替代 LOCK IN SHARE MODE，不过，为了向后兼容，LOCK IN SHARE MODE依然可用。 1. 一致性非锁定读(consistent nonlocking read) InnoDB采用多版本并发控制(MVCC, multiversion concurrency control)来增加读操作的并发性。MVCC是指，InnoDB使用基于时间点的快照来获取查询结果，读取时在访问的表上不设置任何锁，因此，在事务T1读取的同一时刻，事务T2可以自由的修改事务T1所读取的数据。这种读操作被称为一致性非锁定读。这里的读操作就是普通SELECT。 隔离级别为RU和Serializable时不需要MVCC，因此，只有RC和RR时，才存在MVCC，才存在一致性非锁定读。 一致性非锁定读在两种隔离级别RC和RR时，是否有什么不同呢？是的，两种隔离级别下，拍得快照的时间点不同 1. RC时，同一个事务内的每一个一致性读总是设置和读取它自己的最新快照。也就是说，每次读取时，都再重新拍得一个最新的快照（所以，RC时总是可以读取到最新提交的数据）。 2. RR时，同一个事务内的所有的一致性读 总是读取同一个快照，此快照是执行该事务的第一个一致性读时所拍得的。 2. 锁定读(locking read) 如果你先查询数据，然后，在同一个事务内 插入/更新 相关数据，普通的SELECT语句是不能给你足够的保护的。其他事务可以 更新/删除 你刚刚查出的数据行。InnoDB提供两种锁定读，即：SELECT ... FOR SHARE 和 SELECT ... FOR UPDATE。它俩都能提供额外的安全性。 这两种锁定读在搜索时所遇到的（注意：不是最终结果集中的）每一条索引记录(index record)上设置排它锁或共享锁。此外，如果当前隔离级别是RR，它还会在每个索引记录前面的间隙上设置排它的或共享的gap lock（排它的和共享的gap lock没有任何区别，二者等价）。 看完背景介绍，我们再来看一下InnoDB提供的各种锁。 三、InnoDB提供的8种不同类型的锁 InnoDB一共有8种锁类型，其中，意向锁(Intention Locks)和自增锁(AUTO-INC Locks)是表级锁，剩余全部都是行级锁。此外，共享锁或排它锁(Shared and Exclusive Locks)尽管也作为8种锁类型之一，它却并不是具体的锁，它是锁的模式，用来“修饰”其他各种类型的锁。 MySQL5.7及之前，可以通过information_schema.innodb_locks查看事务的锁情况，但，只能看到阻塞事务的锁；如果事务并未被阻塞，则在该表中看不到该事务的锁情况。 MySQL8.0删除了information_schema.innodb_locks，添加了performance_schema.data_locks，可以通过performance_schema.data_locks查看事务的锁情况，和MySQL5.7及之前不同，performance_schema.data_locks不但可以看到阻塞该事务的锁，还可以看到该事务所持有的锁，也就是说即使事务并未被阻塞，依然可以看到事务所持有的锁（不过，正如文中最后一段所说，performance_schema.data_locks并不总是能看到全部的锁）。表名的变化其实还反映了8.0的performance_schema.data_locks更为通用了，即使你使用InnoDB之外的存储引擎，你依然可以从performance_schema.data_locks看到事务的锁情况。 performance_schema.data_locks的列LOCK_MODE表明了锁的类型，下面在介绍各种锁时，我们同时指出锁的LOCK_MODE。 1. 共享锁或排它锁(Shared and Exclusive Locks) 它并不是一种锁的类型，而是其他各种锁的模式，每种锁都有shard或exclusive两种模式。 当我们说到共享锁（S锁）或排它锁（X锁）时，一般是指行上的共享锁或者行上的排它锁。需要注意的是，表锁也存在共享锁和排它锁，即表上的S锁和表上的X锁，表上的锁除了这两种之外，还包括下面将会提到的意向共享锁(Shard Intention Locks)即IS锁、意向排它锁(Exclusive Intention Locks)即IX锁。表上的锁，除了这四种之外，还有其他类型的锁，这些锁都是在访问表的元信息时会用到的（create table/alter table/drop table等），本文不讨论这些锁，详细可见：常用SQL语句的MDL加锁源码分析。 数据行r上共享锁(S锁)和排它锁(X锁)的兼容性如下： 假设T1持有数据行r上的S锁，则当T2请求r上的锁时： 1. T2请求r上的S锁，则，T2立即获得S锁。T1和T2同时都持有r上的S锁。 2. T2请求r上的X锁，则，T2无法获得X锁。T2必须要等待直到T1释放r上的S锁。 假设T1持有r上的X锁，则当T2请求r上的锁时： T2请求r上的任何类型的锁时，T2都无法获得锁，此时，T2必须要等待直到T1释放r上的X锁 2. 意向锁(Intention Locks) 表锁。含义是已经持有了表锁，稍候将获取该表上某个/些行的行锁。有shard或exclusive两种模式。 LOCK_MODE分别是：IS或IX。 意向锁用来锁定层级数据结构，获取子层级的锁之前，必须先获取到父层级的锁。可以这么看InnoB的层级结构：InnoDB所有数据是schema的集合，schema是表的集合，表是行的集合。意向锁就是获取子层级（数据行）的锁之前，需要首先获取到父层级（表）的锁。 意向锁的目的是告知其他事务，某事务已经锁定了或即将锁定某个/些数据行。事务在获取行锁之前，首先要获取到意向锁，即： 1. 事务在获取行上的S锁之前，事务必须首先获取 表上的 IS锁或表上的更强的锁。 2. 事务在获取行上的X锁之前，事务必须首先获取 表上的 IX锁。 事务请求锁时，如果所请求的锁 与 已存在的锁兼容，则该事务 可以成功获得 所请求的锁；如果所请求的锁 与 已存在的锁冲突，则该事务 无法获得 所请求的锁。 表级锁(table-level lock)的兼容性矩阵如下：
对于上面的兼容性矩阵，一定注意两点： 1. 在上面的兼容性矩阵中，S是表的(不是行的)共享锁，X是表的(不是行的)排它锁。 2. 意向锁IS和IX 和任何行锁 都兼容（即：和行的X锁或行的S锁都兼容）。 所以，意向锁只会阻塞 全表请求（例如：LOCK TABLES ... WRITE），不会阻塞其他任何东西。因为LOCK TABLES ... WRITE需要设置X表锁，这会被意向锁IS或IX所阻塞。 InnoDB允许表锁和行锁共存，使用意向锁来支持多粒度锁(multiple granularity locking)。意向锁如何支持多粒度锁呢，我们举例如下 T1: SELECT * FROM t1 WHERE i=1 FOR UPDATE; T2: LOCK TABLE t1 WRITE; T1执行时，需要获取i=1的行的X锁，但，T1获取行锁前，T1必须先要获取t1表的IX锁，不存在冲突，于是T1成功获得了t1表的IX锁，然后，又成功获得了i=1的行的X锁；T2执行时，需要获取t1表的X锁，但，T2发现，t1表上已经被设置了IX锁，因此，T2被阻塞（因为表的X锁和表的IX锁不兼容）。 假设不存在意向锁，则： T1执行时，需要获取i=1的行的X锁（不需要获取t1表的意向锁了）；T2执行时，需要获取t1表的X锁，T2能否获取到T1表的X锁呢？T2无法立即知道，T2不得不遍历表t1的每一个数据行以检查，是否某个行上已存在的锁和自己即将设置的t1表的X锁冲突，这种的判断方法效率实在不高，因为需要遍历整个表。 所以，使用意向锁，实现了“表锁是否冲突”的快速判断。意向锁就是协调行锁和表锁之间的关系的，或者也可以说，意向锁是协调表上面的读写锁和行上面的读写锁（也就是不同粒度的锁）之间的关系的。 3. 索引记录锁(Record Locks) 也就是所谓的行锁，锁定的是索引记录。行锁就是索引记录锁，所谓的“锁定某个行”或“在某个行上设置锁”，其实就是在某个索引的特定索引记录（或称索引条目、索引项、索引入口）上设置锁。有shard或exclusive两种模式。 LOCK_MODE分别是：S,REC_NOT_GAP或X,REC_NOT_GAP。 行锁就是索引记录锁，索引记录锁总是锁定索引记录，即使表上并未定义索引。表未定义索引时，InnoDB自动创建隐藏的聚集索引（索引名字是GEN_CLUST_INDEX），使用该索引执行record lock。 4. 间隙锁(Gap Locks) 索引记录之间的间隙上的锁，锁定尚未存在的记录，即索引记录之间的间隙。有shard或exclusive两种模式，但，两种模式没有任何区别，二者等价。 LOCK_MODE分别是：S,GAP或X,GAP。 gap lock可以共存(co-exist)。事务T1持有某个间隙上的gap lock 并不能阻止 事务T2同时持有 同一个间隙上的gap lock。shared gap lock和exclusive gap lock并没有任何的不同，它俩并不冲突，它俩执行同样的功能。 gap lock锁住的间隙可以是第一个索引记录前面的间隙，或相邻两条索引记录之间的间隙，或最后一个索引记录后面的间隙。 索引是B+树组织的，因此索引是从小到大按序排列的，在索引记录上查找给定记录时，InnoDB会在第一个不满足查询条件的记录上加gap lock，防止新的满足条件的记录插入。
上图演示了：InnoDB在索引上扫描时，找到了c2=11的记录，然后，InnoDB接着扫描，它发现下一条记录是c2=18，不满足条件，InnoDB遇到了第一个不满足查询条件的记录18，于是InnoDB在18上设置gap lock，此gap lock锁定了区间(11, 18)。 为什么需要gap lock呢？gap lock存在的唯一目的就是阻止其他事务向gap中插入数据行，它用于在隔离级别为RR时，阻止幻影行(phantom row)的产生；隔离级别为RC时，搜索和索引扫描时，gap lock是被禁用的，只在 外键约束检查 和 重复key检查时gap lock才有效，正是因为此，RC时会有幻影行问题。 gap lock是如何阻止其他事务向gap中插入数据行的呢？看下图
索引是B+树组织的，因此索引是从小到大按序排列的，如果要插入10，那么能插入的位置只能是上图中标红的区间。在10和10之间插入时，我们就认为是插入在最后面的10的后面。如果封锁了标红的区间，那么其他事务就无法再插入10啦。 问题一：当T2要插入 10时，上图哪些地方允许插入（注意：索引是有序的哦）？ 答：(8, 10)和(10,11)。在10和10之间插入，我们就认为是插入在最后的10后面。 只要封锁住图中标红的区间，T2就无法再插入10啦。上面这两个区间有什么特点吗？对，这两个区间就是：满足条件的每一条记录前面的间隙，及，最后一条不满足条件的记录前面的间隙。InnoDB使用下一个键锁(Next-Key Locks)或间隙锁(Gap Locks)来封锁这种区间。 问题二：gap lock是用来阻塞插入新数据行的，那么，T2, insert into g values("z", 9) 会被阻塞吗？插入("z", 8)，("z", 10)，("z", 11)呢？ 答：上图中，T1的update设置的gap lock是 (8, 10)和(10,11)，而，insert intention lock的范围是(插入值, 向下的一个索引值)。insert intention lock的详细介绍请见下面的6. 插入意向锁(Insert Intention Locks)。 于是，对于上面这些插入值，得到的insert intention lock如下： 插入 ("z", 8)时，insert intention lock 是 (8, 10) -- 冲突，与gap lock (8, 10)重叠了 插入 ("z", 9)时，insert intention lock 是 (9, 10) -- 冲突，与gap lock (8, 10)重叠了 插入 ("z", 10)时，insert intention lock 是 (10, 11) -- 冲突，与gap lock (10, 11)重叠了 插入 ("z", 11)时，insert intention lock 是 (11, 15) -- 不冲突 事实是不是这样呢，看下图
是的，和我们分析的一致，为了看的更清楚，我们把结果列成图表如下
问题三：“gap是解决phantom row问题的”，插入会导致phantom row，但更新也一样也会产生phantom row啊。
例如，上图的T1和T2，T1把所有i=8的行更新为108，T2把i=15的行更新为8，如果T2不被阻塞，T1的WHERE条件岂不是多出了一行，即：T1出现了phantom row？ 答：nice question。我们自己来分析下T1和T2分别加了哪些锁 T1加的锁：idx_i上的next-key lock (5, 8]，PRIMARY上的"b"，以及idx_i上的gap lock (8,10) T2加的锁：idx_i上的next-key lock (11, 15]，PRIMARY上的"f"，以及idx_i上的gap lock (15,108)，最后这个gap lock是因为T1在idx_i上加了新值108 根据上面的分析，T1和T2的锁并没有重叠，即我们分析的结果是：T2不会被阻塞。 但，上图清楚的表明T2确实被阻塞了，原因竟然是：T2 insert intention lock和T1 gap lock(8, 10)冲突了。很奇怪，T2是更新语句，为什么会有insert intention lock呢？ 我不知道确切的原因，因为我没找到文档说这事。根据我的推断，update ... set 成功找到结果集然后执行更新时，在即将被更新进入行的新值上设置了insert intention lock（如果找不到结果集，则就不存在insert intention lock啦），因此，T2在idx_i上的新值8上设置了insert intention lock(8, 10)。最终，T2 insert intention lock(8, 10) 与 T1 gap lock(8, 10)冲突啦，T2被阻塞。 因此，update ... set 成功找到结果集时，会在即将被更新进入行的新值上设置 index record lock 以及 insert intention lock。如前所述，insert intention lock的范围是(插入值，下一个值)，如果T2是 update g set i=9 where i=15; 那么update ... set 所设置的新值是9，则T2 insert intention lock就是(9, 10)啦，它依然会和 T1 gap lock(8, 10)冲突，是这样吗？确实是的，感兴趣的同学可以试试。 5. 下一个键锁(Next-Key Locks) next-key lock 是 (索引记录上的索引记录锁) + (该索引记录前面的间隙上的锁) 二者的合体，它锁定索引记录以及该索引记录前面的间隙。有shard或exclusive两种模式。 LOCK_MODE分别是：S或X。 当InnoDB 搜索或扫描索引时，InnoDB在它遇到的索引记录上所设置的锁就是next-key lock，它会锁定索引记录本身以及该索引记录前面的gap("gap" immediately before that index record)。即：如果事务T1 在索引记录r 上有一个next-key lock，则T2无法在 紧靠着r 前面的那个间隙中 插入新的索引记录(gap immediately before r in the index order)。 next-key lock还会加在“supremum pseudo-record”上，什么是supremum pseudo-record呢？它是索引中的伪记录(pseudo-record)，代表此索引中可能存在的最大值，设置在supremum pseudo-record上的next-key lock锁定了“此索引中可能存在的最大值”，以及 这个值前面的间隙，“此索引中可能存在的最大值”在索引中是不存在的，因此，该next-key lock实际上锁定了“此索引中可能存在的最大值”前面的间隙，也就是此索引中当前实际存在的最大值后面的间隙。例如，下图中，supremum pseudo-record上的next-key lock锁定了区间(18, 正无穷)，正是此next-key lock阻止其他事务插入例如19, 100等更大的值。 supremum pseudo-record上的next-key lock锁定了“比索引中当前实际存在的最大值还要大”的那个间隙，“比大还大”，“bigger than bigger”
6. 插入意向锁(Insert Intention Locks) 一种特殊的gap lock。INSERT操作插入成功后，会在新插入的行上设置index record lock，但，在插入行之前，INSERT操作会首先在索引记录之间的间隙上设置insert intention lock，该锁的范围是(插入值, 向下的一个索引值)。有shard或exclusive两种模式，但，两种模式没有任何区别，二者等价。 LOCK_MODE分别是：S,GAP,INSERT_INTENTION或X,GAP,INSERT_INTENTION。 insert intention lock发出按此方式进行插入的意图：多个事务向同一个index gap并发进行插入时，多个事务无需相互等待。 假设已存在值为4和7的索引记录，事务T1和T2各自尝试插入索引值5和6，在得到被插入行上的index record lock前，俩事务都首先设置insert intention lock，于是，T1 insert intention lock (5, 7)，T2 insert intention lock (6, 7)，尽管这两个insert intention lock重叠了，T1和T2并不互相阻塞。 如果gap lock或next-key lock 与 insert intention lock 的范围重叠了，则gap lock或next-key lock会阻塞insert intention lock。隔离级别为RR时正是利用此特性来解决phantom row问题；尽管insert intention lock也是一种特殊的gap lock，但它和普通的gap lock不同，insert intention lock相互不会阻塞，这极大的提供了插入时的并发性。总结如下： 1. gap lock会阻塞insert intention lock。事实上，gap lock的存在只是为了阻塞insert intention lock 2. gap lock相互不会阻塞 3. insert intention lock相互不会阻塞 4. insert intention lock也不会阻塞gap lock INSERT插入行之前，首先在索引记录之间的间隙上设置insert intention lock，操作插入成功后，会在新插入的行上设置index record lock。 我们用下面三图来说明insert intention lock的范围和特性
上图演示了：T1设置了gap lock(13, 18)，T2设置了insert intention lock(16, 18)，两个锁的范围重叠了，于是T1 gap lock(13, 18)阻塞了T2 insert intention lock(16, 18)。
上图演示了：T1设置了insert intention lock(13, 18)、index record lock 13；T2设置了gap lock(17, 18)。尽管T1 insert intention lock(13, 18) 和 T2 gap lock(17, 18)重叠了，但，T2并未被阻塞。因为 insert intention lock 并不阻塞 gap lock。
上图演示了：T1设置了insert intention lock(11, 18)、index record lock 11；T2设置了next-key lock(5, 11]、PRIMARY上的index record lock "b"、gap lock(11, 18)。此时：T1 index record lock 11 和 T2 next-key lock(5, 11]冲突了，因此，T2被阻塞。 本文仅代表作者个人观点，不代表巅云官方发声，对观点有疑义请先联系作者本人进行修改，若内容非法请联系平台管理员，邮箱2522407257@qq.com。更多相关资讯，请到巅云www.rzxsoft.cn学习互联网营销技术请到巅云建站www.rzxsoft.cn。 |
