MySQL トランザクション、分離、同時実行性

InnoDB トランザクションは、関連する変更をアトミックにコミットし、同時実行されるステートメントからの見え方を制御することで、データベースの不変条件を保護します。分離レベルは制約の代替ではありません。スキーマで表現できる不変条件には、主キー、一意制約、外部キー制約、チェック制約を使用し、そのうえで複数ステートメントによる判断に適したトランザクションとロックのパターンを選択します。

トランザクション境界と自動コミット #

MySQL ではデフォルトで自動コミットが有効になっているため、明示的なトランザクションの外側で実行される各ステートメントは、1 つのトランザクションとしてコミットまたはロールバックされます。複数ステートメントの単位は明示的に開始し、短く保ちます。

sql

START TRANSACTION;

UPDATE account
SET balance = balance - 100
WHERE id = 42 AND balance >= 100;

UPDATE account
SET balance = balance + 100
WHERE id = 84;

COMMIT;

宛先に入金する前に、最初の更新で影響を受けた行数を確認します。不変条件またはステートメントが失敗した場合は ROLLBACK を使用します。接続プールは、接続を返却する前に自動コミット、分離レベル、アクセスモード、その他のセッション状態を復元する必要があります。これらのプロパティはトランザクション間でも保持されるためです。

多くのデータ定義ステートメントおよびアカウント管理ステートメントは暗黙的にコミットします。アトミック DDL は、サポートされるスキーマステートメントをクラッシュセーフにしますが、そのステートメントを周囲のアプリケーショントランザクションの一部にするものではありません。

分離レベルの設定 #

InnoDB における MySQL 8.4 のデフォルトは REPEATABLE READ です。サーバーのデフォルトは --transaction-isolation または transaction_isolation で設定し、意図したスコープでのみ上書きします。

sql

SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SELECT @@GLOBAL.transaction_isolation, @@SESSION.transaction_isolation;

GLOBAL は新しいセッションに対するデフォルトを設定します。SESSION は、その接続で後続のトランザクションに適用されます。修飾子のない SET TRANSACTION は、次のトランザクションに適用されます。同じステートメントで READ ONLY と READ WRITE も選択できます。

MVCC と一貫性読み取り #

InnoDB は、アクティブなスナップショットが必要とする可能性がある間、以前の行バージョンを undo データに保持します。通常、通常の SELECT はロックを取得しない一貫性読み取りを実行します。

READ UNCOMMITTED はコミットされていないバージョンを公開する可能性があります。
READ COMMITTED は、一貫性読み取りごとに新しいスナップショットを作成します。
REPEATABLE READ は、トランザクション内で最初に行われる一貫性読み取り時にスナップショットを作成し、その後の一貫性読み取りで再利用します。
SERIALIZABLE は、自動コミットが無効な場合、通常の SELECT ステートメントを共有ロック読み取りに変換します。

書き込みおよび明示的なロック読み取りは、トランザクションの古い一貫性読み取りスナップショットではなく、現在のコミット済み状態を調べます。スナップショット読み取りと現在読み取りを混在させることは有効ですが、アプリケーションはこの違いを考慮する必要があります。長時間存続するスナップショットは undo 履歴も保持するため、パージへの負荷が増加する可能性があります。

InnoDB のロック種別 #

InnoDB は、ステートメントによって選択されたインデックスレコードおよびインデックス範囲をロックします。

レコードロックは、インデックスエントリを保護します。
ギャップロックは、インデックスエントリ自体をロックせず、その前の区間を挿入から保護します。
ネクストキーロックは、レコードロックと、その直前のギャップを組み合わせたものです。
意図ロックは、トランザクションが行レベルの共有ロックまたは排他ロックを保持している、または取得する予定であることをテーブルレベルで宣言します。これにより、テーブルロックの互換性チェックを効率化できます。

REPEATABLE READ では、ロックを取得する範囲スキャンにネクストキーロックを使用し、ファントム挿入を防ぎます。1 行を見つける一意インデックスの等価検索では、レコードロックだけを使用できます。READ COMMITTED は、外部キーおよび重複キーのチェックで MySQL が必要とする場合を除き、ほとんどの検索でギャップロックを無効にします。

