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)。快照读，读取的是记录的可见版本 (有可能是历史版本)，不用加锁。当前读，读取的是记录的最新版本，并且，当前读返回的记录，都会加上锁，保证其他事务不会再并发修改这条记录。

• Innodb锁问题

• InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）；二是采用了行级锁。行级锁与表级锁本来就有许多不同之处，另外，事务的引入也带来了一些新问题。下面我们先介绍一点背景知识，然后详细讨论InnoDB的锁问题。
• InnoDB实现了以下两种类型的行锁。
  l  共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。
  l  排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。

• 在一个支持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锁 (排它锁)。

• 值得注意的是：InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁！
• 判断一条SQL会对哪些记录上锁之前需要确认几个条件：
  • 前提一：id列是不是主键？
  • 前提二：当前系统的隔离级别是什么？
  • 前提三：id列如果不是主键，那么id列上有索引吗？
  • 前提四：id列上如果有二级索引，那么这个索引是唯一索引吗？
  • 前提五：两个SQL的执行计划是什么？索引扫描？全表扫描
RC隔离级别下针对delete from t1 where id = 10 语句：
• 主键索引，只在对应的主键索引上加排他锁即可

　　　　　　 

• • 唯一索引，在对应的唯一索引及主键索引上都加上排他锁，防止通过主键定位到该条数据的SQL拿到锁修改数据；一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name=’d’,id=10]的记录。
    
  • 非唯一索引，首先，id列索引上，满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。若id列上有非唯一索引，那么对应的所有满足SQL查询条件的记录，都会被加锁。同时，这些记录在主键索引上的记录，也会被加锁。

　　　　　　

• • 如果id列上无索引，那么将会根据聚簇索引搜索全表，筛选出记录，表中所有行都将被锁住；这是因为在Mysql中如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回，然后由MySQL Server层进行过滤。因此也就把所有的记录，都锁上了。

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

     

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

　　　　　　

　　　　RR隔离级别的加锁规则基本和RC级别一致，只不过在非主键索引下，会新增间隙锁，防止幻读；特别注意：如果进行全表扫描的当前读，那么会锁上表中的所有记录，同时会锁上聚簇索引内的所有GAP，杜绝所有的并发 更新/删除/插入 操作。当然，也可以通过触发semi-consistent read，来缓解加锁开销与并发影响，但是semi-consistent read本身也会带来其他问题，不建议使用。

 

• 复合索引与锁
  • 复合索引是根据最左匹配原则来筛选数据的：
  • 下面看一个简单的复合索引，key index_name_cid ('name','cid'),
    
    以该表的(name,cid)复合索引为例,它内部结构简单说就是下面这样排列的：
    

    mysql创建复合索引的规则是首先会对复合索引的最左边的，也就是第一个name字段的数据进行排序，在第一个字段的排序基础上，然后再对后面第二个的cid字段进行排序。其实就相当于实现了类似 order by name cid这样一种排序规则。

    所以：第一个name字段是绝对有序的，而第二字段就是无序的了。所以通常情况下，直接使用第二个cid字段进行条件判断是用不到索引的，当然，可能会出现上面的使用index类型的索引。这就是所谓的mysql为什么要强调最左前缀原则的原因。

    那么什么时候才能用到呢?
    当然是cid字段的索引数据也是有序的情况下才能使用咯，什么时候才是有序的呢？观察可知，当然是在name字段是等值匹配的情况下，cid才是有序的。发现没有，观察两个name名字为 c 的cid字段是不是有序的呢。从上往下分别是4 5。
    这也就是mysql索引规则中要求复合索引要想使用第二个索引，必须先使用第一个索引的原因。（而且第一个索引必须是等值匹配）。
  • 再看一条复杂的SQL在RR级别下的加锁情况：

　　　　

　　　　根据复合索引筛选规则，idx_t1_pu只会用到puptime上的索引（index-key），所以InnoDB通过索引只能过滤出pubtime3,5,10这3条记录，条件userid = 'hdc'则由索引过滤器（index-filter）过滤，何时过滤，视MySQL的版本而定，在MySQL 5.6版本之前，不支持Index Condition Pushdown(ICP)，因此Index Filter在MySQL Server层过滤，在5.6后支持了Index Condition Pushdown，则在存储引擎上过滤。若不支持ICP，不满足Index Filter的记录，也需要加上记录X锁，若支持ICP，则不满足Index Filter的记录，无需加记录X锁 (图中，用红色箭头标出的X锁，是否要加，视是否支持ICP而定)；而comment is not NULL这个Table Filter对应的过滤条件，则在聚簇索引中读取数据后，在MySQL Server层面过滤，同时聚簇索引上也需要X锁。最后，选取出了一条满足条件的记录[8,hdc,d,5,good]，但是加锁的数量，要远远大于满足条件的记录数量。

　　　　

最后总结，由于InnoDB的锁都是加在索引上，所以所有加锁的过程均是在存储引擎这一层实现的；如果有些数据无法在存储引擎这一层过滤掉（Index-Filter在哪一层过滤由MySQL版本觉得），那么InnoDB就会把这些数据统一全加上锁丢给Mysql Server，最后由MySql Server完成过滤；所以在高并发的场景下，为了减少锁的开销，需要尽可能在存储引擎这一层过滤条所有条件得到对应数据。

 

参考资料：

http://hedengcheng.com/?p=771

http://blog.csdn.net/xifeijian/article/details/20313977