OracleとPostgreSQLのデッドロックの比較

Oracle
Oracle PostgreSQL データベース

本記事では、OracleとPostgreSQLのデッドロックを簡単に比較した内容について記載していきます。それぞれ、業務システムで良く使われている有償/無償のRDBMSの筆頭で、移行も盛んに行われていますが、デッドロック時の挙動に若干の違いがあります。実運用時にデッドロックが多発するようなことはないと思いますが、万が一発生した時に、頭の片隅に置いてあると、役に立つかもしれません。

スポンサーリンク

OracleとPostgreSQLのデッドロックまとめ

項目OraclePostgreSQL
デッドロック検知自動自動
デッドロックタイムアウト時間設定できない(1~2秒程度)設定できる(deadlock_timeout)(デフォルト1秒)
デッドロック後のトランザクションロールバックされない中断される
デッドロックにより影響を受けるトランザクションデッドロックを誘発させた方いずれか
デッドロックのログアラートログ、トレースファイルサーバログ

デッドロック検知

OracleもPostgreSQLも、デッドロックはデータベースにより自動的に検知されます。したがって、ユーザ側によるデッドロック確認機能の実装は必要ありません。

デッドロックタイムアウト時間

デッドロックのタイムアウト時間(デッドロックだとみなすまでの時間)ですが、データベース上、Oracleは明示的な設定ができませんが、PostgreSQLは明示的な設定ができます。

Oracleのデッドロックタイムアウト時間

Oracleはデッドロックを数秒(おおよそ1~2秒)以内で検知します。具体的な秒数について英語情報も含めて検索しましたが、下記のフォーラムレベルのもの(1~2秒)しか見当たりませんでした。個人的な体感としても、毎回1~2秒以内には検知されるように思います。

Usually, Oracle takes a second or two to detect a deadlock and throws the error.

Deadlocks in Oracle – Stack Overflow

PostgreSQLのデッドロックタイムアウト時間

PostgreSQLはdeadlock_timeoutパラメータを設定することにより、デッドロック検知までの時間を明示的に設定することができます。デフォルトは1秒です。

パラメータのコンテキストはsuperuserであるため、セッションごとに変更するといったようなことはできません。あくまで、データベース全体に統一的に設定されます。

デッドロック後のトランザクション

検知後のトランザクションの扱いについては、OracleとPostgreSQLで明確に相違があります。

Oracleはトランザクション自体はロールバックせず、あくまでデッドロックを検知したSQL文がロールバックされます。したがって、検知後もSQL文の発行は可能となります。しかし、デッドロックを検知したトランザクションを明示的にコミットかロールバックしない限り、もう片方のトランザクションはロックを待機し続けます。

PostgreSQLはトランザクション自体が強制的に中断されます。したがって、検知後はロールバック操作以外、一切のSQL文が受け付けられなくなります。しかし、もう片方のトランザクションはロックを取得できるため、そちらはほぼ影響なく操作を続けることができます。

Oracle上のデッドロック後のトランザクション

Oracle側の実際の動きを見てみましょう。次の例では、テーブルfoobarについて、トランザクションAとBがたすき掛けで排他ロックを取得しようとしています。

-- トランザクションA                         -- トランザクションB
SQL> lock table foo in exclusive mode;

表がロックされました。
                                             SQL> lock table bar in exclusive mode;

                                             表がロックされました。
-- 先にロック
SQL> lock table bar in exclusive mode;
                                             -- 後にロック
                                             SQL> lock table foo in exclusive mode;
lock table bar in exclusive mode
*
行1でエラーが発生しました。:
ORA-00060: リソース待機の間にデッドロックが検出されました。

SQL> insert into foo values (1, null);

1行が作成されました。

SQL> rollback;

ロールバックが完了しました。
                                             -- トランザクションAロールバック後にロック取得
                                             表がロックされました。

デッドロックを検知したトランザクションA側は、トランザクション全体はロールバックされませんでした。したがって、トランザクションA側は既にロック取得済みのfooテーブルに行を挿入できましたし、トランザクションB側はfooテーブルに対するロック取得を待機し続け、トランザクションAが完了した後にロックを取得しています。

PostgreSQL上のデッドロック後のトランザクション

PostgreSQL側の実際の動きを見てみましょう。次の例では、テーブルfoobarについて、トランザクションAとBがたすき掛けで排他ロックを取得しようとしています。

-- トランザクションA                              -- トランザクションB
postgres=# begin;                                 postgres=# begin;
BEGIN                                             BEGIN
postgres=# lock table foo in exclusive mode;
LOCK TABLE
                                                  postgres=# lock table bar in exclusive mode;
                                                  LOCK TABLE
-- 先にロック
postgres=# lock table bar in exclusive mode;
                                                  -- 後にロック
                                                  postgres=# lock table foo in exclusive mode;
                                                  ERROR:  deadlock detected
                                                  DETAIL:  Process 7548 waits for ExclusiveLock on relation 16384 of database 13938; blocked by process 7510.
                                                  Process 7510 waits for ExclusiveLock on relation 16387 of database 13938; blocked by process 7548.
                                                  HINT:  See server log for query details.
