MySQL の分離レベルの包括的な分析

データベースが同じデータ バッチを同時に追加、削除、および変更すると、ダーティ書き込み、ダーティ読み取り、反復不可能読み取り、ファントム読み取りなどの一連の問題が発生する可能性があります。 MySQL は、トランザクション分離、ロック メカニズム、MVCC マルチバージョン同時実行制御メカニズムなど、トランザクション同時実行の問題を解決するための一連のメカニズムを提供します。今日はトランザクション分離メカニズムについて調べてみましょう。

トランザクションは、SQL ステートメントのグループで構成される論理的な処理単位です。まず、トランザクションの ACID 特性を見てみましょう。

• アトミック性: トランザクションは、データを変更し、完全に実行されるか、まったく実行されないかのいずれかになるアトミック操作単位です。実行レベルから説明します。
• 一貫性: トランザクションの開始時と完了時に、データは一貫した状態を維持する必要があります。実行結果の観点から説明します。
• 分離: データベース システムは、トランザクション実行プロセスが外部から見えず、独立して実行され、外部からの影響を受けないことを保証するために、特定の分離メカニズムを提供します。
• 耐久性: トランザクションが完了すると、データへの変更は永続的になり、システム障害が発生しても維持できます。
  

同時トランザクションの影響:

• ダーティ ライト (更新の損失): 複数のトランザクションが同じ行を選択し、互いの存在を認識しないため、以前のトランザクションのデータ操作が上書きされます。
• ダーティ リード: トランザクション A は、トランザクション B がコミットしていないデータを読み取ります。トランザクション B はロールバックし、トランザクション A はコミットします。最終結果は一貫性の原則に準拠しません。
• 反復不可能な読み取り: 同じトランザクションおよび同じクエリ ステートメントを複数回実行すると、結果が矛盾する場合があります。これは、外部トランザクションによって行われた変更によって発生する可能性があり、分離要件を満たしていません。
• ファントム リード: トランザクション A は、トランザクション B によって送信された新しいデータを読み取りますが、これは分離要件を満たしていません。
  

トランザクション分離レベル:

MySQL は上記の 4 つの分離レベルを提供します。分離が厳密であるほど、問題が発生する可能性は低くなりますが、パフォーマンス コストは大きくなります。デフォルトの分離レベルは、繰り返し読み取りです。以下では、クライアントを使用して検証のための操作を実行します。

まずテーブルとデータを作成する

テーブル「アカウント」を作成します（
 `id` int(11) 符号なし NOT NULL AUTO_INCREMENT,
 `balance` int(11) デフォルト NULL,
 主キー (`id`)
)ENGINE=InnoDB デフォルト文字セット=utf8mb4;

`account` (`id`, `balance`) に挿入します
価値観
  （1,500）、
  （2,600）、
  （3、200）;

クライアントに接続し、分離レベルを確認します。繰り返し読み取りであることがわかります。

MySQL [テスト]> 'tx_isolation' のような変数を表示します。
+---------------+-----------------+
| 変数名 | 値 |
+---------------+-----------------+
| tx_isolation | 繰り返し読み取り |
+---------------+-----------------+

コミットされていないテストを読み取ります:

両方のクライアント AB は set tx_isolation='read-uncommitted'; を実行し、分離レベルを read uncommitted に設定します。

クライアント A がトランザクションを開始します: start transaction; データを照会します: select * from account;

クライアント B がトランザクションを開始します: トランザクションを開始します。データを更新します: アカウントを更新し、残高を balance - 100 に設定します (ID は 1 です)。この時点ではトランザクションはコミットされません。

クライアント A は再度データをクエリします: select * from account; これで、2 つのクエリからのデータが異なります。

A は、B がデータをコミットする前に、B によって更新されたデータを読み取ります。この時点で B がロールバックすると、A のデータはダーティになります。この状況は、コミットされていない読み取りによって発生するダーティ リードです。これは、Read Committed 分離レベルを使用することで解決できます。

commit コマンドを使用して、クライアント AB のトランザクションをコミットします。

提出されたテストを読む:

クライアント A と B は両方とも set tx_isolation='read-committed'; を実行して、分離レベルを read committed に設定します。

クライアント A がトランザクションを開始します: start transaction; データを照会します: select * from account;

クライアント B がトランザクションを開始します: トランザクションを開始します。データを更新します: アカウントを更新し、残高を balance - 100 に設定します (ID は 1 です)。この時点ではトランザクションはコミットされません。

クライアントAは再度データをクエリします: select * from account; これで、クライアントAが2回クエリしたデータは一貫しており、ダーティリードは発生していないことがわかります。

この時点で、クライアント B はトランザクションをコミットします: commit;

