読者です 読者をやめる 読者になる 読者になる

Gblog

おもにTips

Wait-For-Graph の見方(Single)

こっちはおまけです。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が発生したセッションの情報が出力されます。