MySQL 基础篇

为什么Innodb存储引擎的表需要创建主键

在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。

Mysql索引设计如此，Mysql设计的就是innoDB把你的数据和主键索引用B+Tree来组织的，没有主键他的数据就没有一个结构来存储。即： 主键索引就是数据文件本身！

先后先不管主键策略是什么，以下两点都必须遵守

1. 主键不可修改

对于数据库来说，主键其实是可以修改的，只要不和其他主键冲突就可以。但是，对于应用来说，如果一条记录要修改主键，那么就会出现大问题。 因为主键的第二个作用是让其他表的主键外键引用自己，从而实现关系结构。一旦某个表的主键发生了变化，就会导致所有引用了该表的数据必须 全部修改外键。很多Web应用的数据库并不是强约束（仅仅引用主键但并没有设置外键约束），修改主键会导致数据完整性直接被破坏。

2. 业务字段不可用于主键

所有涉及到业务的字段，无论它看上去是否唯一，都决不能用作主键。例如，用户表的Email字段是唯一的，但是，如果用它作主键，就会导致其他 表到处引用Email字段，从而泄漏用户信息。类似的，看上去唯一的用户名、身份证号等，也不能用作主键。对这些唯一字段，应该加上unique 索引约束。此外，修改Email实际上是一个业务操作，这个操作就直接违反了上一条原则。

那么，主键应该使用哪个字段？主键必须使用单独的，完全没有业务含义的字段，也就是主键本身除了唯一标识和不可修改这两个责任外，主键没有 任何业务含义。

为什么推荐主键是整形呢？

MySQL查找过程，就是把节点load到内存然后在内存里不断的去进行数据的比对。假设UUID，既不自增也不是整形。那么，是整形的1<2比较的效率高 还是字符串的"abc"和"abe"比较的效率高呢？显然是前者，因为字符串的比较是转换成ASCII一位一位的比，如果最后一位不一样，比到最后才比较 出大小，就比整形比较慢多了，存储空间来说，整形更小。索引越节约资源越好。

为什么推荐主键是自增的呢？

1. 更方便遍历

如果主键是自增的，那么当遍历数据，从当前节点开始，就可以根据节点间的指针快速找到下一个节点去遍历。

2. 更快速的做数据插入操作，避免增加维护索引的开销

如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成 一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。

如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新记录都要被插到现有索引页的中间某个位置。此时 MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读辉来，这增加了 很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。

主键必须使用单独的，完全没有业务含义的字段，也就是主键本身除了唯一标识和不可修改这两个责任外，主键没有任何业务含义。

MyISAM存储引擎

MyISAM引擎使用B+Tree作为索引结构，叶子节点的data域存放的是数据记录的地址。

下面是MyISAM索引的原理图：

这里设表一共有三列，假设我们以Col1为主键，则上图是一个MyISAM表的主索引（Primary key）示意。可以看出MyISAM的索引文件仅仅保存数据 记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。

如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一棵B+Tree，data域保存数据记录的地址。

因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址， 读取响应数据记录。

MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB存储引擎

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。

第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在 InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶子节点data域保存了完整的数据记录。这个索引的key是数据表的主键， 因此InnoDB表数据文件本身就是主索引。

InnoDB主索引（同时也是数据文件）的示意图，可以看到叶子节点包含了完整的数据记录。这种索引叫做聚集索引。

因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个 可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型 为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data 域。

例如，下图为定义在Col3上的一个辅助索引：

这里以英文字符的ASCII码作为比较准则。

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中 检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助

例如，知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助 索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好注意，因为InnoDB数据文件本身是一棵B+Tree，非单调的主键会造成在插入 新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

MyISAM、InnoDB总结

1. InnoDB使用聚集索引，数据记录本身被存于主索引的叶子节点上，这就要求同一个叶子节点内的各条数据记录按主键顺序存放，因此每当一条新 的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载银子，则开辟一个新的页（节点），如果表使用自增主键，那么 每次插入新的记录时，记录就会顺序添加到当前索引节点后续位置，当一页写满，就会自动开辟一个新的页。这样就会形成一个紧凑的索引结构， 近似顺序填满，由于每次插入时也不需要移动所有数据，因此效率很高，也不会增加很多额外的开销维护索引。
2. 如果使用非自增主键，由于每次插入主键的值近乎于随机，因此每次新记录都要被插到现有索引页的中间某个位置，此时MySQL不得不为了将新 记录插到合适位置而移动数据，甚至目标页面可能已经被写到磁盘而从缓存中清楚，这增加了很多额外开销，同时频繁的移动，分页造成了大量的碎片， 得到不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建并优化填充页面。
3. 由于MySQL从磁盘读取数据时一块一块来读取，同时，根据局部性原理，MySQL引擎会选择预读一部分和你当前读数据所在内存相邻的数据块，这个 时候这些相邻数据块的数据已经在内存中。由于数据库大部分是查询操作，这个时候，如果主键是自增的话，数据存储都是紧凑地存储在一起的， 那么对于局部性原理利用和避免过多地I/O操作都有着巨大的促进作用。

MySQL的索引

对于数据库，我们关心的问题就是如何让它多存，快找，不死机。

MySQL索引

1. 了解MySQL InnoDB引擎的索引吗？

使用索引可以加快查询速度，其实上就是将无序的数据变成有序的(有序就能加快检索速度)，在InnoDB引擎中，索引的底层数据结构是B+树。

2. 为什么不适用红黑树或者B树呢？

MySQL的数据是存储在磁盘的，在查询时一般是不能一次性把全部数据加载到内存中，红黑树是二叉查找树的变种，一个Node节点只能存储一个Key和 一个Value，B-Tree和B+Tree跟红黑树不一样，它们算是多路搜索树，相较于二叉搜索树而言，一个Node节点可以存储的信息会更多，多路搜索树 的高度会比二叉搜索树更低。了解区别之后，其实就容易发现，在数据不能一次加载至内存的场景下，数据需要被检索出来，选择B-Tree或B+Tree的理由就 很充分了(一个Node节点存储信息更多(相较于二叉搜索树)，树的高度更低，树的高度影响检索的速度)。

B+Tree相对于B-Tree而言，它又有两种特性：

