Having Deadlock Problem

From: andy <anandv81_at_rediffmail.com>
Date: 4 Jun 2004 03:01:13 -0700
Message-ID: <9bfbb3fd.0406040201.2d1fce2b@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

Release:	5.8
Version:	Generic_108528-27
Machine:	sun4u

Instance name: POS
Redo thread mounted by this instance: 1
Oracle process number: 36
Unix process pid: 12546, image: oracle_at_hddc48app01 (TNS V1-V3)

• 2004-05-27 16:47:16.930
• SESSION ID:(43.1770) 2004-05-27 16:47:16.900 DEADLOCK DETECTED Current SQL statement for this session: UPDATE STOCKBALANCE SET N_RESERVEDQUANTITY = :b6, B_AUTHENTICATIONSTATUS = :b5, N_AVAILABLEQUANTITY = :b4, N_APPQTYWITHSCHEME = :b3 WHERE V_WAREHOUSECODE = :b2 AND V_STOCKCODE = :b1 ----- PL/SQL Call Stack ----- object line object handle number name 3be780350 8525 package body POSBSCS.INV 394ae5e28 1 anonymous block 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 TX-00060011-00016d27 26 13 X 46 20 X TX-0007000b-0001318b 46 20 X 36 43 X session 13: DID 0001-001A-00006066 session 20: DID 0001-002E-00000002 session 20: DID 0001-002E-00000002 session 43: DID 0001-001A-00006066 Rows waited on: 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) Information on the OTHER waiting sessions: Session 20: pid=46 serial=4420 audsid=3952025 user: 88/POSBSCS O/S info: user: SYSTEM, term: INDEL01STG01, ospid: 17440:16964, machine: EPOS\INDEL01STG01 program: aspnet_wp.exe Current SQL 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)) )
End of information on OTHER waiting sessions.

---

Received on Fri Jun 04 2004 - 05:01:13 CDT