MySQLトランザクション独立性レベルと関連する例(ダーティ・リード、繰り返し不可、幻読み)

目次

ダーティリード

繰り返し不可(Non-repeatable reads)

ファントムリード

繰り返し可能読み出しレベルでの幻読み防止

シリアル化可能レベル幻読み禁止

まとめ

MySQL 8の独立性レベルの変数は、以前のバージョンとは異なり、以前はtx_isolation,MySQL 8はtransaction_に変更されましたisolation.現在の独立性レベルを表示するコマンドは

mysql> select @@global.transaction_isolation,@@transaction_isolation;

+--------------------------------+-------------------------+
| @@global.transaction_isolation | @@transaction_isolation |
+--------------------------------+-------------------------+
| REPEATABLE-READ                | REPEATABLE-READ         |
+--------------------------------+-------------------------+

その他の参考:MySQLの4つのトランザクション独立性レベルの説明

未コミットリード(Read Uncommitted):ダーティリードを許可します.つまり、他のセッションでコミットされていないトランザクション変更のデータ

を読み込むことができます.

コミットリード(Read Committed):コミットされたデータのみが読み込まれます.Oracleなど多くのデータベースでは、デフォルトではこのレベル(重複しない)

です.

リピート可能(Repeated Read):リピート可能.同じトランザクション内のクエリは、トランザクションの開始時刻に一致します.InnoDBのデフォルトレベルです.SQL規格では、この独立性レベルは重複しない読み取りを排除するが、幻読み

も存在する.

シリアルリード(Serializable):完全にシリアル化されたリードは、読むたびにテーブルレベルの共有ロックを取得する必要があり、読み書きは互いに

をブロックする.
ウィキペディア:トランザクション・アイソレーション
テストテーブルusersを作成し、テストデータを挿入

mysql> CREATE TABLE users (id int(11) NOT NULL, name varchar(20), age int(11), PRIMARY KEY(id)) ENGINE=InnoDB;

mysql> INSERT INTO users values (1, 'Joe', 20), (2, 'Jill', 25);

mysql> select * from users;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | Joe  |   20 |
|  2 | Jill |   25 |
+----+------+------+

ダーティリード
例1:独立性レベルがコミットされていないリード(READ UNCOMMITTED)であり、ダーティリード(dirty read)を引き起こす.私たちの例では、トランザクション2はローを変更しましたが、コミットされず、トランザクション1はコミットされていないデータを読みました.今、トランザクション2がさっきの変更をロールバックしたり、別の変更をしたりすると、トランザクション1で調べたデータは正しくありません.この例では,トランザクション2がロールバックするとidが1であり,ageが21のデータ行である.

--            
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

             Session A                              Session B

           START TRANSACTION;                       START TRANSACTION;
time
|          /* Query 1 */
|          SELECT age FROM users WHERE id = 1;
|          /* will read 20 */
|                                                   /* Query 2 */
v                                                   UPDATE users SET age = 21 WHERE id = 1;
                                                    /* No commit here */

           /* Query 1 */
           SELECT age FROM users WHERE id = 1;
           /* will read 21 */
                                                    ROLLBACK; /* lock-based DIRTY READ */

繰り返し不可(Non-repeatable reads)
例2:独立性レベルはコミット済み(READ COMMITTED)の読み取りであり、重複しない読み取りを招く.この例では、トランザクション2のコミットに成功したため、idが1の行の変更が他のトランザクションに表示されます.しかし、トランザクション1は、このローから別の「age」の値を読みました.シリアル化可能(SERIALIZABLE)と繰り返し読み取り可能な独立性レベルでは、データベースは2回目のSELECTリクエスト時にトランザクション2の更新前の値を返す必要があります.コミットされた読み取りとコミットされていない読み取りでは、更新後の値が返されます.この現象は、繰り返して読むことはできません.

--           
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;

             Session A                              Session B

           START TRANSACTION;                       START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 1;
|          /* will read age=20 */
|                                                   /* Query 2 */
v                                                   UPDATE users SET age = 21 WHERE id = 1;
                                                    COMMIT; /* in multiversion concurrency 
                                                        control, or lock-based READ COMMITTED */

           /* Query 1 */
           SELECT * FROM users WHERE id = 1;
           /* will read age=21 */
           COMMIT; /* lock-based REPEATABLE READ */

