MySQLのトランザクションとMVCCの原理を詳しく解説した記事

この記事では、MySQL のトランザクションを理解し、MVCC の原理を紹介します。お役に立てれば幸いです。

#01 トランザクションとは何ですか?

データベース トランザクションは、一連のデータ操作を指します。トランザクション内の操作はすべて成功するか、すべて失敗します。何も行われません。実際、何も行われないわけではありません。可能性があります。一部は完了していますが、1 つのステップが失敗した場合はすべての操作をロールバックする必要があり、これは少しノンストップの操作となります。

MySQL では、トランザクション サポートはエンジン層で実装されます。 MySQL は複数のエンジンをサポートするシステムですが、すべてのエンジンがトランザクションをサポートしているわけではありません。 たとえば、MySQL のネイティブ MyISAM エンジンはトランザクションをサポートしていません。これが、MyISAM が InnoDB に置き換えられた重要な理由の 1 つです。

1.1 4 つの主要な特徴

原子性: トランザクションの開始後、すべての操作は完了または完了します。そうすれば、途中で立ち往生することはありません。トランザクションの実行中にエラーが発生した場合、トランザクションが開始される前の状態にロールバックされ、すべての操作は発生しなかったかのように行われます。つまり、物事は、化学で習う物質の基本単位である原子のように、分割できない全体であるということです。
• 一貫性: トランザクションの開始および終了の前後で、データベースの整合性制約に違反しません。たとえば、A が B にお金を送金する場合、A がそのお金を差し引くが、B が受け取らないということは不可能です。
• 分離: 同時に同じデータを要求できるのは 1 つのトランザクションだけであり、異なるトランザクション間の干渉はありません。たとえば、A は銀行カードからお金を引き出していますが、B は A の引き出しプロセスが完了するまでこのカードにお金を送金することはできません。
• 耐久性 (耐久性): トランザクションの完了後、トランザクションによるデータベースへのすべての更新はデータベースに保存され、ロールバックすることはできません。

1.2 分離レベル

SQL トランザクションの 4 つの主要な特性のうち、アトミック性、一貫性、耐久性はすべて比較的簡単に実現できます。理解する。しかし、トランザクションの分離レベルは確かに難しいので、今日は主に MySQL トランザクションの分離について説明します。

SQL の標準トランザクション分離は、低レベルから高レベルまで次のとおりです:

read uncommitted (コミットされていない読み取り)、read commited (コミットされた読み取り)、Repeatable Read (反復可能な読み取り)、および Serializable (シリアル化可能)。レベルが高くなるほど、効率は低くなります。

Read uncommitted: トランザクションがコミットされていない場合、トランザクションが行った変更は他のトランザクションから参照できます。
• 読み取りコミット: トランザクションがコミットされた後、その変更は他のトランザクションによって認識されます。
• 反復読み取り: トランザクションの実行中に表示されるデータは、トランザクションの開始時に表示されるデータと常に一致します。もちろん、反復可能な読み取り分離レベルでは、コミットされていない変更も他のトランザクションからは見えません。
• シリアル化: 名前が示すように、レコードの同じ行に対して、「書き込み」は「書き込みロック」を追加し、「読み取り」は「読み取りロック」を追加します。読み取り/書き込みロックの競合が発生した場合、後でアクセスされるトランザクションは、実行を続行する前に、前のトランザクションの完了を待つ必要があります。
• したがって、この分離レベルにあるすべてのデータは最も安定していますが、パフォーマンスも最悪です。

1.3 解決された同時実行性の問題

SQL トランザクション分離レベルは、同時実行性の問題を最大限に解決するように設計されています。

