数据库事务隔离级别及mysql实现方案

1、数据库的并发问题

以下几个概念是事务隔离级别要实际解决的问题，所以需要搞清楚都是什么意思。

脏读：读到了其他事务未提交的数据，

不可重复读：在一个事务内，多次读取的同一批数据出现不一致的情况。

幻读：在一个事务内，多次读取的数据集对象不一致。

2、事务隔离级别

上述这些问题，其实就是数据库读一致性问题，由数据库提供的事务隔离机制来进行解决。

隔离级别越高，一致性越好，性能越差。

默认事务隔离级别为可重复读(RR),oracle默认事务隔离级别为读已提交(RC)。

3、mysql的锁类型

3.1、从锁的对象大小角度：

表锁：

行锁：

页锁：

3.2、排他锁和共享锁

InnoDB 实现了标准的行级锁，包括两种：共享锁（简称 s 锁）、排它锁（简称 x 锁）。

对于共享锁而言，对当前行加共享锁，不会阻塞其他事务对同一行的读请求，但会阻塞对同一行的写请求。只有当读锁释放后，才会执行其它事物的写操作。

对于排它锁而言，会阻塞其他事务对同一行的读和写操作，只有当写锁释放后，才会执行其它事务的读写操作。

对于InnoDB 在RR(MySQL默认隔离级别) 而言，对于 update、delete 和 insert 语句，会自动给涉及数据集加排它锁（X）；

对于普通 select 语句，innodb 不会加任何锁。如果想在select操作的时候加上 S锁或者 X锁，需要我们手动加锁。

3.3、行锁的细分：

记录锁（Record Locks）：

对表中的记录加锁，叫做记录锁，简称行锁。比如

SELECT * FROM `test` WHERE `id`=1 FOR UPDATE;

它会在 id=1 的记录上加上记录锁，以阻止其他事务插入，更新，删除 id=1 这一行。

需要注意的是：

id 列必须为唯一索引列或主键列，否则上述语句加的锁就会变成临键锁(有关临键锁下面会讲)。
同时查询语句必须为精准匹配（=），不能为 >、<、like等，否则也会退化成临键锁。

间隙锁（Gap Locks）：

间隙锁是 Innodb 在 RR(可重复读) 隔离级别下为了解决幻读问题时引入的锁机制。间隙锁是innodb中行锁的一种。

请务必牢记：使用间隙锁锁住的是一个区间，而不仅仅是这个区间中的每一条数据。

举例来说，假如emp表中只有101条记录，其empid的值分别是1,2,…,100,101，下面的SQL：

SELECT * FROM emp WHERE empid > 100 FOR UPDATE

当我们用条件检索数据，并请求共享或排他锁时，InnoDB不仅会对符合条件的empid值为101的记录加锁，也会对empid大于101（这些记录并不存在）的“间隙”加锁。

这个时候如果你插入empid等于102的数据的，如果那边事物还没有提交，那你就会处于等待状态，无法插入数据。

临键锁（Next-Key Locks）：

Next-key锁是记录锁和间隙锁的组合，它指的是加在某条记录以及这条记录前面间隙上的锁。

也可以理解为一种特殊的间隙锁。通过临建锁可以解决幻读的问题。每个数据行上的非唯一索引列上都会存在一把临键锁，当某个事务持有该数据行的临键锁时，会锁住一段左开右闭区间的数据。

需要强调的一点是，InnoDB 中行级锁是基于索引实现的，临键锁只与非唯一索引列有关，在唯一索引列（包括主键列）上不存在临键锁

假设有如下表：id主键, age 普通索引

该表中 age 列潜在的临键锁有：
(-∞, 10],
(10, 24],
(24, 32],
(32, 45],
(45, +∞],
在事务 A 中执行如下命令：

-- 根据非唯一索引列 UPDATE 某条记录
UPDATE table SET name = Vladimir WHERE age = 24;
-- 或根据非唯一索引列 锁住某条记录
SELECT * FROM table WHERE age = 24 FOR UPDATE;

不管执行了上述 SQL 中的哪一句，之后如果在事务 B 中执行以下命令，则该命令会被阻塞：

INSERT INTO table VALUES(100, 26, 'tianqi');

