MySQL InnoDB の 4 つのトランザクション レベルとダーティ リード、ノンリピート リード、ファントム リードとは何ですか?

1. MySQL InnoDB トランザクション分離レベル ダーティ リード、リピータブル リード、ファントム リード

MySQL InnoDB トランザクション分離レベルには 4 つのレベルがあり、デフォルトは「REPEATABLE READ」です。

· 1) コミットされていない読み取り (READUNCOMMITTED)。別のトランザクションはデータを変更しましたが、まだコミットしていません。このトランザクションの SELECT は、これらのコミットされていないデータを読み取ります (ダーティ リード)( 最も低い分離レベル、高い同時実行パフォーマンス)。

· 2) 読み取り値を送信します (READCOMMITTED)。このトランザクションが読み取るのは、(他のトランザクションがコミットされた後の) 最新のデータです。問題は、同じトランザクション内で、同じ SELECT が (繰り返し読み取られることなく) 異なる結果を 2 回読み取ることです。反復不可能な読み取りとファントム読み取りの問題 (読み取り中の行のロック) が発生します

· 3)。同一トランザクション内では、SELECT の結果はトランザクション開始時の状態となるため、同一の SELECT 操作で読み取った結果は一致します。ただし、ファントムリーディング（後述）が発生します。ファントム読み取りが発生します (読み取られたすべての行はロックされます)。

· 4).シリアル化 (SERIALIZABLE)。読み取り操作は暗黙的に共有ロックを取得するため、異なるトランザクション間の相互排他 (ロック テーブル) が保証されます。

‘

4 つのレベルが徐々に強くなり、各レベルで問題が解決されます。

· 1) 汚い読書。別のトランザクションがデータを変更しましたが、まだコミットされていないため、このトランザクションの SELECT はコミットされていないデータを読み取ります。

· 2) 繰り返し読まないでください。ダーティ リードを解決した後、同じトランザクションの実行中に、別のトランザクションが新しいデータを送信したため、このトランザクションによって 2 回読み取られたデータの結果が不整合になることがわかります。

· 3）ファントムリーディング。これにより、非反復読み取りの問題が解決され、同じトランザクション内で query の結果がトランザクション開始時の状態 (一貫性) になることが保証されます。ただし、別のトランザクションが同時に新しいデータを送信し、このトランザクションが再び更新されると、これらの新しいデータを発見して「驚き」、以前に読み取られたデータが「幽霊」であるように見えます。

具体的には:

1). ダーティ リード

まずダーティ ページとダーティ データを区別します

ダーティ ページはメモリ バッファです プール内で変更されたページは、まだ変更されていません更新はハードディスクにフラッシュされましたが、REDO ログに書き込まれました。バッファ プール内のページの読み取りと変更は正常であり、フラッシュにより効率が向上します。ダーティ データは、トランザクションがバッファ プール内の行レコードを変更したが、まだ送信していないことを意味します。 ！ ！ 、このときにバッファープール内のコミットされていない行データが読み取られる場合、それはダーティリードと呼ばれ、トランザクションの分離に違反します。ダーティ リードとは、トランザクションがデータにアクセスし、データを変更したが、その変更がまだデータベースに送信されていないときに、別のトランザクションもデータにアクセスし、そのデータを使用することを意味します。

2). 反復不可能な読み取り

は、トランザクション内で同じデータを複数回読み取ることを指します。このトランザクションが終了する前に、別のトランザクションも同じデータにアクセスします。次に、最初のトランザクションの 2 回のデータ読み取りの間に、2 番目のトランザクションの変更により 2 番目のトランザクションがコミットされます。その場合、最初のトランザクションで 2 回読み取られたデータは異なる可能性があります。このように、トランザクション内で 2 回読み取られるデータは異なるため、Non-Repeatable Read と呼ばれます。たとえば、編集者は同じ文書を 2 回読みますが、その合間に作成者が文書を書き直します。編集者が文書をもう一度読むと、文書は変更されています。生の読み取りは再現できません。この問題は、作成者がすべての執筆を終えた後にのみ編集者が文書を読み取ることができるようにすることで回避できます

3) ファントム読み取り:

