2023年MySQL实战核心技术第三篇-Toy模板网

这篇具有很好参考价值的文章主要介绍了2023年MySQL实战核心技术第三篇。希望对大家有所帮助。如果存在错误或未考虑完全的地方，请大家不吝赐教，您也可以点击"举报违法"按钮提交疑问。

目录

六 . 事务隔离：隔离级别，事务隔离原理，MVCC等原理的讲解

6.1 解释：

6.2 隔离性与隔离级别

6.2.1 SQL 标准的事务隔离级别：

6.2.2 事务隔离级别解释：

6.2.3 例子：

6.2.3.1 若隔离级别是“读未提交”

6.2.3.2 若隔离级别是“读提交”

6.2.3.3 若隔离级别是“可重复读”

6.2.3.4 若隔离级别是“串行化”

6.2.3.5读提交和可重复读的视图创建详解：

6.2.3.6 Oracle迁移数据库MySQL

6.2.4 需要可重复读的场景

6.2.5 什么是脏读，不可重复读，和幻读

6.2.5.1 脏读（Dirty Read）

6.2.5.1.1 举个例子

6.2.5.2 不可重复读：

6.2.5.2.1例子：

6.2.5.3 幻读：

6.2.5.3.1例子

6.3 事务隔离的具体实现

6.3.1 一致性视图的创建和读写操作的处理

6.3.2 读操作的详细解释：

6.3.3 加强对于读锁和版本检查的理解

6.3.4 MVCC原理讲解

6.3.4.1 Undo log（回滚日志）和Redo log（重做日志）

6.3.4.2 详细解释 Undo log与binlog的区别：

6.3.4.3 Binlog不可以支持事务的回滚操作和MVCC吗？

6.3.4.4 支持事务的回滚操作和MVCC不用binlog而用undolog是有什么好处吗？

6.3.4.5 MVCC具体实现

6.3.4.5.1 TRX_ID 集合详细解释：

6.3.4.5.2 快照读

6.3.4.5.2.1这行数据究竟是在当前事务开启前提交的还是在当前事务开启后提交的呢？

6.3.4.5.3 当前读

6.3.4.5.3.1 悲观锁和排他锁例子：

6.3.4.5.4 MVCC 解决不可重复读、幻读的实现

6.3.4.5.4.1. 不可重复读与幻读

6.3.4.5.4.2 可重复读的隔离级别是否解决了不可重复读与幻读问题呢?

6.3.4.5.4.3 可重复读隔离级别下幻读产生

6.3.4.5.4.4可重复读隔离级别下幻读产生的解决方案

六 . 事务隔离：隔离级别，事务隔离原理，MVCC等原理的讲解

6.1 解释：

提到事务，你肯定不陌生，和数据库打交道的时候，我们总是会用到事务。最经典的例子就是
转账，你要给朋友小王转 100 块钱，而此时你的银行卡只有 100 块钱。
转账过程具体到程序里会有一系列的操作，比如查询余额、做加减法、更新余额等，这些操作
必须保证是一体的，不然等程序查完之后，还没做减法之前，你这 100 块钱，完全可以借着
这个时间差再查一次，然后再给另外一个朋友转账，如果银行这么整，不就乱了么？这时就要
用到“事务”这个概念了。

事务就是要保证一组数据库操作，要么全部成功，要么全部失败。

在 MySQL 中，事务支持是在引擎层实现的。你现在知道，MySQL 是一个支持多引擎的系统，但并不是所有的引擎都支持事务。比如 MySQL 原生的 MyISAM 引擎就不支持事务，这也是MyISAM 被InnoDB 取代的重要原因之一。

下面将以InnoDB为例子，进行MySQl在事务方面的特定实现，并基于原理给出解释。

6.2 隔离性与隔离级别

提到事务，你肯定会想到 ACID（Atomicity、Consistency、Isolation、Durability，即原子
性、一致性、隔离性、持久性），今天我们就来说说其中 I，也就是“隔离性”。

当数据库上有多个事务同时执行的时候，就可能出现脏读（dirty read）、不可重复读（non-
repeatable read）、幻读（phantom read）的问题，为了解决这些问题，就有了“隔离级
别”的概念。

在谈隔离级别之前，你首先要知道，你隔离得越严实，效率就会越低。因此很多时候，我们都
要在二者之间寻找一个平衡点。

6.2.1 SQL 标准的事务隔离级别：

读未提交（readuncommitted）、

