PostgreSQLのパーティショニング機能とLOCK TABLEの影響範囲の確認 #PostgreSQL

PostgreSQL10のネイティブ・パーティショニング機能で作成されたパーティションが、テーブル単位のロック取得(LOCK TABLE)の影響をどのように受けるか確認する。

今回は、以下のシンプルなパーティションで確認する。

CREATE TABLE parent (i INT) PARTITION BY LIST (i);
CREATE TABLE child1 PARTITION OF parent FOR VALUES IN (1);
CREATE TABLE child2 PARTITION OF parent FOR VALUES IN (2);
CREATE TABLE child3 PARTITION OF parent FOR VALUES IN (3);
CREATE TABLE child4 PARTITION OF parent FOR VALUES IN (4);

Q. 親テーブルのロックで子テーブルのロックも自動的に取得されるか？

A. 親テーブルのロックを取得することで、そのすべての子テーブルについても自動的にロックが取得される。

=# BEGIN;

-- 親テーブルに対してACCESS EXCLUSIVEモードのロックを取得する。
=# LOCK TABLE parent IN ACCESS EXCLUSIVE MODE;

-- ACCESS EXCLUSIVEモードのロックを取得済のテーブル名を表示する。
-- 親テーブルとすべての子テーブルが表示され、
-- 子テーブルのロックも自動的に取得されることがわかる。
=# SELECT c.relname FROM pg_locks l, pg_class c
  WHERE l.relation = c.oid
  AND l.mode = 'AccessExclusiveLock'
  AND l.pid = pg_backend_pid();

 relname 
---------
 parent
 child2
 child3
 child1
 child4
(5 rows)

Q. 子テーブルのロックで親テーブルや他の子テーブルのロックも自動的に取得されるか？

A. 親テーブルや他の子テーブルのロックは取得されない。ロック取得されるのはLOCK TABLEの対象の子テーブルのみ

=# BEGIN;

-- 子テーブルに対してACCESS EXCLUSIVEモードのロックを取得する。
=# LOCK TABLE child1 IN ACCESS EXCLUSIVE MODE;

-- ACCESS EXCLUSIVEモードのロックを取得済のテーブル名を表示する。
-- LOCK TABLEの対象の子テーブルのみが表示され、
-- 親テーブルと他の子テーブルのロックは取得されていないことがわかる。
=# SELECT c.relname FROM pg_locks l, pg_class c
  WHERE l.relation = c.oid
  AND l.mode = 'AccessExclusiveLock'
  AND l.pid = pg_backend_pid();

 relname 
---------
 child1
(1 row)

Q. 子テーブルのロックの取得後、(ロック未取得の)他の子テーブルに対するSQL実行はロック待ちとならないか？

A. ロック待ちとなる。(厳密には、ロック待ちとなるかは、子テーブルのロックのモードとSQL実行の種類によって決まる)

=# BEGIN;

-- 子テーブルに対してACCESS EXCLUSIVEモードのロックを取得する。
=# LOCK TABLE child1 IN ACCESS EXCLUSIVE MODE;

上記のトランザクションの実行中(COMMIT/ROLLBACK前)に、別のセッションから以下のSQLを実行する。

-- 親テーブル経由でi=4に対応する子テーブルchild4にアクセスするSQLを実行する。
-- ロック取得済はchild1でアクセス先はchild4と対象テーブルが異なるため
-- (一見、ロック待ちは発生しないように見えるが)、ロック待ちは発生する。
=# SELECT * FROM parent WHERE i = 4;

PostgreSQL10のネイティブ・パーティショニングの実装だと、親テーブル経由のSQL実行時に、すべての子テーブルに対してSQL実行に必要なロック(SELECTなら基本的にはAccessShareLock)を取得していて、そのため子テーブルの別のロックと競合するらしい。

まとめ

親テーブルのロックを取得することで、そのすべての子テーブルについても自動的にロックが取得される。
子テーブルのロックが取得されても、その親テーブルや他の子テーブルのロックが自動的に取得されることはない。
ただし、子テーブルのロックが取得されると、親テーブル経由で他の子テーブルにアクセスするSQLでもロック待ちが発生する可能性がある。