PostgreSQL であなたの UPDATE が終わらない理由〜トランザクション、ロック、MVCC を実験で理解する〜

アプリケーションから次のようなSQLを実行したのに、なかなか応答が返ってこないことがあります。

UPDATE accounts SET balance = balance - 100 WHERE id = 1;

こういうときに原因の一つとして早めに確認しておきたいものがロック待ちです。ロック待ちとは、あるトランザクションが更新中の行などを、別のトランザクションが同時に変更しようとして、先のトランザクションが終わるまで待っている状態です。PostgreSQLはデータの矛盾を防ぐため、同じ行を複数の処理が同時に勝手に書き換えられないようにします。そのため、後から実行したUPDATEは、必要なロックを取得できるまで待つことがあります。

本記事では、同じ行へのUPDATEによってロック待ちが起きる状況を作り、どこを確認すれば原因を切り分けられるのかを説明します。

対象読者は、PostgreSQLを使い始めたアプリケーション開発者や運用初心者です。SQLは書けるものの、トランザクション、ロック、MVCC、監視ビューの関係をまだ整理できていない人を想定しています。

実験の前に

この実験では、psqlのセッションを3つ使います。

セッションA:先に行を更新して、ロックを保持する
セッションB:同じ行を更新しようとして待つ
セッションC: PostgreSQLの状態を確認する

ここで、実験に入る前にトランザクションについて簡単に整理します。

トランザクションとは、複数のSQL操作をひとまとまりの処理として扱う仕組みです。たとえば、口座Aから100円を引き、口座Bに100円を足す処理を考えます。この2つの更新は、片方だけの成功は許されません。引き落としだけ成功して、入金が失敗すると、データの整合性が崩れるためです。そこで、PostgreSQLでは、関連するSQLを1つのトランザクションとして扱えます。

BEGINを実行すると、トランザクションが始まります。

BEGIN;

トランザクション内で実行した変更は、COMMITするまで確定しません。

COMMIT;

COMMITは、トランザクション内で行った変更を確定する操作です。COMMIT すると、他のセッションからもその変更が見えるようになります。一方、変更を取り消したい場合はROLLBACKを実行します。

ROLLBACK;

ROLLBACKは、トランザクション内で行った変更を取り消す操作です。ROLLBACKすると、そのトランザクションで実行した更新はなかったことになります。

本記事のロック待ちを理解するうえで重要なのは、次の点です。トランザクション中にUPDATE文の実行が終わっていても、COMMITまたはROLLBACKを実行するまで、トランザクションは終わりません。そして、トランザクションが終わるまで、更新に関係するロックが保持されることがあります。つまり、次のような状態が問題になります。

BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;
-- COMMITもROLLBACKもしていない

この状態では、UPDATE 自体は終わっています。しかし、トランザクションはまだ終わっていません。そのため、別のセッションが同じ行を更新しようとすると、先のトランザクションが終わるまで待つことがあります。

本記事では実験を通して、この「トランザクションが終わっていないために、別の更新が待たされる状態」を意図的に作ります。つまり、単に UPDATEを実行するだけでなく、BEGINでトランザクションを開始し、あえて COMMITせずに残す操作が重要になります。

以降の実験では、セッションAがトランザクションを開いたまま行を更新し、セッションBが同じ行を更新しようとして待つ、という流れを何度か使います。

実験の準備

まず、検証用のテーブルを作ります。ここでは、口座残高を表すaccountsテーブルを使います。

-- accountsテーブルを作成
CREATE TABLE accounts (
  id integer PRIMARY KEY
  , owner_name text NOT NULL
  , balance integer NOT NULL
);

-- データを2件登録
INSERT INTO accounts (id, owner_name, balance)
VALUES
  (1, 'alice', 1000),
  (2, 'bob', 2000);

初期状態を確認します。

SELECT * FROM accounts ORDER BY id;
 id | owner_name | balance
----+------------+---------
  1 | alice      |    1000
  2 | bob        |    2000
(2 rows)

以降は各実験を始める前に、セッションAとセッションBで次を実行しておくと安全です。

-- 各実験の前にROLLBACKを実行してトランザクションを終了しておく
-- Session A/B
ROLLBACK;

すでにトランザクションが終わっている場合は、警告が出ることがあります。その場合でも、この実験では問題ありません。

