DBAtlas · データベース運用アトラス

長時間実行中 / オープンなトランザクション

長時間実行中またはオープンなトランザクションが、ロック、undo、肥大化を蓄積している。原因となっているトランザクションを見つけ、その種類 (idle in transaction、長時間のバッチ、影響) を判断し、安全に解消する。

最も古いオープンなトランザクションはどれか?

オープンなトランザクションを開始時刻順に一覧表示する。通常、最も古いものが問題の発生源。その状態を確認する: アクティブに実行中か、それともコミットを待つ 'idle in transaction' か?

SELECT s.sid, s.serial#, s.username, s.status, t.start_time, t.used_ublk*8/1024 AS undo_mb
FROM v$transaction t JOIN v$session s ON s.taddr=t.addr
ORDER BY t.start_time;
  • idle in transaction (コミット忘れ、アイドル状態)
  • アクティブだが非常に長時間実行中のバッチ/DML
  • トランザクションの継続中にロック/肥大化/undo が蓄積している

なぜ idle in transaction が発生するのか?

セッションがトランザクションを開いたまま、クエリを実行せずにアイドル状態になっている。つまり commit/rollback が忘れられている。これによりロックと undo が不必要に保持される。アプリケーションの修正で十分か、それとも緊急に終了させる必要があるか?

SELECT pid, now()-state_change AS idle_age, left(query,80) AS last_query
FROM pg_stat_activity WHERE state='idle in transaction' ORDER BY state_change;
  • アプリケーション/コネクションプールがコミットしない (恒久的な修正)
  • 今すぐ終了させる必要がある

アクティブな長時間トランザクションは何をしているか?

セッションは実際に実行中 (active)。単一の巨大な DML/レポートか、それとも他のセッションを待たせている、緊急に切断すべきブロッカーか?

SELECT s.sid, s.serial#, sq.sql_text, s.last_call_et AS running_secs
FROM v$session s JOIN v$sql sq ON sq.sql_id=s.sql_id
WHERE s.status='ACTIVE' AND s.type='USER' ORDER BY s.last_call_et DESC;
  • 単一の巨大な DML/レポート。分割する必要がある
  • 他のセッションを待たせている。緊急に終了する

長時間トランザクションは何を蓄積しているか?

長時間トランザクションの副作用を特定する: 主に他のセッションを待たせているロックの滞留か、それとも古い行バージョンを保持してクリーンアップ (VACUUM/undo) を妨げている肥大化か?

SELECT pid, pg_blocking_pids(pid) AS blocked_by FROM pg_stat_activity WHERE cardinality(pg_blocking_pids(pid))>0;
SELECT max(age(backend_xmin)) AS max_xmin_age FROM pg_stat_activity WHERE backend_xmin IS NOT NULL;
  • ロックの滞留が他のセッションを待たせている
  • 古いバージョンの蓄積がクリーンアップを妨げている (undo/肥大化)

根本原因: アプリケーションがコミットしない (idle in transaction)

コネクションプールまたはアプリケーションがトランザクションを開いた後、commit/rollback せずに接続をアイドル状態のままにしている。これによりロックと undo が不必要に保持される。

解決策: 原因となっているセッションを安全に終了する

ブロックしている/長時間のセッションが他を待たせている場合は、制御された方法で終了する必要がある。終了するとロールバックが発生し、領域/時間が必要になる場合があるため注意する。

根本原因: 単一の巨大な DML/レポート

単一のトランザクションで数百万行を処理する DML や、非常に長いレポートは、その実行期間全体にわたってロックと undo/肥大化を蓄積する。

根本原因: ロックの滞留キュー

長時間トランザクションが保持するロックにより、その後ろに待機キューができている。多数のセッションが同じリソースを待っている。

根本原因: 古いバージョンの蓄積 (undo/肥大化)

長時間実行中のトランザクションが、それより前の古い行バージョンのクリーンアップを妨げている。Postgres ではテーブル/インデックスの肥大化として、Oracle では undo の圧迫として現れる。

DBAtlas で開く →