1. B+Tree非叶子节点不存储数据，在相同的数据量下，B+Tree更加矮壮。(数据都存储在叶子节点上，非叶子节点的存储能存储更多的索引，所以整颗树就更加 矮壮)
2. B+Tree叶子节点之间组成一个链表，方便于遍历查询(遍历操作在MySQL中比较常见)

1. B+Tree是多路搜索树，树的层级更低(检索更快)
2. B+Tree树只有叶子节点存储数据，B+Tree比B-Tree更加矮壮
3. B+Tree叶子节点有双向链表，编译遍历数据

在MySQL InnoDB引擎下，每创建一个索引，相当于生成了一棵B+Tree。

如果该索引是聚集(聚簇)索引，那么当前B+Tree的叶子节点存储着主键和当前行的数据。

如果该索引是非聚集(聚簇)索引，那么当前B+Tree的叶子节点存储着主键和当前索引列值。

比如写了一句SQL: select * from user where id >= 10，那只要定位到id为10的记录，然后在叶子节点之间通过遍历链表(叶子节点组成的链表)，即可 找到往后的记录了。由于B-Tree会在非叶子节点也存储数据，要遍历的时候可能就得跨层检索，相对麻烦些。基于树的层级以及业务使用场景的特性，所以MySQL 选择了B+Tree作为索引的底层数据结构。

什么叫做回表？

所谓的回表其实就是，当我们使用索引查询数据时，检索出来的数据可能包含其他列，但走的索引树叶子节点只能查到当前列值以及主键ID，所以需要根据主键ID 再去查一遍数据，得到 SQL 所需列。示例，订单表order_id列建了索引，SQL：select order_id, order_name from order_detail where order_id = 123， SQL使用了order_id索引，但在order_id的索引树的叶子节点只有order_id和id，而我们还想检索order_name，所以 MySQL 会拿到id再 去查出order_name再给我们返回，这种操作就叫回表。

回表

当前索引无法检索出完整的内容，需要通过主键二次查询

使用覆盖索引(因为避免了回表操作)，所谓覆盖索引，实际上就是想要查出的列刚好在叶子节点上都存在，比如order_id和order_name创建了联合索引， 刚好在查询时也是order_id和order_name，这些数据都存在索引树的叶子节点上，就不需要回表操作了。

联合索引，最左匹配原则

索引的底层是一棵B+Tree，构建一棵B+Tree只能根据一个值来构建。此处的索引当然也包括联合索引，当索引类型为联合索引时， 数据库会依据联合索引最左的字段来构建B+Tree，也叫最左匹配原则。最左匹配原则：最左优先，以最左边的为起点任何连续的索引都能匹配上。同时 遇到范围查询(<、>、between、like)就会停止匹配。

例子：假如创建一个(a, b, c)的联合索引，那么它的索引树是这样的

该图就是一个形如(a, b, c)联合索引的B+Tree，其中的非叶子节点存储的是第一个关键字的索引a，而叶子节点存储的是三个关键字的数据。这里可以看出a是 有序的，而b, c都是无序的。但是当a相同的时候，b是有序的，b相同的时候，c又是有序的。

通过对联合索引的结构的了解，那么就可以很好的了解为什么最左匹配原则中如果遇到范围查询就会停止。 select * from t where a = 5 and b > 0 and c = 1;这样a, b可以用到索引，c不可以，当查询到b的值以后(这是一个范围值)，c是无序的。所以 就不能根据联合索引来确定到底该取哪一行。

示例：假如建立联合索引(a, b, c)

1. 全值匹配查询时

select * from table_name where a = '1' and b = '2' and c = '3';
select * from table_name where b = '2' and a = '1' and c = '3';
select * from table_name where c = '3' and b = '2' and a = '1';

上面的SQL都用到了索引，where字句几个搜索条件顺序替换不影响查询结果，因为MySQL中有查询优化器，会自动优化查询顺序。

2. 匹配左边的列时

-- 都从最左边开始连续匹配，用到了索引
select * from table_name where a = '1';
select * from table_name where a = '1' and b = '2';
select * from table_name where a = '1' and b = '2' and c = '3';

-- 没有从最左边开始，最后查询没有用到索引，用的是全表扫描
select * from table_name where b = '2';
select * from table_name where c = '3';
select * from table_name where b = '1' and c = '3';

-- 如果不连续时，只用到了a列的索引，b列和c列都没有用到
select * from table_name where a = '1' and c = '3';

3. 匹配列前缀(%)

如果列是字符型的话它的比较规则是先比较字符串的第一个字符，第一个字符小的哪个字符串就比较小，如果两个字符串第一个字符相同，那就再比较第二个 字符，第二个字符比较小的那个字符串就比较小，依次类推，比较字符串。

-- 如果a是字符类型，那么前缀匹配用的是索引，后缀和中缀只能全表扫描了
select * from table_name where a like 'As%'; -- 前缀都是排好序的，走索引查询
select * from table_name where a like '%As'; -- 全表扫描
select * from table_name where a like '%As%'; -- 全表扫描

4. 匹配范围值

-- 可以对最左边的列进行范围查询
select * from table_name where a > 1 and a < 3;

-- 多个列同时进行范围查找时，只有对索引最左边的那个列进行范围查找才用到B+Tree索引，也就是只有a用到索引，在 1<a<3 的范围内b是无序的，不能用
-- 索引，找到 1<a<3 的记录后，只能根据条件 b>1 继续逐条过滤
select * from table_name where a > 1 and a < 3 and b > 1;

6. 排序

一般情况下，我们只能把记录加载到内存中，再用一些排序算法，比如快速排序，归并排序等在内存中对这些记录进行排序，有时候查询的结果集太大不能在内存 中进行排序的话，还可能暂时借助磁盘空间存放中间结果，排序操作完成后再把排好序的结果返回客户端。MySQL中把这种在内存中或磁盘上进行排序的方式统称 为文件排序。文件排序非常慢，但如果order子句用到了索引列，就有可能省去文件排序的步骤。

-- 因为B+Tree索引本身就是按照上述规则排序的，所以可以直接从索引中提取数据，然后进行回表操作取出该索引中不包含的列就好了
select * from table_name order by a, b, c limit 10;

-- 这种颠倒顺序的就没有用到索引
select * from table_name order by b, c, a limit 10;

-- 这种用到部分索引
select * from table_name order by a limit 10;
select * from table_name order by a, b limit 10;

