こっちはおまけです。12.1.0.2 Single 環境でのデッドロック。
*** 2016-03-18 21:52:36.564
DEADLOCK DETECTED ( ORA-00060 )
See Note 60.1 at My Oracle Support for Troubleshooting ORA-60 Errors
[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: (*1)
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-0006001B-0000512C-00000000-00000000 48 10 X 31 364 X
TX-000A0016-000064D4-00000000-00000000 31 364 X 7 355 X
TX-00080015-000052DA-00000000-00000000 7 355 X 48 10 X
session 10: DID 0001-0030-00000014 session 364: DID 0001-001F-00000011
session 364: DID 0001-001F-00000011 session 355: DID 0001-0007-00000005
session 355: DID 0001-0007-00000005 session 10: DID 0001-0030-00000014
Rows waited on: (*2)
Session 10: obj - rowid = 0002121D - AAAhIdAAEAAAAD2AAA
(dictionary objn - 135709, file - 4, block - 246, slot - 0)
Session 364: obj - rowid = 0002121B - AAAhIbAAEAAAADlAAA
(dictionary objn - 135707, file - 4, block - 229, slot - 0)
Session 355: obj - rowid = 0002121C - AAAhIcAAEAAAADuAAA
(dictionary objn - 135708, file - 4, block - 238, slot - 0)
----- Information for the OTHER waiting sessions ----- (*3)
Session 364:
sid: 364 ser: 32666 audsid: 266832 user: 146/TEST
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 31 O/S info: user: test, term: UNKNOWN, ospid: 5359
image: oracle@test (TNS V1-V3)
client details:
O/S info: user: test, term: pts/4, ospid: 5356
machine: test program: sqlplus@test (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update tab2 set c1=1000
Session 355:
sid: 355 ser: 61241 audsid: 266833 user: 146/TEST
flags: (0x41) USR/- flags_idl: (0x1) BSY/-/-/-/-/-
flags2: (0x40009) -/-/INC
pid: 7 O/S info: user: test, term: UNKNOWN, ospid: 5360
image: oracle@test (TNS V1-V3)
client details:
O/S info: user: test, term: pts/1, ospid: 5094
machine: test program: sqlplus@test (TNS V1-V3)
application name: SQL*Plus, hash value=3669949024
current SQL:
update tab1 set c1=1000
----- End of information for the OTHER waiting sessions -----
Information for THIS session:
----- Current SQL Statement for this session (sql_id=01xmdxx2u6ma5) ----- (*4)
update tab3 set c1=1000
===================================================
PROCESS STATE
-------------
Process global information:
...
(*1) が Deadlock graph。
process 48 (session 10) が、TX-0006001B-0000512C を保持した状態で
TX-00080015-000052DA を待機。
process 31 (session 364) が、TX-000A0016-000064D4 を保持した状態で
TX-0006001B-0000512C を待機。
process 7 (session 355) が、TX-00080015-000052DA を保持した状態で
TX-000A0016-000064D4 を待機。
(*2) で objct_id と rowid もわかります。
(*3) が the OTHER waiting sessions。ORA-60 が発生したセッション以外のセッションの情報。
(*4) にプロセス・ステイトとして ORA-60が発生したセッションの情報が出力されます。