は、最初のトランザクションが独立して実行されない場合に発生する現象を指します。トランザクションはテーブル内のデータを変更します。この変更にはテーブル内のすべてのデータ行が含まれます。同時に、2 番目のトランザクションもこのテーブルのデータを変更します。この変更により、テーブルに新しいデータの行が挿入されます。その後、最初のトランザクションを操作するユーザーは、あたかも幻覚が起こったかのように、テーブル内にまだ変更されていないデータ行が存在することに気づくでしょう。たとえば、編集者が作成者によって送信されたドキュメントを変更しますが、プロダクションがその変更をドキュメントのマスター コピーにマージすると、作成者が新しい未編集の内容をドキュメントに追加したことがわかります。この問題は、編集者と制作部門が元のドキュメントの作業を完了するまで、誰もドキュメントに新しい内容を追加できないようにすれば回避できます。

2. 分離レベルの実験

次の実験は、ブロガーの MySQL Server 5.6 に基づいています

まず次のようにテーブルを作成します:

USE test;  
CREATE TABLE `t` (  
  
  `a` int(11) NOT NULL PRIMARY KEY  
  
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

2.1: ダーティリードとリピータブルリードの問題を説明します

事務B READ-COMMITTED,

トランザクションC-1 REPEATABLE-READアクションを開始select * from t;コミット;

	トランザクションC-2 REPEATABLE-READ	トランザクション D SERIALIZABLE	set autocommit =0;
	トランス	;
トランザクションを開始; t(a)values(4)に挿入;
t;	1から*を選択してください,2,3,4 (ダーティ リード: コミットされていないトランザクションでのデータの読み取り)		select * from t;	1,2,3 (ダーティ リードの解決)
1, 2,3	select * from t; 1,2,3	select * from t; 1,2,3
t:	1,2から*を選択してください、 3,4	select * from t: 1,2,3,4	select * from t: 1,2,3,4 (上記と同じトランザクションではないため、次のように読み取られます)トランザクションコミット 最新のものなので読み込めます 4)	select * from t: 1,2,3 (繰り返し読み込み:上記と同じトランザクション内なので、トランザクションのデータのみ開始トランザクションが読み取られ、読み取りを繰り返すだけです)	select * from t: 1,2,3,4
				commit (トランザクションをコミット、以下は新しいトランザクションなので、トランザクションの送信後に最新のデータを読み取ることができます)
				select * from t: 1,2,3,4
READ- UNCOMMITTED はダーティ リードを生成し、実際のシナリオに適用されることはほとんどないため、基本的には使用されません。

2.2. 実験 2: READ-COMMITTED と REPEATABLE-READ のテスト

選択* from t:1,2,3,4 select * from t:1 , 2, 3 (繰り返し読み出し：上記と同じトランザクション内なので、トランザクションのデータのみが開始)トランザクションが読み取られます、つまり、繰り返し読み取られます) commit (トランザクションをコミットします。以下は新しいトランザクションなので、トランザクションが送信された後に最新のデータを読み取ることができます)READ-COMMITTED は、最新のトランザクションによってコミットされたデータが確実に読み取られることを保証するだけです。

トランザクション A

トランザクション B READ-COMMITTED

トランザクション C REPEATABLE-READ

セットautocommit =0;

トランザクションを開始;

トランザクションを開始;

トランザクションを開始;

t(a)値に挿入(4);

tから*を選択してください。

コミット;

select * from t:

1,2,3,4

REPEATABLE-READ can トランザクションで読み取られたデータが反復可能であること、つまり、同じ読み取りであることを確認します (最初に読み取られると、他のトランザクションが新しいデータを送信した場合でも、同じトランザクションで再度読み取られることはありません。

当然数据的可见性都是对不同事务来说的，同一个事务，都是可以读到此事务中最新数据的。如下，

1. start transaction;  
   insert into t(a)values(4);  
   select *from t;    
   1,2,3,4;  
   insert into t(a)values(5);  
   select *from t;  
   1,2,3,4,5;

2.3、实验三：测试SERIALIZABLE事务对其他的影响

事务A SERIALIZABLE	事务B READ-UNCOMMITTED	事务C READ-COMMITTED,	事务D REPEATABLE-READ	事务E SERIALIZABLE
set autocommit =0;
start transaction ;			start transaction;
select a from t union all select sleep(1000) from dual;
	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);	insert into t(a)values(5);
	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction	ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
	SERIALIZABLE 串行化执行，导致所有其他事务不得不等待事务A结束才行可以执行，这里特意使用了sleep函数，直接导致事务B,C,D,E等待事务A持有释放的锁。由于我sleep了1000秒，而innodb_lock_wait_timeout为120s。所以120s到了就报错HY000错误。