-- 联合素银左边列为常量，后边的列排序可以用到索引
select * from table_name where a = 1 order by b, c limit 10;

覆盖索引，最左匹配原则是优化查询的常见思路。联合索引就是按照第一列进行排序，然后第一列排好序的基础上在对第二列进行排序，依次类推。如果没有第一列 直接访问到第二列，第二列肯定是无序的，直接访问后面的列就无法使用索引了。

假设不用MySQL自增的主键，会有什么问题？

首先主键得保证唯一性和空间利用率。另外，由于索引的特性(有序)，如果生成像uuid类似的主键，那插入的性能比自增的要差，因为生成的uuid，在插入时有 可能需要移动磁盘快(比如，快内的空间在当前时刻已经存满，新生成的uuid需要插入已满的块，就需要移动块的数据)。

非主键自增：需要考虑长度、唯一性以及块移动的问题。

MySQL的事务

事务(Transaction)是由一系列对系统中数据进行访问与更新的操作所组成的一个程序执行逻辑单元。

事务的语法

1. start transaction; 或 begin;
2. commit; 使得当前的修改确认
3. rollback; 使得当前的修改被放弃

事务的ACID特性

1. 原子性(Atomicity)

事务的原子性是指事务必须是一个原子的操作序列单元。事务中包含的各项操作在一次执行过程中，只允许出现两种状态。全部执行成功、全部执行失败 事务开始后所有操作，要么全部完成，要么全部不做，不可能停滞在中间环节。事务执行过程中出错，会回滚到事务开始前的状态，所有的操作就像没有发生一样。 也就是说事务是一个不可分割的整体。

2. 一致性(Consistency)

事务的一致性是指事务的执行不能破坏数据库数据的完整性和一致性，一个事务在执行之前和执行之后，数据库都必须处以一致性状态。比如：如果从A账户转账到 B账户，不可能因为A账户扣了钱，而B账户没有加钱。

3. 隔离性(Isolation)

事务的隔离性是指在并发环境中，并发的事务是互相隔离的。也就是说，不同的事务并发操作相同的数据时，每个事务都有各自完整的数据空间。一个事务内部的 操作及使用的数据对其它并发事务是隔离的，并发执行的各个事务是不能互相干扰的。隔离性分4个级别，下面会介绍。

4. 持久性(Duration)

事务的持久性是指事务一旦提交后，数据库中的数据必须被永久的保存下来。即使服务器系统崩溃或服务器宕机等故障。只要数据库重新启动，那么一定能将其恢复 到事务成功结束后的状态。

事务的并发问题

脏读：读取到了没有提交的数据，事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据

不可重复读：同一条命令返回不同的结果集(更新)。事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取 同一数据时，结果不一致。

幻读：重复查询的过程中，数据就发生了量的变化(insert, delete)

事务的隔离级别

事务隔离级别	脏 读	不可重复读	幻 读
读未提交(READ_UNCOMMITTED)	允许	允许	允许
读已提交(READ_COMMITTED)	禁止	允许	允许
可重复读(REPEATABLE_READ)	禁止	禁止	可能会
顺序读(SERIALIZABLE)	禁止	禁止	禁止

4种事务隔离级别从上往下，级别越高，并发性越差，安全性就越来越高。一般数据库默认级别是读已提交或可重复读。

-- 查看当前的事务隔离级别
mysql> select @@tx_isolation;
+-----------------+
|  @@tx_isolation |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set, 1 warning (0.93 sec)

-- 设置当前会话中事务隔离级别
mysql> set session transaction isolation level read uncommitted;
Query OK, 0 rows affected (0.00 sec)

读未提交(READ_UNCOMMITTED)

读未提交，该隔离级别允许脏读，其隔离级别是最低的。换句话说，如果一个事务正在处理某一数据，并对其进行了更新，但同时尚未完成事务，因此还没有提交 事务；而同时，允许另一个事务也能够访问该数据。

脏读实例：在事务A和事务B同时执行时可能会出现如下场景

时间	事务A(存款)	事务B(取款)
T1	开始事务	--
T2	--	开始事务
T3	--	查询余额(1000元)
T4	--	取出1000元(余额0元)
T5	查询余额(0元)	--
T6	--	撤销事务(余额恢复1000元)
T7	存入500元(余额500元)	--
T8	提交事务	--

余额应该为1500元才对。请看T5时间点，事务A此时查询的余额为0，这个数据就是脏数据，他是事务B造成的，很明显是事务没有进行隔离造成的。

读已提交(READ_COMMITTED)

读已提交是不同的事务执行的时候只能获取到已经提交的数据。这样就不会出现上面的脏读的情况了。但是在同一个事务中执行同一个读取，结果不一致。

不可重复读示例：可是解决了脏读问题，还是解决不了可重复读的问题。

时间	事务A(存款)	事务B(取款)
T1	开始事务	--
T2	--	开始事务
T3	--	查询余额(1000元)
T4	查询余额(1000元)	--
T5	--	取出1000元(余额0元)
T6	--	提交事务
T7	查询余额(0元)	--
T8	提交事务	--

事务A其实除了查询两次以外，其它什么事情都没做，结果钱就从1000变成0了，这就是不可重复读的问题。

可重复读(REPEATABLE_READ)

可重复读就是保证在事务处理过程中，多次读取同一个数据时，该数据的值和事务开始时刻是一致的。因此该事务级别限制了不可重复读和脏读，但是有可能出现 幻读的数据。

幻读：幻读就是指同样的事务操作，在前后两个时间段内执行对同一个数据项的读取，可能出现不一致的结果

时间	事务A	事务B
T1	开始事务	--
T2	查询当前所有数据	开始事务
T3	--	插入一条数据
T4	查询当前所有数据	提交事务
T5	进行范围修改	--
T6	查询当前所有数据	--
T7	提交事务	--

顺序读(SERIALIZABLE)

顺序读是最严格的事务隔离级别。它要求所有的事务排队顺序执行，即事务只能一个接一个地处理，不能并发。

MySQL的锁

当数据库有并发事务的时候，可能会产生数据的不一致，这时需要一些机制来保证访问的次序，锁机制就是这样的一个机制。

锁的分类-粒度

在关系型数据库中，可以按照锁的粒度把数据库分为行级锁(INNODB引擎)、表级锁(MYISAM引擎)、和页级锁(BDB引擎)。

