ロックは行ではなく索引につく — WHEREで除外した行も巻き込む — データベースの仕組み

リクエストがアプリケーションに届くまで

ブラウザURL入力
DNS名前→IP
ルータ経路選択
ロードバランサ振り分け
Nginx受け口
アプリ処理
DB保存今ここ

前回、隙間へのロックが「探索とインデックススキャン」で使われることを見ました。その「インデックス」という限定が何を意味するのかを、今回正面から扱います。対象は MySQL 8.4 と PostgreSQL 18 です。

まず結論

InnoDB のロックは行そのものではなく**インデックスレコード**に掛かります。公式は “Record locks always lock index records, even if a table is defined with no indexes.” と書いています。索引が無ければ隠しクラスタ化インデックスが作られ、それが使われます。

そして山場はここです。**既定の REPEATABLE READ では、`WHERE` で除外されるはずの行もロックされます。** 理由は公式が明言しています——“**`InnoDB` does not remember the exact `WHERE` condition, but only knows which index ranges were scanned.**” 記憶されているのは条件ではなく**走査した索引の範囲**です。だから索引が無い列で `UPDATE` すると、実質的に広い範囲が巻き込まれます(READ COMMITTED では、条件に合わない行のロックは `WHERE` の評価後に外されます)。

? 読む前に予測

既定の REPEATABLE READ のまま、インデックスが無い列に対して `UPDATE t SET v = 1 WHERE memo = 'x';` を実行しました。条件に合う行は1行だけです。ロックされるのは何行でしょうか。

当てることが目的ではありません。外した予測ほど記憶に残るので、直感で選んでください。

抽象原理

ロックの単位はインデックスレコード

「行ロック」という言葉から、行そのものに鍵が掛かる絵を想像しがちです。しかし公式の定義は違います。

Record locks always lock index records, even if a table is defined with no indexes. For such cases, InnoDB creates a hidden clustered index and uses this index for record locking.

always で、例外がありません。索引が無いテーブルでも、隠しクラスタ化インデックスを作ってそこに掛けます。つまりロックは常に索引という構造の上の座標に対して取られます。

この設計から、直感に反する帰結が出ます。

A locking read, an UPDATE, or a DELETE generally set record locks on every index record that is scanned in the processing of an SQL statement. It does not matter whether there are WHERE conditions in the statement that would exclude the row. InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.

最後の一文が核心です。条件は記憶されていません。 記憶されているのは「どの索引範囲を走査したか」だけです。

だから「1行だけ更新したのに、なぜ他の行が待たされるのか」という問いへの答えは、更新した行数ではなく走査した範囲を見ることになります。条件に合う行が1行でも、索引が無くて全体を走査したなら、既定の REPEATABLE READ では走査したすべてにロックが残ります。

READ COMMITTED ではこう変わります。

For UPDATE or DELETE statements, InnoDB holds locks only for rows that it updates or deletes. Record locks for nonmatching rows are released after MySQL has evaluated the WHERE condition.

条件に合わなかった行のロックは評価後に外れます。

同じ「1行更新」でも、索引の有無でロックの広さが変わる(既定の REPEATABLE READ)表

UPDATE t SET v = 1 WHERE memo = 'x';   (条件に合う行は1行)
 
memo に索引が無い    全体を走査 → 走査した全レコードにロック
memo に索引がある    該当範囲だけ走査 → ロックもその範囲
 
記憶されているもの   走査した索引範囲(WHERE条件ではない)

✓ ここまでの確認

インデックスが1つも定義されていないテーブルに record lock を掛けると、何に対して掛かりますか。

具体実物

ロックを取らない読み取りと、取る読み取り

InnoDB の既定の SELECT はロックを取りません。

A consistent read means that InnoDB uses multi-versioning to present to a query a snapshot of the database at a point in time. The query sees the changes made by transactions that committed before that point in time, and no changes made by later or uncommitted transactions.

Consistent read is the default mode in which InnoDB processes SELECT statements in READ COMMITTED and REPEATABLE READ isolation levels. A consistent read does not set any locks on the tables it accesses, and therefore other sessions are free to modify those tables at the same time a consistent read is being performed on the table.

しかしそれでは足りない場面があります。

If you query data and then insert or update related data within the same transaction, the regular SELECT statement does not give enough protection. Other transactions can update or delete the same rows you just queried. InnoDB supports two types of locking reads that offer extra safety:

そこで2種類が用意されています。SELECT ... FOR SHARE は “Sets a shared mode lock on any rows that are read. Other sessions can read the rows, but cannot modify them until your transaction commits.”、SELECT ... FOR UPDATE は “For index records the search encounters, locks the rows and any associated index entries, the same as if you issued an UPDATE statement for those rows.” です。

ここで通説が1つ崩れます。

Consistent reads ignore any locks set on the records that exist in the read view. (Old versions of a record cannot be locked; they are reconstructed by applying undo logs on an in-memory copy of the record.)

FOR UPDATE を掛けても、他セッションの素の SELECT は止まりません。 別バージョンを読んで通過します。古いバージョンはロックの対象にすらなりません。止まるのは更新側と、別の locking read の側だけです。

待ちたくない場合の道具もあります。NOWAIT は “never waits to acquire a row lock. The query executes immediately, failing with an error if a requested row is locked.”、SKIP LOCKED は “removing locked rows from the result set.” ただし後者には条件が付きます——“Queries that skip locked rows return an inconsistent view of the data. SKIP LOCKED is therefore not suitable for general transactional work.” 続けて用途が限定されます——“However, it may be used to avoid lock contention when multiple sessions access the same queue-like table.” キュー的なテーブル専用の道具という位置づけです。

古い書き方を見かけたときの対応も明記されています。

SELECT ... FOR SHARE is a replacement for SELECT ... LOCK IN SHARE MODE, but LOCK IN SHARE MODE remains available for backward compatibility. The statements are equivalent.

等価と書かれているので、既存コードの LOCK IN SHARE MODE を読み替えるときに意味を疑う必要はありません。

具体実物

古い姿はどこから復元されるのか

一貫読み取りが「過去の姿」を返せるのは、変更前の情報が残っているからです。8.4 本文はこう書きます。

InnoDB is a multi-version storage engine. It keeps information about old versions of changed rows to support transactional features such as concurrency and rollback. This information is stored in undo tablespaces in a data structure called a rollback segment.

そのために各行に隠しフィールドが足されます。

Internally, InnoDB adds three fields to each row stored in the database:

A 6-byte DB_TRX_ID field indicates the transaction identifier for the last transaction that inserted or updated the row.

A 7-byte DB_ROLL_PTR field called the roll pointer. The roll pointer points to an undo log record written to the rollback segment.

A 6-byte DB_ROW_ID field contains a row ID that increases monotonically as new rows are inserted.

6-byte / 7-byte / 6-byte の3つです。DB_ROLL_PTR が undo log record を指し、そこから「更新前の姿」を組み立てます。

undo log は2種類に分かれ、寿命が違います。

Insert undo logs are needed only in transaction rollback and can be discarded as soon as the transaction commits.

一方で update undo log は一貫読み取りにも使われるため、そのスナップショットを必要とするトランザクションが残っている限り捨てられません。

削除も即座の物理削除ではありません。

In the InnoDB multi-versioning scheme, a row is not physically removed from the database immediately when you delete it with an SQL statement. InnoDB only physically removes the corresponding row and its index records when it discards the update undo log record written for the deletion. This removal operation is called a purge, and it is quite fast, usually taking the same order of time as the SQL statement that did the deletion.

削除した行が消えるのは、その undo log record が捨てられるときです。だから古いスナップショットを必要とするトランザクションが居座ると、削除済みの行も残り続けます。DELETE したのに領域が戻らないという観察の背景がここにあります。

InnoDB 全体の設計方針も、一行で述べられています。

The InnoDB transaction model aims to combine the best properties of a multi-versioning database with traditional two-phase locking.

多版と二相ロックの組み合わせ——ロックの話とスナップショットの話が同じエンジンに同居している理由です。

✓ ここまでの確認

`SELECT ... FOR UPDATE` を掛けた行を、別のセッションが素の `SELECT` で読もうとしました。どうなりますか。

⇄ 2つを見比べる

具体例 A

MySQL — 暗黙のロックに変える

InnoDB の SERIALIZABLE は REPEATABLE READ を土台に、素の SELECT をロックする読み取りに置き換えます。つまり読み取りが待ちを生む側に回ります。autocommit が有効かどうかで挙動が変わる点も、前提として書かれています。

MySQL 8.4 の記述設定ファイル

"This level is like REPEATABLE READ, but
InnoDB implicitly converts all plain
SELECT statements to SELECT ... FOR SHARE
if autocommit is disabled."
 
→ 読み取りがロックを取る = 待ちが発生する

具体例 B

PostgreSQL — 監視して、失敗させる

PostgreSQL の SERIALIZABLE は Repeatable Read と同じように動きつつ、直列化できない条件を監視します。predicate locking という語は出ますが、待ちを生まないと明記され、デッドロックの原因にもならないと断言されています。代わりに失敗が返るので、リトライが設計の前提になります。

PostgreSQL 18 の記述設定ファイル

"Serializable Snapshot Isolation"
"This monitoring does not introduce any
blocking beyond that present in
repeatable read"
"these locks do not cause any blocking"
 
→ 待たない代わりに serialization failure

どちらも SERIALIZABLE という同じ名前です。では、アプリ側が書かなければならないコードはどう違ってくるでしょうか。

一方は「待つ」、もう一方は「失敗する」。それぞれに必要な備えは何でしょうか。

「FOR UPDATE を掛けたから読まれない」は誤り

FOR UPDATE は強い名前ですが、素の SELECT を止める力はありません。

Consistent reads ignore any locks set on the records that exist in the read view.

一貫読み取りは read view にあるレコードのロックを無視します。古いバージョンは “cannot be locked” とまで書かれており、undo log からメモリ上に組み立て直されたものが返ります。

止まるのは、同じレコードを更新しようとした側と、FOR SHARE / FOR UPDATE を掛けようとした側です。「ロックしたのだから誰にも見えない」という前提でロジックを書くと、他セッションが古い値を読んで進むという形で破れます。

なお rollback segment の所在については、system tablespace と書いた古い版の記述が流通していますが、8.4 本文は undo tablespaces です。8.4 の用語集も “Prior to MySQL 8.0, the system tablespace also includes one or more rollback segments used for undo logs.” と、それを 8.0 より前の配置として書いています。バージョンをまたいで引用を混ぜると、こういう食い違いをそのまま持ち込みます。

実務の場面「1行のUPDATEなのに、別の処理が大量に待たされる」と言われたとき

見る順序は更新した行数ではなく、走査した索引範囲です。ここを取り違えると原因に辿り着けません。

まず EXPLAIN で、その UPDATE がどのインデックスを使い、どれだけの行を走査する見込みかを確認します。type が ALL なら全体を走査しており、“every index record that is scanned” が全レコードを意味します。条件に合うのが1行でも関係ありません——“It does not matter whether there are WHERE conditions in the statement that would exclude the row.”

次に performance_schema.data_locks で実際のロックを数えます。条件に合う行数とロック行数が大きく乖離していれば、それが答えです。対処は条件列にインデックスを付けて走査範囲を狭めることで、ロックを減らすためにインデックスを足すという、性能とは別の動機になります。

redo log と undo log を混同しないことも、障害調査では効きます。redo log は “used during crash recovery to correct data written by incomplete transactions” で、変更を再適用する側です。undo log は “how to undo the latest change by a transaction to a clustered index record” で、取り消す側であり、一貫読み取りで過去の姿を組み立てるのにも使われます。「ログが溜まっている」という報告を受けたとき、どちらの話かで見る場所が変わります。undo 側が膨らむのは、古いスナップショットを必要とする長いトランザクションが居座っているときです。

この章のまとめ

ロックは行ではなくインデックスレコードに掛かる。“Record locks always lock index records” で例外が無く、索引が無ければ隠しクラスタ化インデックスが作られる。
既定の REPEATABLE READ では、WHERE で除外される行もロックされる。 理由は “InnoDB does not remember the exact WHERE condition, but only knows which index ranges were scanned.”
だから「1行更新」でも、索引が無くて全体を走査したなら走査範囲すべてが巻き込まれる(READ COMMITTED では条件に合わない行のロックは評価後に外れる)。見るべきは更新行数ではなく走査範囲。
素の SELECT はロックを取らず、さらに read view のロックを無視する。FOR UPDATE は他セッションの素の SELECT を止めない。
古い姿は undo tablespaces の rollback segment(8.4 本文の表記)から復元される。隠しフィールドは 6-byte DB_TRX_ID / 7-byte DB_ROLL_PTR / 6-byte DB_ROW_ID。
SERIALIZABLE は製品で別物。 MySQL は暗黙の FOR SHARE(待ちが出る)、PostgreSQL は SSI(待たない代わりに リトライ必須)。

自分の現場で確かめる

読んだだけでは身につきません。手元の環境で実際に見てみてください。

環境:

既定の REPEATABLE READ のまま、インデックスが無い列を条件にした `UPDATE` を `BEGIN` の中で実行し、`performance_schema.data_locks` に何行のロックが現れるか数える。条件に合う行数と比べると差がはっきり出る
同じ `UPDATE` の条件列にインデックスを追加してから、もう一度ロック数を数える。**走査範囲が狭まればロックも狭まる**ことを自分の目で確認する
FOR UPDATE を掛けた行が、素のSELECTからは読めることを確かめる端末2つで mysql に入る。片方で BEGIN; SELECT * FROM t WHERE id = 1 FOR UPDATE; を実行したまま放置し、もう片方で SELECT * FROM t WHERE id = 1; を実行する。待たされずに値が返ることを確認し、次に UPDATE t SET v = 9 WHERE id = 1; を試して今度は待たされることを比べる

理解度チェック

読んだ直後は「分かった」と感じやすいものです。実際に解いて確かめてください。

問題 1 / 3正解: 0

`WHERE` 条件で除外される行までロックされる理由を、公式の記述どおりに説明したものはどれですか。

用語集(4語)

record lock: 公式の定義は “Record locks always lock index records, even if a table is defined with no indexes. For such cases, `InnoDB` creates a hidden clustered index and uses this index for record locking.” 掛かる先は常にインデックスレコードで、行データそのものではない。
consistent read(一貫読み取り): “A consistent read means that `InnoDB` uses multi-versioning to present to a query a snapshot of the database at a point in time.” ロックを取らない——“A consistent read does not set any locks on the tables it accesses”。さらに “Consistent reads ignore any locks set on the records that exist in the read view.”
rollback segment: 8.4 本文の記述では、変更前の行バージョンを保持する構造。“This information is stored in undo tablespaces in a data structure called a **rollback segment**.” undo log はこの中の undo log segment に置かれる。
SKIP LOCKED: “A locking read that uses `SKIP LOCKED` never waits to acquire a row lock. The query executes immediately, removing locked rows from the result set.” ただし “Queries that skip locked rows return an inconsistent view of the data.” と限定が付き、用途はキュー的なテーブルに絞られる。

ロックは行ではなく索引につく — WHEREで除外した行も巻き込む