ORA-00060 リソース待機の間にデッドロックが検出されました。
が発生し処理が失敗することがあります。
この現象は、一般的な事例として挙げられるようなデッドロックではなく、外部キーに関連する暗黙的なロックが引き起こすデッドロックです。
では、なぜデッドロックが起こったのでしょう？
実際に、上記の現象が発生したときのトレースファイルを調べてみると、それぞれのトランザクションが同じ表の異なる行に対して行排他ロック(SX)をかけており、その表に対して、それぞれのトランザクションが共有行排他ロック(SSX)を要求していることがわかりました。
以下にトレースファイルの該当部分を示します。
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM PRODUCT WHERE PRODUCT_ID = :1
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-000077c8-00000000 19 12 SX SSX 22 16 SX SSX
TM-000077c8-00000000 22 16 SX SSX 19 12 SX SSX
session 12: DID 0001-0013-00000002
session 16: DID 0001-0016-00000002
Rows waited on:
Session 16: obj - rowid = 000077C8 - AAAAAAAAHAAACvnAAA
(dictionary objn - 30664, file - 7, block - 11239, slot - 0)
Session 12: obj - rowid = 0000771E - AAAAAAAAHAAAQ6LAAA
(dictionary objn - 30494, file - 7, block - 69259, slot - 0)
Information on the OTHER waiting sessions:
Session 16:
pid=22 serial=7 audsid=6348 user: 42/TEST
O/S info: user: Administrator, term: unknown, ospid: , machine: test
program: JDBC Thin Client
Current SQL Statement:
DELETE FROM PRODUCT WHERE PRODUCT_ID = :1
End of information on OTHER waiting sessions.
デッドロックが発生している表は親表を参照する子表です。
Oracleでは、親表を参照する子表でかつ外部キーにインデックスを設定されていない場合、親表のレコードを削除するときに子表の全レコードに対して一時的に更新を防止するための共有行排他ロック(SSX)を要求するというロックの仕様になっています。
この子表の外部キーにはインデックスが設定されていなかったため、共有行排他ロック(SSX)が要求されデッドロック状態となったというわけです。
親表の削除時に子表に対して共有行排他ロック(SSX)を要求するのは、子表の外部キーにインデックスが設定されていない場合に限られるため、この問題を解決するためには、子表の外部キーに対してインデックスを設定すればOKです。
無用なトラブルを避けるためにも外部キー項目には原則的にインデックスを設定すべきと考えるのが無難でしょう。