対象読者：Spring Boot / MySQL を使ったAPIアプリケーション開発者（中級〜上級）
バージョン：MySQL 8.0 / InnoDB
はじめに
MySQLを使ったAPIアプリケーションでは、インデックス設計とロックの理解が、性能と安定性を大きく左右します。
実務では、次のような問題に遭遇することが少なくありません。
-
SELECT ... FOR UPDATEを入れたら、想定より広い範囲がロックされた - 更新対象は数行のつもりだったのに、INSERTまで止まった
- デッドロックが頻発し、APIが不安定になった
- インデックス不足で更新SQLが本番障害の原因になった
こうした問題の多くは、単に「SQLの書き方が悪い」のではなく、InnoDBがどのようにインデックスを持ち、どのようにロックを取得するかを正しく理解していないことから起きます。
この記事では、InnoDBの内部構造から始めて、次の流れで整理します。
- InnoDBのストレージ構造
- Clustered Index / Secondary Index
- B+Treeと検索の仕組み
- Covering Index
- Record Lock / Gap Lock / Next-Key Lock
-
SELECT ... FOR UPDATEの実際のロック範囲 - デッドロックと実務での安全な設計
単なる用語解説ではなく、なぜそのロックが必要なのか、どのようなSQLが危険なのか、実務ではどう設計すべきかまでまとめます。
目次
- MySQL(InnoDB)のストレージ構造
- Clustered Indexの仕組み
- Secondary IndexとDouble Read
- B+Treeと検索アルゴリズム
- Covering Index
- MVCCとLocking Read
- MySQLのロック種類
- Record Lock
- Gap Lock
- Next-Key Lock
- Phantom ReadとNext-Key Lockの関係
-
SELECT FOR UPDATEのロック範囲 - デッドロックの発生原理
- インデックス設計とロック競合
- APIアプリケーションでのロック設計
- 実務で安全な
SELECT FOR UPDATEのパターン - 絶対に避けたい設計・SQL
- まとめ
1. MySQL(InnoDB)のストレージ構造
InnoDBはMySQLの標準的なストレージエンジンであり、ACID準拠、MVCC、行レベルロックを提供します。
まず押さえておきたいのは、InnoDBが「行の集合」をそのまま扱っているわけではなく、ページ単位でデータやインデックスを管理しているという点です。
物理構造の概要
┌──────────────────────────────────────────────────┐
│ InnoDB テーブルスペース │
│ ┌────────────────────────────────────────────┐ │
│ │ .ibd ファイル │ │
│ │ ┌──────────┐ ┌──────────┐ ┌──────────┐ │ │
│ │ │ Page 0 │ │ Page 1 │ │ Page N │ │ │
│ │ │ (16KB) │ │ (16KB) │ │ (16KB) │ │ │
│ │ └──────────┘ └──────────┘ └──────────┘ │ │
│ └────────────────────────────────────────────┘ │
└──────────────────────────────────────────────────┘
- ページサイズ：デフォルト16KB
- エクステント：64ページ単位
- セグメント：インデックスやLOBなどの管理単位
この構造を理解しておくと、なぜインデックスアクセスやページ分割が性能に影響するのかが見えやすくなります。
バッファプール
InnoDBはメモリ上にバッファプールを持ち、頻繁にアクセスされるページをキャッシュします。
実務では innodb_buffer_pool_size の設定が極めて重要です。
一般には「サーバーメモリの70〜80%程度」が目安とされますが、実際にはOSや他プロセスの利用状況も見ながら決める必要があります。
ここで重要なこと
ロックの話に入る前に、この章で押さえるべき本質は次の1点です。
InnoDBはインデックスと行をページ単位で管理しており、SQLの実行は必ずインデックス構造と結び付いている。
つまり、ロックを理解するには、まずインデックス構造を理解する必要があります。
2. Clustered Indexの仕組み
InnoDBの最大の特徴は、テーブルデータそのものがPrimary KeyによるB+Treeとして格納されることです。これをClustered Indexと呼びます。
データ配置の概念図
Clustered Index（B+Tree）
┌────────────────┐
│ Root Page │
│ [10 | 30 | 50] │
└───┬────┬────┬──┘
┌──────────┘ │ └──────────┐
▼ ▼ ▼
┌──────────┐ ┌──────────┐ ┌──────────┐
│ Inner │ │ Inner │ │ Inner │
│[1|5|8|10]│ │[11|20|30]│ │[31|40|50]│
└──┬──┬───┘ └──┬──┬───┘ └──┬──┬───┘
│ │ │ │ │ │
▼ ▼ ▼ ▼ ▼ ▼
[Leaf Pages: 実データ行が格納される]
┌────────────────────────────────┐
│ id=1: {name="Alice", age=25} │
│ id=2: {name="Bob", age=30} │
│ id=5: {name="Carol", age=22} │
└────────────────────────────────┘
※ リーフページは双方向リンクリストで接続
重要ポイント
- PRIMARY KEYが存在しない場合、InnoDBは内部的に
ROW_IDを生成します - データの並びはClustered Indexの順序に従います
- そのため、PKの等値検索や範囲検索は非常に強いです
テーブル定義例
CREATE TABLE orders (
id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,
user_id BIGINT UNSIGNED NOT NULL,
status VARCHAR(20) NOT NULL DEFAULT 'pending',
amount DECIMAL(12, 2) NOT NULL,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (id),
INDEX idx_user_id (user_id),
INDEX idx_status_created (status, created_at)
) ENGINE=InnoDB;
実務で問題になるポイント
Clustered Indexは非常に重要ですが、PRIMARY KEYの設計が悪いと副作用も出ます。
たとえば、連番PKは末尾へのINSERTが集中しやすく、高頻度INSERT時のホットスポットになりえます。
一方、完全ランダムなUUID v4はページ分割を多発させやすく、書き込み効率を落とします。
そのため、実務では次のような検討が必要です。
- 連番AUTO_INCREMENTで良いか
- UUIDを使うなら順序性のあるUUID v7や類似IDを使うか
- Clustered Indexの並びが主要な検索に合っているか
3. Secondary IndexとDouble Read
PRIMARY KEY以外のインデックスはすべてSecondary Indexです。
ここで重要なのは、Secondary Indexのリーフには行全体ではなく、対応するPRIMARY KEYの値が保持されるという点です。
Secondary Indexの構造
Secondary Index: idx_user_id (user_id)
B+Tree のリーフページ
┌─────────────────────────────────┐
│ user_id=101 → PK=5 │
│ user_id=101 → PK=12 │
│ user_id=102 → PK=3 │
│ user_id=103 → PK=8 │
└─────────────────────────────────┘
│
│ PK値を取得後、Clustered Indexを再検索
▼
Clustered Index のリーフページ
┌─────────────────────────────────┐
│ id=3: {user_id=102, amount=...}│
│ id=5: {user_id=101, amount=...}│
│ id=8: {user_id=103, amount=...}│
│ id=12: {user_id=101, amount=...}│
└─────────────────────────────────┘
Double Read（二重読み取り）
Secondary Indexを使って条件に一致するPKを見つけた後、必要な列がインデックス内に揃っていなければ、さらにClustered Indexを引き直します。これがDouble Readです。
SELECT id, user_id, amount, created_at
FROM orders
WHERE user_id = 101;
このクエリでは、
-
idx_user_idでuser_id = 101のエントリを探す - 見つかったPKをもとにClustered Indexを再参照する
という流れになります。
なぜ重要なのか
この追加アクセスは、件数が増えるほどランダムアクセスの増加につながります。
「インデックスを使っているのに遅い」というケースの背景には、Double Readが隠れていることがよくあります。
よくある誤解
「インデックスが効いている = 速い」と思いがちですが、実際には
- インデックスで何件拾うか
- その後に何回Clustered Indexへ戻るか
- 結果件数がどれだけ多いか
で性能は大きく変わります。
4. B+Treeと検索アルゴリズム
InnoDBのインデックスはB+Treeで実装されています。
B+Treeの構造
高さ3のB+Tree（例）
Level 0 (Root)
┌───────────────────┐
│ [20 | 40 | 60] │
└──┬────┬────┬────┬─┘
│ │ │ │
Level 1 (Internal)
┌────┘ ┌─┘ ┌─┘ └────┐
▼ ▼ ▼ ▼
[5|10|15] [25|30] [45|50|55] [65|70]
Level 2 (Leaf)
[1→5→10→15→20→25→30→35→40→45→50→55→60→65→70]
何が強いのか
B+Treeは次の用途に強い構造です。
- 等値検索
- 範囲検索
- ソート済み走査
-
ORDER BYとインデックス順の相性が良いケース
計算量のイメージ
| 操作 | 計算量 | 備考 |
|---|---|---|
| 点検索（=） | O(log N) | RootからLeafへ降下 |
| 範囲検索 | O(log N + K) | Kは取得件数 |
| 挿入 | O(log N) | ページ分割が起きることがある |
| 削除 | O(log N) | ページ再編成が起きることがある |
EXPLAINで確認する
EXPLAIN SELECT * FROM orders WHERE user_id = 101\G
EXPLAIN SELECT * FROM orders WHERE user_id BETWEEN 100 AND 200\G
EXPLAINで最低限見るべき項目
| 項目 | 見るポイント |
|---|---|
type |
ALL は危険。ref, range, eq_ref を意識 |
key |
どのインデックスが使われたか |
rows |
推定スキャン行数 |
Extra |
Using index, Using filesort, Using temporary など |
実務での注意
B+Treeは万能ではありません。
たとえば、前方一致でない LIKE '%xxx' は通常インデックスを活かしづらく、フルスキャンに寄りやすくなります。
つまり、インデックス構造に合った検索条件を書くことが重要です。
5. Covering Index
Covering Indexとは、クエリが必要とする列をすべてインデックスだけで満たせる状態を指します。
例
-- 通常のインデックス
INDEX idx_user_id (user_id)
-- Covering Index
INDEX idx_user_status (user_id, status, id)
SELECT id, status
FROM orders
WHERE user_id = 101;
この場合、user_id, status, id がインデックスに揃っているため、Clustered Indexを参照せずに結果を返せます。
なぜ強いのか
Covering Indexの本質は、Double Readを避けられることです。
つまり、
- Secondary Indexを読む
- PKでClustered Indexに戻る
という2段階アクセスが不要になります。
設計指針
クエリ: SELECT A, B FROM t WHERE C = ? AND D > ? ORDER BY E
インデックス:
INDEX (C, D, E, A, B)
考え方は次の順です。
- WHERE句の等値条件
- WHERE句の範囲条件
- ORDER BY 句
- SELECT句で返す列
実務での注意点
Covering Indexは強力ですが、やりすぎると逆効果です。
- インデックスサイズが肥大化する
- INSERT/UPDATEが重くなる
- 更新頻度の高い列を含めると維持コストが高い
つまり、速い参照のために書き込みコストをどこまで許容するかの設計判断が必要です。
絶対に誤解してはいけない点
Covering Indexは「何でもかんでも列を足せば良い」という話ではありません。
本当に頻繁に使うクエリに対して、ピンポイントで効かせるのが基本です。
6. MVCCとLocking Read
ロックの話に入る前に、MVCCを整理しておきます。
InnoDBはMVCCにより、読み取り性能と同時実行性を高めています。
MVCCとは
MVCCは、更新前のバージョン情報を保持することで、読み取りが常に最新の物理データを直接ロックせずに済む仕組みです。
イメージとしては、1つの行に対して複数のバージョンがあるようなものです。
Row
├─ version 1
├─ version 2
└─ version 3
Consistent Read
通常の SELECT は、多くの場合 consistent read として実行されます。
これはスナップショットを参照する読み取りです。
そのため、更新中の行があっても、必要に応じて過去バージョンを見て読み取りを進めます。
Locking Read
一方、次のようなSQLは locking read です。
SELECT ... FOR UPDATESELECT ... FOR SHARE
これらは「読む」だけでなく、その後の更新や整合性確保のためにロックを伴う読み取りです。
なぜこの違いが重要か
多くのロック事故は、次の誤解から始まります。
SELECTとSELECT ... FOR UPDATEは似たようなものだろう
実際にはまったく別物です。
- 通常の
SELECTはスナップショット読み取り -
FOR UPDATEは現在のインデックスレコードをたどりながらロックを取得する
この違いを理解していないと、FOR UPDATE を気軽に入れて本番でロック競合を起こします。
7. MySQLのロック種類
InnoDBのロックは大きく見ると、テーブルレベルと行レベルに分かれます。
ロック階層
┌─────────────────────────────────────────┐
│ テーブルロック（DDL時、LOCK TABLES等） │
├─────────────────────────────────────────┤
│ Intention Lock（IX / IS） │
├─────────────────────────────────────────┤
│ 行レベルロック │
│ ├─ Record Lock │
│ ├─ Gap Lock │
│ └─ Next-Key Lock │
│ Insert Intention Lock │
└─────────────────────────────────────────┘
分離レベルとの関係
単純化しすぎない形で言うと、分離レベルごとにロック付き読み取りの振る舞いは変わります。
| 分離レベル | 一貫性読み取り | ロック付き読み取り時の傾向 |
|---|---|---|
| READ UNCOMMITTED | 最も弱い | 更新時のロックは取得される |
| READ COMMITTED | 文単位の最新コミットを参照 | Gap Lockは抑制されやすいが、用途によっては発生する |
| REPEATABLE READ | 同一トランザクション内で一貫したスナップショット | 範囲条件ではNext-Key Lockが中心 |
| SERIALIZABLE | より強い直列化 | 読み取りもより強く制御される |
まず押さえるべきこと
この記事の文脈では、MySQL 8 / InnoDB / REPEATABLE READ を主軸に考えるのがわかりやすいです。
なぜなら、実務で遭遇する Next-Key Lock の多くはこの前提で理解すると整理しやすいからです。
8. Record Lock
Record Lockは、特定のインデックスレコード1件に対するロックです。
例
BEGIN;
SELECT * FROM orders WHERE id = 5 FOR UPDATE;
この場合、PRIMARY KEY id = 5 に対するRecord Lockが取得されます。
id: 1 2 3 4 [5] 6 7 8 9 10
↑
Record Lock（id=5のみ）
何がブロックされるか
別トランザクションから同じ行に対して
UPDATEDELETESELECT ... FOR UPDATESELECT ... FOR SHARE
などを実行しようとすると待機が発生します。
実務での意味
PRIMARY KEYの等値検索で FOR UPDATE を使うのは、最も安全で意図が明確なロック取得方法です。
言い換えると、
ロックが必要なら、まず「PKの等値検索に落とし込めないか」を考える
のが実務上の基本になります。
よくある危険
インデックスが使えない条件で更新すると、InnoDBは広範囲を走査しながらロックを取ります。
UPDATE orders
SET amount = 999
WHERE status = 'pending';
status に適切なインデックスがない場合、実質的に広範囲の行がロックされ、テーブル全体の同時実行性を著しく損ないます。
9. Gap Lock
Gap Lockは、インデックス上のレコードとレコードの間の“隙間” に対するロックです。
対象は既存行ではなく、新しいレコードが入りうる範囲です。
例
テーブルのインデックス値が次のようになっているとします。
1, 5, 10, 20
このとき、次のSQLを実行します。
BEGIN;
SELECT * FROM orders WHERE id BETWEEN 5 AND 15 FOR UPDATE;
イメージ
インデックス値: 1 5 10 20
BETWEEN 5 AND 15 の読み取りで、
id=5, id=10 の実在レコードだけでなく、
(5,10), (10,20) のギャップも制御対象になる
なぜ必要なのか
もしギャップをロックしなければ、別トランザクションが id = 12 を挿入できてしまいます。
すると、同じ範囲検索を再実行したときに結果集合が変わる可能性があります。
Gap Lockは、この「途中に新しい行が入ってくること」を防ぐ役割を持ちます。
INSERTが止まる理由
INSERTは対象位置に対してInsert Intention Lockを要求します。
そのギャップにすでにGap Lockがあれば、INSERTは待機します。
(10,20) に Gap Lock がある
↓
id=12 を INSERT したい
↓
Insert Intention Lock を取りたい
↓
競合して待機
実務での注意点
Gap Lockは理解しづらく、特に「既存行をロックしていないのにINSERTが止まる」現象の原因になります。
本番で突然INSERTが詰まったとき、原因がGap Lockだと気づけないケースは非常に多いです。
10. Next-Key Lock
Next-Key Lockは、Record LockとGap Lockを組み合わせたロックです。
InnoDBの REPEATABLE READ において、範囲条件のロック付き読み取りで中心になる仕組みです。
基本形
(a, b]
つまり、左開き・右閉じの区間として捉えると理解しやすいです。
例
インデックス値が次のように並んでいるとします。
1 5 10 20
このとき、Next-Key Lockの区間イメージは次のようになります。
(-∞, 1]
(1, 5]
(5, 10]
(10, 20]
(20, +∞)
例1：PRIMARY KEYの等値検索
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
一意インデックスの等値検索では、通常はRecord Lockに最適化されます。
このケースは比較的安全です。
例2：範囲検索
SELECT * FROM orders WHERE id > 5 FOR UPDATE;
この場合、id > 5 の範囲に沿ってNext-Key Lockが取得されます。
(5,10]
(10,20]
(20,+∞)
つまり、既存レコードだけでなく、その範囲へのINSERTもブロックされます。
例3：非ユニークセカンダリインデックスの等値検索
SELECT * FROM orders WHERE user_id = 101 FOR UPDATE;
非ユニークインデックスでは、SQL上は「等値検索」に見えても、内部的にはその値が並ぶ範囲を走査します。
そのため、単純なRecord Lockだけで済まず、前後のギャップも含めた制御が発生しやすくなります。
ここが最も誤解されるポイント
多くのエンジニアは次のように考えます。
SELECT ... FOR UPDATEは行ロックである
これは半分正しく、半分危険です。
実際には、検索条件とインデックスの種類によっては、行だけでなく範囲全体にロックが及ぶことがあります。
この誤解が、最も多い本番ロック事故の原因です。
11. Phantom ReadとNext-Key Lockの関係
Phantom Readとは、同じトランザクション内で同じ条件の範囲検索をしたのに、結果集合が変わる現象です。
発生イメージ
Tx1:
SELECT * FROM orders WHERE amount > 1000;
Tx2:
INSERT INTO orders (... amount=2000 ...);
Tx1:
SELECT * FROM orders WHERE amount > 1000;
→ さっき無かった行が増えている
なぜ問題なのか
これは「1行の更新競合」ではなく、条件に合致する集合そのものが変化する問題です。
そのため、Record Lockだけでは防げません。
Next-Key Lockの役割
SELECT ... FOR UPDATE のようなlocking readで範囲条件を使うと、InnoDBは必要に応じてNext-Key Lockを使い、その範囲に新しい行が入り込むことを防ぎます。
MVCCとの違い
ここで混乱しやすい点があります。
通常の SELECT は、InnoDBの一貫性読み取りにより、同じトランザクション内では安定したスナップショットを見られることがあります。
一方で、SELECT ... FOR UPDATE は現在のインデックスレコードに対してロックを取る読み取りであり、phantomを防ぐにはGap / Next-Keyの仕組みが必要です。
つまり、
- 通常の
SELECTはMVCCによるスナップショット読み取り -
FOR UPDATEは現在行に対するlocking read
という違いがあります。
12. SELECT FOR UPDATE のロック範囲
SELECT ... FOR UPDATE は非常に便利ですが、最も事故を起こしやすいSQLの1つです。
ロック範囲は、単に「何件返るか」ではなく、どのインデックスをどのように走査するかで決まります。
パターン1：PKの等値検索
SELECT * FROM orders WHERE id = 5 FOR UPDATE;
- もっとも安全
- 取得されるのは通常Record Lock
- 影響範囲が最小
パターン2：PKの範囲検索
SELECT * FROM orders WHERE id BETWEEN 5 AND 10 FOR UPDATE;
この場合、存在するレコードに対するロックだけでなく、その前後のギャップも制御対象になります。
つまり、id=6〜9 のような未存在値へのINSERTも止まりうるということです。
パターン3：非ユニークセカンダリインデックスの等値検索
SELECT * FROM orders WHERE user_id = 101 FOR UPDATE;
これは一見シンプルですが、user_id が非ユニークであれば、内部では対象値の並ぶ範囲を走査します。
そのため、前後のギャップや関連するClustered Index行も含めてロック範囲が広がります。
パターン4：インデックスなし条件
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
これは非常に危険です。
インデックスが使えなければ、InnoDBは広範囲のレコードをたどりながらロックを取得することになります。
その結果、実質的にテーブル全体の同時実行性を壊すことがあります。
絶対に軽く使ってはいけないケース
次のようなSQLは本番事故の原因になりやすいです。
SELECT * FROM orders WHERE memo LIKE '%urgent%' FOR UPDATE;
SELECT * FROM orders WHERE created_at > '2024-01-01' FOR UPDATE;
SELECT * FROM job_queue WHERE status = 'waiting' FOR UPDATE;
いずれも、ロック範囲が広すぎたり、ワーカー同士で奪い合いになったりしやすいです。
注意したい設計パターン
次のような2段階取得は、一見安全そうに見えます。
SELECT id FROM orders WHERE status = 'pending' LIMIT 10;
SELECT * FROM orders WHERE id IN (1,2,3,...) FOR UPDATE;
ただし、この2つのSQLの間に別トランザクションが状態を変える余地があります。
そのため、「候補抽出」と「ロック取得」を分ける設計は、厳密な排他が必要なケースでは注意が必要です。
13. デッドロックの発生原理
デッドロックは、複数のトランザクションが互いのロック解放を待ち合い、循環待ちになる状態です。
典型パターン
Tx1: id=5 をロック
Tx2: id=10 をロック
Tx1: id=10 を取りに行く
Tx2: id=5 を取りに行く
→ 循環待ち
例
-- Tx1
BEGIN;
SELECT * FROM orders WHERE id = 5 FOR UPDATE;
-- Tx2
BEGIN;
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
-- Tx1
SELECT * FROM orders WHERE id = 10 FOR UPDATE;
-- Tx2
SELECT * FROM orders WHERE id = 5 FOR UPDATE;
InnoDBはこれを検出し、どちらか一方をロールバックします。
ERROR 1213: Deadlock found when trying to get lock
見えにくいデッドロック
実務では、単純な2行ロックよりも、範囲ロック + INSERT + 別リソース待ち のような複雑な形で発生することが多いです。
たとえば、
- Tx1 が範囲
FOR UPDATEを保持している - Tx2 がその範囲にINSERTしようとして待機している
- その間にTx1がTx2の持つ別行を取りにいく
といった形です。
対策
- ロック取得順序を統一する
- トランザクションを短く保つ
-
FOR UPDATEの対象範囲を最小化する - 範囲ロックを安易に使わない
- デッドロック時はリトライ前提で設計する
Spring Bootでのリトライ例
@Service
public class OrderService {
@Retryable(
value = { DeadlockLoserDataAccessException.class },
maxAttempts = 3,
backoff = @Backoff(delay = 100, multiplier = 2)
)
@Transactional
public void processOrder(Long orderId) {
Order order = orderRepository.findByIdWithLock(orderId);
// ビジネスロジック
}
@Recover
public void recoverFromDeadlock(DeadlockLoserDataAccessException e, Long orderId) {
throw new OrderProcessingException("処理に失敗しました", e);
}
}
14. インデックス設計とロック競合
インデックス設計は、性能だけでなくロック競合の範囲にも直結します。
悪い例
UPDATE orders
SET status = 'processing'
WHERE status = 'pending' AND user_id = 101;
この条件に適切なインデックスが無ければ、広範囲スキャンと広範囲ロックを引き起こします。
良い例1：複合インデックス
INDEX idx_userid_status (user_id, status)
UPDATE orders
SET status = 'processing'
WHERE user_id = 101
AND status = 'pending';
この形なら、対象範囲をかなり絞り込めます。
良い例2：PKで確定してから更新
最終的な更新をPKベースで行うのは、ロック範囲を小さくしやすい基本戦略です。
ただし前章で述べた通り、候補抽出とロック取得を分離するなら、その間の競合をどう扱うかは必ず考える必要があります。
選択性も重要
低選択性の列だけを条件にすると、たとえインデックスがあっても大量行にロックが集中します。
たとえば、
- 全100万行
-
status='pending'が30万行
のような場合、status 単独での FOR UPDATE は危険です。
カーディナリティ確認例
SELECT
INDEX_NAME,
CARDINALITY
FROM information_schema.STATISTICS
WHERE TABLE_SCHEMA = 'mydb'
AND TABLE_NAME = 'orders';
本質
インデックス設計が悪いと、遅くなるだけではなく、ロックの被害範囲まで広がる
これが非常に重要です。
15. APIアプリケーションでのロック設計
高トラフィックAPIでは、ロック設計はDBだけの話ではありません。
トランザクション境界、アプリの処理時間、外部API呼び出し、コネクションプールまで含めて考える必要があります。
JPAの例
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
@Lock(LockModeType.PESSIMISTIC_WRITE)
@Query("SELECT o FROM Order o WHERE o.id = :id")
Optional<Order> findByIdForUpdate(@Param("id") Long id);
@Query(value = """
SELECT * FROM orders
WHERE status = 'pending'
ORDER BY id
LIMIT :limit
FOR UPDATE SKIP LOCKED
""", nativeQuery = true)
List<Order> findPendingOrdersForProcessing(@Param("limit") int limit);
}
サービス層の考え方
@Service
@Transactional(readOnly = true)
public class OrderService {
public List<Order> findUserOrders(Long userId) {
return orderRepository.findByUserId(userId);
}
@Transactional
public Order updateOrderStatus(Long orderId, String newStatus) {
Order order = orderRepository.findByIdForUpdate(orderId)
.orElseThrow(() -> new NotFoundException("Order not found: " + orderId));
order.updateStatus(newStatus);
return orderRepository.save(order);
}
}
実務で本当に危険なこと
次のような実装は危険です。
-
FOR UPDATEでロックを取ったまま外部APIを呼ぶ - ロック保持中に重い業務ロジックを実行する
- コネクションプールが小さいのに長トランザクションを許容する
例
コネクションプール: 10
1トランザクション: 500ms
RPS: 100
→ 同時並行リクエスト数 ≒ 50
→ コネクション不足 + ロック待ち増加
対策
- 外部API呼び出しはトランザクション外へ出す
-
FOR UPDATE後の処理は最短にする -
innodb_lock_wait_timeoutを必要に応じて調整する - 悲観ロックと楽観ロックを使い分ける
楽観ロックとの比較
競合頻度が低いなら、@Version を使った楽観ロックの方が高スループットになりやすいです。
逆に、在庫や残高のように絶対に競合を許したくない処理は悲観ロックが向きます。
16. 実務で安全な SELECT FOR UPDATE のパターン
ここでは、実務で比較的安全に使いやすいパターンを整理します。
パターン1：在庫管理
BEGIN;
SELECT stock_count
FROM products
WHERE id = 42
FOR UPDATE;
UPDATE products
SET stock_count = stock_count - 3
WHERE id = 42
AND stock_count >= 3;
COMMIT;
この形は、
- PK等値検索
- ロック範囲が最小
- その後の更新も同じPK対象
となるため、かなり安全です。
パターン2：ジョブキュー
BEGIN;
SELECT id, payload
FROM job_queue
WHERE status = 'waiting'
AND scheduled_at <= NOW()
ORDER BY priority DESC, id ASC
LIMIT 5
FOR UPDATE SKIP LOCKED;
UPDATE job_queue
SET status = 'running', started_at = NOW()
WHERE id IN (/* 上で取得したID */);
COMMIT;
SKIP LOCKED を使うことで、他ワーカーが処理中の行を飛ばせます。
複数ワーカーが同時に走るジョブ処理では非常に有効です。
パターン3：冪等性保証
冪等性制御の主役は、FOR UPDATE よりも UNIQUE制約 です。
CREATE TABLE order_idempotency (
idempotency_key VARCHAR(64) NOT NULL,
order_id BIGINT,
created_at DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP,
PRIMARY KEY (idempotency_key)
);
基本戦略は次です。
INSERT INTO order_idempotency (idempotency_key, order_id)
VALUES ('req-uuid-xxxx', 999);
重複時はDuplicate Key Errorで弾く、という設計の方がシンプルで強いです。
パターン4：楽観ロック
SELECT id, status, version
FROM orders
WHERE id = 5;
UPDATE orders
SET status = 'processing',
version = version + 1
WHERE id = 5
AND version = 7;
競合頻度が低く、読み取りが多いならこの方式は非常に有効です。
使い分けガイド
| シナリオ | 推奨 |
|---|---|
| 在庫・残高など絶対に競合を避けたい | 悲観ロック |
| ジョブ取り出し | FOR UPDATE SKIP LOCKED |
| ユーザー設定など競合が稀 | 楽観ロック |
| 重複リクエスト防止 | UNIQUE制約中心 |
17. 絶対に避けたい設計・SQL
最後に、実務で本当に危険なパターンをまとめます。
1. インデックスなし条件で FOR UPDATE
SELECT * FROM orders WHERE status = 'pending' FOR UPDATE;
status に適切なインデックスが無ければ、広範囲ロックになります。
本番で最もやってはいけないパターンの1つです。
2. 広すぎる範囲条件で FOR UPDATE
SELECT * FROM orders WHERE created_at > '2024-01-01' FOR UPDATE;
返却件数が少なく見えても、ロック範囲は広くなることがあります。
「条件で絞っているから安全」とは限りません。
3. ロック中に外部APIを呼ぶ
FOR UPDATE取得
↓
外部API呼び出し
↓
レスポンス待ち
↓
UPDATE
これはロック保持時間を無駄に延ばし、競合を増幅させます。
4. ロック順序がバラバラ
- 処理Aは
id=5 → id=10 - 処理Bは
id=10 → id=5
こうした実装はデッドロックの温床です。
5. 低選択性列だけで悲観ロック
status='pending' のような条件だけでロックを取りにいくと、対象が多すぎて危険です。
6. 「インデックスがあるから安全」と思い込む
インデックスがあっても、
- 非ユニーク
- 低選択性
- 範囲条件
- 大量件数
であれば、ロック範囲は十分広くなります。
18. まとめ
この記事で押さえたい要点は次の通りです。
インデックス理解の要点
- InnoDBの実データはClustered Indexにある
- Secondary IndexはPKを持つため、必要に応じてDouble Readが起きる
- B+Treeは等値検索だけでなく範囲検索・順序走査にも強い
- Covering Indexは有効だが、やりすぎると書き込みコストが増える
ロック理解の要点
-
FOR UPDATEは単純な「行ロック」とは限らない - Gap LockはINSERTを止める
- Next-Key Lockは範囲への新規挿入を防ぐ
- インデックス設計が悪いと、性能だけでなくロック被害も広がる
実務設計の原則
- まずPK等値検索に落とし込めないか考える
- トランザクションは短く保つ
- ロック中に外部APIを呼ばない
- ジョブキューでは
SKIP LOCKEDを検討する - 重複防止はUNIQUE制約を第一候補にする
- 楽観ロックと悲観ロックを使い分ける
本番環境で確認したいもの
SHOW STATUS LIKE 'Innodb_deadlocks';
SHOW STATUS LIKE 'Innodb_row_lock%';
SELECT * FROM performance_schema.data_locks;
SELECT * FROM performance_schema.data_lock_waits;
おわりに
MySQLのロック問題は、単に「SQLを丁寧に書けば防げる」というものではありません。
インデックス構造、MVCC、locking read、分離レベル、トランザクション設計がすべてつながっています。
特に SELECT ... FOR UPDATE は便利な反面、理解が浅いまま使うと簡単に本番事故を起こします。
だからこそ、次の順番で考えるのが重要です。
- まずインデックス構造を理解する
- 次にInnoDBのロックの種類を理解する
- 最後に、業務要件に合ったロック方式を選ぶ
この記事が、MySQLのインデックスとロックを体系的に整理する助けになればうれしいです。
参考
- MySQL 8.0 Reference Manual - InnoDB Locking
- MySQL 8.0 Reference Manual - InnoDB Transaction Model
- High Performance MySQL, 4th Edition