读提交（read committed）、

可重复读（repeatable read）

串行化（serializable ）。

6.2.2 事务隔离级别解释：

其中“读提交”和“可重复读”比较难理解，

例子说明这几种隔离级别：

假设数据表 T 中只有一列，其中一行的值为 1，下面是按照时间顺序执行两个事务的行为。

读未提交是指，一个事务还没提交时，它做的变更就能被别的事务看到。

读提交是指，一个事务提交之后，它做的变更才会被其他事务看到。

可重复读是指，一个事务执行过程中看到的数据，总是跟这个事务在启动时看到的数据是一致的。当然在可重复读隔离级别下，未提交变更对其他事务也是不可见的。

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

6.2.3 例子：

假设表T只有一列，其中一行值为1.

mysql> create table T(c int) engine=InnoDB;
insert into T(c) values(1);

2023年MySQL实战核心技术第三篇,2023年MySQL核心技术面试知识总结,mysql,数据库

在不同的隔离级别下，事务 A 会有哪些不同的返回结果，也就是图里面 V1、V2、
V3 的返回值分别是什么。

6.2.3.1 若隔离级别是“读未提交”

则 V1 的值就是 2。这时候事务 B 虽然还没有提交，但是结
果已经被 A 看到了。因此，V2、V3 也都是 2。

6.2.3.2 若隔离级别是“读提交”

则 V1 是 1，V2 的值是 2。事务 B 的更新在提交后才能被 A 看
到。所以， V3 的值也是 2。

6.2.3.3 若隔离级别是“可重复读”

则 V1、V2 是 1，V3 是 2。之所以 V2 还是 1，遵循的就是
这个要求：事务在执行期间看到的数据前后必须是一致的。

6.2.3.4 若隔离级别是“串行化”

则在事务 B 执行“将 1 改成 2”的时候，会被锁住。直到事务A 提交后，事务B才可以继续进行执行，所以从A的角度看，v1,v2值是1，v3的值是2.

在实现上，数据库里面会创建一个视图，访问的时候以视图的逻辑结果为准。在“可重复读”
隔离级别下，这个视图是在事务启动时创建的，整个事务存在期间都用这个视图。在“读提
交”隔离级别下，这个视图是在每个 SQL 语句开始执行的时候创建的。这里需要注意的是，
“读未提交”隔离级别下直接返回记录上的最新值，没有视图概念；而“串行化”隔离级别下
直接用加锁的方式来避免并行访问。

6.2.3.5读提交和可重复读的视图创建详解：

在数据库中，为了支持事务隔离性，系统会维护一个视图（或称为快照）来决定每个事务可以看到的数据内容。根据隔离级别的不同，在事务执行过程中创建视图的时间会有所区别。

在"可重复读"隔离级别下，事务启动时会创建一个一致性视图，这个视图包含了事务开始时刻数据库中的数据状态。整个事务期间，该一致性视图不会改变，因此事务内部所有的查询操作都是基于这一时刻的数据库状态进行的。其他并发事务对数据的修改不会被可重复读事务感知到，即使其他事务已经提交了对某个数据的修改，对于可重复读事务来说，该数据的值依然是一开始创建视图时的值。

而在"读提交"隔离级别下，每个SQL语句开始执行时都会创建一个新的视图，视图会基于当前的数据库状态生成。这意味着在执行每个SQL语句之前，都会反映其他并发事务已经提交的修改。因此，"读提交"隔离级别下的事务能够看到其他事务已经提交的修改。

总结区别：

在"可重复读"隔离级别下，事务开始时创建一致性视图，整个事务期间使用该视图，不受其他事务的修改影响。
在"读提交"隔离级别下，每个SQL语句开始执行时都会创建一个新的视图，能够看到其他已提交事务的修改。

6.2.3.6 Oracle迁移数据库MySQL

我们可以看到在不同的隔离级别下，数据库行为是有所不同的。Oracle 数据库的默认隔离级
别其实就是“读提交”，因此对于一些从 Oracle 迁移到 MySQL 的应用，为保证数据库隔离
级别的一致，你一定要记得将 MySQL 的隔离级别设置为“读提交”。
配置的方式是，将启动参数 transaction-isolation 的值设置成 READ-COMMITTED。你可以
用 show variables 来查看当前的值。

6.2.4 需要可重复读的场景

例子：