合わせて、セッションAとセッションBのPIDも確認しておきます。

-- セッションA
SELECT pg_backend_pid();
 pg_backend_pid
----------------
             52
(1 row)


-- セッションB
SELECT pg_backend_pid();
 pg_backend_pid
----------------
             57
(1 row)

実験1:同じ行をUPDATEすると片方が待つ

この実験で確認すること

この実験では、同じ行を2つのトランザクションからUPDATEすると、後から実行した UPDATEが待たされることを確認します。ポイントは、先にUPDATEしたセッションがCOMMITするまで、後続の UPDATEが進めないことです。

手順1:セッションAでトランザクションを開始する

まず、セッションAでトランザクションを開始します。

-- Session A
BEGIN;

この時点で、セッションAはトランザクションの中に入ります。まだデータは更新していません。

手順2:セッションAでid = 1の行を更新する

続けて、セッションAでid = 1の行を更新します。

-- Session A
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

このUPDATE自体はすぐに終わります。ただし、まだCOMMITしていません。そのため、セッションA のトランザクションは開いたままです。

ここが重要です。UPDATE文の実行が終わっていても、トランザクションが終わっていなければ、PostgreSQL はその更新に関係するロックを保持し続けます。

手順3:セッションBで同じ行を更新する

次に、セッションBでもトランザクションを開始します。

-- Session B
BEGIN;

そのあと、セッションBで同じid = 1の行を更新します。

-- Session B
UPDATE accounts SET balance = balance - 50 WHERE id = 1;

このUPDATEは応答が返ってきません。アプリケーションから見ると、「UPDATEが終わらない」状態に見えます。

なぜ待つのか

セッションAは、id = 1の行を先に更新しました。しかし、まだCOMMITも ROLLBACKもしていません。この状態で、セッションBが同じ行を更新しようとすると、PostgreSQLはセッションB の更新をすぐには進めません。セッションAの更新が確定するのか、取り消されるのかがまだ分からないからです。そのため、セッションBはセッションA のトランザクションが終わるのを待ちます。この待ちが、ロック待ちです。

この実験で分かったこと

同じ行を同時に更新しようとすると、後から実行したUPDATEが待つことがあります。ただし、画面上で UPDATEが返ってこないだけでは、SQLの処理が重いのか、ロックを待っているのかは分かりません。

次は、PostgreSQLの中でその待ち状態を確認します。

実験2: pg_stat_activityで何を待っているかを確認する

この実験で確認すること

この実験では、待っているセッションが本当にロック待ちなのかを確認します。使うのは、pg_stat_activityです。pg_stat_activity は、PostgreSQLに接続している各セッションの状態を確認できるビューです。

確認するSQL

セッションCから、次のSQLを実行します。

-- Session C
SELECT
  pid
  , state
  , wait_event_type
  , wait_event
  , xact_start
  , query_start
  , query
FROM
  pg_stat_activity
WHERE
  datname = current_database()
  AND pid <> pg_backend_pid()
  AND query LIKE '%UPDATE accounts%'
ORDER BY
  query_start;
 pid |        state        | wait_event_type |  wait_event   |          xact_start           |          query_start          |                           query
-----+---------------------+-----------------+---------------+-------------------------------+-------------------------------+-----------------------------------------------------------
  52 | idle in transaction | Client          | ClientRead    | 2026-06-15 05:57:26.539576+00 | 2026-06-15 05:57:26.541089+00 | UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  57 | active              | Lock            | transactionid | 2026-06-15 05:57:31.885276+00 | 2026-06-15 05:57:31.886839+00 | UPDATE accounts SET balance = balance - 50 WHERE id = 1;
(2 rows)

まず確認する列

ここで最初に確認する列は、次の3つです。

state
wait_event_type
wait_event

stateは、そのセッションの大まかな状態を示します。ここでは、pid = 57のセッションBは state = 'active'になっています。これは、PostgreSQLがそのSQLを処理中として扱っているという意味です。ただし、active
だからといって、CPUを使って計算し続けているとは限りません。何かを待っていても、SQLの実行中であれば
activeと表示されることがあります。

そこで、wait_event_typeとwait_eventを合わせて確認します。

結果の読み方

