PostgreSQL の競合書き込み

PostgreSQL の競合書き込み は何をしますか？

重要: 「コードで読み取り → チェック → 書き込み」を行わないでください。Read Committed では、他のトランザクションによる変更が失われます。不変条件の種類に応じて適切な手段を選択してください:

不変条件	手段
条件がまだ真の場合に行を変更	1 つの UPDATE ... WHERE <条件> + 影響行数の確認
読み取り、コードで判断、同じ行を書き込み	同一トランザクション内の SELECT ... FOR NO KEY UPDATE と書き込み
重複を禁止	一意インデックス + INSERT ... ON CONFLICT
期間の重複を禁止	EXCLUDE USING gist (btree_gist) を使用した除外制約
SQL 制約で表現できない行セット全体のルール	全参加者によるシリアライザブル分離レベル + リトライ
タスクキュー	FOR UPDATE SKIP LOCKED
単一のハンドラによる排他制御	pg_advisory_xact_lock

データベース側の制約はコードよりも信頼性が高く、あらゆる分離レベルとクライアントで不変条件を維持します。Go コードのトランザクションとリトライ処理はスキル pgx-txmanager を参照してください。アサーションの確認は CHECKS.md を参照してください。

分離レベル

• Read Committed (デフォルト): 各コマンドは最新のスナップショットを参照します。占有された行に対する UPDATE は、他のトランザクションを待ってから行を再読み取りし、WHERE 条件を再評価します。
• Repeatable Read と Serializable は再読み取りを行いません。競合が発生するとエラー 40001 ("could not serialize access") が発生します。Serializable はこのエラーがより頻繁に発生します。分離レベルを上げたら、すぐにトランザクション全体のリトライ処理を実装してください。そうしないと、ユーザーにエラーが表示されます。40001 は COMMIT 時にも発生する可能性があります。
• Repeatable Read のスナップショットは BEGIN 直後ではなく、BEGIN 後の最初のコマンドで構築されます。 最初のクエリで読み取りを実行するタイミングが重要です。
• Serializable は Serializable トランザクション間でのみ結果を保証します。 Serializable で不変条件を保護する場合、その不変条件に触れる すべての トランザクションは Serializable で実行する必要があります。
• Serializable 内では EXPLAIN を確認してください。 Seq Scan はテーブル全体に述語ロックをかけ、40001 の発生率を高めます。インデックスを使用した読み取りは、読み取られた行とインデックスページのみをロックします。適切なインデックスを作成してください。小規模なテーブルでは Seq Scan も許容されます。

条件が真の場合に変更: UPDATE 内の条件

データベースには計算済みの値ではなく、アクションを渡します: SET sent = sent + 1、SET left = left - $1 WHERE left >= $1、WHERE id = $1 AND status = 'free'。影響を受けた行が 0 の場合 (RowsAffected() == 0 または RETURNING で pgx.ErrNoRows) は、条件が満たされなかったか、行が存在しないことを意味します。これは成功ではなく、ドメインエラーです。

tag, err := q.Exec(ctx,
	`UPDATE seats SET status = 'booked', customer_id = $2
	 WHERE id = $1 AND status = 'free'`,
	seatID, customerID,
)
if err != nil {
	return fmt.Errorf("book seat %d: %w", seatID, err)
}
if tag.RowsAffected() == 0 { // 席はすでに予約済みか、存在しない
	return fmt.Errorf("book seat %d: %w", seatID, ErrConflict)
}

Repeatable Read と Serializable では、同じ競合は 40001 で終了するため、リトライが必要です。

同じ行を読み取り、書き込み

SELECT ... FOR NO KEY UPDATE (ロックモードの詳細は「行ロックモード」セクションを参照) と書き込みを同じトランザクション内で実行します (trManager.Do)。オートコミットでは、SELECT の直後にロックが解除されます。まだ存在しない行はロックで保護されません。重複した挿入を防ぐのは、一意インデックスまたは制約のみです。