很明显，事务 A 在对 age 为 24 的列进行 UPDATE 操作的同时，也获取了 (10, 32] 这个区间内的**临键锁**。

`总结：`这里对记录锁、间隙锁、临键锁做一个总结

InnoDB 中的行锁的实现依赖于索引，一旦某个加锁操作没有使用到索引，那么该锁就会退化为表锁。
记录锁存在于包括主键索引在内的唯一索引中，锁定单条索引记录。
间隙锁存在于非唯一索引中，锁定开区间范围内的一段间隔，它是基于临键锁实现的。
临键锁存在于非唯一索引中，该类型的每条记录的索引上都存在这种锁，它是一种特殊的间隙锁，锁定一段左开右闭的索引区间。

3.4、意向锁

意向锁主要是用来解决在加表级锁时，避免一行一行的查询该表是否存在其他锁。

分为意向共享锁(IS) 和意向排他锁(IX)

意向共享(IS)锁：事务有意向对表中的某些行加共享锁（S锁）

-- 事务要获取某些行的 S 锁，必须先获得表的 IS 锁。
SELECT column FROM table ... LOCK IN SHARE MODE;

意向排他(IX)锁：事务有意向对表中的某些行加排他锁（X锁）

-- 事务要获取某些行的 X 锁，必须先获得表的 IX 锁。
SELECT column FROM table ... FOR UPDATE;

首先我们要明白四点

意向共享锁（IS）和意向排他锁（IX）都是表锁。
意向锁是一种不与行级锁冲突的表级锁，这一点非常重要。
意向锁是 InnoDB 自动加的，不需用户干预。
意向锁是在 InnoDB 下存在的内部锁，对于MyISAM 而言没有意向锁之说。

这里就会有疑惑，既然前面已经有了共享锁（S锁）、排它锁（X锁）。那么为什么需要引入意向锁呢？它能解决什么问题呢？

我们可以理解意向锁存在的目的就是为了让InnoDB 中的行锁和表锁更高效的共存。

为什么这么说，我们来举一个例子。下面有一张表 id是主键

事务 A 获取了某一行的排他锁，并未提交：

SELECT * FROM users WHERE id = 6 FOR UPDATE;

事务 B 想要获取users表的表锁：

LOCK TABLES users READ;

因为共享锁与排他锁互斥，所以事务 B 在视图对 users 表加共享锁的时候，必须保证：

当前没有其他事务持有 users 表的排他锁。
当前没有其他事务持有 users 表中任意一行的排他锁。

为了检测是否满足第二个条件，事务 B 必须在确保users表不存在任何排他锁的前提下，去检测表中的每一行是否存在排他锁。很明显这是一个效率很差的做法，但是有了意向锁之后，情况就不一样了：事务B只要看表上有没有意向共享锁，有则说明表中有些行被共享行锁锁住了，因此，事务B申请表的写锁会被阻塞。这样是不是就高效多了。

这里我们再来看下共享(S)锁、排他(X)锁、意向共享锁（IS）、意向排他锁（IX）的兼容性

可以看出意向锁之间是互相兼容的.那你存在的意义是啥？

意向锁不会为难意向锁。也不会为难行级排他(X)/共享(X)锁,它的存在是为难表级排他(X)/共享(X)锁。

注意这里的排他(X)/共享(S)锁指的都是表锁！意向锁不会与行级的共享/排他锁互斥！

意向锁与意向锁之间永远是兼容的，因为当你不论加行级的X锁或S锁，都会自动获取表级的IX锁或者IS锁。也就是你有10个事务，对不同的10行加了行级X锁，那么这个时候就存在10个IX锁。

这10个IX存在的作用是啥呢，就是假如这个时候有个事务，想对整个表加排它X锁,那它不需要遍历每一行是否存在S或X锁，而是看有没有存在**意向锁**，只要存在一个意向锁，那这个事务就加不了表级排它(X)锁，要等上面10个IX全部释放才行。

插入意向锁

在讲解插入意向锁之前，先来思考一个问题

下面有张表 id主键，age普通索引

首先事务 A 插入了一行数据，并且没有 commit：

INSERT INTO users SELECT 4, 'Bill', 15;

随后事务 B 试图插入一行数据：

INSERT INTO users SELECT 5, 'Louis', 16;

请问：

1、事务A使用了什么锁？

