mysqlのファントムリーディングを解決する方法

トランザクション分離レベル (tx_isolation)

Mysql には 4 つのレベルのトランザクション分離があり、各レベルには文字または数値が付いています

次のコマンドを使用して、グローバル/セッションのトランザクション分離レベルを表示/設定できます

mysql> SELECT @@global.tx_isolation, @@tx_isolation;
+-----------------------+------------------+
| @@global.tx_isolation | @@tx_isolation   |
+-----------------------+------------------+
| REPEATABLE-READ       | READ-UNCOMMITTED |
+-----------------------+------------------+
1 row in set (0.00 sec)

# 设定全局的隔离级别 设定会话 global 替换为 session 即可 把set语法温习一下
# SET [GLOABL] config_name = 'foobar';
# SET @@[session.|global.]config_name = 'foobar';
# SELECT @@[global.]config_name;

SET @@gloabl.tx_isolation = 0;
SET @@gloabl.tx_isolation = 'READ-UNCOMMITTED';

SET @@gloabl.tx_isolation = 1;
SET @@gloabl.tx_isolation = 'READ-COMMITTED';

SET @@gloabl.tx_isolation = 2;
SET @@gloabl.tx_isolation = 'REPEATABLE-READ';

SET @@gloabl.tx_isolation = 3;
SET @@gloabl.tx_isolation = 'SERIALIZABLE';

ファントム リーディング

まず、ファントム リーディングとは何かを理解する必要があります。現在、インターネット上ではファントム リーディングについての説明が数多く出回っていますが、このブログ記事では、よく考えてみれば、それを覆す例が見つかると個人的に感じています。ブログ記事のように、ノンブロッキング IO と非同期 IO を同一視し、その後、多くの記事を拝借しました。実際、この 2 つはまったく異なります。ノンブロッキング IO は同期 IO の一部であり、このモードは非同期 IO ではありません。世間の誤解は「修正」されたので、本題に戻りましょう。

ファントム リードは RU / RC / RR レベルで表示されます。SERIALIZABLE によりファントム リードが排除されます。ただし、ダーティ リードとノンリピータブル リードは RU / RC の下に依然として存在するため、RR レベルでファントムを調査します。他の干渉を読み取って排除します。

注: RR レベルでファントム読み取りが発生する可能性がありますが、レコードに X ロックを手動で追加する方法を使用してファントム読み取りを排除することもできます。 SERIALIZABLE は、すべてのトランザクションに X ロックを追加することでファントム読み取りを防止しますが、多くのシナリオでは、ビジネス SQL にはファントム読み取りのリスクがありません。 SERIALIZABLE を使用すると、トランザクションの絶対的なセキュリティを確保できますが、パフォーマンスに多くの不必要な損失が発生します。したがって、RRではビジネスニーズに応じてロックするかどうかを決定することができ、ファントムリードの危険性がある場合はロックし、存在しない場合はロックしないことで、トランザクションのセキュリティとパフォーマンスを両立します。これは、mysql のデフォルトの分離レベルである RR がトランザクション分離レベルである理由でもあり、ファントム読み取りについての正しい理解が必要です。

ファントム読み取りエラーの理解: ファントム読み取りは、トランザクション A が異なるデータ セットを取得するために 2 つの選択操作を実行する場合、つまり、選択 1 で 10 レコードを取得し、選択 2 で 11 レコードを取得する場合と言われています。これは実際にはファントム読み取りではなく、R-U R-C レベルでのみ発生する非反復読み取りの一種であり、mysql のデフォルトの RR 分離レベルでは発生しません。

ファントム リーディングについての私の口語的な理解は次のとおりです:

ファントム リーディングは、2 つの読み取りによって得られた結果セットが異なるという意味ではありません。ファントム リーディングの焦点は特定の時間にあります。データ選択操作の結果によって表されるステータスは、その後のビジネス操作をサポートできません。具体的には、あるレコードが存在するかどうかを選択し、存在しない場合は、レコードを挿入する準備をしますが、挿入を実行すると、すでにレコードが存在していることがわかり、挿入できません。このとき、ファントムリードが発生します。が発生します。

これは、mysql ファントム読み取りのより鮮明なシナリオです (Zhihu での私の回答を借用):

table users: id primary key

トランザクション T1

# #トランザクション T2

