- ブラウザURL入力
- DNS名前→IP
- ルータ経路選択
- ロードバランサ振り分け
- Nginx受け口
- アプリ処理
- DB保存今ここ
前回、InnoDB の既定が REPEATABLE READ で、スナップショットが最初の一貫読み取りで確定することを見ました。今回はその同じレベルで動くもう一つの機構、隙間へのロックを扱います。対象は MySQL 8.4 です。
まず結論
InnoDB は行だけでなく**行と行の隙間**にも鍵をかけます。公式は “To prevent phantoms, `InnoDB` uses an algorithm called next-key locking that combines index-row locking with gap locking.” と書いています。存在しない行の場所を押さえるので、他のトランザクションは**そこに挿入できません**。
ただし重要な限定があります。next-key lock が使われるのは **“for searches and index scans”**。素の `SELECT` は “setting no locks” です。**RR で素の `SELECT` を繰り返して同じ集合が返るのは、gap lock のおかげではなくスナップショットのおかげ**です。この2つを混ぜないことが、この回の主題です。
InnoDB の既定(REPEATABLE READ)で `BEGIN` してから素の `SELECT * FROM t WHERE x > 10;` を2回実行する間に、別の接続が条件に合う行を挿入して COMMIT しました。2回目の SELECT はどうなるでしょうか。
当てることが目的ではありません。外した予測ほど記憶に残るので、直感で選んでください。
存在しない行に、どうやって鍵をかけるのか
行ロックだけでは防げない問題があります。公式はこう定義します。
The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.
For example, if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a "phantom" row.
2回目に増えている行が問題です。しかしその行は1回目の時点では存在しません。存在しない行に鍵はかけられません。そこで InnoDB は、行ではなく場所に鍵をかけます。
To prevent phantoms,
InnoDBuses an algorithm called next-key locking that combines index-row locking with gap locking.
隙間の定義はこうです。
A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.
A gap might span a single index value, multiple index values, or even be empty.
空でもよいという点が核心です。空の隙間に鍵をかけておけば、そこに現れるはずだった行が現れなくなります。そして next-key lock はその組み合わせです。
A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.
(negative infinity, 10](10, 11](11, 13](13, 20](20, positive infinity) ← supremum pseudo-record 側
区間の切り方に注目してください。値そのものだけでなく、値と値の間、そして最小値より下と最大値より上まで覆われています。最後の区間には特別な相手がいます。
For the last interval, the next-key lock locks the gap above the largest value in the index and the "supremum" pseudo-record having a value higher than any value actually in the index.
next-key lock とは何の組み合わせだと公式は定義していますか。
効く場面は限定されている
ここが最も誤解されるところです。公式の記述には限定が付いています。
By default,
InnoDBoperates inREPEATABLE READtransaction isolation level. In this case,InnoDBuses next-key locks for searches and index scans, which prevents phantom rows.
“for searches and index scans” です。すべての SELECT ではありません。 素の SELECT については別のページが明確に書いています。
SELECT ... FROMis a consistent read, reading a snapshot of the database and setting no locks unless the transaction isolation level is set toSERIALIZABLE.
鍵をかけないのです。ではなぜ RR で素の SELECT を2回実行すると同じ集合が返るのか。理由は別の機構です。
Consistent reads within the same transaction read the snapshot established by the first read.
最初の読み取りで確定したスナップショットを読み続けているからです。他のトランザクションの挿入は成功します。ただしこちらには見えません。gap lock は一切関与していません。
一方、SELECT ... FOR UPDATE・UPDATE・DELETE を使えば next-key lock が実際に取られ、挿入そのものがブロックされます。同じ「2回読んで同じ」という結果が、まったく違う手段で成立します。
gap lock は挿入だけを止める
行ロックの感覚を持ち込むと外す性質があります。
Gap locks in
InnoDBare "purely inhibitive", which means that their only purpose is to prevent other transactions from inserting to the gap. Gap locks can co-exist. A gap lock taken by one transaction does not prevent another transaction from taking a gap lock on the same gap. There is no difference between shared and exclusive gap locks. They do not conflict with each other, and they perform the same function.
共有と排他の区別が無い。同じ隙間に複数のトランザクションが同時に gap lock を持てます。止めるのは挿入だけです。
挿入側にも仕組みがあります。
An insert intention lock is a type of gap lock set by
INSERToperations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
同じ隙間でも位置が違えば待たないので、隙間が広いだけで挿入が直列化するわけではありません。広い区間が押さえられていても、そこへ挿入しようとする者同士が互いに待つとは限らない、ということです。
ここまでを整理すると、ファントムの定義に戻れます。問題は “the same query produces different sets of rows at different times” でした。同じ問いに違う答えが返ることが避けたい事態です。next-key lock はそれを「増える余地を先に塞ぐ」ことで防ぎ、スナップショットは「答えを固定する」ことで防ぎます。向きが逆であることに注意してください。前者は他人の行動を止め、後者は自分の見え方を固定するだけで他人を止めません。
無効化の条件も明記されています。
Gap locking can be disabled explicitly. This occurs if you change the transaction isolation level to
READ COMMITTED. In this case, gap locking is disabled for searches and index scans and is used only for foreign-key constraint checking and duplicate-key checking.
消えるのではなく用途が2つに絞られます。代償はこうです。
Because gap locking is disabled, phantom row problems may occur, as other sessions can insert new rows into the gaps.
一意インデックスでの一意行検索でも不要になります。
Gap locking is not needed for statements that lock rows using a unique index to search for a unique row. (This does not include the case that the search condition includes only some columns of a multiple-column unique index; in that case, gap locking does occur.)
括弧の中が実務で効きます。複合一意インデックスの一部の列しか条件に書かなかった場合は、一意行に絞り込めないので gap locking が起きます。「一意インデックスがあるから隙間はロックされない」と覚えると、ここで外します。
裏返しの条件も明記されています。
If
idis not indexed or has a nonunique index, the statement does lock the preceding gap.
インデックスが無い列、あるいは一意でないインデックスなら、前の隙間まで押さえられます。 同じ WHERE id = ... という文でも、その列にどんなインデックスが付いているかでロックの広さが変わるということです。次の回で「ロックは行ではなく索引につく」を扱いますが、その予告がここに出ています。
gap lock の性質について、公式が明記していることはどれですか。
InnoDB — 隙間に鍵をかける
探索とインデックススキャンでは next-key lock が使われ、隙間への挿入が実際にブロックされます。一方で素の SELECT は鍵をかけず、スナップショットで一貫性を得ます。つまり同じレベルの中に2つの機構が併存し、どちらが働くかは文の種類で決まります。
素のSELECT 鍵をかけない / スナップショットで一貫→ 他者のINSERTは成功するが見えないSELECT FOR UPDATE next-key lock を取るUPDATE / DELETE → 他者のINSERTがブロックされる
PostgreSQL — スナップショット隔離
PostgreSQL の Repeatable Read もファントムを許しません。ただし隙間に鍵をかけるのではなく、スナップショット隔離という技法で達成します。標準が要求するより強い保証だと文書自身が述べています。
"does not allow phantom reads"実装 Snapshot Isolation標準との関係 "a stronger guarantee than isrequired by the SQL standard"残る現象 serialization anomalies
どちらも REPEATABLE READ でファントムを防いでいます。では両者の違いはどこにあり、その違いは何を予測させるでしょうか。
「防げるか」ではなく「どうやって防いでいるか」を比べてください。挿入しようとした側に何が起きるかが変わります。
この章のまとめ
- ファントムは2回目に増えている行の問題。存在しない行には鍵をかけられないので、InnoDB は隙間に鍵をかける。
- 隙間は “even be empty” でもよい。next-key lock は record lock + その前の gap lock。
- 区間は
(negative infinity, 10]から(20, positive infinity)まで。最後の区間には supremum pseudo-record が相手として登場する。 - 効く場面は “for searches and index scans” に限定。素の
SELECTは “setting no locks”。RR で結果が安定するのはスナップショットのおかげで gap lock は無関係。 - gap lock は "purely inhibitive"。共有と排他の区別が無く、止めるのは挿入だけ。
READ COMMITTEDにすると探索からは外れ、外部キー検査と重複キー検査だけに使われる。代償は “phantom row problems may occur”。- PostgreSQL の RR もファントムを許さないが手段が違う(Snapshot Isolation)。結果が同じでも機構は別。
自分の現場で確かめる
読んだだけでは身につきません。手元の環境で実際に見てみてください。
理解度チェック
読んだ直後は「分かった」と感じやすいものです。実際に解いて確かめてください。
「InnoDB は REPEATABLE READ なので、gap lock によってファントムが完全に無くなる」。この説明の評価はどれですか。
用語集(4語)
- gap lock
- 公式の定義は “A gap lock is a lock on a gap between index records, or a lock on the gap before the first or after the last index record.”。隙間は “might span a single index value, multiple index values, or even be empty”。性質は "purely inhibitive" で、目的は挿入を防ぐことだけ。
- next-key lock
- “A next-key lock is a combination of a record lock on the index record and a gap lock on the gap before the index record.”。ファントム防止のための機構で、“To prevent phantoms, `InnoDB` uses an algorithm called next-key locking that combines index-row locking with gap locking.”
- ファントム(phantom)
- “The so-called phantom problem occurs within a transaction when the same query produces different sets of rows at different times.” 具体例は “if a SELECT is executed twice, but returns a row the second time that was not returned the first time, the row is a "phantom" row.”
- insert intention lock
- “An insert intention lock is a type of gap lock set by `INSERT` operations prior to row insertion. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.”