事务与隔离级别

前言

近两天在nCoV的影响下，公司下达任务的强度也下降了，就想趁着这段时间重温下SQL基础.在重温的过程中，一些原本自己认为已经掌握的很好的东西也渐渐模糊起来.
写这一系列主要是因为看到了github上的一个库mysql-deadlocks，主要记载了一些常见的MySQL死锁案例. 里面有关于死锁的博文， 在阅读后才发现自己对于SQL乃至锁相关的知识是多么的薄弱.所以这里题主就想着根据博文做一下学习笔记，加深自己对该部分的理解，共同学习共同进步.

事务的四要素ACID

• 原子性(Atomicity) : 要么全部完成，要么全部不完成
• 一致性(Consistency) : 一个事务单元要提交之后才会对其他事务可见
• 隔离性(Isolation) : 并发事务之间不会相互影响，设立了不同程度的隔离级别，通过适度破坏一致性，以提高性能
• 持久性(Durability) : 事务提交之后即持久化到磁盘上，不会丢失

事务存在的并发问题

现在有这样一张表，只有三个字段，主键id，姓名name和余额balance，其中name为二级索引

脏读 (dirty read)

假设有两个事务，一个在进行转账操作，将A账户下的100块转到B账户，同时有另一个事务再拍对A和B的账户余额进行求和统计，如下图所示:

事务1执行成功后，A余额900，B余额1100，总和为2000;但是事务2求和算出的结果是A+B=1900.这就是上面说的，没有提交的事务被其他事务读取到了，这就叫做脏读.

不可重复读 (unrepeatable read)

第二种场景叫不可重复读，事务2第一次获取A的账户余额为1000元，这个时候事务1对A的账户余额操作减去了100元，事务2再去查询A的账户余额发现变成了900元， 这样就出现了同一个事务对同一条记录读取两遍，两次读出的结果竟然不一样.

不可重复读和脏读的区别在于，脏读是读取了另一个事务未提交的修改，而不可重复读是读取了另一个事务提交之后的修改， 本质上都是其他事务的修改影响了本事务的读取.那么不可重复读有什么问题呢? 假设上面的事务2存在两个子例程，第一个例程是读取所有账户的余额计算总和， 可以得到所有人的余额总和为3000，第二个例程是读取所有账户的余额计算平均值，3个人总和3000按理应该是平均每人1000才对，却计算出了2900/3=966，这就导致了数据不一致.

幻读 (phantom read)

关于幻读，大概是事务中存在的并发问题中最难理解的部分了.关于幻读，最简单的解释是:同样的条件，第一次和第二次读出来的记录数不一样.(注意是记录数) 幻读和不可重复读的区别在于，后者是两次读取同一条数据，得到不一样的结果;而前者是两次读取同一个范围内的记录，得到不一样的记录数 (这种说法其实只是便于理解，但并不准确，因为可能存在另一个事务先插入一条记录然后再删除一条记录的情况，这个时候两次查询得到的记录数是相同的， 但这也是幻读，所以严格点的说法是:两次读取得到的结果集相同).

不可重复读是因为其他事务进行了UPDATE操作，幻读是因为其他事务进行了INSERT或者DELETE操作.

下面举一个幻读的例子:

在事务2的两次查询中，第一次查出两条记录，第二次却查出3条记录，多出来的这条记录，就像幽灵(phantom)一样.

丢失更新

上面说的三种情况，都是一个事务写，一个事务读，由于一个事务的写导致另一个事务读到了不该读到的数据;那么如果两个事务都是写，又会发生什么呢?

假设两个事务同时对A的余额进行修改，他们都会查出A的当前余额为1000，然后事务2修改A的余额，将A的余额加100变成了1100并提交， 这个时候A的余额应该是1100，但是这个时候事务1并不知道A的余额已经变动，而是继续在1000的基础上进行减100的操作并提交事务， 这样事务2的提交被覆盖掉了，事务1提交之后A的余额变成了900元.这就是说事务1的提交覆盖了事务2的提交，事务2的update操作就完全丢失了，整个过程如下图所示:

还有一种丢失更新为回滚覆盖，顾名思义，即事务1回滚覆盖了事务2已经提交的更新，回滚覆盖问题十分可怕，因此几乎所有数据库都不允许回滚覆盖。

有时候我们把回滚覆盖称之为第一类丢失更新问题，提交覆盖称为第二类丢失更新问题。

隔离级别

那么如何解决上述并发问题呢？最简单的办法是对所有操作加锁，写时不准其他事务读，读时不准其他事务写，完美的解决了并发问题，因为已经把并发扼杀在摇篮里了。 这就是四大隔离级别里的序列化，在序列化的隔离级别下，可以保证所有事务的安全之星，数据库的一致性得以保障，但性能毋庸置疑是最低的。