-- トランザクションB自動中断後に自動的にロック取得
LOCK TABLE
                                                  -- 中断されたトランザクションでは操作できない
                                                  postgres=# insert into bar values(1);
                                                  ERROR:  current transaction is aborted, commands ignored until end of transaction block

デッドロックを検知したトランザクションB側は、トランザクション全体が中断されました。こうなると、ロールバックの操作しか受け付けなくなります。したがって、トランザクションB側で追加で実施したSQL文は無視されていますし、トランザクションA側はbarテーブルに対するロックを自動的に取得できました。

デッドロックにより影響を受けるトランザクション

前述の検証では、OracleとPostgreSQL両方で同じようにデッドロックを発生させても、ロールバックまたは中断させられるトランザクション(影響を受けるトランザクション)は、それぞれ異なる結果となりました。

公式情報の公開記載は見つけられませんでしたが、これは、デッドロック後の対応を行うトランザクションの判定が、OracleとPostgreSQLで異なっているためと推測されます。

Oracleは「デッドロックを誘発させたトランザクション」について対処し、PostgreSQLは「デッドロックに関わったトランザクションのどちらか」について対処するというものです(※誤りがあったら申し訳ございません)。

Oracle上でデッドロックにより影響を受けるトランザクション

If your ORA-00060 is caused by competing resources, the perpetual embrace happens when the aborted task (Task B) attempts to lock a row which is being held by another task (Task A), which, in-turn, is waiting for task B to release a lock. To prevent a perpetual wait, Oracle aborts the transaction that caused the deadlock.

Oracle deadlocks tips

上記の記載を見ると、中断されるトランザクションは、競合するトランザクションが既にロックしている行に対してロックを試みた側というように捉えられます。「先に仕掛けた方が悪い」ということですね。実際のデッドロック検証時もそのような形でロールバックが行われています。

PostgreSQL上でデッドロックにより影響を受けるトランザクション

PostgreSQLの場合は、ロック待機が1秒を超えるとデットロックが起こってるんじゃないか考えて、デッドロックの有無を確認するチェックが実行されます。このチェックのロジックが走った結果、デットロックが見つかると、どれかのセッションがABORTされ、残ったセッションは継続できる、ということになります。つまり、このデットロックの輪の確認が一秒に1回チェックが走り、問題があればデッドロックを破る、という処理が行われています。

6. ロック制御・PostgreSQL Internals

上記の記載を見ると、中断されるトランザクションは、先にデータベースによるチェックで見つかった側というように捉えられます。実際のデッドロック検証時も、Oracleとは違い、被害を受けた側(トランザクションB)のトランザクションが中断されています。

ちなみに、上記では1秒と記載がありますが、これはdeadlock_timeoutのデフォルト値が1秒だからであり、この値を変更するとこの検証間隔も長くなります。

デッドロックのログ

デッドロックはロジックの設計ミスを暗示するものであるため、デッドロックの検出を監視し、後から情報を確認するというケースもあるかと思います。

Oracleはアラートログにデッドロックが検出されたということが出力され、詳細な情報はトレースファイルに記録されるという形となります。

PostgreSQLはサーバログにデッドロックが検出されたということが出力され、詳細な情報(Oracleに比べると簡易的ですが)も同じくサーバログに記録されるという形となります。

Oracle上のデッドロックのログ

Oracleでは、まず、アラートログ上に次のようなメッセージが出力されます。したがって、よくある運用通り、アラートログにORA-という出力があるかという監視を行えば、デッドロックに気が付くことができます。

Oracle上のデッドロックのアラートログ出力例

Sat Jun 13 12:24:05 2020
ORA-00060: Deadlock detected. More info in file /u01/app/oracle/diag/rdbms/orcl/orcl/trace/orcl_ora_19394.trc.

その上で、詳細な情報はアラートログに出力されたトレースファイルに記録されます。

Oracle上のデッドロックのトレースファイル出力例

*** 2020-06-13 12:24:05.843
*** SESSION ID:(39.2553) 2020-06-13 12:24:05.843
*** CLIENT ID:() 2020-06-13 12:24:05.843
*** SERVICE NAME:(SYS$USERS) 2020-06-13 12:24:05.843
*** MODULE NAME:(sqlplus@oracle01 (TNS V1-V3)) 2020-06-13 12:24:05.843
*** ACTION NAME:() 2020-06-13 12:24:05.843



*** 2020-06-13 12:24:05.843
DEADLOCK DETECTED ( ORA-00060 )

[Transaction Deadlock]

The following deadlock is not an ORACLE error. It is a
deadlock due to user error in the design of an application
or from issuing incorrect ad-hoc SQL. The following
information may aid in determining the deadlock:

