RE: Deadlock issue

Hello

I think you're right. It's not an fk-related problem, because your sessions try to get not share locks but exclusive locks. I'm not sure, but maybe it's related to low value of the INITRANS? Do you have many concurrent sessions which try to update the table? You can find some info about deadlocks at www.ixora.com.au

Please, let me know when you rectify the problem. I'm interested in such things

Regards,
Eduard

>
>
> 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.
>
> __________________________________________________
> Do You Yahoo!?
> Yahoo! Calendar - Get organized for the holidays!
> http://calendar.yahoo.com/
> --
> Please see the official ORACLE-L FAQ: http://www.orafaq.com
> --
> Author: K Gopalakrishnan
> INET: kaygopal_at_yahoo.com
>
> Fat City Network Services -- (858) 538-5051 FAX: (858) 538-5051
> San Diego, California -- Public Internet access /
> Mailing Lists
> --------------------------------------------------------------------
> To REMOVE yourself from this mailing list, send an E-Mail message
> to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
> the message BODY, include a line containing: UNSUB ORACLE-L
Received on Fri Nov 17 2000 - 07:20:50 CST