drop table if exists child;
create table child (id int, pid int, primary key (id, pid))engine=innodb;
drop table if exists parent;
create table parent (id int, count int, primary key (id))engine=innodb;
insert into parent values (1, 0);
alter table child add foreign key (id) references parent (id);
トランザクションA
-------------------------------------
begin;
insert into child values (1, 1);
update parent set count = count + 1 where id = 1;
commit;
トランザクションB
-------------------------------------
begin;
insert into child values (1, 2);
update parent set count = count + 1 where id = 1;
commit;
上から順番に実行して見て下さい。トランザクションBのupdateを実行した時点でデッドロック確定です。
トランザクション分離レベルに関係無く、デッドロックが発生します。
また、AとBのupdateの順序が逆転しても結果は同じです。
文章で説明すると、人事部テーブルに社員を2人追加し、人事部テーブルの人数をインクリメントしたら、デッドロック確定です。
結論を言うと、外部キーはslock（共有ロック）を取得してしまうからなのです。
slockは、xlock（排他ロック）をブロックしますが、slockをブロックしません。ここが問題なのです。
つまり、別セッションによる親テーブルのロック取得を許してしまうのです。
子テーブルのinsertでロックを取得するのは、親に属さない子が存在しない事を保証するためです。
これだけ読むと仕様通りなのですが、問題なのは自動的にslockを取得する点です。
全く意識しなくても自動的にslockが握られるので、いつの間にかデッドロックになる、というのがデッドロックが起きやすい原因です。
では順番に検証していきます。
トランザクションA
-------------------------------------
begin;
insert into child values (1, 1);
update parent set count = count + 1 where id = 1;
ここで外部キーの機能が自動的に親テーブルの外部キーidをslock。
Aがwhere句でidにxlock。しかし、Bが既にidにslockしているので、AのxlockはBのslockにブロックされてロック開放待ち。
この状態でセッションBのupdateが実行されると、お互いのロックの開放待ちになり、Bがデッドロック。
これは実は簡単です。Bによるslockを阻止すればいいのです。
つまり、Bより先にxlockを取得することで解消するのです。slockだとブロックしないのでxlockです。
トランザクションA
-------------------------------------
begin;
select id from parent where id = 1 for update;
insert into child values (1, 1);
update parent set count = count + 1 where id = 1;
commit;
トランザクションB
-------------------------------------
begin;
select id from parent where id = 1 for update;
insert into child values (1, 2);
update parent set count = count + 1 where id = 1;
commit;
このように、親の外部キーをfor updateでxlockするだけです。
もしくは、以下のように先に親テーブルを更新する事でも解消します。
トランザクションA
-------------------------------------
begin;
update parent set count = count + 1 where id = 1;
insert into child values (1, 1);
commit;
トランザクションB
-------------------------------------
begin;
update parent set count = count + 1 where id = 1;
insert into child values (1, 2);
commit;
トランザクションA
-------------------------------------
begin;
select id from parent where id = 1 lock in share mode;
insert into child values (1, 1);
update parent set count = count + 1 where id = 1;
commit;
トランザクションB
-------------------------------------
begin;
select id from parent where id = 1 lock in share mode;
insert into child values (1, 2);
update parent set count = count + 1 where id = 1;
commit;
このように、外部キーの代わりに、明示的にlock in share modeでslockしてもデッドロックになります。
使えます。必ず親テーブルをxlockしてから更新する、というルールを守れば問題ありません。
これは外部キーが無くても必要な事なので、結局親テーブルのxlockは必要になりますね。
