MySQL

 

MySQLの外部キーとデッドロック

MySQLの外部キーが何故デッドロックを起こしやすいのかを検証します。何故デッドロックが発生しやすいのかを正しく把握し、対処方法を把握しましょう。もうMySQLは外部キーが使えないなんて言わせんません!

新サイト、tree-mapsを公開しました!!

tree-maps: 地図のWEB TOOLの事ならtree-mapsにお任せ!

地図に関するWEB TOOL専門サイトです!!

大画面で大量の緯度経度を一気にプロット、ジオコーディング、DMS<->DEGの相互変換等ができます!

◯ 広告
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は必要になりますね。

treeおすすめの書籍です!
【送料無料】実践ハイパフォ-マンスMySQL第2版

【送料無料】実践ハイパフォ-マンスMySQL第2版
価格:4,830円(税込、送料別)

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド

【送料無料】エキスパ-トのためのMySQL運用+管理トラブルシュ-ティングガイド
価格:3,465円(税込、送料別)

【送料無料】基礎からのMySQL改訂版

【送料無料】基礎からのMySQL改訂版
価格:3,129円(税込、送料別)

◯ 広告