为了调和事务的安全性和性能的之间的冲突，适当的隔离借呗，可以有效的提高数据库的并发性能。于是便有了四种不同的隔离级别：

• 读未提交（Read Uncommitted) ： 可以读取未提交的记录，会出现脏读，幻读，不可重复读，等所有并发问题；
• 读已提交（Read Committed）：事务中只能看到已经提交的修改，不会出现脏读现象，但是会出现幻读，不可重复读（大多数数据库默认隔离级别是RC，MySQL为RR）
• 可重复读（Repeatable Read）：MySQL InnoDB 默认的隔离级别，解决了不可重复读问题，但是依然存在幻读问题；（MySQL实现由差异）
• 序列化（Serializable）：最高隔离级别，无任何并发问题。

针对这四种隔离级别，应该根据具体业务来取舍，如果某个系统的业务里根本就不会出现重复读的场景，完全可以将数据库的隔离级别设置为RC，这样可以最大程度提高数据库的并发性。 不同的隔离级别和可能发生的并发问题如下表：

其中，在 可重复读（RR） 隔离级别下，是否可能出现第二类丢失更新问题（提交覆盖）比较有争议， 有的人认为提交覆盖问题其实是不可重复读问题的一种特殊形式，所以在 RR 隔离级别解决了不可重复读，自然也不可能出现提交覆盖。 在标准的传统实现中，RR 隔离级别是使用持续的 X 锁和持续的 S 锁来实现的， 由于是持续的 S 锁，所以避免了其他事务有写操作，也就不存在提交覆盖问题。但是 MySQL 在 RR 隔离级别下， 普通的 SELECT 语句只是快照读，没有任何的加锁，和标准的 RR 是不一样的。如果要让 MySQL 在 RR 隔离级别下不发生提交覆盖， 可以使用 SELECT … LOCK IN SHARE MODE 或者 SELECT … FOR UPDATE 。

隔离级别的实现

上面所说的事务和隔离级别的概念，其实都是SQL标准红通用的概念，但是不同的数据库产品对标准的实现也会有很大不同。 譬如在SQL标准中，RR隔离级别解决了不可重复读问题，但依然存在幻读现象；而在MySQL的RR隔离级别下，通过多版本快照读和间隙锁技术解决了幻读问题。

传统的隔离级别

上面说了很多，其实我一直在刻意的避免谈到锁，因为隔离级别和锁本身就是两个东西，SQL规范中定义的四种隔离界别，分别是为了解决事务并发时可能会遇到的四种问题， 至于如何解决，实现方式是什么，规范中并没有严格定义。锁作为最简单最显而易见的实现方式，可能被广为人知，所以大家在讨论某个隔离级别的时候，往往会说这个隔离级别的加锁方式是什么样的。 其实，锁只是实现隔离级别的几种方式之一，除了锁，实现并发问题的方式还有时间戳，多版本控制等等，这些也可以称为无锁的并发控制。

传统的隔离级别是基于锁实现的，这种方式叫做基于锁的并发控制（Lock-Based Concurrent Control，简写 LBCC）。 通过对读写操作加不同的锁，以及对释放锁的时机进行不同的控制，就可以实现四种隔离级别。传统的锁有两种：读操作通常加共享锁（Share locks，S锁，又叫读锁）， 写操作加排它锁（Exclusive locks，X锁，又叫写锁）；加了共享锁的记录，其他事务也可以读，但不能写；加了排它锁的记录，其他事务既不能读，也不能写。 另外，对于锁的粒度，又分为行锁和表锁，行锁只锁某行记录，对其他行的操作不受影响，表锁会锁住整张表，所有对这个表的操作都受影响。

归纳起来，四种隔离级别的加锁策略如下：

• 读未提交（Read Uncommitted）：事务读不阻塞其他事务读和写，事务写阻塞其他事务写但不阻塞读；通过对写操作加 “持续X锁”，对读操作不加锁 实现；
• 读已提交（Read Committed）：事务读不会阻塞其他事务读和写，事务写会阻塞其他事务读和写；通过对写操作加 “持续X锁”，对读操作加 “临时S锁” 实现；不会出现脏读；
• 可重复读（Repeatable Read）：事务读会阻塞其他事务事务写但不阻塞读，事务写会阻塞其他事务读和写；通过对写操作加 “持续X锁”，对读操作加 “持续S锁” 实现；
• 序列化（Serializable）：为了解决幻读问题，行级锁做不到，需使用表级锁。