Deadlock graph:
                       ---------Blocker(s)--------  ---------Waiter(s)---------
Resource Name          process session holds waits  process session holds waits
TM-00012232-00000000        28      39     X             29       1           X
TM-00012234-00000000        29       1     X             28      39           X

session 39: DID 0001-001C-00000C5F      session 1: DID 0001-001D-00000410
session 1: DID 0001-001D-00000410       session 39: DID 0001-001C-00000C5F

Rows waited on:
  Session 39: obj - rowid = 00012234 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 74292, file - 0, block - 0, slot - 0)
  Session 1: obj - rowid = 00012232 - AAAAAAAAAAAAAAAAAA
  (dictionary objn - 74290, file - 0, block - 0, slot - 0)

----- Information for the OTHER waiting sessions -----
Session 1:
  sid: 1 ser: 5545 audsid: 4294967295 user: 0/SYS flags: 0x41
  pid: 29 O/S info: user: oracle, term: UNKNOWN, ospid: 19396
    image: oracle@oracle01 (TNS V1-V3)
  client details:
    O/S info: user: oracle, term: pts/1, ospid: 19395
    machine: oracle01 program: sqlplus@oracle01 (TNS V1-V3)
    application name: sqlplus@oracle01 (TNS V1-V3), hash value=1634161439
  current SQL:
  lock table foo in exclusive mode

----- End of information for the OTHER waiting sessions -----

Information for THIS session:

----- Current SQL Statement for this session (sql_id=5g9s5xfpspx7s) -----
lock table bar in exclusive mode

主に次のようなことが分かります。

  • *** SESSION ID:(39.2553)
    • セッション識別子39がデッドロックにより影響を受けた
  • Deadlock graph
    • セッション識別子139の間でデッドロックが発生した
  • Information for the OTHER waiting sessions
    • セッション識別子1lock table foo in exclusive modeを実行していた
  • Current SQL Statement for this session
    • セッション識別子39lock table bar in exclusive modeを実行していた

PostgreSQL上のデッドロックのログ

PostgreSQLでは、log_lock_waitsのパラメータをonに設定すると、deadlock_timeoutよりも長い時間ロック状態となっているSQL文をログ出力できます。サーバログ上に全ての情報が出力されます。

内容はOracleより非常にシンプルです。デッドロック検出自体はERROR: deadlock detectedで記録されるため、こちらも良くある通り、ERROR:で監視していれば気が付くことができます。

PostgreSQL上のデッドロックのサーバログ出力例

2020-06-15 02:01:56 UTC:xx.xx.xx.xx(55590):postgres@postgres:[29366]:LOG: process 29366 still waiting for ExclusiveLock on relation 28685 of database 14007 after 10.061 ms
2020-06-15 02:01:56 UTC:xx.xx.xx.xx(55590):postgres@postgres:[29366]:DETAIL: Process holding the lock: 29052. Wait queue: 29366.
2020-06-15 02:01:56 UTC:xx.xx.xx.xx(55590):postgres@postgres:[29366]:STATEMENT: lock table bar in exclusive mode;
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:LOG: process 29052 detected deadlock while waiting for ExclusiveLock on relation 28682 of database 14007 after 10.059 ms
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:DETAIL: Process holding the lock: 29366. Wait queue: .
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:STATEMENT: lock table foo in exclusive mode;
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:ERROR: deadlock detected
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:DETAIL: Process 29052 waits for ExclusiveLock on relation 28682 of database 14007; blocked by process 29366. Process 29366 waits for ExclusiveLock on relation 28685 of database 14007; blocked by process 29052. Process 29052: lock table foo in exclusive mode; Process 29366: lock table bar in exclusive mode;
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:HINT: See server log for query details.
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55582):postgres@postgres:[29052]:STATEMENT: lock table foo in exclusive mode;
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55590):postgres@postgres:[29366]:LOG: process 29366 acquired ExclusiveLock on relation 28685 of database 14007 after 1553.795 ms
2020-06-15 02:01:58 UTC:xx.xx.xx.xx(55590):postgres@postgres:[29366]:STATEMENT: lock table bar in exclusive mode;

主に次のようなことが分かります。

  • プロセス29366lock table bar in exclusive modeを実行しロック取得待機をしていた
  • プロセス29052lock table foo in exclusive modeを実行しロック取得待機をしていた
  • プロセス29052でのロック取得待機中にデッドロックを検出した
  • プロセス29366lock table bar in exclusive modeの実行によりロック取得を行えた

まとめ

以上、OracleとPostgreSQLのデッドロックの挙動について簡単に比較してきました。

Oracleはデッドロック時の挙動が分かりやすく、より詳細な情報が得られますが、PostgreSQLはデッドロックのタイムアウト時間をユーザ側で指定できるため、チューニングに幅が出ます。

一長一短だとは思いますが、それぞれのデッドロック時の挙動の違いを考えながら設計を行うと、面白いかもしれません。

コメント

タイトルとURLをコピーしました