2、事务 B 是否会被事务 A 阻塞？

插入意向锁是在插入一条记录行前，由 INSERT 操作产生的一种间隙锁。

该锁用以表示插入意向，当多个事务在同一区间（gap）插入位置不同的多条数据时，事务之间不需要互相等待。

假设存在两条值分别为 4 和 7 的记录，两个不同的事务分别试图插入值为 5 和 6 的两条记录，每个事务在获取插入行上独占的（排他）锁前，都会获取（4，7]之间的间隙锁，但是因为数据行之间并不冲突，所以两个事务之间并不会产生冲突（阻塞等待）。

总结来说，插入意向锁的特性可以分成两部分：

插入意向锁是一种特殊的间隙锁 —— 间隙锁可以锁定开区间内的部分记录。
插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待。

虽然插入意向锁中含有意向锁三个字，但是它并不属于意向锁而属于间隙锁，因为意向锁是表锁而插入意向锁是行锁。

现在我们可以回答开头的问题了：

1、使用插入意向锁与记录锁。
2、事务 A 不会阻塞事务 B。

为什么不用间隙锁

如果只是使用普通的间隙锁会怎么样呢？我们在看事务A,其实它一共获取了3把锁

id 为 4 的记录行的记录锁。
age 区间在（10，15] 的间隙锁。
age 区间在（15，20] 的间隙锁。

最终，事务 A 插入了该行数据，并锁住了（10，20] 这个区间。

随后事务 B 试图插入一行数据：

INSERT INTO users SELECT 5, 'Louis', 16;

因为 16 位于（15，20] 区间内，而该区间内又存在一把间隙锁，所以事务 B 别说想申请自己的间隙锁了，它甚至不能获取该行的记录锁，自然只能乖乖的等待事务 A结束，才能执行插入操作。

很明显，这样做事务之间将会频发陷入阻塞等待，插入的并发性非常之差。这时如果我们再去回想我们刚刚讲过的插入意向锁，就不难发现它是如何优雅的解决了并发插入的问题。

总结

InnoDB在RR的事务隔离级别下，使用插入意向锁来控制和解决并发插入。
插入意向锁是一种特殊的间隙锁。
插入意向锁在锁定区间相同但记录行本身不冲突的情况下互不排斥。

4、mysql事务隔离的实现方案

4.1、实现可重复读

为了解决不可重复读，或者为了实现可重复读，MySQL 采用了 MVVC (多版本并发控制) 的方式。

我们在数据库表中看到的一行记录可能实际上有多个版本，每个版本的记录除了有数据本身外，还要有一个表示版本的字段，记为 row trx_id，而这个字段就是使其产生的事务的 id，事务 ID 记为 transaction id，它在事务开始的时候向事务系统申请，按时间先后顺序递增。

按照上面这张图理解，一行记录现在有 3 个版本，每一个版本都记录这使其产生的事务 ID，比如事务A的transaction id 是100，那么版本1的row trx_id 就是 100，同理版本2和版本3。

在上面介绍读提交和可重复读的时候都提到了一个词，叫做快照，学名叫做一致性视图，这也是可重复读和不可重复读的关键，可重复读是在事务开始的时候生成一个当前事务全局性的快照，而读提交则是每次执行语句的时候都重新生成一次快照。

对于一个快照来说，它能够读到那些版本数据，要遵循以下规则：

当前事务内的更新，可以读到；
版本未提交，不能读到；
版本已提交，但是却在快照创建后提交的，不能读到；
版本已提交，且是在快照创建前提交的，可以读到；

利用上面的规则，再返回去套用到读提交和可重复读的那两张图上就很清晰了。还是要强调，两者主要的区别就是在快照的创建上，可重复读仅在事务开始是创建一次，而读提交每次执行语句的时候都要重新创建一次。

4.2、并发写问题

存在这的情况，两个事务，对同一条数据做修改。最后结果应该是哪个事务的结果呢，肯定要是时间靠后的那个对不对。并且更新之前要先读数据，这里所说的读和上面说到的读不一样，更新之前的读叫做“当前读”，总是当前版本的数据，也就是多版本中最新一次提交的那版。

