第十章：第18节 MySQL进阶篇——InnoDB（RC模式下）行锁实现方式
更新于：2017-09-04 15:03:41
InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。
InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
索引的作用不只是影响查询性能，还会对事务有影响，对并发性有影响（可见索引很重要）。在实际应用中，要特别注意InnoDB行锁的这一特性，不然的话，可能导致大量的锁冲突，从而影响并发性能。
通过一个例子我们来看下不用索引是不是直接锁表。
“user_info”表是InnoDB表，“user_name”字段加了唯一索引
1）目前是“RR”隔离级别，（上下）两边各开启事务，分别为不同条件的select语句加“排他锁”
是不是相互之间不受影响，假如楠神把索引去掉，select语句不走索引，直接全表查询
（上边）的加“排他锁”的select执行完，再执行（下边）的加“排他锁”的select就会处于锁等待状态，然后报超时错误。
可以肯定了，“锁”和“索引”是有极大关联的。
楠神从网上看过别人写的文章，描述在“RC”和“RR”这两种隔离级别各种情况下加锁的图解，挺好的文章，拿过来与大家分享：
一个事务中有这样一条SQL语句
delete from t1 where id = 10;
这个SQL会加什么锁？
在回答这个问题前，必须要知道以下的一些前提条件：
前提一：id列是不是主键？
前提二：当前系统的隔离级别是什么？
前提三：id列如果不是主键，那么id列上有索引吗？
前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
前提五：SQL的执行计划是什么？索引扫描？全表扫描？
没有这些前提，直接一条SQL是不知道它加什么锁的。当这个问题前提明确了，SQL会加什么锁，也就一目了然。下面，将这些问题的答案进行组合，然后按照从易到难的顺序，逐个分析每种组合下，对应的SQL会加哪些锁？
注：下面的这些组合，做一个前提假设，也就是有索引时，执行计划一定会选择使用索引进行过滤 (索引扫描)。
组合一：id列是主键，RC隔离级别
组合二：id列是二级唯一索引，RC隔离级别
组合三：id列是二级非唯一索引，RC隔离级别
组合四：id列上没有索引，RC隔离级别
组合五：id列是主键，RR隔离级别
组合六：id列是二级唯一索引，RR隔离级别
组合七：id列是二级非唯一索引，RR隔离级别
组合八：id列上没有索引，RR隔离级别
组合九：Serializable隔离级别
1）组合一：id主键+RC
这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10; 只需要将主键上，id = 10的记录加上X锁即可。如下图所示：
结论：id是主键时，此SQL只需要在id=10这条记录上加X锁即可。
2）组合二：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锁。
为什么要回主键索引？不解地去看本章第3节InnoDB索引实现方式。
为什么主键索引上的记录也要加锁？试想一下，如果另外一个事务并发执行：update t1 set id = 100 where name = ‘d’; 此时，delete语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。
结论：若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于主键索引(聚簇索引)上的[name='d',id=10]的记录。
3）组合三：id非唯一索引+RC
相对于组合一、二，组合三又发生了变化，隔离级别仍旧是RC不变，但是id列上的约束又降低了，id列不再唯一，只有一个普通的索引。假设delete from t1 where id = 10; 语句，仍旧选择id列上的索引进行过滤where条件，那么此时会持有哪些锁？同样见下图：
根据此图，可以看到，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。
结论：若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。
额外的演示（会与下节课内容做对比）：
在“RC”隔离级别下，是会出现“不可重复读”、“幻读”问题，“不可重复读”我们演示过了，我们来看下是不是会出现“幻读”。
给“age”字段加个普通索引，目前是“RC”隔离级别。
（上边）两次的普通select获取的结果集行数不一样，所以在“RC”下普通select会出现“幻读”问题。
换成“当前读”，给select加个“排他锁”：
分析：
1.（上边）1执行加了“排他锁”的select，但是不影响（下边）2执行插入新数据操作。
2.（上边）执行3时，出现了锁冲突，毫无疑问是（下边）2“排他锁”还没有解锁，3需要给2插入的数据加“排他锁”。
3.（下边）4提交事务，（上边）5执行加了“排他锁”的select，读取了包括新添加的数据。
所以在“RC”下加锁select也会出现“幻读”问题。记住这个地方，在“RR”下会有所不同。
4）组合四：id无索引+RC
相对于前面三个组合，这是一个比较特殊的情况。id列上没有索引，where id = 10;这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？或者是换句话说，全表扫描时，会加什么锁？这个答案也有很多：有人说会在表上加X锁；有人说会将聚簇索引上，选择出来的id = 10;的记录加上X锁。那么实际情况呢？请看下图：
由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。从图中可以看到，满足删除条件的记录有两条，但是，聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足条件，全部被加上X锁。既不是加表锁，也不是在满足条件的记录上加行锁。
有人可能会问？为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。
注：在实际的实现中，MySQL有一些改进，在MySQL Server过滤条件，发现不满足后，会调用unlock_row方法，把不满足条件的记录放锁 (违背了2PL的约束)。这样做，保证了最后只会持有满足条件记录上的锁，但是每条记录的加锁操作还是不能省略的。
在“RC”下，我们看MySQL有没有做优化改进：
“user_name”列没有加索引，“age”有索引
分析：
（上边）3 select加了“排他锁”，由于select没有用到索引，锁全表。按理说（下边）4执行的话应该被影响到，属于锁等待状态，可是没有。说明在“RC”下MySQL的确做了优化，把不满足条件的记录放锁了。
结论：若id列上没有索引，SQL会走聚簇索引的全扫描进行过滤，由于过滤是由MySQL Server层面进行的。因此每条记录，无论是否满足条件，都会被加上X锁。但是，为了效率考量，MySQL做了优化，对于不满足条件的记录，会在判断后放锁，最终持有的，是满足条件的记录上的锁，但是不满足条件的记录上的加锁/放锁动作不会省略。同时，优化也违背了2PL的约束。
其他组合下节继续讲解……