Deadlocks

How can I differentiate if a deadlock is due to insufficient ITL or due to an application coding issue.

Am pasting the deadlock graph from the trace file:-

Deadlock graph:
---------Blocker(s)-------- ---------Waiter(s)---------
Resource Name process session holds waits process session holds waits
TX-00070054-0002f914 27 389 X 35 220 X
TX-00010017-00034176 35 220 X 27 389 X
session 389: DID 0001-002F-000018A9 session 220: DID 0001-0037-0001E759
session 220: DID 0001-0037-0001E759 session 389: DID 0001-002F-000018A9
Rows waited on:
Session 220: obj - rowid = 00006BD6 - AAAPAaAAeAAAPUUAAp
(dictionary objn - 19508, file - 30, block - 62740, slot - 41)
Session 389: obj - rowid = 00006BD6 - AAAPAaAAeAAAPUSAB9
(dictionary objn - 19508, file - 30, block - 62738, slot - 125)

In your case deadlock happened because one session locked the row that was needed by another session.
You can see different rowids.

Session 220: obj - rowid = 00006BD6 - AAAPAaAAeAAAPUUAAp
(dictionary objn - 19508, file - 30, block - 62740, slot - 41)
Session 389: obj - rowid = 00006BD6 - AAAPAaAAeAAAPUSAB9
(dictionary objn - 19508, file - 30, block - 62738, slot - 125)

Tamil

Thanks Tamil.

But would this be any indicator of whether this is an application coding issue, or if the sessions are waiting for ITL slots?

This is an application coding problem. Remember, when there is a shortage of ITL, oracle dynamically creates one in the free space provided the table is created with enough PCTFREE parameter value.

Tamil

Thanks a lot.