セッションBの行を確認すると、次のようになっています。

wait_event_type = Lock
wait_event      = transactionid

wait_event_type = Lockは、ロックを待っていることを示します。wait_event = transactionid は、別のトランザクションが終わるのを待っていることを示します。この例では、セッションBがセッションAのトランザクション終了を待っています。

つまり、セッションBのUPDATEは、検索や計算に時間がかかっているのではありません。ロック待ちで止まっています。

この実験で分かったこと

UPDATEが返ってこないときは、まずpg_stat_activityを確認します。特に、state
だけで判断しないことが重要です。wait_event_typeと
wait_eventを合わせて確認すると、そのセッションが何を待っているのかを確認できます。

次は、どのセッションが待たせているのかを確認します。

実験3: pg_blocking_pids()で待たせている相手を探す

この実験で確認すること

この実験では、待っているセッションを止めている相手を特定します。待っている側を、ここではウェイターと呼びます。待たせている側を、ここではブロッカーと呼びます。

PostgreSQLでは、pg_blocking_pids()を使うと、当該PIDのブロッカーPIDを確認できます。

確認するSQL

セッションCで次のSQLを実行します。pg_blocking_pids()は引数に指定したPIDのブロッカーPIDの配列を返します。

-- Session C
WITH activity AS (
  SELECT
    pid
    , state
    , wait_event_type
    , wait_event
    , pg_blocking_pids(pid) AS blocking_pids
    , query
  FROM
    pg_stat_activity
  WHERE
    datname = current_database()
    AND pid <> pg_backend_pid()
)
SELECT
  *
FROM
  activity
WHERE
  query LIKE '%UPDATE accounts%'
ORDER BY
  pid;
 pid |        state        | wait_event_type |  wait_event   | blocking_pids |                           query
-----+---------------------+-----------------+---------------+---------------+-----------------------------------------------------------
  57 | active              | Lock            | transactionid | {52}          | UPDATE accounts SET balance = balance - 50 WHERE id = 1;
  52 | idle in transaction | Client          | ClientRead    | {}            | UPDATE accounts SET balance = balance - 100 WHERE id = 1;

結果の読み方

pid = 57の行を確認すると、blocking_pidsが{52}になっています。これは、pid = 57 のセッションが、pid = 52のセッションで待たされているという意味です。

この例では、次の関係になります。

ウェイター（待っている側）:     pid = 57
ブロッカー（待たせている側）:   pid = 52

つまり、pid = 57のSQLだけを確認しても原因は分かりません。次に確認すべきなのは、ブロッカーのpid = 52の状態です。

実験4: idle in transactionはロックを保持し得る状態

この実験で確認すること

この実験では、idle in transactionの意味を確認します。idle in transactionは、トランザクションの中にいるが、現在はSQL を実行していない状態です。何もしていないように見えても、トランザクションが終わっていなければ、ロックを保持していることがあります。

状況の確認

ここまでの状態では、セッションAがid = 1の行を更新し、まだCOMMITしていません。セッションB は、同じ行を更新しようとして待っています。

セッションCから次のSQLを実行します。実験3のSQLに表示列(xact_start, state_change)を追加しています。

-- Session C
WITH activity AS (
  SELECT
    pid
    , state
    , wait_event_type
    , wait_event
    , pg_blocking_pids(pid) AS blocking_pids
    , xact_start
    , state_change
    , query
  FROM
    pg_stat_activity
  WHERE
    datname = current_database()
    AND pid <> pg_backend_pid()
)
SELECT
  *
FROM
  activity
WHERE
  query LIKE '%UPDATE accounts%'
ORDER BY
  xact_start NULLS LAST;
 pid |        state        | wait_event_type |  wait_event   | blocking_pids |          xact_start           |         state_change          |                           query
-----+---------------------+-----------------+---------------+---------------+-------------------------------+-------------------------------+-----------------------------------------------------------
  52 | idle in transaction | Client          | ClientRead    | {}            | 2026-06-15 05:57:26.539576+00 | 2026-06-15 05:57:26.542127+00 | UPDATE accounts SET balance = balance - 100 WHERE id = 1;
  57 | active              | Lock            | transactionid | {52}          | 2026-06-15 05:57:31.885276+00 | 2026-06-15 05:57:31.886842+00 | UPDATE accounts SET balance = balance - 50 WHERE id = 1;
