快上网专注成都网站设计 成都网站制作 成都网站建设
成都网站建设公司服务热线：028-86922220

网站建设知识

十年网站开发经验 + 多家企业客户 + 靠谱的建站团队

量身定制 + 运营维护+专业推广+无忧售后，网站问题一站解决

MySQLInnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

今天就跟大家聊聊有关MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析，可能很多人都不太了解，为了让大家更加了解，小编给大家总结了以下内容，希望大家根据这篇文章可以有所收获。

创新互联公司专注于雁江企业网站建设,成都响应式网站建设公司,商城网站开发。雁江网站建设公司,为雁江等地区提供建站服务。全流程按需网站设计，专业设计，全程项目跟踪，创新互联公司专业和态度为您提供的服务

文章正文开始

“加什么样的锁”与以下因素相关

  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

事务隔离级别设置和查看的详细语法请见：https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

二、一致性非锁定读和锁定读

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)的兼容性矩阵如下：

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

对于上面的兼容性矩阵，一定注意两点：

  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，防止新的满足条件的记录插入。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：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中插入数据行的呢?看下图

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

索引是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) -- 不冲突

事实是不是这样呢，看下图

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

是的，和我们分析的一致，为了看的更清楚，我们把结果列成图表如下

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

问题三：“gap是解决phantom row问题的”，插入会导致phantom row，但更新也一样也会产生phantom row啊。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

例如，上图的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”

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

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的范围和特性

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：T1设置了gap lock(13, 18)，T2设置了insert intention lock(16, 18)，两个锁的范围重叠了，于是T1  gap lock(13, 18)阻塞了T2 insert intention lock(16, 18)。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：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。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：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被阻塞。

7、自增锁(AUTO-INC Locks)

表锁。向带有AUTO_INCREMENT列  的表时插入数据行时，事务需要首先获取到该表的AUTO-INC表级锁，以便可以生成连续的自增值。插入语句开始时请求该锁，插入语句结束后释放该锁(注意：是语句结束后，而不是事务结束后)。

你可能会想，日常开发中，我们所有表都使用AUTO_INCREMENT作主键，所以会非常频繁的使用到该锁。不过，事情可能并不像你想的那样。在介绍AUTO-INC表级锁之前，我们先来看下和它密切相关的SQL语句以及系统变量innodb_autoinc_lock_mode

INSERT-like语句

  1. insert

  2. insert ... select

  3. replace

  4. replace ... select

  5. load data

外加，simple-inserts, bulk-inserts, mixed-mode-inserts

simple-inserts

待插入记录的条数，提前就可以确定(语句初始被处理时就可以提前确定)因此所需要的自增值的个数也就可以提前被确定。

包括：不带嵌入子查询的 单行或多行的insert, replace。不过，insert ... on duplicate key update不是

bulk-inserts

待插入记录的条数，不能提前确定，因此所需要的自增值的个数 也就无法提前确定

包括：insert ... select, replace ... select, load data

在这种情况下，InnoDB只能每次一行的分配自增值。每当一个数据行被处理时，InnoDB为该行AUTO_INCREMENT列分配一个自增值

mixed-mode-inserts

也是simple-inserts语句，但是指定了某些(非全部)自增列的值。也就是说，待插入记录的条数提前能知道，但，指定了部分的自增列的值。

INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');

INSERT ... ON DUPLICATE KEY  UPDATE也是mixed-mode，最坏情况下，它就是INSERT紧跟着一个UPDATE，此时，为AUTO_INCREMENT列所分配的值在UPDATE阶段可能用到，也可能用不到。

再看一下系统变量innodb_autoinc_lock_mode，它有三个候选值0，1，和2

8.0.3之前，默认值是1，即“连续性的锁定模式(consecutive lock  mode)”;8.0.3及之后默认值是2，即“交织性锁定模式(interleaved lock mode)”

a. 当innodb_autoinc_lock_mode=0时，INSERT-like语句都需要获取到AUTO-INC表级锁;

b.  当innodb_autoinc_lock_mode=1时，如果插入行的条数可以提前确定，则无需获得AUTO-INC表级锁;如果插入行的条数无法提前确定，则就需要获取AUTO-INC表级锁。因此，simple-inserts和mixed-mode  inserts都无需AUTO-INC表级锁，此时，使用轻量级的mutex来互斥获得自增值;bulk-inserts需要获取到AUTO-INC表级锁;

