MySQL：事务隔离---为什么你改了我还看不见

深碍√TFBOYSˉ_ 2022-03-18 12:18 228阅读 0赞

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]

# 事务的原则 #

所谓事务，就是保证一组数据库操作，要么全部成功，要么全部失败。一共支持下面四个原则

*  1、原子性：在事务中的操作，必须同时完成或者同时回滚，不会只成功或者回滚一部分。
 *  2、一致性：不能破坏数据库的一致性状态。比如A向B转账，不可能A扣了钱，B却没收到。
 *  3、隔离性：不同的事务之前互相不能影响。比如A正在从一张银行卡中取钱，在A取钱的过程结束前，B不能向这张卡转账。
 *  4、持久性：事务完成以后，即保存数据，不能回滚。

# 事务并发造成的问题 #

当数据库上有多个事务同时执行的时候，就会出现 脏读、不可重复读、幻读的问题。

## 脏读 ##

事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据。

## 不可重复读 ##

事务A多次读取同一数据，事务B在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果不一致。

## 幻读 ##

事务A对表中的数据进行了修改，涉及到表中的全部行。同时，事务B也修改这个表中的数据，向表中插入一行新数据。那么，事务A发现表中还有自己没有修改的行，就好象发生了幻觉一样。

## 小结 ##

*  不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。
 *  解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表

# MySQL的事务隔离级别 #

事务隔离级别，就是为了解决上面几种问题而诞生的。

为什么要有事务隔离级别？

*  因为事务隔离级别越高，在并发下会产生的问题就越少，但同时付出的性能消耗也将越大，因此很多时候必须在并发性和性能之间做一个权衡。
 *  所以设立了几种事务隔离级别，以便让不同的项目可以根据自己项目的并发情况选择合适的事务隔离级别，

在MySQL中，事务是在存储引擎中实现的。

*  在MySQL的众多存储引擎中，但是所有的引擎都支持事务。InnoDB引擎是支持事务的
 *  注意：下面这里说的事务隔离级别指的是InnoDB下的事务隔离级别。

## 读未提交 ##

> 读未提交，顾名思义，就是一个事务可以读取另一个未提交事务的数据。

比如：

事例：

*  老板要给程序员发工资，程序员的工资是3.6万/月。但是发工资时老板不小心按错了数字，按成3.9万/月，该钱已经打到程序员的户口，但是事务还没有提交
 *  就在这时，程序员去查看自己这个月的工资，发现比往常多了3千元，以为涨工资了非常高兴。
 *  但是老板及时发现了不对，马上回滚差点就提交了的事务，将数字改成3.6万再提交。

分析：

*  实际程序员这个月的工资还是3.6万，但是程序员看到的是3.9万。
 *  他看到的是老板还没提交事务时的数据。这就是脏读。

那怎么解决脏读呢？Read committed！**读提交，能解决脏读问题**。

## 读已提交 ##

> 读提交，**就是一个事务要等另一个事务提交后才能读取数据**。

事例：

*  程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（程序员事务开启），收费系统事先检测到他的卡里有3.6万，就在这个时候！！程序员的妻子要把钱全部转出充当家用，并提交。
 *  当收费系统准备扣款时，再检测卡里的金额，发现已经没钱了（第二次检测金额当然要等待妻子转出金额事务提交完）。程序员就会很郁闷，明明卡里是有钱的…

分析：

*  这就是读提交，若有事务对数据进行更新（UPDATE）操作时，**读操作事务要等待这个更新操作事务提交后才能读取数据**，可以解决脏读问题。
 *  但在这个事例中，**出现了一个事务范围内两个相同的查询却返回了不同数据，这就是不可重复读**。

那怎么解决可能的不可重复读问题？Repeatable read ！（可重复读）

## 可重复读 ##

> 可重复读：
> 
>  *  一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据时一致的。
>  *  简单来说，就是**在开始读取数据（事务开启）时，不再允许修改操作**

事例：

*  程序员拿着信用卡去享受生活（卡里当然是只有3.6万），当他埋单时（事务开启，不允许其他事务的UPDATE修改操作），收费系统事先检测到他的卡里有3.6万。
 *  这个时候他的妻子不能转出金额了。接下来收费系统就可以扣款了。

