SELECT ... FROM は一貫性読み取りであり、データベースのスナップショットを読み取り、トランザクションの分離レベルが SERIALIZABLE に設定されなければロックを設定しません。 SERIALIZABLE レベルの場合、検索で見つかったインデックスレコード上に共有ネクストキーロックが設定されます。 ただし、一意の行を検索するために一意のインデックスを使用して行をロックするステートメントには、インデックスレコードのロックのみが必要です。

一意インデックスを使用する SELECT ... FOR UPDATE および SELECT ... FOR SHARE ステートメントは、スキャンされた行のロックを取得し、結果セットに含まれない行のロックを解除します (たとえば、WHERE 句で指定された基準を満たさない場合)。 ただし場合によっては、クエリーの実行中に結果行とその元のソースとの関係が失われたために、行のロックがすぐに解除されない可能性もあります。 たとえば UNION では、スキャン (およびロック) されたテーブル内の行が、結果セットに含める対象となるかどうかの評価前に、一時テーブルに挿入される可能性があります。 この状況では、一時テーブル内の行と元のテーブル内の行との関係は失われているため、クエリー実行が終了するまで後者の行のロックは解除されません。

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

検索で検出されたインデックスレコードの場合、SELECT ... FOR UPDATE は、他のセッションによる SELECT ... FOR SHARE の実行または特定のトランザクション分離レベルでの読取りをブロックします。 一貫性読み取りでは、読み取られたビュー内に存在するレコードに設定されたロックはすべて無視されます。

UPDATE ... WHERE ... は、検索で見つかったすべてのレコード上に排他ネクストキーロックを設定します。 ただし、一意の行を検索するために一意のインデックスを使用して行をロックするステートメントには、インデックスレコードのロックのみが必要です。

UPDATE がクラスタ化されたインデックスレコードを変更すると、影響を受けるセカンダリインデックスレコードが暗黙的にロックされます。 UPDATE 操作では、新しいセカンダリインデックスレコードを挿入する前に重複チェックスキャンを実行するとき、および新しいセカンダリインデックスレコードを挿入するときに、影響を受けるセカンダリインデックスレコードの共有ロックも取得されます。

DELETE FROM ... WHERE ... は、検索で見つかったすべてのレコード上に排他ネクストキーロックを設定します。 ただし、一意の行を検索するために一意のインデックスを使用して行をロックするステートメントには、インデックスレコードのロックのみが必要です。

INSERT は、挿入される行に排他ロックを設定します。 このロックは、ネクストキーロックではなくインデックスレコードロックである (つまり、ギャップロックが存在しない) ため、ほかのセッションが挿入された行の前にあるギャップに挿入することは回避されません。

行を挿入する前に、挿入意図ギャップロックと呼ばれるギャップロックのタイプが設定されます。 このロックは、同じインデックスギャップに挿入する複数のトランザクションは、そのギャップ内の同じ場所に挿入しなければ相互に待機する必要がないように、意図的に挿入することを示しています。 値が 4 と 7 のインデックスレコードが存在すると仮定します。 それぞれ値 5 と 6 の挿入を試みる別々のトランザクションは、挿入される行の排他ロックを取得する前に挿入インテンションロックを使用して、4 と 7 の間にあるギャップをロックしますが、行の競合が発生しないため相互にブロックされません。

重複キーエラーが発生すると、重複インデックスレコード上の共有ロックが設定されます。 複数のセッションが同じ行を挿入しようとしているときに、別のセッションがすでに排他ロックを取得していた場合は、このように共有ロックを使用することでデッドロックが発生する可能性があります。 これは、別のセッションがその行を削除した場合に発生する可能性があります。 InnoDB テーブル t1 の構造が次のようになっているとします。

CREATE TABLE t1 (i INT, PRIMARY KEY (i)) ENGINE = InnoDB;

次に、3 つのセッションが次の処理を順番に実行するものとします。

セッション 1:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

セッション 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

セッション 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

セッション 1:

ROLLBACK;

セッション 1 による最初の処理では、行の排他ロックが取得されます。 セッション 2 と 3 の処理ではどちらも重複キーエラーが発生し、どちらのセッションも行の共有ロックをリクエストします。 セッション 1 はロールバック時に行の排他ロックを解放し、キュー内のセッション 2 と 3 の共有ロックリクエストが付与されます。 この時点でセッション 2 と 3 でデッドロックが発生します。どちらも他方が保持している共有ロックのために、行の排他ロックを取得できません。

キー値が 1 の行がテーブルに含まれている場合も似たような状況が発生し、3 つのセッションが次の処理を順番に実行します。

セッション 1:

START TRANSACTION;
DELETE FROM t1 WHERE i = 1;

セッション 2:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

セッション 3:

START TRANSACTION;
INSERT INTO t1 VALUES(1);

セッション 1:

COMMIT;

セッション 1 による最初の処理では、行の排他ロックが取得されます。 セッション 2 と 3 の処理ではどちらも重複キーエラーが発生し、どちらのセッションも行の共有ロックをリクエストします。 セッション 1 はコミット時に行の排他ロックを解放し、キュー内のセッション 2 と 3 の共有ロックリクエストが付与されます。 この時点でセッション 2 と 3 でデッドロックが発生します。どちらも他方が保持している共有ロックのために、行の排他ロックを取得できません。

