Re: Determine exact cause of deadlock based on trace file

gautrak_at_yahoo.com (Gautrak) wrote:
> Hi,
>
> Below is a deadlock graph from the trace file generated.
>
> Kindly help me to derive exact cause and recommended changes in
> database to reduce the probability of these happening.

I see you haven't received any useful replies, so I hope you don't mind if I speculate a little bit on this.

>
> - The tablespaces are locally managed migrated from dictionary managed
> tablespaces (segment space management : USER)
> - The database access is through application servers with connection
> pooling.
> - Primary key of table SK_TB_ANS is (NM_CID, SK_SV_ID, USR_ID, SK_QNO)
> - There is one more index on this table on (NM_CID, SK_SV_ID, USR_ID)

Why is there one index which is just the leading edge of the other index?

> - There is no foreign key on SK_TB_ANS
> - File 16 : Undo tablespace

This is very strange to me. Why would undo ever form one leg of a deadlock triangle? I don't see why two transactions would ever be writing into the same block of undo at the same time, in a transactional manner. That (combined with some other things) makes me wonder if this is not a traditional deadlock but rather some sort of space management issue or Oracle bug.

> - File 14 : Tablespace of table SK_TB_ANS
> - Object# 42293 : table SK_TB_ANS
> - This has happened during a time when large number of users were
> accessing the application

Are they all human users, or is there some kind of automated data processing going on, too? Is the deadlock a frequent occurence? Are commits occuring at every point that they logically should occur? Looking at the application code, what other DMLs are occuring prior to (and in the same transaction as) the DMLs that are reported in the trace file?

> Deadlock graph:
> ---------Blocker(s)--------
> ---------Waiter(s)---------
> Resource Name process session holds waits process session
> holds waits
> TX-008f000c-00000002 33 63 X 49 55
> S
> TX-00110012-000030e5 49 55 X 34 35
> X
> TX-008f000c-00000002 34 35 X 33 63
> X

If I can trust the formatting above, it seems that the first line in the deadlock graph has both sessions as waits on the resource, rather than one "waits" and one "holds". If that is so, one wonders why is the deadlock a deadlock? There would be no cycle. Also, the first line and the last line refer to the same resource, which seems a little odd to me.

I notice that both ordinary table rows being waited on are in the same block. Maybe this is really some kind of ITL issue but Oracle gets confused while untangling it for reporting purposes, turning it into this?

Xho

-- 
-------------------- http://NewsReader.Com/ --------------------
Usenet Newsgroup Service                        $9.95/Month 30GB