Oracle/MySQL/PostgreSQLにおけるサブクエリを含むDMLの非互換性

JPOUG Advent Calendar 2013の23日目です。比較的簡単なDMLでもRDBMS間の非互換性が出てしまうという怖い話をします。

+----+------+
| id | data |
+----+------+
|  1 | a    |
|  2 | b    |
|  3 | c    |
+----+------+
BEGIN;
-- IDが最も小さいレコードをロックする
SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;

(何らかの処理)

-- 処理が終わったので削除する
DELETE FROM q WHERE id = (1つ目のSQLで取得したID);
COMMIT;

IDが最も小さいレコードをロックし、何らかの処理を行って、最後にそのレコードを削除します。IDが最も小さいレコードを取得するというのはよく見かけるもので、例えばジョブキューを素朴に実装するとこのような流れになるかと思います。なおジョブキューを本当にこのように実装してしまうとトラブルの元になりますので、気になる方はBaron Schwartz氏の以下のエントリをご参照ください。

実行例

最初はOracle Database 12.1.0.1.0の実行例です。

 SESSON1> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
         ID DATA
 ---------- ----------
          1 a

          SESSION2> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
          (待たされる)

 SESSON1> DELETE FROM q WHERE id = 1;
 1 row deleted.
 
 SESSON1> COMMIT;
 Commit complete.

                  ID DATA
          ---------- ----------
                   2 b

次はMySQL 5.6.15の実行例です。トランザクション分離レベルはOracle Databaseに合わせてREAD COMMITTEDとしています。

 SESSON1> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
 Query OK, 0 rows affected (0.00 sec)
 
 SESSON1> BEGIN;
 Query OK, 0 rows affected (0.00 sec)
 
 SESSON1> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
 +----+------+
 | id | data |
 +----+------+
 |  1 | a    |
 +----+------+
 1 row in set (0.00 sec)

          SESSION2> SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
          Query OK, 0 rows affected (0.00 sec)
          
          SESSION2> BEGIN;
          Query OK, 0 rows affected (0.00 sec)
          
          SESSION2> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
          (待たされる)

 SESSON1> DELETE FROM q WHERE id = 1;
 Query OK, 1 row affected (0.00 sec)
 
 SESSON1> COMMIT;
 Query OK, 0 rows affected (0.00 sec)

          Empty set (8.26 sec)

MySQLの場合、セッション2のSELECT FOR UPDATE文が空振りします。

MySQLはサブクエリでMIN(ID)=1を取得して、外側のクエリでID=1のレコードをロックしようとして空振りする分かりやすい挙動です。一方Oracle Databaseは文レベルの読み取り一貫性を保証するために、SELECT FOR UPDATE文でロックしようとしたレコードが変更されていた場合、クエリを再起動してMIN(ID)=2を取得しなおすという挙動です。この挙動はマニュアルに記載されています。

私はOracle Databaseの挙動の方が好きですが、内部的にクエリを複数回実行することから、デッドロックが発生する、トリガが複数回起動されるといったトラブルを招く場合があります。

PostgreSQL 9.3.2の実行例も見てみましょう。

 SESSION1=> BEGIN;
 BEGIN
 
 SESSION1=> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
  id | data
 ----+------
   1 | a
 (1 行)

          SESSION2=> BEGIN;
          BEGIN
          
          SESSION2=> SELECT id, data FROM q WHERE id = (SELECT MIN(id) FROM q) FOR UPDATE;
          (待たされる)

 SESSION1=> DELETE FROM q WHERE id = 1;
 DELETE 1
 
 SESSION1=> COMMIT;
 COMMIT

           id | data
          ----+------
          (0 行)

PostgreSQLの場合は、MySQLと同様にセッション2のSELECT FOR UPDATE文が空振りします。

結果一覧

トランザクション分離レベルがREAD COMMITTEDの場合に加え、REPEATABLE READ、SERIALIZABLEの場合も調査しました。セッション2で実行されたSELECT FOR UPDATE文がどのような結果になったのか、一覧を以下に示します。

RDBMS READ COMMITTED REPEATABLE READ SERIALIZABLE
Oracle 12.1.0.1.0 ID=2を取得 (なし) 直列化エラー(※1)
MySQL 5.6.15 空振り 空振り ID=2を取得
PostgreSQL 9.3.2 空振り 直列化エラー(※2) 直列化エラー(※2)
  • ※1 ORA-08177: can't serialize access for this transaction
  • ※2 ERROR: could not serialize access due to concurrent update

三者三様で、どのトランザクション分離レベルでも挙動が揃うということがありません。正直頭を抱えてしまいます。

対処方法

これらのRDBMSで挙動を揃えたい場合は、SELECT FOR UPDATE文でサブクエリを使用することを禁止し、Oracle Databaseが行っているクエリの再起動を自前で実装することになるかと思います。PL/SQLでの実装例を以下に示します。

DECLARE
    v_min_id NUMBER;
    v_id     NUMBER;
    v_data   VARCHAR2(10);
BEGIN
    LOOP
        SELECT MIN(id) INTO v_min_id FROM q;
        BEGIN
            SELECT id, data INTO v_id, v_data FROM q WHERE id = v_min_id FOR UPDATE;
            EXIT;
        EXCEPTION
            WHEN NO_DATA_FOUND THEN
                NULL;
        END;
    END LOOP;
END;
/

Oracle Databaseで構築されたシステムをPostgreSQLに移行する際や、複数のRDBMSに対応したアプリケーションを開発する際に注意していただければと思います。明日は@MOTOTAKERさんです。