Py学习  »  DATABASE

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

数据分析与开发 • 5 年前 • 448 次点击  

(给数据分析与开发加星标,提升数据技能


来源:iceman1952

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


事务隔离级别设置和查看的详细语法请见:

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



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


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被阻塞。


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种类型的介绍。



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中显示了哪些锁,请见本文最后一段。


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



四、不同的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在表的全部行上都加锁



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


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


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



示例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服务器再对该索引条件之外的其他查询条件进行求值,从而得到最终结果集。



上图中,在不同的隔离级别下,执行了相同的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。


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



上图中,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。这可能会导致死锁。



上图演示了: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不会再等待,一旦探测到死锁,就立即回滚牺牲者事务。



上图演示了:在上图的状态下,当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无法看到全部锁”示例一



上图演示了: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无法看到全部锁”示例二



插入新行时,会先设置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无法看到全部锁”示例三



插入新行时,本来是不会在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 死锁与日志二三事

记录一次 Mysql 死锁排查过程

架构师带你玩转分布式锁


看完本文有收获?请转发分享给更多人

关注「数据分析与开发」加星标,提升数据技能

喜欢就点一下「好看」呗~


今天看啥 - 高品质阅读平台
本文地址:http://www.jintiankansha.me/t/ZA3CUnf5C0
Python社区是高质量的Python/Django开发社区
本文地址:http://www.python88.com/topic/29012
 
448 次点击