MyISAM和InnoDB存储引擎使用的锁：

MyISAM采用表级锁(table-level locking)；

InnoDB支持行级锁(row-level locking)和表级锁，默认为行级锁；

行级锁、表级锁和页级锁对比：

行级锁：MySQL中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。行级锁能大大减少数据库操作的冲突。其加锁粒度最小，但加锁的开销也最大。 行级锁分为共享锁和排他锁。

特点

开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

表级锁：MySQL中锁定粒度最大的一种锁，表示当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分MySQL引擎支持。最常使用的MyISAM与InnoDB 都支持表级锁定。表级锁定分为表共享读锁(共享锁)与表独占写锁(排他锁)。

特点

开销小，加锁快；不会出现死锁；锁定粒度大，发出锁冲突的概率最高，并发度最低。

页级锁：是MySQL中锁定粒度介于行级锁和表级锁中间的一种锁。表级锁速度快，但冲突多，行级冲突少，但速度慢。所以取了折衷的页级，一次锁定相邻的一组 记录。

特点

开销和加锁时间介于表级和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。

锁的分类-类别

InnoDB实现了如下两种标准的行级锁：

共享锁(S Lock)：允许事务对一条行数据进行读取，又叫做读锁。当用户进行数据的读取时，对数据加上共享锁，共享锁可以同时加上多个。

排他锁(X Lock)：允许事务对一条行数据进行删除或更新，又叫做写锁。当用户进行数据的写入时，对数据加上排他锁。排他锁只可以加一个，它和其它的 排他锁、共享锁都互斥。

如果一个事务T1已经获得了行r的共享锁，那么另外的事务T2可以立即获得行r的共享锁，因为读取并没有改变行r的数据，这种情况为锁兼容(Lock Compatible)。 但若有其它的事务T3想获得行r的排他锁，则其必须等待事务T1、T2释放行r上的共享锁--这种称为锁不兼容。因为获取排他锁一般是为了改变数据，所以不能 同时进行读取或其它写入操作。

X锁与任何锁都不兼容，而S锁仅和S锁兼容

此外，InnoDB存储引擎支持多粒度锁定，这种锁定允许事务在行级上的锁和表级上的锁同时存在。为了支持在不同粒度上进行加锁操作，InnoDB存储引擎支持一种 额外的锁方式，称之为意向锁(Intention Lock)。意向锁是将锁定的对象分为多个层次，意向锁意味着事务希望在更细粒度上进行加锁。

InnoDB存储引擎支持意向锁设计比较简练，其意向锁即为表级别的锁。设计目的主要是为了在事务中揭示下一行将被请求的锁类型。其支持两种意向锁：

• 意向共享锁(IS Lock)：事务想要获得一张表中的某几行的共享锁
• 意向排他锁(IX Lock)：事务想要获得一张表中某几行的排他锁

意向共享锁/意向排他锁属于表锁，且取得意向共享锁/意向排他锁是取得共享锁/排他锁的前置条件。

共享锁/排他锁与意向共享锁/意向排他锁的兼容性关系：

	X	IX	S	IS
X	互斥	互斥	互斥	互斥
IX	互斥	兼容	互斥	兼容
S	互斥	互斥	兼容	兼容
IS	互斥	兼容	兼容	兼容

这里需要重点关注的是IX锁和IX锁是相互兼容的，这是导致上面场景--发生死锁的前置条件，后面会对死锁原因进行详细分析。

由于InnoDB存储引擎支持的是行级别的锁，因此意向锁不会阻塞除全表扫描以外的任何请求，它们的主要目的是为了表示是否有人请求锁定表中的某一行数据。

如果没有意向锁，当已经有人使用行锁对表中的某一行进行修改时，如果另外一个请求要求对全表进行修改，那么就需要对所有的行是否被锁定进行扫描，在这种 情况下，效率是非常低的；不过，在引入意向锁之后，当有人使用行锁对表中的某一行修改之前，会先为表添加意向互斥锁(IX)，再为行记录添加互斥锁(X)， 在这是如果有人尝试对全表进行修改就不需要判断表中的每一行数据是否被加锁了，只需要等待意向互斥锁释放就可以了。

锁的算法

InnoDB有三种行锁的算法：

1. Record Lock

简单说就是单个行记录上加锁，防止事务间修改或删除数据。Record Lock总是会去锁住索引记录，如果表建立的时候没有设置任何一个索引，InnoDB存储引擎 会使用隐式的主键来进行锁定。

2. Gap Lock

间隙锁，表示只锁住一段范围，不锁记录本身，通常表示两个索引记录之间，或者索引上的第一条记录之前，或者最后一条记录之后的锁。

3. Next-Key Lock

Gap Lock + Record Lock，锁定一个范围及锁定记录本身。例如一个索引有10，11，13，20这四个值，那么该索引可能被Next-Key Locking的区间为 (负无穷, 10), (10, 11), (11, 13), (13, 20), (20, 正无穷)。需要理解一点，InnoDB中加锁都是给所有记录一条一条加锁，并没有一个直接的范围 可以直接锁住，所以会生成多个区间。

MySQL默认情况下使用RR的隔离级别，而Next-Key Lock正是为了解决RR隔离级别下的不可重复读问题和幻读问题。所谓不可重复读就是一个事务内执行相同 的查询，会看到不同的记录，在RR隔离级别下是不允许的。

假设索引上有记录1, 4, 5, 8, 12，执行类似语句SELECT ... WHERE col > 10 FOR UPDATE。如果我们不在(8, 12)之间加上Next-key Lock 另外一个会话就可能向其中插入一条记录9，再执行一次相同的SELECT ... FOR UPDATE，就会看到新插入的记录。这也是为什么MySQL插入一条记录时，需要 判断下一条记录上是否加锁了，如果加锁就需要等待。

InnoDB对行的查询默认采用Next-key算法。然而，当查询条件为等值时，且索引有唯一属性时(就是只锁定一条记录)，InnoDB存储引擎会对 Next-key Lock 进行优化，将其降级为Record Lock，即仅锁住索引本身，而不是一个范围，因为此时不会产生重复读问题。

锁读取

一致性非锁定读(consistent nonlocking read)：是指InnoDB存储引擎通过多版本控制(MVCC)读取当前数据库中行数据的方式。如果读取的行正在 执行DELETE或UPDATE操作，这时读取操作不会因此去等待行上锁的释放。相反地，InnoDB会去读取行的一个快照。

