https://ngyuki.hatenablog.com/entry/2013/04/22/151042

先日、作っていたシステムでデッドロックがわりかし頻繁に発生することが判りました。

RDBMS のデッドロックは必ずしもバグでは無く、無理に対応するよりアプリケーション側でハンドリングしてトランザクションのリトライなどを行った方がいい場合もあると思いますが、このケースでは明らかにバグでした。

デッドロックするクエリ

テーブル定義は次の通りで・・・

CREATE TABLE tt
(
  id INTEGER NOT NULL PRIMARY KEY,
  no INTEGER NOT NULL
);

INSERT INTO tt VALUES (1, 0);

次のようなSQLで更新します。

BEGIN;

/* (1) */
SELECT @no := no FROM tt WHERE id = 1 LOCK IN SHARE MODE;

/* (2) */
UPDATE tt SET no = @no + 1 WHERE id = 1;

COMMIT;

(1) のクエリで取得した行をアプリケーション側で加工して (2) のクエリで書き戻しています。(1) で取得してから (2) で書き戻すまでに他トランザクションに更新されると困るので LOCK IN SHARE MODE でロックしています。上の例だと no 列をインクリメントしているだけですが、実際にはSQLだけでは解決出来ない色々な処理を行なっていました。

この SQL を次のように [A] と [B] の２本の接続で並列に実行するとデッドロックします。

/* [A] */ BEGIN;
/* [B] */ BEGIN;

/* [A] */ SELECT @no := no FROM tt WHERE id = 1 LOCK IN SHARE MODE;
/* [B] */ SELECT @no := no FROM tt WHERE id = 1 LOCK IN SHARE MODE;

/* [A] */ UPDATE tt SET no = @no + 1 WHERE id = 1;
/* [B] */ UPDATE tt SET no = @no + 1 WHERE id = 1;

/* [A] */ COMMIT;
/* [B] */ COMMIT;

まちがい

デッドロックした理由は簡単で、次のように互いにブロックしあっているからです。

[A] の UPDATE が [B] の SELECT ～ LOCK IN SHARE MODE でブロックされる
[B] の UPDATE が [A] の SELECT ～ LOCK IN SHARE MODE でブロックされる

更新するための行を先立って取得するなら LOCK IN SHARE MODE ではなく名前の通りに FOR UPDATE を使うべきでした。私ってほんとバカ。