假设你在管理一个个人银行账户表。一个表存了账户余额，一个表存了账单明细。到了月底你
要做数据校对，也就是判断上个月的余额和当前余额的差额，是否与本月的账单明细一致。你
一定希望在校对过程中，即使有用户发生了一笔新的交易，也不影响你的校对结果。
这时候使用“可重复读”隔离级别就很方便。事务启动时的视图可以认为是静态的，不受其他
事务更新的影响。

6.2.5 什么是脏读，不可重复读，和幻读

脏读，不可重复读（Non-repeatable Read）和幻读（Phantom Read）都是数据库事务并发访问时可能出现的问题，它们表示在事务执行过程中读取到的数据内容发生了变化或者数量发生了变化。

6.2.5.1 脏读（Dirty Read）

是数据库事务并发访问时可能出现的问题，它指的是一个事务读取了另一个未提交的事务所做的修改，从而读取到了不一致或无效的数据。

具体来说，在一个事务中，当某个数据项被修改但尚未提交时，另一个并发的事务可以读取到这个未提交的修改结果。这种读取到的数据是"脏"的，因为它可能包含了未经验证或者不完整的修改。

6.2.5.1.1 举个例子

考虑两个并发的事务，事务A和事务B。事务A首先对某个数据项进行了修改，但是尚未提交；然后事务B读取了同一数据项，并且获取到了事务A所做的未提交的修改结果。如果此时事务A回滚或者修改结果发生了变化，事务B就读取到了错误的数据，即脏数据。

脏读是一种数据一致性问题，因为读取到的数据可能是不正确的或者不符合预期的。为了避免脏读，数据库引擎通常采用事务隔离机制，如使用读锁、版本控制或快照隔离级别等方法，以确保事务在读取数据时只能看到已经提交的修改结果，而不会读取到未提交的脏数据。这样可以保证数据库的数据一致性和可靠性。

6.2.5.2 不可重复读：

不可重复读指的是在同一个事务内多次读取同一数据项时，读取到的数据内容发生了变化。具体来说，当一个事务在执行过程中进行了两次相同的查询操作，但是在两次查询之间有另一个事务对相同的数据项进行了修改并提交时，第二次查询可能会读取到与第一次查询不一致的结果。

6.2.5.2.1例子：

事务A首先从一个表中读取某个数据项，然后在读取之后的某个时间点，事务B对该数据项进行了修改并成功提交。如果事务A再次读取同一数据项，那么它将会读取到与之前读取结果不一致的新数据。

6.2.5.3 幻读：

幻读指的是在同一个事务内多次执行相同的查询操作时，读取到的数据数量发生了变化。具体来说，当一个事务在执行过程中进行了两次相同的查询操作，但是在两次查询之间有另一个事务插入、删除，第二次查询可能会返回与第一次查询不同的数据数量。

6.2.5.3.1例子

事务A首先根据某个条件从一个表中查询出一组数据，并在接下来的某个时间点，事务B插入了满足相同条件的一条新数据并成功提交。如果事务A再次执行同样的查询操作，那么它将会返回比之前多了一条记录的结果集。

不可重复读和幻读都是由于并发事务对数据库中的数据进行修改而导致的问题。为了避免这些问题，数据库引擎通常采用锁机制、版本控制或快照隔离级别等方法来提供一定程度的事务隔离性和数据一致性，保证事务执行过程中读取到的数据是符合预期的。

6.3 事务隔离的具体实现

接下来对可重复读进行详细的事务隔离的具体是怎么实现的，进行讲解。

可重复读隔离级别的底层实现思路

6.3.1 一致性视图的创建和读写操作的处理

一致性视图的创建：
- 数据库引擎在事务启动时，会为该事务创建一个一致性视图，用于记录当前时刻数据库的快照。
- 创建一致性视图的方法有多种，其中一种常见的方式是使用多版本并发控制（MVCC）机制。在MVCC中，每个数据项都会带有时间戳信息，事务根据自己启动时的时间戳来决定可见的数据版本。
读写操作的处理：
- 对于读操作：当事务进行读取操作时，数据库引擎会根据事务的一致性视图来确定可见的数据。
  - 引擎会检查被读取的数据项是否已经被其他并发事务修改过。
  - 如果数据在事务启动之后被修改过，引擎会判断该数据是否对当前事务可见。
  - 如果数据对当前事务不可见（即未提交的修改），引擎会通过读锁或版本检查等机制阻止当前事务读取该数据，直到修改事务提交或回滚。