c. 当innodb_autoinc_lock_mode=2时，完全不再使用AUTO-INC表级锁;

我们生产数据库版本是5.6.23-72.1，innodb_autoinc_lock_mode=1，而且，我们日常开发中用到大都是simple-inserts，此时根本就不使用AUTO-INC表级锁，所以，AUTO-INC表级锁用到的并不多哦。

LOCK_MODE：AUTO-INC表级锁用到的并不多，且，AUTO-INC锁是在语句结束后被释放，较难在performance_schema.data_locks中查看到，因此，没有进行捕获。感兴趣的同学可以使用INSERT  ... SELECT捕获试试。

8、 空间索引(Predicate Locks for Spatial Indexes)

我们平时很少用到MySQL的空间索引。所以，本文忽略此类型的锁

到此为止，MySQL InnoDB 8种类型的锁我们就介绍完了。我们以一个例子结束8种类型的介绍。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

T1先执行，事务ID是8428;T2后执行，事务ID是8429

上图演示了：

  1. 任何事务，在锁定行之前，都需要先加表级锁intention lock，即：第三行的IX和***行的IX。

  2. idx_c是辅助索引，InnoDB扫描idx_c时遇到了c=222，于是，在idx_c上加了next-key  lock，即：第四行的X。next-key lock就是 index record lock+gap lock，于是此next-key  lock锁定了idx_c上值为222的索引记录，以及222前面的间隙，也就是间隙(22, 222)。

  3. idx_c是辅助索引，在主键索引之外的任何索引上加index record lock时，都需要在该行的主键索引上再加index record  lock，于是，又在PRIMARY上添加了index record lock，即：第五行的X,REC_NOT_GAP。

  4. InnoDB扫描完c=222后，又扫描到了c=2222，这是idx_c上，***个不满足索引扫描条件的索引记录，于是InnoDB在c=2222上加gap  lock，c=2222上的gap lock锁定的范围是“idx_c上2222前面的间隙”，这本应该是(222,  2222)，但，T1即将在idx_c上插入c=224，于是，c=2222上的gap lock锁定的范围是(224, 2222)。即：第六行的X,GAP。

  5. InnoDB即将在idx_c上插入c=224，224也是不满足c=222的，于是InnoDB在c=224上加gap lock，该gap  lock锁定了224前面的间隙，也就是(222, 224)，即，第七行的X,GAP。

  6. T2执行INSERT成功后，会在新插入行的加index record lock，但，T2在插入之前，首先要作的是得到表级锁intention  lock以及设置表的每个索引的insert intention lock，该锁的范围是(插入值, 向下的一个索引值)，于是，在设置idx_c上的insert  intention lock范围就是(226, 2222)，这个范围和事务T1第六行gap lock范围(224,  2222)重叠。于是，事务T2被阻塞了，T2必须等待，直到T1释放第六行的gap lock。

performance_schema.data_locks表中并不能看到T2的全部锁，比如，T2也得在iux_b上设置insert intention  lock，但，performance_schema.data_locks中并没有这个锁。关于performance_schema.data_locks中显示了哪些锁，请见本文***一段。

把这些锁及其范围列出来如下图所示

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

四、不同的SQL加了什么样的锁?

OK，我们已经了解了InnoDB各种不同类型的锁，那么，不同SQL语句各加了什么样的锁呢

我们用最朴素的想法来思考一下，用锁作什么呢?锁要作的就是达到事务隔离的目的，即：两个并发执行的事务T1和T2，如果T1正在修改某些行，那么，T2要并发  读取/修改/插入 满足T1查询条件的行时，T2就必须被阻塞，这是锁存在的根本原因。index record lock, gap lock, next-key  lock都是实现手段，这些手段使得锁既能达到目的，还能实现***的并发性。所以，当我们考虑事务T1中的SQL上加了什么锁时，就想一下，当T1执行时，如果并发的事务  T2不会触及到T1的行，则T2无需被阻塞，如果T2的要 读取/修改/插入  满足T1条件的行时，T2就得被T1阻塞。而T1阻塞T2的具体实现就是：T1在已存在的行上加index record  lock使得T2无法触碰已存在的行，以及，T1在不存在的行上加gap lock使得T2无法插入新的满足条件的行。

