X(Twitter) Zenn GitHub RSS 共有

MySQLにおけるファントムリード

作成日時：2025-01-26
更新日時：2025-11-01

※あくまで自分の解釈。正しいとは限らない。

なぜREPEATABLE READでファントムリードが発生しないか

MVCCとギャップロックのおかげ。

パターン1:

1. トランザクションA：SELECT * FROM tbl;
2. トランザクションB：行挿入してコミット。
3. トランザクションA：再度SELECT * FROM tbl;
   2で挿入したデータは見えない

ファントムリードは発生しない。
3のSELECTは1の時のスナップショットを参照しているから。

パターン2:

1. トランザクションA：SELECT * FROM tbl WHERE (インデックスを使用した範囲検索) FOR UPDATE;
2. トランザクションB：行挿入はギャップロックで失敗

登録できないのでファントムリードは発生しない。

FOR UPDATE

15.7.2.4 読取りのロックに下記の記載がある。

一貫性読み取りでは、読み取られたビュー内に存在するレコードに設定されたロックはすべて無視されます。 (古いバージョンのレコードはロックできません。レコードのインメモリーコピー上の Undo ログに適用することで、再構築されます。)

以下、推察。

非ユニークインデックスのロック

WHERE id = xxx FOR UPDATEにおいて、
idがユニークインデックスなら行ロック。
idが非ユニークインデックスなら同じキー値を持つレコードが複数存在する可能性があるため、ギャップロックorネクストキーロック。

非インデックスをキーにUPDATEすると、実質テーブルロックになる可能性がある。

検索にせよ更新にせよ、何らかの条件にしている項目は、パフォーマンスが許す限りインデックスを貼るべきか。
検索パフォーマンスの向上や、ロック範囲の縮小につながる。

比較

差し込み

-- A: トランザクション開始
BEGIN TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SELECT * FROM table WHERE id BETWEEN 1 AND 10;
-- 結果: 5は存在しない

-- B: (別のセッションで)
BEGIN;
INSERT INTO table (id, ...) VALUES (5, ...);
COMMIT;

-- A: 更新を実行
UPDATE table SET column = value WHERE id BETWEEN 1 AND 10;
--  REPEATABLE READにおいて5は更新されない

メモ

PostgreSQLの範囲ロックはMySQLと違って、実際にレコードが無くともその範囲でロックされる。

なんだか、PostgreSQLは論理、MySQLは物理で管理している感じがする。
ロックの考え方や、FOR UPDATE、クラスター/非クラスターなど。追記型とか。

参考