アクセスパスによって、InnoDB が調査してロックできるエントリが決まります。したがって、インデックスがない場合や選択性が低い場合は、処理量と競合の両方が広がります。ロック対象の述語ごとに実行計画を確認します。

MySQL は、ステートメントがスキーマオブジェクトを使用している間、メタデータロックも取得します。テーブルにアクセスしたオープントランザクションが存在すると、行の競合がなくても ALTER TABLE が待機することがあります。Performance Schema では、こうした待機を診断するためのメタデータロックテーブルとデータロックテーブルが公開されています。

楽観的 Compare-and-Swap #

楽観的同時実行制御では、ビジネス条件とバージョン比較を 1 回の書き込みにまとめます。

sql

UPDATE account
SET balance = balance - 100,
    version = version + 1
WHERE id = 42
  AND version = 17
  AND balance >= 100;

影響を受けた行が 1 行であれば、状態遷移は成功しています。0 行の場合は、行、バージョン、またはビジネス述語がすでに一致しなくなっています。現在の状態を再読み込みし、操作が引き続き有効な場合にのみ再試行します。すべての競合を上限のない再試行に変換してはなりません。

悲観的ロック読み取り #

書き込む内容を決定する前にトランザクションが現在の状態を調べる必要がある場合は、ロック読み取りを使用します。

sql

START TRANSACTION;

SELECT available
FROM inventory
WHERE sku = 'A-42'
FOR UPDATE;

UPDATE inventory
SET available = available - 1
WHERE sku = 'A-42' AND available > 0;

COMMIT;

FOR SHARE は共有ロックを取得し、FOR UPDATE は排他ロックを取得します。NOWAIT は、競合するロックを待機せずに戻ります。SKIP LOCKED はロックされた行を除外できるため、キューコンシューマーをサポートできますが、意図的に一貫性のないビューを提示するため、一般的なビジネスクエリには適していません。ネットワーク呼び出し、リモートサービス、ユーザー入力は、ロック区間の外側に置きます。

アドバイザリロックとテーブルロック #

GET_LOCK(name, timeout) は、接続が所有するサーバー全体で名前付きのアドバイザリロックを取得します。コミットでは解放されません。RELEASE_LOCK、接続の終了、またはサーバーのシャットダウンによって解放されます。アドバイザリロックはテーブル行を保護しないため、すべての参加者が同じ命名規約を守る必要があります。

sql

SELECT GET_LOCK('schema-migration', 10);
-- 制限時間内で、接続を固定した処理を実行する。
SELECT RELEASE_LOCK('schema-migration');

アドバイザリロックは、協調的なシングルトン処理に使用し、データベース制約や行ロックの代替として使用してはなりません。接続プールは、名前付きロックをまだ所有している接続を返却してはなりません。

LOCK TABLES は、別個のセッションレベルの明示的なテーブルロック境界を確立し、暗黙的にコミットする場合があります。並行性を低下させ、通常の InnoDB 行ロックとは異なる形でトランザクションテーブルと相互作用します。文書化された管理ワークフローまたは互換性ワークフローに限定して使用し、必ず UNLOCK TABLES で境界を終了します。

デッドロック、ロック待機、再試行 #

同時実行下ではデッドロックが発生することを前提にします。InnoDB は待機サイクルを検出し、エラー 1213 とともに 1 つのトランザクションをロールバックします。innodb_lock_wait_timeout を超えるロック待機はエラー 1205 を返します。設定によっては、トランザクション全体ではなく失敗したステートメントだけがロールバックされる場合があります。アプリケーションは明示的にロールバックし、再試行する場合はクリーンなトランザクション境界から開始する必要があります。

行を一貫した順序でロックし、選択性の高いインデックスを使用し、トランザクションの存続時間を制限することで、回避可能な競合を減らします。繰り返し発生するケースは、SHOW ENGINE INNODB STATUS と、data_locks、data_lock_waits、metadata_locks などの Performance Schema テーブルで診断します。

再試行では、元の結果が不確実になった後に、データベース外の処理を再度実行する可能性があります。外部から観測可能な操作にはべき等キーまたは永続的な操作識別子を付与し、バックオフとジッターを伴って再試行回数を制限します。永遠に再試行するのではなく、最終的な競合を報告します。