Re: Any DEADLOCK experts there?

I would expect your trace dump to include Process 12 and process 38. Each process should list the SQL executing at the time of the dump, so you can check if they were both doing the same thing at the same time.

Can you be sure that two copies of

    SCBICASH.ACCOUNTS
won't end up trying to update the same rows in the ENTITY_ACCOUNT_MST table in
different orders - is there a PL/SQL cursor loop, perhaps, that does not have a suitable order by on it ?

I can't spot anything in the dump you have posted that would tell us whether it was an ITL deadlock or a data deadlock - however, the absence of any lines like:

    Rows waited on
    Session 12 - obj - rowid = ...
    Session 38 - obj - rowid = ...

suggest your assumption about ITLs is correct. (These lines appear for 8.1 - I am not sure if earlier versions were quite so helpful).

--

Jonathan Lewis
Yet another Oracle-related web site:  http://www.jlcomp.demon.co.uk

Practical Oracle 8i:  Building Efficient Databases

Publishers:                 Addison Wesley Longman
Book bound date:     8th Dec 2000
See a first review at:
http://www.ixora.com.au/resources/index.htm#practical_8i

K Gopalakrishnan wrote in message <8v5mfp$a4a$1_at_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.