上图直观地展现了InnoDB一致性非锁定读的机制。之所以称其为非锁定读，是因为不需要等待行上排他锁的释放。快照数据是指该行的之前版本的数据，每行记录 可能有多个版本，一般称这种技术为行多版本技术。由此带来的并发控制，称之为多版本并发控制(Multi Version Concurrency Control, MVCC)。 InnoDB是通过undo log来实现MVCC。undo log本身用来在事务中回滚数据，因此快照数据本身是没有额外开销的。此外，读取快照数据是不需要上锁的， 因为没有事务需要对历史的数据进行修改操作。

一致性非锁定读是InnoDB默认的读取方式，即读取不会占用和等待行上的锁。但是并不是在每个事务隔离级别下都是采用这种方式。此外，即使都是使用一致性 非锁定读，但是对于快照数据的定义各不相同。

下面举个例子来详细说明一下上述的情况。

首先在会话A中显示地开启一个事务，然后读取test表中的id为1的数据，但是事务并没有提交。与此同时，在开启另一个会话B，将test表中id为1的记录修改为 id=3，但是事务同样也没有提交，这样id=1的行其实加了一个排他锁。

由于InnoDB在READ COMMITTED和REPEATABLE READ事务隔离级别下使用一致性非锁定读，这时如果会话A再次读取id为1的记录，仍然能够读取到相同的数据。 此时，READ COMMITTED和REPEATABLE READ事务隔离级别没有任何区别。

当会话B提交事务后，会话A再次运行SELECT * FROM test WHERE id = 1的SQL语句时，两个事务隔离级别下得到的结果就不一样了：

对于READ COMMITTED的事务隔离级别，它总是读取行的最新版本，如果行被锁定了，则读取该行版本的最新一个快照。因为会话B的事务已经提交，所以 在该隔离级别下上述SQL语句的结果集是空的。
对于REPEATABLE READ的事务隔离级别，总是读取事务开始时的行数据，因此，在该隔离级别下，上述SQL语句仍然会获得相同的数据。

在默认情况下，即事务的隔离级别是repeatable read模式下，InnoDB存储引擎的SELECT操作使用的是一致性非锁定读。但是在某些情况下，用户需要显示的 读取数据操作进行加锁保证数据逻辑的一致性。

InnoDB提供了两种方式实现一致性锁定读：

select ... for update，对读取的行加了X锁
select ... lock in share mode，对读取的行加了S锁

需要注意的是，以上两种语句必须在一个事务当中，当事务提交了，锁也就释放了。

阻塞

因为不同锁之间的兼容性关系，有时候一个事务中的锁需要等待另一个事务中的锁释放它所占用的资源，这就是阻塞。

在InnoDB中，参数innodb_lock_wait_timeout用来控制等待的时间，innodb_rollback_on_timeout用来设定是否在等待超时后回滚。前者是动态的， 后者是静态的。

mysql> show variables like 'innodb_lock_wait_timeout'\G;
******************************* 1. row
Variable_name: innodb_lock_wait_timeout
Value: 50

show variables like 'innodb_rollback_on_timeout'\G;
******************************* 1. row
Variable_name: innodb_rollback_on_timeout
Value: OFF

死锁

死锁是指两个或两个以上的事务在执行过程中，因争夺资源而造成的一种相互等待的现象。若无外力作用，事务都将无法推进下去。

解决死锁做简单的方法就是超时，即当两个事务互相等待时，当一个等待时间超过了某一阈值，其中一个事务进行回滚，另一个等待的事务就能继续进行。

但是如果超时的事务所占权重比较大，如事务更新了很多行，占用了较多的undo log，回滚这个事务的时间相对于另一个事务所占用的时间可能会更多，就显得不 合适了。因此，除了超时机制，当前数据库都普遍采用**等待图(wait-for graph)**的方式来进行死锁监测。wait-for graph要求数据库保存一下两种信息：

• 锁的信息链表
• 事务等待链表

通过上述链表可以构造出一张图，而在这个图中若存在回路，就代表存在死锁，因此资源间相互发生等待。在wait-for graph中，事务为途中的节点，而在图中， 事务T1指向T2边的定义为：

• 事务T1等待事务T2所占用的资源
• 事务T1最终等待T2所占用的资源，也就是事务之间在等待相同的资源，而事务T1发生在事务T2的后面

示例：

在Transaction Wait Lists中可以看到共有4个事务t1、t2、t3、t4，故在wait-for graph中应有4个节点。

通过上图可以发现存在回路(t1, t2)，因此存在死锁。可以发现wait-for graph是一种较为主动的死锁检测机制，在每个事务请求锁并发生等待时都会判断 是否存在回路，若存在则有死锁，通常来说InnoDB存储引擎选择回滚undo量最小的事务。

InnoDB锁相关状态查询

用户可以使用INFOMATION_SCHEMA库下的INNODB_TRX、INNODB_LOCKS和INNODB_LOCK_WAITS表来监控当前事务并分析可能出现的问题。关键字段 如下：

mysql> SELECT * FROM information_schema.INNODB_TRX\G;
******************* 1.row ************************
trx_id: 7311F4
trx_state: LOCK WAIT
trx_started: 2010-01-04 10:49:33
trx_requested_lock_id: 7311F4:96:3:2
trx_wait_started: 2010-01-04 10:49:33
trx_weight: 2
trx_mysql_thread_id: 471719
trx_query: select * from parent lock in share mode

表只能显示当前运行的InnoDB事务，并不能直接判断锁的一些情况。如果需要查看锁，则还需要访问表，该表的关键字段组成如下：

mysql> SELECT * FROM information_schema.INNODB_LOCKS\G;
******************* 1.row ************************
lock_id: 16219:56:4:5
lock_trx_id: 16219
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`z`
lock_index: b
lock_space: 56
lock_page: 4
lock_rec: 5
lock_data: 6, 7
******************* 2.row ************************
lock_id: 16218:56:4:5
lock_trx_id: 16218
lock_mode: X,GAP
lock_type: RECORD
lock_table: `test`.`z`
lock_index: b
lock_space: 56
lock_page: 4
lock_rec: 5
lock_data: 6, 7
2 rows in set, 1 warning (0.00 sec)