(2 rows)

結果の読み方

pid = 52のセッションは、state = 'idle in transaction'になっています。

これは、次の状態を意味します。

トランザクションはまだ開いている
ただし、現在はSQLを実行していない

また、この行ではwait_event_type = Client、wait_event = ClientReadになっています。これは、PostgreSQL がクライアントから次の命令を待っていることを示します。つまり、セッションAはデータベース内部でロックを待っているのではなく、クライアント側から次の SQL、COMMIT、またはROLLBACKが送られるのを待っている状態です。

この状態でも、セッションAはロックを解放していません。ロックは、トランザクションがCOMMITまたは ROLLBACKで終わるまで保持されます。そのため、セッションBは待ち続けます。

なぜ問題になりやすいのか

idle in transactionは、アプリケーションの作りによって長引くことがあります。

たとえば、次のような処理です。

トランザクションを開始したあとに、外部APIの応答を待つ
トランザクションを開始したあとに、ユーザー入力を待つ
例外が発生したのに、ROLLBACKしないまま接続を残す

これらは、ロックを保持したまま待ち時間を伸ばす原因になります。

実務で確認する列

実務でidle in transactionを見つけたときは、すぐにセッションを終了する前に、まず次の列を確認します。

xact_start
state_change
query
application_name
client_addr

特に
xact_start
が古い場合は、長時間トランザクションが開いたままになっている可能性があります。ただし、セッションを強制終了すると、アプリケーション側ではエラーになります。注文更新や残高更新の途中であれば、業務処理にも影響します。そのため、まずはどのアプリケーション、どの接続元、どの処理がトランザクションを開いたままにしたのかを特定することが重要です。

timeout設定について

idle in transactionを長時間残したくない場合は、idle_in_transaction_session_timeout を使う方法もあります。この設定は、トランザクション内で何もしていないセッションを、指定時間後に切断します。ただし、切断された側のアプリケーションにはエラーとして見えます。本番環境で使う場合は、事前に影響を確認する必要があります。

この実験で分かったこと

idle in transaction
は、何もしていない安全な状態とは限りません。トランザクションが終わっていなければ、ロックを保持し続けることがあります。ロック待ちを調べるときは、待っている側だけでなく、待たせている側の
stateとxact_startを確認します。

次は、通常のロック待ちとデッドロックの違いを確認します。

実験5:通常のロック待ちとデッドロックは違う

この実験で確認すること

ここまでの実験では、セッションBがセッションAを一方的に待っていました。この場合、セッションAがCOMMITまたは ROLLBACKすれば、セッションBは進めます。これは通常のロック待ちです。

一方で、デッドロックは違います。デッドロックは、複数のトランザクションが互いに相手を待ってしまい、どちらも先へ進めなくなる状態です。

実験前の準備

セッションAとセッションBの両方でROLLBACKを実行し、前の実験の状態を終わらせます。

-- Session A / Session B
ROLLBACK;

手順1:セッションAがid = 1を更新する

-- Session A
BEGIN;
UPDATE accounts SET balance = balance - 100 WHERE id = 1;

セッションAは、id = 1の行を更新します。まだCOMMITはしません。

手順2:セッションBがid = 2を更新する

-- Session B
BEGIN;
UPDATE accounts SET balance = balance - 50 WHERE id = 2;

セッションBは、id = 2の行を更新します。こちらも、まだCOMMITはしません。

この時点では、セッションAとBは別々の行を更新しているため、互いに待っていません。

手順3:セッションAがid = 2を更新しようとする

次に、セッションAがid = 2の行を更新しようとします。

-- Session A
UPDATE accounts SET balance = balance + 100 WHERE id = 2;

id = 2は、すでにセッションBが更新しています。そのため、セッションAはセッションBのトランザクション終了を待ちます。

この時点では、まだデッドロックではありません。セッションAが、セッションBを一方的に待っているだけです。

手順4:セッションBがid = 1を更新しようとする

最後に、セッションBがid = 1の行を更新しようとします。

-- Session B
UPDATE accounts SET balance = balance + 50 WHERE id = 1;

id = 1は、すでにセッションAが更新しています。そのため、セッションBはセッションAのトランザクション終了を待ちます。

