MySQL 加锁处理分析
1.1 MVCC：Snapshot Read vs Current Read
1.2 Cluster Index：聚簇索引
1.3 2PL：Two-Phase Locking
1.4 Isolation Level
2 一条简单SQL的加锁实现分析
2.1 组合一：id主键+RC
2.2 组合二：id唯一索引+RC
2.3 组合三：id非唯一索引+RC
2.4 组合四：id无索引+RC
2.5 组合五：id主键+RR
2.6 组合六：id唯一索引+RR
2.7 组合七：id非唯一索引+RR
2.8 组合八：id无索引+RR 1
2.9 组合九：Serializable 1
3 一条复杂的SQL 1
4 死锁原理与分析
5 总结
==================================================================
- 背景
MySQL/InnoDB的加锁分析，一直是一个比较困难的话题。我在工作过程中，经常会有同事咨询这方面的问题。同时，微博上也经常会收到MySQL锁相关的私信，让我帮助解决一些死锁的问题。本文，准备就MySQL/InnoDB的加锁问题，展开较为深入的分析与讨论，主要是介绍一种思路，运用此思路，拿到任何一条SQL语句，都能完整的分析出这条语句会加什么锁？会有什么样的使用风险？甚至是分析线上的一个死锁场景，了解死锁产生的原因。
注：MySQL是一个支持插件式存储引擎的数据库系统。本文下面的所有介绍，都是基于InnoDB存储引擎，其他引擎的表现，会有较大的区别。
- MVCC：Snapshot Read vs Current Read
MySQL InnoDB存储引擎，实现的是基于多版本的并发控制协议——MVCC (Multi-Version Concurrency Control) (注：与MVCC相对的，是基于锁的并发控制，Lock-Based Concurrency Control)。MVCC最大的好处，相信也是耳熟能详：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突是非常重要的，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的RDBMS，都支持了MVCC。
在MVCC并发控制中，读操作可以分成两类：快照读 (snapshot read)与当前读 (current read)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
在一个支持MVCC并发控制的系统中，哪些读操作是快照读？哪些操作又是当前读呢？以MySQL InnoDB为例：
- 快照读：简单的select操作，属于快照读，不加锁。(当然，也有例外，下面会分析)
- select * from table where ?;
- 当前读：特殊的读操作，插入/更新/删除操作，属于当前读，需要加锁。
- select * from table where ? lock in share mode;
- select * from table where ? for update;
- insert into table values (…);
- update table set ? where ?;
- delete from table where ?;
所有以上的语句，都属于当前读，读取记录的最新版本。并且，读取之后，还需要保证其他并发事务不能修改当前记录，对读取记录加锁。其中，除了第一条语句，对读取记录加S锁 (共享锁)外，其他的操作，都加的是X锁 (排它锁)。
为什么将 插入/更新/删除 操作，都归为当前读？可以看看下面这个 更新 操作，在数据库中的执行流程：
从图中，可以看到，一个Update操作的具体流程。当Update SQL被发给MySQL后，MySQL Server会根据where条件，读取第一条满足条件的记录，然后InnoDB引擎会将第一条记录返回，并加锁 (current read)。待MySQL Server收到这条加锁的记录之后，会再发起一个Update请求，更新这条记录。一条记录操作完成，再读取下一条记录，直至没有满足条件的记录为止。因此，Update操作内部，就包含了一个当前读。同理，Delete操作也一样。Insert操作会稍微有些不同，简单来说，就是Insert操作可能会触发Unique Key的冲突检查，也会进行一个当前读。
注：根据上图的交互，针对一条当前读的SQL语句，InnoDB与MySQL Server的交互，是一条一条进行的，因此，加锁也是一条一条进行的。先对一条满足条件的记录加锁，返回给MySQL Server，做一些DML操作；然后在读取下一条加锁，直至读取完毕。
- Cluster Index：聚簇索引
InnoDB存储引擎的数据组织方式，是聚簇索引表：完整的记录，存储在主键索引中，通过主键索引，就可以获取记录所有的列。关于聚簇索引表的组织方式，可以参考MySQL的官方文档：Clustered and Secondary Indexes 。本文假设读者对这个，已经有了一定的认识，就不再做具体的介绍。接下来的部分，主键索引/聚簇索引 两个名称，会有一些混用，望读者知晓。
- 2PL：Two-Phase Locking
传统RDBMS加锁的一个原则，就是2PL (二阶段锁)：Two-Phase Locking。相对而言，2PL比较容易理解，说的是锁操作分为两个阶段：加锁阶段与解锁阶段，并且保证加锁阶段与解锁阶段不相交。下面，仍旧以MySQL为例，来简单看看2PL在MySQL中的实现。
从上图可以看出，2PL就是将加锁/解锁分为两个完全不相交的阶段。加锁阶段：只加锁，不放锁。解锁阶段：只放锁，不加锁。
- Isolation Level
隔离级别：Isolation Level，也是RDBMS的一个关键特性。相信对数据库有所了解的朋友，对于4种隔离级别：Read Uncommited，Read Committed，Repeatable Read，Serializable，都有了深入的认识。本文不打算讨论数据库理论中，是如何定义这4种隔离级别的含义的，而是跟大家介绍一下MySQL/InnoDB是如何定义这4种隔离级别的。
MySQL/InnoDB定义的4种隔离级别：
- Read Uncommited
可以读取未提交记录。此隔离级别，不会使用，忽略。
- Read Committed (RC)
快照读忽略，本文不考虑。
针对当前读，RC隔离级别保证对读取到的记录加锁 (记录锁)，存在幻读现象。
- Repeatable Read (RR)
快照读忽略，本文不考虑。
针对当前读，RR隔离级别保证对读取到的记录加锁 (记录锁)，同时保证对读取的范围加锁，新的满足查询条件的记录不能够插入 (间隙锁)，不存在幻读现象。
- Serializable
从MVCC并发控制退化为基于锁的并发控制。不区别快照读与当前读，所有的读操作均为当前读，读加读锁 (S锁)，写加写锁 (X锁)。
Serializable隔离级别下，读写冲突，因此并发度急剧下降，在MySQL/InnoDB下不建议使用。
- 一条简单SQL的加锁实现分析
在介绍完一些背景知识之后，本文接下来将选择几个有代表性的例子，来详细分析MySQL的加锁处理。当然，还是从最简单的例子说起。经常有朋友发给我一个SQL，然后问我，这个SQL加什么锁？就如同下面两条简单的SQL，他们加什么锁？
- SQL1：select * from t1 where id = 10;
- SQL2：delete from t1 where id = 10;
针对这个问题，该怎么回答？我能想象到的一个答案是：
- SQL1：不加锁。因为MySQL是使用多版本并发控制的，读不加锁。
- SQL2：对id = 10的记录加写锁 (走主键索引)。
这个答案对吗？说不上来。即可能是正确的，也有可能是错误的，已知条件不足，这个问题没有答案。如果让我来回答这个问题，我必须还要知道以下的一些前提，前提不同，我能给出的答案也就不同。要回答这个问题，还缺少哪些前提条件？
- 前提一：id列是不是主键？
- 前提二：当前系统的隔离级别是什么？
- 前提三：id列如果不是主键，那么id列上有索引吗？
- 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
- 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？
没有这些前提，直接就给定一条SQL，然后问这个SQL会加什么锁，都是很业余的表现。而当这些问题有了明确的答案之后，给定的SQL会加什么锁，也就一目了然。下面，我将这些问题的答案进行组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁？
注：下面的这些组合，我做了一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤 (索引扫描)。但实际情况会复杂很多，真正的执行计划，还是需要根据MySQL输出的为准。
- 组合一：id列是主键，RC隔离级别
- 组合二：id列是二级唯一索引，RC隔离级别
- 组合三：id列是二级非唯一索引，RC隔离级别
- 组合四：id列上没有索引，RC隔离级别
- 组合五：id列是主键，RR隔离级别
- 组合六：id列是二级唯一索引，RR隔离级别
- 组合七：id列是二级非唯一索引，RR隔离级别
- 组合八：id列上没有索引，RR隔离级别
- 组合九：Serializable隔离级别
排列组合还没有列举完全，但是看起来，已经很多了。真的有必要这么复杂吗？事实上，要分析加锁，就是需要这么复杂。但是从另一个角度来说，只要你选定了一种组合，SQL需要加哪些锁，其实也就确定了。接下来，就让我们来逐个分析这9种组合下的SQL加锁策略。
注：在前面八种组合下，也就是RC，RR隔离级别下，SQL1：select操作均不加锁，采用的是快照读，因此在下面的讨论中就忽略了，主要讨论SQL2：delete操作的加锁。
- 组合一：id主键+RC
这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id = 10的记录加上X锁即可。如下图所示：
结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。
- 组合二：id唯一索引+RC
这个组合，id不是主键，而是一个Unique的二级索引键值。那么在RC隔离级别下，delete from t1 where id = 10; 需要加什么锁呢？见下图：
此组合中，id是unique索引，而主键是name列。此时，加锁的情况由于组合一有所不同。由于id是unique索引，因此delete语句会选择走id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique索引上的id=10索引记录加上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’,id=10]的记录。
- 组合三：id非唯一索引+RC
相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图：
根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。
结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
- 组合四：id无索引+RC
相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10;的记录加上X锁。那么实际情况呢？请看下图：
由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。
有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。
注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。
结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了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非唯一索引+RR
还记得前面提到的MySQL的四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。但是在组合五、组合六中，加锁行为又是与RC下的加锁行为完全一致。那么RR隔离级别下，如何防止幻读呢？问题的答案，就在组合七中揭晓。
组合七，Repeatable Read隔离级别，id上有一个非唯一索引，执行delete from t1 where id = 10; 假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？同样看下面这幅图：
此图，相对于组合三：[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]之前，不会插入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; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：
如图，这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的间隙(GAP)，也同时被加上了GAP锁。这个示例表，只有6条记录，一共需要6个记录锁，7个GAP锁。试想，如果表上有1000万条记录呢？
在这种情况下，这个表上，除了不加锁的快照度，其他任何加锁的并发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，会加什么锁？假定在Repeatable Read隔离级别下 (Read Committed隔离级别下的加锁情况，留给读者分析。)，同时，假设SQL走的是idx_t1_pu索引。
在详细分析这条SQL的加锁情况前，还需要有一个知识储备，那就是一个SQL中的where条件如何拆分？具体的介绍，建议阅读我之前的一篇文章：SQL中的where条件，在数据库中提取与应用浅析 。在这里，我直接给出分析后的结果：
- Index key：pubtime > 1 and puptime < 20。此条件，用于确定SQL在idx_t1_pu索引上的查询范围。
- Index Filter：userid = ‘hdc’ 。此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index Key。
- Table Filter：comment is not NULL。此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。
在分析出SQL where条件的构成之后，再来看看这条SQL的加锁情况 (RR隔离级别)，如下图所示：
从图中可以看出，在Repeatable Read隔离级别下，由Index Key所确定的范围，被加上了GAP锁；Index Filter锁给定的条件 (userid = ‘hdc’)何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。
结论：在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录，不加X锁，否则需要X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。
- 死锁原理与分析
本文前面的部分，基本上已经涵盖了MySQL/InnoDB所有的加锁规则。深入理解MySQL如何加锁，有两个比较重要的作用：
- 可以根据MySQL的加锁规则，写出不会发生死锁的SQL；
- 可以根据MySQL的加锁规则，定位出线上产生死锁的原因；
下面，来看看两个死锁的例子 (一个是两个Session的两条SQL产生死锁；另一个是两个Session的一条SQL，产生死锁)：
上面的两个死锁用例。第一个非常好理解，也是最常见的死锁，每个事务执行两条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)；
- 了解SQL本身的执行计划 (主键扫描 vs 唯一键扫描 vs 范围扫描 vs 全表扫描)；
- 了解数据库本身的一些实现细节 (过滤条件提取；Index Condition Pushdown；Semi-Consistent Read)；
- 了解死锁产生的原因及分析的方法 (加锁顺序不一致；分析每个SQL的加锁顺序)
有了这些知识点，再加上适当的实战经验，全面掌控MySQL/InnoDB的加锁规则，当不在话下。
***********************************************************
评论部分***********************************************************
何大师，咨询个优化器的问题。select * from a,b group by a.name是先驱动表group by再join，还是先join再group by？我看几乎所有执行计划里都是前者，profile也看不出什么。何大师麻烦从源码角度解释下！多谢了！
是否因为group后再join，能够降低中间结果数量？
难得的数据库技术文章，对Mysql innodb事务与锁介绍的全面透彻。
目前还用不上，但是介意我转载下么？以后学习可能有用
转载，请标明下出处吧。
讲的很通透，借用来给科普下，
深入潜出，nice!
可以针对insert专门出一篇，顺便介绍下隐式缩
这个建议，可以考虑。
求大师 出一篇insert加锁的文章
当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。
mysql正常关闭时，undo的脏块会写入datafile，undo会被释放吧？重启后，数据第一次被读取到buffer，此时应该是从datafile直接读取，应该为当前读吧？上锁？
可能我理解有误，请登成师解读下！最新版本，必须保证是已经提交的一致版本，你说的情况，如果事物已提交，就读取。如果事务未提交，则通过undo回滚。
假如已经提交，那么我觉得应该是当前读，这应该不上锁。
“在读多些少的OLTP应用中” => “在读多写少的OLTP应用中”
谢谢指正。
请问聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。这个锁的效果和表锁有什么区别？
rc隔离级别下，有区别，记录仍旧可以插入。rr下，功能上无区别。但是innodb不会主动升级表锁。
有个select的问题，select * from t1 where id=1 for update。这种情况对于组合1的加锁情况是怎样的？
有个select的问题，select * from t1 where id>=1 and id <=10 for update。这种情况对于组合1的加锁情况是怎样的？
在rc级别下,由于没有间隙锁,因此可以插入.
在rr级别下,由于有间隙锁,因此不能插入.
对吧?
但是什么时候,会触发表锁呢?
非常感谢你的分享，受益匪浅：） 我们也用MYSQL，只是简单的insert ，select，但数据量大，30T数据。只用myisam存的。
何大师留的思考题，测试过了，select for update 使用排他锁时，gap 锁是会生效的。
有一个关于死锁检测的问题，想请教一下 。
我在Mac Os 10.8版本下用了mysql Ver 14.14 Distrib 5.1.71, for apple-darwin12.0.0 (i386）这个版本的mysql做了一个死锁检测实验。表结构如下:
+——-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——-+————-+——+—–+———+——-+
| id | int(11) | NO | PRI | NULL | |
| name | varchar(10) | NO | | NULL | |
+——-+————-+——+—–+———+——-+我的事务隔离级别是repeatable-read.
现在表里面有两条记录：
+—-+——+
| id | name |
+—-+——+
| 1 | new |
| 4 | new |
+—-+——+—————-实验1:——————————-
事务1：
begin;
select * from t where id =1 for update;事务2：
begin;
update t set name =’d’ where id =4 ;事务1：
update t set name=’d’ where id= 4;
(被Hold住）
事务2：
update t set name =’d’ where id =1;事务1 此时被检测到死锁，被重启事务了。
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction但是我做了另外一个测试：
—————–实验2————————
事务1：
begin
select * from t where id =1 for update
事务2：
begin
select * from t where id =4 for update;事务1：
update t set name =’d’ where id =4;
（被阻塞了）
事务2：
update t set name =’d’ where id =1;
ERROR 1213 (40001): Deadlock …. （后面错误省略）我想问的是，两个测试，mysql都检测到了死锁，为什么实验1中由事务2触发死锁，重启的是事务1；
但是实验2 中，事务2触发死锁，重启的却是事务2。 mysql在检测到死锁以后，重启的事务的依据是什么呢？
有什么好的死锁检测工具能推荐一下么？简单来说，mysql的死锁检测到之后，会选择一个事务进行回滚。而选择的依据：看哪个事务的权重最小，事务权重的计算方法：事务加的锁最少；事务写的日志最少；事务开启的时间最晚。实验1，事务2写了日志，事务1没有，回滚事务1。实验2，都没写日志，但是事务1开始的早，回滚事务2。
死锁检测工具，目前我也不知道有什么好工具。
事务被重启的操作，会报给应用程序吗？
谢谢回复，还会一如既往的关注你的技术文章的。
看了你的分析，对MySQL加锁的知识又有了新的认识。非常感谢。
第二种死锁情况，为何不锁[20,100]?
最后总结处的知识点，能否给出一些参考链接或书籍，供深入学习，谢谢！[20,100]也要加锁的，只是跟死锁无关，因此忽略了。
我考虑，添加一些参考资料，不过最好的资料，还是MySQL的源码。
非常好
分析很详细，学习了。
最近刚好碰到了一个死锁问题，看了您这篇文章，收获颇多
但我碰到死锁的表是用的联合主键，想问下mysql的联合主键是如何建立索引的？
如果是建立两个非唯一索引，然后在MySQL Server层面验证主键唯一性的话，似乎无法解释我遇到的死锁问题
求教mysql联合主键的索引机制，谢谢啦！联合主键，跟单一主键一模一样，没有区别。
不理解联合主键和单一主键一模一样啥意思，具体问题简化如下：
表结构为：
CREAT TABLE ‘t_gs_config’ {
‘serverId” int(11),
‘activityId’ int(11),
‘name’ varchar(255),
PRIMARY KEY(`serverId`, `activityId`)
} ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 ;
用的是联合主键serverId和activityId。
猜测当时场景，表t_gs_config的内容为
serverId activityId name
41 40 s11
42 40 s22
43 40 s13
75 45 s75
76 45 s76
77 45 s77
session1:
delete from t_gs_config where activityId=37;
session2:
insert into t_gs_config (name, activityId, serverId) values (‘s70’, 44, 70);
insert into t_gs_config (name, activityId, serverId) values (‘s71’, 44, 71);
insert into t_gs_config (name, activityId, serverId) values (‘s72’, 44, 72);我的理解是，在RR情况下
session1会在activityId的索引表上加一个gap锁，session2会在activityId和serverId的索引表上分别加一个gap锁，不会发生死锁啊
但实际情况是，死锁了。。。回滚了session1一模一样的意思，就是都是只有一个主键索引。只是一个主键只有一个列，一个有两个列。基本知识，建议先学习些数据库基础。
确实对数据库基础知识不熟悉，囧
联合主键只有一个主键索引
所以session1中的delete操作的约束条件只有一列，所以相当于组合8的情况，无索引，走的全表扫描，session2中的insert操作把包含了联合主键两列数据，所以相当于组合5的情况，依次加gap锁
这样就能解释通了。。。谢谢啦！呵呵，这就是我这篇文章期待达到的效果。
应该不会死锁吧，我做实验了，只会锁等待
什么测试？针对什么场景？
用的是和benpaorun一样的数据做的，执行sql的顺序也是一样的，发现只会锁等待。
按照博文来的分析的话，我觉得锁等待也比较合理
您好， 看了您的文章后，我立即分析了一下我遭遇到的死锁， 想请教您一下。
Isolation-level: Read-Committed
Table t(id primary key, status key, uuid)SQL:
update t set uuid=”uuid” where status = 1 limit 50;这条sql 是多进程执行的， 也就是可能有两个 worker 在同时执行这条 SQL. 根据您的讲解， 因为status 不是 unique key ，所以会针对 status =1 的所有记录全部加锁, 并且 对应的 id 也会全部锁上； 如果有两个进程在同时操作，这就会导致 deadlock 吗？ 两个进程加锁的顺序应当是一致的啊， 也会出现死锁吗？ 还是有可能不一致？
另外，这里的 limit 50 应当和锁没有关系吧，还是说只找 50 条进行加锁呢？
仅仅是这一条sql，加锁顺序是一致的，不会产生死锁。除非还有其他sql参与。
还需要关注一下，这条sql走的是索引，还是全表扫描？
应当是 索引吧， status 字段是有索引的，这样不会全表扫描吧。
另外， 这里有 limit 50 是锁 50 条还是满足条件的都锁上呢？
有索引，没说就不会走全表扫描。有执行计划，有查询优化，有代价估算。
delete from table1 where id in (1,2,3,4,5) ，id是主键，RR级别，这样会加GAP锁吗
你可以把这条sql，拆为5条id = 的sql来分析。
在RC隔离级别下，索引表是否可以不加锁，而是在对数据表执行操作的时候执行double check，比如delete from t1 where id = 10; 在操作数据表的时候，检查索引列id是否还是10，如果不是就跳过
深入浅出，讲得特别好，继续关注博主的博客和微博
组合七：id非唯一索引+RR
比如一个Session A执行了delete from t1 where id = 10;会加两个锁，X锁和Gap锁
Session B要执行什么样的sql？？才可以在Innodb_locks查看到Gap锁，还是说Gap锁在Innodb_locks就是表现为X锁？试过几种sql:
delete from t1 where id =10;
delete from t1 where name =’b’;
update t1 set id =111 where name =’b’;请指教，谢谢！
http://www.mysqlperformanceblog.com/2012/03/27/innodbs-gap-locks/
看完这个post知道了
Session B中执行insert into t1(name,id) values(‘ddd’,6);
可以查看到Gap锁建议解释的时候能举个具体的sql例子，这样有图看文章还可以自己测试一把（对于不是很懂得人很有帮助），这样可以更好理解
gap锁不是x锁，是两种不同的类型。关于你给的这个链接，组合七就很好的解释了这个问题。
gap锁的功能，就是锁住此锁对应的区域，不可新插入数据。当然，你的建议不错，以后会注意新加一些例子。
只是个人建议，你文章写得很好，思路非常清晰，看着就想动手试试，看看效果
对应这个Gap锁有一点不是很明白，比如举得的例子，删除Id【等于】10，那为什么要锁住相邻的区域。
你列举的数据，是不能插入id为6到11的数据，不在这个范围可以正常插入。
在删除【等于】的情况下，Gap锁的区域范围是怎么限定了？是相邻的两个数（【6】，【11】）之间就不让插入？删除【大于】【小于】10锁定范围还是很好理解。
gap锁的目的，只要把握一点：就是让后续不能插入满足条件的新纪录，然后按照这个点，去考虑哪些地方需要加gap锁。
能认真写这么多文字出来 还这么专业 不容易！！！！ 没有几个小时搞不定，学mysql的国人有福气啊
关于组合七最后留的那个问题，（流火工程师）来解答下，此时会对不存在的间隙加上gap锁，比如表中已有id=1,2,3,6，那么select where id=4 for update会锁住(3,6)之间所有的间隙包括小数如3.1,3.2,5.999等，并且在show innodb status里面显示锁住的行为hex 80000006，只显示这一行信息。其他间隙如7,8,9不会锁的。
锁住间隙是对的，但是表述上有所不当。其实间隙是一个连续范围，例如你这里说的(3, 6)范围，就是一个连续范围，这个范围中的所有插入，均被禁止了。
- 2013年个人微博推荐技术资料汇总 | 何登成的技术博客12月 26th, 201316:42
弱弱地问一句，我看的书里面都说的是RR隔离级别不允许脏读和不可重复读，但是可以幻读，怎么和作者说的不一样呢？
你说的没错，因此我在文章一开始，就强调了这一点。mysql innodb引擎的实现，跟标准有所不同。
这正是我这两天最大的困惑！
一般大家提到Serializable才会来解决幻读的问题，RR只是解决不可重复读+脏读的问题。或者说，很多描述下，不可重复读有时候只是针对某条已经存在的记录，有时候是针对某个给定的条件。这么一看，确实明白了，Innodb的实现，与ISO定义的隔离级别及其解决问题的范围，是不一致的！嗯，是的。InnoDB在这个处理上，比较奇葩…
这问题应该算“奇葩”，还是算innodb做得更好？或者是对于“不可重复读”的理解和实现不一致？当然我不知道我理解的四层隔离级别及其对应处理的问题，是否和ANSI/ISO定义的是一致（希望回答一下）。我看其他博客中解释mysql（应该就是指InnoDB）的RR，解决了“幻读”问题。
既然RR解决了“幻读”的问题，那么在mysql中，Serializable级别，应该解决哪些其他的问题呢？
谢谢。
https://dev.mysql.com/doc/refman/5.6/en/set-transaction.html#isolevel_repeatable-read
For locking reads (SELECT with FOR UPDATE or LOCK IN SHARE MODE), UPDATE, and DELETE statements, locking depends on whether the statement uses a unique index with a unique search condition, or a range-type search condition. For a unique index with a unique search condition, InnoDB locks only the index record found, not the gap before it. For other search conditions, InnoDB locks the index range scanned, using gap locks or next-key locks to block insertions by other sessions into the gaps covered by the range.
官方的解释，RR级别时，如果查询是个范围，会加 gap locks 或者 next-key。 但是我不太确定是否会有例外，仍然不能避免幻读。
- (转)MySQL 加锁处理分析 | 幽静的麦田_临沂php程序员12月 27th, 201314:27
您好，能不能介绍一下 insert into … select…from… 中 select 的 加锁方式？
insert into … select … from …中的select，会对表加读锁(更确切的说，应该是对表中所有的记录加读锁)，因此，select的操作，是一个当前读。
这个为什么要锁所有记录呢？不是可以MVCC实现多版本读吗？
问题：如果组合五、组合六下，针对SQL：select * from t1 where id = 10 for update; 第一次查询，没有找到满足查询条件的记录，那么GAP锁是否还能够省略？
这个问题我在RR隔离级别下试过，感觉很奇怪，事务1执行select * from t1 where id = 10 for update; 后，事务2就不能插入insert id为10 的记录，说明是加了GAP锁，但是事务2也可以执行 select * from t1 where id = 10 for update;，这个时候事务1和事务2貌似都有id=10的GAP锁，2个事务都不能执行insert id=10的记录，会报死锁异常。给我的感觉就像是此时拿到的GAP锁不是一个排他锁，像是一个共享锁。作者能不能详细说明下这块。gap锁本身的作用是防止后续的插入操作，因此gap锁只跟插入相冲突，gap锁之间不冲突，就会发生你这提到的情况。
我下去针对GAP锁冲突做了如下测试，感觉GAP锁之间是有冲突，测试情况如下：
>>表article数据如下：
+——-+————-+
| id | name |
+——-+————-+
| 1 | title1 |
+——-+————-+
| 2 | title2 |
+——-+————-+
| 3 | title3 |
+——-+————-+
| 9 | title9 |
+——-+————-+
| 10 | title10 |
+——-+————-+分别开2个mysql窗口模拟2个事务，设置autocommit=0，然后分别执行如下语句：
select * from article where [] for update;
+—————————-+——————-+——————-+——————-+————————-+
| | id=6 | id6 | id>5 and id<7 |
7>+—————————-+——————-+——————-+——————-+————————-+
| id=6 | 不冲突 | 不冲突 | 不冲突 | 不冲突 |
+—————————-+——————-+——————-+——————-+————————-+
| id6 | 不冲突 | 冲突 | 冲突 | 冲突 |
+—————————-+——————-+——————-+——————-+————————-+
| id>5 and id5 and id<7的时候也是加GAP锁，但是二个事务就会冲突，
7>这是什么原因？上面的内容被转码了，直接看图片吧，http://pan.baidu.com/s/1i34xAst
你这个问题真的很好，也指出了我文中的一点小问题。
按照原理来说，id>5 and id<7这个查询条件，在表中找不到满足条件的项，因此会对第一个不满足条件的项(id = 9)上加GAP锁，防止后续其他事务插入满足条件的记录。
而GAP锁与GAP锁是不冲突的，那么为什么两个同时执行id>5 and id<7查询的事务会冲突呢？
原因在于，MySQL Server并没有将id<7这个查询条件下降到InnoDB引擎层，因此InnoDB看到的查询，是id>5，正向扫描。读出的记录id=9，先加上next key锁(Lock X + GAP lock)，然后返回给MySQL Server进行判断。
MySQL Server此时才会判断返回的记录是否满足id<7的查询条件。此处不满足，查询结束。因此，id=9记录上，真正持有的锁是next key锁，而next key锁之间是相互冲突的，这也说明了为什么两个id>5 and id<7查询的事务会冲突的原因。
id>6为何会与id<6冲突，一个间隙为(3, positive infinity)一个为(negative infinity, 9)；但是间隙锁不会冲突的啊
看我给ontheway的回复。查询过程中，不是只加间隙锁，而是加next key，next key是会冲突的。
关于MySQL的锁有些地方没搞清楚
1. 意向锁的作用
SELECT … FOR UPDATE 会锁住选中的行，和直接对那些行加X锁有什么区别？
2. S和X锁什么时候加？
在RR级别下的SELECT好像不会加S锁，但是会加X锁。那什么时候会加S锁呢？补充一下，在网上找到一些资料说Intention lock 是表级锁，但是我这边试下来，在RR级别下是next key lock，并不会锁住整张表。
谁说Intention Lock是表级锁？谁写的？拉出去突突了。
Intention Lock是用在哪个级别上的？
看你这些问题，我真不知道如何回答，感觉你对锁的理解，比较混乱。至于第二点，select lock in share mode; 会加S锁。
我理解的select lock in share mode会加IS锁，select for update会加IX锁。
所以我没搞清楚IX锁和X锁，他们的区别在哪里。都是对一些记录加锁。建议去看一些锁相关的基础文章，你这些理解，都是有问题的。
登博，帮你搞了一个净版的pdf，见：http://yun.baidu.com/share/link?shareid=3229086908&uk=4146200807#dir/path=%2F%E5%85%A8%E5%B1%80%E5%85%B1%E4%BA%AB
- 发表于 2016-11-03 13:25
- 阅读 ( 483 )