行セットに基づく判断 (書き込みスキュー)

「担当者が空いている」、「予約が期間内に収まる」、「シフトに残りの当直者がいる」などのシナリオ: 2 つのトランザクションが同じスナップショットを読み取り、それぞれが 自分の 行を書き込み、両方がコミットします。Read Committed では、これにより 2 つの重複する予約が発生する可能性があります。Repeatable Read でも防げません。同じ行への書き込みのみを検出するためです。

• 既存の行に基づく判断: 判断の基準となる行を正確にロックします (FOR NO KEY UPDATE / FOR UPDATE)。
• 行が存在しないことの判断 (「重複する予約がない」など): ロックするものは何もありません。制約が必要です:

  • 値の重複: 一意インデックス

  • 期間の重複: 除外制約。Read Committed では、2 番目のトランザクションは最初のトランザクションを待ち、23P01 エラーを受け取ります。シリアライザブル分離レベルやリトライは不要です:

    CREATE EXTENSION IF NOT EXISTS btree_gist; -- trusted: データベースに対する CREATE 権限が必要
    ALTER TABLE bookings ADD CONSTRAINT bookings_no_overlap
        EXCLUDE USING gist (resource_id WITH =, during WITH &&); -- during tstzrange
    

    23P01 (exclusion_violation) は、ドメインの競合エラーに変換します。

• ルールを制約で表現できない場合: すべての参加者にシリアライザブル分離レベルを使用し、40001 でリトライします。

行ロックモード

行をロックして読み取り、更新するが、外部キーに適した一意インデックス (PK、一意キー。部分インデックスや式インデックスは除く) の列を変更しない場合は、FOR NO KEY UPDATE を使用します。FOR UPDATE は DELETE や、そのような列の変更のために残しておきます。理由: 子行の挿入時の外部キーチェックは、親行に対して FOR KEY SHARE ロックを取得します。これは FOR UPDATE とは競合しますが、FOR NO KEY UPDATE とは競合しません。親行 (顧客、リソース、注文など) に対する不要な FOR UPDATE は、それを参照する行の挿入をブロックします。

タスクキュー

複数のワーカーが 1 つのステートメントでタスクを取得します:

UPDATE tasks
SET status = 'running', taken_at = now()
WHERE id = (
	SELECT id FROM tasks
	WHERE status = 'ready'
	ORDER BY id
	LIMIT 1
	FOR UPDATE SKIP LOCKED
)
RETURNING id

• SKIP LOCKED は占有された行をスキップするため、ワーカーは互いに待機しません。同一トランザクション内の「SELECT ... FOR UPDATE SKIP LOCKED、続いて UPDATE」という組み合わせも正しく動作します。ロックなしの SELECT と別の UPDATE は使用できません。2 つのワーカーが同じタスクを取得する可能性があります。
• サブクエリ内の ORDER BY は、予測可能な順序を提供します。
• ワーカーはタスクを保持したままクラッシュする可能性があります。一般的な方法: taken_at によるリース期限を設定し、running 状態でリース期限を超えたタスクを再び ready と見なします。
• SKIP LOCKED はデータの一貫性のないビューを提供するため、キューのみで使用してください。

他人のロックで待機しない

デフォルトでは、クエリはロックを無期限に待機し、ユーザーにはアプリケーションがハングしたように見えます。

• トランザクション内で SET LOCAL lock_timeout を使用します。 セッションパラメータではありません。コネクションプールから取得した接続は、後で他のクエリに使用される可能性があるためです。タイムアウトは、トランザクション全体ではなく、ロック待機のたびに適用されます。
• UPDATE と DELETE には NOWAIT オプションはありません。 即座にエラーを返す必要がある場合は、同じトランザクション内でこれらの行に対して SELECT ... FOR UPDATE NOWAIT を最初に実行します。
• NOWAIT と SKIP LOCKED は行にのみ影響します。 クエリはテーブルロック (例: ALTER TABLE) を通常どおり待機します。したがって、これらを使用する場合でも lock_timeout が必要です。
• これらの場合のエラーは 55P03 (lock_not_available) です。トランザクション全体をリトライしてください。

