🦔

PostgreSQLでロック原因を調べる

2024/04/24に公開

概要

ロック待ちが発生し、DBのスループットが著しく低下するケースがあったため、
どのプロセスでロックが競合してしまっているか確認する方法を参考記事を元にまとめました。

データベースクラスタ内で発生しているロック情報を示すビュー。
ロックを取得しているプロセスid、ロックの種類、ロックの対象などの情報を格納している。
バージョン14以降では、ロック待ちを始めた時刻まで表示可能。

現在アクティブな接続に関する情報を提供するシステムビュー。
各接続の現在の状態、実行されているクエリなどの詳細情報が表示される。

引数に指定したプロセスIDのロック取得をブロックしているプロセスIDを配列で返すシステム関数。
頻繁にこの関数を呼び出すとDBの性能劣化に影響があるとドキュメントには記載がある。
（今回は調査のための一時的な使用なので、問題ないはず）

<!-- 別々のトラザクションで下記を実行 -->
BEGIN;
SELECT * FROM hoges WHERE id = 1 FOR UPDATE;

BEGIN句でトランザクションを開始して、FOR UPDATEで排他的ロックをかけいます。
COMMITがされないので、2つ目のトランザクションでの実行はロックの解放待ち状態となります。

SELECT pid, pg_blocking_pids(pid) FROM pg_stat_activity;

現在実行中のプロセスIDとそのプロセスのロック取得をブロックしているPIDの一覧を取得します。
今回は、プロセスID 2543のプロセスのロック取得が、1488のプロセスによってブロックされた状態ということがわかります。

SELECT * FROM pg_locks WHERE pid = 1488;

一部のlock_typeでExclusivceLock（排他的ロック）を取得していることがわかります。
また、grantedの列が全てtrueなので、ロックの取得が許可された状態です。

次に、ロック待ちをしている方のロック取得状況を確認します。

SELECT * FROM pg_locks WHERE pid = 2543;

一番下のtransactionidの行に注目すると、grantedがfalseになっているのがわかります。
これは、同じtransactionidに対して排他的ロックがかかっているためです。

transactionにロックがかかる理由

参考記事に記載がありますが、行レベルのロックはpg_locksに表示されません。
行ロックの解放を待っている場合、その行のロックを保持しているtransactionidを待機するためこれがビューとして表示されるようです。
（わかったような、わかってないような...）

ロックの原因となるプロセスID、クエリ等の情報も手に入っているので、あとは判断に応じてロック原因のプロセスを停止することもできます。

SELECT pg_cancel_backend([1488]);

実際にロックが発生したタイミングでしか使いどきがこないような気がしますが、
何かあった時の予備道具としてしまっておこうと思います。