结合上面介绍的每种隔离级别分别是用来解决事务并发中的什么问题，再来看看它的加锁策略其实都挺有意思的。其中 读未提交 网上有很多人认为不需要加任何锁，这其实是错误的， 我们上面讲过，有一种并发问题在任何隔离级别下都不允许存在，那就是第一类丢失更新（回滚覆盖），如果不对写操作加 X 锁，当两个事务同时去写某条记录时，可能会出现丢失更新问题， 这里 有一个例子可以看到写操作不加 X 锁发生了回滚覆盖。再看 读已提交，它是为了解决脏读问题，只能读取已提交的记录，要怎么做才可以保证事务中的读操作读到的记录都是已提交的呢？ 很简单，对读操作加上 S 锁，这样如果其他事务有正在写的操作，必须等待写操作提交之后才能读，因为 S 和 X 互斥，如果在读的过程中其他事务想写，也必须等事务读完之后才可以。 这里的 S 锁是一个临时 S 锁，表示事务读完之后立即释放该锁，可以让其他事务继续写，如果事务再读的话，就可能读到不一样的记录，这就是 不可重复读 了。为了让事务可以重复读， 加在读操作的 S 锁变成了持续 S 锁，也就是直到事务结束时才释放该锁，这可以保证整个事务过程中，其他事务无法进行写操作，所以每次读出来的记录是一样的。 最后，序列化 隔离级别下单纯的使用行锁已经实现不了，因为行锁不能阻止其他事务的插入操作，这就会导致幻读问题，这种情况下， 我们可以把锁加到表上（也可以通过范围锁来实现，但是表锁就相当于把表的整个范围锁住，也算是特殊的范围锁吧）。

从上面的描述可以看出，通过对锁的类型（读锁还是写锁），锁的粒度（行锁还是表锁），持有锁的时间（临时锁还是持续锁）合理的进行组合，就可以实现四种不同的隔离级别。 这四种不同的加锁策略实际上又称为 封锁协议（Locking Protocol），所谓协议，就是说不论加锁还是释放锁都得按照特定的规则来。 读未提交 的加锁策略又称为 一级封锁协议，后面的分别是二级，三级，序列化 的加锁策略又称为 四级封锁协议。

其中三级封锁协议在事务的过程中为写操作加持续 X 锁，为读操作加持续 S 锁，并且在事务结束时才对锁进行释放， 像这种加锁和解锁明确的分成两个阶段我们把它称作 两段锁协议（2-phase locking，简称 2PL）。 在两段锁协议中规定，加锁阶段只允许加锁，不允许解锁；而解锁阶段只允许解锁，不允许加锁。 这种方式虽然无法避免死锁，但是两段锁协议可以保证事务的并发调度是串行化的（关于串行化是一个非常重要的概念，尤其是在数据恢复和备份的时候）。 在两段锁协议中，还有一种特殊的形式，叫 一次封锁，意思是指在事务开始的时候，将事务可能遇到的数据全部一次锁住，再在事务结束时全部一次释放，这种方式可以有效的避免死锁发生。 但是这在数据库系统中并不适用，因为事务开始时并不知道这个事务要用到哪些数据，一般在应用程序中使用的比较多。

MySQL的隔离级别

虽然数据库的四种隔离级别通过 LBCC 技术都可以实现，但是它最大的问题是它只实现了并发的读读，对于并发的读写还是冲突的，写时不能读，读时不能写，当读写操作都很频繁时，数据库的并发性将大大降低，针对这种场景，MVCC 技术应运而生。 MVCC 的全称叫做 Multi-Version Concurrent Control（多版本并发控制），InnoDb 会为每一行记录增加几个隐含的“辅助字段”，（实际上是 3 个字段：一个隐式的 ID 字段，一个事务 ID，还有一个回滚指针）， 事务在写一条记录时会将其拷贝一份生成这条记录的一个原始拷贝，写操作同样还是会对原记录加锁，但是读操作会读取未加锁的新记录，这就保证了读写并行。要注意的是，生成的新版本其实就是 undo log，它也是实现事务回滚的关键技术。 关于 InnoDb 的 MVCC 机制的实现原理推荐看下这篇文章：MySQL数据库事务各隔离级别加锁情况–read committed && MVCC。

InnoDb 通过 MVCC 实现了读写并行，但是在不同的隔离级别下，读的方式也是有所区别的。 首先要特别指出的是，在 read uncommit 隔离级别下，每次都是读取最新版本的数据行，所以不能用 MVCC 的多版本，而 serializable 隔离级别每次读取操作都会为记录加上读锁， 也和 MVCC 不兼容，所以只有 RC 和 RR 这两个隔离级别才有 MVCC。下面我们通过两个例子，来看看这两个隔离级别下 MVCC 有什么区别。

