组合五: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非唯一索引+RR 还记得前面提到的MySQL的四种隔离级别的区别吗?RC隔离级别允许幻读,而RR隔离级别,不允许存在幻读。但是在组合五、组合六中,加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下,如何防止幻读呢?问题的答案,就在组合七中揭晓。 组合七,Repeatable Read隔离级别,id上有一个非唯一索引,执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤,最后的加锁行为,是怎么样的呢?同样看下面这幅图: 其实这个多出来的GAP锁,就是RR隔离级别,相对于RC隔离级别,不会出现幻读的关键。确实,GAP锁锁住的位置,也不是记录本身,而是两条记录之间的GAP。所谓幻读,就是同一个事务,连续做两次当前读 (例如:select * from t1 where id = 10 for update;),那么这两次当前读返回的是完全相同的记录 (记录数量一致,记录本身也一致),第二次的当前读,不会比第一次返回更多的记录 (幻象)。 如何保证两次当前读返回一致的记录,那就需要在第一次当前读与第二次当前读之间,其他的事务不会插入新的满足条件的记录并提交。为了实现这个功能,GAP锁应运而生。 如图中所示,有哪些位置可以插入新的满足条件的项 (id = 10),考虑到B+树索引的有序性,满足条件的项一定是连续存放的。记录[6,c]之前,不会插入id=10的记录;[6,c]与[10,b]间可以插入[10, aa];[10,b]与[10,d]间,可以插入新的[10,bb],[10,c]等;[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,aa],首先会定位到[6,c]与[10,b]间,然后在插入前,会检查这个GAP是否已经被锁上,如果被锁上,则Insert不能插入记录。因此,通过第一遍的当前读,不仅将满足条件的记录锁上 (X锁),与组合三类似。同时还是增加3把GAP锁,将可能插入满足条件记录的3个GAP给锁上,保证后续的Insert不能插入新的id=10的记录,也就杜绝了同一事务的第二次当前读,出现幻象的情况。 有心的朋友看到这儿,可以会问:既然防止幻读,需要靠GAP锁的保护,为什么组合五、组合六,也是RR隔离级别,去不需要加GAP锁呢? 首先,这是一个好问题。其次,回答这个问题,也很简单。GAP锁的目的,是为了防止同一事务的两次当前读,出现幻读的情况。而组合五,id是主键;组合六,id是unique键,都能够保证唯一性。一个等值查询,最多只能返回一条记录,而且新的相同取值的记录,一定不会在新插入进来,因此也就避免了GAP锁的使用。其实,针对此问题,还有一个更深入的问题:如果组合五、组合六下,针对SQL:select * from t1 where id = 10 for update; 第一次查询,没有找到满足查询条件的记录,那么GAP锁是否还能够省略?此问题留给大家思考。 结论:Repeatable Read隔离级别下,id列上有一个非唯一索引,对应SQL:delete from t1 where id = 10; 首先,通过id索引定位到第一条满足查询条件的记录,加记录上的X锁,加GAP上的GAP锁,然后加主键聚簇索引上的记录X锁,然后返回;然后读取下一条,重复进行。直至进行到第一条不满足条件的记录[11,f],此时,不需要加记录X锁,但是仍旧需要加GAP锁,最后返回结束。 组合八:id无索引+RR 组合八,Repeatable Read隔离级别下的最后一种情况,id列上没有索引。此时SQL:delete from t1 where id = 10; 没有其他的路径可以选择,只能进行全表扫描。最终的加锁情况,如下图所示: 在这种情况下,这个表上,除了不加锁的快照度,其他任何加锁的并发SQL,均不能执行,不能更新,不能删除,不能插入,全表被锁死。 当然,跟组合四:[id无索引, Read Committed]类似,这个情况下,MySQL也做了一些优化,就是所谓的semi-consistent read。semi-consistent read开启的情况下,对于不满足查询条件的记录,MySQL会提前放锁。针对上面的这个用例,就是除了记录[d,10],[g,10]之外,所有的记录锁都会被释放,同时不加GAP锁。semi-consistent read如何触发:要么是read committed隔离级别;要么是Repeatable Read隔离级别,但是设置了 innodb_locks_unsafe_for_binlog 参数。更详细的关于semi-consistent read的介绍,可参考我之前的一篇博客:MySQL+InnoDB semi-consitent read原理及实现分析 。 结论:在Repeatable Read隔离级别下,如果进行全表扫描的当前读,那么会锁上表中的所有记录,同时会锁上聚簇索引内的所有GAP,杜绝所有的并发 更新/删除/插入 操作。当然,也可以通过触发semi-consistent read,来缓解加锁开销与并发影响,但是semi-consistent read本身也会带来其他问题,不建议使用。 组合九:Serializable 针对前面提到的简单的SQL,最后一个情况:Serializable隔离级别。对于SQL2:delete from t1 where id = 10; 来说,Serializable隔离级别与Repeatable Read隔离级别完全一致,因此不做介绍。 Serializable隔离级别,影响的是SQL1:select * from t1 where id = 10; 这条SQL,在RC,RR隔离级别下,都是快照读,不加锁。但是在Serializable隔离级别,SQL1会加读锁,也就是说快照读不复存在,MVCC并发控制降级为Lock-Based CC。 结论:在MySQL/InnoDB中,所谓的读不加锁,并不适用于所有的情况,而是隔离级别相关的。Serializable隔离级别,读不加锁就不再成立,所有的读操作,都是当前读。 一条复杂的SQL 写到这里,其实MySQL的加锁实现也已经介绍的八八九九。只要将本文上面的分析思路,大部分的SQL,都能分析出其会加哪些锁。而这里,再来看一个稍微复杂点的SQL,用于说明MySQL加锁的另外一个逻辑。SQL用例如下: 在详细分析这条SQL的加锁情况前,还需要有一个知识储备,那就是一个SQL中的where条件如何拆分?具体的介绍,建议阅读我之前的一篇文章:SQL中的where条件,在数据库中提取与应用浅析 。在这里,我直接给出分析后的结果:
在分析出SQL where条件的构成之后,再来看看这条SQL的加锁情况 (RR隔离级别),如下图所示: 结论:在Repeatable Read隔离级别下,针对一个复杂的SQL,首先需要提取其where条件。Index Key确定的范围,需要加上GAP锁;Index Filter过滤条件,视MySQL版本是否支持ICP,若支持ICP,则不满足Index Filter的记录,不加X锁,否则需要X锁;Table Filter过滤条件,无论是否满足,都需要加X锁。 死锁原理与分析 本文前面的部分,基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁,有两个比较重要的作用:
下面,来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁;另一个是两个Session的一条SQL,产生死锁): 第二个用例,虽然每个Session都只有一条语句,仍旧会产生死锁。要分析这个死锁,首先必须用到本文前面提到的MySQL加锁的规则。针对Session 1,从name索引出发,读到的[hdc, 1],[hdc, 6]均满足条件,不仅会加name索引上的记录X锁,而且会加聚簇索引上的记录X锁,加锁顺序为先[1,hdc,100],后[6,hdc,10]。而Session 2,从pubtime索引出发,[10,6],[100,1]均满足过滤条件,同样也会加聚簇索引上的记录X锁,加锁顺序为[6,hdc,10],后[1,hdc,100]。发现没有,跟Session 1的加锁顺序正好相反,如果两个Session恰好都持有了第一把锁,请求加第二把锁,死锁就发生了。 结论:死锁的发生与否,并不在于事务中有多少条SQL语句,死锁的关键在于:两个(或以上)的Session加锁的顺序不一致。而使用本文上面提到的,分析MySQL每条SQL语句的加锁规则,分析出每条语句的加锁顺序,然后检查多个并发SQL间是否存在以相反的顺序加锁的情况,就可以分析出各种潜在的死锁情况,也可以分析出线上死锁发生的原因。 总结 写到这儿,本文也告一段落,做一个简单的总结,要做的完全掌握MySQL/InnoDB的加锁规则,甚至是其他任何数据库的加锁规则,需要具备以下的一些知识点: 了解数据库的一些基本理论知识:数据的存储格式 (堆组织表 vs 聚簇索引表);并发控制协议 (MVCC vs Lock-Based CC);Two-Phase Locking;数据库的隔离级别定义 (Isolation Level);
有了这些知识点,再加上适当的实战经验,全面掌控MySQL/InnoDB的加锁规则,当不在话下。 转载请保留固定链接: https://linuxeye.com/database/1986.html |