我所理解的MySQL之四：事务、隔离级别及MVCC

mysql教程栏目介绍MySQL相关的事务、隔离级别及MVCC。

yours=yours+50=150+50=200my=50yours=2007endendmy=50yours=200

两次转账后，最终的结果是我的账户余额为50元，你的账户余额为200元，这显然是不对的。

而如果在保证事务隔离性的情况下，就不会发生上面的情况，损失的只是一定程度上的一致性。

1.4 持久性（Durability）

事务的持久性是指：事务在提交以后，它所做的修改就会被永久保存到数据库。

在上述的转账场景中，持久性就保证了在转账成功之后，我的账户余额为0，你的账户余额为200。

2. 自动提交与隐式提交

2.1 自动提交

在 MySQL 中，我们可以通过 begin 或 start transaction 来开启事务，通过 commit 来关闭事务，如果 SQL 语句中没有这两个命令，默认情况下每一条 SQL 都是一个独立的事务，在执行完成后自动提交。

比如：

update user set name=&＃39;重塑&＃39; where id=1; 

假设我只执行这一条更新语句，在我关闭 MySQL 客户端然后重新打开一个新的客户端后，可以看到 user 表中的 name 字段值全变成了「重塑」，这也印证了这条更新语句在执行后已被自动提交。

自动提交是 MySQL 的一个默认属性，可以通过 SHOW VARIABLES LIKE &＃39;autocommit&＃39; 语句来查看，当它的值为 ON 时，就代表开启事务的自动提交。

mysql> SHOW VARIABLES LIKE &＃39;autocommit&＃39;;
+---------------+-------+| Variable_name | Value |
+---------------+-------+| autocommit    | ON    |
+---------------+-------+1 row in set (0.00 sec) 

我们可以通过 SET autocommit = OFF 来关闭事务的自动提交。

2.2 隐式提交

然而，即便我们已经将 autocommit 变量的值改为 OFF 关闭事务自动提交了，在执行某些 SQL 语句的时候，MySQL 还是会将事务自动提交掉，这被称为隐式提交。

会触发隐式提交的 SQL 语句有：

• DDL（Data definition language，数据定义语言），如 create, drop, alter, truncate
• 修改 MySQL 自带表数据的语句，如 create/drop user, grant, set password
• 在一个事务中，开启一个新的事务，会隐式提交上一个事务，如：

在事务B中有两个查询语句N1和N2，执行的结果是N1=刺猬，N2=重塑，由此可以证明。

• 其他还有一些管理语句就不一一举例了，可自行百度。

3. 隔离级别

事务的隔离级别规定了一个事务中所做的修改，在事务内和事务间的可见性。较低级别的隔离通常可以执行更高的并发，系统开销也更低。

在 SQL 标准中定义了四种事务的隔离级别，分别是读未提交（Read Uncommitted）、读已提交（Read Committed）、可重复读（Repeatable Read）、可串行化（Serializable）。

为了详细解释这四种隔离级别及它们各自发生的现象，假设有两个事务即将执行，执行内容如下表：

在事务A和事务B执行的过程中，有三处查询 N1,N2,N3，在每个隔离级别下，它们值的情况是不同的，下面分别讨论。

3.1 读未提交（Read Uncommitted）

在读未提交的隔离级别下，事务中的修改，即便没有提交，对其他事务也都是可见的。

在上述场景中，若数据库的隔离级别为读未提交，由于事务A可以读取未提交事务B修改后的数据，即时刻3中事务B的修改对事务A可见，所以N1=重塑，N2=重塑，N3=重塑。

3.2 读已提交（Read Committed）

在读已提交的隔离级别下，事务中的修改只有在提交之后，才会对其他事务可见。

在上述场景中，若数据库的隔离级别为读已提交，由于事务A只能读取事务B提交后的数据，即时刻3中事务B的修改对事务A不可见，N2处的查询在事务B提交之后，故对事务A可见。所以N1=刺猬，N2=重塑，N3=重塑。

3.3 可重复读（Repeatable Read）

可重复读是 MySQL 的默认事务隔离级别。在可重复读的隔离级别下，一个事务中多次查询相同的记录，结果总是一致的。

在上述场景中，若数据库的隔离级别为可重复读，由于查询N1和N2在一个事务中，所以它们的值都是「刺猬」，而N3是在事务A提交以后再进行的查询，对事务B的修改是可见的，所以N3=重塑。

3.4 可串行化（Serializable）

在可串行化的隔离级别下，事务都是串行执行的，读会加读锁，写会加写锁，事务不会并发执行，所以也就不会发生异常情况。

在上述场景中，若数据库的隔离级别为可串行化，首先开启事务A，在开启事务B时被阻塞，直到事务A提交之后才会开启事务B，所以N1=刺猬，N2=刺猬。而N3处的查询会在事务B提交之后才执行（事务B先被阻塞，执行顺序在N3查询语句之前），所以N3=重塑。

4. 隔离级别导致的问题

在不同的事务隔离级别中，如果遇到事务并发执行，就会出现很多问题，如脏读（Dirty Read）、不可重复读（Non-Repeatable Read）、幻读（Phantom Read）等，下面就分别用不同的例子来详细说明这些问题。

4.1 脏读（Dirty Read）

脏读（Dirty Read）是指一个事务可以读取另一个未提交事务修改的数据。

看下面的案例，假设隔离级别为读未提交：