- 对于写操作：当事务进行写入操作时，数据库引擎会使用锁机制来保证事务之间的互斥，以及确保事务内部的读取操作不受其他并发事务的影响。
  - 事务发起写入请求后，引擎会给要写入的数据项加上写锁，阻止其他并发事务对该数据进行读取或写入操作。
  - 在事务提交之前，所有的修改都只在内存中进行，不会被其他事务看到。
  - 当事务提交时，引擎会将事务所做的所有修改持久化到磁盘，并释放相关的锁。

可重复读隔离级别的底层实现思路包括创建一致性视图和使用读写锁机制。但无论采用何种具体实现方式，目标都是保证事务的读取操作始终保持一致，不受其他并发事务的影响。

6.3.2 读操作的详细解释：

当事务进行读取操作时，数据库引擎会采用一致性视图来确定可见的数据。一致性视图是一个时间点上的数据库状态快照，代表了当前事务在启动时可见的数据。

在读操作之前，数据库引擎会检查被读取的数据项是否已经被其他并发事务修改过。这是通过比较数据项的时间戳或版本号来实现的。如果数据项的修改时间晚于当前事务的启动时间，则说明该数据在事务启动之后被修改过。

接下来，引擎会判断被修改的数据项是否对当前事务可见。如果数据对当前事务不可见（即未提交的修改），引擎会阻止当前事务读取该数据。

引擎可以使用不同的机制来阻止当前事务读取未提交的修改，其中常见的机制包括读锁和版本检查。

读锁是一种并发控制机制，它会在当前事务读取数据的同时对该数据项进行加锁，防止其他事务修改该数据。如果有其他事务对数据项加了写锁，则当前事务无法获取读锁，必须等待写锁释放后才能读取数据。

另一种常见的机制是版本检查。每个数据项可能都有一个或多个版本号，用于跟踪其修改历史。当事务启动时，引擎会记录事务开始时的版本号。在读取数据时，引擎会检查被读取数据项的版本号是否大于当前事务的启动版本号。如果是，则说明该数据在事务启动后被修改过，当前事务无法读取。只有当事务的启动版本号小于等于数据项的版本号时，该数据对当前事务才是可见的。

通过读锁或版本检查等机制，数据库引擎可以确保事务读取的数据是一致的和符合规则的，从而维护数据库的一致性和完整性。这样可以防止数据的脏读（读取到未提交的修改）、不可重复读（同一事务内多次读取到不同结果）和幻读（同一查询在不同时间点返回不同的结果）等并发读取问题。

6.3.3 加强对于读锁和版本检查的理解

在数据库事务处理中，当一个事务对某个数据项进行了修改但尚未提交时，引擎需要确保其他并发的事务不会读取到该未提交的修改。为此，引擎可以采用不同的机制来阻止读取未提交的修改，其中包括读锁和版本检查。

读锁（Read Lock）：在引擎使用读锁机制时，当一个事务开始对某个数据项进行修改时，引擎将为该数据项加上一个读锁。读锁的作用是防止其他事务写入该数据项，但允许其他事务继续读取该数据项。当其他事务要读取该数据项时，它们必须先申请读锁。如果当前事务已经对该数据项进行了修改但尚未提交，其他事务的读锁申请将被阻塞，直到当前事务释放读锁或者提交修改。

使用读锁机制可以避免脏读（Dirty Read）的问题，即读取到未提交的修改结果。
版本检查（Version Checking）：当引擎使用版本检查机制时，每个数据项都会有一个对应的版本号或时间戳。在事务开始时，引擎会记录下当前事务的开始时间或版本号。当一个事务要读取某个数据项时，引擎会检查该数据项的版本号或时间戳，并将其与当前事务的开始时间或版本号进行比较。

如果数据项的版本号或时间戳大于当前事务的开始时间或版本号，说明该数据项已经被其他事务修改并提交了。此时，引擎会阻止当前事务读取该数据项，以保证读取到的数据是一致的。如果数据项的版本号或时间戳小于等于当前事务的开始时间或版本号，则表示该数据对当前事务是可见的，当前事务可以顺利读取该数据。

使用版本检查机制可以避免不可重复读（Non-repeatable Read）和幻读（Phantom Read）的问题，即在同一个事务内多次读取同一数据项时，读取到的数据内容不一致或者数量发生变化。

6.3.4 MVCC原理讲解

可重复读隔离级别下事务的实现方式

