UPDATE1文だけでもデッドロックするという話

デッドロックは複数のトランザクションが複数のレコードをロックする場合にタイミングによって発生する。

1. トランザクションAがレコード1をロック→成功
2. トランザクションBがレコード2をロック→成功
3. トランザクションAがレコード2をロック→2のロックの解放待ち
4. トランザクションBがレコード1をロック→1のロックの解放待ち

AもBも互いのロックの解放を待つことになってデッドロックする。
MySQLはデッドロックを検出すると、片方のトランザクションでデッドロックエラーを発生させて強制的にトランザクションを終了させる。

たとえば、次のような2つのトランザクションを同時に動かすとデッドロックエラーが発生する。

CREATE TABLE t (a INT, val INT, INDEX(a));
INSERT INTO t VALUES (1,0),(2,0),(3,0),(4,0),(5,0),(6,0),(7,0),(8,0),(9,0);

トランザクションA

BEGIN;
UPDATE t SET val=0 WHERE a=1;  # レコード1をロック
UPDATE t SET val=0 WHERE a=9;  # レコード9をロック
COMMIT;

トランザクションB

BEGIN;
UPDATE t SET val=0 WHERE a=9;  # レコード9をロック
UPDATE t SET val=0 WHERE a=1;  # レコード1をロック
COMMIT;

こんな感じ

端末A

% while mysql test -e 'begin; update t set val=1 where a=1; update t set val=1 where a=9; commit;'; do echo -n .; done
.........................................................................................................

端末B

% while mysql test -e 'begin; update t set val=1 where a=9; update t set val=1 where a=1; commit;'; do echo -n .; done
.................................................ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction

明に BEGIN でトランザクションを書かずに UPDATE 文一つだけであっても複数レコードをロックするような場合であればデッドロックが発生し得る。

CREATE TABLE x (a INT, b INT, val INT, INDEX(a), INDEX(b));
INSERT INTO x VALUES (1,9,0),(2,8,0),(3,7,0),(4,6,0),(5,5,0),(6,4,0),(7,3,0),(8,2,0),(9,1,0);

トランザクションA

UPDATE x SET val=0 WHERE a IN (1,9);

トランザクションB

UPDATE x SET val=0 WHERE b IN (1,9);

端末A

% while mysql test -e 'update x set val=0 where a in (1,9)'; do echo -n .; done
.........................................................................................................

端末B

% while mysql test -e 'update x set val=0 where b in (1,9)'; do echo -n .; done
.....ERROR 1213 (40001) at line 1: Deadlock found when trying to get lock; try restarting transaction

この場合レコードをロックする順番はクエリが使用しているインデックスの順番なので、

1. A が (a,b)=(1,9) のレコードをロック→成功
2. B が (a,b)=(9,1) のレコードをロック→成功
3. A が (a,b)=(9,1) のレコードをロック→2のロックの解放待ち
4. B が (a,b)=(1,9) のレコードをロック→1のロックの解放待ち

…となってデッドロックするという感じ。

なお、インデックス b を逆順にすると、同じデータ＆同じクエリでも、

CREATE TABLE x (a INT, b INT, val INT, INDEX(a), INDEX(b DESC));
INSERT INTO x VALUES (1,9,0),(2,8,0),(3,7,0),(4,6,0),(5,5,0),(6,4,0),(7,3,0),(8,2,0),(9,1,0);

1. A が (a,b)=(1,9) のレコードをロック→成功
2. B が (a,b)=(1,9) のレコードをロック→1のロックの解放待ち
3. A が (a,b)=(9,1) のレコードをロック→成功
4. A が終了(=COMMIT)して1のロックが解放される
5. B が (a,b)=(9,1) のレコードをロック→成功

…みたいな感じになりデッドロックは発生しなくなる。

ただし、インデックスに DESC が効くのは MySQL 8.0 からなので、5.7 では無意味。