どうも！デッドロック撲滅委員会会長の優です。
今回はトランザクションでよくありがちなデッドロックのご紹介。
デッドロックとは、複数のプロセスが互いに相手の占有している資源の解放を待ってしまい、処理が停止してしまうこと。データベースの排他制御の不備などが原因で起こる。
資源aを占有する処理Aと資源bを占有する処理Bが並列に実行されており、Aが次の処理にbを、Bが次の処理にaを利用したい場合、AとBは互いに相手が資源を解放するのを待ってしまい、どちらも処理が停止してしまう。AやBの処理内容自体に誤りが無くても起きるため、原因不明の不具合としてなかなか発見されないことが多い。
@see e-Words
引用文に原因不明とありますがその通りで。DBが何故か落ちるという相談を頂くことがあります。
『DBが時々落ちる』という相談を受けたら、デッドロックを疑う!!
- トランザクション命令が行われているテーブルで、なぜかストレージエンジンがMyISAM(!!)
→InnoDBに変更する必要があります。 - ロールバック時のエラー制御の仕組みがない。
プログラムでロールバックや検知の仕組みの実装。また、ロックする順番や制御などの検証を行い根本的解決を行います。
DBが固まったり、落ちると聞くシステムの引継ぎの際はチェックをすると良いかもしれません。
バックナンバー
たすきがけのデッドロック
よくデッドロックはなんぞや？といった時に提示されるパターンです。
CREATE TABLE a_table(
a_id INT UNSIGNED PRIMARY KEY,
point INT UNSIGNED,
value VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO a_table(a_id, point, value) VALUES
(1, 100, "a");
CREATE TABLE b_table(
b_id INT UNSIGNED PRIMARY KEY,
point INT UNSIGNED,
value VARCHAR(255)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO b_table(b_id, point, value) VALUES
(1, 100, "a");
解決には？
テーブルへの書き込みの順番を揃えてあげることで解決です！
簡単ですね！
外部キー制約によるデッドロック
外部キー制約があるテーブルはINSERT時に親テーブルに共有ロックがかかります。
CREATE TABLE parent_job( job_id INT UNSIGNED PRIMARY KEY, name VARCHAR(255) )ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO parent_job (job_id, name) VALUES (1, "勇者"); CREATE TABLE child_member( user_id INT UNSIGNED PRIMARY KEY, name VARCHAR(255) NOT NULL, job_id INT UNSIGNED, FOREIGN KEY(job_id) REFERENCES parent_job(job_id) )ENGINE=InnoDB DEFAULT CHARSET=utf8; INSERT INTO testdb.child_member (user_id, name, job_id) VALUES (1, "You", 1);
たすきがけになっていないはずなのに…どうして？
何気に見逃しがちなパターンです。
解決
外部キー制約がされているテーブルを操作する前に、親のテーブルに対して先に排他ロックをかけておきます。
すぐに対応が出来ない！
設計の見直しや検証が終わるまで対症療法として、デッドロックになっても早くタイムアウトするようにしておく、という手もあります。
/etc/my.cnf
innodb_lock_wait_timeout=10;
プログラムでロールバックする
また、プログラム側でデッドロックを検地した場合はロールバックする仕組みを実装することも出来ます。
try{
(略)
$dbh->beginTransaction();
try {
(略)
//コミット
$dbh->commit();
}catch(PDOException $e){
//ロールバック
$dbh->rollback();
throw $e;
}
} catch(PDOException $e){
echo $e->getMessage();
}
ロールバックの仕組みの実装と同時にデッドロック時にログに出力するようにしたり、メールなどの通知でデッドロックを検知する仕組みを実装すると良いかもしれませんね！
ギャップロック
ギャップロック: これはインデックスレコード間にあるギャップのロック、または先頭のインデックスレコードの前や末尾のインデックスレコードのあとにあるギャップのロックです。
@see https://dev.mysql.com/doc/refman/5.6/ja/innodb-record-level-locks.html
インデックスの空振りロックです。
CREATE TABLE a_table(
a_id INT UNSIGNED PRIMARY KEY,
point INT UNSIGNED,
value VARCHAR(255),
INDEX idx_a_id(a_id)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO a_table(a_id, point, value) VALUES
(1, 100, "a"),
(2, 100, "b"),
(3, 100, "c"),
(9, 100, "i"),
(10, 100, "j");
パターン1 排他ロックで対象IDにレコードが存在しない場合
Aトランザクション
BEGIN; SELECT * FROM a_table WHERE a_id > 10 FOR UPDATE;
Bトランザクション
BEGIN; INSERT INTO a_table(a_id, point, value) VALUES(14, 100, "n");
Aトランザクション
BEGIN; SELECT * FROM a_table WHERE a_id = 5 FOR UPDATE;
ギャップロックがかかる。
パターン2 指定IDの排他ロックを空振りした場合
Aトランザクション
BEGIN; SELECT * FROM a_table WHERE a_id = 5 FOR UPDATE;
Bトランザクション
BEGIN; INSERT INTO a_table(a_id, point, value) VALUES(14, 100, "n");
INSERT INTO a_table(a_id, point, value) VALUES(8, 100, "n");
ギャップロックがかかる。
モード、トランザクション！
降順SELECTと昇順SELECTソートで魂のロックをぶつけろ！
DBは奥が深いですね。
お疲れ様です。
