PostgreSQL

概要

PostgreSQL でユーザ定義関数を使うときのノートです。ユーザ定義関数でテーブルの更新処理を行い、同時に別のトランザクションが該当データの更新処理を行う場合に、ユーザ定義関数で直感とは異なるデータが参照される場合があります。

PL/pgSQL や C、SQL で作成したユーザ定義関数でこの現象が起きます。これは、SPIというサーバプログラム開発用インタフェースに起因するためです。

ユーザ定義関数

ユーザ定義関数 (f1) が下記となっている場合に注意する必要があります。

1. (f1 関数 a 行目) UPDATE 文か DELETE 文を使っている (t1 テーブルの r1 行を対象とします)。
2. (f1 関数 b 行目) t1 テーブルの (この時点では見えないはずの) 更新前の r1 行を参照する SQL 文がある。

状況

上記のユーザ定義関数 f1 を下記の状況で使った場合に注意が必要です。

1. あるトランザクション z1 が t1 テーブルの r1 行を更新処理などでロックしていたとします。
2. 別のトランザクション z2 が f1 関数を呼び出して t1 テーブルの r1 行を更新しようとします。このときに、r1 行がロックされているので、z2 は f1 関数 a 行目で待たされます。
3. z1 が COMMIT したとします (仮に z1 がアボートした場合は問題はありません)。
4. z2 の待ちが解除されます。
   このときに、f1 関数 b 行目で更新前の r1 行が検索されます。
   この点に注意してください。

サンプル

ユーザ定義関数のサンプルです。

DROP TABLE t1;
CREATE TABLE t1 (i int, j int);
INSERT INTO t1 VALUES (1, 100);
INSERT INTO t1 VALUES (2, 200);

DROP FUNCTION f1(int, int);
CREATE FUNCTION f1(int, int) RETURNS int AS '
DECLARE
    rec RECORD;
    key ALIAS FOR $1;
    value ALIAS FOR $2;
BEGIN
    UPDATE t1 SET j = value WHERE i = key; -- f1 関数 a1 行目
    FOR rec IN SELECT oid, ctid, xmin, xmax, cmin, cmax, i, j FROM t1 LOOP -- f1 関数 a2 行目
        RAISE NOTICE ''oid % ctid % xmin % xmax % cmin % cmax % i % j %'', rec.oid, rec.ctid, rec.xmin, rec.xmax, rec.cmin, rec.cmax, rec.i, rec.j;
    END LOOP;
    RETURN 0;
END;
' LANGUAGE 'plpgsql';

再現

z1 と z2 は別のトランザクションです。

z1=# SELECT * FROM t1;
 i |  j
---+-----
 1 | 100
 2 | 200
(2 rows)

z1=# BEGIN;
z1=# UPDATE t1 SET j = 111 WHERE i = 1;
UPDATE 1
z2=# SELECT f1(1, 999); -- f1 関数 a1 行目で待ち状態
z1=# COMMIT;

待ち状態が解除された後の z2 の出力です。

NOTICE:  oid 144530 ctid (0,1) xmin 23461 xmax 23466 cmin 23466 cmax 1 i 1 j 100
NOTICE:  oid 144531 ctid (0,2) xmin 23462 xmax 0 cmin 0 cmax 0 i 2 j 200
NOTICE:  oid 144530 ctid (0,4) xmin 23468 xmax 2 cmin 2 cmax 0 i 1 j 999
 f1
----
  0
(1 row)

問題の回避方法

ユーザ定義関数 f1 を使うトランザクションの ISOLATION LEVEL を SERIALIZABLE にします。具体的には、トランザクションの先頭 (BEGIN の直後) で下記のようにします。

z2=# BEGIN;
z2=# SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
z2=# SELECT f1(1, 999);

ただし、SERIALIZABLE を使用できるか否かは、業務の仕様に依存します。業務の仕様上、SERIALIZABLE が利用できない場合は、ユーザ定義関数と同様の処理をフロントエンドプログラムから行ってください。フロントエンドプログラムを利用するとこの現象は起きません。

バージョン

この現象は下記のバージョンで確認しました。

• 6.5.3
• 7.0.3
• 7.1.3
• 7.2 RC2
• 7.3.4