在读未提交的隔离级别下，N1的值是「重塑」，由于事务B的回滚，N2的值是「刺猬」。这里在N1处就发生了脏读，显然N1处的查询结果是一个脏数据，会对正常业务产生影响。

脏读会发生在读未提交的隔离级别中。

4.2 不可重复读（Non-Repeatable Read）

不可重复读（Non-Repeatable Read）是指，两次执行相同的查询可能会得到不一样的结果。

继续使用介绍隔离级别时的AB事务案例，同时假设隔离级别为读已提交：

在读已提交的隔离级别下，事务可以读取到其他事务提交的数据。在上述案例中结果是N1=刺猬，N2=重塑，N3=重塑，在事务A中，有两次相同的查询N1和N2，但是这两次查询的结果并不相同，这就发生了不可重复读。

不可重复读会发生在读未提交、读已提交的隔离级别中。

4.3 幻读（Phantom Read）

幻读（Phantom Read）是指，一个事务在读取某个范围内记录时，另外一个事务在该范围内插入一条新记录，当之前的事务再次读取这个范围的记录时，会读到这条新记录。

看下面的案例，假设此时隔离级别为可重复读：

事务A有三次查询，在N1和N2之间，事务B执行了一条 insert语句并提交，N3处的查询使用的是 for update。

N1处的结果很显然只有「刺猬」，N2处的结果由于事务A开启在事务B之前，所以也是「刺猬」，而N3处的结果理论上在可重复读的隔离级别中也应该只有「刺猬」，但实际上N2的结果是「刺猬」和「五条人」，这就发生了幻读。

这就很奇怪了，不是说可重复读的隔离级别能够保证一个事务中多次查询相同的记录，结果总是一致的吗？这种结果并不满足可重复读的定义。

事实上，在可重复读的隔离级别下，如果使用的是当前读，那么就可能发生幻读现象。

当前读和快照读会在下文中介绍事务的实现原理及 MVCC 时讨论，这里先给一个结论。

幻读会发生在读未提交、读已提交、可重复读的隔离级别中。

这里需要额外注意的是：幻读和不可重复读都是说在一个事务中的同一个查询语句结果不同，但幻读更侧重于查询到其他事务新插入的数据（insert）或其他事务删除的数据（delete），而不可重复读的范围更广，只要结果不同就可以认为是不可重复读，但一般我们认为不可重复读更侧重于其他事务对数据的更新（update）。

4.4 小结

通过上面的描述，我们已经知道四种隔离级别的概念以及它们分别会遇到的问题，事务的隔离级别越高，隔离性就越强，所遇到的问题也就越少。但同时，隔离级别越高，并发能力就越弱。

下表是对隔离级别的概念不同隔离级别会发生的问题情况的小结：

5. MVCC

MVCC（Multi-Version Concurrency Control）即多版本并发控制，这是 MySQL 为了提高数据库并发性能而实现的。它可以在并发读写数据库时，保证不同事务的读-写操作并发执行，同时也能解决脏读、不可重复读、幻读等事务隔离问题。

在前文讨论幻读的时候提到过当前读的概念，正是由于当前读，才会在可重复读的隔离级别下也会发生幻读的情况。

在解释可重复读隔离级别下发生幻读的原因之前，首先介绍 MVCC 的实现原理。

5.1 MVCC 的实现原理

首先我们需要知道，InnoDB 的数据页中每一行的数据是有隐藏字段的：

• DB_ROW_ID: 隐式主键，若表结构中未定义主键，InnoDB 会自动生成该字段作为表的主键
• DB_TRX_ID: 事务ID，代表修改此行记录的最后一次事务ID
• DB_ROLL_PTR: 回滚指针，指向此行记录的上一个版本（上一个事务ID对应的记录）

每一条修改语句都会相应地记录一条回滚语句（undo log），如果把每一条回滚语句视为一条数据表中的记录，那么通过事务ID和回滚指针就可以将对同一行的修改记录看作一个链表，链表上的每一个节点就是一个快照版本，这就是 MVCC 中多版本的意思。

举个例子，假设对 user 表中唯一的一行「刺猬」进行多次修改。

update user set name=&＃39;重塑&＃39; where id=1;update user set name=&＃39;木马&＃39; where id=1;update user set name=&＃39;达达&＃39; where id=1; 

那么这条记录的版本链就是：

N1，N2处的查询想必已经十分明确都是「刺猬」了。而在N3处所使用的查询语句是for update，使用它进行查询就会对目标记录添加一把「行级锁」，行级锁的意义以后再说，现在只需要知道for update能够锁住目标记录就可以了。

加锁自然是防止别人修改，那么理所当然，锁住的当然也就是记录的最新版本了。所以，在使用for update进行查询的时候，会使用当前读，读到目标记录的最新版本，所以在N3处的查询语句就会把事务B中本对于事务A来说不可见的记录也查询出来，也就发生了幻读。

使用当前读的语句有：

• select ... for update
• select ... lock in share mode（共享读锁）
• update ...
• insert ...
• delete ...

6. 温故知新

1. 事务的ACID四大特性
2. 自动提交与隐式提交（隐式提交的语句）
3. 事务的隔离级别
4. 事务各个隔离级别可能出现的问题
5. MVCC 的实现原理与过程
6. RC 与 RR 生成读视图的时机

更多相关免费学习推荐：mysql教程(视频)

以上就是我所理解的MySQL之四：事务、隔离级别及MVCC的详细内容，更多请关注 第一PHP社区 其它相关文章！