Home > アーカイブ > 2013-06
2013-06
PostgreSQL INSERT で外部キーの被参照テーブルに ROW SHARE ロックがかかる
- 2013-06-12 (水)
- Web+DB
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
参照
- コメント (Close): 0
- トラックバック (Close): 0
Home > アーカイブ > 2013-06
- 検索
- フィード
- メタ情報