Deadlock issue

From: K Gopalakrishnan <kaygopal_at_yahoo.com>
Date: Fri, 17 Nov 2000 04:07:21 -0800 (PST)
Message-Id: <10683.122361@fatcity.com>

Hi !

Can anyone tell me the cause for these deadlocks?

Initially i had thought this is related to index-foreign key issues. Now those foreign keys are indexed and all objects are valid.

Any thoughts?

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

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]
      ----------------------------------------
      SO: 44c3989c, type: 16, owner: 44047398, flag:
INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=44c3989c
handle=42fc1a14 mode=N
      call pin=0 session pin=44c36324
      user=44047398 session=44047398 count=1
flags=[00] savepoint=286123
      LIBRARY OBJECT HANDLE: handle=42fc1a14
      namespace=CRSR flags=RON/PN0/[10010000]
      kk-dd-aa-ll=00-41-40-41 lock=N pin=0
      lwt=42fc1a2c[42fc1a2c,42fc1a2c]
ltm=42fc1a34[42fc1a34,42fc1a34]
      pwt=42fc1a44[42fc1a44,42fc1a44]
ptm=42fc1a98[42fc1a98,42fc1a98]
      ref=42fc1a1c[42fc2900,42fc2900]
        LIBRARY OBJECT: object=42fc16d0
        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 42fc19a8 42fc178c I/P/A     0 NONE  
            6 42fc1740 42fc0638 I/-/A     0 NONE  
      ----------------------------------------
      SO: 44c5e424, type: 17, owner: 44047398, flag:
INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=44c5e424 handle=0
lock=44c5fe5c
      user=44047398 session=44047398 count=0 mask=00
savepoint=286103 flags=[00]
      ----------------------------------------
      SO: 44c5fe5c, type: 16, owner: 44047398, flag:
INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=44c5fe5c
handle=42fc3bf0 mode=N
      call pin=0 session pin=44c5e424
      user=44047398 session=44047398 count=1
flags=[00] savepoint=286073
      LIBRARY OBJECT HANDLE: handle=42fc3bf0

Have a nice day !!

Best Regards,
K Gopalakrishnan,
Bangalore, INDIA. Received on Fri Nov 17 2000 - 06:07:21 CST