MySQL (InnoDB) でデッドロック検知される条件について

解決されてしばらく経っていますが、あとから見にきた方のために少しだけ補足します。

今回の例でデッドロックが発生するのは、トランザクション分離レベルがREPEATABLE READの場合です。InnoDBはREPEATABLE READがデフォルト設定となっていますが、ここをあえてREAD COMMITTEDに設定するとこのデッドロックは発生しなくなります。これはREAD COMMITTEDの場合、最初のSELECT FOR UPDATE文がギャップロックを確保しないためです。

もちろんREAD COMMITTEDではギャップロックを確保しないことによりファントムリードが発生しますし、さらにファジーリードも発生しますので、既存のプログラムに対していきなりトランザクション分離レベルを変更することは難しいです。

個人的には、過去のしがらみのない新規プログラムであればREAD COMMITTEDを選択し、ファジーリードを起こしたくないところは適宜LOCK IN SHARE MODEかFOR UPDATEをかける、ファントムリードについては妥協するか1トランザクション内で同じテーブルを何度も参照しないよう設計面で注意する、といった対応にした方が面倒が少ないのではないかと考えています。Oracle Database、Microsoft SQL Server、PostgreSQLはデフォルトがREAD COMMITTEDですので、移植性も良くなります。

なおREAD COMMITTEDの場合はバイナリログのフォーマットがROWになるので、バイナリログの容量増加について注意する必要があります。

SELECT ～ FOR UPDATE が空振りしたことで InnoDB のギャップロックによるデッドロックになったのだと思われます。

例えば次のようなテーブル定義で・・・

CREATE TABLE z
(
  id INT NOT NULL,
  PRIMARY KEY (id)
);

INSERT INTO z (id) VALUES (2), (6);

次のSQLを並列に実行した場合、SELECT ～ FOR UPDATE が空振りするとタイミングによってはデッドロックします。

BEGIN;
SELECT * FROM z WHERE id = 4 FOR UPDATE;
INSERT INTO z VALUES (4);
COMMIT;

SELECT ～ FOR UPDATE で行がヒットしない場合、そのトランザクション中でその行（id が 4 の行）が存在しないことを保証する必要があります。
ただし、存在しない行はロック出来ないのでギャップロックという方法で、↑のケースであれば id が 3 ～ 5 の行が共有ロックされたような状態になります。

そのため、2 つのトランザクション (A) と (B) が以下のように並列に実行されるとデッドロックします。

/* (A). 3～5 が共有ロック */
SELECT * FROM z WHERE id = 4 FOR UPDATE;

/* (B). 3～5 が共有ロック(共有なので競合しない) */
SELECT * FROM z WHERE id = 4 FOR UPDATE;

/* (A). (B) の共有ロックと競合する */
INSERT INTO z VALUES (4);

/* (B). (A) の共有ロックと競合する → デッドロック！*/
INSERT INTO z VALUES (4);