クライアント A は再度データをクエリします: select * from account; この時点で、クライアント A によってクエリされたデータが変更されていることがわかります。これは繰り返し不可能な読み取りです。

繰り返し読み取りテスト:

両方のクライアント AB は set tx_isolation='repeatable-read'; を実行して、分離レベルを繰り返し読み取りに設定します。

クライアント A がトランザクションを開始します: start transaction; データを照会します: select * from account;

クライアント B はトランザクションを開始します: トランザクションを開始します。データを更新します: アカウントを更新します。残高を balance - 100 に設定します (ID は 1 です)。トランザクションをコミットします。

クライアント A は再度データをクエリします: select * from account; ここで、クライアント A によって 2 回クエリされたデータは一貫しており、繰り返し読み取られたデータも一貫していることがわかります。

クライアント A は更新ステートメントを実行します: update account set balance = balance - 50 where id = 1;

クライアント A は再度データをクエリします: select * from account; このとき、id=1 のデータはクライアント B が -50 に更新した後のデータであり、データの一貫性は損なわれていません。

クライアント B はトランザクションを再度開き、データを挿入します: insert into account(id,balance) values ​​(4,1000); トランザクションをコミットします。

クライアントクエリ、結果は前回と同じ

クライアント A は、update account set balance = balance - 100 where id = 4; を実行して、クライアント B の新しく挿入されたデータを更新します。実行は成功します。すべてのデータを再度クエリすると、id = 4 のデータが挿入され、ファントム リードが発生します。

# クライアント実行プロセス: # 分離レベルの繰り返しを設定します MySQL [test]> set tx_isolation='repeatable-read';
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)
# トランザクションを開始します。MySQL [test]> start transaction;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
# すべてのデータをクエリする MySQL [test]> select * from account;
+----+---------+
| ID | 残高 |
+----+---------+
| 1 | 300 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
セット内の 3 行 (0.00 秒)
# 2 つの結果が一致しているかどうかを確認するために再度クエリを実行します。MySQL [test]> select * from account;
+----+---------+
| ID | 残高 |
+----+---------+
| 1 | 300 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
セット内の 3 行 (0.00 秒)
# クライアント B がデータを挿入すると、今度はクライアント A は MySQL をクエリできなくなります [test]> select * from account;
+----+---------+
| ID | 残高 |
+----+---------+
| 1 | 150 |
| 2 | 600 |
| 3 | 200 |
+----+---------+
セット内の 3 行 (0.00 秒)
# クライアント A はクライアント B によって挿入されたデータを更新し、更新が成功したことを確認します。MySQL [test]> update account set balance = balance + 1000 where id = 4;
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0
# 再度クエリを実行すると、データはクエリできますが、ファントム読み取りが発生します。MySQL [test]> select * from account;
+----+---------+
| ID | 残高 |
+----+---------+
| 1 | 400 |
| 2 | 600 |
| 3 | 200 |
| 4 | 2000 |
+----+---------+
セット内の 4 行 (0.00 秒)
# トランザクションをコミットする MySQL [test]> commit;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)

# B クライアント実行プロセス: 分離レベルを繰り返し読み取りに設定する MySQL [test]> set tx_isolation='repeatable-read';
クエリは正常、影響を受けた行は 0 行、警告は 1 件 (0.00 秒)
# トランザクションを開始します。MySQL [test]> start transaction;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
# データを更新し、MySQL に直接送信します [test]> update account set balance = balance - 100 where id = 1;
クエリは正常、1 行が影響を受けました (0.00 秒)
一致した行: 1 変更された行: 1 警告: 0

MySQL [テスト]>コミット;
クエリは正常、影響を受けた行は 0 行 (0.01 秒)
# トランザクションを再度開始します。MySQL [test]> start transaction;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)
# データを挿入する MySQL [test]> insert into account(id,balance) values ​​(4,1000);
クエリは正常、1 行が影響を受けました (0.01 秒)
MySQL [テスト]>コミット;
クエリは正常、影響を受けた行は 0 行 (0.00 秒)

最後のタイプのシリアル化: set tx_isolation='serializable'; は自分で検証でき、上記の問題をすべて解決できますが、一般的には使用されません。一貫性は確保されますが、パフォーマンスが大幅に低下し、同時実行性が極端に低くなります。デフォルトは繰り返し読み取りです。

分離レベルは、トランザクションの同時実行の問題をある程度処理できます。さらに、他の手段もありますが、これについては後で再度検討します。

上記は、MySQL の分離レベルに関する包括的な分析です。MySQL の分離レベルの詳細については、123WORDPRESS.COM の他の関連記事をご覧ください。