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

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 エンジンの利点も失われます。実際の応用例は少ない。

SELECT * FROM t_bitfly;
空のセット

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

| ------+------+
| 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)

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

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：

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、 および

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

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

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

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

それ自体、反復可能な読書とコミットされた読書は矛盾しています。同じトランザクション内で、反復可能な読み取りが保証されている場合、他のトランザクションのコミットは表示されないため、コミットされた読み取りに違反します。また、コミットされた読み取りが保証されている場合、前の 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 トランザクションのデフォルトの分離レベルは反復読み取りであり、ファントム読み取りを確実に回避するには、アプリケーションでロックされた読み取りを使用する必要があります。このロック度に使用されるメカニズムはネクストキーロックです。