分析：

*  重复读可以解决不可重复读问题。
 *  写到这里，应该明白的一点就是，不可重复读对应的是修改，即UPDATE操作。
 *  但是可能还会有幻读问题。因为幻读问题对应的是插入INSERT操作，而不是UPDATE操作。

什么时候会出现幻读？

事例：

*  程序员某一天去消费，花了2千元，然后他的妻子去查看他今天的消费记录（全表扫描FTS，妻子事务开启），看到确实是花了2千元，就在这个时候，程序员花了1万买了一部电脑，即新增INSERT了一条消费记录，并提交。
 *  当妻子打印程序员的消费记录清单时（妻子事务提交），发现花了1.2万元，似乎出现了幻觉，这就是幻读。

那怎么解决幻读问题？Serializable！

## 串行读 ##

> 串行读，就是对于同一行记录，“写”会加“写锁”，“读”会加“读锁”。当出现读写锁冲突的时候，后访问的事务必须等前一个事务执行完成，才能继续执行。

完全串行化读，每次都会锁表，读写互相阻塞。

*  Serializable 是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。
 *  但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。

## 小结 ##

*  读未提交：别人改数据的事务尚未提交，我在我的事务中也能读到。
 *  读已提交：别人改数据的事务已经提交，我在我的事务中才能读到。
 *  可重复读：别人改数据的事务已经提交，我在我的事务中也不去读。
 *  串行：我的事务尚未提交，别人就别想改数据。

这4种隔离级别，并行性能依次降低，安全性依次提高。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]

## 事务隔离与视图 ##

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。

*  在“可重复读”隔离级别下，这个事务是在事务启动的时候创建的，整个事务存在期间都会用到这个视图
 *  在“读提交”基本下，这个视图是在每个SQL语句开始执行的时候创建的
 *  注意，“读未提交”时，直接返回记录上的最新值，没有视图概念
 *  “串行读”隔离级别下，直接用加锁的方式来避免并行访问

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。

> 对于RR，你可以这么想，每个事务启动的时候打一个快照，别人改的“我不听我不  
> 听”

配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以用 show variables 来查看当前的值。

# 事务隔离的实现 #

隔离的实现主要有读写锁和MVCC（Multi-Version Concurrency Control）多版本并发处理方式。

## 读写锁 ##

最简单直接的的事务隔离实现方式

*  每次读操作需要获取一个共享(读)锁，每次写操作需要获取一个写锁。
 *  共享锁之间不会产生互斥，共享锁和写锁之间、以及写锁与写锁之间会产生互斥。
 *  当产生锁竞争时，需要等待其中一个操作释放锁后，另一个操作才能获取到锁。

## MVCC ##

*  在读写锁中，读和写的排斥作用大大降低了事务的并发效率，于是人们又提出了能不能让读写之间也不冲突的方法，就是读取数据时通过一种类似快照的方式将数据保存下来，这样读锁就和写锁不冲突了。
 *  不同的事务session会看到自己特定版本的数据，即使其他的事务更新了数据，但是对本事务仍然不可见，本事务看到的数据始终是第一次查询到的数据。
 *  在数据库中，这个快照的处理方式叫多版本并发控制（Multi-Version Concurrency Control）。
 *  这种方式真正实现了非阻塞读，只有在写操作时才需要加行级锁，因此并发效率更高。

在各个数据库系统的，MVCC的实现机制不尽相同，下面来详细介绍一下InnoDB是如何实现MVCC的，主要讨论可重复读级别的实现。

### 两个概念 ###

首先，需要了解两个概念：ReadView、undo log

#### ReadView ####

*  ReadView其实就是上面提到的快照
 *  ReadView主要是用来做可见性判断的，即通过ReadView可以知道：哪些事务的提交结果对当前事务可见，哪些事务的提交结果对当前事务不可见。
 *  关于如何判断可见性，是根据可预见性判断算法来的

#### undo log ####

readView是事务的快照，但是通过ReadView仅仅能知道哪些事务的提交结果对当前事务可见，可是还是不知道当前事务的数据快照在哪啊。undo log就是来解决这个问题的。

