select 存储过程_MySQL学习（五）：Innodb存储引擎锁与MVCC机制的实现原理

概述

• 锁的主要作用是在并发访问时，锁住所操作的数据表，数据页或者数据行，MySQL中不同的存储引擎存在差异，从而避免多个客户端对同一个数据进行操作，导致数据不一致现象的发生。
• 锁根据是否需要对数据修改分为共享锁和排它锁，分记为S锁和X锁，其中S锁与S锁是共享的，S锁与X锁是互斥的，X锁与X锁也是互斥的，即数据读取SELECT操作使用共享锁，数据更新相关操作使用互斥锁。
• 在MySQL当中，myisam存储引擎使用的是表锁，innodb存储引擎使用的是行锁，其中锁的粒度越大，并发性能越差，所以OLTP应用一般使用innodb存储引擎，并发性能更好。
• 在innodb存储引擎的事务实现当中，行锁主要用来实现事务的ACID的I，即隔离性。不同事务之间，当需要对同一个数据行进行修改时，则第一个写事务加X锁之后，其他需要写事务需要阻塞。对于读事务，由于MVCC机制不需要对数据行加锁，故可以正常读数据，不过是最近的快照数据。

MVCC机制

MVCC机制与锁

• 锁主要包含S锁和X锁，由于S锁与X锁互斥，故如果有其他事务对数据行加了X锁，则其他事务，不管是读事务还是写事务，再申请加S锁或X锁时，都需要阻塞，所以这样会影响并发性能。
• 在innodb存储引擎当中，由于默认隔离级别为REPEATABLE READ(可重复读)，对于读操作SELECT，默认是使用MVCC机制实现一致性非锁定读，该机制不需要对数据行进行加锁，故不会占用和等待数据行上的锁，提供了并发性能。

MVCC的实现基础

• 基于undo日志快照，实现了MVCC机制，由于每个数据行可能存在多个版本的快照，故也称为多版本并发控制机制，实现了“一致性非锁定读”，在事务当中，读操作SELECT不需要加锁，而是读取undo日志中的最新快照。其中undo日志为用来实现事务回滚，本身没有带来额外的开销，过程如下：

MVCC与事务隔离级别

• 在innodb存储引擎中，不是所有的事务隔离级别都是使用MVCC机制的一致性非锁定读来提供并发性能。使用该机制的为REPEATABLE-READ可重复读和READ COMMITTED读提交，不过这两种机制所使用的数据行快照版本存在差别。
• 可重复读：基于事务开始时，读取的数据行版本，在事务过程中，不会改变，即对相同的数据行，如果事务本身没有进行修改，则多次SELECT返回相同的数据。
• 读提交：在事务期间，每次SELECT读取的都是最新的数据行数据快照，因为读提交这种事务隔离级别，不实现可重复读。
• 由于REPEATABLE-READ和READ-COMMITTED这两种事务隔离级别都使用了MVCC机制来实现一致性非锁定读，故如果读操作需要加锁的话，则需要显示加锁，如下：

SELECT ... FOR UPDATE; // 加X锁SELECT ... LOCK IN SHARE MODE; // 加S锁

MVCC机制与外键约束

• 外键约束主要用来实现数据完整性，即主表和关联表之间的完整性。在关联表中进行UPDATE和INSERT等写事务时，首先需要SELECT父表，此时的SELECT读取父表不是基于MVCC机制来实现一致性非锁定读的，因为这样会产生数据不一致问题，如当前父表的数据行被其他事务修改了，而是需要对父表加S锁，即在内部实现当中对父表是使用：SELECT … LOCK IN SHARE MODE 来读取的，如果此处有其他事务对父表加X锁，则该操作需要阻塞。

锁的实现

索引与行锁

• innodb存储引擎使用行锁来实现数据的并发安全性。不过并不是对所有的写事务都使用行锁，使用行锁的前提是：写事务对数据表的写操作的查找条件需要包含索引列，包含主键索引或者辅助索引，如UPDATE … SET … WHERE …中的WHERE列需要包含索引列，否则会使用表锁。
• 所以innodb的行锁锁在的其实是数据行的索引，如果是主键索引，由于主键索引是聚簇索引，故锁住的是数据行本身；如果是辅助索引，锁住的是该索引，而不是具体的数据行。

锁的类型

• 行锁只是一个比较泛的概念，在innodb存储引擎中，行锁的实现主要包含三种算法：

1. Record-Key Lock：单个数据行的锁，锁住单条记录；
2. Gap Lock：间隙锁，锁住一个范围，但是不包含数据行本身；
3. Next-Key Lock：Record-Key Lock + Gap Lock，锁住数据行本身和一个范围的数据行。

• 所以innodb的行锁不是简单的锁住某一个数据行这个单条记录，而是根据更新条件，如WHERE中可能包含 > 等范围条件，和事务隔离级别来确定是锁住单条还是多条数据行。

事务隔离级别与锁

REPEATABLE-READ：可重复读

• 可重复读这种事务隔离级别使用的是Next-Key Lock这种锁，即锁住数据行本身和临近一个范围的数据行。不过如果查询条件只包含唯一索引且记录唯一，如=操作，则会降级为Record-Key Lock，只锁住该单独的数据行，如 UPDATE … SET … WHERE a=1，其中a为主键索引，则此处只锁住这条记录，而不是锁住周围的其他数据行。如果是辅助索引，则继续使用Next-Key Lock，即对于=操作，也会锁住周围的数据行。
• REPEATABLE-READ使用Next-Key Lock这种锁算法的主要原因是：innodb存储引擎的可重复读实现是不存在幻读现象的，所以通过锁住一个范围来避免这个范围在当前事务操作过程中，进行了数据写入，如下：假如当前存a=1,2,5的数据行：

SELECT * FROM t WHERE a >= 2 FOR UPDATE;

• 如果使用Record-Key Lock，则是锁住a=2和a=5这两个数据行，但是期间其他事务可以插入a=4的数据行，当再次执行该SQL的时候，则数据就变成了2，4，5三个数据行了，存在幻读现象，不符合可重复读；
• 所以在REPEATABLE-READ这种事务隔离级别下，以上SQL锁住的是(2, +无穷大)，这个范围的数据行，即只要a大于等于2的数据行都不能插入和更新，故插入a=4的数据行的事务需要阻塞。

READ COMMITTED：读提交

• READ-COMMITED这种事务隔离级别使用的是Record-Key Lock这种锁算法，只需要锁住当前SQL匹配的数据行即可，如上面的SQL，只需锁住a=2和a=5的数据行，其他事务可以插入a=4的数据行，故存在幻读现象。

死锁检测

• 死锁的定义为：两个或多个事务在执行过程中，相互竞争资源而导致相互等待，导致都无法继续执行下去产生死锁的现象。
• 死锁的解决方法包含：阻塞回滚，超时回滚和主动检测回滚三种解决方法，其中阻塞回滚为只要遇到阻塞则立即回滚事务，超时回滚为阻塞超时之后回滚，这两种都是被动回滚，而主动检测回滚为innodb主动检测是否存在死锁，存在则回滚事务量最小的事务。
• 主动检测回滚：innodb使用了一个等待图算法来检测发生死锁的事务，如果发现某些事务存在等待环，则回滚这些事务中undo日志量最小的事务来解决死锁，如下：t1和t2之间存在死锁，其中图的节点为事务，边指向该事务等待的资源所在的其他事务。