MySQL锁

锁介绍

无论何时，只要有多个查询需要在同一时刻修改数据，都会产生并发控制的问题，而这种并发问题的直接后果就是数据不一致问题，那么如何解决这种问题呢？MySQL需要采用锁机制来解决。

• 按照锁的粒度来说，mySQL主要包含三种类型（级别）的锁定机制：全局锁、表级锁、行级锁。
• 全局锁和表级锁都是由MySQL的server层实现的，各存储引擎可以共用。
• 全局锁：锁的是整个database数据库，使用场景：数据备份。
• 表级锁：锁的是整个表
• 行级锁是由存储引擎层实现的，而且只有InnoDB和xtradb引擎支持，行级锁锁的是某N行数据。
• 按照锁的功能来说，分为：共享读锁和排他写锁。
• 除此之外，还有悲观锁（排他写锁）和乐观锁（使用某一版本列或者为唯一列进行逻辑控制）的定义。

MySQL中的InnoDB和MyISAM存储引擎最大的区别：InnoDB有事务的支持、行级锁的支持。

• 行级锁（row-level）：最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的，由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。

• 表级锁（table-level）：和行级锁定相反，表级锁的锁定是MySQL各存储引擎中最大颗粒度的锁定机制，该锁定机制最大特点是实现逻辑非常简单，所以获取锁和释放锁的速度较快，由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。当然，锁定颗粒度大所带来的最大负面影响是踹下锁定资源争用的概率会最高，即发生所冲突概率最大。

总的来说，MySQL这两种锁的特性可导致归纳如下：

• 表级锁：开销小，加锁快，不会出现死锁，锁定粒度大，发生锁冲突概率最高，并发度低。
• 行级锁：开销大，加锁慢，会出现死锁，锁定粒度小，发生锁冲突概率最低，并发度高。

行锁原理分析

接下来选择几个有代表性的例子，来详细分析MySQL的加锁处理，当然，还是从最简单的例子说起。

• SQL1：

select * from t1 where id = 10;

• SQL2:

delete from ti where id = 10;

针对这两个问题，该怎么回答？能想象到的一个答案是：

• SQL1：不加锁，因为MySQL是使用多版本并发控制的(MVCC)，读不加锁。
• SQL2：对id=10的记录加写锁（走主键索引）。

这个答案对吗？说不上来，可能是正确，也可能是错误的，已知条件不足，这个问题没有答案，必须还要知道以下一些前提，前提不同，能给出的答案也就不同，要回答这个问题，还缺少哪些前提条件？

• 前提一：id列是不是主键？
• 前提二：当前系统的隔离级别是多少？
• 前提三：id列如果不是主键，那么id列上有索引吗？
• 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
• 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描？

没有这些前提，直接就给定一条SQL，然后问这个SQL会加什么锁，都是很业余的表现，而当这些问题有了明确的答案之后，给定的SQL会加什么锁，也就一目了然，下面我们将这些问题的前提进行组合，然后从易到难的顺序，逐个分析每种组合下，SQL2会加什么锁。

注：下面的这些组合，需要做一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤（索引扫描），但是实际情况会复杂很多，真正的执行计划，还需要根据MySQL输出的为准。

• 组合一：id列是主键，RC隔离级别
• 组合二：id列是二级唯一索引，RC隔离级别
• 组合三：id列是二级非唯一索引，RC隔离级别
• 组合四：id列上没有索引，RC隔离级别
• 组合五：id列是主键，RR隔离级别
• 组合六：id列是二级唯一索引，RR隔离级别
• 组合七：id列是二级非唯一索引，RR隔离级别
• 组合八：id列上没有索引，RR隔离级别
• 组合九：Serializable隔离级别

组合一：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是唯一索引，因此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列是唯一索引，那么SQL需要加两个X锁，一个对应唯一索引上的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锁，既不是加表锁，也不是在满足条件的记录上加行锁。为什么不只是在满足条件的记录上加锁呢？这是由MySQL的实现决定的，如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤，因此也把所有的记录都锁上了。

注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录释放锁，这样做，保证了最后只会持有满足条件记录上的锁，达式每条记录的加锁操作还是不能省略的。

若id列上没有索引，SQL会走聚簇素银的全表扫描进行过滤，由于过滤是由MySQL Server层面进行的，因此每条记录无论是否满足条件，都会被加上X锁，但是为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后释放锁，最终持有的，是满足条件上的记录上的锁，但是不满足条件的记录上的加锁/释放锁动作不会省略，同时，优化也违背了ZPL的约束。