undo log就是我们通常说的回滚日志，undo log存放的是数据的历史记录，也可以叫数据的快照。

### "快照"在MVCC中是怎么工作的？ ###

在可重复读的级别下，事务在启动的时候就“快照”。注意，这个快照是基于整个库的。

这个好像看上去不太现实啊。如果一个库有100G，那么我启动一个事务，MySQL就要拷贝100G的数据出来，这个过程地多慢啊。

实际上，我们并不需要拷贝这100G的数据。我们先来看看这个快照是怎么实现的。

*  InnDB里面每个事务都有一个唯一的事务ID，叫做transaction id。它是在事务开始的时候向InnoDB的事务系统申请的，是按照申请顺序严格递增的
 *  而每行数据也都是有多个版本的。每次事务更新的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记作row trx\_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它
 *  也就是说，数据表中的一行记录，其实可能有多个版本 (row)，每个版本有自己的 row trx\_id。  
    ![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_16_color_FFFFFF_t_70_g_se_x_16]
 *  图中虚线框里是同一行数据的 4 个版本，当前最新版本是 V4，k 的值是 22，它是被transaction id 为 25 的事务更新的，因此它的 row trx\_id 也是 25。
 *  每次语句更新都会生成undo log（回滚日志），上图中三个虚线箭头，就是undo log；而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来的。
 *  按照可重复读的定义，一个事务启动的时候，能够看到所有已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。
 *  因此，一个事务只需要在启动的时候声明说：“以我启动的时刻为准，如果一个数据版本是在我启动之前生成的，就认；如果是在我启动之后才生成的，我就不认，我必须要找到它的上一个版本”
 *  当然，如果“上一个版本”也不可见，那么就需要继续向前找。还有，如果是这个事务自己更新的数据，它自己还是要认的。
 *  在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前正在“活跃”的所有事务ID，“活跃”是指，启动了但是还没有提交。
 *  数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1为高水位
 *  这个视图数组和高水位，就组成当前事务的一致性视图（read-view）
 *  而数据版本的可见性规则，就是基于数据的 row trx\_id 和这个一致性视图的对比结果得到  
    的。
 *  这个视图数组把所有的 row trx\_id 分成了几种不同的情况。

![在这里插入图片描述][7f8a80dc1a4148c1a7661d715c6117e8.png]

这样，对于当前事务的启动瞬间来说，一个数据版本的 row trx\_id，有以下几种可能：

1.  如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的；
2.  如果落在红色部分，表示这个版本是由将来启动的事务生成的，是肯定不可见的；
3.  如果落在黄色部分，那就包括两种情况  
    a. 若 row trx\_id 在数组中，表示这个版本是由还没提交的事务生成的，不可见；  
    b. 若 row trx\_id 不在数组中，表示这个版本是已经提交了的事务生成的，可见。

你看，有了这个声明后，系统里面随后发生的更新，是不是就跟这个事务看到的内容无关了呢？因为之后的更新，生成的版本一定属于上面的 2 或者 3(a) 的情况，而对它来说，这些新的数据版本是不存在的，所以这个事务的快照，就是“静态”的了。

**InnoDB利用了“所有数据都有多个版本”这个特性，实现了“秒级创建快照”的能力**

### 可重复读实现 ###

*  在MySQL中，实际上每条记录在更新的时候都会同时记录一条回滚操作。记录上的最新值，通过回滚操作，都可以得到前一个状态的值。
 *  假设一个值从 1 被按顺序改成了 2、3、4，在回滚日志里面就会有类似下面的记录。

![在这里插入图片描述][watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_18_color_FFFFFF_t_70_g_se_x_16]

*  当前值是4，但是在查询这条记录的时候，不同时刻启动的事务会有不同的read-view。
    
     *  如图所示，在视图A、B、C里面，这一个记录的值分布是1、2、4，同一条记录在系统中可以有多个版本，就是数据库的多版本并发控制（MVCC）。
     *  对于read-view A，要得到1，就必须将当前值依次执行图中所有的回滚操作得到。
 *  同时你会发现，即使现在有另外一个事务正在将4改成5，这个事务跟read-view A、B、C对应的事务是不会冲突的。
 *  那么回滚日志什么时候会删除呢？当系统判断没有事务再需要用到这些回滚日志时，就会删除这个日志。
 *  什么时候才不需要了呢？就是当系统里没有比这个回滚日志更早的 read-view 的时候。

