0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?

@mori-dev@github

`SELECT FOR UPDATE` は「これから INSERT される行」までは止めない

0
Posted at

環境

  • PostgreSQL 14 以降（動作を確認した最低バージョン）
  • Node.js 18 以降
  • pg (node-postgres) 8.x

背景

別記事 では、SELECT FOR UPDATE を使ってテナント別の連番払い出しを直列化する方法を書きました。

そこでは、テナントごとに 1 行だけ存在する tenant_seq をロックしています。

SELECT last_seq
FROM tenant_seq
WHERE tenant_id = $1
FOR UPDATE

この使い方では、SELECT FOR UPDATE はよく効きます。既に存在する tenant_seq の 1 行をロックし、同じテナントの連番払い出しを待たせられるためです。

一方で、SELECT FOR UPDATE は「条件に一致する行を将来 INSERT されないようにする」仕組みではありません。

この記事では、その違いを扱います。

効く場面:
  既に存在する行をロックする

効かない場面:
  同じ WHERE 条件に後から入ってくる INSERT を止める

たとえば、管理画面で複数のドキュメントを一括でアーカイブ、またはアーカイブ解除する機能を考えます。

この機能には、「操作前の状態を row_audit_details テーブルに記録し、後から行ごとに元の状態へ戻せる」という要件があるとします。

素直に実装すると次のような 2 ステップになります。

  1. 対象行の変更前状態を SELECT する
  2. 対象行を UPDATE する

ここで問題になるのが、2 つのクエリの間に別トランザクションが新しい行を INSERT した場合の挙動です。

実装と問題の構造

変更前状態の記録と UPDATE を原子的に行うため、同一トランザクション内で SELECT ... FOR UPDATE → UPDATE の順に実行しました。

// bulk-archive.ts (簡略化)
const client = await pool.connect();
await client.query('BEGIN');

// ① 対象行の変更前状態を取得してロック
const beforeSnapshots = await selectBulkBeforeSnapshots(client, {
  tenantId,
  workspaceId,
  filter: bulkFilter,
  targetArchived,
});

// ② 同じ WHERE 条件で一括 UPDATE
const result = await bulkArchiveOperation(client, {
  tenantId,
  workspaceId,
  operation,  // 'archive' | 'unarchive'
  filter: bulkFilter,
});

await client.query('COMMIT');
client.release();

selectBulkBeforeSnapshots は FOR UPDATE を付けています。

SELECT id, archived, metadata_json
FROM documents
WHERE tenant_id = $1
  AND workspace_id = $2
  AND archived IS DISTINCT FROM $3
FOR UPDATE

FOR UPDATE を付ければ「②の UPDATE が完了するまで、ロックした既存行を他のトランザクションが変更できない」と想定していました。これは正しいです。

しかし、「①の時点で存在しなかった行が、② の UPDATE に含まれることを防ぐ」わけではありません。

ここが、行ロックと述語ロックの違いです。

行ロック:
  既に存在する行を守る

述語ロック:
  条件に一致する範囲へ後から入る行まで含めて守る

PostgreSQL の SELECT ... FOR UPDATE は、通常の使い方では前者です。WHERE archived IS DISTINCT FROM $3 という条件そのものを「予約」するわけではありません。

READ COMMITTED でのファントムインサート

PostgreSQL のデフォルト分離レベルは READ COMMITTED です。

READ COMMITTED で UPDATE を実行すると、UPDATE はクエリ開始時点での「最新のコミット済み状態」を参照します。FOR UPDATE でロックした行セットは「ロック取得時点で存在した行」に限られ、その後に別トランザクションが INSERT した行はロック対象外です。

UPDATE の WHERE 条件は SELECT FOR UPDATE の条件と同じです。しかし UPDATE は最新の DB 状態に対して評価されるため、途中で INSERT された行 4 も UPDATE の対象になります。

結果として:

  • beforeSnapshots には行 4 が含まれていない
  • bulkArchiveOperation の affectedIds には行 4 が含まれている
  • row_audit_details は行 4 の変更前状態を持たない

実際の影響

この実装では、一括操作の取り消しのために row_audit_details に変更前状態を記録しています。行 4 の変更前状態が欠落すると、後から一括操作を取り消すときに、行ごとの復元ではなく「一律の状態変更」に降格します。

// bulk-archive-revert.ts (簡略化)
const details = await getAuditLogDetails(pool, auditId);

if (details.length === 0) {
  // 行ごとの記録なし → 全行を一律の archived 状態へ戻す
  console.warn('[bulk-archive] falling back to uniform archived update');
  await bulkRevertArchived(pool, {
    tenantId,
    workspaceId,
    ids: affectedIds,
    archived: revertArchived,
  });
} else {
  // 行ごとの復元: 各行を before_archived / before_metadata_json で UPDATE
  // details に存在しない行 (行 4) は matchedDetails に含まれずスキップされる
  const matchedDetails = affectedIds
    .map((id) => detailMap.get(id))
    .filter((d): d is NonNullable<typeof d> => d !== undefined);
  // ...
}

一律の状態変更は多くの場合で近い結果になりますが、行ごとの変更前状態を再現するものではありません。たとえば、操作前から既にアーカイブ済みだった行まで同じ扱いになり、意味論のズレが生じます。

分離レベルとの関係

今回の問題は、SELECT FOR UPDATE が既存行だけをロックし、その後の UPDATE が READ COMMITTED のルールで最新のコミット済み行を見に行くことで起きます。