组合五：id主键+RR

id列是主键列，RR隔离级别，针对delete from t1 where id = 10这条SQL，加锁与id主键+RC是一样的

组合六：id唯一索引+RR

组合六的加锁与组合二：id唯一索引+RC是一样的，两个X锁，id唯一索引满足条件的记录上的一个，对应的聚簇索引上的记录一个。

组合七：id非唯一索引+RR

还记得前面提到的MySQL四种隔离级别的区别吗？RC隔离级别允许幻读，而RR隔离级别，不允许幻读，但是在组合五、组合六中，加锁的行为又是与RC下的加锁行为完全一致，那么RR隔离级别下，如何防止幻读呢？问题的答案就在组合七中揭晓。

组合七，RR隔离级别，id列上有一个非唯一索引，执行delete from t1 where id = 10，假设选择id列上的索引进行条件过滤，最后的加锁行为，是怎么样的呢？如下图：

相对于组合三看似相同，其实却又很大的区别，最大的区别在于，这幅图中多了一个GAP锁，而且GAP锁看起来也不是加在记录上的，倒像是加在两条记录之间的位置，GAP锁有何用？

其实这个多出来的GAP锁，就是RR隔离级别，相对于RC隔离级别，不出现幻读的关键，GAP锁住的位置也不是记录本身，而是两条记录之间的GAP，所谓防止幻读，就是同一个事务，连续两次当前读（例如：select * from t1 where id = 10 for update），那么这两次当前读返回的是完全相同的记录（记录数量一致，记录本身也一致），第二次当前读，不会比第一次返回更多的记录（幻象）。

何如保证两次当前读返回一致的记录，那就需要在第一次当前读与第二次当前读之间，其他的事务不会插入新的满足条件的记录并提交，为了实现这个功能，GAP锁应运而生。

如图中所示，考虑到B+树索引的有序性，满足条件的项一定是连续存放的，为了保证记录[6,c]与[10,b]间，[10,b]与[10,d]之间，[10,d]与[11,f]不会插入新的满足条件即id=10的记录，MySQL选择了用GAP锁，将这三个GAP锁起来。

insert操作，例如insert[10,a]，首先会定位到[6,c]与[10,b]之间，然后插入前，会检查这个GAP是否已经被锁上，如果被锁上，则insert不能插入记录。

有人可能会问了：既然防止幻读，需要靠GAP锁的保护，为什么组合五、组合六，也是RR隔离级别，却不需要加上GAP锁？

回答这个问题也很简单，GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况，而组合五:id是主键，组合六:id是唯一索引列，都能够保证唯一性，是一个等值查询，最多返回一条记录，而且新的相同取值的记录，一定不会再插入进来，因此也避免了GAP锁的使用。

针对此问题，还有一个更深入的问题：如果组合五、组合六下：针对select * from t1 where id = 10 fro update，第一次查询，没有找到满足查询条件的记录，那么GAP锁是否能够省略呢？当然是不能的，同样也会加上GAP锁的。

RR隔离级别下，id列上有一个非唯一索引，对应的SQL:delete from t1 where id = 10，首先id索引定位第一条满足条件的记录，加记录上的X锁，加GAP锁，然后加主键聚簇索引上的X锁，然后返回，读取下一条，重复进行，直至进行到第一条不满足条件的记录[11,f]，此时，不需要加记录X锁，但是仍然需要加GAP锁。最后返回结束。

组合八：id无索引+RR

id列上没有索引，此时加锁情况如下：

这是一个很恐怖额现像，首先聚簇索引上所有的记录，都被加上了X锁，其次，聚簇索引上每条记录之间都加上了GAP锁，这个示例表，只有6条记录，一共需要6个记录锁，即X锁，7个GAP锁，试想，如果表上有1000万条记录呢？

这种情况下，除了不加锁的快照读，其他任何加锁的并发SQL，均不能执行，不能更新，不能删除，不能插入，全表被锁死。

当然，跟组合四：id无索引+RC类似，这个情况下，MySQL也做了一些优化，就是所谓的semi-consistent read, semi-consistent read开启的情况下，对于不满足条件的记录，MySQL会提前释放锁，针对Consistent read如何触发：要么read commited隔离级别，要么Repeatable Read隔离级别，同时设置了innodb_locks_unsafe for binlog参数。