return s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	// 定数。ユーザー入力は使用しない。操作に合わせて値を調整
	if _, err := q.Exec(ctx, `SET LOCAL lock_timeout = '1s'`); err != nil {
		return err
	}
	return s.repo.MoveBooking(ctx, from, to)
})

待機キューは公平ではありません。 新しい FOR SHARE / FOR KEY SHARE は、ホットな行に対して待機中の UPDATE を追い越す可能性があり、そのようなロックのストリームが UPDATE を無期限にブロックする可能性があります。待機中の順序は保証されません。ホットな行 (共有カウンター、人気商品の在庫など) の場合は、lock_timeout を設定し、トランザクションをリトライしてください。

ハングアップの分析: pg_locks では行ロックは直接表示されません。待機中のプロセスは、ロック保持者の transactionid を待機しているものとして表示されます。誰がブロックしているかは、pg_blocking_pids(pid) で確認できます (頻繁に呼び出さないでください)。テーブルの行ロックは、contrib の pgrowlocks 拡張機能で表示できます (スーパーユーザーがインストールする必要があります)。

アドバイザリロック

「単一のハンドラのみ」 (シングルワーカーの outbox、マイグレーター) の場合は、トランザクション内で pg_advisory_xact_lock を使用します。ロックはコミットまたはロールバック時に自動的に解放されます。

const advisoryOutboxRelay int64 = 1 // サブシステムの固定キー

err := s.trManager.Do(ctx, func(ctx context.Context) error {
	q := s.getter.DefaultTrOrDB(ctx, s.pool)
	if _, err := q.Exec(ctx, `SELECT pg_advisory_xact_lock($1)`, advisoryOutboxRelay); err != nil {
		return err
	}
	return s.relayBatch(ctx) // 1 バッチ。ロックはトランザクションと共に解放される
})

• セッションレベルの pg_advisory_lock は、専用の接続でのみ使用してください。 pool.Acquire で取得した接続で使用します。ロックした回数だけ、同じ接続で解放する必要があります。セッションレベルのロックは COMMIT やロールバックでは解放されず、同じセッションで再取得できます。プールでは、ロックを保持したまま接続が返却され、解放処理が別の接続に送信されると、ロックが「リーク」します。
• 無限ループのワーカーのためにロックを保持してトランザクションを開いたままにしないでください。作業のバッチごとに xact ロックを取得してください。長期稼働するリーダーには、専用接続でセッションレベルのロックを使用します。
• キーは、サブシステムごとの固定の数値定数、または (int, int) のペアにします。hashtext は使用しないでください。この関数はドキュメント化されておらず、32 ビットハッシュは衝突を引き起こす可能性があります。

クエリ内の関数

テーブルを読み取り、テーブルの読み取りと一緒にクエリ内で呼び出される PL/pgSQL または SQL 関数は、STABLE とマークしてください。VOLATILE は関数内の各クエリに対して新しいスナップショットを取得するため、Read Committed ではテーブルのデータと関数のデータが異なる時点のものになる可能性があります。IMMUTABLE はテーブルを読み取らない関数にのみ使用してください。VOLATILE は副作用のある関数に使用します。

エラーコード

コード	意味	対処法
40001 serialization_failure	Repeatable Read / Serializable での競合	トランザクション全体をリトライ
40P01 deadlock_detected	デッドロック	トランザクション全体をリトライ。ロックは常に同じ順序で取得する
55P03 lock_not_available	NOWAIT、lock_timeout	トランザクション全体をリトライするか、「ビジー」を返す
23505 unique_violation	重複	ドメインの競合。予期される重複は ON CONFLICT で処理
23P01 exclusion_violation	除外制約の違反 (重複)	ドメインの競合