Home > アーカイブ > 2013-06

2013-06

PostgreSQL INSERT で外部キーの被参照テーブルに ROW SHARE ロックがかかる

この記事の所要時間: 350

postgresql

PostgreSQL で、トランザクション処理中にあるテーブルに INSERT 文を発行すると、並列で実行している別処理にて、そのテーブルの外部キーの被参照テーブルへのEXCLUSIVE ロックが取得できない現象があったのでメモ。

再現

検証用にテーブルを2つ(products, orders)を用意します。orders テーブルの product_no には products テーブルへの外部キーを設定します。

CREATE TABLE products (
  product_no integer PRIMARY KEY,
  name text,
  price numeric
);

CREATE TABLE orders (
  order_id serial PRIMARY KEY,
  product_no integer REFERENCES products (product_no),
  quantity integer
);

products テーブルにレコードを登録しておきます。

test=> INSERT INTO products VALUES(1, 'item1', 1000);

トランザクションを開始して orders テーブルへ INSERT 文を実行します。トランザクションは実行中のままにしておきます。

test=> BEGIN;
BEGIN
test=> INSERT INTO orders(product_no, quantity) VALUES(1, 1);
INSERT 0 1

別ターミナルを開いて、psql を実行します。こちらで products テーブルの EXCLUSIVE ロックを取得しようとするとロック待ちになります。

% psql test
test=> BEGIN;
BEGIN
test=> LOCK TABLE orders IN EXCLUSIVE MODE;

pg_locks でロック状態を確認すると、products テーブルに ROW SHARE ロックがかかっていることが分かります。これが EXCLUSIVE ロックに競合しているために、ロック待ちが発生しました。

% psql test
test=> SELECT relname,pg_locks.mode FROM pg_locks INNER JOIN pg_class ON pg_class.oid=pg_locks.relation WHERE mode<>'AccessShareLock';
   relname   |       mode
-------------+------------------
 orders      | RowExclusiveLock
 products    | RowShareLock
(2 rows)

PostgreSQL のドキュメントを見ると ROW SHARE ロックは、SHARE ROW EXCLUSIVE ロック以下であれば競合しないので、SHARE ROW EXCLUSIVE MODE に変更するとロックを取得することができました。

% psql test
test=> BEGIN;
BEGIN
test=> LOCK TABLE orders IN SHARE ROW EXCLUSIVE MODE;
LOCK TABLE

INSERT 以外では、UPDATE 文で外部キーを更新(上記例では orders.product_no を更新)した場合も同様に外部キーの被参照テーブル(products)に ROW SHARE ロックがかかりました。

前にも

実際に INSERT や UPDATE を実行しているテーブルへロックがかかるのは容易に想像できるのですが、外部キーの被参照テーブルだとうっかり見落としがちなので注意が必要ですね。

実はこの現象を調べてたところ、7 年前に自分で書いたエントリが見つかりました(PHP4 + PostgreSQL7.4!)。すっかり忘れていた内容だったので書いてて良かったのですが、同じとこで躓くのが成長していないというかアホというか;-p

参照

制約 | PostgreSQL9.2.4文書
明示的ロック | PostgreSQL9.2.4文書

  • コメント (Close): 0
  • トラックバック (Close): 0

Home > アーカイブ > 2013-06

検索
フィード
メタ情報

Return to page top