MVCC 全称是 multiversion concurrency control，即多版本并发控制，是 innodb 实现事务并发与回滚的重要功能。

首先先进行事务日志的解释：

6.3.4.1 Undo log（回滚日志）和Redo log（重做日志）

Undo log（回滚日志）和Redo log（重做日志）是MySQL事务日志系统中的两个关键组成部分。

Undo log（回滚日志）：
- 功能：Undo log用于支持事务的回滚和MVCC（多版本并发控制）。
- 内容：它记录了事务对数据库进行修改的操作，包括对数据页的修改、新增和删除等操作，并以逻辑方式记录了被修改的数据的旧值，在回滚时可以将数据恢复到之前的状态。
- 存储位置：Undo log通常存储在InnoDB存储引擎的回滚段中，或者可以配置为使用独立的undo表空间。
- 生命周期：Undo log在事务开始时创建，在事务提交或回滚后被删除。
Redo log（重做日志）：
- 功能：Redo log用于实现持久性和故障恢复机制，确保事务的提交对数据库是持久的。
- 内容：它记录了事务对数据库进行修改的操作，以物理方式记录修改的内容，如在什么位置对哪些数据进行了怎样的修改。
- 存储位置：Redo log通常存储在磁盘上的一个或多个文件中，以确保即使数据库发生故障，也能够通过重放Redo log来恢复已提交的事务。
- 生命周期：Redo log的生命周期可以根据配置进行管理，可以设置最大的重做日志文件大小、保留时间等。

MySQL的事务日志系统使用Undo log和Redo log的组合来实现数据的一致性和持久性。当事务提交时，首先将数据的修改操作记录到Redo log中，以确保在数据库故障恢复时可以重放这些操作。然后，Undo log用于支持事务的回滚和MVCC，可以将事务的修改操作撤销到之前的状态。

这种组合的使用可以保证MySQL数据库在面对故障或事务回滚时能够保持数据的一致性和完整性。

6.3.4.2 详细解释 Undo log与binlog的区别：

MySQL的binlog（二进制日志）也可以用于实现事务的恢复和数据同步。

Binlog是MySQL数据库的一种日志文件，记录了数据库中的所有更新操作，包括对表结构和数据的修改，如INSERT、UPDATE、DELETE语句等。它可以在数据库故障后被使用来进行恢复，以保证数据的完整性。

在MySQL中，binlog可以用于以下方面：

数据库备份与恢复：通过将binlog文件备份，可以在需要时使用mysqlbinlog工具将其应用到数据库中，以实现数据库的恢复。
主从复制：主节点将自己的更新操作记录到binlog中，并且从节点可以通过读取主节点的binlog来进行数据同步，从而保证主从节点的数据一致性。

需要强调的是，Undo log和Binlog在MySQL中扮演不同的角色：

Undo log主要用于支持事务的回滚操作和MVCC。
Binlog主要用于备份、恢复和数据同步。

它们的使用并不互斥，而是相辅相成的。由于它们的功能和使用方式不同，因此它们被设计为独立的组件，分别用于不同的目的。

6.3.4.3 Binlog不可以支持事务的回滚操作和MVCC吗？

在MySQL中，Binlog可以用于支持事务的恢复和数据同步，但是它并不直接支持事务的回滚和MVCC。

在MySQL中，Undo log是用于支持事务的回滚操作和MVCC的关键组成部分，而Binlog主要用于备份、恢复和数据同步。对于事务的回滚，MySQL会通过Undo log来实现，即在事务执行时，将操作记录到Undo log中，在进行事务回滚时，将撤销记录应用到相应的数据页中，从而回滚事务。

对于MVCC机制，MySQL则是通过将Undo log和Read View（读视图）结合起来实现的。事务执行查询时，会根据自己的ID获取当前的Read View，并利用Undo log来构建一个与Read View一致的视图，然后对视图执行查询。

虽然Binlog文件可以用于恢复事务，但它并非用于直接支持事务的回滚和MVCC的组成部分。这些功能是由Undo log和Read View来支持的。

6.3.4.4 支持事务的回滚操作和MVCC不用binlog而用undolog是有什么好处吗？

使用Undo log而不是Binlog来支持事务的回滚操作和MVCC有以下好处：

