【PostgreSQL】SELECT FOR UPDATE でロック待ちを発生させて pg_locks で観測してみた
はじめに
PostgreSQL でロック競合が発生すると、クエリが返ってこなくなることがあります。
本記事では pg_locks を使ってロック待ちの状態を観測する手順を確認します。
3行まとめ
- ロック待ちは
pg_locksのgranted = fで見える WHERE transactionid = xxxで「誰が誰を待っているか」まで特定できるCOMMITするとロックが解放され、待機中セッションが動き出す
PostgreSQL インストール
検証のための実行環境は EC2（AL2023, t3.micro）にインストールした PostgreSQL を使用しました。
EC2 に PostgreSQL をインストールする手順としては下記をご参照ください。
PostgreSQL 17.10 を利用し、検証していきます。
[ec2-user@ip-xx-xx-xx-xx ~]$ sudo -u postgres psql
psql (17.10)
Type "help" for help.
postgres=# SELECT version();
version
---------------------------------------------------------------------------------------------------------------
PostgreSQL 17.10 on x86_64-amazon-linux-gnu, compiled by gcc (GCC) 11.5.0 20240719 (Red Hat 11.5.0-5), 64-bit
(1 row)
サンプルデータ準備
-- DB 作成
postgres=# CREATE DATABASE blog_sample_db;
CREATE DATABASE
postgres=# \c blog_sample_db
You are now connected to database "blog_sample_db" as user "postgres".
-- テーブル作成
blog_sample_db=# CREATE TABLE accounts (
id INTEGER PRIMARY KEY,
name TEXT,
balance INTEGER
);
CREATE TABLE
-- データ投入
blog_sample_db=# INSERT INTO accounts VALUES (1, 'Alice', 1000);
INSERT 0 1
blog_sample_db=# INSERT INTO accounts VALUES (2, 'Bob', 2000);
INSERT 0 1
-- データ確認
blog_sample_db=# SELECT * FROM accounts;
id | name | balance
----+-------+---------
1 | Alice | 1000
2 | Bob | 2000
(2 rows)
ターミナルを 3 つ用意
ロック競合を再現するため、ターミナルを 3 つ用意します。
PostgreSQL は接続ごとに 1 プロセスを生成します。今回はそれぞれ別セッションとして動作させます。
- ターミナル1 → セッションA（ロックを取る側）
- ターミナル2 → セッションB（ロック待ちになる側）
- ターミナル3 → 観測用（pg_locks を SELECT する）
各セッションの PID（プロセスID）は SELECT pg_backend_pid(); で確認できます。
ロックを発生させる
セッションA でロックを取ります。
-- PID 確認
blog_sample_db=# SELECT pg_backend_pid();
pg_backend_pid
----------------
6475
(1 row)
-- トランザクション開始
blog_sample_db=# BEGIN;
BEGIN
blog_sample_db=*# SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
id | name | balance
----+-------+---------
1 | Alice | 1000
(1 row)
FOR UPDATE
FOR UPDATEによりSELECT文により取り出された行が更新用であるかのようにロックされます。 これにより、それらは現在のトランザクションが終わるまで、他のトランザクションがロック、変更、削除できなくなります。
13.3. 明示的ロック
セッションBでも同じ行にロックを取ろうとしてみます。
が、セッション A が該当の行をロックしているため、値が返却されません。
-- PID 確認
blog_sample_db=# SELECT pg_backend_pid();
pg_backend_pid
----------------
6481
(1 row)
-- トランザクション開始
blog_sample_db=# BEGIN;
BEGIN
blog_sample_db=*# SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
-- ここで何も値が返却されず、カーソルが止まる（ロック待ち状態）
ロックの状態を確認する
ターミナル 3 からロックの状態を確認します。
postgres=# SELECT pg_backend_pid();
pg_backend_pid
----------------
6487
(1 row)
blog_sample_db=# SELECT pid, locktype, transactionid, mode, granted
FROM pg_locks
WHERE NOT granted;
pid | locktype | transactionid | mode | granted
------+---------------+---------------+-----------+---------
6481 | transactionid | 76445 | ShareLock | f
(1 row)
PID 6481（Session B）が granted = f になっています。
これは「Session B がロック待ち中」であることを意味します。
transactionid xid
ロックの対象となるトランザクションのID。
...
pid int4
ロックを保持、もしくは待っているサーバプロセスのプロセスID。
...
granted bool
trueの場合は、ロックが保持されている。 falseの場合は、ロックが待ち状態
52.12. pg_locks
指定されたプロセスにより保持されているロックを表す行内ではgrantedはtrueです。 falseの場合は、このロックを獲得するため現在プロセスが待機中であることを示しています。
52.12. pg_locks
Session B は transactionid 76445 の終了を待っています。
これは誰のトランザクションなのか、同じ transactionid で絞り込んでみます。
blog_sample_db=# SELECT pid, locktype, transactionid, mode, granted
FROM pg_locks
WHERE transactionid = 76445
ORDER BY granted DESC;
pid | locktype | transactionid | mode | granted
------+---------------+---------------+---------------+---------
6475 | transactionid | 76445 | ExclusiveLock | t
6481 | transactionid | 76445 | ShareLock | f
(2 rows)
上記より、PID 6475（セッションA）にて granted=t となっているため、
transactionid 76445 を保持しているのは Session A であることがわかりました。
つまり Session B は Session A のトランザクションが終わるのを待っていることが、以上より確認できました。
ロックを解放する
最後に、Session A が COMMIT するとロックが解放されることを確認します。
ターミナル1（Session A） で COMMIT します。
blog_sample_db=*# COMMIT;
COMMIT
するとその直後、ターミナル2（セッションB）で結果が返ってきました。
blog_sample_db=*# SELECT * FROM accounts WHERE id = 1 FOR UPDATE;
id | name | balance
----+-------+---------
1 | Alice | 1000
(1 row)
ターミナル3で pg_locks を確認すると、ロックを示していた行が消えていることが確認できました。
blog_sample_db=# SELECT pid, locktype, transactionid, mode, granted
FROM pg_locks
WHERE NOT granted;
pid | locktype | transactionid | mode | granted
-----+----------+---------------+------+---------
(0 rows)
検証は以上です。
終わりに
今回は pg_locks を使ってロック待ちの状態を観測しました。
ロック待ちが発生している場合、granted = f の行を探すことで、どのセッションが何を待っているかを特定できます。
実務では複数のロック待ちが同時に発生することもあります。
今後は「デッドロック」なども実際に検証していこうと思います。
本記事がどなたかのお役に立てば幸いです。
参考情報