重複しない読み取りを避けるには、2つのポリシーがあります.1つは、トランザクション2をトランザクション1のコミットまたはロールバックに遅延させてから実行することです.この方式はT 1,T 2のシリアル化スケジューリングを実現した.シリアルスケジューリングは、繰り返し読み取りをサポートします.
もう1つのポリシーは、マルチバージョン同時制御です.トランザクション2を先にコミットできるように、同時パフォーマンスを向上させる.ただし、トランザクション1は、トランザクション2の前に開始されるため、トランザクション1が実行を開始する時点のデータベースのスナップショット上で操作する必要があります.トランザクション1が最終的にコミットされると、データベースはその結果がT 1,T 2シリアルスケジューリングに等価であるかどうかをチェックします.等価の場合、トランザクション1のコミットが許可されます.等価でない場合、トランザクション1はロールバックし、シリアル化に失敗したエラーを投げ出す必要があります.
ロックベースの同時制御を使用すると、繰り返し読み取り可能な独立性レベルではID=1のローがロックされ、トランザクション1のコミットまたはロールバック前に文2の実行がブロックされます.コミットリードのレベルでは、文1が2回目に実行され、ageが変更されました.
マルチバージョン同時制御メカニズムでは、シーケンス化(SERIALIZABLE)レベルが可能であり、2回のSELECT文で読み出されたデータはいずれもトランザクション1の開始スナップショットであるため、同じデータが返される.ただし、トランザクション1がUPDATE行のデータをしようとすると、トランザクション1はロールバックされ、シリアル化に失敗したエラーが投げ出されます.
コミットリード独立性レベルでは、各文は文の実行前のスナップショットを読み取るため、更新前後の異なる値を読み取る.このレベルではシリアル化エラーは発生しません(シリアル化は必要ありません).トランザクション1も再試行は必要ありません.
ファントムリード
以下の内容は参考にしました:mysql幻読の詳細、例と解決方法
幻読エラーの理解:幻読はトランザクションAが2回のselect操作を実行して異なるデータセットを得る、すなわちselect 1が10個の記録を得、select 2が11個の記録を得るという.これは実際には幻読ではありません.これは再読できないもので、R-U R-Cレベルでしか現れませんが、mysqlのデフォルトのRR分離レベルでは現れません.
幻読は、2回の読み取りで得られた結果セットが異なるというわけではなく、あるselect操作で得られた結果によって特徴付けられたデータ状態が後続のトラフィック操作をサポートできない点に重点を置いている.より具体的には、selectのあるレコードが存在するかどうか、存在しないかどうか、このレコードを挿入する準備をしていますが、insertを実行するとこのレコードが存在していることに気づき、挿入できません.このとき、幻読が発生します.

--            
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 3;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (3, 'Woody', 28);
                                                        COMMIT;

           /* Query 3 */
           INSERT INTO users values (3, 'Woody', 28);
           /* ERROR 1062 (23000): Duplicate entry '3' 
              for key 'PRIMARY' */
           
           /* Query 4 */
           SELECT * FROM users WHERE id = 3;
           /* Empty set */
           COMMIT;

セッションA:プライマリトランザクションは、テーブルにidが3のレコードがあるかどうかを検出し、ない場合は挿入します.これは、通常のビジネスロジックです.
セッションB:セッションAの正常なトランザクション実行を乱すことを目的として、トランザクションを妨害する.
RR独立性レベルでは、Query 1、Query 2は正常に実行され、Query 3はプライマリ・キーの競合を誤って報告し、セッションAの業務では実行に失敗します.ここで、セッションAは幻読みが発生します.なぜなら、セッションAがQuery 1で読み取ったデータの状態は後続の業務操作をサポートできないためです.会话A:「鬼に会った.さっき読んだ结果、こんな操作をサポートしてくれるはずなのに、どうして今はできないの?」セッションAは信じられないほどQuery 4を実行しており,Query 1が読み取った結果と同じであることが分かった(RR下のMMVCメカニズム).このとき、既に幻読みは発生しているに違いない.T 1は何度読み取ってもid=3の記録は調べられないが、彼が読み取って存在しないと認定した記録(このデータはセッションBに挿入されている)を挿入することはできず、セッションAにとっては幻読みとなっている.
実はRRも幻読を避けることができて、selectに対して手動でXロック(SELECT...FOR UPDATEこれもSERIALIZABLEの隔離レベルの下で暗黙的にあなたのためにすることができます)を加えることを操作することによって、同時にまた現在の記録が存在しなくても、例えばid=3は存在しないことを知る必要があります.現在のトランザクションでは、インデックスがロックされているため、レコードエンティティが存在するかどうかは関係なく、存在しない場合はnext-key lockギャップXロックを追加するレコードロックも取得されます.他のトランザクションでは、インデックスのレコードを挿入できないため、幻読みは抑制されます.
SERIALIZABLE独立性レベルでは、step 1が実行されると暗黙的に行(X)ロック/gap(X)ロックが追加され、Query 2がブロックされ、Query 3が正常に実行され、T 1がコミットされた後、T 2が実行され続ける(プライマリ・キー競合実行に失敗)ことができ、T 1にとって業務は正しく、成功したブロックは業務を乱すT 2を殺し、T 1にとって彼が前期に読み取った結果はその後続業務を支えることができる.
したがってmysqlの幻読は,2回の読み出し結果セットが異なるのではなく,トランザクションが事前に検出されていないレコードを挿入すると,これらのデータがすでに存在していることに驚き,以前の検出で得られたデータは幽霊のようになっていることに気づいた.
ここでは読み取りの意味を柔軟に理解し、1回目のselectは読み取り、2回目のinsertも実は暗黙的な読み取りに属し、mysqlのメカニズムで読み取ったにすぎず、挿入データもプライマリキーの衝突があるかどうかを先に読み取ってから挿入を実行するかどうかを決定する.
繰り返して読むことはできませんが、読む-読むことに重点を置いて、幻読は読む-書くことを言って、書くことで読むのが鬼影であることを証明します.
繰り返し可能レベルでの幻読み防止
RRレベルでは、SELECT操作に対しても手動で(X)ロックをかけるだけで、SERIALIZABLEレベル(SELECTに対して暗黙的にロックをかける)に似ています.