通过表INNODB_LOCKS查看每张表上锁的情况后，用户就可以来判断由此引发的等待情况。当事务量非常大，其中锁和等待也时常发生，这个时候就不那么容易判断。 但是通过表INNODB_LOCK_WAITS，可以很直观的反应当前事务的等待。表INNODB_LOCK_WAITS由四个字段组成，如下表所示：

mysql> SELECT * FROM information_schema.INNODB_LOCK_WAITS\G;
******************* 1.row ************************
requesting_trx_id: 7311F4
requesting_lock_id: 7311F4:96:3:2
blocking_trx_id: 730FEE
blocking_lock_id: 370Fee:96:3:2

通过上述的SQL语句，用户可以清楚直观地看到哪个事务阻塞了另一个事务，然后使用上述的事务ID和锁ID，去INNODB_TRX和INNODB_LOCKS表中查看更加详细 的信息。

锁的应用实例解析

需要特别注意的是，MySQL怎么使用锁，与事务的隔离级别、列上的索引状况等密切相关。单独讨论SQL语句的加锁方式以及范围而不考虑使用场景，都是没有意义的。

场景一：RR隔离级别+主键索引

首先根据如下代码创建测试表t，然后开启两个事务进行操作。

create table t (a int primary key);
insert into t select 1;
insert into t select 2;
insert into t select 5;
insert into t select 7;
insert into t select 9;

开启事务1，保持未提交的状态：

begin;
select * from t where a = 5 for update;

开启事务2并提交：

begin;
insert into t select 4;
commit;

表t共有1、2、5、7、9五个值，在上面的例子中，在事务1中首先对a = 5进行X锁定，由于a是主键且唯一，因此锁定的仅是5这个值，而不是(2, 5)这个范围。 这样在事务2中插入值4而不会阻塞，可以立即插入并返回。即锁定由Next-key Lock算法降级为Record Lock，从而提高应用的并发性。

注意

对于唯一键值的锁定，Next-Key Lock降级为Record Lock仅存在与查询所有的唯一索引列。若唯一索引由多个列组成，而查询仅是查找多个唯一索引列中 的其中一个，那么查询其实是range类型查询，而不是const类型查询，故InnoDB存储引擎依然使用Next-Key Lock进行锁定。

但如果对主键进行范围查询时，锁的范围是怎么样的呢？

begin;
select * from t where a <= 5 for update;

针对下面的SQL语句：

insert into test.t select 6;
insert into test.t select 8;

第一条会产生锁等待，而第二条可以插入成功。

表t共有1、2、5、7、9五个值，在上面的例子中，在事务1中执行a <= 5范围查询时，事务1加锁范围是(负无穷, 1)、(1, 2)、(2, 5)、(5, 7)，且 1、2、5、7这四条记录也都加锁了，也就是说再插入<= 7的记录都会产生锁等待，插入记录8是成功的。

那么InnoDB如何判断是否允许插入数据呢？对于普通索引，insert的加锁策略是：查找小于等于insert_rec的第一条记录，然后查看第一条记录的下一个记录 是否有Gap锁，有则等待，没有则插入。比如插入6这条记录，首先定位<= 6的记录，也就是5，然后确认5的下一条记录是否锁住了Gap，这里也就是7，当7 这条记录有锁是代表锁住的是(5, 7)这个范围，就不允许插入(会申请一把插入意向锁)，保证了可重复读。证明了RR隔离级别下使用了Next-key Lock来保证 其"可重复读"的特性。如果没有锁就直接插入即可。

但是如果插入的记录有唯一约束，只判断下一条记录是否锁住了Gap就不行了，显然会插入重复数据破坏唯一性。这时还会把插入的记录与前一条数据进行比较， 如果相同则给插入记录的前一条记录加S Lock(lock in share mode)，加锁成功则返回duplicate key，否则等待S Lock。

这个地方有一个疑问：为什么MySQL加锁时，不直接加5这条记录本身以及< 5的记录呢？为什么还要给加锁呢？因为(5, 7)加不加锁并不会影响RR级别可 重复读的特性。其实这跟B+Tree有关系，首先MySQL定位到1这条记录并加锁，然后顺着1往后读取数据并加锁，直到读取到第一条不匹配数据才能确定是否停止 继续读取数据，而在RR隔离级别下只要被读到的数据都需要进行加锁。如果查询条件是< 5，那么加锁只会加到5这条记录为止。

如果是在RC隔离级别下，只会对符合条件的记录进行加记录锁，不会对满足条件的下一条记录进行加锁。

场景二：RR隔离级别+普通索引

正如上面介绍的，Next-Key Lock降级为Record Lock仅在查询的列是唯一索引且条件为等值查询的情况下。若是普通索引，则情况会完全不同。

创建测试表z：

create table z(id int primary key, b int, index(b));
insert into z values(1, 1);
insert into z values(3, 1);
insert into z values(5, 3);
insert into z values(7, 6);
insert into z values(10, 8);

begin;
select * from z where b = 3 for update;

这是SQL语句通过索引列b进行查询，因此其使用传统的Next-Key Lock技术加锁，并且由于有两个索引，其需要分别进行锁定。对于聚集索引，其仅对列id等于5 的索引加上Record Lock。那么，为什么主键索引上的记录也要加锁呢？因为有可能其他事务会根据主键对z表进行更新，试想一下，如果主键索引没有加锁，那么 显然会存在并发问题。

而对于普通索引，其加上的是NextKey Lock，锁定的范围是(1, 3)，特别需要注意的是，InnoDB存储引擎还会对普通索引下一个键值加上Gap lock，即还有 一个普通索引范围为(3, 6)的锁，因此整个锁的范围为：(3, 1)、(5, 3)和(5, 3)、(7, 6)。

此时，若在新的事务2中运行下面的SQL语句：

select * from z where id = 5 lock in share mode;
insert into z select 2, 1;
insert into z select 4, 2;
insert into z select 6, 6;

第一个SQL语句，在事务1中执行的SQL语句已经对聚集索引中的列a = 5的值加上X锁，因此执行会被阻塞。

第二个SQL语句，主键插入2，没有问题，插入的普通索引值1也不在锁定的范围中(应是(2, 1)不在(3, 1)、(5, 3)锁定范围内)，因此可以执行成功。

第三个SQL语句，主键插入4，没有问题，插入的普通索引值2在锁定的范围(1, 3)中(应是(4, 2)在(3, 1)、(5, 3)锁定范围内)，因此执行会被阻塞。

