Re: Having Deadlock Problem

Andy,

As error message indicates, the 02049 error is usually the result of a distributed transaction. The timeout arises when a process is unable to lock something in a remote database, but can't find out what it happening in the remote database that is causing the problem.

The trace file you have sent us, though, is usually generated by an 00060 error - which is a deadlock within a single instance. In this case, the deadlocked process detects (within three seconds) where the problem lies, and performs a rollback to internal savepoint, dumping some information about the cause of deadlock.

In this particular case, you seem to have a simple data deadlock. You process has attempted to update row 'X' then row 'Y' in turn using the statement:

> UPDATE STOCKBALANCE
> SET
> N_RESERVEDQUANTITY = :b6,
> B_AUTHENTICATIONSTATUS = :b5,
> N_AVAILABLEQUANTITY = :b4,
> N_APPQTYWITHSCHEME = :b3
> WHERE
> V_WAREHOUSECODE = :b2 AND
> V_STOCKCODE = :b1

Another session has attempted to lock the two rows in the reverse order using the statement:

> UPDATE STOCKBALANCE SET N_APPQTYWITHSCHEME = 65 WHERE (
> (V_WAREHOUSECODE = :V00002) AND (V_STOCKCODE = :V00003) AND ((791109
> IS NULL AND N_RESERVEDQUANTITY IS NULL) OR (N_RESERVEDQUANTITY =
> 791109)) AND ((:V00006 IS NULL AND B_AUTHENTICATIONSTATUS IS NULL) OR
> (B_AUTHENTICATIONSTATUS = :V00007)) AND ((14252 IS NULL AND
> N_AVAILABLEQUANTITY IS NULL) OR (N_AVAILABLEQUANTITY = 14252)) AND
> ((55 IS NULL AND N_APPQTYWITHSCHEME IS NULL) OR (N_APPQTYWITHSCHEME =
> 55)) AND ((:V00012 IS NULL AND N_VERSION IS NULL) OR (N_VERSION =
> :V00013)) )

The row ids of the rows that are in direct collision are:

> Session 20: obj - rowid = 0000AB10 - AAALfsAAmAAAFsaAAG
> (dictionary objn - 43792, file - 38, block - 23322, slot - 6)
> Session 43: obj - rowid = 0000AB10 - AAALfsAAmAAAFsaAAA
> (dictionary objn - 43792, file - 38, block - 23322, slot - 0)

You should be able to do:

    select * from stockbalance where rowid = 'AAALfsAAmAAAFsaAAG';     select * from stockbalance where rowid = 'AAALfsAAmAAAFsaAAA';

to find out the details of the rows causing the problem.

I would be a little surprised if this trace file had anything to do with your 02049 error - but it is possible that there is some feature of ASP.NEt(C#) or COM+ that makes all transaction go through the XA interface and appear to be distributed.

-- 
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.0406040201.2d1fce2b_at_posting.google.com...


> Hi,

>

> I am working on an online application built using ASP.NEt(C#) which

> uses COM+ enterprise services and Oracle 9i (Hosted on Sun Solaris),

> the application server is hosted on windows 2000 Advanced Server.

> There is a very severe problem being faced by us that, the

> transactions that are handled by COM+ hang up and take up the

> resources and Database Locks. As a result the subsequent transactions

> fail and are not able to access the database tables. The error code

> returned is

>

> ORA-02049: timeout: distributed transaction waiting for lock

>

> I am new to oracle and having a really bad time with this problem as

> the whole application comes to a standstill. Attached below is a trace

> file provided by my DBA . Can someone please help me with this as I

> dont know how to interpret the data given in this file. Essentially I

> want to know , what could be triggering this behaviour (eg. Resource

> contention, table/Row level locking).

>

> Thanks in advance

>

> Regards

>

> Anand

>