SERIALIZABLE は、読み取りでも書き込みでも、同じテーブルを読み取る他のトランザクションに影響を与える非常に厳密なシリアル化実行モードです。これは、厳密なテーブルレベルの読み取り/書き込み排他ロックです。また、innodb エンジンの利点も失われます。実際の応用例は少ない。

2.4. 実験 4: ファントム リード

一部の記事では、InnoDB の反復読み取りが「ファントム リード」(ファントム リード) を回避すると書いていますが、これは正確ではありません。実験を実行します: (以下の実験はすべて、ストレージ エンジンと分離レベルに注意する必要があります)

1. CREATE TABLE `t_bitfly` (

2. `id` ) bigint ( 20) NOTNULL デフォルト '0'` value` varchar

   (32)
3. default

   NULL 、プライマリ

4. KEY

   (`id`) ENGINE=InnoDB DEFAULTCHARSET=utf8;

5. select @@global.tx_isolation, @@tx_isolation;  

6. +----------------------+----------------- +

7. | @@global.tx_isolation | @@tx_isolation |  

8. +----------------------+----------------- +

9. | 繰り返し可能-読む | 繰り返し可能-読む |  

10. +----------------------+----------------- +

实验4-1：

セッション A	セッション B
トランザクションを開始 ;	トランザクションを開始 ;
SELECT * FROM t_bitfly; 空のセット
	INSERT INTO t_bitfly VALUES (1, 'a');COMMIT;
SELECT * FROM ; |空のセット
INSERT INTO t_bitfly VALUES (1, 'a'); |エラー 1062 (23000): |キー 1 のエントリ '1' が重複しています (そのようなレコードは存在しないとはっきり言いました)	私

このように、ファントム読み取りが発生し、テーブルにデータがないと思われますが、実際にはデータがすでに存在しており、送信後にデータの競合が発見されます。

実験 4-2:

SELECT * t_bitfly;| +------+------+UPDATE t_bitfly SET value='z';| SELECT * FROM t_bitfly;| +------+------+ | |

セッション A

セッション B

トランザクションを開始 ;

トランザクションを開始 ;

SELECT * FROM t_bitfly;

| +------+------+
| |a |
| +------+-------+

INSERT INTO t_bitfly VALUES (2, 'b');

| 1 |

| ------+------+
| 1 |a |
| + ------+------+

(どうしてさらに 1 行あるのですか)

| +------ -+------+

| 1 |z |
|

このトランザクションで初めて行が読み取られ、更新が行われた後、別のトランザクションで送信されたデータが表示されます。一種の幻読ともいえる。

解説付き

では、InnoDBがファントムリードを回避できると指摘した理由は何でしょうか？

http://dev.mysql.com/doc/refman/5.0/en/innodb-record-level-locks.html

デフォルトでは、InnoDB は REPEATABLE READ トランザクション分離レベルで動作し、innodb_locks_unsafe_for_binlog システム変数は無効になっています。この場合、InnoDB は検索とインデックス スキャンにネクスト キー ロックを使用し、ファントム行を防ぎます (セクション 13.6.8.5 を参照)。 「Next-Key Locking を使用したファントム問題の回避」) 準備された理解は、分離レベルが反復読み取りで、innodb_locks_unsafe_for_binlog が無効になっている場合、

検索

およびインデックスのスキャン中の next-keylock の使用を回避できるということです。重要な点は、InnoDB はデフォルトで通常のクエリにネクストキー ロックも追加するのか、それともアプリケーション自体がロックを追加する必要があるのか​​ということです。この文だけを読むと、InnoDB は通常のクエリにもロックを追加するのではないかと思うかもしれません。そうであれば、シリアル化 (SERIALIZABLE) との違いは何でしょうか。 MySQL マニュアルには別の段落があります:

