Re: Having Deadlock Problem

I've tried to get a three-way 'open graph' deadlock reported with a set of distributed transactions running from sql*plus, and haven't been able to emulate the trace file you got. I always ended up with ORA-02049, and no deadlock graph. Perhaps it's a timing thing, and you have enough very small transactions going on that you hit a critical window.

I can't tell you why the leading transaction gets stuck - either it's an application error, or it's an Oracle bug: in either case, I can't diagnose it remotely.

As far as ITL locks are concerned, the entry in v$lock for the blocked session would show a REQUEST in mode 4 against the transaction of the session that's doing the blocking. There are other reasons for mode 4's though - including bitmap index side-effects, and IOT row locks.

If you have a high degree of concurrency, then the objects (and their indexes) that are the targets for highly concurrent DML may need to have their initrans increased - but since ITLs can grow dynamically and you have pctfree set to 10, then it's not very likely that ITLs / initrans has anything to do with the problem.

The bottom line on ORA-00060 deadlocks is that Oracle is correct - it's usually a flaw in the application design. Two tasks that should not be happening concurrently are doing similar work in the reverse order.

-- 
Regards

Jonathan Lewis

http://www.jlcomp.demon.co.uk

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

http://www.jlcomp.demon.co.uk/seminar.html
Optimising Oracle Seminar - schedule updated May 1st


"andy" <anandv81_at_rediffmail.com> wrote in message
news:9bfbb3fd.0406080400.7b0419d5_at_posting.google.com...


> Hi Jonathan,

>

> Thanks for your reply it was very helpful. You are right , COM+

> components intiate distributed transactions using XA interface

> provided by ORACLE. I have been analyzing this problem for a while now

> and had felt that this was a deadlock problem. What surprised me also

> was that there is no closed loop here .

> What happens is that the first transaction (session 13) gets stuck and

> holds the lock for indefinite period.

>

> The log file was generated by Oracle and the ORA 0249 error might not

> be the reason for this error.

>

> What I'm trying to figure out is why does the first transaction get

> stuck? Is there anyway I could locate this? or maybe atleast shortlist

> the possible causes why the first transaction getting stuck.

>

> Thanks in advance

>

> Andy

>

>