QA@IT

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

22960 PV

InnoDBにはデッドロック検出機能があると言われていますが、これがいまひとつどういう条件で発動するのかがよくわかっていません。

http://dev.mysql.com/doc/refman/5.5/en/innodb-deadlock-detection.html

とくに、SHOW ENGINE INNODB STATUSLATEST DETECTED DEADLOCKセクションをみたときに、transaction (1), (2)ともにACTIVE 0 secで、まったく待ち時間なく、瞬時にデッドロックと判定されていることがあります。

この、デッドロックと判定される条件とは、具体的にどういうものでしょうか?

具体例を出します。今回発生してる例では、同じUNIQUE INDEX (a, b, c)上の全く同一のタプル(1, 2, 3)に対して

SELECT ... WHERE a=1 and b=2 and c=3 FOR UPDATE

INSERT INTO ... (a, b, c, v) VALUES (1,2,3,1) ON DUPLICATE KEY UPDATE v = v + 1

がコンフリクトして、前者がロールバックされています。コード的には、単純化していうと

START TRANSACTION
SELECT ... WHERE a=1 and b=2 and c=3 FOR UPDATE
INSERT INTO ... (a, b, c, v) VALUES (1,2,3,1) ON DUPLICATE KEY UPDATE v = v + 1
COMMIT

が並列に走るイメージです。

期待した挙動は、SELECT ... FOR UPDATEに入った時点で、すでに稼働中のINSERT ... ON DUPLICATE KEY UPDATEが同じタプルに対して存在していたら、それが終わるまで(ロックの解放を)待つ、というものでしたが、そのようには動いてくれず、デッドロックになってしまうようです。

  • innodb_lock_wait_timeoutはいくつになってますか? -
  • デフォルトなので50です。ちなみにLock timeoutは発生していません。 -

回答

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

今回の例でデッドロックが発生するのは、トランザクション分離レベルが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になるので、バイナリログの容量増加について注意する必要があります。

編集 履歴 (0)
  • 的確な回答をありがとうございます。READ COMMITTEDにした場合の欠点ってバイナリログがrow-basedになることぐらいなんですかね?であれば、たしかに説明されてる注意点をふまえて新規アプリではREAD COMMITTEDにするのがいい気がしてきました。 -
  • READ COMMITTEDはSQL実行ごとのkernel_mutexによる排他制御が遅いという話があります。MySQL 5.6で改善されるかも知れません。
    http://on.fb.me/RI1EEl

    またBINLOG_FORMAT=ROWは主キーのないテーブルでスレーブの性能が極端に悪化することがあり、MySQL 5.6で対策が導入されます。
    http://bit.ly/UC37sm
    -

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);
編集 履歴 (1)
  • なるほど、再現しました。LOCK IN SHARE MODEではないFOR UPDATEって排他ロックだと思っていたのですが、どうも共有ロックっぽいですね。で、この空間に複数のギャップロックがセットされるので即座にデッドロック判定されるってことですね。SELECT FOR UPDATEをやめて直接INSERTを競わせるように変更することで解決できました。ありがとうございます。 -
ウォッチ

この質問への回答やコメントをメールでお知らせします。