前面我们说过“加什么样的锁”与以下因素相关

  1. 当前事务的隔离级别

  2. SQL是一致性非锁定读(consistent nonlocking read)还是DML或锁定读(locking read)

  3. SQL执行时是否使用了索引，所使用索引的类型(主键索引，辅助索引、唯一索引)

我们来看一下，不同的隔离级别下，使用不同的索引时，分别加什么锁。在讨论之前，我们先剔除无需讨论的情况

首先，普通SELECT 使用一致性非锁定读，因此根本不存在锁。无需讨论;

再者，作为开发者，我们几乎从来不会使用到隔离级别RU和Serializable。这两个隔离级别无需讨论。

于是，剩下的就是  给定锁定读SELECT或DML(INSERT/UPDATE/DELETE)语句，在不同隔离级别下，使用不同类型的索引时，分别会加什么样的锁?直接给出答案，其加锁原则如下

（一）、RR时，如果使用非唯一索引进行搜索或扫描，则在所扫描的每一个索引记录上都设置next-key lock。

这里“所扫描的每一个索引记录”是指当扫描执行计划中所使用的索引时，搜索遇到的每一条记录。WHERE条件是否排除掉某个数据行并没有关系，InnoDB并不记得确切的WHERE条件，InnoDB倔强的只认其扫描的索引范围(index  range) 。

你可能觉得InnoDB在设置锁时蛮不讲理，竟然不管WHERE条件排除掉的某些行，这不是大大增加了锁的范围了嘛。不过，等我们了解了MySQL执行SQL时的流程，这就好理解了。MySQL的执行计划只会选择一个索引，使用一个索引来进行扫描，MySQL执行SQL语句的流程是，先由InnoDB引擎执行索引扫描，然后，把结果返回给MySQL服务器，MySQL服务器会再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集，而加锁时只考虑InnoDB扫描的索引，由MySQL服务器求值的其他WHERE条件并不考虑。当然，MySQL使用index_merge优化时会同时使用多个索引的，不过，这个时候设置锁时也并不特殊，同样，对于所用到的每一个索引，InnoDB在所扫描的每一个索引记录上都设置next-key  lock。

加的锁一般是next-key lock，这种锁住了索引记录本身，还锁住了每一条索引记录前面的间隙，从而阻止其他事务 向  索引记录前面紧接着的间隙中插入记录。

如果在搜索中使用了辅助索引(secondary index)，并且在辅助索引上设置了行锁，则，InnoDB还会在 相应的 聚集索引  上设置锁;表未定义聚集索引时，InnoDB自动创建隐藏的聚集索引(索引名字是GEN_CLUST_INDEX)，当需要在聚集索引上设置锁时，就设置到此自动创建的索引上。

（二）、RR时，如果使用了唯一索引的唯一搜索条件，InnoDB只在满足条件的索引记录上设置index record  lock，不锁定索引记录前面的间隙;如果用唯一索引作范围搜索，依然会锁定每一条被扫描的索引记录前面的间隙，并且再在聚集索引上设置锁。

（三）、RR时，在***个不满足搜索条件的索引记录上设置gap lock或next-key lock。

一般，等值条件时设置gap lock，范围条件时设置next-key lock。此gap lock或next-key  lock锁住***个不满足搜索条件的记录前面的间隙。

（四）、RR时，INSERT在插入新行之前，必须首先为表上的每个索引设置insert intention lock。

每个insert intention lock的范围都是(待插入行的某索引列的值, 此索引上从待插入行给定的值向下的***个索引值)。只有当insert  intention lock与某个gap lock或next-key  lock冲突时，才能在performance_schema.data_locks看到insert intention lock。

（五）、RC时，InnoDB只在完全满足WHERE条件的行上设置index record lock。

（六）、RC时，禁用了gap lock。

正因为此，RC时不存在gap lock或next-key lock。这是为什么呢?我们想一想啊，gap lock是用来解决phantom  row问题的，gap lock封锁的区间内不能插入新的行，因为插入时的insert intention lock会和gap  lock冲突，从而阻止了新行的插入。但，隔离级别RC是允许phantom row的，因此RC时gap lock是被禁用的。

（七）、RR或RC时，对于主键或唯一索引，当有重复键错误(duplicate-key error)时，会在 重复的索引记录上 设置 shared  next-key lock或shared index record lock。这可能会导致死锁。