13.2.8.5. Next-Key Locking を使用したファントム問題の回避 (http://dev.mysql.com/doc/refman/5.0/en/innodb-next-key-)ロック。html)

ファントムを防止するには、

ネクストキーと呼ばれるアルゴリズムを使用します インデックス行ロックとギャップ ロックを組み合わせたロック

ネクストキー ロックを使用して、アプリケーションに一意性チェックを実装できます。共有モードでデータを読み取り、行の重複が見つからない場合は、 insert を実行すると、行を安全に挿入でき、次のキーのロックが行に設定されていることがわかります。 したがって、ネクストキー ロックを使用すると、テーブル内の何かが存在しないことを「ロック」できます。

InnoDB私の理解では、InnoDB は Next- を提供します。キーロックは利用可能ですが、アプリケーション自体がロックする必要があります。マニュアルには例が記載されています:

SELECT * FROM child WHERE id> 100 FOR UPDATE;

这样，InnoDB会给id大于100的行（假如child表里有一行id为102），以及100-102，102+的gap都加上锁。

可以使用show engine innodb status来查看是否给表加上了锁。

再看一个实验，要注意，表t_bitfly里的id为主键字段。

实验4-3：

Session A	Session B
start transaction ;	start transaction ;
SELECT * FROM t_bitfly  WHERE id<=1  FOR UPDATE; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly   VALUES (2, 'b'); | Query OK, 1 row affected
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	INSERT INTO t_bitfly VALUES (0, '0'); |  (waiting for lock ... |   then timeout) ERROR 1205 (HY000):Lock wait timeout exceeded; |try restarting transaction
SELECT * FROM t_bitfly; | +------+-------+ | | id   | value | | +------+-------+ | |    1 |a     | | +------+-------+
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | ID |値 | | +------+------+ | |    1 |a | | +------+-------+

id

添付メモ:

MySQL マニュアルの反復読み取りにおけるロックの詳細な説明:

http://dev.mysql.com/doc/refman/5.0/en/set-transaction.html#isolevel_repeatable-read

読み取りをロックする場合 (SELECT と FOR) 更新 または LOCK IN SHARE MODE),UPDATE共有モードでロックDELETE)、InnoDB更新InnoDB、 および

DELETE

ステートメントの場合、ロックはステートメントが 一意の検索条件を持つ一意のインデックス、または範囲型の検索条件を持つ一意のインデックスの場合、

InnoDB

はロックします。 他の検索条件の場合、

InnoDB

はロックします。インデックス レコードのみが見つかります。 スキャンされたインデックス範囲。ギャップ ロックまたはネクスト キー (ギャップとインデックス レコード) ロックを使用して、その範囲でカバーされるギャップへの他のセッションによる挿入をブロックします。

一貫性読み取りとコミット読み取り。まず実験を見てください。実験 4-4: セッション A

セッション B	🎜トランザクションを開始 ;🎜🎜🎜🎜トランザクションを開始 ;🎜
SELECT * FROM t_bitfly; | +------+------+ | | ID |値 | | +------+------+ | |    1 |a | | +------+-------+
	INSERT INTO t_bitfly VALUES (2, 'b');
	COMMIT;
SELECT * FROM t_bitfly; | +------+-------+ | | ID |値 | | +------+-------+ | |    1 |a | | +------+-------+
SELECT * t_bitfly ロックから共有モード; | +-----+----------+ | | ID |値 | | +-----+----------+ | |  1 |a | | |  2 |b | | +----+-------+
SELECT * 更新用に t_bitfly から; | +-----+----------+ | | ID |値 | | +-----+----------+ | |  1 |a | | |  2 |b | | +-----+------+
SELECT * FROM t_bitfly; | +-----+----------+ | | ID |値 | | +-----+----------+ | |  1 |a | | +-----+------+

追記: 通常の読み取りを使用すると、一貫した結果が得られます。ロックされた読み取りを使用すると、「最新」の「コミット」された読み取り結果が読み取られます。

それ自体、反復可能な読書とコミットされた読書は矛盾しています。同じトランザクション内で、反復可能な読み取りが保証されている場合、他のトランザクションのコミットは表示されないため、コミットされた読み取りに違反します。また、コミットされた読み取りが保証されている場合、前の 2 つの読み取りの結果に一貫性がなく、反復可能な読み取りに違反します。

InnoDB はそのようなメカニズムを提供していると言えます。デフォルトの反復読み取り分離レベルでは、ロックされた読み取りを使用して最新のデータをクエリできます。

http://dev.mysql.com/doc/refman/5.0/en/innodb-consistent-read.html

データベースの「最も新しい」状態を確認したい場合は、READ COMMITTED 分離のいずれかを使用する必要があります。レベルまたはロック読み取り:
SELECT * FROM t_bitfly LOCK IN SHARE MODE;

------

3. 概要

結論: MySQL InnoDB トランザクションのデフォルトの分離レベルは反復読み取りであり、ファントム読み取りを確実に回避するには、アプリケーションでロックされた読み取りを使用する必要があります。このロック度に使用されるメカニズムはネクストキーロックです。

以上がMySQL InnoDB の 4 つのトランザクション レベルとダーティ リード、ノンリピート リード、ファントム リードとは何ですか?の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。