これで、次の状態になります。

セッションAは、セッションBを待っている
セッションBは、セッションAを待っている

これがデッドロックです。

デッドロック時のエラー

PostgreSQLはデッドロックを検出すると、どちらか一方のトランザクションを中止します。

エラーの例です。

ERROR:  deadlock detected
DETAIL:  Process 57 waits for ShareLock on transaction 2317; blocked by process 52.
Process 52 waits for ShareLock on transaction 2318; blocked by process 57.
HINT:  See server log for query details.
CONTEXT:  while updating tuple (0,1) in relation "accounts"

このエラーが出たあとは、エラーになったトランザクションはそのまま続けられません。次の実験へ進む前に、セッションAとBの両方で ROLLBACKを実行します。

-- Session A / Session B
ROLLBACK;

通常のロック待ちとの違い

通常のロック待ちは、ブロッカー側が終われば解消します。一方、デッドロックでは、互いに相手を待っています。そのままではどちらも進めません。

そのため、PostgreSQL はどちらか一方を中止して、待ち続ける状態を解消します。どちらのトランザクションが中止されるかをアプリケーション側で決め打ちしてはいけません。デッドロックで失敗した場合は、失敗したSQLだけを再実行するのではなく、トランザクション全体を最初からやり直す必要があります。

デッドロックを減らす考え方

複数行を更新する処理では、更新する順序をそろえることが基本です。たとえば、ある処理はid = 1のあとに id = 2を更新し、別の処理はid = 2のあとにid = 1を更新すると、デッドロックが起きやすくなります。

更新順序を決められるなら、常に同じ順序で更新します。たとえば、常にidの小さい順に更新するなどが考えられます。

lock_timeoutについて

通常のロック待ちが長すぎる場合の安全装置として、lock_timeoutがあります。

SET lock_timeout = '3s';

lock_timeoutは、ロックを取得できない状態が指定時間を超えたときに、そのSQL をエラーにします。これは、デッドロックを検出する設定ではありません。ロック待ちが長くなりすぎたSQLを失敗させるための設定です。また、statement_timeout との関係にも注意が必要です。statement_timeoutは、SQL全体の実行時間に対する制限です。lock_timeout
は、ロック取得待ちに対する制限です。statement_timeoutが
lock_timeout以下に設定されていると、ロック待ち専用のエラーより先に statement_timeoutが発生します。ロック待ちを切り分けたい場合は、両者の値を分けて設定します。

この実験で分かったこと

通常のロック待ちは、ブロッカー側が終われば解消します。一方、デッドロックは、互いに相手を待つため、そのままでは解消しません。PostgreSQL はどちらか一方のトランザクションを中止します。アプリケーションでは、デッドロックやロックタイムアウトで失敗した場合に、トランザクション全体をやり直せるようにしておく必要があります。

次は、書き込みではなく、読み取りがどう見えるのかを確認します。

実験6:通常のSELECTは止まらない

この実験で確認すること

ここまでの実験では、UPDATE同士の待ちを見てきました。では、あるセッションが行を更新してまだ COMMITしていないとき、別のセッションがその行をSELECTするとどうなるのでしょうか。

FOR UPDATEなどを付けない通常のSELECTは、多くの場合、更新中の行を待たずに結果を返します。これは、PostgreSQL が、読み取り時点で見えてよい行の版を選んで読みます。この仕組みをMVCC (Multi-Version Concurrency Control) と呼びます。日本語では、多版型同時実行制御と呼ばれます。

以降では、「見えてよい行の版を読む仕組み」を簡単に説明します。

実験前の準備

セッションAとセッションBの両方でROLLBACKを実行します。

-- Session A / Session B
ROLLBACK;

手順1:セッションAでid = 1を更新する

まず、更新前の値を確認します。

-- Session A
SELECT id, owner_name, balance FROM accounts WHERE id = 1;
 id | owner_name | balance
----+------------+---------
  1 | alice      |    1000
(1 row)

セッションAでトランザクションを開始します。

-- Session A
BEGIN;

次に、id = 1の残高を999に更新します。

-- Session A
UPDATE accounts SET balance = 999 WHERE id = 1;

ここでも、まだCOMMITはしません。つまり、セッションAの変更はまだ確定していません。

