InnoDB锁与隔离级别

2016 年 11 月 17 日, iamjs, 0

前面已经有一篇文章介绍过mysql锁机制了，这篇文章主要细致探索InnoDB锁的类型特性以及和隔离级别的关系。这篇文章所有的测试都以下面的表为基础：

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26

mysql> show create table lock_test;
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table     | Create Table                                                                                                                                                                   |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| lock_test | CREATE TABLE `lock_test` (
  `id` int(11) NOT NULL DEFAULT '0',
  `team` int(11) DEFAULT '0',
  PRIMARY KEY (`id`),
  KEY `team` (`team`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 |
+-----------+--------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)
mysql> 
select * from lock_test;
+----+------+
| id | team |
+----+------+
|  4 |    0 |
|  1 |    1 |
|  3 |    1 |
|  2 |    2 |
|  5 |    5 |
| 10 |   10 |
| 19 |   20 |
+----+------+
7 rows in set (0.00 sec)

一、InnoDB锁

下面将介绍以下几种锁的特点：

• Shared and Exclusive Locks
• Intention Locks
• Record Locks
• Gap Locks
• Next-Key Locks
• Insert Intention Locks
• AUTO-INC Locks

Shared and Exclusive Locks

共享锁与独占锁，InnoDB锁的基本种类，下面所讲的行锁与间隙锁也有S锁和X锁的种类区分。

共享独占锁兼容矩阵

Intention Locks

意向锁是一种很独特的表级锁，这种锁并不需要我们操作时考虑去加锁，而是mysql自动加锁。另外，这种锁虽然是一种表级锁，但是它的加锁是加在行上的，事务给数据行加记录锁时，会先申请给表加上对应的意向锁，意向锁之间并不冲突。 意向锁主要目的是为了在一个事务中揭示下一行将被请求的锁类型。如果没意向锁，需要表锁时，要一行行检查某个表是否发生了行锁，进而判断能否表锁成功，如果有了意向锁，不用一个个去检查，直接从表的层次就可判断。

意向锁兼容矩阵

Record Locks

顾名思义，这个就是InnoDB标志性的行锁，行锁是加在索引之上的，这里就不重复阐述了，记录锁可以区分为X和S两种。

Gap Locks

间隙锁，InnoDB引擎RR级别下的产物，间隙锁是加在行与行之间的缝隙上的，主要目的就是为了阻止幻象。RR级别下，给某一行记录加锁时，存储引擎会扫描到满足条件的行，并锁上该行以及前面的间隙（Next Key Locks），然后在扫到索引第一条不满足where条件的行之间锁上一个Gap锁，另外在涉及外键的地方也会有间隙锁存在，具体的会在下面下一键锁一起描述。Gap Locks也有S和X锁类型之分。

如何关闭间隙锁？方法一，隔离级别设置为RC；方法二，修改参数：innodb_locks_unsafe_for_binlog 设置为1。

Next-Key Locks

下一键锁，RR隔离级别下默认的加锁模式。由一个Gap锁和Record锁锁组成，是一个左开右闭的区间锁。下面在RR级别下对Record、Gap、Next key三种锁加锁场景进行一一举例：

• 唯一键上的间隙锁：

我们知道，间隙锁的存在就是为了解决幻象的问题，对于已存在行记录的查询，因为唯一性并不存在幻象问题，所以并不需要加上间隙锁。

例：select * from lock_test where id = 1 lock in share mode;

唯一键查询加record lock

但是如果查询的条件是主键上不存在的记录，这时候就必须要对该记录所在范围加Gap锁，否则会出现幻读现象。

例：select * from lock_test where id > 19 for update;

唯一索引上查询不存在记录需加Gap锁

•  非唯一键加Gap锁：

在非唯一索引上，查询不存在的范围时InnoDB会给索引加上Gap锁，例如select * from lock_test where team > 6 and team <=10，会在(6,10]这个范围加上一个Gap和Record锁。RR级别下默认加的锁便是Next Key Locks，下面我们看一下对于存在记录的查询InnoDB是怎么加锁的。

例：select * from lock_test where team = 2 for update;

Next Key Locks

• 更新涉及到边界时：

在加Next Key Locks的时候，两侧的Gap锁的边界是没有锁住的，这时候两端的记录是可以更新的，但是如果更新记录时会影响原先间隙锁范围，那么操作就要被挂起，等待间隙锁释放。

更新Gap锁边导致冲突

• update时加的锁：

update操作加锁情况，update语句除了跟select xxx for update加的锁一样，另外还要加多一个更新后记录的record锁。

update操作加的Gap锁

insert操作涉及到插入意向锁，在下面进行讨论。另外，还在何登成教授博客里面看到过一个delete操作引起的Gap锁冲突导致死锁的问题，具体可以跳转到一个最不可思议的MySQL死锁分析阅读。

Insert Intention Locks

InnoDB在执行insert操作时会对插入成功的行加上排它锁，这个排它锁是个record锁，而非next-key锁（当然更不是gap锁了），不会阻止其他并发的事务往这条记录之前插入记录。在插入之前，会先在插入记录所在的间隙加上一个插入意向gap锁（简称I锁吧），并发的事务可以对同一个gap加I锁。如果insert 的事务出现了duplicate-key error ，事务会对duplicate index record加共享锁。这个共享锁在并发的情况下是会产生死锁的，比如有两个并发的insert都对要对同一条记录加共享锁，而此时这条记录又被其他事务加上了排它锁，排它锁的事务提交或者回滚后，两个并发的insert操作是会发生死锁的。

插入意向锁

AUTO-INC Locks

自增加的锁，以保证自增操作的原子性和唯一性，本篇文章不对此锁进行讨论。

最后，我们记录一下InnoDB多个锁之间的兼容矩阵，有一点需要注意的是，Next Key Locks实际上也是一个Gap锁加Record锁，分析锁冲突时可以拆成两个锁来看待。

InnoDB锁兼容矩阵

二、隔离级别对数据一致性的实现

事务与隔离级别之间有什么关系？在mysql中隔离级别决定了事务的操作效果，事务可以看出是一串原子性的sql语句，如果在一个事务中多次用锁，是不是别的事务就不能使用与这些锁相冲突的？答案是否定的，这取决于隔离级别，他会决定一个事务什么时候加什么锁。

首先看看我们的SQL在各个隔离级别下采用的是什么一致性机制：

常规的select查询，实际上InnoDB在非Serializable级别下，并不会启用锁机制（行锁表锁），而是会启用MVCC的机制来保证并发。关于MVCC可以看这篇介绍MySQL数据库MVCC多版本并发控制简介（转）。下面将验证不同隔离级别对锁使用的影响以及对事务结果的影响。

RU：未提交读，最低等级的隔离级别，读不加任何锁，写加独占锁，因此进行读操作的时候会出现各种一致性读问题，高并发下数据极易出问题。

RC：已提交读，开了了MVCC机制，每个查询事务有自己的一个事务版本id号，根据事务的版本号来获取正确的数据，因此事务不会读到还未提交事务带来的数据影响。

RR：可重复读，此处MVCC级别高于RC中的级别，快照读中可以实现重复读，另外引入间隙锁的概念，在当前读中可以解决查询操作中幻读问题。（快照读指单纯的select语句，当前读指select … for update或select … lock in share mode，有了间隙锁就不用升级到S便可以支持数据可重复读）

S：序列化是最高的隔离级别，所有的读写操作都默认加上锁，可以解决所有一致性读问题，但是极大降低了并发能力。

• 验证RU不支持脏读：

RU下不支持脏读

可以看到未提交的事务一对数据的修改，影响到了事务二的读取。

• 验证RC隔离级别支持脏读、不支持可重复读

RC支持脏读不支持可重复读

可以看到RC模式下，对于未提交的事务一的数据修改，并不影响事务二，但是事务一一修改了，事务二就无法重复读了。

•  验证RR下支持可重复读，会出现幻象

RR支持可重复读、不支持幻读

由上面的例子可以看到，事务一在修改数据时并不会对事务二的快照读有什么影响。在测试幻读这里有个比较有意思的地方，在事务一新增数据后，事务二快照读当中并不会读取到幻读的数据，但是当前读存在幻象，也就是说不影响当前读返回的数据，但是会影响更新或修改的结果，暂时不明白。

• S级别会对所有读写加锁，应用场景不多，这里就不去验证了。

三、总结

实际上并不是所有的DML操作都会产生锁，InnoDB是支持MVCC这种乐观锁的，只有在指定当前读或者设置为S隔离级别情况下需要用悲观锁。

参考文章：

MySQL 加锁处理分析

MySQL innodb的锁机制解读

Insert into 加锁机制

Fiddler中显示IP的设置方法 两阶段锁协议