【PostgreSQL】外部キーを含むテーブルへのInsert処理では関連テーブルにFOR KEY SHAREの行レベルロックがかかる

この記事は毎週必ず記事がでるテックブログ "Loglass Tech Blog Sprint" の 5週目 の記事です！
1年間連続達成まで 残り48週 となりました！

背景

PostgreSQL利用時に動作確認を行なっていた際に、あるトランザクションが他のトランザクションをブロックするような動き（詳細は後述）をしている箇所がありました。

意図的に排他制御をしている箇所ではなかったため、詳細を調べてたところ外部キー制約が関連していたので動きを確認してみました。

バージョン

PostgreSQL 12.1

環境準備

まず、動作確認のために、2つのテーブル（test_a, test_b）を作成します。
このうち、test_bにはtest_aに対する外部キーを持たせています。

-- test_aテーブルを作成
create table test_a (
  id bigserial primary key,
  name varchar(30)
);

-- test_bテーブルを作成
-- test_a_idに外部キー制約を追加
create table test_b (
  id bigserial primary key,
  name varchar(30),
  test_a_id bigint references test_a
);

テーブルの状態としては以下のような形です。

次に、test_aにあらかじめデータをinsertしておきます。

-- test_aテーブルに値を挿入
insert into test_a (name) values ('test_a');

上記の処理により、test_aには以下のようなデータがinsertされました。

select * from test_a;

/* selectした結果 */
 id |  name
----+--------
  1 | test_a
(1 行)

動作確認

処理待ちとなる状況を確認

環境の準備が整ったところでテーブルのロックの状況を確認していきます。

トランザクションを手動で貼った状態で、test_bにレコードを挿入します。test_a_idには先ほどtest_aにinsertしたレコードのidを設定します。

-- トランザクション開始
begin;
-- test_aに挿入した値のidを設定して挿入
-- /* selectした結果 */
--  id |  name
-- ----+--------
--   1 | test_a
insert into test_b (name, test_a_id) values ('test_b', 1);

上記のトランザクションはcommitせず、この状態で別のトランザクションで例えばtest_aのレコードを削除する処理をします。

-- トランザクション開始
begin;
-- test_aに挿入した値のidを指定して削除
delete from test_a where id = 1; -- 処理待ちになる

test_bへtest_aに対する外部キーを持ったレコードをinsertしようとしているのでdelete文は処理待ちになります。

pg_locksの状況を確認

上記で動作確認を行った状態がどのような状態となっているかpg_locksを確認します。
上記の状態でまず、insertした処理後にpg_locksを確認するとtest_aに関してはRowShareLockが獲得されているのがわかります。

select *
from pg_locks
where pid = pg_backend_pid() AND relation = 'test_a'::regclass;

/* selectした結果 */
 locktype | database | relation | page | tuple | virtualxid | transactionid | classid | objid | objsubid | virtualtransaction |  pid  |     mode     | granted | fastpath
----------+----------+----------+------+-------+------------+---------------+---------+-------+----------+--------------------+-------+--------------+---------+----------
 relation |    16391 |   163510 |      |       |            |               |         |       |          | 13/3479            | 14545 | RowShareLock | t       | t
(1 行)

test_bへのデータinsert時にtest_aに対してRowShareLockがかかっています。

※テーブルレベルロックモードモードとしては以下の種類が存在しますが、ROWという名前が付いていても全てテーブルレベルのロックなのは注意が必要です。

テーブルレベルロックモード
ACCESS SHARE
ROW SHARE
ROW EXCLUSIVE
SHARE UPDATE EXCLUSIVE
SHARE
SHARE ROW EXCLUSIVE
EXCLUSIVE
ACCESS EXCLUSIVE

参考： https://www.postgresql.org/docs/12/explicit-locking.html

pgrowlocksを利用して状況を確認

続いて、pgrowlocksを利用して行レベルのロックの状況を確認できるためそちらでも確認をしてみます。

※pgrowlocksはCREATE EXTENSIONで追加する必要があります。

insertした処理後に結果を確認すると以下のようになります。

select * from pgrowlocks('test_a');

/* selectした結果 */
 locked_row | locker | multi |  xids   |       modes       |  pids
------------+--------+-------+---------+-------------------+---------
 (0,1)      |  14095 | f     | {14095} | {"For Key Share"} | {14545}
(1 行)

selectした結果からtest_aに対してFor Key Share行レベルロックがかかっていることが確認できました。

For Key Shareに関するドキュメントを確認すると、以下のような記載があり、DELETEの処理は他のトランザクションをブロックすると記載があることが確認できます。

FOR KEY SHARE
Behaves similarly to FOR SHARE, except that the lock is weaker: SELECT FOR UPDATE is blocked, but not SELECT FOR NO KEY UPDATE. A key-shared lock blocks other transactions from performing DELETE or any UPDATE that changes the key values, but not other UPDATE, and neither does it prevent SELECT FOR NO KEY UPDATE, SELECT FOR SHARE, or SELECT FOR KEY SHARE.

まとめ

外部キーを含むテーブルへのInsert処理では関連テーブルにFOR KEY SHAREの行レベルロックがかかることがわかりました。
意図しないデッドロックの原因にもなるためこの辺りの動きは意識しておきたいところです。

参考

PostgreSQL 12 Documentation