手順2:セッションBで通常のSELECTを実行する

セッションBから、BEGINでトランザクションを開始して、同じ行を読み取ります。

-- Session B
BEGIN;
SELECT id, owner_name, balance FROM accounts WHERE id = 1;
 id | owner_name | balance
----+------------+---------
  1 | alice      |    1000
(1 row)

セッションBのSELECTはロック待ちになりません。また、セッションAが更新したbalance = 999も見えません。セッションB からは、最後にCOMMIT済みの値である1000が見えます。

なぜ止まらないのか

セッションAのUPDATEは、まだCOMMITされていません。もし、セッションBから未コミットの 999が見えてしまうと、あとからセッションAが ROLLBACKしたときに困ります。存在しなかった変更を読んだことになるからです。そのため、PostgreSQLは通常の SELECTに対して、未コミットの変更を見せません。代わりに、読み取り時点で見えてよい、古い行の版を返します。

PostgreSQLのデフォルト設定では、各SQLを実行するたびに、その時点で COMMIT済みのデータを読みます。この設定を、トランザクション分離レベルの
Read Committed
と呼びます。トランザクション分離レベルとは、あるトランザクションから、他のトランザクションの変更をどこまで見せるかを決める設定です。

SELECT FOR UPDATEは待つ

ただし、すべてのSELECTが待たないわけではありません。SELECT FOR UPDATE は、読み取った行をあとで更新する前提でロックしようとします。

-- Session B
SELECT id, owner_name, balance FROM accounts WHERE id = 1 FOR UPDATE;

この場合は、セッションAのトランザクション終了を待ちます。通常の SELECTは、見えてよい行の版を読めるため、止まらないことが多いです。一方、SELECT FOR UPDATE は行をロックしようとするため、更新中の行と競合します。

この実験で分かったこと

PostgreSQLでは、未コミットの変更は通常のSELECTから見えません。この実験では、通常の SELECTは、更新中の行を待たずに、見えてよい行の版を読みます。一方、SELECT FOR UPDATE は行をロックしようとするため、対象の行が他のトランザクションで更新中の場合は、待つことがあります。

次は、ロック待ちしていたUPDATEが再開したときに、どの条件で更新されるのかを確認します。

実験7: UPDATEは待ったあとにWHEREを再確認する

この実験で確認すること

この実験では、ロック待ちしていたUPDATEが再開したときの動きを確認します。重要なのは、ロック待ちしていた UPDATEが、ロック待ちの前に見えていた古い行をそのまま更新するわけではない、という点です。

PostgreSQLのデフォルト設定では、ロック待ちが終わったあと、更新後の行に対してWHERE条件をもう一度確認します。

実験前の準備

セッションAとセッションBの両方でROLLBACKを実行します。

-- Session A / Session B
ROLLBACK;

手順1:セッションAがid = 1を更新する

まず、更新前の値を確認します。

-- Session A
SELECT id, owner_name, balance FROM accounts WHERE id = 1;
 id | owner_name | balance
----+------------+---------
  1 | alice      |    1000
(1 row)

セッションAでトランザクションを開始します。

-- Session A
BEGIN;

次に、id = 1の残高を1000にします。ここでは、同じ行を更新中の状態にするため、id = 1の残高を 1000に更新します。値は変わりませんが、UPDATEは実行されるため、この行はセッションA のトランザクションが終わるまで、他の更新と競合する状態になります。

-- Session A
UPDATE accounts SET balance = 1000 WHERE id = 1;

まだCOMMITはしません。

手順2:セッションBが条件付きUPDATEを実行する

セッションBでトランザクションを開始します。

-- Session B
BEGIN;

次に、残高が500以上の場合だけ、50減らすUPDATEを実行します。

-- Session B
UPDATE accounts
SET balance = balance - 50
WHERE id = 1
  AND balance >= 500
RETURNING *;

このUPDATEは待ちます。セッションAが同じid = 1の行を更新して、まだCOMMITしていないからです。

手順3:セッションAが残高を400にしてCOMMITする

セッションBが待っている間に、セッションAで同じ行の残高を400にします。

-- Session A
UPDATE accounts SET balance = 400 WHERE id = 1;

そのあと、セッションAのトランザクションを確定します。

-- Session A
COMMIT;

