Re: finding solution 4 this Special DeadLock

<meysam.khayatan_at_gmail.com> wrote in message news:767a5292-ee6e-4ea3-b4b3-144eaf52b997_at_l1g2000hsa.googlegroups.com...
> Dear friends
> i have a problem in DeadLock
> in udump alert log file i have a deadlock that in trace file
> described as below:
> ===============================================================
> *** 2008-02-06 09:00:06.223
> *** SESSION ID:(32.18099) 2008-02-06 09:00:06.219
> DEADLOCK DETECTED
> Current SQL statement for this session:
> LOCK TABLE PER_ANALYSIS_CRITERIA IN EXCLUSIVE MODE
> 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:
> ---------
> Blocker(s)-------- ---------Waiter(s)---------
> Resource Name process session holds waits process
> session holds waits
> TM-0000aa04-00000000 104 32 X
> 496 195 SS
> TM-0000aa04-00000000 496 195 SS
> 104 32 X
> session 32: DID 0001-0068-00000006 session 195: DID 0001-01F0-00000006
> session 195: DID 0001-01F0-00000006 session 32: DID 0001-0068-00000006
> Rows waited on:
> Session 195: no row
> Session 32: no row
> Information on the OTHER waiting sessions:
> Session 195:
> pid=496 serial=47814 audsid=21880507 user: 183/APPS
> O/S info: user: applprod, term: , ospid: 17365, machine: ESCOERP1
> program:
> client info: 86
> 0
> application name: ESCO_TRAIN_ATT_ABS, hash value=1926534256
> action name: Online-ESCO_TRAINING_RESP, hash value=2617383380
> Current SQL Statement:
> lock table PER_ANALYSIS_CRITERIA in row share mode
> End of information on OTHER waiting sessions.
> ===============================================================
> its all description in my trace file !!!!
> i can't find reason this deadlock
> i'm beginner in oracle dba
> how can i analyse this trace file???
> please tell me reason of this DeadLock and how can i solve it?
> thx
> Best Regards

I'm not sure that this deadlock is as trivial as some people seem to think

It's a little hard to make out because of the word-wrap and variable width font, but it looks as if session 195 is both holding and waiting for an SS lock on PER_ANALYSIS_CRITERIA, which means session 32 is waiting for its X lock because 195 is holding an SS, at the same time 195 can't get the SS because 32 is ahead of it waiting for an X.

We need to know which version - exactly - of Oracle, and whether there are distributed transactions or parallel DML going on that could be confusing the issue.

-- 
Regards

Jonathan Lewis
http://jonathanlewis.wordpress.com

Author: Cost Based Oracle: Fundamentals
http://www.jlcomp.demon.co.uk/cbo_book/ind_book.html

The Co-operative Oracle Users' FAQ
http://www.jlcomp.demon.co.uk/faq/ind_faq.html