外部キー制約の親子テーブルにおいて共有ロックからのdeadlock

deadlock のログを発見し、調べました。

結論はタイトルの通りです。

とりあえず、サンプルのSQLで試します。

CREATE TABLE players (
  id int primary key,
  name varchar(20)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE player_items (
  id int primary key,
  player_id int,
  item_id int,
  CONSTRAINT fk_player_id FOREIGN KEY (player_id) REFERENCES players (id)
) ENGINE=INNODB DEFAULT CHARSET=utf8;

INSERT INTO players (id, name) VALUES (1, "player_1");

ターミナル1

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO player_items (id, player_id, item_id) VALUES (1, 1, 1);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE players set name = "player_one" where id = 1;
Query OK, 0 rows affected (6.29 sec)
Rows matched: 1  Changed: 0  Warnings: 0

ターミナル2

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> INSERT INTO player_items (id, player_id, item_id) VALUES (10, 1, 2);
Query OK, 1 row affected (0.00 sec)

mysql> UPDATE players set name = "player_ichi" where id = 1;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
mysql>

deadlock の発生していたコードをみたとき、UPDATE 文でロック待ちをしても、deadlock が起こるとは思いませんでした。

SHOW ENGINE INNODB STATUS;を確認すると、

共有ロックかかっているレコードをUPDATEしようとしてデッドロックになっているようでしたが、コード上は明示的に共有ロックかけているようなところは見当たらず、なぜ共有ロックがかかっているのか不明でした。。

MySQL :: MySQL 5.6 リファレンスマニュアル :: 14.6.6 InnoDB と FOREIGN KEY 制約

外部キーチェックの実行時に、InnoDB は、調査対象の子または親のレコード上に共有の行レベルロックを設定します。

どうやら MySQL の外部キー制約の仕様で、外部キー制約のテーブルへの INSERT で共有ロックがかかっているようです。

今回は一時的に SET FOREIGN_KEY_CHECKS=0 で外部キー制約を一時的に外すことで対応しました。