MySQLでInsert into Selectでデッドロック発生

     

MySQLでSELECT文の結果でINSERT文を発行するようなロジックで意図せぬデッドロックが発生するといったことがあったのでこの備忘録を残します。デッドロック検出機能にて完全停止には至らなかったですが、穏やかではない話です。

気づいたきっかけはバッチ処理にて下記のようなエラーログが検出されたことでした。

Lock wait timeout exceeded; try restarting transaction

デッドロックが発生した処理

デッドロックを引き起こした処理の流れは大まかに下記のようになります。
トランザクションA、トランザクションBともに定周期に実行されるバッチ処理でAとBは同一時間に稼動する可能性があります。
また、各トランザクション内で行ロック(SELECT FOR UPDATE)は実施していません。

①トランザクションAで新しいレコードを追加
②トランザクションBで①の結果が範囲に含まれるような条件でするINSERT INTO SELECT文を発行
③トランザクションAで②の結果に含まれるレコードを削除

デッドロックが発生した原因

ロジック内で行ロックを行っていないのにデッドロックとはいささか納得がいかない節もあったのですが、該当現象が発生するのには下記のような理由があるようです。

MySQLのデフォルトトランザクション分離レベルは「REPEATABLE READ」

トランザクション分離レベル(wikipedia)

OracleやDB2など多くのデータベースのデフォルトは「READ COMMITTED」になっていると思いますが、MySQLのデフォルトトランザクション分離レベルは「REPEATABLE READ」です。トランザクション分離レベルをざっくり説明すると、あるセッションのトランザクションで更新されたデータが、他セッションからのどう見えるかを設定するパラメータ値です。

「REPEATABLE READ」の場合、更新とは別のセッションで一度SELECTしたクエリを再度SELECTすると、そのデータが別なセッションで更新されていたとしても初回にSELECTした値と同じ値を返します。
「READ COMMITTED」の場合、最初のSELECTから次のSELECTの間に別なトランザクションが更新・削除を行いコミットされていれば更新後のデータを読み込みます。

該当現象が発生したときのMySQLのトランザクション分離レベルの設定値は「REPEATABLE READ」でした。

デッドロックとは直接は関係ありませんが、現象付近ログを見ると採番が怪しい雰囲気でしたが絡んでそうなネタです。

MySQLのネクストキーロック

InnoDB のレコード、ギャップ、およびネクストキーロック

MySQLは更新時にファントム行が生まれるのを防ぐために、インデックス行のロックや、ギャップロックと呼ばれる範囲指定での更新や存在しない行への更新時のロックを行うようです(これらをあわせてネクストキーロックと呼ばれるそうです)。
ズバリここがデッドロックを引き起します。

理屈はわかるけど、こっそりロックしてちゃっかりデッドロックしないでよ!って気分になります。
トランザクション分離レベルが「READ COMMITED」の場合ギャップロックは処理しないとあります。

対策

トランザクション分離レベルの変更

トランザクション分離レベルを「READ COMMITTED」にすることで対応します。
また、トランザクション分離レベルが「READ COMMITTED」の場合、バイナリログの設定(binlog format)がSTATEMENTで運用不可の為、バイナリログの保存形式を「MIXED」に変更します。

my.cnf

[mysqld]
transaction-isolation=READ-COMMITTED
binlog_format=0

データ整合性の確保はケースバイケースですので要件にて判断するのがよいと思います。