これで、セッションBの待ちが解除されます。

セッションBの結果

セッションBのUPDATEは再開します。ただし、セッションB は、ロック待ちの前に見えていた行をそのまま更新するわけではありません。セッションAがCOMMITした後の行に対して、WHERE 条件をもう一度確認します。

この時点で、id = 1のbalanceは400です。セッションBの条件は次のとおりでした。

WHERE id = 1
  AND balance >= 500

balance = 400なので、balance >= 500は成り立ちません。そのため、セッションBのUPDATEは0 行更新になります。

 id | owner_name | balance
----+------------+---------
(0 rows)

この動きが重要な理由

アプリケーションで避けたいのは、先にSELECTで値を確認し、その結果だけを前提にして、あとから無条件に UPDATEする書き方です。

BEGIN;
SELECT balance FROM accounts WHERE id = 1;

UPDATE accounts
SET balance = balance - 500
WHERE id = 1;
COMMIT;

この書き方では、SELECTしてから UPDATEするまでの間に、別のトランザクションが同じ行を更新する可能性があります。つまり、アプリケーションが事前に見た残高と、実際に UPDATEする時点の残高が同じとは限りません。

残高不足を防ぎたいなら、更新してよい条件をUPDATEのWHEREに入れます。

BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1
  AND balance >= 500;
COMMIT;

このSQLは、UPDATEする時点のbalanceが500以上の場合だけ、残高を減らします。1行更新されれば成功です。0 行なら、残高不足として扱えます。

分離レベルを上げる場合

トランザクション分離レベルをRepeatable Readや
Serializable
に上げる方法もあります。ただし、その場合は競合する更新を検出して、トランザクションがエラーになることがあります。分離レベルを上げる場合は、失敗した SQLだけでなく、トランザクション全体を最初から再試行できる設計が必要です。

この実験で分かったこと

ロック待ちしていたUPDATEは、ロック待ちが終わったあとに WHERE条件を再確認します。そのため、並行更新に強い処理にしたい場合は、事前のSELECT結果だけに頼らず、UPDATE のWHEREに更新条件を入れることが重要です。

実務での切り分け手順

ここまでの実験で、ロック待ちの見方を段階的に確認しました。実務でも、まず見るところは同じです。

UPDATEが返ってこないときは、最初にpg_stat_activityを確認します。ここで見るのは、待っているセッションの state、wait_event_type、wait_eventです。wait_event_typeが Lockであれば、そのセッションはロック待ちしている可能性が高いです。

次に、pg_blocking_pids()を使って、そのセッションを待たせているPIDを確認します。待っているSQL だけを見ても、原因は分かりません。重要なのは、待っている（ウェイター）側と、待たせている（ブロッカー）側を分けて見ることです。

待たせている（ブロッカー）側のPIDが分かったら、もう一度
pg_stat_activity
を確認します。特に、state、xact_start、state_change、application_name、client_addr、query を見ます。state = 'idle in transaction'で、xact_start が古い場合は、トランザクションを開いたまま放置している可能性があります。ただし、すぐにセッションを終了すればよいとは限りません。まず、どのアプリケーション、どの接続元、どの処理がトランザクションを開いたままにしているのかを確認します。

再発を減らす設計

ロック待ちそのものは、PostgreSQLの異常ではありません。同じ行を同時に更新しようとすれば、待つことがあります。

問題になるのは、待たせているトランザクションが長く残り、他の処理を止め続けることです。

ここでは、再発を減らすための基本的な考え方を整理します。

トランザクションを短くする

更新や明示的なロックを取得したあとは、できるだけ早くCOMMITまたはROLLBACKします。トランザクション中に外部API 呼び出し、ユーザー入力待ち、重いファイル処理などを入れると、ロックを持ったまま待ち時間が伸びます。また、例外が発生した場合は、必ず ROLLBACKします。

更新順序をそろえる

複数行を更新する処理では、更新順序をそろえます。ある処理はid = 1のあとにid = 2を更新し、別の処理は id = 2のあとにid = 1を更新すると、デッドロックが起きやすくなります。順序を決められるなら、常に同じ順序で更新します。

lock_timeoutを検討する

長時間待たせたくない処理では、lock_timeoutを検討します。

SET lock_timeout = '3s';