假设T1, T2,  T3三个事务，T1已经持有了X锁，T2和T3发生了重复键错误，因此T2和T3都在等待获取S锁，这个时候，当T1回滚或提交释放掉了X锁，则T2和T3就都获取到了S锁，并且，T2和T3都请求X锁，“T2和T3同时持有S锁，且都在请求X锁”，于是死锁就产生了。

好了，规则都列出来了，是时候实践一把了。下面在展示锁时，我们同时指出了当前所使用的隔离级别，表上的索引以及事务的SQL语句。

实践一：搜索时无法使用索引，即全表扫描时，InnoDB在表的全部行上都加锁

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：搜索条件无法使用索引时，InnoDB不得不在表的全部行上都加锁。所以，索引实在太重要了，查询时，它能加快查询速度;更新时，除了快速找到指定行，它还能减少被锁定行的范围，提高插入时的并发性。

实践二：唯一索引和非唯一索引、等值查询和范围查询加锁的不同

搜索时使用 唯一索引 作等值查询时，InnoDB只需要加index record lock;搜索时使用 唯一索引作范围查询时 或  使用非唯一索引作任何查询时 ，InnoDB需要加next-key lock或gap lock。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

示例1演示了：使用非唯一索引 idx_c 搜索或扫描时，InnoDB要锁住索引本身，还要锁住索引记录前面的间隙，即next-key lock: X 和  gap lock: X,GAP。next-key lock既锁住索引记录本身，还锁住该索引记录前面的间隙，gap  lock只锁住索引记录前面的间隙。等值条件时，在***一个不满足条件的索引记录上设置gap lock。

示例2演示了：使用唯一索引 iux_b 的唯一搜索条件，即，使用唯一索引执行等值查找时，InnoDB只需锁住索引本身，即index record  lock: X, REC_NOT_GAP，并不锁索引前面的间隙。

示例3演示了：使用唯一索引 iux_b 进行范围扫描时，依然需要锁定扫描过的每一个索引记录，并且锁住每一条索引记录前面的间隙，即next-key  lock: X。范围条件时，在***一个不满足条件的索引记录上设置next-key lock。

实践三：不同隔离级别加锁的不同

无论何种隔离级别，SQL语句执行时，都是先由InnoDB执行索引扫描，然后，返回结果集给MySQL服务器，MySQL服务器再对该索引条件之外的其他查询条件进行求值，从而得到最终结果集。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图中，在不同的隔离级别下，执行了相同的SQL。无论何种隔离级别，PRIMARY上的index record  lock总是会加的，我们不讨论它。在idx_b上，隔离级别为RC时，InnoDB加了index record  lock，即：X,REC_NOT_GAP，隔离级别为RR时，InnoDB加了next-key lock，即X。注意：RC时没有gap lock或next-key  lock哦。

上图演示了：事务的隔离级别也会影响到设置哪种锁。如我们前面所说，gap lock是用来阻止phantom row的，而RC时是允许phantom  row，所以，RC时禁用了gap lock。因此，上图中，RC时没有在索引上设置gap lock或next-key lock。

实践四：操作不存在的索引记录时，也需要加锁

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图中，idx_b上并不存在b=266的索引记录，那么，当更新b=266的记录时，是否需要加锁呢?是的，也需要加锁

无论b=266是否存在，RR时，InnoDB在***个不满足搜索条件的索引记录上设置gap lock或next-key lock。一般，等值条件时设置gap  lock，范围条件时设置next-key lock。上图中是等值条件，于是InnoDB设置gap lock，即上图的X,GAP，其范围是(226,  2222)，正是此gap lock使得并发的事务无法插入b列大于等于266的值，RC时，由于gap lock是被禁止的，因此，并不会加gap  lock，并发的事务可以插入b列大于等于266的值。

上图演示了：操作不存在的索引记录时，也需要加锁。

实践五：重复键错误(duplicate-key error)时，会加共享锁。这可能会导致死锁。

对于主键或唯一索引，当有重复键错误(duplicate-key error)时，会在 重复的索引记录上 设置 shared next-key  lock或shared index record lock。这可能会导致死锁。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：T1在主键1上设置exclusive index record  lock。T2和T3插入时，会产生重复键错误，于是T2和T3都在主键1上设置了shared next-key lock。如上图所示

