MySQL プロジェクトでトランザクション分離レベルを選択する方法

導入

コンテンツから始めましょう。誰もが次のような面接のシナリオに遭遇したことがあると思います。

インタビュアー: 「MySQL にはトランザクション分離レベルがいくつありますか?」
あなた: 「コミットされていない読み取り、コミットされた読み取り、繰り返し読み取り、シリアル化可能! デフォルトは繰り返し読み取りです」
インタビュアー: 「なぜ MySQL はデフォルトの分離レベルとして繰り返し読み取りを選択するのですか?」
（どう答えていいか分からず、苦々しい表情をしていますね！）
インタビュアー: 「プロジェクトではどの分離レベルを選択しましたか? その理由は?」
あなた: 「もちろん、デフォルトの繰り返し読み取りです。理由は...うーん...」
（その後、戻って通知を待つことができます！）

上記のような恥ずかしい状況を避けるために、読み続けてください。

Mysql のデフォルトのトランザクション分離レベルは、Repeatable Read です。Mysql は、インターネットプロジェクトでも、変更を加えずにデフォルトの分離レベルを使用しますか?
いいえ、私たちのプロジェクトでは通常、Read Commited 分離レベルを使用します。

何！実際には Read Committed です。インターネットでは、この分離レベルでは不可重復讀と幻讀の問題があると言われていませんか?ご心配なく？さて、この記事は私たちの質問から始めましょう!

文章

まず、質問について考えてみましょう。Oracle と SqlServer では、デフォルトの分離レベルとして Read Commited が選択されています。MySQL では、デフォルトの分離レベルとして Read Commited ではなく、Repeatable Read が選択される理由は何でしょうか。

なぜ？なぜ？なぜ？

これには歴史的な理由があり、もちろんマスタースレーブレプリケーションから始める必要があります。
主從復制，是基于什么復制的？

binlog レプリケーションに基づいています。ここでは binlog の概念を取り上げたくありません。binlog はデータベースの変更を記録するファイルであるということだけ理解してください。

binlog有幾種格式？

はい、3つのタイプがあります。

ステートメント: 変更SQLステートメントを記録する
行: 各行の実際のデータの変更を記録します
混合: ステートメントモードと行モードの混合

MySQL 5.0 より前では、binlog はSTATEMENT形式のみをサポートします。ただし、この形式では、Read Commited の分離レベルにおけるマスタースレーブレプリケーションにバグがあるため、MySQL では、デフォルトの分離レベルとして Repeatable Read が使用されます。
次に、binlog がSTATEMENT形式であり、分離レベルが Read Commited の場合にどのようなバグが発生するかについて説明します。下の図に示すように、マスター上で次のトランザクションを実行します。

このとき、マスター上で次の文を実行します。

テストから*を選択します。

出力は次のようになります

+---+
| バ |
+---+
| 3 |
+---+
セット内の1行

ただし、この時点でスレーブでこのステートメントを実行すると、出力は次のようになります。

空のセット

このように、マスターとスレーブの不整合という問題が発生します。理由は実は非常に単純で、マスターでの実行順序は最初に削除し、次に挿入するということです。現時点では、binlog は STATEMENT 形式になっており、レコードの順序は最初に挿入、次に削除です。スレーブは binglog と同期しているため、スレーブの実行順序がマスターの実行順序と一致しません。マスタースレーブ

矛盾している！

どうすれば解決できるでしょうか?

解決策は2つあります。
（１）分離レベルはRepeatable Readに設定され、この分離レベルでギャップロックが導入されます。 Session 1 delete ステートメントを実行すると、ギャップがロックされます。すると、挿入ステートメントの実行時にSsession 2ブロックされます。
（２）binglogのフォーマットを行形式に変更します。これは行ベースのレプリケーションなので、当然SQL実行順序の違いによる問題は発生しません。ただし、この形式は MySQL バージョン 5.1 まで導入されませんでした。したがって、歴史的な理由により、MySQL は、マスタースレーブレプリケーションで問題が発生しないように、デフォルトの分離レベルを Repeatable Read に設定しています。

MySQL がデフォルトの分離レベルとして Repeatable Read を選択する理由を理解した後、それを Read Commited と比較して、インターネットプロジェクトで分離レベルが Read Commited に設定されている理由を説明しましょう。

対比

さて、まずは一つ理解しておきましょう！ Read UnCommitted と Serializable の 2 つの分離レベルは、次の 2 つの理由によりプロジェクトでは使用されません。

Read UnCommitted を使用すると、トランザクションは別のトランザクションによってコミットされていないデータを読み取ります。言うまでもなく、これは非論理的です。
シリアル化 (Serializable) を使用すると、各読み取り操作がロックされ、スナップショットの読み取りが無効になります。この分離レベルは、通常、MySQL の組み込み分散トランザクション機能を使用する場合に使用されます。 (MySQL のこの機能は、XA トランザクション、強力な一貫性トランザクションであり、パフォーマンスが低いため、これまで一度も使用したことはありません。インターネット分散ソリューションでは、ほとんどの場合、結果整合性トランザクションソリューションが使用されます。)