还是使用上面的 account 表，首先将 MySQL 的事务隔离级别设置为 RR，然后开启一个事务，执行下面的 SQL：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)
 
mysql> update account set balance = 2000 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
 
mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  2000   |
+----+----------+---------+
1 row in set (0.00 sec)

开启第二个事务，如下：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

可以看到事务 2 查询出来的还是 A 原始的信息，这个时候事务 1 使用 commit 提交：

mysql> commit;
Query OK, 0 rows affected (0.05 sec)

并在事务 2 中再查询一次，发现查询出来的结果不变，还是原始值：

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

同样的，我们将数据库的隔离级别设置为 RC，重新做一次这个实验，第一步都是一样的，只是第二步当事务 1 commit 之后，事务 2 再查一次发现查询出来的是最新提交的记录了：

mysql> select * from account where id = 1;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  1 |        A |  2000   |
+----+----------+---------+
1 row in set (0.00 sec)

通过这个实验可以知道，尽管 RR 和 RC 隔离级别都实现了 MVCC 来满足读写并行，但是读的实现方式是不一样的：RC 总是读取记录的最新版本，如果该记录被锁住，则读取该记录最新的一次快照， 而 RR 是读取该记录事务开始时的那个版本。 虽然这两种读取方式不一样，但是它们读取的都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read）， 有时候也叫做 非阻塞读（Nonlocking Read），RR 隔离级别下的叫做 一致性非阻塞读（Consistent Nonlocking Read）。

除了 快照读 ，MySQL 还提供了另一种读取方式：当前读（Current Read），有时候又叫做 加锁读（Locking Read） 或者 阻塞读（Blocking Read）， 这种读操作读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据加锁的不同，又分成两类：

• SELECT … LOCK IN SHARE MODE：加 S 锁
• SELECT … FOR UPDATE：加 X 锁
• INSERT / UPDATE / DELETE：加 X 锁

当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题， 关于记录锁和间隙锁的概念后面再讲，有兴趣的同学可以和上面一样，通过两个例子来感受一下。

首先在 RC 隔离级别下，开启一个事务，执行下面的 SQL 查询所有 id > 3 的记录，使用当前读而不是快照读：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account where id > 3 lock in share mode;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  4 |        D |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

同时，开启另一个事务，向 account 表中新增一条记录，然后修改 id = 4 的记录：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into account(name, balance) value('E', 1000);
Query OK, 1 row affected (6.24 sec)
 
mysql> update account set balance = 2000 where id = 4;

可以看到事务 2 在事务 1 当前读之后，仍然可以新增记录，但是在执行 update 操作的时候被阻塞，这说明了事务 1 在执行当前读的时候在 id = 4 这条记录上加了锁， 但是并没有对 id > 3 这个范围加锁。然后我们切换到事务 1 中，再次查询：

mysql> select * from account where id > 3 lock in share mode;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

这个时候事务 1 直接死锁了，原因其实很简单，事务 2 在 insert 新纪录的时候（id = 5）会在新纪录上加锁， 所以事务 1 再次执行当前读，想获取 id > 3 的记录，就需要在 id = 4 和 id = 5 这两条记录上加锁，但是 id = 5 这条记录已经被事务 2 锁住了， 于是事务 1 被事务 2 阻塞，同时事务 2 还在等待 事务 1 释放 id = 4 上的锁，于是便产生了死锁。

接下来把隔离级别设置成 RR，再重复事务 1 刚刚的操作：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> select * from account where id > 3 lock in share mode;
+----+----------+---------+
| id |     name | balance |
+----+----------+---------+
|  4 |        D |  1000   |
+----+----------+---------+
1 row in set (0.00 sec)

同时，事务 2 向 account 表中新增一条记录：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
 
mysql> insert into account(name, balance) value('E', 1000);

我们发现，这个时候事务 2 就被阻塞了，很显然事务 1 在执行 select … lock in share mode 的时候，不仅在 id = 4 这条记录上加了锁，而且在 id > 3 这个范围上也加了锁。 关于 MySQL 不同的隔离级别，读操作的差异总结起来如下图所示（其中，读未提交和可序列化都和 MVCC 不兼容，可以暂且认为它们都属于当前读）：

Reference

解决死锁之路

转载

本文遵循 CC 4.0 by-sa 版权协议,转载请附上原文出处链接和本声明。

• Previous
  crontab定时任务
• Next
  常见的锁类型