如果此时，T1 rollback释放掉其所持有的index record lock，则T2和T3等待获取的shared next-key  lock都成功了，然后，T2和T3争夺主键1上的index record lock，于是T2和T3就死锁了，因为它俩都持有shard next-key  lock，双方谁都不会放弃已经得到的shared next-key lock，于是，谁都无法得到主键1的index record lock。

需要明确的是死锁的可能性并不受隔离级别的影响，因为隔离级别改变的是读操作的行为，而死锁是由于写操作产生的。死锁并不可怕，MySQL会选择一个牺牲者，然后，在系统变量innodb_lock_wait_timeout指定的秒数达到后，自动回滚牺牲者事务;从MySQL5.7开始，新加入了系统变量innodb_deadlock_detect(默认ON)，如果开启此变量，则MySQL不会再等待，一旦探测到死锁，就立即回滚牺牲者事务。

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：在上图的状态下，当T1 commit时，T1释放了主键1上的index record lock，于是T2和T3等待获取的shared  next-key lock都成功了，然后，T2和T3争夺主键1上的index record lock，于是T2和T3死锁了，因为它俩都持有shard  next-key lock，双方谁都不会放弃已经得到的shared next-key lock，于是，谁都无法得到主键1的index record  lock。

五、performance_schema.data_locks中能看到全部的锁吗?

显而易见，performance_schema.data_locks并未显示全部的锁，那么，它显示了哪些锁呢?很不幸，我并未找到文档说这事，尽管文档(https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html)说：“事务持有的每一个锁  以及  事务被阻塞的每一个锁请求，都在该表中占据一行”，但，我们很多例子都表明，它并未显示全部的锁。根据我的试验，我猜测performance_schema.data_locks显示的是WHERE条件所触碰到的索引上的锁，“WHERE条件所触碰到的索引”是指SQL实际执行时所使用的索引，也就是SQL执行计划的key列所显示的索引，正因为此，INSERT时看不到任何锁，update  g set a=a+1 where b=22时只看到idx_b上的锁。需要强调的是，这是我自己试验并猜测的，我并未在文档中看到这种说法。

假设T1和T2两个事务操作同一个表，先执行T1，此时尽管performance_schema.data_locks中只显示T1的WHERE条件所触碰到的索引上的锁，但是，事实上在T1的WHERE条件触碰不到的索引上，也是会设置锁的。尽管表的索引idx并未被T1所触碰到，即performance_schema.data_locks显示T1在索引idx并没有设置任何锁，但，当T2执行  锁定读/插入/更新/删除 时触碰到了索引idx，T2才恍然发现，原来T1已经在索引idx上加锁了。

我们来看下面的三个例子

“performance_schema.data_locks无法看到全部锁”示例一

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

上图演示了：T1执行时，只触碰到了索引idx_b，T1执行完后，在performance_schema.data_locks中只能看到idx_b上的锁，看起来T1并未在idx_a上设置任何锁;但，当T2执行触碰到了索引idx_a时，T2才恍然发现，原来T1已经在idx_a上设置了index  record lock啦。

“performance_schema.data_locks无法看到全部锁”示例二

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

插入新行时，会先设置insert intention lock，插入成功后再在插入完成的行上设置index record lock。

上图演示了：T1插入了新行，但，在performance_schema.data_locks中，我们既看不到T1设置的insert intention  lock，也看不到T1设置的index record  lock。这是因为T1的WHERE条件并未触碰到任何索引(T1根本不存在WHERE条件)，因此我们看不到T1的这两个锁;但，当T2要删除T1新插入的行时，T2才恍然发现，原来T1已经在索引c2上设置了index  record lock啦。

“performance_schema.data_locks无法看到全部锁”示例三

MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析

插入新行时，本来是不会在performance_schema.data_locks中显示insert intention  lock的，因为插入时WHERE条件并未触碰到任何索引(插入时根本不存在WHERE条件)。

上图演示了：T2插入新行时的insert intention lock 和 T1的gap  lock冲突了，于是，我们得以在performance_schema.data_locks中观察到T2插入新行时需要请求insert intentin  lock。

看完上述内容，你们对MySQL InnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析有进一步的了解吗？如果还想了解更多知识或者相关内容，请关注创新互联行业资讯频道，感谢大家的支持。


网页名称：MySQLInnoDB锁介绍及不同SQL语句分别加什么样的锁的示例分析
网站路径：http://6mz.cn/article/pegpgi.html

其他资讯