效率和性能：Undo log是在存储引擎层面实现的，以页面（page）为单位记录数据的修改，相对于Binlog文件的记录方式更加高效。因为Undo log直接记录了被修改数据的旧值，可以通过撤销操作将数据恢复到之前的状态，而不需要执行大量的逆向操作。这样可以减少IO操作和计算开销，提高事务回滚的性能。
数据一致性：Undo log记录了事务对数据库的修改操作，可以确保事务回滚时数据可以完全恢复到之前的状态，从而保证了数据的一致性。Binlog虽然可以用于恢复事务，但它记录的是事务对数据库的更新语句，如果在事务执行过程中发生了并发修改，使用Binlog恢复可能会导致数据不一致。
MVCC支持：Undo log是实现MySQL的MVCC机制的重要组成部分。通过记录被修改数据的旧值，可以在事务的隔离级别下提供一致的读取视图，避免了读取脏数据和不可重复读的问题。Binlog并不直接支持MVCC，它主要用于数据备份、恢复和同步。

使用Undo log来支持事务的回滚操作和MVCC可以提高事务回滚的效率和性能，并保证数据的一致性。Binlog虽然在事务恢复和数据同步方面有其作用，但与Undo log相比，它们在功能和实现方式上有所区别。

6.3.4.5 MVCC具体实现

具体的实现是，在数据库的每一行中，添加额外的

三个字段：(隐藏字段)

DB_TRX_ID – 记录插入或更新该行的最后一个事务的事务 ID
DB_ROLL_PTR – 指向改行对应的 undolog 的指针
DB_ROW_ID – 单调递增的行 ID，他就是 AUTO_INCREMENT 的主键 ID

innodb 拥有一个自增的全局事务 ID，每当一个事务开启，在事务中都会记录当前事务的唯一 id，而全局事务 ID 会随着新事务的创建而增长。
同时，新事务创建时，事务系统会将当前未提交的所有事务 ID 组成的数组传递给这个新事务，本文的下面段落我们称这个数组为 TRX_ID 集合。

6.3.4.5.1 TRX_ID 集合详细解释：

在事务系统中，当一个新的事务被创建时，事务管理器会将当前未提交的所有事务 ID 组成的数组传递给这个新事务。这个数组通常被称为 TRX_ID 集合。TRX_ID 集合的目的是让新事务能够了解当前系统中正在进行的其他活动事务，并根据需要进行适当的处理。

TRX_ID 集合的组成：TRX_ID 集合是一个包含了当前未提交的所有事务 ID 的数组。事务 ID 是用来唯一标识事务的值。每当一个新的事务开始时，都会被分配一个唯一的事务 ID。
传递给新事务：当新事务被创建时，事务管理器会将 TRX_ID 集合传递给它。这样，新事务就能够知道当前系统中正在进行的其他事务的存在。
了解并处理其他事务：通过获得 TRX_ID 集合，新事务可以了解其他事务的存在、状态以及可能对共享资源造成的影响。这样，新事务就能够根据需要进行适当的处理，例如根据其他事务的锁状态来决定是否等待锁的释放或选择回滚自己的操作。
并发控制与事务隔离性：TRX_ID 集合在并发控制和事务隔离性中起着重要的作用。通过比较 TRX_ID 集合中的事务 ID 和当前事务的事务 ID，可以确定其他事务是否与当前事务存在冲突。这样可以实现一致性和隔离性的目标，避免数据访问冲突和并发问题。

TRX_ID 集合是一个包含了当前未提交的所有事务 ID 的数组，用于传递给新事务并让它了解当前系统中正在进行的其他活动事务。这样新事务就能够根据需要进行适当的处理，确保并发控制和事务隔离性的正确性和一致性。

6.3.4.5.2 快照读

正如我们前面介绍的，每当一个事务更新一条数据时，都会在写入对应 undo log 后将这行记录的隐藏字段 DB_TRX_ID 更新为当前事务的事务 ID，用来表明最新更新该数据的事务是该事务。

当另一个事务去 select 数据时，读到该行数据的 DB_TRX_ID 不为空并且 DB_TRX_ID 与当前事务的事务 ID 是不同的，这就说明这一行数据是另一个事务修改并提交的。

6.3.4.5.2.1这行数据究竟是在当前事务开启前提交的还是在当前事务开启后提交的呢？

通过 TRX_ID 集合，就很容易判断这个问题了，如果这一行数据的

DB_TRX_ID 在 TRX_ID 集合中或大于当前事务的事务 ID：

那么就说明这行数据是在当前事务开启后提交的，否则说明这行数据是在当前事务开启前提交的。