> 为什么建议尽量不要使用长事务。

*  长事务意味着系统里面会存在很老的事务视图。由于这些事务随时可能会访问数据库里面的任何数据，所以这个事务提交前，数据库里面它可能用到的回滚记录都必须保留，这就会导致大量占用存储空间。
 *  在MySQL 5.5以及之前的版本，回滚日志是跟数据字典一起放在ibdata文件里面的，即使长事务最终提交，回滚段被清理，文件页不会变小。我见过数据只有 20GB，而回滚段有200GB 的库。最终只好为了清理回滚段，重建整个库。
 *  除了对回滚段的影响，长事务还占用锁资源，也可能拖垮整个库

> 如何避免长事务对业务的影响

这个问题，我们可以从应用开发端和数据库端来看

（1） 从应用开发端来看

*  确认是否使用了set autocommit=0。这个确认工作可以在测试环境中开展，把MySQL 的 general\_log 开起来，然后随便跑一个业务逻辑，通general\_log 的日志来确认。一般框架如果会设置这个值，也就会提供参数来控制行为，你的**目标就是把它改成 1**。
 *  确认是否有不必要的只读事务。有些框架会习惯不管什么语句先用 begin/commit 框起来。或者好几个 select 语句放到了事务中。**这种只读事务可以去掉**。
 *  业务连接数据库时，根据业务本身的评估，通过 SET MAX\_EXECUTION\_TIME命令，来控制每个语句执行的最长时间，避免单个语句意外执行太长时间。

（2）其次，从数据库端来看：

*  监控 information\_schema.Innodb\_trx表，设置长事务阈值，超过就报警/或者kill
 *  Percona 的 pt-kill 这个工具不错，推荐使用；
 *  在业务功能测试阶段要求输出所有的 general\_log，分析日志行为提前发现问题；
 *  . 如果使用的是 MySQL 5.6 或者更新版本，把 innodb\_undo\_tablespaces 设置成 2（或更大的值）。如果真的出现大事务导致回滚段过大，这样设置后清理起来更方便。

# 事务的启动方式 #

MySQL 的事务启动方式有以下几种：

1.  显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，回滚语句是 rollback。
2.  set autocommit=0，这个命令会将这个线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。

因此，会建议总是使用 set autocommit=1, 通过显式语句的方式来启动事务。

> 那 `set autocommit=1`会不会导致“多一次交互”的问题呢？

会，但是可以使用`commit work and chain`语法解决

*  当设置了`autocommit=1`，用begin显式启动的事务，如果执行commit则提交事务。
 *  如果执行`commit work and chain`，则是提交事务并自动启动下一个事务，这样也省去了再次执行begin语句的开销。
 *  同时带来的好处是从程序开发的角度明确地知道每个语句是否处于事务中。

你可以在 information\_schema 库的 innodb\_trx 这个表中查询长事务，比如下面这个语句，用于查找持续时间超过 60s 的事务。

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70]: /images/20220318/c3127bdc305d4959941e9f19647b4f22.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poaXpoZW5nZ3Vhbg_size_16_color_FFFFFF_t_70 1]: /images/20220318/f211cc7ed19e4baa918f99c9e30b3c4a.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_16_color_FFFFFF_t_70_g_se_x_16]: /images/20220318/4c19a8e1675e4134846e14b6107d43a4.png
[7f8a80dc1a4148c1a7661d715c6117e8.png]: /images/20220318/a37d36b9b27c4c5293cb5425e399fb64.png
[watermark_type_ZHJvaWRzYW5zZmFsbGJhY2s_shadow_50_text_Q1NETiBAT2NlYW4mJlN0YXI_size_18_color_FFFFFF_t_70_g_se_x_16]: /images/20220318/c84dd4ae7d254b99a788a55381c4f5ce.png