トランザクションの同時実行制御、失敗させてみた

はじめに

本記事はリンクアンドモチベーション Advent Calendar 2023 の3日目になります。

これまではなんとなく聞いたことあるけど、小難しそうだし、必要なタイミングでちゃんと理解できたらいいや〜
くらいに思っていたので、アドベントカレンダーの機会を使って、実際に触りながら、まとめてみました。

※以降の内容は、普段業務で利用しているMySQL(InnoDB)を中心に記載しております

いきなりですが、まとめ

トランザクション分離レベルとは、複数のトランザクションが同時に実行される際の、互いへの影響度合いを決めている。
分離レベルが高ければ、データの一貫性が保たれるが、パフォーマンスを低下させる影響がある。

トランザクション分離レベルと、発生してしまう同時実行制御の問題の関係性は、以下です。
この関係性を実際に触って試してみたり、少し深ぼったりしてみました。

分離レベル	Dirty Read	Non-repeatable Read	Phantom Read
READ UNCOMMITTED	○	○	○
READ COMMITTED	x	○	○
REPEATABLE READ	x	x	○ (InnoDBでは x)
SERIALIZABLE	x	x	x

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

READ UNCOMMITTED

MySQL公式から引用

SELECT ステートメントは非ロックの方法で実行されますが、以前のバージョンの行が使用される可能性もあります。 そのため、この分離レベルを使用すると、このような読み取りには一貫性がありません。 これは、ダーティー読み取りとも呼ばれます。 そうでなければ、この分離レベルは READ COMMITTED のように機能します。

つまり、他のトランザクションがコミットしていないデータを読み取ることができてしまう。
Dirty Read、Non-Repeatable Read、Phantom Readが発生する。

READ COMMITTED

MySQL公式から引用

各読取り一貫性は、同じトランザクション内であっても、独自の新しいスナップショットを設定して読み取ります。 読取り一貫性の詳細は、セクション15.7.2.3「一貫性非ロック読み取り」 を参照してください。

ロック読取り (FOR UPDATE または FOR SHARE を使用した SELECT)、UPDATE ステートメントおよび DELETE ステートメントの場合、InnoDB はインデックスレコードのみをロックし、その前のギャップはロックしないため、ロックされたレコードの横に新しいレコードを自由に挿入できます。 ギャップロックは、外部キー制約チェックおよび重複キーチェックにのみ使用されます。

ギャップロックが無効になっているため、他のセッションがギャップに新しい行を挿入できるため、ファントムの問題が発生する可能性があります。 ファントムの詳細は、セクション15.7.4「ファントム行」 を参照してください。

つまり、既にコミットされたデータを読み取ることによって、Dirty Readを防いでいるが、
コミットされたデータであれば、読み取ることができてしまうため、Non-Repeatable Readが発生してしまう。
また、ギャップロックはされないため、Phantom Readが発生してしまう可能性がある。

REPEATABLE READ

MySQL公式から引用

これが InnoDB のデフォルトの分離レベルです。 同じトランザクション内の Consistent reads は、最初の読取りによって確立された snapshot を読み取ります。 つまり、同じトランザクション内で複数のプレーン (非ロック) SELECT ステートメントを発行すると、これらの SELECT ステートメントも互いに一貫性が保たれます。

つまり、同じトランザクション内では、最初に読み込んだデータを保持し続けるため、Non-Repeatable Readを防いでいる。

locking reads (FOR UPDATE または FOR SHARE のある SELECT)、UPDATE および DELETE ステートメントの場合、ロックはステートメントが一意の検索条件を持つ一意のインデックスを使用するか、範囲タイプの検索条件を使用するかによって異なります。

• 一意の検索条件を使用した一意のインデックスの場合、InnoDB は見つかったインデックスレコードのみをロックし、その前にあるギャップはロックしません。
• その他の検索条件の場合、InnoDB は、ギャップロックまたはネクストキーロックを使用して、範囲に含まれるギャップへのほかのセッションによる挿入をブロックすることによって、スキャンされたインデックス範囲をロックします。 ギャップロックおよびネクストキーロックについては、セクション15.7.1「InnoDB ロック」 を参照してください。

また、ギャップロックやネクストキーロックが使用されるため、Phantom Readを防いでいる。

SERIALIZABLE

MySQL公式から引用

このレベルは REPEATABLE READ と似ていますが、autocommit が無効になっている場合、InnoDB はすべてのプレーン SELECT ステートメントを SELECT ... FOR SHARE に暗黙的に変換します。 autocommit が有効な場合、SELECT は独自のトランザクションです。 したがって、読み取り専用であることがわかっているため、一貫性のある (非ロック) 読み取りとして実行された場合は直列化することができ、ほかのトランザクションのためのブロックは必要ありません。 (選択した行が他のトランザクションによって変更された場合にプレーン SELECT を強制的にブロックするには、autocommit を無効にします。)