これは、ロックを取得できない状態が指定時間を超えたら、そのSQL をエラーにする設定です。ただし、エラーになった場合にアプリケーション側でどう扱うかを決めておく必要があります。

log_lock_waitsを確認する

本番環境では、log_lock_waitsも確認対象になります。log_lock_waitsを有効にすると、deadlock_timeout を超えたロック待ちをログに出せます。常にpg_stat_activityを手動で見続けるのではなく、ログから、どのSQL がいつ、どの程度ロック待ちしたのかを後から確認できます。

アプリケーションで再試行を考える

デッドロックやロックタイムアウトで失敗した場合、失敗したSQL だけを途中から再実行するのは危険です。基本は、トランザクション全体を最初からやり直します。ただし、無制限に再試行し続けるのではなく、再試行回数には上限を設けます。

UPDATEのWHEREに条件を入れる

並行更新で条件が変わる処理では、事前のSELECT結果だけに頼らないようにします。たとえば、残高が足りる場合だけ引き落とすなら、条件を UPDATEのWHEREに入れます。

BEGIN;
UPDATE accounts
SET balance = balance - 500
WHERE id = 1
  AND balance >= 500;
COMMIT;

以上、UPDATEが返ってこない状況を、同じ行への更新、ロック待ち、待たせているトランザクション、通常の SELECTの見え方に分けて確認しました。

ロック待ちそのものは、PostgreSQL の異常ではありません。同じデータを同時に矛盾なく更新するために必要な動きです。問題になるのは、ロックを持ったトランザクションが長く残り、他の処理を止め続けることです。

UPDATEが返ってこないときは、待っているSQLだけを見るのではなく、PostgreSQLの中で何を待っているのかを確認します。そのうえで、pg_blocking_pids() を使い、待たせている相手を探します。

待っている側と、待たせている側を分けて見る。この見方を持っておくと、ロック待ちを原因の一つとして落ち着いて切り分けられます。

SRA OSSでは、PostgreSQL/Pgpool-II のサポートを提供しております。
詳しくは弊社ホームページをご覧ください。

実験の前に

実験の準備

実験1:同じ行をUPDATEすると片方が待つ

この実験で確認すること

手順1:セッションAでトランザクションを開始する

手順2:セッションAでid = 1の行を更新する

手順3:セッションBで同じ行を更新する

なぜ待つのか

この実験で分かったこと

実験2: pg_stat_activityで何を待っているかを確認する

この実験で確認すること

確認するSQL

まず確認する列

結果の読み方

この実験で分かったこと

実験3: pg_blocking_pids()で待たせている相手を探す

この実験で確認すること

確認するSQL

結果の読み方

実験4: idle in transactionはロックを保持し得る状態

この実験で確認すること

状況の確認

結果の読み方

なぜ問題になりやすいのか

実務で確認する列

timeout設定について

この実験で分かったこと

実験5:通常のロック待ちとデッドロックは違う

この実験で確認すること

実験前の準備

手順1:セッションAがid = 1を更新する

手順2:セッションBがid = 2を更新する

手順3:セッションAがid = 2を更新しようとする

手順4:セッションBがid = 1を更新しようとする

デッドロック時のエラー

通常のロック待ちとの違い

デッドロックを減らす考え方

lock_timeoutについて

この実験で分かったこと

実験6:通常のSELECTは止まらない

この実験で確認すること

実験前の準備

手順1:セッションAでid = 1を更新する

手順2:セッションBで通常のSELECTを実行する

なぜ止まらないのか

SELECT FOR UPDATEは待つ

この実験で分かったこと

実験7: UPDATEは待ったあとにWHEREを再確認する

この実験で確認すること

実験前の準備

手順1:セッションAがid = 1を更新する

手順2:セッションBが条件付きUPDATEを実行する

手順3:セッションAが残高を400にしてCOMMITする

セッションBの結果

この動きが重要な理由

分離レベルを上げる場合

この実験で分かったこと

実務での切り分け手順

再発を減らす設計

トランザクションを短くする

更新順序をそろえる

lock_timeoutを検討する

log_lock_waitsを確認する

アプリケーションで再試行を考える

UPDATEのWHEREに条件を入れる

関連記事:

最近の投稿

カテゴリー

タグ