第四个SQL语句，插入的主键6没有被锁定，6也不在范围(1, 3)之间。但插入的值6在另一个锁定的范围(3, 6)中(应是(6, 6)在(5, 3)、(7, 6)锁定范围内)， 故同样需要等待。

在RR隔离级别下，对于INSERT的操作，其会检查插入记录的下一条记录是否被锁定，若已经被锁定，则不允许操作，从而避免不可重复读问题。而下面的SQL语句， 不会被阻塞，可以立即执行：

insert into z select 8, 6;
insert into z select 2, 0;
insert into z select 6, 7;

从上面的例子中可以看到，Gap Lock的作用是为了阻止多个事务将记录插入到同一个范围内，解决"不可重复读"问题的产生。例如在上面的例子中，事务1中用户 已经锁定了b = 3的记录，若此时没有Gap Lock锁定(3, 6)，那么用户可以插入索引b列为3的记录，这会导致事务1中的用户再次执行同样的查询时会返回 不同的记录，即产生不可重复读问题。

这里有一个问题值得思考一下，为什么插入(6, 6)不允许，而插入(8, 6)是允许的。这个InnoDB索引结构有关系，二级索引是指向主键，所以结构如下： (1, 1)，(1, 3)，(3, 5)，(6, 7)，(8, 10)。真正的Gap Lock锁住的也是((1, 1)，(1, 3))这样的结构，所以当插入(6, 6)时，需要插入到 (3, 5)，(6, 7)之间，这区间被锁，所以无法插入；而我们插入(8, 6)是需要插入到(6, 7)，(8, 10)之间，没有锁存在，所以可以插入成功。

另外，在RR隔离级别下，访问条件为二级索引的情况下，就算访问一条不存在的记录同样需要加Next-Key Lock，比如：

select * from z where b = 7 for update;

InnoDB会对(6, 8)这个区间(即(7, 6)，(10, 8)区间)加了Gap Lock，也就是插入这个区间的数据都会被阻塞。

虽然在RR隔离级别默认使用Gap Lock，但用户可以通过以下两种方式来显示地关闭Gap Lock：

将事务的隔离级别设置为 READ COMMITTED； 将参数innodb_locks_unsafe_for_binlog设置为1；

当设置了上述参数或隔离级别调整为READ COMMITTED时，除了外键约束和唯一性检查(duplicate key)依然需要Gap Lock，其余情况仅使用Record Lock 进行锁定。但需要知道的是，上述设置破坏了事务的隔离性，并且对于MySQL复制来说，可能会导致主从数据的不一致。

场景三：RC隔离级别+主键索引

将事务隔离级别切为RC，开启事务1，保持未提交的状态：

begin;
select * from z where id > 3 for update;

执行下面的SQL：

insert into z select 6, 6;

可以执行成功。证明在事务1执行主键范围for update时，事务2对这个范围仍可以申请写锁。证明RC隔离级别没有使用Next-Key Lock，而是使用行级锁锁 住对应的记录。

场景四：RC隔离级别+普通索引

将事务隔离级别切为RC，开启事务1，保持未提交状态：

begin;
select * from z where b > 3 for update;

此时，若在新的事务2中运行下面的SQL语句：

insert into z select 8, 4;
update z set b = 10 where b = 6;
update z set b = 10 where b = 1;

第一个SQl语句，插入数据到事务1的for update范围内是可以的，因为这里事务1在RC模式下没有加Next-Key Lock锁，可以插入数据。

第二个SQL语句，会出现锁等待，选择普通索引作为条件，此时MySQL给普通索引b > 3的记录都会加行锁。同时，这些记录对应主键索引上的记录也都加上了锁。

第三个SQL语句，插入成功，说明b < 3的记录都无锁。

场景五：RR+无索引

InnoDB行锁是通过给索引上的索引项加锁来实现的，这一点MySQL与Oracle不同，后者是通过在数据块中对相应数据行加锁来实现的。InnoDB这种行锁实现 特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将会给所有记录加锁。

在RR隔离级别下，由于查询条件没有索引，那么InnoDB需要扫描所有数据来查找数据，对于扫描过的数据InnoDB都会加上锁，并且是加Next-Key Lock。

建立测试表：

create table tab_no_index(id int, b int) engine=innodb;
insert into tab_no_index values(1, 1), (2, 2), (3, 3), (4, 4), (100, 100);

开启事务1，保持未提交状态：

begin;
select * from tab_no_index where id = 1 for update;

执行下面的SQL：

insert into tab_no_index values(5, 5);

这时候，并不能插入成功。看起来事务1只给一行加了排他锁，但事务2在请求其他行的排他锁时，却出现了锁等待。原因就在于没有索引的情况下，InnoDB只能扫描 所有记录(锁住所有记录)。当我们给其增加一个唯一索引后，InnoDB就只锁定了符合条件的行。

当我们给其增加一个唯一索引后，InnoDB就只锁定了符合条件的行。

create table tab_with_index(id int, b int, primary key(id));
insert into tab_with_index values(1, 1), (2, 2), (3, 3), (4, 4), (100, 100);

开启事务1，保持未提交状态：

begin;
select * from tab_with_index where id = 1 for update;

执行下面的SQL：

select * from tab_with_index where id = 2 for update;

由这个例子可以看出，对于id是主键索引的情况下，只锁了id = 1这一样记录。其余的行都是可以进行DML操作的，但前提条件是以id为条件。如果是以b字段 为条件，那么还是会锁的。

场景六：RC+无索引

上面演示了在RR隔离级别下，对于where条件无索引的情况下，InnoDB是对所有记录加Next-Key Lock。

但是在RC隔离级别下，对于where条件无索引的情况下，则不会对所有记录加锁，而是只对命中的数据的聚簇索引加X锁。

同样针对tab_no_index表，开启事务1，保持未提交状态：

begin;
delete from tab_no_index where id = 1;

执行下面的SQL：

delete from tab_no_index where id = 99;
update tab_no_index set id = 2 where id = 100;
insert into tab_no_index select 99, 99;

都可以执行成功。

沙场练兵

下面两条简单的SQL，加什么锁？

select * from t1 where id = 10;
delete from t1 where id = 10;

错误的回答：

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

