1、锁的类型InnoDB实现了如下两种标准的行级锁共享锁S Lock允许事务对一条行数据进行读取排他锁X Lock允许事务对一条行数据进行删除或更新如果一个事务T1已经获得了行r的共享锁 那么另外的事务T2可以立即获得行r的共享锁 因为读取并没有改变行 r 的数据 称这种情况为锁兼容(Lock Compatible)。 但若有其他的事务T3想获得行r的排他锁 则其必须等待事务T1, T2释放行r上的共享锁——这种情况称为锁不兼容。因为获取排他锁一般是为了改变数据所以不能同时进行读取或则其他写入操作。image.png从上表可以发现X锁与任何锁都不兼容而S锁仅和S锁兼容。此外 InnoDB 存储引擎支持多粒度锁定 这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支待在不同粒度上进行加锁操作 InnoDB 存储引擎支持 一种额外的锁方式 称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次 意向锁意味着事务希望在更细粒度上进行加锁。InnoDB存储引擎支持意向锁设计比较简练其意向锁即为表级别的锁。设计目的主要是为了在事务中揭示下一行将被请求的锁类型。其支持两种意向锁意向共享锁IS Lock事务想要获得一张表中某几行的共享锁意向排他锁IX Lock事务想要获得一张表中某几行的排他锁由于InnoDB存储引擎支持的是行级别的锁因此意向锁不会阻塞除全表扫描以外的任何请求它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。如果没有意向锁当已经有人使用行锁对表中的某一行进行修改时如果另外一个请求要对全表进行修改那么就需要对所有的行是否被锁定进行扫描在这种情况下效率是非常低的不过在引入意向锁之后当有人使用行锁对表中的某一行进行修改之前会先为表添加意向互斥锁IX再为行记录添加互斥锁X在这时如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了只需要通过等待意向互斥锁被释放就可以了。故表级意向锁和行级锁的兼容性如下表所示image.png2、锁的算法InnoDB有三种行锁的算法Record Lock简单说就是单个行记录上加锁防止事务间修改或删除数据。Record Lock总是会去锁住索引记录如果表建立的时候没有设置任何一个索引InnoDB存储引擎会使用隐式的主键来进行锁定。Gap Lock间隙锁表示只锁住一段范围不锁记录本身通常表示两个索引记录之间或者索引上的第一条记录之前或者最后一条记录之后的锁。Next-Key LockGap Lock Record Lock锁定一个范围及锁定记录本身。例如一个索引有10, 11, 13, 20这四个值那么该索引可能被Next-key Locking的区间为(负无穷, 10), (10, 11), (11, 13), (13, 20), (20, 正无穷)。需要理解一点InnoDB中加锁都是给所有记录一条一条加锁并没有一个直接的范围可以直接锁住所以会生成多个区间。MySQL默认情况下使用RR的隔离级别而Next-key Lock正是为了解决RR隔离级别下的不可重复读问题和幻读问题。所谓不可重复读就是一个事务内执行相同的查询会看到不同的行记录在RR隔离级别下这是不允许的。不可重复读和幻读的区别1、不可重复读的重点是修改同样的条件第1次和第2次读取的值不一样。2、幻读的重点在于新增或者删除同样的条件 第1次和第2次读出来的记录数不一样从控制角度来看1、不可重复读只需要锁住满足条件的记录2、幻读要锁住满足条件及其相近的记录假设索引上有记录145812我们执行类似语句SELECT … WHERE col 10 FOR UPDATE。如果我们不在(8, 12)之间加上Next-key Lock另外一个会话就可能向其中插入一条记录9再执行一次相同的SELECT ... FOR UPDATE就会看到新插入的记录。这也是为什么MySQL插入一条记录时需要判断下一条记录上是否加锁了如果加锁就需要等待。InnoDB对行的查询默认采用Next-key算法然而当查询条件为等值时且索引有唯一属性时就是只锁定一条记录InnoDB存储引擎会对Next-Key Lock进行优化将其降级为Record Lock即仅锁住索引本身而不是一个范围因为此时不会产生重复读问题。3、锁读取一致性非锁定读(consistent nonlocking read)是指InnoDB存储引擎通过多版本控制(MVVC)读取当前数据库中行数据的方式。如果读取的行正在执行DELETE或UPDATE操作这时读取操作不会因此去等待行上锁的释放。相反地InnoDB会去读取行的一个快照。image.png上图直观地展现了InnoDB一致性非锁定读的机制。之所以称其为非锁定读是因为不需要等待行上排他锁的释放。快照数据是指该行的之前版本的数据每行记录可能有多个版本一般称这种技术为行多版本技术。由此带来的并发控制称之为多版本并发控制(Multi Version Concurrency Control, MVVC)。InnoDB是通过undo log来实现MVVC。undo log本身用来在事务中回滚数据因此快照数据本身是没有额外开销。此外读取快照数据是不需要上锁的因为没有事务需要对历史的数据进行修改操作。一致性非锁定读是InnoDB默认的读取方式即读取不会占用和等待行上的锁。但是并不是在每个事务隔离级别下都是采用此种方式。此外即使都是使用一致性非锁定读但是对于快照数据的定义也各不相同。在事务隔离级别READ COMMITTED和REPEATABLE READ下InnoDB使用一致性非锁定读。然而对于快照数据的定义却不同。我们下面举个例子来详细说明一下上述的情况。image.png首先在会话A中显示地开启一个事务然后读取test表中的id为1的数据但是事务并没有提交。与此同时在开启另一个会话B将test表中id为1的记录修改为id3但是事务同样也没有提交这样id1的行其实加了一个排他锁。由于InnoDB在READ COMMITTED和REPEATABLE READ事务隔离级别下使用一致性非锁定读这时如果会话A再次读取id为1的记录仍然能够读取到相同的数据。此时READ COMMITTED和REPEATABLE READ事务隔离级别没有任何区别。当会话B提交事务后会话A再次运行SELECT * FROM test WHERE id 1的SQL语句时两个事务隔离级别下得到的结果就不一样了对于READ COMMITTED的事务隔离级别它总是读取行的最新版本如果行被锁定了则读取该行版本的最新一个快照。因为会话B的事务已经提交所以在该隔离级别下上述SQL语句的结果集是空的。对于REPEATABLE READ的事务隔离级别总是读取事务开始时的行数据因此在该隔离级别下上述SQL语句仍然会获得相同的数据。在默认情况下即事务的隔离级别是repeatable read模式下InnoDB存储引擎的SELECT操作使用的是一致性非锁定读。但是在某些情况下用户需要显示的读取数据操作进行加锁保证数据逻辑的一致性。InnoDB提供了两种方式实现一致性锁定读select … for udpate对读取的行加了X锁select … lock in share mode对读取的行加了S锁需要注意的是以上两种语句必须在一个事务当中当事务提交了锁也就释放了。4、阻塞因为不同锁之间的兼容性关系有时候一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源这就是阻塞。在InnoDB中参数innodb_lock_wait_timeout用来控制等待的时间innodb_rollback_on_timeout用来设定是否在等待超时后回滚。前者是动态的后者是静态的。mysqlshow variables likeinnodb_lock_wait_timeout\G;***************************1. row Variable_name: innodb_lock_wait_timeout Value:50mysqlshow variables likeinnodb_rollback_on_timeout\G;1. row:innodb_rollback_on_timeout Value: OFF5、死锁死锁是指两个或两个以上的事务在执行过程中因争夺资源而造成的一种相互等待的现象。若无外力作用事务都将无法推进下去。解决死锁做简单的方法就是超时即当两个事务互相等待时当一个等待时间超过了某一阈值其中一个事务进行回滚另一个等待的事务就能继续进行。但是如果超时的事务所占权重比较大如事务更新了很多行占用了较多的undo log回滚这个事务的时间相对于另一个事务所占用的时间可能会更多就显得不合适了。因此除了超时机制当前数据库都普遍采用等待图wait-for graph的方式来进行死锁检测。wait-for graph要求数据库保存以下两种信息:锁的信息链表事务等待链表通过上述链表可以构造出一张图而在这个图中若存在回路就代表存在死锁因此资源间相互发生等待。在 wait-for graph中,事务为图中的节点。而在图中,事务T1指向T2边的定义为:事务T1等待事务T2所占用的资源事务T1最终等待T2所占用的资源,也就是事务之间在等待相同的资源,而事务T1发生在事务T2的后面来看一个例子image.png在 Transaction Wait Lists中可以看到共有4个事务t1、t2、t3、t4,故在wait-for graph中应有4个节点。image.png通过上图可以发现存在回路(t1,t2)因此存在死锁。可以发现wait-for graph是一种较为主动的死锁检测机制在每个事务请求锁并发生等待时都会判断是否存在回路若存在则有死锁通常来说InnoDB存储引擎选择回滚undo量最小的事务。6、InnoDB锁相关状态查询用户可以使用INFOMATION_SCHEMA库下的INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS表来监控当前事务并分析可能出现的锁问题。的关键字段如下mysqlSELECT * FROM information_schema.INNODB_TRX\G;*************************************1.row ********************************************* trx_id: 7311F4 trx_state: LOCK WAIT trx_started:2010-01-0410:49:33 trx_requested_lock_id: 7311F4:96:3:2 trx_wait_started:2010-01-0410:49:33 trx_weight:2trx_mysql_thread_id:471719trx_query:select* from parent lockinshare mode表只能显示当前运行的InnoDB事务并不能直接判断锁的一些情况。如果需要查看锁则还需要访问表该表的关键字段组成如下mysqlSELECT * FROM.INNODB_LOCKS\G;1. row lock_id:16219:56:4:5 lock_trx_id:16219lock_mode: X,GAP lock_type: RECORD lock_table:test.zlock_index: b lock_space:56lock_page:4lock_rec:5lock_data:6,72. row lock_id:16218:56:4:5:16218lock_mode: X,GAP lock_type: RECORD lock_table:test.zlock_index: b lock_space:56lock_page:4lock_rec:5lock_data:6,72rowsinset,1warning(0.00sec)通过表INNODB_LOCKS查看每张表上锁的情况后用户就可以来判断由此引发的等待情况。当时当事务量非常大其中锁和等待也时常发生这个时候就不那么容易判断。但是通过表INNODB_LOCK_WAITS可以很直观的反应当前事务的等待。表INNODB_LOCK_WAITS由四个字段组成如下表所示。mysqlSELECT * FROM.INNODB_LOCK_WAITS\G;*******************************************1.row************************************ requesting_trx_id: 7311F4 requesting_lock_id: 7311F4:96:3:2 blocking_trx_id: 730FEE blocking_lock_id: 730FEE:96:3:2通过上述的SQL语句用户可以清楚直观地看到哪个事务阻塞了另一个事务然后使用上述的事务ID和锁ID去INNODB_TRX和INNDOB_LOCKS表中查看更加详细的信息。7、锁的应用实例解析有一点需要特别注意MySQL怎么使用锁与事务的隔离级别、列上的索引状况等密切相关。单拎出一条SQL语句来讨论加锁的方式以及范围而不考虑使用场景都是耍流氓的行为。下面就不同场景下锁的使用情况做一下对比深入理解上面的理论知识。7.1 场景1RR隔离级别主键索引首先根据如下代码创建测试表t然后开启两个事务进行操作。create table t(a int primary key);insert into tselect1;insert into tselect2;insert into tselect5;insert into tselect7;insert into tselect9;开启事务1保持未提交的状态begin;select* from t wherea5forupdate;开启事务2并提交begin;insert into tselect4;commit;表t共有1、2、5、7、9五个值在上面的例子中在事务1中首先对a5进行X锁定而由于a是主键且唯一因此锁定的仅是5这个值而不是2,5这个范围。这样在事务2中插入值4而不会阻塞可以立即插入并返回。即锁定由Next-key Lock算法降级为Record Lock从而提高应用的并发性。需要注意的一点是对于唯一键值的锁定Next-Key Lock降级为Record Lock仅存在于查询所有的唯一索引列。若唯一索引由多个列组成而查询仅是查找多个唯一索引列中的其中一个那么查询其实是range类型查询而不是const类型查询故InnoDB存储引擎依然使用Next-Key Lock进行锁定。但如果对主键进来范围查询时锁的范围是怎么样的呢begin;select* from t where a5forupdate;针对下面的sql语句insert into test.tselect6;insert into test.tselect8第一条会产生锁等待而第二条可以插入成功。表t共有1、2、5、7、9五个值在上面的例子中在事务1中执行a5范围查询时事务1加锁范围是(负无穷, 1)、(1, 2)、(2, 5)、(5, 7)且1、2、5、7这四条记录也都加锁了也就是说再插入7的记录都是会产生锁等待插入记录8是成功的。那么InnoDB如何判断是否允许插入数据呢对于普通索引insert的加锁策略是查找小于等于 insert_rec 的第一条记录然后查看第一条记录的下一个记录是否有Gap锁有则等待没有则插入。比如说我们插入6这条记录首先定位6的记录也就是5然后确认5的下一条记录是否锁住了Gap这里也就是7当7这条记录有锁是代表锁住的是(5, 7)这个范围就不允许插入会申请一把插入意向锁保证了可重复读。证明了在RR隔离级别下使用了Next-key Lock来保证其“可重复读”的特性。如果没有锁就直接插入即可。但是如果插入的记录有唯一约束时只判断下一条记录是否锁住了Gap就不行了显然会插入重复数据破坏唯一性。这时还会把插入的记录与前一条数据进行比较如果相同则给插入记录的前一条记录加S Locklock in share mode加锁成功则返回duplicate key否则等待S Lock。这个地方可能有人会有一个疑问为什么MySQL在加锁时不直接加5这条记录本身以及5的记录呢为什么还要给加锁呢因为(5, 7)加不加锁并不会影响RR级别可重复读的特性。其实这就跟B树有关系了首先MySQL定位到1这条记录并加锁然后顺着1往后读取数据并加锁直到读取到第一条不匹配数据才能确定是否停止继续读取数据而在RR隔离级别下只要被读到的数据都需要进行加锁。如果查询条件是5那么加锁只会加到5这条记录为止。如果是在RC隔离级别下只会对符合条件的记录进行加记录锁不会对满足条件的下一条记录进行加锁。7.2 场景2RR隔离级别普通索引正如上面所介绍的Next-Key Lock降级为Record Lock仅在查询的列是唯一索引且条件为等值查询的情况下。若是辅助索引则情况会完全不同首先根据如下代码创建测试表zcreate table z(id int primary key,b int,index(b));insert into z values(1,1);insert into z values(3,1);insert into z values(5,3);insert into z values(7,6);insert into z values(10,8);begin;select* from z whereb3forupdate;这时SQL语句通过索引列b进行查询因此其使用传统的Next-key Locking技术加锁并且由于有两个索引其需要分别进行锁定。对于聚集索引其仅对列id等于5的索引加上Record Lock。那么为什么主键索引上的记录也要加锁呢因为有可能其他事务会根据主键对 z 表进行更新试想一下如果主键索引没有加锁那么显然会存在并发问题。而对于辅助索引其加上的是Next-key Lock锁定的范围是1, 3特别需要注意的是InnoDB存储引擎还会对辅助索引下一个键值加上Gap lock即还有一个辅助索引范围为3, 6的锁。此时若在新的事务2中运行下面的SQL语句select* from z whereid5lockinshare mode;insert into zselect2,1;insert into zselect4,2;insert into zselect6,6;第一个SQL语句在事务1中执行的SQL语句已经对聚集索引中的列“a5”的值加上X锁因此执行会被阻塞。第二个SQL语句主键插入2没有问题插入的辅助索引值1也不在锁定的范围1,3中因此可以执行成功。第三个SQL语句主键插入4没有问题插入的辅助索引值2在锁定的范围1,3中因此执行会被阻塞。第四个SQL语句插入的主键6没有被锁定6也不在范围1,3之间。但插入的值6在另一个锁定的范围3,6中故同样需要等待。在RR隔离级别下对于INSERT的操作其会检查插入记录的下一条记录是否被锁定若已经被锁定则不允许操作从而避免不可重复读问题。而下面的SQL语句不会被阻塞可以立即执行insert into zselect8,6;insert into zselect2,0;insert into zselect6,7;从上面的例子中可以看到Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内解决“不可重复读”问题的产生。例如在上面的例子中事务1中用户已经锁定了b3的记录若此时没有Gap Lock锁定(3, 6)那么用户可以插入索引b列为3的记录这会导致事务1中的用户再次执行同样的查询时会返回不同的记录即产生不可重复读问题。这里有一个问题值得思考一下为什么插入(6, 6)不允许而插入(8, 6)是允许的。这跟InnoDB索引结构有关系我们知道二级索引是指向主键所以结构如(1, 1), (1, 3), (3, 5), (6, 7), (8, 10)。真正的Gap锁锁住的也是((1, 1), (1, 3))这样的结构所以当我们插入(6, 6)时需要插入到(3, 5), (6, 7)之间这区间被锁所以无法插入而我们插入(8, 6)是需要插入到(6, 7), (8, 10)之间没有锁存在所以可以插入成功。另外在RR隔离级别下我们访问条件为二级索引的情况下就算访问一条不存在的记录同样需要加Next-key Lokcs比如我们查询select* from z whereb7forupdate;InnoDB会对(6, 8)这个区间加了Gap Lock也就是说插入这个区间的数据都会被阻塞。虽然在RR隔离级别默认使用Gap Lock但用户可以通过以下两种方式来显式地关闭Gap Lock将事务的隔离级别设置为READ COMMITTED将参数innodb_locks_unsafe_for_binlog设置为1当设置了上述参数或隔离级别调整到READ COMMITTED时除了外键约束和唯一性检查duplicate key依然需要Gap Lock其余情况仅使用Record Lock进行锁定。但需要知道的是上述设置破坏了事务的隔离性并且对于MySQL复制来说可能会导致主从数据的不一致。虽然MySQL目前默认隔离级别是RR但是基本生产环境标配基本都是RC隔离级别ROW格式。7.3 场景3RC隔离级别主键索引将事务隔离级别切为RC开启事务1保持未提交的状态begin;select* from z where id3forupdate;执行下面的SQLinsert into zselect6,6;可以执行成功。证明在事务1执行主键范围for update时事务2对这个范围扔可以申请写锁。证明RC隔离级别没有使用NEXT-KEY Lock而是使用行级锁锁住对应的记录。7.4 场景4RC隔离级别普通索引将事务隔离级别切为RC开启事务1保持未提交的状态begin;select* from z where b3forupdate;此时若在新的事务2中运行下面的SQL语句insert into zselect8,4;update zsetb10where b6;update zsetb10where b1;第一个SQL语句插入数据到事务1的for update范围内是可以的因为这里事务1在RC模式下没有加NEXT-KEY LOCK锁所以可以插入数据。第二个SQL语句会出现锁等待我们选择普通索引作为条件此时MySQL给普通索引b3的记录都会加行锁。同时这些记录对应主键索引上的记录也都加上了锁第三个SQL语句插入成功说明b3的记录都无锁。7.5 场景5RR 无索引InnoDB行锁是通过给索引上的索引项加锁来实现的这一点MySQL与Oracle不同后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着只有通过索引条件检索数据InnoDB才使用行级锁否则InnoDB将会给所有记录加锁。在RR隔离级别下由于查询条件没有索引那么InnoDB需要扫描所有数据来查找数据对于扫描过的数据InnoDB都会加上锁并且是加Next-key lock。建立测试表create table tab_no_index(id int,b int)engineinnodb;insert into tab_no_index values(1,1),(2,2),(3,3),(4,4),(100,100);开启事务1保持未提交的状态begin;select* from tab_no_index whereid1forupdate;执行下面的SQLinsert into tab_no_index values(5,5);这时候并不能插入成功。看起来事务1只给一行加了排他锁但事务2在请求其他行的排他锁时却出现了锁等待。原因就在于没有索引的情况下InnoDB只能扫描所有记录锁住所有记录。当我们给其增加一个唯一索引后InnoDB就只锁定了符合条件的行。当我们给其增加一个唯一索引后InnoDB就只锁定了符合条件的行。create table tab_with_index(id int,b int,primary key(id));insert into tab_with_index values(1,1),(2,2),(3,3),(4,4),(100,100);开启事务1保持未提交的状态begin;select* from tab_with_index whereid1forupdate;执行下面的SQLselect* from tab_with_index whereid2forupdate;由这个例子可以看出对于id是主键索引的情况下只锁了id1这一行记录。其余的行都是可以进行DML操作的但前提条件是以id为条件。如果是以b字段为条件那么还是会锁的。7.6 场景6RC 无索引上面演示了在RR隔离级别下对于where条件无索引的情况下InnoDB是对所有记录加Next-key Locks。但是在RC隔离级别下对于where条件无索引的情况下则不会对所有记录加锁而是只对命中的数据的聚簇索引加X锁。同样针对tab_no_index表开启事务1保持未提交的状态begin;delete from tab_no_index whereid1;执行下面的SQLdelete from tab_no_index whereid99;update tab_no_indexsetid2whereid100;insert into tab_no_indexselect99,99;都可以执行成功。7.7 沙场练兵有了上面的分场景演习下面就两条简单SQL进行一场加锁分析的实战。下面两条简单的SQL加什么锁select* from t1 whereid10;delete from t1 whereid10;一眼看去不经大脑的回答是SQL1不加锁。因为MySQL是使用多版本并发控制的读不加锁SQL2对id 10的记录加写锁 (走主键索引)可能是正确的也有可能是错误的已知条件不足要回答这个问题还缺少几个前提条件id列是不是主键当前系统的隔离级别是什么id列如果不是主键那么id列上有索引吗id列上如果有二级索引那么这个索引是唯一索引吗组合一id列是主键RC隔离级别这个组合是最简单最容易分析的组合。id是主键Read Committed隔离级别给定SQLdelete from t1 where id 10;只需要将主键上id 10的记录加上X锁即可。image.png组合二id唯一索引RCid是unique索引而主键是name列。由于id是unique索引因此delete语句会选择id列的索引进行where条件的过滤在找到id10的记录后首先会将unique索引上的id10索引记录加上X锁同时会根据读取到的name列回主键索引(聚簇索引)然后将聚簇索引上的name ‘d’ 对应的主键索引项加X锁。为什么聚簇索引上的记录也要加锁如果并发的一个SQL是通过主键索引来更新update t1 set id 100 where name ‘d’;此时如果delete语句没有将主键索引上的记录加锁那么并发的update就会感知不到delete语句的存在违背了同一记录上的更新/删除需要串行执行的约束。若id列是unique列其上有unique索引。那么SQL需要加两个X锁一个对应于id unique索引上的id 10的记录另一把锁对应于聚簇索引上的[name’d’,id10]的记录。image.png组合三id非唯一索引RC相对于组合一、二id列不再唯一只有一个普通的索引。满足id 10查询条件的记录均已加锁。同时这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于组合二最多只有一个满足等值查询的记录而组合三会将所有满足查询条件的记录都加锁。image.png组合四id无索引RC这个过滤条件没法通过索引进行过滤那么只能走全表扫描做过滤。对应于这个组合SQL会加什么锁换句话说全表扫描时会加什么锁由于id列上没有索引因此只能走聚簇索引进行全部扫描。聚簇索引上所有的记录都被加上了X锁。无论记录是否满足条件全部被加上X锁。既不是加表锁也不是在满足条件的记录上加行锁。image.png为什么不是只在满足条件的记录上加锁呢这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤那么存储引擎层面就会将所有记录加锁后返回然后由MySQL Server层进行过滤。因此也就把所有的记录都锁上了。在实际的实现中MySQL有一些改进在MySQL Server过滤条件发现不满足后会调用unlock_row方法把不满足条件的记录放锁 (违背了2PL的约束)。这样做保证了最后只会持有满足条件记录上的锁但是每条记录的加锁操作还是不能省略的。组合五id主键RR上面的四个组合都是在Read Committed隔离级别下的加锁行为接下来的四个组合是在Repeatable Read隔离级别下的加锁行为。id列是主键列Repeatable Read隔离级别针对delete from t1 where id 10;这条SQL加锁与组合一[id主键Read Committed]一致。组合六id唯一索引RR与组合二[id唯一索引Read Committed]一致。两个X锁id唯一索引满足条件的记录上一个对应的聚簇索引上的记录一个。组合七id非唯一索引RRRC隔离级别允许幻读而RR隔离级别不允许存在幻读。那么RR隔离级别下如何防止幻读呢组合七Repeatable Read隔离级别id上有一个非唯一索引执行delete from t1 where id 10;假设选择id列上的索引进行条件过滤最后的加锁行为是怎么样的呢image.png相对于组合三[id列上非唯一锁Read Committed]看似相同其实却有很大的区别。最大的区别在于多了一个GAP锁而且GAP锁看起来也不是加在记录上的是加载两条记录之间的位置GAP锁有何用这个多出来的GAP锁就是RR隔离级别相对于RC隔离级别不会出现幻读的关键。GAP锁锁住的位置不是记录本身而是两条记录之间的GAP。所谓幻读就是同一个事务连续做两次当前读(例如select * from t1 where id 10 for update;)那么这两次当前读返回的是完全相同的记录 (记录数量一致记录本身也一致)第二次的当前读不会比第一次返回更多的记录 (幻象)。如何保证两次当前读返回一致的记录那就需要在第一次当前读与第二次当前读之间其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能GAP锁应运而生。如图中所示有哪些位置可以插入新的满足条件的项 (id 10)考虑到B树索引的有序性满足条件的项一定是连续存放的。记录[6,c]之前不会插入id10的记录[6,c]与[10,b]间、[10,b]与[10,d]间、[10,d]与[11,f]间可以插入满足条件的[10,e],[10,z]等而[11,f]之后也不会插入满足条件的记录。因此为了保证[6,c]与[10,b]间[10,b]与[10,d]间[10,d]与[11,f]不会插入新的满足条件的记录MySQL选择了用GAP锁将这三个GAP给锁起来。Insert操作如insert [10,a]首先会定位到[6,c]与[10,b]间然后在插入前会检查这个GAP是否已经被锁上如果被锁上则Insert不能插入记录。因此通过第一遍的当前读不仅将满足条件的记录锁上 (X锁)与组合三类似。同时还是增加3把GAP锁将可能插入满足条件记录的3个GAP给锁上保证后续的Insert不能插入新的id10的记录也就杜绝了同一事务的第二次当前读出现幻象的情况。既然防止幻读需要靠GAP锁的保护为什么组合五、组合六也是RR隔离级别却不需要加GAP锁呢 GAP锁的目的是为了防止同一事务的两次当前读出现幻读的情况。而组合五id是主键组合六id是unique键都能够保证唯一性。一个等值查询最多只能返回一条记录而且新的相同取值的记录一定不会在新插入进来因此也就避免了GAP锁的使用。结论Repeatable Read隔离级别下id列上有一个非唯一索引对应SQLdelete from t1 where id 10;首先通过id索引定位到第一条满足查询条件的记录加记录上的X锁加GAP上的GAP锁然后加主键聚簇索引上的记录X锁然后返回然后读取下一条重复进行。直至进行到第一条不满足条件的记录[11,f]此时不需要加记录X锁但是仍旧需要加GAP锁最后返回结束。组合八id无索引RRid列上没有索引。此时SQLdelete from t1 where id 10;没有其他的路径可以选择只能进行全表扫描。最终的加锁情况如下图所示image.png这是一个很恐怖的现象。首先聚簇索引上的所有记录都被加上了X锁。其次聚簇索引每条记录间的间隙(GAP)也同时被加上了GAP锁。这个示例表只有6条记录一共需要6个记录锁7个GAP锁。试想如果表上有1000万条记录呢在这种情况下这个表上除了不加锁的快照度其他任何加锁的并发SQL均不能执行不能更新不能删除不能插入全表被锁死。当然跟组合四[id无索引, Read Committed]类似这个情况下MySQL也做了一些优化就是所谓的semi-consistent read。semi-consistent read开启的情况下对于不满足查询条件的记录MySQL会提前放锁。结论在Repeatable Read隔离级别下如果进行全表扫描的当前读那么会锁上表中的所有记录同时会锁上聚簇索引内的所有GAP杜绝所有的并发 更新/删除/插入 操作。当然也可以通过触发semi-consistent read来缓解加锁开销与并发影响但是semi-consistent read本身可能会带来其他问题。组合九SerializableSerializable隔离级别。对于SQL2delete from t1 where id 10;来说Serializable隔离级别与Repeatable Read隔离级别完全一致Serializable隔离级别影响的是SQL1select * from t1 where id 10;这条SQL在RCRR隔离级别下都是快照读不加锁。但是在Serializable隔离级别SQL1会加读锁也就是说快照读不复存在MVCC并发控制降级为Lock-Based CC。MVCC 最大的好处读不加锁读写不冲突。在读多写少的OLTP应用中读写不冲突是非常重要的极大的增加了系统的并发性能这也是为什么现阶段几乎所有的RDBMS都支持了MVCC在MySQL/InnoDB中所谓的读不加锁并不适用于所有的情况而是隔离级别相关的。Serializable隔离级别读不加锁就不再成立所有的读操作都是当前读