对于当前事务开启后提交的数据，当前事务需要通过隐藏段的 DB_ROLL_PTR 字段找到 undo log，然后进行逻辑上的回溯才能拿到事务开启时的原数据。
这个通过 undo log + 数据行获取到事务开启时的原始数据的过程就是“快照读”。

6.3.4.5.3 当前读

数据库中，当我们进行诸如插入（insert）、更新（update）"select ... for update"等操作时，通常只需要读取当前行的数据，并不需要回溯到事务开启前的数据状态。下面对这些操作进行简要说明：

插入（insert）：当执行插入操作时，我们只需要将新插入的数据写入数据库，不需要关注事务开始前的数据状态。
更新（update）：更新操作是指将已有行的数据进行修改。在执行更新操作时，通常只涉及当前的数据行，而不需要考虑之前的数据状态。
"select ... for update"：这也是一种查询语句，但它与前面的查询语句有所不同。执行该语句时会获取排他锁，以防止其他事务读取或修改相同的数据行。同样地，它只需要读取当前行的数据，并保持排他锁直到事务结束，无需回溯到事务开启前的数据状态。

这些操作通常只需要操作当前行的数据，并不需要回溯到事务开启前的数据状态。这在数据库处理中非常常见，使得数据库的读取操作更加高效和快速。

"select ... for update"当前读详细解释：

"select ... for update"语句在读取当前行的数据时，确实不需要回溯到事务开启前的数据状态，也不会判断该行是否被其他事务修改。

这是因为"select ... for update"语句的主要目的是获取排他锁，以确保当前事务对所选定的数据行具有独占的访问权限。它并不关心数据行在事务开启前的状态，也不关心其他事务是否修改了这些行。

当执行"select ... for update"语句时，数据库会在读取符合条件的行的同时对这些行加上排他锁。这个锁的作用是防止其他事务同时读取或修改这些行。而对于已经被其他事务修改的数据行，在当前事务读取时并不会进行特殊处理，而是直接返回最新的数据。

如果当前事务需要关注数据行的修改情况，可以在读取后通过其他方式进行判断。例如，可以在事务中加入适当的检查逻辑，来验证读取的数据是否符合预期。这样可以保证数据的一致性和完整性，并避免对过期数据的使用。

6.3.4.5.3.1 悲观锁和排他锁例子：

当一个事务执行"select ... for update"语句时，它会在读取符合条件的行的同时对这些行加上排他锁，从而防止其他事务同时读取或修改这些行。但是对于已经被其他事务修改的数据行，在当前事务读取时并不会进行特殊处理，而是直接返回最新的数据。

如果当前事务需要关注数据行的修改情况有以下几种方式可以实现：

悲观锁：在事务中显示地使用排他锁来保证数据的一致性和完整性。例如，在读取数据之前先加上排他锁，然后再进行后续的操作。这样可以确保当前事务对所选定的数据行具有独占的访问权限，避免了其他事务的干扰。
乐观锁：在事务中使用乐观锁，即先读取数据，然后在更新数据时进行版本控制，判断该数据是否被其他事务修改过。如果数据没有被修改，则更新数据；否则，发生冲突，需要回滚事务或重新尝试更新数据。这样可以在不使用排他锁的情况下保证数据的一致性和完整性。

乐观锁

有一个银行账户表account，其中包含以下字段：id, name, balance, version。每次更新数据时，都需要通过version字段来控制版本。在事务中执行以下操作：

先读取数据：SELECT id, name, balance, version FROM account WHERE id = 100;
根据当前读取的数据，进行计算和处理，并准备更新balance和version字段的值；
更新数据：UPDATE account SET balance = 5000, version = version + 1 WHERE id = 100 and version = 1;

先通过SELECT语句读取id为100的账户的数据，并将版本号version的值为1保存到事务中。然后，根据当前读取的数据，进行计算和处理，例如扣款、转账等操作。最后，通过UPDATE语句更新数据时，加上版本号的控制条件，确保只有版本号为1的账户数据被更新。如果由于其他事务的修改导致该数据行的版本号已经不是1，则更新失败，需要回滚事务或重新尝试更新数据。

这样的处理方式可以避免使用排他锁，提高并发性能，同时也能保证数据的一致性和完整性。

6.3.4.5.4 MVCC 解决不可重复读、幻读的实现

6.3.4.5.4.1. 不可重复读与幻读

