MySQl 加锁分析（转）
本文，就 MySQL/InnoDB 的加锁问题，展开分析。MySQL 支持多种存储引擎，不同的存储引擎特性不同，下面的所有介绍，都是基于 InnoDB 存储引擎。
InnoDB 实现的是基于多版本的并发控制协议 —— 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 ?;
一条简单 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会加哪些锁？
组合一：id列是主键，RC隔离级别
id 是主键，Read Committed 隔离级别，给定SQL：
delete from t1 where id = 10;
只需要将主键上，id=10 的记录加上X锁即可。
组合二：id列是唯一索引，RC隔离级别
id 是唯一索引，主键是 name 列，给定SQL：
delete from t1 where id = 10;
由于 id 是唯一索引，因此 delete 语句会选择 id 列的索引进行 where 条件的过滤，在找到 id=10 的记录后，首先会将唯一索引上的 id=10 索引记录加上X锁，同时，会根据读取到的 name 列（主键），将主键索引（聚簇索引）上的 name='d' 对应的主键索引项加X锁。
为什么聚簇索引上的记录也要加锁？
如果并发的一个SQL，是通过主键索引来更新：
update t1 set id = 100 where name = 'd';
此时，如果 delete 语句没有将主键索引上的记录加锁，那么并发的 update 就会感知不到 delete 语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。
组合三：id列是非唯一索引，RC隔离级别
id列索引不再是唯一索引，，给定SQL：
delete from t1 where id = 10;
满足 id=10 查询条件的记录，均已加X锁。同时，这些记录对应的主键索引上的记录也都加上了X锁。组合二唯一的区别在于，组合二最多只有一个满足查询条件的记录，而组合三会将所有满足查询条件的记录都加锁。
组合四：id列无索引，RC隔离级别
这个过滤条件，没法通过索引进行过滤，那么只能通过全表扫描做过滤，会对所有记录加上X锁（不是加表锁，也不是对满足条件的记录加锁）。
即全表扫描时，由于 id 列上没有索引，只能走聚簇索引，进行全部扫描。聚簇索引上所有的记录，都被加上了 X锁。
为什么不是只在满足条件的记录上加锁呢？
这是由于 MySQL 的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由 MySQL Server 层进行过滤。因此也就把所有的记录，都锁上了。
在实际的实现中，MySQL 有一些改进，在 MySQL Server 过滤条件，发现不满足后，会调用 unlock_row 方法，把不满足条件的记录释放锁 （违背了2PL的约束）。这样做保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。*
组合五：id列是主键，RR隔离级别
上面的四个组合，都是在 Read Committed 隔离级别下的加锁行为，接下来分析在 Repeatable Read 隔离级别下的加锁行为。对于 SQL：
delete from t1 where id = 10;
加锁情况与组合一：[id列是主键，RC隔离级别]一致。
组合六：id列是唯一索引，RR隔离级别
加锁情况与组合二：[id列是唯一索引，RC隔离级别]一致。
组合七：id列是非唯一索引，RR隔离级别
RC隔离级别允许幻读，RR隔离级别下不允许幻读，如何防止幻读呢？对于 SQL：
delete from t1 where id = 10;
加锁情况与组合三：[id列是非唯一索引，RC隔离级别]最大的区别是多了GAP锁，GAP锁是在两条记录之间的。
GAP锁有什么用？
GAP锁锁住的位置，不是记录本身，而是两条记录之间的GAP。所谓幻读，就是同一个事务，连续做两次当前读（例如：select * from t1 where id = 10 for update;），那么这两次当前读返回的是完全相同的记录 （记录数量一致，记录本身也一致），第二次的当前读，不会比第一次返回更多的记录。
如何保证两次当前读返回一致的记录，需要在第一次当前读与第二次当前读之间，保证其他的事务不会插入新的满足条件的记录并提交。GAP锁就是为了实现这个功能。
如上图所示，考虑到B+树索引的有序性，有以下位置可以插入新的满足条件的项（id=10）：[6,c]与[10,b]间、[10,b]与[10,d]间、[10,d]与[11,f]。因此，为了这些区间不会插入新的满足条件的记录，使用GAP锁，将这三个GAP给锁起来。
如果此时 insert 插入一条[10,aa]记录，首先会定位到[6,c]与[10,b]间，然后在插入前，会检查这个GAP是否已经被锁上，如果被锁上，insert 不能插入记录。
既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加GAP锁呢？
GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id 列是主键；组合六，id 列是唯一键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，不存在GAP。
组合八：id列无索引，RR隔离级别
同样的这个过滤条件，没法通过索引进行过滤，那么只能通过全表扫描做过滤。最终的加锁情况，如下图所示：
可以看到，首先，聚簇索引上的所有记录，都被加上了X锁。其次，聚簇索引每条记录间的GAP，都被加上了GAP锁。这个示例表，只有6条记录，一共需要6个X锁，7个GAP锁。试想，如果表上有1000万条记录，会是非常恐怖的情况。
在这种情况下，这个表上，除了不加锁的快照读，其他任何加锁的并发 SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。
与组合四：[id列无索引, Read Committed]类似，MySQL 做了一些优化，对于不满足查询条件的记录，MySQL 会提前放锁。
组合九：Serializable隔离级别
在 Serializable 隔离级别，对于SQL2：
delete from t1 where id = 10;
与 Repeatable Read 隔离级别完全一致
而受到影响的是SQL1：
select * from t1 where id = 10;
在RC，RR隔离级别下，都是快照读，不加锁。但是在 Serializable 隔离级别，SQL1会加读锁，也就是说快照读不复存在，MVCC并发控制降级为 Lock-Based CC（基于锁的并发控制）。
在 MySQL/InnoDB 中，所谓的读不加锁，并不适用于所有的情况，而是与隔离级别相关的。Serializable 隔离级别，读不加锁就不再成立，所有的读操作，都是当前读。
一条复杂 SQL 的加锁分析
id 作为主键，有联合索引 idx_t1_pu（时间戳，UserId），SQL如下：
delete from t1
where pubtime > 1 and pubtime < 20 and userid='hdc' and comment is not null;
假定在 Repeatable Read 隔离级别下，SQL使用的是 idx_t1_pu 索引，首先分析 SQL where 构成
Index key：
pubtime > 1 and puptime < 20。 在 idx_t1_pu 索引上的查询范围。
Index Filter：
userid = 'hdc' 。在 idx_t1_pu 索引上进行过滤。
Table Filter：
comment is not NULL。在 idx_t1_pu 索引上无法过滤，只能在聚簇索引上过滤。
再来看看这条SQL的加锁情况 (RR隔离级别)
由 Index Key 所确定的范围，被加上了GAP锁。Index Filter 锁给定的条件何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown，在MySQL Server层过滤，在5.6后支持了ICP，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁（图中，用红色箭头标出的X锁）。Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要X锁。
最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。
在Repeatable Read隔离级别下，针对一个复杂的SQL，首先需要提取其where条件。Index Key确定的范围，需要加上GAP锁；Index Filter过滤条件，根据MySQL版本是否支持ICP，加X锁；Table Filter过滤条件，无论是否满足，都需要加X锁。
死锁原理与分析
理解MySQL如何加锁，有两个比较重要的作用：
写出不会发生死锁的SQL；定位出线上产生死锁的原因；
两个死锁的例子
1 两个Session的两条SQL产生死锁
非常好理解，也是最常见的死锁，每个事务执行两条SQL，分别持有了一把锁，然后加另一把锁，产生死锁。
2 两个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间是否存在以相反的顺序加锁的情况，就可以分析出各种潜在的死锁情况，也可以分析出线上死锁发生的原因。
About The Author