つまり、SELECT文を実行すると、SELECT ... FOR SHARE に暗黙的に変換されるため、必ず共有ロックがかかる。

必ず共有ロックがかかるので、ロック競合が発生し、パフォーマンスの低下やデッドロックのリスクが増加する。

同時実行制御の失敗

Dirty Read

ダーティリードとは、データベースシステムのトランザクション処理において、あるトランザクションが処理の最中にストレージに書き込んだ未確定なデータを、他のトランザクションが読み込んで使用してしまうこと。

つまり、まだコミットされていないデータに対しても読み込みができてしまう。

引用元

実際に起こしてみた

STEP1: セッションAでトランザクションを開始し、table_a にデータを挿入する(未コミット)

-- トランザクションの分離レベルをREAD UNCOMMITTEDに変更
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

-- 現状のテーブルの状況を確認（ID: 1のみが入っている状態 ※画像1）
SELECT * FROM table_a;

-- table_a に対して、ID: 2のデータを挿入
INSERT INTO table_a(id, created_at) VALUES (2, now());

-- まだコミットはしていない

画像1

STEP2: セッションBでトランザクションを開始し、table_a の状態を確認する

-- トランザクションの分離レベルをREAD UNCOMMITTEDに変更
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;

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

-- 現状のテーブルの状況を確認
-- まだコミットされていないID: 2のデータが取得できてしまう（※画像2）
SELECT * FROM table_a;

画像2

Non-Repeatable Read

ノンリピータブルリードとは、データベースシステムのトランザクション処理において、あるトランザクションが同じレコードを何度も繰り返し読み出す場合に途中で他のトランザクションが内容を更新してしまうこと。

つまり、トランザクション内で、データを読み取るたびに、値が変わってしまう可能性がある。

引用元

実際に起こしてみた

STEP1: セッションAでトランザクションを開始し、table_a のデータを読み取る

-- トランザクションの分離レベルをREAD COMMITTED(READ UNCOMMITTEDでも可)に変更
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

-- ID: 1,2が存在する(※ 画像3)
SELECT * FROM table_a;

画像3

STEP2: セッションBでトランザクションを開始し、table_a のデータを更新

-- トランザクションの分離レベルをREAD COMMITTED(READ UNCOMMITTEDでも可)に変更
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

UPDATE table_a SET created_at = now() WHERE id = 2;

COMMIT;

STEP3: セッションAで再度、table_a のデータを読み取る

-- トランザクションの分離レベルをREAD COMMITTED(READ UNCOMMITTEDでも可)に変更
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- BEGIN;

-- ID: 1,2が存在する
-- SELECT * FROM table_a;

-- --------------
-- STEP:2 セッションBでデータ更新
-- --------------

-- ID: 2のcreated_atが更新されている(※画像4)
SELECT * FROM table_a;

COMMIT;

画像4

---

Phantom Read

ファントムリードとは、データベースシステムのトランザクション処理において、あるトランザクションが読み出しを複数回行うと、その間に他のトランザクションが追加したデータが増えてしまうこと。

つまり、トランザクション内で、データを読み取るたびに、データが増えたり（挿入）、消えたり（削除）してしまう可能性がある。

引用元

実際に起こしてみた

STEP1: セッションAでトランザクションを開始し、table_a のデータを読み取る

-- トランザクションの分離レベルをREAD COMMITTED(READ UNCOMMITTEDでも可)に変更
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

-- ID: 1,2が存在する(※ 画像5)
SELECT * FROM table_a;


-- table_a に対して、ID: 2のデータを挿入
INSERT INTO table_a(id, created_at) VALUES (2, now());

-- まだコミットはしていない

画像5

STEP2: セッションBでトランザクションを開始し、table_a にデータ挿入

-- トランザクションの分離レベルをREAD COMMITTED(READ UNCOMMITTEDでも可)に変更
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

BEGIN;

INSERT INTO table_a(id, created_at) VALUES (3, now());

COMMIT;

STEP3: セッションAで再度、table_a のデータを読み取る

-- トランザクションの分離レベルをREAD COMMITTED(READ UNCOMMITTEDでも可)に変更
-- SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

-- BEGIN;

-- ID: 1,2が存在する
-- SELECT * FROM table_a;

-- --------------
-- STEP:2 セッションBでデータ挿入
-- --------------

-- ID: 3が見えてしまう(※画像6)
SELECT * FROM table_a;

COMMIT;

画像6

最後に

なんとなく小難しいなと思っていたものも、実際に触りながら試してみると、理解が進んだり、案外楽しみながらできました。
やっぱり、触ってみるのが理解するためには大事なことを改めて実感しました。

弊社リンクアンドモチベーションでは、テックブログもやっておりますので、よかったら御覧ください。

テックブログ