“不可重复读”与“幻读”是两个数据库常见的极易混淆的问题。
不可重复读指的是，在一个事务开启过程中，当前事务读取到了另一事务提交的修改。
幻读则指的是，在一个事务开启过程中，读取到另一个事务提交导致的数据条目的新增或删除。

6.3.4.5.4.2 可重复读的隔离级别是否解决了不可重复读与幻读问题呢?

上面我们提到，对于正常的 select 查询 innodb 实际上进行的是快照读，即通过判断读取到的行的 DB_TRX_ID 与 DB_ROLL_PTR 字段指向的 undo log 回溯到事务开启前或当前事务最后一次更新的数据版本，从而在这样的场景下避免了可重复读与幻读的问题。
针对已存在的数据，insert 和 update 操作虽然是进行当前读，但 insert 与 update 操作后，该行的最新修改事务 ID 为当前事务 ID，因此读到的值仍然是当前事务所修改的数据，不会产生不可重复读的问题。

但如果当前事务更新到了其他事务新插入并提交了的数据，这就会造成该行数据的 DB_TRX_ID 被更新为当前事务 ID，此后即便进行快照读，依然会查出该行数据，产生幻读。

6.3.4.5.4.3 可重复读隔离级别下幻读产生

在可重复读隔离级别下，事务在开始时会创建一个快照，并且整个事务过程中都使用这个快照来读取数据。这个快照会包含事务开始时数据库的状态，包括每行数据的版本信息（DB_TRX_ID）。

然而，如果当前事务更新了其他事务新插入并提交了的数据，就会导致被更新的行的版本信息发生变化。具体来说，被更新的行的DB_TRX_ID会被修改为当前事务的ID。这将导致这些行能够被当前事务看到，即使是在进行快照读的情况下也是如此。这样就可能产生幻读问题。

例子来说明这种情况：

1. 创建表

CREATE TABLE `ZLW` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `value` int(10) unsigned NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

2. 插入数据

INSERT INTO `ZLW` (`value`) VALUES (1), (2), (3)

3. 在两个窗口中分别开启A，B事务并查询出现有数据：

4. 在A事务中先更新 id 为 1 的数据，再插入一条 id 为 4 的数据，再删除 id 为 2 的数据，然后。

5. 在B事务中查询，可以看到此时查询出来的仍然是事务开启时的初始数据，说明当前隔离级别和场景下并没有脏读的问题存在。

6. 然后我们提交A事务所有的修改，接着在B事务中查询，可以看到此时查询到的结果仍然是事务开启前的原始数据，说明当前隔离级别和场景下并没有不可重复读和幻读的问题存在：

7. 我们在B事务中执行一条修改

updata ZLW 
set 'value'=110
where id>=3;

本应在B事务中只影响一行的 update 操作返回了 changed: 2，接着，查询结果将会出现 id 为 4 的行，说明了幻读问题的存在：

6.3.4.5.4.4可重复读隔离级别下幻读产生的解决方案

在InnoDB中，可重复读隔离级别通过MVCC（多版本并发控制）来解决不可重复读和幻读的问题。MVCC使用了快照读的机制，在读取数据时仅仅获取事务开始时的数据库快照，并且保留了所有历史版本的数据。

对于不可重复读的问题，由于事务在读取数据时只会获取到事务开始时的快照，因此即使其他事务在此期间修改了数据并提交，当前事务也不会读取到其他事务的修改，从而避免了不可重复读的问题。

对于幻读的问题，MVCC使用了行级锁和间隙锁来解决。行级锁用于保护已经存在的行数据，而间隙锁则用于保护不存在的行数据。当一个事务对一个范围进行读取时，如果其他事务正在该范围内插入或删除数据，则当前事务会被阻塞，以避免幻读的发生。

需要注意的是，在可重复读隔离级别下，当一个事务读取到数据后，其他事务对该数据的修改并不会立即反映到当前事务的结果中。这是因为当前事务读取的是快照而非实时数据。只有当当前事务提交时，才会将当前事务所做的所有修改一起提交并生效。

总之，通过MVCC和快照读机制，可重复读隔离级别可以有效地解决不可重复读和幻读的问题，提供了更稳定和可靠的数据一致性保证。文章来源地址https://www.toymoban.com/news/detail-697663.html

到了这里，关于2023年MySQL实战核心技术第三篇的文章就介绍完了。如果您还想了解更多内容，请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章，希望大家以后多多支持TOY模板网！

2023年MySQL实战核心技术第三篇