本記事では、OracleとPostgreSQLのデッドロックを簡単に比較した内容について記載していきます。それぞれ、業務システムで良く使われている有償/無償のRDBMSの筆頭で、移行も盛んに行われていますが、デッドロック時の挙動に若干の違いがあります。実運用時にデッドロックが多発するようなことはないと思いますが、万が一発生した時に、頭の片隅に置いてあると、役に立つかもしれません。
OracleとPostgreSQLのデッドロックまとめ
| 項目 | Oracle | PostgreSQL |
|---|---|---|
| デッドロック検知 | 自動 | 自動 |
| デッドロックタイムアウト時間 | 設定できない(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側の実際の動きを見てみましょう。次の例では、テーブルfooとbarについて、トランザクション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側の実際の動きを見てみましょう。次の例では、テーブルfooとbarについて、トランザクション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- セッション識別子
1と39の間でデッドロックが発生した
- セッション識別子
Information for the OTHER waiting sessions- セッション識別子
1はlock table foo in exclusive modeを実行していた
- セッション識別子
Current SQL Statement for this session- セッション識別子
39はlock 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;主に次のようなことが分かります。
- プロセス
29366はlock table bar in exclusive modeを実行しロック取得待機をしていた - プロセス
29052はlock table foo in exclusive modeを実行しロック取得待機をしていた - プロセス
29052でのロック取得待機中にデッドロックを検出した - プロセス
29366はlock table bar in exclusive modeの実行によりロック取得を行えた
まとめ
以上、OracleとPostgreSQLのデッドロックの挙動について簡単に比較してきました。
Oracleはデッドロック時の挙動が分かりやすく、より詳細な情報が得られますが、PostgreSQLはデッドロックのタイムアウト時間をユーザ側で指定できるため、チューニングに幅が出ます。
一長一短だとは思いますが、それぞれのデッドロック時の挙動の違いを考えながら設計を行うと、面白いかもしれません。
コメント