可能正确，也有可能错误，因为已知条件不足，要回答这个问题，还缺少几个前提条件：

id列是不是主键？
当前系统的隔离级别是什么？
id列如果不是主键，那么id列上有索引吗？
id列上如果有二级索引，那么这个索引是唯一索引吗？

组合一：id列是主键，RC隔离级别

这个组合，是最简单，最容易分析的组合。id是主键，Read Committed隔离级别，给定SQL：delete from t1 where id = 10;只需要将主键上，id = 10 的记录加上X锁即可。

组合二：id唯一索引+RC

id是unique索引，而主键是name列。由于id是unique索引，因此delete语句会选择id列的索引进行where条件的过滤，在找到id=10的记录后，首先会将unique 索引上的id=10索引记录加上X锁，同时，会根据读取到的name列，回主键索引(聚簇索引)，然后将聚簇索引上的name='d'对应的主键索引项加X锁。

为什么聚簇索引上的记录也要加锁？如果并发的一个SQL，是通过主键索引来更新：update t1 set id = 100 where name = 'd';此时，如果delete 语句没有将主键索引上的记录加锁，那么并发的update就会感知不到delete语句的存在，违背了同一记录上的更新/删除需要串行执行的约束。

若id列是unique列，其上有unique索引。那么SQL需要加两个X锁，一个对应于id unique索引上的id = 10的记录，另一把锁对应于聚簇索引上的[name='d', id=10] 的记录。

组合三：id非唯一索引+RC

相对于组合一、二，id列不在唯一，只有一个普通索引。满足id = 10查询条件的记录，均已加锁。同时，这些记录对应的主键索引上的记录也都加上了锁。 与组合二唯一的区别在于，组合二最多只有一个满足等值查询的记录，而组合三会将所有满足查询条件的记录都加锁。

组合四：id无索引+RC

这个过滤条件，没法通过索引进行过滤，那么只能走全表扫描做过滤。对应于这个组合，SQL会加什么锁？换句话说，全表扫描时，会加什么锁？

由于id列上没有索引，因此只能走聚簇索引，进行全部扫描。聚簇索引上所有的记录，都被加上了X锁。无论记录是否满足调教，全部被加上X锁。既不是加表锁， 也不是在满足条件的记录上加行锁。

为什么不是只在满足条件的记录上加锁呢？这是由于MySQL的实现决定的。如果一个条件无法通过索引快速过滤，那么存储引擎层面就会将所有记录加锁后返回， 然后有MySQL Server层进行过滤。因此也就把所有的记录都锁上了。

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

组合五：id主键+RR

上面的四个组合，都是在Read Committed隔离级别下的加锁行为，接下来的四个组合，是在Repeatable Read隔离级别下的加锁行为。

id列是主键列，Repeatable Read隔离级别，针对delete from t1 where id = 10;这条SQL，加锁与组合一一致。

组合六：id唯一索引+RR

与组合二一致。两个X锁，id唯一索引满足条件的记录上一个，对应的聚簇索引上的记录一个。

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

RC隔离级别允许幻读，而RR隔离级别，不允许存在幻读。那么RR隔离级别下，如何防止幻读呢？

组合七，Repeatable Read隔离级别，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锁 应运而生。

如图中所示，有哪些位置可以插入新的满足条件的项(id = 10)，考虑到B+Tree索引的有序性，满足条件的项一定是连续存放的。记录(6, c)之前，不会插入 id = 10的记录；(6, c)与(10, b)间、(10, b)与(10, d)间、(10, d)与(11, f)间可以插入满足条件的(10, e)、(10, z)等；而(11, f)之后也不会 插入满足条件的记录。

因此，为了保证(6, c)与(10, b)间、(10, b)与(10, d)间、(10, d)与(11, f)不会插入新的满足条件的记录，MySQL选择了用Gap锁，将这三个Gap给锁 起来。

Insert操作，如insert (10, a)，首先会定位到(6, c)与(10, b)间，然后在插入前，会检查这个Gap是否已经被锁上，如果被锁上，则Insert不能插入记录。 因此，通过第一遍的当前读，不仅将满足条件的记录锁上(X锁)，与组合三类似，同时还增加3把Gap锁，将可能插入满足条件记录的 3个Gap给锁上，保证后续的Insert不能插入新的id = 10的记录，也就杜绝了同一事务的第二次当前读，出现幻读的情况。

既然防止幻读，需要靠Gap锁保护，为什么组合五、组合六，也是RR隔离级别，却不需要加Gap锁呢？ Gap锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况。而组合五，id是主键；组合六，id是 unique键，都能够保证唯一性。一个等值查询，最多只能返回一条记录，而且新的相同取值的记录，一定不会有新记录插入进来，因此也就避免了Gap锁的使用。

结论

Repeatable Read隔离级别下，id列上有一个非唯一索引，对应SQL：delete from t1 where id = 10; 首先，通过id索引定位到第一条满足查询条件 的记录，在记录上加X锁，在Gap上加Gap锁，然后在主键聚簇索引上的加X锁，然后返回；然后读取下一条，重复进行。直至进行到第一条不满足条件的记录(11, f)， 此时，不需要在记录上加X锁，但是仍旧需要加Gap锁，最后返回结束。

组合八：id无索引+RR

id列上没有索引。此时SQL：delete from t1 where id = 10; 没有其他的路径可以选择，只能进行全表扫描。最终的加锁情况，如下图所示：

这是一个很恐怖的现象。首先，聚簇索引上的所有记录，都被加上了X锁；其次，聚簇索引每条记录键的间隙(Gap)，也同时被加上了Gap锁。这个示例表，只有6条 记录，共需要6个记录锁，7个Gap锁。试想，如果表上有1000万条记录呢？

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

当然，跟组合四类似，这种情况，MySQL也做了一些优化，就是所谓的semi-consistent read。semi-consistent read开启 的情况下，对于不满足查询条件的记录，MySQL会提前释放锁。

结论

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

组合九：Serializable

Serializable隔离级别，对于SQL2：delete from t1 where id = 10; 来说，Serializable隔离级别与Repeatable Read隔离级别完全一致。

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

MVCC最大的好处：读不加锁，读写不冲突。在读多写少的OLTP应用中，读写不冲突很关键，极大的增加了系统的并发性能，这也是为什么现阶段，几乎所有的 RDBMS，都支持MVCC。

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