REPEATABLE READ

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

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

READ COMMITTED

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

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

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

READ COMMITTED 分離レベルでは、行ベースのバイナリロギングのみがサポートされます。 READ COMMITTED を binlog_format=MIXED とともに使用する場合、サーバーは自動的に行ベースのロギングを使用します。

READ COMMITTED の使用には、追加の効果があります:

次のような例について、このテーブルから検討します。

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);
COMMIT;

この場合、テーブルにはインデックスがないため、検索およびインデックススキャンでは、インデックス付けされたカラムではなく、非表示のクラスタインデックスをレコードロックに使用します (セクション15.6.2.1「クラスタインデックスとセカンダリインデックス」 を参照)。

あるセッションで、次のステートメントを使用して UPDATE を実行するとします:

# Session A
START TRANSACTION;
UPDATE t SET b = 5 WHERE b = 3;

また、最初のセッションのステートメントの後に次のステートメントを実行して、別のセッションで UPDATE を実行するとします:

# Session B
UPDATE t SET b = 4 WHERE b = 2;

InnoDB は各 UPDATE を実行する際に、まず各行の排他ロックを取得し、次にその行を変更するかどうかを判断します。 InnoDB は、行を変更しない場合、ロックを解放します。 それ以外の場合、トランザクションが終了するまで InnoDB はそのロックを保持します。 これにより、トランザクション処理が次のような影響を受けます。

デフォルトの REPEATABLE READ 分離レベルを使用する場合、最初の UPDATE は読取り対象の各行に対して x ロックを取得し、それらのいずれも解放しません:

x-lock(1,2); retain x-lock
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); retain x-lock
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); retain x-lock

次のように、2 番目の UPDATE は (1 番目の更新がすべての行のロックを保持しているため)、ロックを取得しようとしてもすぐにブロックされ、1 番目の UPDATE がコミットまたはロールバックを実行するまで続行されません。

x-lock(1,2); block and wait for first UPDATE to commit or roll back

かわりに READ COMMITTED が使用されている場合、最初の UPDATE は読み取る各行で x ロックを取得し、変更しない行の X ロックを解放します:

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); retain x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); retain x-lock
x-lock(5,2); unlock(5,2)

2 番目の UPDATE では、InnoDB は 「semi-consistent」 読取りを実行し、MySQL に読み取られた各行の最新のコミット済バージョンを返して、MySQL がその行が UPDATE の WHERE 条件に一致するかどうかを判断できるようにします:

x-lock(1,2); update(1,2) to (1,4); retain x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); retain x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); retain x-lock

ただし、WHERE 条件にインデックス付けされたカラムが含まれており、InnoDB でインデックスが使用されている場合は、レコードロックを取得および保持するときにインデックス付けされたカラムのみが考慮されます。 次の例では、最初の UPDATE は、b = 2 の各行で x ロックを取得して保持します。 2 番目の UPDATE は、カラム b に定義されたインデックスも使用するため、同じレコードで x ロックを取得しようとするとブロックされます。

CREATE TABLE t (a INT NOT NULL, b INT, c INT, INDEX (b)) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2,3),(2,2,4);
COMMIT;

# Session A
START TRANSACTION;
UPDATE t SET b = 3 WHERE b = 2 AND c = 3;

# Session B
UPDATE t SET b = 4 WHERE b = 2 AND c = 4;

READ COMMITTED 分離レベルは、起動時に設定することも、実行時に変更することもできます。 実行時に、すべてのセッションに対してグローバルに設定することも、セッションごとに個別に設定することもできます。

READ UNCOMMITTED

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

SERIALIZABLE

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