| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
 |
 | |||
| Oracle FAQ | Your Portal to the Oracle Knowledge Grid | |
|
| |||
Home -> Community -> Usenet -> c.d.o.server -> Re: Interpretting Deadlock Info
Gene
One of the notes available to you from Support is included below.
HTH. Pete
What does the trace information mean ?
DEADLOCK DETECTED
Current SQL statement for this session:
update eg_60 set txt='ses2' where num=1
-----------------------------------------------------------------------
This shows the statement which was executing which received the
ORA-60
error. It is this statement which was rolled back.
Deadlock graph:
---------Blocker(s)--------
---------Waiter(s)---------
Resource Name process session holds waits process session
holds waits
TX-00020012-0000025e 12 11 X 11
10 X
TX-00050013-0000003b 11 10 X 12
11 X
-----------------------------------------------------------------------
This shows who was holding each lock, and who was waiting for each lock.
The columns in the graph indicate:
Resource Name Lock name being held / waited for.
process V$PROCESS.PID of the Blocking / Waiting session
session V$SESSION.SID of the Blocking / Waiting session
holds Mode the lock is held in
waits Mode the lock is requested in
So in this example:
SID 11 holds TX-00020012-0000025e in X mode
and wants TX-00050013-0000003b in X mode
SID 10 holds TX-00050013-0000003b in X mode
and wants TX-00020012-0000025e in X mode
The important things to note here are the LOCK TYPE, the MODE HELD and
the MODE REQUESTED for each resource as these give a clue as to the reason for the deadlock.
Rows waited on:
Session 10: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAB
Session 11: obj - rowid = 00000BF6 - AAAAv2AAEAAAAqKAAA
If the deadlock is due to row-level locks being obtained in different
orders then this section of the trace file indicates the exact rows that
each session is waiting to lock for themselves. Ie: If the lock requests
are TX mode X waits then the 'Rows waited on' may show useful information.
For any other lock type / mode the 'Rows waited on' is not relevant and
usually shows as "no row".
In the above example:
SID 10 was waiting for ROWID 'AAAAv2AAEAAAAqKAAB' of object 0xBF6
(which is 3062 in decimal)
SID 11 was waiting for ROWID 'AAAAv2AAEAAAAqKAAA' of object
0xBF6
This can be decoded to show the exact row/s. Eg: SID 10 can be shown to be waiting thus:
SELECT owner, object_name, object_type
FROM dba_objects WHERE object_id = 3062;
Owner Object_Name Object_Type
------- --------------- ---------------
SYSTEM EG_60 TABLE
SELECT * FROM system.eg_60 WHERE ROWID='AAAAv2AAEAAAAqKAAB';
NUM TXT
---------- ----------
2 Second
Gene Hubert wrote:
> How do I intrepret this deadlock info from a trace file. I was
> able to determine the table and row from the last two lines, but
> don't know what to do with the rest of this.
> Gene Hubert, Qualex Inc., Durham, NC
> ---------------------
> ksqded1: deadlock detected via did
>
> Deadlock graph:
> (where pr=process,ss=session,hd=holds,wt=waits)
> -Blocker(s) -Waiter(s)-
> Resource Name pr ss hd wt pr ss hd wt
> TX-0006000f-00000253 15 8 X 11 17 X
> TX-00070012-00000252 11 17 X 15 8 X
>
> session 8: DID 0001-000F-00000001 session 17: DID
> 0001-000B-00000001
>
> session 17: DID 0001-000B-00000001 session 8: DID
> 0001-000F-00000001
>
> Rows waited on:
> Session 17: obj - rowid = 00002FFA - AAAC/6AAGAAAGHEAAa
> Session 8: obj - rowid = 00002FFA - AAAC/6AAGAAAGHNAAq
--
Regards
Pete
Peter Sharman Email: psharman_at_us.oracle.com WISE Course Development Manager Phone: +1.650.607.0109 (int'l) Worldwide Internal Services Education (650)607 0109 (local)San Francisco
SQL> select standard_disclaimer, witty_remark 2 from company_requirements;
Opinions are mine and do not necessarily reflect those of Oracle Corporation
"Controlling application developers is like herding cats."
Kevin Loney, ORACLE DBA Handbook
"Oh no it's not! It's much harder than that!"
Bruce Pihlamae, long term ORACLE DBA
Received on Fri Mar 12 1999 - 11:53:29 CST
 |
 |