Re: Having Deadlock Problem

"Jonathan Lewis" <jonathan_at_jlcomp.demon.co.uk> wrote:
> 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.

Jonathan,

From the holds-waits list in the trace file, it looks like there there is one extra transaction in the mix, but that there isn't a closed loop. I.e, session 13 is blocking 20, and 20 is blocking 43, but there is no indication that 43 is blocking 13.

Am I misinterpreting something here?

Thanks,

Xho

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