PostgreSQL の分離レベルを簡単に整理すると、次のようになります。

分離レベル 同一トランザクション内の読み取り ファントムリード
READ COMMITTED 文ごとに新しいスナップショットを見る 発生しうる
REPEATABLE READ トランザクション内で同じスナップショットを見る PostgreSQL では発生しない
SERIALIZABLE 直列実行と同等になるように扱う 発生しない

REPEATABLE READ でも、途中で INSERT された行は同じトランザクション内の後続クエリから見えません。

この具体例だけなら、REPEATABLE READ でも「途中で INSERT された行が後続の UPDATE に混ざる」ことは避けられます。

ただし、複数のトランザクションが絡む不変条件まで含めて安全にしたい場合は、SERIALIZABLE まで上げる方が意図は明確です。あるいは、後述するように更新対象の id 集合を先に確定します。

SERIALIZABLE を使う場合は、トランザクション開始直後、最初のクエリを実行する前に分離レベルを指定します。

await client.query('BEGIN');
await client.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
// 以降の SELECT / UPDATE は同一スナップショット上で評価される

SERIALIZABLE では、実行結果が直列実行と同じになるように PostgreSQL が検査します。競合がある場合は serialization failure (40001) が発生するため、アプリ側でのリトライが必要になります。

現実的な対処法

対処法 A: id 集合を先に確定して UPDATE に渡す

今回のように「変更前状態を記録した行だけを UPDATE したい」場合は、FOR UPDATE で取得した id 集合を UPDATE の WHERE 条件に使うのが分かりやすいです。

SELECT FOR UPDATE で対象行をロックし、その時点の id 集合を確定します。その後、UPDATE は同じ WHERE 条件を再評価するのではなく、その id 集合に限定します。

// ① FOR UPDATE で変更前状態を取得し、id 集合を確定する
const beforeSnapshots = await selectBulkBeforeSnapshots(client, params);
const lockedIds = beforeSnapshots.map((r) => r.id);

if (lockedIds.length === 0) {
  await client.query('COMMIT');
  client.release();
  return { affected: 0, failed: [], affectedIds: [] };
}

// ② UPDATE を lockedIds に限定する（新規 INSERT 行は lockedIds に含まれない）
const result = await client.query<{ id: string }>(
  `UPDATE documents
   SET archived = $3, updated_by = $4, updated_at = now(), version = version + 1
   WHERE id = ANY($5::uuid[])
     AND tenant_id = $1
     AND workspace_id = $2
   RETURNING id`,
  [tenantId, workspaceId, archivedValue, updatedBy, lockedIds],
);

id 集合を UPDATE の WHERE に渡すことで、FOR UPDATE 後に INSERT された行は lockedIds に含まれないため UPDATE 対象外になります。

この方法は、分離レベルを上げずに「変更前状態を記録した行」と「実際に UPDATE する行」を一致させられます。

対処法 B: SERIALIZABLE 分離レベル

await client.query('BEGIN');
await client.query('SET TRANSACTION ISOLATION LEVEL SERIALIZABLE');
const beforeSnapshots = await selectBulkBeforeSnapshots(client, params);
const result = await bulkArchiveOperation(client, params);
await client.query('COMMIT');

シンプルですが、一括操作の頻度が高い場合に serialization failure のリトライが必要です。

更新対象を id 集合で明示できるなら、まずは対処法 A の方が実装の意図が読みやすくなります。

一方で、複数のクエリ全体を「この条件で見た世界」として扱いたい場合や、id 集合に閉じられない不変条件がある場合は、SERIALIZABLE を検討します。

READ COMMITTED でズレが起きる条件の整理

FOR UPDATE があっても READ COMMITTED でズレが起きる条件は、次の 3 つです。

  1. SELECT FOR UPDATE と UPDATE が 同じ WHERE フィルタ を使っている
  2. SELECT FOR UPDATE の後から UPDATE の前に、別トランザクションが新しい行を INSERT してコミットする
  3. UPDATE が WHERE フィルタで評価した最新の DB 状態 を参照する（READ COMMITTED の動作）

この 3 条件が揃わない場合は影響しません。たとえば SELECT FOR UPDATE で取得した id リストを UPDATE の WHERE id = ANY(...) に使えば、新規 INSERT 行は対象外になります（対処法 A）。

まとめ

  • SELECT ... FOR UPDATE は、既に存在する行をロックするには有効です。テナント別連番のように、対象行が先に存在する設計ではよく効きます
  • 一方で、SELECT ... FOR UPDATE は「ロック取得後に INSERT された行が UPDATE に含まれること」を防ぐものではありません
  • READ COMMITTED（PostgreSQL のデフォルト）では、UPDATE は「クエリ開始時点での最新コミット済み状態」を参照するため、途中 INSERT された行も UPDATE 対象になります
  • 変更前状態の記録と UPDATE を完全に一致させる必要がある場合は、FOR UPDATE で取得した id 集合を UPDATE の WHERE 条件に明示的に渡す設計が分かりやすいです
  • SERIALIZABLE も選択肢ですが、serialization failure (40001) のリトライを前提にします

参考資料

0
0
0

Register as a new user and use Qiita more conveniently

  1. You get articles that match your needs
  2. You can efficiently read back useful information
  3. You can use dark theme
What you can do with signing up
Sign upLogin
0
0

Delete article

Deleted articles cannot be recovered.

Draft of this article would be also deleted.

Are you sure you want to delete this article?