読者です 読者をやめる 読者になる 読者になる

【MySQL】外部キー制約とロックとデッドロックについて

mysql

はじめに

MySQLはよく使っているのですが外部キー制約はほとんど使った事ありませんでした。
使うデメリットとしてはデータの登録作業が面倒だから・・・程度で考えていました

が、今回は既に外部キーを採用しているシステムでの作業でしたがほとんど経験なかったので少し苦労しました
発生した現象としては原因不明のデッドロックエラーでこのようなエラーでした

Deadlock found when trying to get lock; try restarting transaction

なぜ意味不明かと思ったかと言うと以下からそう思っていました
・対象のテーブルを使用している箇所が1箇所だけ
・必ず一意となるデータをINSERTするだけ

概要

会員テーブル(user)

会員ID(id) 会員名(name) カウント(count)
1 太郎 0
2 花子 0

商品テーブル(item)

商品ID(id) 商品名(name) カウント(count)
1 りんご 0
2 みかん 0

アクセスログテーブル(access)

会員ID(user_id) 商品ID(item_id) 日時(access_date)
1 1 2016-03-18 00:00:00
1 2 2016-03-18 00:01:00

こんな感じで、access.user_idとaccess.item_idにはそれぞれuser.id、item.idへの外部キーを定義してありました。
で、実装してある処理のイメージとしては以下のようなものでした
・会員がサイトのあるページににアクセスするたびにaccessへinsert
バッチ処理accessからデータを取得してその件数をuser.countとitem.countへupdate

処理実装

accessへの登録処理

$user_id = ・・・
$item_id = ・・・

BEGIN
INSERT INTO access(user_id, item_id, access_date) VALUES ($user_id, $item_id, NOW());
・・・
COMMIT

user.countとitem.countへの更新処理(1分間間隔とかで常に実行されているイメージ)

BEGIN

// accessからデータを取得してuser.countを更新
$result = SELECT user_id, COUNT(*) AS count FROM access GROUP BY user_id;
foreach ($result as $v) {
    UPDATE user SET count = count + $v["count"] WHERE id = $v["user_id"];
}

// accessからデータを取得してitem.countを更新
$result = SELECT COUNT(*) FROM access GROUP BY item_id;
foreach ($result as $v) {
    UPDATE item SET count = count + $v["count"] WHERE id = $v["item_id"];
}
・・・
COMMIT

今回はこのような処理でデッドロックが発生しました

デッドロックの再現

コンソールを2つ立ち上げてそれぞれMySQLコマンドを実行するイメージです

処理A 処理B 説明
begin begin
user.id=1のuser.countを更新 ・処理Bがuser.id=1のロックを取得
insert into access (user_id,item_id)
vales(1,100)
・処理Bがuser.id=1のロックを持つため待ちに入る
・item.id=100のロックを取得
item.id=100のitem.countを更新 ・処理Aがitem.id=100のロックを持つため待ちに入る
デッドロック発生!!

上記からわかるように外部キーを設定した場合、
参照元(子)のテーブルにINSERTすると参照先(親)のレコードのロックを取得する
ということのようで今回のようなことが発生しました

結論

やっぱり外部キーはどうしても必要でなければ今後も使わないほうがよさそうだな・・・と改めて思いました

以上です