#step1 T1: SELECT * FROM \`users\` WHERE \`id\` = 1;
step2 T2: INSERT INTO \`users \` VALUES (1, 'big cat');
step3 T1: \`users\` VALUES (1, 'big cat');
step4 T1: SELECT * FROM \`users \` WHERE \`id\` = 1;

T1: メイン トランザクション。テーブル内に ID 1 のレコードがあるかどうかを検出し、ない場合はそれを挿入します。これは、予期される通常のビジネス ロジックです。

T2: 干渉トランザクション。目的は、T1 の通常のトランザクション実行を妨害することです。

RR 分離レベルでは、ステップ 1 とステップ 2 は正常に実行されますが、ステップ 3 は主キーの競合エラーを報告します。T1 のビジネスの場合、実行は失敗します。ここで T1 が発生しました ファントム読み取り、ステップ 1 で T1 によって読み取られたデータ ステータスでは、その後のビジネス オペレーションをサポートできないため、T1: 「くそー、今読み取った結果はこのようなオペレーションをサポートできるはずなのに、なぜ今それができないのですか?」 T1 はそれを信じられず、ステップ 4 を再度実行したところ、setp1 によって読み取られた結果が同じであることがわかりました (RR での MMVC メカニズム)。このときファントムリードが発生しているのは間違いなく、T1が何度読み込んでもid=1のレコードは見つからないが、読み込みによって存在しないと判断したこのレコードを挿入することはできない（このデータは挿入されている）。 T2 )、T1 の場合、ファントムと読み取られます。

実際、RR は行を手動で追加することでファントム読み取りを回避することもできます。また、現在のレコードが存在しない場合 (たとえば id=1 が存在しない場合)、現在のトランザクションはレコード ロック (InnoDB の行ロックはインデックスをロックするため、レコード エンティティが存在するかどうかは関係ありません。存在する場合は行 X ロックを追加し、存在しない場合はネクスト キー ロック ギャップを追加します)

SERIALIZABLE 分離レベルでは、ステップ 1 の実行時に行 (X) ロック/ギャップ (X) ロックが暗黙的に追加されるため、ステップ 2 はブロックされ、ステップ 3 は通常どおり実行されます。T1 が送信された後、T2 は次のことができます。実行を継続します (主キー競合の実行は失敗します)。T1 の場合、ビジネスは正しいです。ブロックに成功すると、ビジネスを中断した T2 が強制終了されます。T1 の場合、初期の読み取り結果はその後のビジネスをサポートできます。

所以 mysql 的幻读并非什么读取两次返回结果集不同，而是事务在插入事先检测不存在的记录时，惊奇的发现这些数据已经存在了，之前的检测读获取到的数据如同鬼影一般。

这里要灵活的理解读取的意思，第一次select是读取，第二次的 insert 其实也属于隐式的读取，只不过是在 mysql 的机制中读取的，插入数据也是要先读取一下有没有主键冲突才能决定是否执行插入。

不可重复读侧重表达 读-读，幻读则是说 读-写，用写来证实读的是鬼影。

RR级别下防止幻读

RR级别下只要对 SELECT 操作也手动加行（X）锁即可类似 SERIALIZABLE 级别（它会对 SELECT 隐式加锁），即大家熟知的：

# 这里需要用 X锁， 用 LOCK IN SHARE MODE 拿到 S锁 后我们没办法做 写操作
SELECT `id` FROM `users` WHERE `id` = 1 FOR UPDATE;

如果 id = 1 的记录存在则会被加行（X）锁，如果不存在，则会加 next-lock key / gap 锁（范围行锁），即记录存在与否，mysql 都会对记录应该对应的索引加锁，其他事务是无法再获得做操作的。

这里我们就展示下 id = 1 的记录不存在的场景，FOR UPDATE 也会对此 “记录” 加锁，要明白，InnoDB 的行锁（gap锁是范围行锁，一样的）锁定的是记录所对应的索引，且聚簇索引同记录是直接关系在一起的。

id = 1 的记录不存在，开始执行事务：
step1: T1 查询 id = 1 的记录并对其加 X锁
step2: T2 插入 id = 1 的记录，被阻塞
step3: T1 插入 id = 1 的记录，成功执行（T2 依然被阻塞中），T1 提交（T2 唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

SERIALIZABLE级别杜绝幻读

在这个层面上，我们不必对 SELECT 操作进行显式加锁，因为InnoDB会自动加锁以确保事务的安全性，但是这会导致性能较低

step1: T1 查询 id = 2 的记录，InnoDB 会隐式的对齐加 X锁
step2: T2 插入 id = 2 的记录，被阻塞
step3: T1 插入 id = 2 的记录，成功执行（T2 依然被阻塞中）
step4: T1 成功提交（T2 此时唤醒但主键冲突执行错误）

T1事务符合业务需求成功执行，T2干扰T1失败。

以上がmysqlのファントムリーディングを解決する方法の詳細内容です。詳細については、PHP 中国語 Web サイトの他の関連記事を参照してください。