ダーティ リード: トランザクション A はトランザクション B によって更新されたデータを読み取り、その後 B が操作をロールバックします。その後、A によって読み取られたデータはダーティ データです
• Non-repeatable read: トランザクション A にはさらに多くのデータがありますトランザクション A が同じデータを複数回読み取ると、トランザクション B がデータを更新して送信するため、トランザクション A が同じデータを複数回読み取ると結果が不一致になります。
• ファントム リーディング: システム管理者 A は、データベース内のすべての生徒の成績を特定のスコアから ABCDE の成績に変更しましたが、システム管理者 B はこの時点で特定のスコアのレコードを挿入しました。完了した後、幻覚を見たかのように、まだ修正されていない記録が 1 つあることに気づきました。これをファントム リーディングと呼びます。

SQL 次の表に示すように、トランザクション分離レベルが異なれば、同時実行性の問題も解決できます。

シリアル化された分離レベルのみが 3 つの問題すべてを解決し、他の 3 つの分離レベルにはそれぞれ欠陥があります。

#トランザクション分離レベル未コミットの読み取りコミット済みの読み取り 繰り返し読み取り連載##不可能##不可能#不可能

ダーティ リード	Non-repeatable read	ファントム リード
可能	可能	可能
不可能	可能	可能
不可能	不可能	可能
		# 追記: 非反復読み取りとファントム読み取りは混同されやすいですが、非反復読み取りは変更に重点が置かれ、ファントム読み取りは追加または削除に重点が置かれます。非反復読み取りの問題を解決するには、条件を満たす行をロックするだけで済みますが、ファントム読み取りの問題を解決するには、テーブルをロックする必要があります 1.4例をあげてください これは少しわかりにくいかもしれないので、例を挙げてみましょう。以前のテーブル構造とテーブル データはそのままです CREATE TABLE `student`  (   `id` int(11) NOT NULL AUTO_INCREMENT,   `name` varchar(100) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL,   `age` int(11) NULL DEFAULT NULL,   PRIMARY KEY (`id`) USING BTREE ) ENGINE = InnoDB AUTO_INCREMENT = 66 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Compact; 今、2 つの食品を同時に開始したいとします。トランザクション A は生徒に次の質問をします。 id = 2 年齢、トランザクション B は id = 2 の生徒の年齢を更新します。プロセスは次のとおりです。 4 つの分離レベルでの X1、X2、および X3 の値は何ですか? コミットされていない読み取り: トランザクション B はコミットされていないものの、その変更はすでにコミットされているため、X1 の値は 23 です。見る。 (B が後で X1 の値をロールバックすると、その値はダーティになります)。 X2 と X3 の値も 23 です。これは理解できます。 送信された読み取り: B は変更されましたが、A はそれを認識できないため、X1 の値は 22 です。 (B が後でロールバックされた場合、X1 の値は変更されないため、ダーティ リードの問題は解決されます) X2 と 繰り返し読み取り可能: X1 および(この期間中に B がどのように変更されても、A が送信されない限り、B は表示されません。これにより、反復不可能な読み取りの問題が解決されます)、X3 の値は 23 です。 A が送信され、B が表示されます。値が変更されました。 シリアル化: A がコミットするまで、変更の実行中、B はロックされます。 B は実行を続行できます。 (A が読み取り中、B は書き込みできません。この時点でデータが最新であることを確認する必要があります。ファントム読み取りの解決) したがって、X1 と X2 は両方とも 22 で、最後の X3 は B の後に実行されます。送信され、その値は 23 です。 では、なぜそのような結果が起こるのでしょうか?トランザクション分離レベルはどのように実装されますか? トランザクション分離レベルはどのように実装されますか?私は Geek Time での Ding Qi 先生のクラスで答えを見つけました: 実際、データベース内にビューが作成され、アクセス時にはビューの論理結果が優先されます。 「Repeatable Read」分離レベルでは、このビューはトランザクションの開始時に作成され、トランザクション全体で使用されます。 「読み取りコミット」分離レベルでは、このビューは各 SQL ステートメントの先頭に作成されます。ここで注意する必要があるのは、「read uncommitted」分離レベルでは、ビューの概念なしでレコードの最新の値が直接返されるのに対し、「serialization」分離レベルでは、並列を避けるためにロックが直接使用されることです。アクセス＃＃＃。 1.5 トランザクション分離レベルの設定 データベースごとにデフォルトのトランザクション分離レベルも大きく異なります。Oracle データベースのデフォルトの分離レベルは次のとおりです。 Read commit ですが、MySQL は repeatable read です。したがって、システムでデータベースを Oracle から MySQL に移行する必要がある場合は、予期せぬ問題を回避するために、レベルを移行前のレベル (読み取りコミット) と一致するように設定してください。 #1.5.1 トランザクション分離レベルの確認 # # 查看事务隔离级别 5.7.20 之前 SELECT @@transaction_isolation show variables like 'transaction_isolation'; # 5.7.20 以及之后 SELECT @@tx_isolation show variables like 'tx_isolation' +---------------+-----------------+ | Variable_name | Value           | +---------------+-----------------+ | tx_isolation  | REPEATABLE-READ | +---------------+-----------------+ #1.5.2 分離レベルの設定 分離レベルを変更するステートメントの形式は次のとおりです: set [スコープ] トランザクション分離レベル [トランザクション分離レベル]スコープはオプションです: SESSION (セッション)、GLOBAL ( global); 分離レベル 上記の 4 つであり、大文字と小文字は区別されません。 例: グローバル分離レベルを read-commit に設定します set global transaction isolation level read committed; 1.6 トランザクションの起動 MySQL トランザクションの起動は次のようにいくつかの方法があります: 显式启动事务语句， begin 或 start transaction。配套的提交语句是 commit，或者回滚语句是 rollback。 # 更新学生名字 START TRANSACTION; update student set name = '张三' where id = 2; commit; set autocommit = 0，这个命令会将线程的自动提交关掉。意味着如果你只执行一个 select 语句，这个事务就启动了，而且并不会自动提交。这个事务持续存在直到你主动执行 commit 或 rollback 语句，或者断开连接。 set autocommit = 1，表示 MySQL 自动开启和提交事务。 比如执行一个 update 语句，语句只完成后就自动提交了。不需要显示的使用 begin、commit 来开启和提交事务。所以当我们执行多个语句的时候，就需要手动的用 begin、commit 来开启和提交事务。 start transaction with consistent snapshot；上面提到的 begin/start transaction 命令并不是一个事务的起点，在执行到它们之后的第一个操作 InnoDB 表的语句，事务才真正启动。如果你想要马上启动一个事务，可以使用 start transaction with consistent snapshot 命令。 第一种启动方式，一致性视图是在执行第一个快照读语句时创建的； 第二种启动方式，一致性视图是在执行 start transaction with consistent snapshot 时创建的。 02 事务隔离的实现 理解了隔离级别，那事务的隔离是怎么实现的呢？要想理解事务隔离，先得了解 MVCC 多版本的并发控制这个概念。而 MVCC 又依赖于 undo log 和 read view 实现。 2.1 什么是 MVCC？ 百度上的解释是这样的： MVCC，全称 Multi-Version Concurrency Control，即多版本并发控制。MVCC 是一种并发控制的方法，一般在数据库管理系统中，实现对数据库的并发访问，在编程语言中实现事务内存。 MVCC 使得数据库读不会对数据加锁，普通的 SELECT 请求不会加锁，提高了数据库的并发处理能力；数据库写才会加锁。 借助 MVCC，数据库可以实现 READ COMMITTED，REPEATABLE READ 等隔离级别，用户可以查看当前数据的前一个或者前几个历史版本，保证了 ACID 中的 I 特性（隔离性)。 MVCC 只在 REPEATABLE READ 和 READ COMMITIED 两个隔离级别下工作。其他两个隔离级别都和 MVCC 不兼容 ，因为 READ UNCOMMITIED 总是读取最新的数据行，而不是符合当前事务版本的数据行。而 SERIALIZABLE 则会对所有读取的行都加锁。 2.1.1 InnDB 中的 MVCC InnDB 中每个事务都有一个唯一的事务 ID，记为 transaction_id。它在事务开始时向 InnDB 申请，按照时间先后严格递增。 而每行数据其实都有多个版本，这就依赖 undo log 来实现了。每次事务更新数据就会生成一个新的数据版本，并把  transaction_id 记为 row trx_id。同时旧的数据版本会保留在 undo log 中，而且新的版本会记录旧版本的回滚指针，通过它直接拿到上一个版本。 所以，InnDB 中的 MVCC 其实是通过在每行记录后面保存两个隐藏的列来实现的。一列是事务 ID：trx_id；另一列是回滚指针：roll_pt。 2.2 undo log 回滚日志保存了事务发生之前的数据的一个版本，可以用于回滚，同时可以提供多版本并发控制下的读（MVCC），也即非锁定读。 根据操作的不同，undo log 分为两种： insert undo log 和 update undo log。 2.2.1 insert undo log insert 操作产生的 undo log，因为 insert 操作记录没有历史版本只对当前事务本身可见，对于其他事务此记录不可见，所以 insert undo log 可以在事务提交后直接删除而不需要进行 purge 操作。 purge 的主要任务是将数据库中已经 mark del 的数据删除，另外也会批量回收 undo pages 所以，插入数据时。它的初始状态是这样的： 2.2.2 update undo log UPDATE 和 DELETE 操作产生的 Undo log 都属于同一类型：update_undo。（update 可以视为 insert 新数据到原位置，delete 旧数据，undo log 暂时保留旧数据）。 事务提交时放到 history list 上，没有事务要用到这些回滚日志，即系统中没有比这个回滚日志更早的版本时，purge 线程将进行最后的删除操作。 1 つのトランザクションが現在のデータを変更します: 別のトランザクションがデータを変更します: データベース内に同じレコードの複数のバージョンが存在します。これが、前述のマルチバージョン同時実行制御 MVCC です。 さらに、アンドゥ ログを利用してロールバックすることで、以前のバージョンの状態に戻すことができます。たとえば、V1 に戻すには、2 つのロールバックを順番に実行するだけで済みます。 2.3 読み取りビュー #読み取りビューは、RC の実装をサポートするために MVCC を実装するときに InnDB によって使用される一貫した読み取りビューです。 (Read Committed) および RR (Repeatable Read) 分離レベル 。 read ビューは実際には存在せず、単なる概念であり、undo ログはその具体化です。主にバージョンとアンドゥログを通じて計算されます。この機能は、トランザクションが参照できるデータ を決定することです。 各トランザクションまたはステートメントには、独自の整合性ビューがあります。通常のクエリ ステートメントは一貫した読み取りです。一貫した読み取りは、行 trx_id と一貫したビュー に基づいてデータ バージョンの可視性を決定します。 2.3.1 データ バージョンの可視性ルール 読み取りビューには、主に現在のシステム内の他のアクティブな読み取りおよび書き込みトランザクションが含まれます。この実装では、InnDB はトランザクションごとに配列を構築し、トランザクションが開始された時点で現在 アクティブな (まだ送信されていない) トランザクション を保存します。 前述したように、トランザクション ID は時間の経過とともに厳密に増加します。 システム内で送信されたトランザクション ID の最大値が配列の最低水位として記録され、作成されたトランザクション ID 1 は高水位として記録されます。 このビュー配列と最高水位は、現在のトランザクションの整合性ビュー (読み取りビュー) を形成します。 この配列を図に描くと、次のようになります。 ルールは次のとおりです: 1 trx_id が灰色の領域にある場合、trx_id がアクセスされたバージョンの は、配列内の最低水位の ID 値より小さいです。つまり、このバージョンを生成したトランザクションは読み取りビューを生成する前にコミットされているため、このバージョンは表示され、現在のトランザクションからアクセスできます。 。 2 trx_id がオレンジ色の領域にある場合、アクセスされたバージョンの trx_id が配列内の高水位の ID 値より大きいこと、つまり、このバージョンを生成したトランザクションが生成されたことを意味します。読み取りビューが生成された後であるため、このバージョンは表示されず、現在のトランザクションにアクセスすることはできません。 3 緑色の領域にある場合は、次の 2 つの状況が考えられます: a) trx_id が配列内にあり、このバージョンがコミットされていないバージョンによって生成されたことを証明します。トランザクション、非表示 b) trx_id は配列内にありません。これは、このバージョンがコミットされたトランザクションによって生成されたことを証明します。表示可能 3 番目のポイントです。チュートリアル中、私は少し混乱しましたが、幸運なことに一部の熱心なネチズンは次のように答えました: 緑色の領域にある場合は、トランザクション ID が低水位と高水位の範囲内にあることを意味します。本当に見えるかどうかは、これが緑色の領域にあるかどうかによって決まります。緑色の領域にこのトランザクション ID がない場合は表示され、ある場合は表示されません。この範囲内にあるということは、この範囲にこの値があるという意味ではありません ([1,2,3,5]、4 はこの配列の 1 ～ 5 の範囲内にありますが、この配列内にはありません)。 これは少し理解するのが難しいかもしれません。3 つのトランザクションが同じデータのクエリと更新を行うというシナリオを想定しています。理解を容易にするために図を描きました: 元のデータは次のとおりです。ID = 2 の Zhang San の情報を更新します: 上の写真について質問させていただきます。 RC (読み取りコミット) および RR (反復読み取り) 分離レベルでは、T4 および T5 時点でのクエリ経過時間の値はそれぞれいくらですか? T4 の更新された値は何ですか? 少し考えてみてください。誰もが独自の答えを持っていると思います。答えは記事の最後にありますので、自分なりの疑問を持ちながら読み進めていただければと思います。 2.3.2 RR (Repeatable Read) での結果 RR レベルでは、クエリはトランザクションが実行される前にのみ認識されます。送信された完了したデータについては、トランザクションが開始されるとビューが構築されます。したがって、一貫性のあるスナップショット コマンドでトランザクションの開始を使用すると、ビューがすぐに作成されます。 现在假设： 事务 A 开始前，只有一个活跃的事务，ID = 2， 已提交的事务也就是插入数据的事务 ID = 1 事务 A、B、C 的事务 ID 分别是 3、4、5 在这种隔离级别下，他们创建视图的时刻如下： 根据上图得，事务 A 的视图数组是[2,3]；事务 B 的视图数组是 [2,3,4]；事务 C 的视图数组是[2,3,4,5]。分析一波： T4 时刻，B 读数据都是从当前版本读起，过程是这样的： 读到当前版本的 trx_id = 4，刚好是自己，可见 所以 age = 24 T5 时刻，A 读数据都是从当前版本读起，过程是这样的： 读到当前版本的 trx_id = 4，比自己视图数组的高水位大，不可见 再往上读到 trx_id = 5，比自己视图数组高水位大，不可见 再往上读到 trx_id = 1，比自己视图数组低水位小，可见 所以 age = 22 这样执行下来，虽然期间这一行数据被修改过，但是事务 A 不论在什么时候查询，看到这行数据的结果都是一致的，所以我们称之为一致性读。 其实视图是否可见主要看创建视图和提交的时机，总结下规律： 版本未提交，不可见 版本已提交，但在视图创建后提交，不可见 版本已提交，但在视图创建前提交，可见 2.3.2.1 快照读和当前读 事务 B 的 update 语句，如果按照上图的一致性读，好像结果不大对？ 如下图周明，B 的视图数组是先生成的，之后事务 C 才提交。那就应该看不见 C 修改的 age = 23 呀？最后 B 怎么得出 24 了？ 没错，如果 B 在更新之前执行查询语句，那返回的结果肯定是 age = 22。问题是更新就不能在历史版本更新了呀，否则 C 的更新不就丢失了？ 所以，更新有个规则：更新数据都是先读后写（读是更新语句执行，不是我们手动执行），读的就是当前版本的值，叫当前读；而我们普通的查询语句就叫快照读。 因此，在更新时，当前读读到的是 age = 23，更新之后就成 24 啦。 2.3.2.2 select 当前读 除了更新语句，查询语句如果加锁也是当前读。如果把事务 A 的查询语句 select age from t where id = 2 改一下，加上锁（lock in mode 或者 for update），也都可以得到当前版本 4 返回的 age = 24 下面就是加了锁的 select 语句： select age from t where id = 2 lock in mode;  select age from t where id = 2 for update; 2.3.2.3 事务 C 不马上提交 假设事务 C 不马上提交，但是 age = 23 版本已生成。事务 B 的更新将会怎么走呢？ 事务 C 还没提交，写锁还没释放，但是事务 B 的更新必须要当前读且必须加锁。所以事务 B 就阻塞了，必须等到事务 C 提交，释放锁才能继续当前的读。 2.3.3 RC（读提交）下的结果 在读提交隔离级别下，查询只承认在语句启动前就已经提交完成的数据；每一个语句执行之前都会重新算出一个新的视图。 注意：在上图的表格中用于启动事务的是 start transaction with consistent snapshot 命令，它会创建一个持续整个事务的视图。所以，在  RC 级别下，这命令其实不起作用。等效于普通的 start transaction（在执行 sql 语句之前才算是启动了事务）。所以，事务 B 的更新其实是在事务 C 之后的，它还没真正启动事务，而 C 已提交。 现在假设： トランザクション A が開始される前は、アクティブなトランザクションが 1 つだけあります (ID = 2、 コミットされたトランザクションは、データを挿入するトランザクション ID = 1です) トランザクション A、B 、C のトランザクション ID はそれぞれ 3、4、5 です。 この分離レベルでは、ビューが作成される時間は次のとおりです。 上図によると、トランザクション A のビュー配列は [2,3,4] ですが、高水位が 6 以上 (トランザクション ID 1 が作成されている)、ビュー配列トランザクション B のビュー配列は [2,4]、トランザクション C のビュー配列は [2,5] です。分析の波: T4 時間に、B は現在のバージョンからデータを読み取ります。プロセスは次のとおりです: 現在のバージョンの trx_id を読み取りますversion = 4、これはたまたま私自身です。 so age = 24 時刻 T5 で、A は現在のデータからデータを読み取ります。 trx_id = 4 の現在のバージョンを読み取ります。これは独自の整合性ビューの範囲内ですが、4 が含まれており、非表示です さらに上に進むと、trx_id = 5 になります。これは、独自の整合性ビューの整合性ビュー範囲内ですが、5 は含まれておらず、表示されます ため、age = 23 #03 巨人の肩 #cnblogs.com/wyaokai/p/10921323.html time.geekbang.org/column/article/70562 zhuanlan.zhihu.com/p/117476959 cnblogs.com/xd502djj/p/6668632.html blog.csdn.net/article/details/109044141 blog.csdn.net/u014078930/article/details/ 99659272 04 概要 この記事では、次のようなトランザクションのあらゆる側面について詳しく説明します。 4 つの主な機能、分離レベル、解決された同時実行の問題、分離レベルの設定と表示方法、トランザクションの開始方法など。さらに、RR と RC の 2 つの絶縁レベルがどのように達成されるかについても深く理解できました。 MVCC、アンドゥ ログ、読み取りビューが連携して MVCC を実装する方法についての詳細な説明が含まれます。最後に、スナップショットの読み取り、現在の読み取りなどについて話しました。不倫に関する知識のすべてがここにあると言えるでしょう。この記事を読んでもまだ理解できない場合は、私に殴りに来てください。 [関連する推奨事項: mysql ビデオ チュートリアル ]

以上がMySQLのトランザクションとMVCCの原理を詳しく解説した記事の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。