急成長でぶつかったMySQLの罠とその向き合い方

Transcript

1. 徳富 博（プラットフォームエンジニアリングチーム） 急成長でぶつかったMySQLの罠と その向き合い方 @yannKazu1 ふかぼりSRE
2. 自己紹介
3. 自己紹介 徳富 博 - 所属: タイミー プラットフォームエンジニアリング チーム - 好きな技術: Go, Ruby, TypeScript, AWS, GCP… - 野菜を育ててます
4. 今日お話しすること このセッションの位置づけ 以前、アドベントカレンダーで Aurora MySQL の運用ナレッジについてのブログ (https://tech.timee.co.jp/entry/2025/12/03/104738） を書きました。今日はその内容をベースにして、どう向き合っているかについてご紹介できればと思います。 なお、弊社では Aurora MySQL を使っているので、その前提でお聞きいただけると助かります！ (※ 分離レベ ルは MySQL デフォルトの REPEATABLE READ 前提です）
5. 急成長の文脈 🚀 プロダクトの成長によるアクセス数の急激な増加 急成長によりトラフィックが急増。今まで問題として認知されていなかった部分が 顕著なボトルネックとして表面化するようになった 📦 プロダクトの成長によるデータ量の急激な増加 レコード数が膨れ上がり、以前は一瞬で終わっていたクエリの処理速度が 目に見えて低下。インデックス設計の重要性が一気に増した 👥 開発者増加による Migration頻度の増加 開発チームの規模が大きくなるにつれてスキーマ変更・カラム追加の頻度が急増 ALTER TABLE に伴うロックの影響が無視できなくなってきた
6. 本日のアジェンダ 01 01 罠①：DDL実行時の落とし穴 「安全なはず」が止まる・落ちる 02 罠②：Drop Table中にに MDLによるデッドロックが大量発生する しかも SHOW ENGINE INNODB STATUS には出てこない 03 罠③：レプリカの重いクエリが Writerに影響する Undo ログと RollbackSegmentHistoryListLength 04 罠④：同時リクエストによるデッドロック ギャップロック・ロック昇格 05 罠⑤：意外に広いロック範囲 UPDATEのネクストキーロック・外部キーのSロック 06 罠⑥：急成長でじわじわ悪化するスロークエリ リリース時は無害だったクエリが、データ増加×トラフィック増加で詰まる 07 罠⑦：急成長で Buffer Poolが足りなくなる キャッシュが効かなくなりRead I/Oが急増する 08 まとめ：どう向き合うか 改善策と運用プラクティス
7. CHAPTER 02 罠①：DDL 実行時の落とし穴 「安全なはず」が止まる・落ちる
8. この章で話すこと 1 メタデータロックに注意 ALTER は止まらない？ INPLACE でも ALTER の開始・完了時に MDL（排他ロック）は必ず発生する。 その間、後続の SELECT / UPDATE は MDL 待ちキューに詰まる。 Aurora レプリカの挙動 Aurora レプリカは MDL 待ちが溜まらず、待機中の SELECT がエラーで落ちる。 → lost connection をリトライできる仕組みが必要。 2 外部キー制約に注意 FK 追加 = COPY 強制 foreign_key_checks=1（デフォルト）のまま外部キーを追加すると COPY アルゴリズムが強制される。 COPY = テーブル全コピー ＋ その間 INSERT / UPDATE / DELETE が完全停止。 対策 ridgepole / migration 実行前に SET SESSION foreign_key_checks = 0 を先に実行。 これだけで INPLACE になり、DML を止めずにオンライン実行できる。
9. 1: メタデータロックに注意
10. 「オンラインDDLなら安全」という誤解 アルゴリズムが何であれ、MDL は必ず存在する ✗ よくある誤解 「オンラインDDLなら日中でも 止まらず特に何も気にせずALTERできる」 「ロックはゼロ」 ✅ 実際には オンラインDDLはデータ再構築をバックグラウンドで行うが ALTER で排他MDLを取得するタイミングがある → その間の SELECT / UPDATE はMDL待ちキューに詰まる ALTER のアルゴリズムによって MDL 取得タイミング・条件は変わる ALGORITHM=COPY 新テーブルへの全コピー方式。 開始から完了まで継続して排他MDLに近 い強いロックを保持し、DMLも基本的にブ ロックされる。 ロック時間が最も長くなる。 ALGORITHM=INPLACE ① 初期化フェーズ: 共有アップグレード可能 MDLを取得 ② 実行フェーズ: DMLと並走。必要な操作は 実行準備中に短時間取得 ③ 最終フェーズ (commit table definition): 排 他MDLに昇格し常に取得 ALGORITHM=INSTANT データディクショナリのメタデータのみ変 更。 準備・実行中は排他MDLを取らないが、 最終フェーズ (commit table definition phase) に排他MDL取得 重要: アルゴリズムに関わらず MDL は必ず発生する。「オンラインDDL = ゼロロック」は誤り。 ※ docs: dev.mysql.com/doc/refman/8.0/en/innodb-online-ddl-performance.html
11. メタデータロックによって引き起こされる事故 テーブル定義を守る “共有” と “排他” の綱引きが、詰まりの原因 ① MDLの2種類 共有MDL（Shared） みんなが同時に持てる「入室バッジ」 • SELECT / UPDATE / INSERT / DELETE 実行時 に自動取得 • 共有MDL同士は競合しない（並行OK） 排他MDL（Exclusive） 1人しか持てない「貸切マスターキー」 • ALTER TABLE / DROP TABLE 実行時に必要 • 既存の共有MDLが全部外れるまで取得できない • 取得後は他のMDLを全ブロック ② なぜ「詰まる」のか？ — 時系列で見る 1 T0 バッチ処理が走り始める（共有MDL取得） ロングトランザクションが SELECT を実行 → 共有MDLを握ったまま長時間保持 2 T1 ALTER TABLE が排他MDLを要求 → 待機へ 既存の共有MDLが解放されるまで取得できず、ALTER自身がキューで待たされる 3 T2 後続の SELECT/UPDATE もまとめて待機 ALTERの後ろに並んだクエリは共有MDLすら取れず全停止 → APIタイムアウトが大量発 生 覚えておくこと： ロングトランザクション中にALTERが走ると、そのテーブルへのクエリは “全停止” する。対処は「長時間トランザクションを避ける」＋「ALTERは閑散 時に」。
12. Aurora リードレプリカ特有の挙動 Vanilla MySQLとは別の動きをする Vanilla MySQL（RDS MySQL含む） Aurora MySQL DDL反映方法 SQLスレッドで順次適用 クラスターボリューム共有 → ほぼ即時反映 リードレプリカの挙動 Waiting for table metadata lock が溜まって待機 実行中のクエリが強制終了される （Lost connection...） ロック待ち レプリカ側でMDL待ちが ズラッと並ぶ MDL待ちは溜まらない が単発エラーが発生
13. Aurora レプリカの lost connection への対応 MDL発生時にSELECTがエラーになるなら、再実行できるようにする Aurora MDL時の挙動 ALTER TABLE 実行タイミングで Readレプリカで実行中のSELECTが 「Lost connection to MySQL server during query」エラーで落ちる 弊社の対応方針 Rails の7.1から導入された自動リトライ機能を活用し、 lost connection エラー発生時に 一定回数クエリを自動リトライするよう設定。 (リトライするかは一定の 条件あり) ALTER 完了後にリトライが成功するため アプリ側にはリカバリされる。
14. 22: 外部キー制約追加時に注意
15. 外部キー追加の罠①： COPY アルゴリズムになる 🚨 「インデックス追加と同じ感覚で FOREIGN KEY を追加したら、テーブルコピーが走ってしまった」 外部キー制約追加時のアルゴリズム ❌ foreign_key_checks = 1（デフォルト） ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); → ALGORITHM=COPY が強制される その間 ordersテーブルに対するINSERT / UPDATE / DELETE がすべ てブロック（SELECT は可能） ✅ foreign_key_checks = 0 を先にセット SET SESSION foreign_key_checks = 0; ALTER TABLE orders ADD CONSTRAINT fk_user FOREIGN KEY (user_id) REFERENCES users(id); → ALGORITHM=INPLACE になる DML を止めずにオンラインで実行できる テーブルコピーが走らない 📖 MySQL 公式ドキュメントより： 「外部キー制約の追加: INPLACE アルゴリズムは foreign_key_checks が無効な場合にサポートされます。それ以外 の場合は COPY アルゴリズムのみがサポートされます」 ⚠ 注意: foreign_key_checks=0 にすると制約チェックをスキップするため、整合性違反データが混入するリスクがある。実行前にデータ整合性が保 たれていることを確認すること。
16. 外部キー追加の罠②：参照先テーブルにも S ロックがか かる Migration 中に別テーブルへの更新 API が突然タイムアウトする なぜ起きるか：外部キーを追加する際、 InnoDBは子テーブル（orders）の既存データが参照先（ users）に存在するかの整合性チェックを 行う。このチェック中、参照先テーブル（ users）に共有ロック（Sロック）がかかり続け、UPDATE/INSERT/DELETE がブロックされる。 テーブル構成 users（親テーブル） id PK name email FK → orders（子テーブル・ FK追加対象） id PK user_id ← FK追加 amount Migration 中のロック状態 ⚠ 盲点は「参照先テーブル」 users（参照先） → 整合性チェック中ずっと S ロック → UPDATE / INSERT / DELETE が待たされる → Lock wait timeout が発生 ※ orders 側は INPLACE なら DML 可能 Migration 中に何が起きるか ① Migration 開始 ALTER TABLE orders ADD FOREIGN KEY ... が実行される COPY 開始 ② 整合性チェック InnoDB が orders の全行を走査し users.id の存在を確認 → この間ずっと users に S ロックをか け続ける S Lock ③ ユーザー退会 API が呼ばれる UPDATE users SET deleted_at=NOW() WHERE id=123 ← X ロックが必要だが S ロックと競合 → Lock wait timeout exceeded TIMEOUT ✅ 対策： foreign_key_checks=0 で INPLACE にすれば子テーブルの COPY が走らず、参照先テーブルへの S ロックも発生しない。
17. CHAPTER 02 罠②：外部キーを持つテーブルへの DDL操作 がMDLによるデッドロックを引き起こす 「安全なはず」が止まる・落ちる・見えないデッドロックになる
18. 前提知識: MySQLのレイヤー構造を理解する 「サーバーコア」と「InnoDB」はどう違う？ 🔷 MySQL サーバーレイヤー（ SQL Layer） ・SQL パーサー ・クエリオプティマイザー ・メタデータロック（MDL）管理 ・スレッド/接続管理 ・レプリケーション制御 🟢 InnoDB ストレージレイヤー バッファプール : データ・インデックスのメモリキャッシュ 行レベルロック (InnoDB): レコードロック・ギャップロック・ネクスト キーロック MVCC / Undo ログ: 複数バージョン同時実行制御 ・RollbackSegment トランザクション管理 : ACID保証、デッドロック検出 <= よく発生す るデッドロックはこっち 一般的にイメージするデッドロックはストレージレイヤーのものですが今回対象としているのはサーバーレ イヤーのデッドロック
19. SHOW ENGINE INNODB STATUSに出ないデッドロック Migrationでデッドロックが起きたのに Datadogに何も来ない… 🚨 起きたこと Migration(Drop Table) 実行中にデッドロックが大量発生。 通常ならデッドロックが発生した場合 Aurora → CloudWatch → firehose → Datadog にロックモニター情報が届くはずが、今回は何も来 なかった。 CloudWatch の生ログをたどってもロックモニター自体が出力されておらず、 Aurora 側でそもそも検知されていない状態だった。 なぜ検知されなかったのか InnoDB ストレージエンジン層のロック （検知できる） InnoDB ストレージエンジン層のロック競合を検知する 対象：行ロック / ギャップロック / ネクストキーロック （SHOW ENGINE INNODB STATUS / innodb_print_all_deadlocks） MDL(サーバーコア ) による デッドロック（検知できない） MDL（メタデータロック）はサーバーレイヤーで管理される → InnoDB ロックモニターの対象外 → Datadog / CloudWatch に何も出ない 💡 「ログが来ない = デッドロックじゃない」は誤り。 MDLデッドロックはロックモニターに出てこない。
20. MDLによるデッドロックの発生メカニズム 「DROP TABLE 中間テーブル」が親テーブルの排他 MDLを取りにいく orders 親テーブル customers 親テーブル customers_orders 子テーブル（FK あり） タイムライン＆ロック状態 時刻 セッション① アプリ セッション② Migration orders MDL状態 customers MDL状態 T1 START TX; SELECT * FROM orders; ー> ordersに共有MDL S (①) なし T2 DROP TABLE customers_orders; → customers_orders 排他MDL ✅ → customers 排他MDL ✅ → orders 排他MDL …待機⏸ S(①) ↑X(②)待 X (②) T3 SELECT * FROM customers; → customers 共有MDL …待機⏸ （②が排他MDL保持中） orders の排他MDL 待機中… S(①) ↑X(②)待 X(②) ↑S(①)待 T3 時点：①は customers 待ち ／ ②は orders 待ち　⇄ 相互待機 → デッドロック ❌
21. MDLデッドロックの再発防止策 問題のあるマイグレーション -- 外部キー制約がある状態でいきなり DROP DROP TABLE customers_orders; -- → orders, customers の排他MDL競合が起きる → 改善 改善後のマイグレーション -- 先に外部キー制約を削除してから DROP ALTER TABLE customers_orders DROP FOREIGN KEY fk_customers_orders_order_id; ALTER TABLE customers_orders DROP FOREIGN KEY fk_customers_orders_customer_id; DROP TABLE customers_orders; -- → 排他MDLの競合対象が最小化される 学んだこと 1 サーバーコア管理の MDL: InnoDB ロックモニターでは検知されない。Performance Schema の metadata_locks テーブルで確認 可能 2 外部キーはロック範囲を広げる : DROP TABLE 前に外部キー制約を削除しておくことで競合リスクを大幅低減(CREATE TABLEも 同様の事象が発生します) 3 Migration前の確認を徹底する : 本番環境では参照先テーブルへのDMLが走っていないことを確認してから実行
22. CHAPTER 03 罠③：Auroraの場合レプリカの重いクエリ がWriterに影響する Undo ログと RollbackSegmentHistoryListLength
23. MySQL と Aurora MySQL は別物 レプリケーションの仕組みが根本的に違う 通常の MySQL binlog ベースの論理レプリケーション Source （Primary） Replica binlog → I/O スレッド → リレーログ → SQL スレッド適用 Source 専用 独立したストレージ Replica 専用 独立したストレージ ストレージは完全に独立 。Source で書き込んだデータを binlog として送 り、Replica が自分のストレージに書き直す Undo ログも独立 。Replica 上の長時間クエリが Source のパージをブ ロックしない Aurora クラスターボリューム共有アーキテクチャ Writer （Primary） Reader （Replica） REDO ログをメモリに非同期適用（バッファプール更新のみ） 共有クラスターボリューム データ本体・ Undo ログ・REDO ログを Writer / Reader が共有 ストレージは共有 。常に最新。binlog でのレプリケーションは不要 Reader はバッファプールだけ更新 。Writer の REDO ログを受け取ってメ モリに反映 → 適用が追いつく時間がレプリカラグ 1 / 2
24. なぜ Reader のクエリで Writer が遅くなったのか Reader で動いた Redash の長時間トランザクションが、共有ボリューム経由で Writer に波及 Redash → Reader → 共有ボリューム → Writer Redash 数時間の集計クエリ （長時間トランザクション） Writer （本番ワークロード） クエリ実行 Reader （Replica） 共有クラスターボリューム Undo ログがパージできず蓄積 RollbackSegmentHistoryListLength ↑ スナップショット保持 波及 何が連鎖したか STEP 1 Redash が Reader で長時間クエリ実行 数時間の集計クエリが Reader 上で長時間トランザクションとして居座り、読み 取り一貫性スナップショットを要求し続ける ↓ STEP 2 共有ボリュームの Undo ログがパージ不可 Reader のスナップショットが参照中の古い行バージョンを削除できず、共有ボ リューム上に蓄積 → RollbackSegmentHistoryListLength が増加 ↓ STEP 3 Writer の更新処理が重くなる Writer が行を更新・参照するたび、膨らんだ履歴リストを辿る必要が出てくる （MVCC のコスト増大） ↓ STEP 4 本番の Writer クエリまで劣化 Reader で動かしていたはずの Redash クエリが、共有ボリュームを介して Writer 側の本番ワークロードを巻き込んで遅延 2 / 2
25. 対策：トランザクション分離レベルを活用する 集計クエリには(分析基盤では) 分離レベルをREAD COMMITTED を設定することで Undo の肥大化を抑制できる -- セッション単位でトランザクション分離レベルを下げる SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED; -- その後、集計クエリを実行 SELECT SUM(amount) FROM orders WHERE ... トレードオフの整理 分離レベル Non-repeatable read Phantom read Undoログ蓄積 REPEATABLE READ（デ フォルト） 防ぐ 防ぐ（製品によるがMySQLの 場合は防ぐ） 多い ⚠ READ COMMITTED（集計 向け） 起きうる 起きうる 少ない ✅ 💡 集計系はリアルタイム整合性より安定稼働が重要。 READ COMMITTED で十分なケースがほとんど。
26. 前ページ補足 [補足説明]なぜ Reader の長時間クエリで Undo ログがパージ不可になるのか MVCC / Read View / Undo ログ の観点から ─ 対策の前提を揃える 01 行の更新の実体 02 MVCC 03 Undo ログ 04 Read View 05 長時間 TX の罠
27. STEP 1 そもそも「行を更新する」と何が起きるのか 1 つの行は「複数の過去」を抱えている ─ どこに?いつ消える? STEP 1 身近な例:銀行口座 amount の更新履歴 初回入金 trx_id = 310 amount = 200 2 回目の更新 trx_id = 420 amount = 250 最新の更新(今テーブル上にある値) trx_id = 500 amount = 300 ↑ 「過去の値」はどこに消えた? 実はまだ残っている。 ここで生まれる 2 つの疑問 Q1 更新中の行を別の人が読みたいとき、ロックを取らずにどう 読む? → 答え:MVCC で複数版を持ち、その人に見せてよい版を返す。 (STEP 2) 読み手側がロック待ちにならず、更新もブロックしない。 Q2 過去の値はどこに残り、いつ消える? → 答え:Undo ログに残る。不要になれば purge される。(STEP 3) 「不要」の判定を誰がするか ─ ここが後で重要になる。
28. STEP 2 MVCC とは ─ ロックを取らずに読むための仕組み Multi-Version Concurrency Control / 「行に複数の版を持たせる」発想 もし MVCC がなかったら A さん 行を更新中 (ロックを取得) B さん 同じ行を読みたいだけ → B さんは A の更新が終わるまで待たされる 読むだけなのに遅延する。同時実行性が著しく低い。 これだと遅い。だったら ─ 「読むときはロック取らずに、ちょっと前の版を見せちゃおう」 この発想こそが MVCC (Multi-Version Concurrency Control)。 MVCC のイメージ:1つの行 = 複数の版 最新版(テーブル上にある行) trx_id = 500, amount = 300 1 つ前の版(Undo から再構築) trx_id = 420, amount = 250 さらに前の版(Undo から再構築) trx_id = 310, amount = 200 読み手ごとに「見える版」を切り替える → ロック不要で読める
29. STEP 3 Undo ログとは ─ 過去の版を再構築する「戻し方メモ」 テーブルには最新版しかない。過去は Undo ログから逆算して復元する 1 行のデータの実際の姿 テーブル上(最新版) trx_id = 500 amount = 300 ↓ 逆方向にたどると過去が再構築できる Undo ログ(変更前の値のメモ) 1 つ前:amount を 250 → 300 に変えた (戻すなら 250) 2 つ前:amount を 200 → 250 に変えた (戻すなら 200) Undo = 「戻し方レシピ」。順に適用すれば過去の版が復元できる。 Undo ログの 2 つの役割 ① ロールバック用 トランザクションが失敗したら、変更前の値に戻す。 名前の「Undo」はここから。 ② 過去バージョンの再構築 ← 本題! 最新行 + Undo を逆適用 → 過去時点の行を復元。 これこそが MVCC で「ロックなしに過去を読む」正体。 では「どの版がまだ必要か?」 を判定するのは誰? → それが Read View (STEP 4)
30. STEP 4 Read View とは ─「見てよい世界」の境界線 TX が持つスナップショット。どの trx_id までが自分に見えるかを決める 例えるなら:入り口でパシャッと撮った「見てよい世界」の集合写真 TX は Read View を作った時点で「そこまでにコミット済みの変更」を記録し、以降はその基準で読む。 行を読むとき、行の trx_id が基準より新しければ Undo を逆適用して古い版に戻す。 ① 最新版を読む trx_id = 500 自分の Read View より新しい? (= 見てはいけない値?) → ② Undo を逆適用 amount: 300 → 250 → 200 過去の版を再構築する → ③ Read View に合う版を返す trx_id = 310 amount = 200 ← この版を読み手に返す ここが肝:Read View が「いつ作られるか」が、どの Undo を残すかを決める Read View が長時間固定されると、古い行バージョンは「まだ必要」と判定され続け、Undo が消せなくなる。
31. STEP 5 Read View は「いつ」作られる?─ 分離レベルの違い REPEATABLE READ は TX 開始時に 1 回、READ COMMITTED はクエリごとに作り直す REPEATABLE READ (MySQL のデフォルト) TX 開始 TX 終了 📷 Read View は 1 回だけ作成 → 最後まで使い回す (固定) 共有ボリューム上の Undo ログ Undo ver.5 Undo ver.4 Undo ver.3 Undo ver.2 Undo ver.1 全版が残り続ける purge できない → Undo が肥大し続け、MVCC 参照コストが増加。 READ COMMITTED (集計クエリ向け) TX 開始 TX 終了 📷 📷 📷 📷 📷 クエリごとに Read View を作り直す 共有ボリューム上の Undo ログ Undo ver.2 Undo ver.1 古い版は次々 purge 共有ボリュームが軽い → 代わりに、同じ TX 内で読むたびに値が変わる可能性あり。
32. ここまでを統合 ─ purge が止まる 3 ステップ連鎖 「長時間 TX」→「Read View 固定」→「Undo が捨てられない」 この流れが Writer まで波及する Reader 上の長時間 TX が走っている間、その Read View より古い行バージョンは「まだ必要」と判定され、共有ボリューム上の Undo ログが purge されずに溜まり 続ける。 01 Reader で長時間 TX Redash の集計クエリが Reader 上で数分〜数時間居 座る。その間、Writer 側では本番 UPDATE が活発に 走り続ける。 ▶ 02 Read View が開始時に固定 REPEATABLE READ では開始時の Read View を最 後まで保持。「この Read View より古い版は捨てられ ない」が成立。 ▶ 03 共有ボリュームで Undo 肥大 Writer の更新版が全て残り、 RollbackSegmentHistoryListLength が増加。MVCC 参 照コストが増え、Writer 本番クエリまで劣化。 結論 集計クエリ側のセッションを READ COMMITTED に下げれば、Read View がクエリごとに切り替わり、古い版を掴み続けなくなる → 共有ボリュームの Undo が正しく purge される
33. CHAPTER 04 罠④：同時リクエストによる デッドロック ギャップロック・ロック昇格
34. パターン①：ギャップロック デッドロック パターン②： S→X昇格 デッドロック
35. パターン①：ギャップロックとは何か 「存在しないレコードの隙間」にかかるロック ギャップロック（Gap Lock） ＝ インデックス上の「レコードとレコードの間の隙間」に対してかかるロック SELECT ... FOR UPDATE でレコードが存在しなかった場合、その「検索した範囲」に他のトランザクションが INSERT できないよう封鎖する。 インデックスイメージ（ email カラム） email 1 email 5 email 1 ～ email 5 の「ギャップ」（ email3 など存在しない） ギャップロックの特性 🔍 例えばSELECT FOR UPDATE で検索ヒットしない時などに発生 WHERE email='email3' で検索して行が存在しなければ、 InnoDB はそのギャップ範囲をロックする。「念のため封鎖しておく」動作。 🤝 Gap Lock 同士は競合しない 同じギャップに対して複数のトランザクションが Gap Lock を取れてしまう。「お互いに封鎖できた」状態になる。これが落とし穴。 💥 INSERT は Gap Lock と競合する ギャップ内への INSERT は、そのギャップに Gap Lock を持つどのトランザクションとも競合する。 → 相互ブロックが生まれやすい。
36. パターン①：ギャップロック デッドロックの流れ Gap Lock 同士は取れる → そのまま両方 INSERT しようとする シナリオ：Tx A と Tx B が同時に「email3 を検索して、なければ INSERT する」という処理を実行する Tx A Tx B ① SELECT FOR UPDATE → email3 が存在しない → Gap Lock 取得 SELECT * FROM users WHERE email='email3' FOR UPDATE; -- 存在しない → Gap Lock ✅ Gap Lock SELECT * FROM users WHERE email='email3' FOR UPDATE; -- 存在しない → Gap Lock ✅ Gap Lock 💡 Gap Lock 同士は競合しない → 両方が「取れた」と思っている ② INSERT email3 を試みる → 相手の Gap Lock に阻まれる INSERT INTO users (email) VALUES('email3'); -- Tx B の Gap Lock に阻まれる -- → 待機 ⏸ WAIT INSERT INTO users (email) VALUES('email3'); -- Tx A の Gap Lock に阻まれる -- → 待機 ⏸ WAIT ③ Tx A は Tx B の待ち、Tx B は Tx A の待ち → 相互待機 → デッドロック 💥
37. パターン①：なぜ起きるか・どう防ぐか 「存在チェック → INSERT」という処理パターンが引き起こす なぜ起きるか -- 「なければ INSERT する」という処理を複数スレッドが同時に実行すると … SELECT * FROM users WHERE email='email3' FOR UPDATE; -- 存在しない → Gap Lock INSERT INTO users (email) VALUES ('email3'); -- 相手の Gap Lock に阻まれる → WAIT 対策 ✅ ① INSERT IGNORE または INSERT ... ON DUPLICATE KEY UPDATE 「存在チェックしてから INSERT」という2ステップをやめて、1クエリで 完結させる。 Gap Lock が取れない→そもそも競合しない。 INSERT IGNORE INTO users (email) VALUES ('email3'); -- または INSERT INTO users (email) VALUES ('email3') ON DUPLICATE KEY UPDATE updated_at = NOW(); ✅ ② FOR UPDATE をやめる（存在確認だけなら共有ロック or ロックなし） 「INSERT するかどうか未定」の段階で FOR UPDATE（X ロック）を取 るから Gap Lock が問題になる。 本当に X ロックが必要か設計を見直す。 -- 存在確認だけなら FOR UPDATE は不要 SELECT * FROM users WHERE email='email3'; -- INSERT するときに UNIQUE 制約に任せる
38. パターン②： S→X昇格 デッドロック
39. パターン②： Sロック・Xロックとは何か 「共有ロックは競合しない」が落とし穴になる ロックの種類 S ロック（共有ロック） 取得方法：SELECT ... LOCK IN SHARE MODE 「読み取り中。他が更新するのは待ってほしい」 S ロックを持っている間、他の S ロックは許可される。 ただし X ロックはブロックされる。 X ロック（排他ロック） 取得方法：SELECT ... FOR UPDATE / UPDATE / DELETE 「更新中。誰も触らないでほしい」 X ロックは他のいかなるロック（ S も X も）とも競合する。 X ロックを取るには全ての S ロックが解放される必要がある。 ロック互換性 S ＋ S 競合しない ✅ 両方が同時に読める S ＋ X 競合する ❌ 読んでいる間は更新できない X ＋ X 競合する ❌ 更新中は誰も触れない 💥 落とし穴：「S ロックを持ったまま X ロックに昇格しようとする」と、相手の S ロックが邪魔になる → 相互ブロック
40. パターン②：テーブル構成 reviews（親）← FK — review_activities（子）を同一 TX 内で操作する テーブル構成 reviews（親テーブル） PK id bigint offer_id bigint NOT NULL reviewer_id bigint NOT NULL status varchar 'pending' etc. created_at datetime updated_at datetime review_activities（子テーブル） PK id bigint FK review_id bigint → reviews.id action varchar 'submitted' etc. performed_by_id bigint NOT NULL created_at datetime updated_at datetime
41. パターン②： S→X昇格 デッドロックの流れ FK INSERT で暗黙の S ロック → 同 TX 内で UPDATE → 相互ブロック シナリオ：Tx A・Tx B がほぼ同時に「子テーブルへ INSERT → 親テーブルを UPDATE」という処理を実行する テーブル： reviews（親） ←FK— review_activities（子） Tx A Tx B ① FKがあるため review_activities に INSERT → InnoDB が reviews の参照行に S ロックを自動取得（ S 同士は競合しないので両方成功） INSERT INTO review_activities (review_id, action) VALUES (1, 'submitted'); -- InnoDB: reviews.id=1 に -- S ロック自動取得 ✅ S Lock INSERT INTO review_activities (review_id, action) VALUES (1, 'checked'); -- InnoDB: reviews.id=1 に -- S ロック自動取得 ✅ -- （S同士は競合しない） S Lock 💡 S ロック同士は競合しない → Tx A・Tx B どちらも取得成功 ② reviews を UPDATE → X ロックが必要 → 相手の S ロックに阻まれて待機 UPDATE reviews SET status = 'approved' WHERE id = 1; -- X ロック必要だが -- Tx B の S ロックが邪魔 -- → 待機 ⏸ WAIT UPDATE reviews SET status = 'rejected' WHERE id = 1; -- X ロック必要だが -- Tx A の S ロックが邪魔 -- → 待機 ⏸ WAIT ③ Tx A は Tx B の S ロック待ち、Tx B は Tx A の S ロック待ち → 相互待機 → デッドロック 💥
42. パターン②：なぜ起きるか・どう防ぐか 根本原因 # 同一 TX 内で FK 子テーブルに INSERT してから親テーブルを UPDATE すると… review_activity = ReviewActivity.create!(review_id: id, ...) # → reviews に S ロック（暗黙） review.update!(status: 'approved') # → reviews に X ロックが必要 → 相手の S ロックと競合 対策 ✅ ① UPDATE を先に実行する（処理順の入れ替え） ← 最も シンプル 先に X ロックを取ってしまえば、後続の FK INSERT の S ロック取 得は同じ TX が X を保持しているため成功する。 別の TX から S ロックを取られる前に X を確保できる。 # UPDATE（X ロック）を先に実行 review.update!(status: 'approved') # その後 INSERT → S ロック取得は # 自 TX が X を持っているので成功 ReviewActivity.create!(review_id: id, ...) ✅ ② INSERT 前に SELECT FOR UPDATE で先に X ロックを取る 処理順を変えられない場合の代替手段。 FOR UPDATE で reviews の X ロックを先取りしておくことで、FK INSERT 時の S ロック取得が「昇格」ではなく同一ロックの再確認 になる。 # 先に X ロックを取得 review = Review.lock.find(id) # FOR UPDATE # 以降の INSERT・UPDATE は順番待ちになり # 相互ブロックが発生しない ReviewActivity.create!(review_id: id, ...) review.update!(status: 'approved')
43. デッドロックとの向き合い方
44. AIコードレビューでデッドロックが発生しやすい実装を指摘するように している(シフトレフト )
45. デッドロック発生を即座に検知・分析できる仕組み (シフトライト ) Aurora エラーログ → CloudWatch → Firehose → Datadog 前提設定： Aurora クラスターパラメーターグループで innodb_print_all_deadlocks = 1 を設定する 監視パイプライン Aurora Error Log デッドロック発生時に ロックモニター情報を出力 CloudWatch Logs エラーログを リアルタイム収集 Subscription Filter フィルタリング Kinesis Firehose ログを ストリーム配信 Datadog Logs クエリとロック情報を すぐに確認できる Datadog で確認できること どのクエリとどのクエリでデッドロックしたか ロックモニターに 2 つのトランザクションのクエリが出力される どのロックを持っていて、何を待っていたか HOLDS THE LOCK / WAITING FOR THIS LOCK で保持・待機ロックが確認でき る どちらがロールバックされたか WE ROLL BACK TRANSACTION (N) で被害トランザクションを特定できる いつ・何件発生しているか ログのタイムスタンプと件数でデッドロックの頻度を把握できる
46. ロックモニターの読み方 *** (1) TRANSACTION: ← ① TX番号・どのクエリかを確認 TRANSACTION 421, ACTIVE 0 sec inserting MySQL thread id 10, query id 500 app INSERT INTO users (email,name) VALUES ('email3@example.com','Alice') ← クエリA *** (1) HOLDS THE LOCK(S): ← ② 保持しているロック index idx_email of table 'app'.'users' trx id 421 lock_mode X locks gap before rec ← ギャップロック(X) を保持中 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: ← ③ 待っているロック index idx_email of table 'app'.'users' trx id 421 lock_mode X locks gap before rec insert intention waiting ← INSERT をブロックされている *** (2) TRANSACTION: ← ④ もう一方のTXも同じ構造を確認 TRANSACTION 422, ACTIVE 0 sec inserting MySQL thread id 11, query id 501 app INSERT INTO users (email,name) VALUES ('email3@example.com','Bob') ← クエリB *** (2) HOLDS THE LOCK(S): index idx_email of table 'app'.'users' trx id 422 lock_mode X locks gap before rec ← B も同じギャップロックを保持 *** (2) WAITING FOR THIS LOCK TO BE GRANTED: index idx_email of table 'app'.'users' trx id 422 lock_mode X locks gap before rec insert intention waiting ← A のギャップロックに阻まれている *** WE ROLL BACK TRANSACTION (2) ← ⑤ TX(2)=クエリBがロールバック対象 読み方まとめ : ① どのクエリか → ② 何を持っているか → ③ 何を待っているか → ④ 相手も同じ構造か（＝相互待機） → ⑤ どちらが ロールバックされたか
47. CHAPTER 05 罠⑤：意外に広い ロック範囲 UPDATEのネクストキーロック・外部キーの Sロック
48. パターン①： UPDATE でテーブル全体がロックされる パターン②：外部キー制約で親テーブルに Sロックが伝播す る
49. パターン①： UPDATE でテーブル全体がロックされる 「1行だけ更新したかった」のに … ❌ インデックスなしのUPDATE UPDATE orders SET status = 'shipped' WHERE user_id = 123; -- user_id にインデックスなし！ 何が起きるか ⚠ テーブル全体をスキャンして user_id=123 を探す ⚠ スキャンしたすべての行に排他ネクストキーロック ⚠ 本来1ユーザーだけ更新したいのにテーブル全体が ロック ⚠ 他トランザクションの INSERT/UPDATE/DELETE がほぼ停 止 ✅ 適切なインデックス付き -- インデックスを追加！ CREATE INDEX idx_orders_user_id ON orders(user_id); UPDATE orders SET status = 'shipped' WHERE user_id = 123; -- user_id=123 の行だけロック インデックスがある場合 ✓ インデックスで対象行を特定 → 最小限のスキャン ✓ ロック範囲が劇的に狭まる ✓ 他トランザクションへの影響を最小化
50. パターン②：外部キー制約で親テーブルに Sロックが伝播す る
51. 前提：テーブル構造と外部キー制約 どのテーブルがどうつながっているか テーブル定義（ DDL） -- 親テーブル① CREATE TABLE users ( id INT PRIMARY KEY, name VARCHAR(100), email VARCHAR(255) ); -- 親テーブル② CREATE TABLE groups ( id INT PRIMARY KEY, name VARCHAR(100) ); -- 子テーブル（中間テーブル） CREATE TABLE group_users ( user_id INT NOT NULL, group_id INT NOT NULL, PRIMARY KEY (user_id, group_id), FOREIGN KEY (user_id) REFERENCES users(id), FOREIGN KEY (group_id) REFERENCES groups(id) ); テーブルの関係 users 🔑 id (PK) name email groups 🔑 id (PK) name group_users 🔑 user_id (FK) 🔑 group_id (FK) なぜSロックが親テーブルにかかるのか group_users に INSERT / DELETE すると、InnoDB は 参照整合性チェックのため 参照先の親行を読みにいく。 このとき自動で 共有ロック（Sロック） を取得する。 コードに書かなくても、外部キーがある限り必ず発生する。
52. バッチ処理と APIが競合する group_users の操作が users の UPDATE をブロックする シナリオ : グループ移動バッチ（ group_users を大量 DELETE→INSERT）が動いている間に、ユーザー退会 API（users を UPDATE）が実行され る トランザクション① グループ移動バッチ BEGIN; -- group_users を削除 DELETE FROM group_users WHERE user_id=123, group_id=1; -- → InnoDB が users.id=123 に S ロック自動取得 -- 新グループに追加 INSERT INTO group_users VALUES(123, 2); -- → users.id=123, groups.id=2 に S ロック -- ループ継続（大量件数を COMMITせず処理） -- S ロックを保持し続ける ... トランザクション② ユーザー退会 API BEGIN; UPDATE users SET deleted_at = NOW() WHERE id = 123; -- X ロックを取得したいが -- TX① が users.id=123 の S ロックを -- 保持中 → 待機 -- → Lock wait timeout exceeded -- ユーザー退会が失敗する 🔴 S ロック（共有）は複数取れるが、 X ロック（排他）は S ロックが全部解放されるまで取れない → 退会・更新系 API がすべてブロックされる ✅ 対策: バルク処理を小さいバッチ（例： 100件ごと）に分割して途中で COMMIT → Sロックの保持時間を短縮する
53. CHAPTER 06 罠⑥：急成長でじわじわ悪化する スロークエリ リリース時は無害だったクエリが、データ増加 ×トラフィック増加で詰まる
54. 「リリース時は問題なかった」のに詰まる罠 データ量 × アクセス数の掛け算で顕在化する ❌ 典型的なパターン リリース直後 データ数が少ないのでフルスキャンでも数ms。問題な し。 数ヶ月後 データが数百万件に増加。同じクエリが数秒に。 急成長期 そのエンドポイントへのアクセスも急増。処理が詰まり はじめる。 障害 タイムアウト多発・接続枯渇・DB負荷急上昇。 なぜ気づきにくいか ・ リリース時のレビューでは少量データでテストしており問題が見えな い ・ データ増加は緩やかなため、劣化が少しずつ進み気づくのが遅れ る ・ コード変更なしで突然遅くなるため、原因特定に時間がかかる ・ AIコードレビューでもクエリの実行計画まではチェックできない データ量の増加 × アクセス数の増加 → 「無害だったクエリ」が障害の引き金になる 💡 急成長サービスでは「今動いている」は「将来も動く」を意味しない。データ量を見越したクエリ・インデックス設計が必要。
55. Datadog Database Monitoring で各チームが自律的に改善 できるようにしている 各開発チームがトレースから実行計画まで自力で辿れる仕組み 発見〜改善のフロー トレースで検知 🔍 遅いエンドポイントを トレースで特定 ↓ どのクエリが遅いか → 実行計画を確認 📋 Datadog DB Monitoring で EXPLAIN を確認 ↓ どこにコストがかかるか → AI が仮説を提示 🤖 Datadog の AI 機能が 実行計画を解析し インデックス案を提案 → チームが修正 ✅ 各開発チームが 自律的にインデックスを 追加して改善 Datadog Database Monitoring で何ができるか スロークエリの検出 実行時間・頻度でランキング。問題クエリを一目で特定できる 実行計画の可視化 EXPLAIN の結果をUIで視覚的に確認。 Full Scan / 非効率な結合を検知 AI による改善提案 実行計画をもとに適切なインデックス構成を AI が自動で提案 チームの自律改善 SRE を介さず各開発チームが自分たちのクエリを改善できる
56. せっかくなので実際の Datadog画面をチラ見せします
57. DBMとトレース連携するとトレースから遅いクエリを特定し、その実行計画を確認できる
58. 実行計画がビジュアライズされているので問題箇所が知識がなくても特定し やすい(①でビジュアライズ切り替え可能 )
59. さらにAIによる改善提案も見れる (②をクリックすると見れる )
60. 実行計画の見方を知りたい方はこちらをご覧ください
61. CHAPTER 08 罠⑦：データ増加で Buffer Poolが 足りなくなる 「キャッシュが効いている」はいつまでも続かない
62. InnoDB Buffer Pool とは MySQL の性能を最も左右するメモリ領域 Buffer Pool = テーブルのデータページ・インデックスページをメモリにキャッシュする領域 クエリ実行時、MySQL はまず Buffer Pool を確認する。ヒットすれば Buffer Pool(メモリ)から返す。ミスすればストレージから読み込む buffer poolに保存する。 クエリ実行フロー ① クエリ実行 ② Buffer Pool を確認 → → ✅ ヒット → メモリから返す（高速・低コスト） ❌ ミス → ストレージから読込（低速・ I/O 発生） Aurora での Buffer Pool サイズ innodb_buffer_pool_size = DBInstanceClassMemory × 3/4 インスタンスサイズに応じて自動計算。スケールアップすると Buffer Pool も比例して拡張される。 (調整も可能) 💡 「どの程度のデータを Buffer Poolに載せられているか」 がクエリ性能を大きく左右する
63. 急成長でBuffer Poolが足りなくなる 「ヒットしていたはず」のデータがキャッシュから溢れる 急成長期に起きること 📈 データ量の増加 テーブルの行数・サイズが増え、ワーキングセットが Buffer Pool を超えはじめる。 一度読み込んだデータが追い出され、次のクエリでまたストレージから読み直す。 🗂 インデックスの増加 機能追加でインデックスが増えるたびに Buffer Pool を消費する。 インデックス自体もページとしてキャッシュされるため、無計画な追加は逆効果になりうる。 🐌 結果：毎クエリでストレージから読み直しが発生 → Read I/O 急増 → クエリが急激に遅くなる
64. 監視すべきメトリック Buffer Pool Hit Ratio CloudWatch / Aurora メトリクス キャッシュヒット率。ここが下がるとストレージ読み 取りが急増する Read IOPS / Read Latency CloudWatch / Aurora メトリクス I/O 読み取りの頻度と遅延。 Buffer Pool ミスが増え ると連動して悪化 チューニングのアプローチ インスタンスサイズアップ パラメータチューニングでbuffer poolサイズを増や す Buffer Pool 使用率 Performance Schema 100% 張り付きはワーキングセットが Buffer Poolに 収まりきれていないサイン 不要なindexを削除する
65. CHAPTER 07 まとめ：どう向き合うか 改善策と運用プラクティス
66. SREとしてどう向き合うか レイヤーを意識する サーバーレイヤー（ MDL）なのかInnoDB（行ロック）なのかで観 測方法・対策が全く異なる。「どのレイヤーで起きているか」を 最初に問う習慣を持つ 仕組みを理解してから運用する 「InnoDB バッファプール」「 MVCC/Undoログ」「メタデータロッ ク」の概念を知っているだけでトラブルシューティングの精度 が格段に上がる 高トラフィックは「通常では起きない」競合を日常化 する 理論上起きにくい競合も、リクエスト数が増えれば確率論的 に必ず発生する。設計段階から競合を想定したロック設計・イ ンデックス設計を行う 改善を積み重ねる 単発の対応で終わらせず、ポストモーテム →再現実験→予防 策→モニタリング追加のサイクルを回す。 Aurora運用知識そ のものがプロダクトの継続性に直結する