在RR隔离级别下，如果进行全表扫描的当前读，那么会锁上表中所有的记录，同时会锁上聚簇索引内所有GAP，杜绝所有的并发更新、删除、插入操作，当然也可以通过semi-consistent read，来缓解加锁的开销与并发影响，但是semi-consistent res本身也会带来其他的问题，不建议使用

组合九：Serializable

针对前面提到的简单SQL，最后一个情况：序列化隔离级别下，对于:delete from t1 where id = 10来说，序列化隔离级别与RR隔离级别加锁完全一样。

序列化隔离级别下，影响的是：select * from t1 where id = 10这条SQL，在RC，RR隔离级别下，都是快照读，不加锁，但是在序列化隔离级别下，也会加锁，也就是说快照读不复存在，MVCC并发控制降级为Lock-Based CC。

在MySQL/InnoDB中，所谓的读不加锁，并不适用于所有的情况，而是跟隔离级别相关，序列化隔离级别下读不加锁不再成立，所有的读操作，都是当前读。

复杂SQL分析

再来看一个稍微复杂的SQL，用于说明MySQL加锁的另外一个逻辑，SQL示例如下：

图中的SQL会加什么锁？假定在RR隔离级别下，假设SQL走的是idx_t1_pu索引。在详细分析这条SQL加锁情况之前，还需要有一个知识储备，那就是一个SQL中的where条件如何进行拆分，如下：

• Index key：pubtime > 1 and pubtime < 20，此条件，用于确定SQL在idx_t1_pu索引上的查询范围。
• Index fiter：userid = 'hdc'，此条件，可以在idx_t1_pu索引上进行过滤，但不属于Index key。
• Table Filter：comment is not null，此条件，在idx_t1_pu索引上无法过滤，只能在聚簇索引上过滤。

再来看看这条SQL的加锁情况，如下图所示

从图中可以看出，在RR隔离级别下，由Index key所确定的范围，被加上了GAP锁，Index Filter给定的条件(userid = hdc)何时过滤，视MySQL版本而定，在Mysql5.6版本之前，不支持Index Condition PushDown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6之后支持了Index Condition PushDown(ICP)，则在index上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录不需要加记录X锁（图中，用红色箭头标出的X锁，是否要加由是否支持ICP而定），而Table Filter对应的过滤条件，则在聚簇索引中读取后，在MySQL Server层面过滤，因此聚簇索引上也需要加X锁，最后选取了一条满足条件的记录[8,hdc.d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

在RR隔离级别下，针对一个复杂的SQL，首先需要提取where条件，Index key确定的额范围，需要加上GAP锁，Index Filter过滤条件，视MySQL版本是否支持ICP，若支持ICP，则不满足Index Filter的记录不需要加X锁，否则需要加X锁。Table Filter过滤条件，无论是否满足，都需要加X锁。

死锁原理分析

深入理解MySQL如何加锁，有两个比较重要的作用

• 可以根据MySQL的加锁规则，写出不会发生死锁的SQL
• 可以根据MySQL的加锁规则，定位出上线上产生死锁的原因。

下面，看看两个死锁的例子（一个是两个session的两条SQL产生的死锁，另一个是两个session的一条SQL产生的死锁）。

上面的两个死锁示例，第一个非常好理解，也是最常见的死锁，每个事务执行的两条SQL，分别持有了一把锁，然后等待另一把锁，产生死锁。

第二个示例，虽然两个session都只有一条SQL，仍旧会产生死锁，首先必须回顾上面的MySQL加锁规则，针对session1，从name索引出发，读到的[hdc,1]，[hdc,6]均满足条件，不仅会给name索引上记录加X锁，而且会加聚簇索引上的记录X锁，加锁顺序为先[1,hdc,100]，后[6,hdc,10]，而session2，从pubtime索引出发，[10,6],[100,1]均满足条件，同样也会加聚簇索引上的记录X锁，加锁顺序为[6,hdc,10],后[1,hdc,100]，发现没有，跟session1的加锁顺序正好相反，如果两个session恰好都持有了一把锁，请求第二把锁，死锁就发生了。

死锁的发生与否，并不在于事务中有多少条SQL语句，关键在于两个以上的session加锁顺序不一致。

如何解决死锁？

MySQL默认会主动探知死锁，并回滚某一个影响最小的事务，等另一个事务执行完成之后，再重新执行该事务。