MySQLでINSERTのデッドロックに嵌る人を1人でも減らすために
この記事ははてなデベロッパーアドベントカレンダー2015の12月24日の記事です。
昨日は 
id:stefafafan さんのエンジニアと英語でした。
こんにちは、こんばんは。
クリスマス・イヴですね、皆さんはどのような一日を過ごされる(た)のでしょうか。
僕は一人です。
改めまして、先日初めての合コンを経験/失敗して二度と行かないと誓った はてなの 
id:ichirin2501 です。今回は小ネタとしてMySQL(InnoDB)のBULK INSERTにおけるデッドロックの話をしようと思います。ただ、外部キー制約が絡むと複雑になるので今回は触れません。それについてはこちらを参照ください。
あ、タイトルはオマージュです*1。
Topic
- 検証環境
- INSERTのデッドロック
- 避けられないケース
- もしくはロックする
- リトライ処理に注意
- 初期データ
- Duplicateの場合
- Deadlockの場合
- まとめ
INSERTのデッドロック
INSERT文によるデッドロックは、殆どBULK INSERTでデータの挿入順が原因です。
テーブル定義
CREATE TABLE `player` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `money` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `player_idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
例えば各トランザクションで同時に以下のようなクエリが発行されたときにデッドロックになる可能性があります。
A> INSERT INTO player (name) VALUES("a"),("b"),("c"),....,("z"); B> INSERT INTO player (name) VALUES("z"),("y"),("x"),....,("a");
2つのトランザクションで各クエリだけで順番に手動で実行してもデッドロックを再現させることは難しいでしょう。再現させるためにはプログラムで高速に並列実行させればデッドロックになります。よくデッドロックの例としてあるのはトランザクション中に各クエリ単位で獲得するロックの交差によるものです。こちらは実行時間は関係なく、手動で再現するのは簡単です。今回の例はクエリの実行中に発生するロックの交差が原因なので、INSERT文を打って応答が返ってきてはもう遅いのです。BEGIN-COMMITによる明示的なトランザクション関係なく発生するデッドロックということでもあります。
何故デッドロックが発生するのかを簡単に説明します...というか、見た通りという感じですが、INSERTのVALUESは記述順に実行されます。InnoDBは全ての行が生成出来てから全部のロックを獲得するのではなく、生成しながらロックを獲得します。つまり、nameカラムにユニーク制約があるため、a,b,c,...と昇順にロックを取るのと、z,x,y...と降順にロックを取るのが同時に走ればユニーク制約でデッドロックするのは明らかです。もちろん、nameカラムにユニーク制約がなければデッドロックにはならずに済みます。ちなみにSELECT FOR UPDATE文でIN句などを条件に用いたときの記述順は関係ありません。InnoDB側が実行した走査順に依存します。だいたい使用されたインデックスの昇順になるので、WHERE IN()の中身の順序自体が原因でデッドロックになることはありません。
解決方法は、ソートすることです。今回のテーブル定義では、nameカラムでソートしてBULK INSERTすれば同時に実行されてもデッドロックはなくなります。あと INSERT IGNORE なら大丈夫...なんてことはないです。クエリ実行中はロック獲得済み状態なので、排他ロックされてる行に対して共有ロックを実行しても待たされるので同じ結果になります。
避けられないケース
うっ、頭がいたい...。テーブル定義でユニーク性が求められるカラムが主キーのみであれば主キーをソートすれば良い。また、主キーがAUTO_INCREMENTでユニーク制約なカラムが一つしかない場合はユニーク制約なカラムでソートすれば良い...ですが、基本的にはユニーク性のカラムが2つ以上ある場合はBULK INSERTによるデッドロックは避けられません。
テーブル定義
CREATE TABLE `player` ( `id` bigint(20) unsigned NOT NULL, `name` varchar(20) NOT NULL, `money` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `player_idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
AUTO_INCREMENTをはずしました。アプリ実装的にはidは別途生成してINSERT時に渡す仕様に変更しただけです。ここでは id,nameはどちらも衝突する可能性があるものとします。この場合、ソートしてもデータ次第でデッドロックが避けられないのです。
仮にVALUESを(id,name)でソートしたINSERTが偶然以下のようなものだとします。
A> INSERT INTO player (id,name) VALUES(1,"c"),(2,"d"),(3,"e")...; B> INSERT INTO player (id,name) VALUES(2,"a"),(3,"b"),(4,"c")...;
とっぴろきー！
同時に実行されたと仮定すると、A側で(2,"d")を生成しようとしたとき、既にB側でid=2はロック獲得済みなので待たされてしまいます。B側は(3,"b")の生成が終わった後、(4,"c")の生成をしようとして、name="c"はA側で生成/ロック獲得済みなのでここでデッドロックになります。ユニーク制約のあるカラム同士がなんらかの条件を元に範囲が決まっていればあるいは...という感じですが、それだと独立してカラム定義する価値が失われてしまいます。なので、前提としてどうしても避けられない組み合わせが存在します。現実的には、2つのカラムのうち片方はほとんど衝突しないからもう片方を最優先にソートすれば良い、などでデッドロックはほぼなくなったりします。上記の例は極端で、困るケースはほとんどないと思います。
リトライ処理に注意
結論から言うと、クエリでエラーになったら即座にリクエスト全体をエラーとして扱うほうが良いです。とは言え、INSERT処理では成功するまでリトライ処理を書いてしまいがちかと思います。罠があるのでご紹介します。例えば以下のような疑似コードがだめな例になります。
BEGIN
UPDATE hogehoge
# uuidが衝突しても最大10回までリトライする
for cnt in 1..10 {
uuid = get_uuid()
ok = 0
try {
INSERT INTO piyopiyo
ok = 1
} catch {
warn "error retry cnt"
}
if ok then break
}
COMMIT何故だめかと言うと、INSERTのエラー内容次第でトランザクションが継続orロールバックの差があるためです。
上記のような疑似コードではエラーを捕まえた後、内容を見てリトライ処理の粒度を考慮する必要があります。Duplicateエラーの場合は本来の期待された動作をするでしょう。しかし、INSERT時にDeadlockエラーであれば最初に成功したUPDATE文も含めてロールバックされてしまいます。リトライで成功しても情報は一部失われることに、悲しい。
簡単に確認してみましょう。
初期データ
CREATE TABLE `player` ( `id` bigint(20) unsigned NOT NULL AUTO_INCREMENT, `name` varchar(20) NOT NULL, `money` bigint(20) unsigned NOT NULL DEFAULT '0', PRIMARY KEY (`id`), UNIQUE KEY `player_idx_name` (`name`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; SELECT * FROM player; +----+---------+-------+ | id | name | money | +----+---------+-------+ | 1 | ichirin | 0 | | 2 | hatena | 0 | | 3 | beer | 0 | | 4 | sushi | 0 | +----+---------+-------+ 4 rows in set (0.00 sec)
Duplicateの場合
単純に既にある要素を含めたINSERT文を発行します。
BEGIN; SELECT * FROM player WHERE name = "sushi"; +----+-------+-------+ | id | name | money | +----+-------+-------+ | 4 | sushi | 0 | +----+-------+-------+ 1 row in set (0.00 sec) UPDATE player SET money = money + 1 WHERE name = "sushi"; SELECT * FROM player WHERE name = "sushi"; +----+-------+-------+ | id | name | money | +----+-------+-------+ | 4 | sushi | 1 | +----+-------+-------+ 1 row in set (0.00 sec) INSERT INTO player (name) VALUES("2501"), ("ichirin"); ERROR 1062 (23000): Duplicate entry 'ichirin' for key 'player_idx_name' COMMIT; SELECT * FROM player; +----+---------+-------+ | id | name | money | +----+---------+-------+ | 1 | ichirin | 0 | | 2 | hatena | 0 | | 3 | beer | 0 | | 4 | sushi | 1 | +----+---------+-------+ 4 rows in set (0.00 sec)
Deadlockの場合
実際はインデックス作成に刺さるときもあれば、要素がソートされてなくてINSERT文同士でデッドロックするケースが殆どだと思います。今回手動で再現するにあたり、INSERT文をデッドロックさせるためにはインデックス作成をロックさせるのが手頃なので、少し強引にしています。A,B,Cはそれぞれ異なるトランザクションで実行されているのを意味します。
A> BEGIN; B> BEGIN; C> BEGIN; A> SELECT * FROM player WHERE name = "sushi"; +----+-------+-------+ | id | name | money | +----+-------+-------+ | 4 | sushi | 0 | +----+-------+-------+ 1 row in set (0.00 sec) A> UPDATE player SET money = money + 1 WHERE name = "sushi"; A> SELECT * FROM player WHERE name = "sushi"; +----+-------+-------+ | id | name | money | +----+-------+-------+ | 4 | sushi | 1 | +----+-------+-------+ 1 row in set (0.00 sec) C> INSERT INTO player (name) VALUES("b"); B> INSERT INTO player (id,name) VALUES(100,"a"),(101,"b"),(102,"c"); A> INSERT INTO player (id,name) VALUES(102,"a"),(101,"b"),(100,"c"); C> ROLLBACK; A> # Aがエラーになる ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction A> COMMIT; A> SELECT * FROM player; +----+---------+-------+ | id | name | money | +----+---------+-------+ | 1 | ichirin | 0 | | 2 | hatena | 0 | | 3 | beer | 0 | | 4 | sushi | 0 | +----+---------+-------+ 4 rows in set (0.00 sec)
このようにエラーの種類によって直後の状態が異なってきます。デッドロックor重複エラーとか依存した処理は書きたくありません。エラーになったら即座にリクエスト全体をエラーとして扱うほうが無難です。
まとめ
- BULK INSERTはソートしよう
- 避けられないケースは存在するので基本はエラー前提で設計しよう
- INSERTで内部リトライ処理を書くときはトランザクションの粒度に気をつけよう
はてなでは、ミドルウェアと友達になれるエンジニアを募集しています。
hatenacorp.jp
明日のアドベントカレンダーは 
id:stanaka の担当です。ご期待ください。