假设事务A执行 update 操作， update 的时候要对所修改的行加行锁，这个行锁会在提交之后才释放。而在事务A提交之前，事务B也想 update 这行数据，于是申请行锁，但是由于已经被事务A占有，事务B是申请不到的，此时，事务B就会一直处于等待状态，直到事务A提交，事务B才能继续执行，如果事务A的时间太长，那么事务B很有可能出现超时异常。如下图所示。

加锁的过程要分有索引和无索引两种情况，比如下面这条语句

update user set age=11 where id = 1

id 是这张表的主键，是有索引的情况，那么 MySQL 直接就在索引数中找到了这行数据，然后干净利落的加上行锁就可以了。

而下面这条语句

update user set age=11 where age=10

表中并没有为 age 字段设置索引，所以， MySQL 无法直接定位到这行数据。那怎么办呢，当然也不是加表锁了。MySQL 会为这张表中所有行加行锁，没错，是所有行。但是呢，在加上行锁后，MySQL 会进行一遍过滤，发现不满足的行就释放锁，最终只留下符合条件的行。虽然最终只为符合条件的行加了锁，但是这一锁一释放的过程对性能也是影响极大的。所以，如果是大表的话，建议合理设计索引，如果真的出现这种情况，那很难保证并发度。

4.3、解决幻读

上面介绍可重复读的时候，那张图里标示着出现幻读的地方实际上在 MySQL 中并不会出现，MySQL 已经在可重复读隔离级别下解决了幻读的问题。

前面刚说了并发写问题的解决方式就是行锁，而解决幻读用的也是锁，叫做间隙锁，MySQL 把行锁和间隙锁合并在一起，解决了并发写和幻读的问题，这个锁叫做 Next-Key锁。

假设现在表中有两条记录，并且 age 字段已经添加了索引，两条记录 age 的值分别为 10 和 30。

此时，在数据库中会为索引维护一套B+树，用来快速定位行记录。B+索引树是有序的，所以会把这张表的索引分割成几个区间。

如图所示，分成了3 个区间，(负无穷,10]、(10,30]、(30,正无穷]，在这3个区间是可以加间隙锁的。

之后，我用下面的两个事务演示一下加锁过程。

在事务A提交之前，事务B的插入操作只能等待，这就是间隙锁起得作用。当事务A执行update user set name='风筝2号’ where age = 10; 的时候，由于条件 where age = 10 ，数据库不仅在 age =10 的行上添加了行锁，而且在这条记录的两边，也就是(负无穷,10]、(10,30]这两个区间加了间隙锁，从而导致事务B插入操作无法完成，只能等待事务A提交。不仅插入 age = 10 的记录需要等待事务A提交，age<10、10<age<30 的记录页无法完成，而大于等于30的记录则不受影响，这足以解决幻读问题了。

这是有索引的情况，如果 age 不是索引列，那么数据库会为整个表加上间隙锁。所以，如果是没有索引的话，不管 age 是否大于等于30，都要等待事务A提交才可以成功插入。

4.4、总结

MySQL 的 InnoDB 引擎才支持事务，其中可重复读是默认的隔离级别。

读未提交和串行化基本上是不需要考虑的隔离级别，前者不加锁限制，后者相当于单线程执行，效率太差。

读提交解决了脏读问题，行锁解决了并发更新的问题。并且 MySQL 在可重复读级别解决了幻读问题，是通过行锁和间隙锁的组合 Next-Key 锁实现的。

5、mysql修改隔离级别

修改隔离级别的语句是：set [作用域] transaction isolation level [事务隔离级别]，
SET [SESSION | GLOBAL] TRANSACTION ISOLATION LEVEL {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE}。

其中作用于可以是 SESSION 或者 GLOBAL，GLOBAL 是全局的，而 SESSION 只针对当前回话窗口。隔离级别是 {READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE} 这四种，不区分大小写。

比如下面这个语句的意思是设置全局隔离级别为读提交级别。

mysql> set global transaction isolation level read committed;

参考文献：

一文详解MySQL的锁机制 (qq.com)

看一遍就懂：MVCC原理详解 (qq.com)

InnoDB中的意向锁，不与行级锁冲突的表级锁-腾讯云开发者社区-腾讯云 (tencent.com)

MySQL事务隔离级别和实现原理（看这一篇文章就够了！） - 知乎 (zhihu.com)