エラー内容
DBテーブルの大量レコードを削除するバッチ実行中に、同じDBへ読み書きするAPIリクエストを継続的に投げると、以下の問題が発生しました。
- 性能劣化：一部リクエストで20〜30秒の遅延
- エラー発生：一部リクエストでタイムアウト
{ "message": "Endpoint request timed out" }
原因
削除バッチによる長時間のロック保持により、APIリクエストがロック待ちとなりタイムアウトしていました。
主な要因は以下の通りです。
① ギャップロック（Next-Key Lock）
MySQLのデフォルト分離レベルである REPEATABLE READ では、
削除対象の行だけでなく「インデックスの範囲」もロックされます。
今回のSQL：
DELETE FROM table WHERE create_date < ten_minutes_ago;
このような 範囲条件（<, >, BETWEENなど） は、
インデックスを利用する場合にギャップロックが発生しやすく、
広範囲のロックにつながります。
② 長時間トランザクション
DELETEを1トランザクションで大量に実行していたため、
- トランザクションが長時間継続
- ロックが解放されない
という状態になっていました。
※ ロックは「テーブル単位」ではなく「トランザクション単位」で保持されます。
③ 待ち行列の発生
（高負荷時に）APIリクエストがロック待ちとなり、
- リクエストが詰まる
- タイムアウトが連鎖
という典型的な「スローダウン → 崩壊」パターンに陥っていました。
対応内容
対応①：トランザクション分離レベルの変更
削除バッチのセッションで、分離レベルを READ COMMITTED に変更しました。
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
効果は以下の通りです。
- ギャップロック（Next-Key Lock）が抑制される
- 削除対象行のみロックされるようになる
- APIリクエストへの影響が軽減される
ただし、注意点として、READ COMMITTEDでは以下の特性があります。
- 他トランザクションのコミット結果が見える
- 同一トランザクション内でも読み取り結果が変わる可能性がある（ファジーリード）
ただし今回のバッチでは
- 単方向処理（DELETE → COMMIT）
- 同じデータを複数回読むことがない
ため、実用上問題ないと判断しました。
また、この設定はセッション単位のため、API側の挙動には影響しません。
トランザクション分離レベルの詳細は、下記をご参照ください。
対応②：トランザクションの分割（COMMITの細分化）
以下のように、テーブル単位でCOMMITするように変更しました。
DELETE FROM table1 WHERE create_date < ten_minutes_ago;
COMMIT; -- ここでロック解放
DELETE FROM table2 WHERE create_date < ten_minutes_ago;
COMMIT; -- ここでロック解放
これにより、以下の効果が得られます。
- トランザクション時間の短縮
- ロック保持時間の短縮
- COMMITごとにロックが解放されるため、待機中リクエストが処理可能になる
まとめ
上記対応により、エラーや性能劣化が解消されたことを確認できました。
ただし、大量DELETEはそのまま流さず、分割して実行することも大事です。
今回は上記の対応としましたが、そのような方策が可能であれば検討すべきでしょう。
以上！