您现在的位置是：网站首页> 编程资料编程资料

MYSQL事务的隔离级别与MVCC_Mysql_

2023-05-26 384人已围观

简介 MYSQL事务的隔离级别与MVCC_Mysql_

前言

提到数据库，你多半会联想到事务，进而还可能想起曾经背得滚瓜乱熟的ACID，不知道你有没有想过这个问题，事务有原子性、隔离性、一致性和持久性四大特性，为什么偏偏给隔离性设置了级别？一切还得从事务说起。

1. 事务（transaction）的起源

学习数据库事务的时候，一个典型的案例就是「转账」，这篇文章也不能免俗，故事就从招财向陀螺借100块钱开始吧。

一个看似非常简单的现实世界的状态转换，转换成数据库中的操作却并没有那么单纯。

这个看起来很简单的借钱操作至少包含了两个动作：

陀螺的账户余额-100
招财的账户余额+100

要保证转账操作的成功，数据库必须把这两个操作作为一个逻辑整体来执行，这个逻辑整体就是一个事务。

1.1. 事务的定义

事务就是包含有限个（单条或多条）数据库操作（增删改查）的、最小的逻辑工作单元（不可再分）。

说到这里不得不吐槽一下，事务的英文是transaction，直译为“交易”的意思，但是不知道为什么被意译成了“事务”，让人很难从字面上理解这个概念的含义。
中国人对翻译的“信达雅”的偏执在计算机领域或多或少有点不讨喜。

1.2. 哪些存储引擎支持事务

并不是所有的数据库或者所有的存储引擎都支持事务。

对于MySQL而言，事务作为一种功能特性由存储引擎提供。目前支持事务功能的存储引擎只有InnoDB和NDB，鉴于InnoDB目前是MySQL默认的存储引擎，我们的研究重点自然也就是InnoDB存储引擎了。

因此文章接下来默认的存储引擎就是InnoDB，特殊情况下会特别指出。

那么InnoDB在什么情况下才会出现事务呢？

2. MySQL的事务语法

如果你不是DBA，在平时和MySQL的交互中你可能极少直接使用到它的事务语法，一切都被编程框架封装得很好了。但是现在我们要直接使用MySQL进行事务的研究了，抛开框架，跟我稍微回顾一下语法，这是非常必要的。

2.1. 自动提交

当我运行这样单独一条更新语句的时候，它会有事务吗？

UPDATE user_innodb SET name = '蝉沐风' WHERE id = 1;

实际上，这条语句不仅会自动开启一个事务，而且执行完毕之后还会自动提交事务，并持久化数据。

这是MySQL默认情况下使用的方式——自动提交。在此方式下，增删改的SQL语句会自动开启事务，并且是一条SQL一个事务。

自动提交的方式虽然简单，但是对于转账这种涉及到多条SQL的业务，就不太适合了。因此，MySQL提供了手动开启事务的方法。

2.2. 手动操作事务

2.2.1. 开启事务

可以使用下面两种语句开启一个事务：

BEGIN
START TRANSACTION

对比BEGIN而言，START TRANSACTION后面可以添加一些操作符，不过这不是我们的研究重点，可以不必理会。

2.2.2. 提交或回滚

开启事务之后就可以继续编写需要放到当前事务中的SQL语句了。当写完最后一条语句，如果你觉得写得没问题，你可以提交事务；反之你后悔了，想把数据库恢复到之前的状态，你可以回滚事务。

提交事务 COMMIT
回滚事务 ROLLBACK

2.3. autocommit系统变量

MySQL提供了一个叫做autocommit的系统变量，用来表示是否开启自动提交：

mysql> SHOW VARIABLES LIKE 'autocommit'; +---------------+-------+ | Variable_name | Value | +---------------+-------+ | autocommit | ON | +---------------+-------+

autocommit的默认值为ON，表示默认开启自动提交。但是自动提交和手动操作事务并不冲突，如果不显式使用BEGIN或START TRANSACTION开启一个事务，那么InnoDB会在每一条增删改语句执行之后提交事务。

如果我们把autocommit设为OFF，除非我们手动使用BEGIN或START TRANSACTION开启一个事务，否则InnoDB绝不会自动开启事务；同样，除非我们使用COMMIT或ROLLBACK提交或回滚事务，否则InnoDB不会自动结束事务。

实际上，InnoDB会因为某些特殊语句的执行或客户端连接断开等特殊情况而导致事务自动提交（即使我们没有手动输入COMMIT），这种情况叫做隐式提交。

3. 事务并发执行导致的读问题

MySQL会使用独立的线程处理每一个客户端的连接，这就是多线程。每个线程都可以开启事务，这就是事务的并发。

不管是多线程的并发执行还是事务的并发执行（其实本质上是一回事儿），如果不采取点措施，都会带来一些问题。

3.1. 脏读

假设事务T1和T2并发执行，都要访问user_innodb表中id为1的数据，不同的是T1先读取数据，紧接着T2修改了数据的name字段，需要注意的是，T2并没有提交！

此时，T1再次执行相同的查询操作，会发现数据发生了变化，name字段由「王刚蛋」变成了「蝉沐风」。

如果一个事务读到了另一个未提交事务修改过的数据，而导致了前后两次读取的数据不一致的情况，这种事务并发问题叫做脏读。

3.2. 不可重复读