言い換えれば、私たちが気にしなければならない質問は 1 つだけです。分離レベルは、コミット読み取りにすべきか、それとも繰り返し読み取りにすべきかということです。
次に、これら 2 つのレベルを比較し、トランザクション分離レベルとして Read Commited を選択する理由について説明します。
テーブル構造が次のようになっていると仮定します。

 テーブル「test」を作成します（
`id` int(11) NULLではない、
`color` varchar(20) NOT NULL,
主キー (`id`)
) エンジン=InnoDB

データは以下のとおりです

+----+-------+
| ID | 色 |
+----+-------+
| 1 | 赤 |
| 2 | 白 |
| 5 | 赤 |
| 7 | 白 |
+----+-------+

説明を簡単にするために、以下

繰り返し読み取り (RR とも呼ばれます)
Read Commited (略して RC)。

理由 1: RR 分離レベルではギャップロックが存在するため、デッドロックの可能性が RC よりもはるかに高くなります。
この時点でステートメントを実行する

更新のために、ID <3 のテストから * を選択します。

RR 分離レベルでは、ギャップ (2,5) をロックして他のトランザクションによるデータの挿入を防ぐことができるギャップロックがあります。
ただし、RC 分離レベルではギャップロックは存在せず、他のトランザクションがデータを挿入できます。

ps : デッドロックは、RC 分離レベルでデッドロックが発生しないことを意味するわけではありませんが、発生確率は RR よりも低くなります。

理由 2: RR 分離レベルでは、条件列がインデックスにヒットしない場合、テーブルがロックされます。 RC分離レベルでは、行のみがロックされます<br /> この時点で、ステートメントが実行されます

テストセット color = 'blue' を更新します (color = 'white' の場合)。

RC 分離レベルでは、最初にクラスター化インデックスを調べて完全スキャンを実行します。ロックは次のとおりです。

しかし、実際には、MySQL は最適化されています。MySQL サーバーは条件をフィルタリングし、条件が満たされていないことを検出すると、unlock_row メソッドを呼び出して、条件を満たさないレコードをロックします。

実際のロックは以下のとおりです

ただし、RR 分離レベルでは、クラスター化インデックスが完全にスキャンされ、次に示すようにテーブル全体がロックされます。

理由 3: RC 分離レベルでは、半一貫性読み取り機能により更新操作の同時実行性が向上します。

5.1.15 では、InnoDB に「半一貫性」と呼ばれる概念が導入され、同じレコード行を更新する際の競合が軽減され、ロック待機が削減されました。
いわゆる半一貫性読み取りとは、更新ステートメントがロックされたレコードの行を読み取る場合、InnoDB はレコードの最後に送信されたバージョンを返し、MySQL の上位層がこのバージョンが更新の where 条件を満たしているかどうかを判断することを意味します。条件が満たされた場合 (更新が必要)、MySQL は読み取り操作を再度開始し、今回は行の最新バージョンを読み取り (ロック) ます。
具体的な性能は以下のとおりです。
今回はセッション1とセッション2の2つのセッションがあります。
セッション1の実行

テスト セット color = 'blue' を更新します (color = 'red' の場合)。

まだトランザクションをコミットしないでください。
同時にセッション2が実行される

テストセット color = 'blue' を更新します (color = 'white' の場合)。

セッション 2 が行をロックしようとすると、その行にすでにロックが存在することがわかります。InnoDB は半一貫性読み取りを有効にし、コミットされた最新のバージョン (1、赤)、(2、白)、(5、赤)、(7、白) を返します。 MySQL は読み取り操作を再開し、今回は行の最新バージョンを読み取り (そしてロックし) ます。
RR 分離レベルでは、セッション 2 は待機することしかできません。

2つの質問

RC レベルでは、非反復読み取りの問題を解決する必要がありますか?
解決する必要はありません。この問題は許容範囲です。結局のところ、データはすでに送信されているので、読み取ることに大きな問題はありません。 Oracle のデフォルトの分離レベルは RC です。Oracle のデフォルトの分離レベルを変更したことがありますか?

RC レベルでは、マスタースレーブレプリケーションにどのような binlog 形式が使用されますか?
OK、この分離レベルでは、使用されるバイナリログは行形式、つまり行ベースのレプリケーションになります。 Innodb の創設者も、binlog にこの形式を使用することを推奨しています。

要約する

この記事はただ 1 つのことを説明するものです。インターネットプロジェクトでは、分離レベルとして Read Commited を使用してください。

これで、MySQL プロジェクトでトランザクション分離レベルを選択する方法についての記事は終了です。MySQL トランザクション分離レベルの詳細については、123WORDPRESS.COM の以前の記事を検索するか、次の関連記事を引き続き参照してください。今後とも 123WORDPRESS.COM をよろしくお願いいたします。

以下もご興味があるかもしれません: