管理画面から開始された CSV 出力の DB 処理が、1時間半を超えて続きました。
しかも、正常完了を示す記録は残っていません。
その途中で、CSV が参照していたテーブルへ ALTER TABLE が投入されました。DDL はロックを取得できず、その後に来た通常の SELECT まで待機しました。
私が障害調査でコードと Web・PHP・DB の記録を追い、確認できたのは次の三つです。
- CSV 処理で
set_time_limit(0)が呼ばれていた - CSV の分割
SELECT全体が、リクエスト単位の外側トランザクションに含まれていた - 待機中 DDL の後ろで後続
SELECTが待つ一方、CSV 側の分割SELECTは進み続けていた
CSV は N 件ずつ取得していました。
「N 件ずつなら、クエリの間でロックも外れるのでは？」
コードだけを見ると、そう思えます。ところが、外側に一つのトランザクションがあれば話は別でした。
見るべきなのは、CSV を何件ずつ取得したかではありません。DB トランザクションが、いつ始まり、いつ終わったかです。
一般的なロック仕様は PostgreSQL 16 の公式ドキュメントで確認しながら、本番で観測した事実と別環境の再現結果を分けて振り返ります。
Metrics
本番で確認した影響
コードとWeb・PHP・DBの記録を時系列で照合した結果です
- CSVのDB処理
- 1時間半超正常完了を示す記録なし
- 後続SELECT
- ロック待ち待機中DDLの後ろで停止
本番記録から確定できた範囲
本番調査では、Web のアクセス記録、PHP の slow log、DB の statement・duration・一時ファイルの記録、対象コードを突き合わせました。
slow log から CSV の処理経路と実行中の PHP プロセスを確認し、DB 側では同じ形のページングクエリを繰り返す長寿命セッションを追跡しました。そのセッションは DDL が待ち始めた後もページ処理を続け、セッションが消えた後に待機中 DDL と後続処理が進みました。
CSV の外側トランザクションが DDL の blocker だったことと、待機中 DDL が後続アクセスの soft blocker になったことは、コードと本番記録の両方から確認できました。
一方、CSV には正常完了を示す記録がありません。DB 側の活動が終わった直接のきっかけまでは確定できなかったため、この記事でも「CSV が完走した」とは扱いません。
確認できたことと、最後まで分からなかったことは混ぜずに書いておきます。
set_time_limit(0)が外すPHPの上限
set_time_limit(0) は PostgreSQL のロックを直接作りません。ここは分けて考えます。
PHP の公式マニュアルでは、set_time_limit() が成功した場合、0 はそのスクリプトに PHP の実行時間上限を設けない指定です。また、この関数は真偽値を返すため、呼び出した事実と設定が成功した事実も、本来は分けて観測する対象です。(PHP settimelimit)
ただし、この上限はスクリプト自身の実行時間に対するものです。非 Windows 環境ではシステムコール、ストリーム操作、DB クエリなどの時間は計測に含まれず、Windows では実時間が計測されます。Web サーバーなど別の層が独自の timeout を持つ場合もあります。(PHP Runtime Configuration)
そのため、set_time_limit(0) を削除すれば DB の長時間待機が既定の30秒で終わる、とまでは言えません。
今回のコードで確認できたのは、PHP の停止条件を一つ外す指定があり、実際の DB 処理が1時間半を超えて続いたことです。set_time_limit(0) を単独の根本原因に据えるのは無理があります。
ただ、管理画面の同期 CSV へこれを置くのは避けておいた方がよい、というのが正直な感想です。
大きな CSV は、期限とキャンセル方法を持つ非同期ジョブへ移す方が安全だと思います。ただし、非同期化だけでは DB のトランザクションは短くなりません。ジョブ側にも SQL とトランザクションの終了条件が必要です。
N件ずつ取得してもトランザクションは分かれない
今回のコードでは、CSV のページング処理より前に共通処理がトランザクションを開始し、レスポンス送信後まで終了しない構成でした。ページングループ内に COMMIT はありませんでした。
BEGIN
SELECT 1回目
CSVへ書き込み
SELECT 2回目
CSVへ書き込み
SELECT 3回目
...
COMMITまたは接続終了アプリケーション上では、個々の SELECT が終わっています。
PostgreSQL から見れば、まだ同じトランザクションの中です。
通常の SELECT は、参照したテーブルへ ACCESS SHARE を取得します。テーブルレベルロックは、savepoint まで巻き戻した場合などの例外を除き、通常はトランザクションの終了まで保持されます。(PostgreSQL 16 Explicit Locking)
一方、ALTER TABLE はサブコマンドごとに必要なロックが異なりますが、明記された例外を除く既定は ACCESS EXCLUSIVE です。ACCESS EXCLUSIVE は ACCESS SHARE を含むすべてのテーブルレベルロックと競合します。(PostgreSQL 16 ALTER TABLE)
したがって、CSV の最初の参照で取得した ACCESS SHARE が同じトランザクションに残っていれば、ACCESS EXCLUSIVE を要求する DDL は待ちます。
待機中DDLが後続SELECTまで止める
本番で観測した待ち列
本番で確認した関係を匿名化して簡略化すると、次の形です。
T1: 管理画面のCSV
AccessShareLock: granted
T2: ALTER TABLE
AccessExclusiveLock: waiting
T3: 後続のSELECT
AccessShareLock: waitingT1 の CSV が ACCESS SHARE を保持しているため、T2 の DDL は待ちます。
その後に来た T3 も ACCESS SHARE を要求します。T1 とは競合しませんが、先に待っている T2 の ACCESS EXCLUSIVE とは競合します。
PostgreSQL 16 の pg_blocking_pids() は、競合ロックを保持する hard blocker だけでなく、競合するロックを先に待っている soft blocker も返すと説明しています。つまり、T2 が待ち列の前にいることで T3 を待たせる関係は、PostgreSQL の公開仕様に含まれます。(PostgreSQL 16 System Information Functions)
別環境で再現した挙動
通常の PostgreSQL を使った別環境でも、T1 が BEGIN 後に SELECT を終え、COMMIT せずに待つ状態を作りました。その後に T2 の ALTER TABLE、T3 の SELECT を順に発行すると、T2 と T3 はともに待機しました。
その間に T1 から同じテーブルへもう一度 SELECT すると、T1 は待ち列へ入らず完了しました。T1 を COMMIT すると、T2、T3 の順で進みました。
同じトランザクションは自分自身とロック競合しないことも PostgreSQL 16 の公式ドキュメントに明記されています。(PostgreSQL 16 Explicit Locking)
既存トランザクションのCSVは進む
DDLは待つ
新しい通常リクエストも待つ本番で CSV のページングクエリが DDL の待機開始後も進んだ挙動は、この再現結果と一致しました。ここで説明している待ち列は Aurora PostgreSQL 固有の機能ではなく、PostgreSQL の一般的なロック機構です。
管理画面上では CSV が動いているのに、その裏では周囲のリクエストだけがじわじわ詰まっていきます。
CSV処理にBEGINがなくてもautocommitとは限らない
PostgreSQL は、明示的な BEGIN がなければ各文を個別のトランザクションとして実行し、成功時に暗黙の commit を行います。(PostgreSQL 16 BEGIN)
ただし、CSV 関数の中に BEGIN がないことと、アプリケーション全体が autocommit かどうかは別の話です。DB ドライバー、middleware、event listener、共通処理などが、コントローラへ到達する前にトランザクションを始める場合があるためです。
「こんな普通の管理画面で、まさかリクエスト全体にトランザクションが張られているとは」と思いました。
そう思っても、まったく不思議ではありません。ここはかなり見落としやすいと思います。
今回のコードでは、CSV の SQL はレスポンス送信時に実行される stream callback の中にありました。一方、共通トランザクションはリクエストの入口で始まり、レスポンス送信が終わった後に終了する構成でした。この二つをコード上でつなぐと、CSV の全ページが同じトランザクションに入ります。
フレームワーク名だけから、この境界を判断することはできません。
どの接続で、いつトランザクションを始め、どの処理の後に終了したか。
CSV のループだけでなく、リクエストの入口、レスポンス送信、終了処理まで追う。
確認範囲をここまで広げて、ようやくトランザクションの寿命が見えました。
待機中DDLを安全に外す
ここからは、上の待ち列を確認できた場合に私なら採る復旧手順です。別の blocker や別種のロック競合がある状況へ、そのまま持ち出せる手順ではありません。
通常アクセスが詰まったからといって、古い CSV セッションをいきなり terminate するのは危ないです。
最初に、writer 側でセッションと待ち関係を確認します。PID は再利用され得るため、backend_start、接続ロール、アプリケーション名、接続元、現在文も同時に照合します。
SELECT
a.pid,
a.backend_start,
a.usename,
a.application_name,
a.client_addr,
a.state,
a.wait_event_type,
a.wait_event,
a.xact_start,
a.query_start,
pg_blocking_pids(a.pid) AS blocking_pids,
left(a.query, 160) AS query
FROM pg_stat_activity AS a
WHERE a.datname = current_database()
AND a.pid <> pg_backend_pid()
ORDER BY a.xact_start NULLS LAST, a.query_start;他ロールのセッションについて query などの詳細を見るには、superuser、pg_read_all_stats、または対象セッションを所有するロールに応じた権限が必要です。(PostgreSQL 16 Monitoring Statistics)
T2 の待機中 DDL が T3 の soft blocker だと確認できた場合、私なら長時間 CSV を先に終了せず、DDL の現在文を先にキャンセルします。CSV を先に終了すると、ほかに blocker がなければ DDL が ACCESS EXCLUSIVE を取得して実行へ進むためです。
「CSV を止めれば復旧する」と思って実行すると、待っていた DDL が今度は走り始めます。復旧操作の順番を誤ると、別の影響を発生させかねません。
次の :ddl_pid は、直前に照合した PID を渡すクライアント側の bind parameter です。
SELECT pg_cancel_backend(:ddl_pid);pg_cancel_backend() はセッション全体ではなく現在の query をキャンセルします。明示的トランザクション内の DDL がエラーになった場合、接続は idle in transaction (aborted) として残り得ます。この state は、トランザクション内の文がエラーになった状態です。(PostgreSQL 16 Administration Functions, PostgreSQL 16 pgstatactivity)
DDL を発行したクライアントを操作できるなら、同じ接続から明示的に終了します。(PostgreSQL 16 ROLLBACK)
ROLLBACK;元のクライアントから ROLLBACK できず、照合した同じセッションが残り、保持済みロックなどの影響が続く場合に限って、影響を確認したうえでセッション終了を判断します。
SELECT pg_terminate_backend(:ddl_pid);これらの関数は既定で superuser に制限され、対象ロールのメンバーシップや pg_signal_backend でも許可される場合があります。ただし、superuser の backend を操作できるのは superuser だけです。また、戻り値 true は signal の送信成功を表すだけで、復旧完了の証明ではありません。(PostgreSQL 16 Administration Functions)
実行後は最初の pg_stat_activity の query を再実行し、次を確認します。
- 対象 PID と
backend_startの組み合わせが消えた、または意図した state へ変わった - 待機中 DDL が soft blocker ではなくなった
- 後続
SELECTのロック待ちが解消した - DDL を実行した側のトランザクションが終了した
ここまで確認してから、長時間 CSV の接続をどう終えるか判断します。
私なら三つ直す
- HTTP処理とSQLと非同期ジョブにそれぞれ終了条件を置く
- 長時間の読み取りをリクエスト単位トランザクションから外す
- DDLに
lock_timeoutとstatement_timeoutを設定する
管理画面の同期 CSV からは set_time_limit(0) を外し、各層の timeout とキャンセル時の挙動を実環境で確認します。一つの timeout が、PHP・Web サーバー・DB・ジョブをまとめて止めてくれるとは考えません。
対象は異なりますが、処理する場所と実行時点を境界ごとに決める考え方は、Next.jsとCloudflare WorkersをSSG-firstで設計した理由にも通じます。
読み取り専用 CSV は、リクエスト単位トランザクションから外します。
各分割 SELECT を autocommit で実行すれば、成功した各文のトランザクションは文末で終了し、その文が取得した ACCESS SHARE も解放されます。(PostgreSQL 16 BEGIN, PostgreSQL 16 Explicit Locking)
ただし、各文が別々の時点の snapshot を見る設計で要件を満たすかは別問題です。CSV 全体で一貫した snapshot が必要なら、REPEATABLE READ の読み取り専用トランザクションなどを候補にし、長時間トランザクションの影響も含めて設計します。(PostgreSQL 16 Transaction Isolation)
DDL を待たせ続けない。これは設定で先に決めます。
BEGIN;
SET LOCAL lock_timeout = '3s';
SET LOCAL statement_timeout = '30s';
ALTER TABLE example_table
ADD COLUMN note text;
COMMIT;lock_timeout はロック取得を待つ時間へ、取得試行ごとに適用されます。statement_timeout はロック待ちを含む文全体へ適用されます。後者を前者以下にすると statement_timeout が先に発火するため、例では lock_timeout を短くしています。(PostgreSQL 16 Client Connection Defaults)
SET LOCAL の設定は現在のトランザクションが終わるまでです。(PostgreSQL 16 SET)
明示的トランザクション内で timeout や DDL エラーが発生した場合、デプロイ処理は後続 DDL へ進まず、同じ接続で ROLLBACK します。その後、対象セッションとロック待ちが消えたことを再確認します。
守りたいのは、DDL の成功率ではありません。
短時間でロックを取れない DDL を失敗させ、通常トラフィックを道連れにしないこと。
最後に
今回、set_time_limit(0) が PostgreSQL のロックを直接作ったわけではありません。
コード上では、PHP の実行時間上限を設けない指定と、レスポンス送信後まで続く外側トランザクションが重なっていました。DB 記録では、そのトランザクションが1時間半を超えてページングクエリを続け、その間 ACCESS SHARE を保持した挙動を確認できました。
その状態で ACCESS EXCLUSIVE を要求する ALTER TABLE が待ち列へ入り、後続の SELECT まで待機しました。このロック連鎖は本番記録で確認し、通常の PostgreSQL を使った別環境でも同じ順序を再現しました。
ただし、長時間 CSV は正常完了しておらず、DB 側の活動が終わった直接のきっかけは未確定です。
BEGIN と COMMIT がどこにあるか。処理を無期限にしていないか。DDL がロックを取れないとき、短時間で失敗できるか。
この三つを先に決めておくと、将来の自分やチームがだいぶ楽になると思います。
本番固有の記述は、匿名化したコードと Web・PHP・DB の記録から確認できた範囲に限定しています。ロック機構、監視関数、session 操作、timeout の一般仕様は PostgreSQL 16 の公式ドキュメント、PHP の実行時間制限は PHP 公式マニュアルで確認しました。再現結果は本番観測と区別して記載し、Aurora PostgreSQL 固有の挙動とは扱っていません。