INSERT ... ON DUPLICATE KEY UPDATE は、重複キーエラーが発生したときに更新される行に共有ロックではなく排他ロックが設定されるという点で、単純な INSERT と異なります。 重複する主キー値に対して排他的なインデックスレコードロックが取得されます。 重複する一意キー値に対して排他的なネクストキーロックが取得されます。

REPLACE は、一意のキーが競合していなければ、INSERT と同様に動作します。 それ以外の場合は、置換される行に排他ネクストキーロックが配置されます。

INSERT INTO T SELECT ... FROM S WHERE ... では、T に挿入された各行に排他的インデックスレコードロック (ギャップロックなし) が設定されます。 トランザクション分離レベルが READ COMMITTED の場合、InnoDB は S で一貫性読取り (ロックなし) として検索を実行します。 それ以外の場合、InnoDB は S から取得した行に共有ネクストキーロックを設定します。 後者の場合、InnoDB はロックを設定する必要があります: ステートメントベースのバイナリログを使用したロールフォワードリカバリ中は、すべての SQL ステートメントを、最初に実行されたのとまったく同じ方法で実行する必要があります。

CREATE TABLE ... SELECT ... は、INSERT ... SELECT の場合と同様に、SELECT を共有ネクストキーロックを使用して実行するか、一貫性読み取りとして実行します。

構造文 REPLACE INTO t SELECT ... FROM s WHERE ... または UPDATE t ... WHERE col IN (SELECT ... FROM s ...) で SELECT が使用されると、InnoDB はテーブル s の行に共有ネクストキーロックを設定します。

InnoDB は、テーブルで以前に指定された AUTO_INCREMENT カラムを初期化する際に、AUTO_INCREMENT カラムに関連付けられたインデックスの最後に排他ロックを設定します。

innodb_autoinc_lock_mode=0 では、InnoDB は特別な AUTO-INC テーブルロックモードを使用します。このモードでは、自動増分カウンタにアクセスしながら、ロックが取得され、(トランザクション全体の最後ではなく) 現在の SQL ステートメントの最後まで保持されます。 AUTO-INC テーブルロックが保持されている間は、ほかのクライアントはそのテーブルに挿入できません。 innodb_autoinc_lock_mode=1 を使用した「「一括挿入」」でも同じ動作が発生します。 テーブルレベルの AUTO-INC ロックは、innodb_autoinc_lock_mode=2 では使用されません。 詳細は、セクション15.6.1.6「InnoDB での AUTO_INCREMENT 処理」 を参照してください。

InnoDB は、ロックを設定せずに、事前に初期化された AUTO_INCREMENT カラムの値をフェッチします。

FOREIGN KEY 制約がテーブル上で定義されている場合は、制約条件をチェックする必要がある挿入、更新、または削除が行われると、制約をチェックするために、参照されるレコード上に共有レコードレベルロックが設定されます。 InnoDB は、制約が失敗する場合に備えて、これらのロックの設定も行います。

LOCK TABLES はテーブルロックを設定しますが、これらのロックを設定する InnoDB レイヤーよりも上位の MySQL レイヤーです。 InnoDB は、innodb_table_locks = 1 (デフォルト) かつ autocommit = 0 の場合にテーブルロックを認識し、InnoDB よりも上位の MySQL レイヤーは、行レベルロックを識別します。

それ以外の場合は、InnoDB の自動デッドロック検出では、このようなテーブルロックが関与するデッドロックを検出できません。 また、この場合には上位の MySQL レイヤーは行レベルロックを識別しないため、現在別のセッションが行レベルロックを保持しているテーブル上でテーブルロックを取得できます。 ただし、セクション15.7.5.2「デッドロック検出」で説明したように、これによりトランザクションの完全性が危険にさらされることはありません。

innodb_table_locks=1 (デフォルト) の場合、LOCK TABLES で各テーブル上に 2 つのロックが取得されます。 MySQL レイヤーでのテーブルロックに加えて、InnoDB テーブルロックも取得されます。 バージョン 4.1.2 よりも前の MySQL では、InnoDB テーブルロックが取得されませんでした。この古い動作は、innodb_table_locks=0 を設定すれば選択できます。 InnoDB テーブルロックが取得されない場合は、テーブルの一部のレコードがほかのトランザクションによってロックされなくても、LOCK TABLES が完了します。

MySQL 8.0 では、LOCK TABLES ... WRITE を使用して明示的にロックされたテーブルには、innodb_table_locks=0 が無効です。 LOCK TABLES ... WRITE で暗黙的に (たとえば、トリガーを使用して)、または LOCK TABLES ... READ によって、読み取りまたは書き込み用にロックされたテーブルには有効です。

トランザクションで保持されているすべての InnoDB ロックは、トランザクションがコミットまたは中止されると解放されます。 したがって、autocommit=1 モードの InnoDB テーブル上で LOCK TABLES を呼び出しても、取得された InnoDB テーブルロックはすぐに解放されてしまうため、まったく意味がありません。

LOCK TABLES では暗黙的な COMMIT および UNLOCK TABLES が実行されるため、トランザクションの実行中に追加のテーブルをロックできません。