Any DEADLOCK experts there?

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Sat, 18 Nov 2000 10:43:05 GMT
Message-ID: <8v5mfp$a4a$1@nnrp1.deja.com>

 Hi !

I have been investigating this issue for some time and initially i had thought the issue was unindexed foreign keys. Now both sessions are waiting for an exclusive lock (which is not in case of FK indexes). It generates huge trace files every now and then,.

I am suspecting this is because of ITL entry shortages in that data block, Can anyone confirm this.

Here is the part of the trace file..

Please copy your mail to kaygopal_at_yahoo.com

Thanks in Advance..

DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE ENTITY_ACCOUNT_MST SET ACCT_BALANCE=ACCT_BALANCE + :b1 WHERE ENTITY_TYPE = :b2 AND ENTITY_CODE = :b3 AND ACCT_NAME = :b4 ----- PL/SQL Call Stack -----
  object line object
  handle number name

42ffc5bc       411  package body SCBICASH.ACCOUNTS
42ff92dc        93  function SCBICASH.OUT_NOSTRO_POOL
42ffc5bc       805  package body SCBICASH.ACCOUNTS
42ffc5bc       206  package body SCBICASH.ACCOUNTS
42ffc5bc       104  package body SCBICASH.ACCOUNTS
41307c10       383  procedure SCBICASH.OUTSTATION_SCHEDULE
41301470         1  anonymous block
41301470         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-00090014-00003434        12      13     X             38
39           X
TX-00050032-000032fa        38      39     X             12
13           X
===================================================

PROCESS STATE

---

Process global information:

     process: 44037010, call: 440728d4, xact: 44b52f9c, curses: 44047398, usrses: 44047398

---

  SO: 44037010, type: 1, owner: 0, flag: INIT/-/-/0x00   (process) Oracle pid=13, calls cur/top: 440728d4/4407288c, flag: (0) -

            int error: 0, call error: 0, sess error: 0
(latch info) wait_event=0 bits=10

      holding     40004fac enqueues level=4 state=busy
        gotten 134870 times wait, failed first 34 sleeps 332
        gotten 0 times nowait, failed: 0

    O/S info: user: pmount, term: ?, ospid: 12988     OSD pid info: pid: 32bc, image:

---

    SO: 44047398, type: 3, owner: 44037010, flag: INIT/-/-/0x00
(session) trans: 44b52f9c, flag: (41) USR/- BSY/-/-/-/-/-

              oct: 3, prv: 0, user: 14/SCBICASH     O/S info: user: OraUser, term: Windows PC, ospid: 12638:01, machine:

              program: P:\UAPPS\CASHIN30\EXE\PBDWE040.DLL     last wait for 'enqueue' seq=31142 wait_time=-2

                name|mode=54580006, id1=50032, id2=32fa
      ----------------------------------------
      SO: 44bbd138, type: 17, owner: 44047398, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=44bbd138 handle=0 lock=44bbe6b4
      user=44047398 session=44047398 count=0 mask=00 savepoint=286516
flags=[00]
      ----------------------------------------
      SO: 44bbe6b4, type: 16, owner: 44047398, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=44bbe6b4 handle=42f557a8 mode=N
      call pin=0 session pin=44bbd138
      user=44047398 session=44047398 count=1 flags=[00] savepoint=286516
      LIBRARY OBJECT HANDLE: handle=42f557a8
      namespace=CRSR flags=RON/PN0/[10010000]
      kk-dd-aa-ll=00-41-40-41 lock=N pin=0
      lwt=42f557c0[42f557c0,42f557c0] ltm=42f557c8[42f557c8,42f557c8]
      pwt=42f557d8[42f557d8,42f557d8] ptm=42f5582c[42f5582c,42f5582c]
      ref=42f557b0[42f56abc,42f56abc]
        LIBRARY OBJECT: object=42f55534
        type=CRSR flags=EXS[0001] status=VALD load=0
        DEPENDENCIES: count=1 size=16
        AUTHORIZATIONS: count=1 size=16 entrysize=12
        ACCESSES: count=1 size=16
        TRANSLATIONS: count=1 size=16
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 42f5573c 42f555f0 I/P/A     0 NONE
            6 42f555a4 42f5449c I/-/A     0 NONE
      ----------------------------------------
      SO: 44c36324, type: 17, owner: 44047398, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=44c36324 handle=0 lock=44c3989c
      user=44047398 session=44047398 count=0 mask=00 savepoint=286123

flags=[00]

--
Best Regards,
K Gopalakrishnan,
Bangalore,  INDIA.


Sent via Deja.com http://www.deja.com/
Before you buy.

Received on Sat Nov 18 2000 - 04:43:05 CST