#       X ，   FOR SHARE    S             
SELECT `id` FROM `users` WHERE `id` = 3 FOR UPDATE;

id=3のレコードが存在する場合は行(X)ロックされ、存在しない場合はnext-lock key/gapロック(範囲行ロック)が加算されます.すなわち、レコードが存在するかどうかはmysqlがレコードに対応するインデックスにロックされ、他のトランザクションは操作できません.
ここではid=3のレコードが存在しないシーンを示し,FOR UPDATEもこの「レコード」にロックをかけるが,InnoDBの行ロック(gapロックは範囲行ロックであり,同じ)はレコードに対応するインデックスをロックし,クラスタインデックスはレコードと直接関係していることを理解する.

--            
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          SELECT * FROM users WHERE id = 3 FOR UPDATE;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (3, 'Woody', 28);
                                                        /*    ，ERROR 1205 (HY000): Lock wait timeout exceeded; 
                                                           try restarting transaction */

           /* Query 3 */
           INSERT INTO users values (3, 'Woody', 28);
           /* Query OK, 1 row affected */
           
           COMMIT;
           /* Query OK, 0 rows affected */

シリアルレベルでの幻の読み取りを抑制
このレベルでは、SELECT操作を明示的にロックする必要はありません.InnoDBは自動的にロックされ、トランザクションは安全ですが、パフォーマンスは低いです.

--            
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

             Session A                                  Session B

           START TRANSACTION;                           START TRANSACTION;
time
|          /* Query 1 */
|          select * from users where id = 4;
|          /* Empty set */
|                                                       /* Query 2 */
v                                                       INSERT INTO users values (4, 'Bill', 29);
                                                        /*    ，ERROR 1205 (HY000): Lock wait 
                                                           timeout exceeded; try restarting transaction */

           /* Query 3 */
           INSERT INTO users values (4, 'Bill', 29);
           /* Query OK, 1 row affected */
           
           COMMIT;
           /* Query OK, 0 rows affected */

Step 1:セッションAクエリーid=4のレコード、InnoDBは暗黙的に整列してXロックstep 2:セッションB挿入id=4のレコード、ブロックstep 3:セッションA挿入id=4のレコード、正常に実行(セッションBは依然としてブロック中)step 4:セッションAの正常なコミット(セッションBはこの時点で起動したがプライマリ・キー・バースト実行エラー)セッションAトランザクションはビジネスニーズに合致して正常に実行され、セッションBはセッションAを干渉して失敗した.
まとめ
RRレベルはmysqlトランザクションのデフォルトの独立性レベルとして、トランザクションのセキュリティとパフォーマンスの折衷であり、二八の法則(20%のトランザクションは幻読の可能性があり、80%のトランザクションは幻読のリスクがない)にも合致する可能性があります.私たちは幻読を正しく認識した後、シーンに応じて柔軟に幻読の発生を防ぐことができます.
SERIALIZABLEレベルは、幻読時刻が発生すると悲観的に考えられているため、自動的に暗黙的に事務所に必要なリソースにロックをかけ、他のトランザクションがこのリソースにアクセスするとブロックされて待機するため、トランザクションは安全ですが、パフォーマンスを真剣に考慮する必要があります.
InnoDBのロー・ロックは、レコードそのものではなくインデックスでロックされていることも明確に認識する必要があります.そのため、あるインデックスと同じレコードがロックされ、インデックス競争を引き起こします.これは、ビジネスsqlを厳格に設計し、できるだけプライマリ・キーまたは一意のインデックスを使用してレコードにロックをかける必要があります.インデックスマッピングのレコードが存在する場合、ロー・ロックが存在し、存在しない場合、next-key lock/gapロック/ギャップ・ロックが追加されるため、InnoDBはトランザクションのレコードに対する事前占有を実現することができ、レコードが存在する場合、それは本トランザクションであり、レコードが存在しない場合、それは本が存在しないので、本が存在しない限り、他のトランザクションはそれを占有したくない.
あとはMySQLの「幻読み」が分からないなんて言わないで