同样是T1和T2两个事务，T1通过id=1查询到了一条数据，然后T2紧接着UPDATE（DELETE也可以）了该条记录，不同的是，T2紧接着通过COMMIT提交了事务。

此时，T1再次执行相同的查询操作，会发现数据发生了变化，name字段由「王刚蛋」变成了「蝉沐风」。

如果一个事务读到了另一个已提交事务修改过的（或者是删除的）数据，而导致了前后两次读取的数据不一致的情况，这种事务并发问题叫做不可重复读。

看到这里是不是有点懵了？怎么读到未提交事务修改的数据是并发问题，读到已提交事务修改的数据还是并发问题呢？

这里先不急着回答你，因为还有个幻读呢。

3.3. 幻读

还是T1和T2这俩货，T1先查找了所有name为「王刚蛋」的用户信息，此时发现拥有这个硬汉名字的用户只有一个。然后T2插入了一个同样叫做「王刚蛋」的用户的信息，并且提交了。

此时，T1再次执行相同的查询操作，发现相比上次的查询结果多了一行数据，不由得怀疑自己是不是出了幻觉。

如果一个事务首先根据某些搜索条件P查询出一些结果，另一个事务写入（可以是INSERT，UPDATE）了一些符合搜索条件P的数据，此时前一个事务再次读取符合条件P的记录时就会获取到之前没有读取过的记录。这个现象叫做幻读。

4. 回答一些可能存在的问题

现在是中场答疑时间。

一个事务读到未提交事务修改的数据不行，读到已提交事务修改的数据为什么还不行？

你是不是觉得一个事务读取到其他事务最新提交的数据是一种正常现象？或者说在多数情况下这是我们期望的一种行为？没错，这种现象确实是正常的。不是说不行，而是针对我们讨论的读一致性问题上，这两种现象都算是并发问题，因为谈这个问题的时候我们已经把语境固定死了，就是在同一个事务中的前后两次SELECT的操作结果不该和其他事务产生瓜葛，否则就是出现了读一致性问题。

我只听说过事务的一致性，没听说过读一致性

事务在并发执行时一共有下面3种情况：

读-读：并发事务相继读取相同记录，由于读取操作本身不会改变记录的值，因此这种情况下自然不会有并发问题；
读-写/写-读：一个事务进行读取操作，另一个事务进行写（增删改）操作；
写-写：并发事务相继对相同记录进行写（增删改）操作。

不知道你有没有注意到上一节的标题是「事务并发执行导致的读问题」。并且脏读、不可重复读和幻读都是在读-写/写-读的情况下出现的，那写-写情况怎么办？

一切的并发问题都可以通过串行化解决，但是串行化效率太低了！

再优化一下，一切并发问题都可以通过加锁来解决，这种方案我们称为基于锁的并发控制（Lock Bases Concurrency Control, LBCC）！但是在读多写少的环境下，客户端连读取几条记录都需要排队，效率还是太低了！

难不成数据库有避免给读操作加锁就可以解决一致性问题的方法？没错，接下来我们要讲的就是这个方法，所以我们才把一致性问题分为读一致性和写一致性，而写一致性就得依赖数据库的锁机制了。

心急吃不了热豆腐，这篇文章先给你讲明白读一致性问题。

不可重复读和幻读的最大区别是什么？

这个问题的答案在网上五花八门，要回答这个问题自然要找官方了。这个官方不是MySQL官方，而是美国国家标准协会（ANSI）。

我们上面谈到的脏读、不可重复读和幻读问题都是理论知识，并不涉及到具体的数据库。考虑到所有数据库在设计的过程中都可能遇到这些问题，ANSI就制定了一个SQL标准，其中最著名的就是SQL92标准，其中定义了「不可重复读」和「幻读」（当然也定义了脏读，但鉴于没啥异议，我就没截图），我把其中的重点单词给大家标注了一下，希望大家能彻底搞懂两者的区别。

我用中文翻译一下就是：

不可重复读：事务T1读取了一条记录，事务T2修改或者删除了同一条记录，并且提交。如果事务T1试图再次读取同一条记录的时候，会读到被事务T2修改的数据或者压根读不到。

幻读：事务T1首先读取了符合某些搜索条件P的一些记录。然后事务T2执行了某些SQL语句产生了符合搜索条件P的一条或多条记录。如果事务T1再次读取符合条件P的记录，将会得到不同于之前的数据集。

SQL标准对于不可重复读已经说得很清楚了，事务T2要对T1读取的记录进行修改或者删除操作，并且必须要提交事务。但是对于幻读的定义就说得很模糊，尤其是文中使用了generate（生成/产生），再结合one or more rows，我们可以认为事务T2执行了INSERT语句插入了之前没有读到的记录，或者是执行了更新记录键值的UPDATE语句生成了符合T1之前的搜索条件的记录，总之只要是事务T1之前没有读到的数据，都算是幻影数据，至于事务T2需不需要提交压根儿没提。

5. SQL标准与4种隔离级别

如果按照对一致性影响的严重程度，对上面提到的3种并发读问题排个序的话，就是下图这样：

我们刚才也提到了，这3种并发读问题都是理论知识，并不涉及到具体的数据库。因此SQL标准再次发挥了作用，他

上一篇：SQL使用复合索引实现数据库查询的优化_Mysql_

下一篇：深入理解MySQL中MVCC与BufferPool缓存机制_Mysql_