Solved

ORA-000060 Deadlock

Posted on 2004-10-12
15
6,133 Views
Last Modified: 2010-08-05
Hello,

once again we've got one of these Oracle riddles...

We have set up a new database with the same data structures we are using for years now, but this time we got problems. There are, of course, differences in the databases: Oracle version, operating system, backup concept etc. vary from instance to instance. However, we do not understand why we have this type of problem.

There is one table with about 1000 - 10000 data sets in it. There are a lot of contraints and indexes on the table. There are also triggers which control the data inserted or updated.

What we are trying to do is update a column which is not involved in the constraints. We get an error ORA-000060 (deadlock while waiting for ressource).

I would be happy to provide any more information but there is so much I could tell you about the table that I don't know where to start :-).

What are possible reasons for the deadlock, and how do we get rid of that?
0
Comment
Question by:akrueger
15 Comments
 
LVL 23

Accepted Solution

by:
seazodiac earned 300 total points
ID: 12286813
ok,  deadlocks are very rare in Oracle, so when it happens, oracle record the information to the alert_<SID>.log file

can you post the section of alert.log when you think ORA-000060 occurs.


you can search throught the alert.log file using this errror code.

let me know
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12287135
Once you have found the ORA-00060 error message, you can go to the trace file indicated by this alert.log

you can identify further the exact problem causing this deadlock.
0
 

Author Comment

by:akrueger
ID: 12287572
OK, I found the trace file. It is about 1 MB, so I cannot post it here. Which section do you need? Are there any keywords I can look for?
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12287597
search for ORA , and nail down to the portion that shows the deadlock error.

and post that section up here...
0
 

Author Comment

by:akrueger
ID: 12287987
I cannot find any ORA messages in the file, so I will post about 500 lines. I removed the parts which seem to contain the data of the table (which was ap pretty large piece of the file).

Dump file /var/opt/oracle/admin/product/PRODUCT1/udump/ora_10283_product1.trc
Oracle8i Enterprise Edition Release 8.1.7.4.0 - 64bit Production
With the Partitioning option
JServer Release 8.1.7.4.0 - 64bit Production
ORACLE_HOME = /opt/oracle/app/oracle/product/8.1.7
System name:      HP-UX
Node name:      byfeo1
Release:      B.11.00
Version:      D
Machine:      9000/800
Instance name: PRODUCT1
Redo thread mounted by this instance: 1
Oracle process number: 24
Unix process pid: 10283, image: oracle@byfeo1 (TNS V1-V3)

*** 2004-10-12 15:55:30.822
*** SESSION ID:(38.62673) 2004-10-12 15:55:30.809
DEADLOCK DETECTED
Current SQL statement for this session:
UPDATE attribute_data  SET deleted=:7 WHERE rowid=:nav_rowid
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
TM-00006291-00000000        24      38     S             34      58          SX
TM-00006292-00000000        34      58    SX             24      38           S
session 38: DID 0001-0018-00000002      session 58: DID 0001-0022-000EB2F9
session 58: DID 0001-0022-000EB2F9      session 38: DID 0001-0018-00000002
Rows waited on:
Session 58: no row
Session 38: no row
===================================================
PROCESS STATE
-------------
Process global information:
     process: c000000008077370, call: c000000008658690, xact: c0000000085491f0, curses: c0000000080a6190, usrses: c0000000080a6190
  ----------------------------------------
  SO: c000000008077370, type: 1, owner: 0, pt: 0, flag: INIT/-/-/0x00
  (process) Oracle pid=24, calls cur/top: c000000008658690/c000000008658690, flag: (0) -
            int error: 0, call error: 0, sess error: 0, txn error 0
  (post info) last post received: 0 0 18
              last post received-location: ksqrcl
              last process to post me: c0000000080751f0 1 0
              last post sent: 13835058055422496664 0 1
              last post sent-location: kslfre
              last process posted by me: c000000008078860 233 0
    (latch info) wait_event=0 bits=10
      holding     c0000000358b39c0 Parent+children enqueue hash chains level=4
        Location from where latch is held: ksqcmi: kslgpl:
        Context saved from call: 0
        state=busy
        recovery area:
Dump of memory from 0xC000000008070BB0 to 0xC000000008070BC0
08070BB0 00000000 00000000 00000000 00000000  [................]
    Process Group: DEFAULT, pseudo proc: c00000000808b378
    O/S info: user: oracle, term: UNKNOWN, ospid: 10283
    OSD pid info: 10283
    ----------------------------------------
    SO: c0000000080a6190, type: 3, owner: c000000008077370, pt: 0, flag: INIT/-/-/0x00
    (session) trans: c0000000085491f0, creator: c000000008077370, flag: (41) USR/- BSY/-/-/-/-/-
              DID: 0001-0018-00000002, short-term DID: 0000-0000-00000000
              txn branch: 0
              oct: 0, prv: 0, user: 26/PRODUCT
    O/S info: user: Administrator, term: EUROPA-ALT, ospid: 612:816, machine: GLOBUS1\EUROPA-ALT
              program: SQLNav4.exe
    last wait for 'enqueue' blocking sess=0x80b1f90 seq=414 wait_time=308
                name|mode=544d0004, id1=6292, id2=0
      ----------------------------------------
      SO: c0000000086af6b0, type: 36, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=c0000000086af6b0 handle=c00000001063ab80 mode=S lock=c00000001808aa18
      user=c0000000080a6190 session=c0000000080a6190 count=1 mask=0041 savepoint=61 flags=[00]
      ----------------------------------------
      SO: c00000001808aa18, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c00000001808aa18 handle=c00000001063ab80 mode=N
      call pin=0 session pin=c0000000086af6b0
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=PNS/[08] savepoint=61
      LIBRARY OBJECT HANDLE: handle=c00000001063ab80
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0041-0041 lock=N pin=S latch=2
      lwt=c00000001063abb0[c00000001063abb0,c00000001063abb0] ltm=c00000001063abc0[c00000001063abc0,c00000001063abc0]
      pwt=c00000001063abe0[c00000001063abe0,c00000001063abe0] ptm=c00000001063ac70[c00000001063ac70,c00000001063ac70]
      ref=c00000001063ab90[c00000001e4b06b0,c00000001e4b06b0] lnd=c00000001063ac88[c00000001063ac88,c00000001063ac88]
        LIBRARY OBJECT: object=c000000008f4a060
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        DEPENDENCIES: count=15 size=16
        AUTHORIZATIONS: count=1 size=16 minimum entrysize=17
        ACCESSES: count=1 size=16
        TRANSLATIONS: count=1 size=16
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c00000000b8f2728 c00000000a8fbed0 I/P/A     0 NONE  
            6 c000000008f4a170 c00000001d042b78 I/P/A     1 NONE  
      ----------------------------------------
      SO: c00000001dfb4e80, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c00000001dfb4e80 handle=c00000001dcfb2a8 mode=N
      call pin=c00000001e241378 session pin=0
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=[00] savepoint=13
      LIBRARY OBJECT HANDLE: handle=c00000001dcfb2a8
      name=UPDATE attribute_data  SET deleted=:7 WHERE rowid=:nav_rowid
      hash=39fb32d timestamp=10-11-2004 16:29:29
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch=2
      lwt=c00000001dcfb2d8[c00000001dcfb2d8,c00000001dcfb2d8] ltm=c00000001dcfb2e8[c00000001dcfb2e8,c00000001dcfb2e8]
      pwt=c00000001dcfb308[c00000001dcfb308,c00000001dcfb308] ptm=c00000001dcfb398[c00000001dcfb398,c00000001dcfb398]
      ref=c00000001dcfb2b8[c00000001dcfb2b8,c00000001dcfb2b8] lnd=c00000001dcfb3b0[c00000001dcfb3b0,c00000001dcfb3b0]
        LIBRARY OBJECT: object=c00000001e4b0460
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 c000000010468520 c00000001e4b06b0 c00000001063ab80
             1 c000000010468520 c0000000104682d0 c00000000fb78958
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c0000000134974c0 c00000001e4b0550 I/P/A     0 NONE  
      ----------------------------------------
      SO: c0000000085491f0, type: 25, owner: c0000000080a6190, pt: 0, flag: INIT/-/-/0x00
      (trans) bsn = 7, flg = 0x1e03, flg2 = 0x00, prx = 0x0, ros = 2147483647, spn = 62
      efd = 5
      parent xid: 0x0000.000.00000000
      env: (scn: 0x0707.a8984376  xid: 0x0005.049.0003957e  uba: 0x00800161.982d.4c)
      cev: (spc = 8466  usi = 5  ubk tsn: 1 rdba: 0x00800161  useg tsn: 1 rdba: 0x00800156
            hwm uba: 0x00800161.982d.4c  col uba: 0x00000000.0000.00
            num bl: 1 bk list: 0xc000000008535130)
      (enqueue) TX-00050049-0003957E      DID: 0001-0018-00000002
      lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
      res: c00000000813d268, mode: X, prv: c00000000813d278, sess: c0000000080a6190, proc: c000000008077370
            xga: 0x0, heap: UGA
        ----------------------------------------
        SO: c00000000852c638, type: 22, owner: c0000000085491f0, pt: 0, flag: INIT/-/-/0x00
        DML LOCK: tab=25234 flg=01 chi=0
                  his[0]: mod=4 spn=62
        (enqueue) TM-00006292-00000000      DID: 0001-0018-00000002
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
        res: c00000000813ccb8, req: S, prv: c00000000813ccd8, sess: c0000000080a6190, proc: c000000008077370
        ----------------------------------------
        SO: c00000000852d550, type: 22, owner: c0000000085491f0, pt: 0, flag: INIT/-/-/0x00
        DML LOCK: tab=25233 flg=01 chi=0
                  his[0]: mod=4 spn=62
        (enqueue) TM-00006291-00000000      DID: 0001-0018-00000002
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
        res: c00000000813d0c8, mode: S, prv: c00000000813d0d8, sess: c0000000080a6190, proc: c000000008077370
        ----------------------------------------
        SO: c00000000852cb78, type: 22, owner: c0000000085491f0, pt: 0, flag: INIT/-/-/0x00
        DML LOCK: tab=25101 flg=01 chi=0
                  his[0]: mod=4 spn=62
        (enqueue) TM-0000620D-00000000      DID: 0001-0018-00000002
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
        res: c00000000813c638, mode: S, prv: c00000000813c648, sess: c0000000080a6190, proc: c000000008077370
        ----------------------------------------
        SO: c00000000852cec0, type: 22, owner: c0000000085491f0, pt: 0, flag: INIT/-/-/0x00
        DML LOCK: tab=25288 flg=01 chi=0
                  his[0]: mod=4 spn=62
        (enqueue) TM-000062C8-00000000      DID: 0001-0018-00000002
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
        res: c00000000813c500, mode: S, prv: c00000000813c510, sess: c0000000080a6190, proc: c000000008077370
        ----------------------------------------
        SO: c00000000852d4a8, type: 22, owner: c0000000085491f0, pt: 0, flag: INIT/-/-/0x00
        DML LOCK: tab=25287 flg=01 chi=0
                  his[0]: mod=4 spn=62
        (enqueue) TM-000062C7-00000000      DID: 0001-0018-00000002
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
        res: c00000000813c770, mode: S, prv: c00000000813c780, sess: c0000000080a6190, proc: c000000008077370
        ----------------------------------------
        SO: c000000008535130, type: 24, owner: c0000000085491f0, pt: 0, flag: -/-/-/0x00
        (List of Blocks) next index = 1
        index   savepoint   itli   buffer hint
        --------------------------------------
            0           9      1   0xc0000000081b75d0
        ----------------------------------------
        SO: c00000000852c788, type: 22, owner: c0000000085491f0, pt: 0, flag: INIT/-/-/0x00
        DML LOCK: tab=25093 flg=01 chi=0
                  his[0]: mod=2 spn=7
        (enqueue) TM-00006205-00000000      DID: 0001-0018-00000002
        lv: 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00 00
        res: c00000000813cd88, mode: SS, prv: c00000000813cd98, sess: c0000000080a6190, proc: c000000008077370
      ----------------------------------------
      SO: c00000001e2411f8, type: 36, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=c00000001e2411f8 handle=0 lock=c00000000863f5a8
      user=c0000000080a6190 session=c0000000080a6190 count=0 mask=0000 savepoint=3 flags=[00]
      ----------------------------------------
      SO: c00000000863f5a8, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c00000000863f5a8 handle=c00000000cdda108 mode=N
      call pin=0 session pin=c00000001e2411f8
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=[00] savepoint=3
      LIBRARY OBJECT HANDLE: handle=c00000000cdda108
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0041-0041 lock=N pin=0 latch=2
      lwt=c00000000cdda138[c00000000cdda138,c00000000cdda138] ltm=c00000000cdda148[c00000000cdda148,c00000000cdda148]
      pwt=c00000000cdda168[c00000000cdda168,c00000000cdda168] ptm=c00000000cdda1f8[c00000000cdda1f8,c00000000cdda1f8]
      ref=c00000000cdda118[c000000015f63f68,c000000015f63f68] lnd=c00000000cdda210[c00000000cdda210,c00000000cdda210]
        LIBRARY OBJECT: object=c00000000985da80
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        DEPENDENCIES: count=1 size=16
        AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
        ACCESSES: count=1 size=16
        TRANSLATIONS: count=1 size=16
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c0000000187bf0a0 c0000000190e1750 I/P/A     0 NONE  
            6 c00000000985db90 c00000001d97c8e0 I/-/A     0 NONE  
      ----------------------------------------
      SO: c000000008635528, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c000000008635528 handle=c0000000110210a8 mode=N
      call pin=c0000000086af1b0 session pin=0
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=[00] savepoint=1
      LIBRARY OBJECT HANDLE: handle=c0000000110210a8
      name=SELECT * FROM attribute_data WHERE rowid=:nav_rowid FOR UPDATE NOWAIT
      hash=8445cbf6 timestamp=10-11-2004 15:14:19
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch=2
      lwt=c0000000110210d8[c0000000110210d8,c0000000110210d8] ltm=c0000000110210e8[c0000000110210e8,c0000000110210e8]
      pwt=c000000011021108[c000000011021108,c000000011021108] ptm=c000000011021198[c000000011021198,c000000011021198]
      ref=c0000000110210b8[c0000000110210b8,c0000000110210b8] lnd=c0000000110211b0[c0000000110211b0,c0000000110211b0]
        LIBRARY OBJECT: object=c000000015f63d18
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 c00000000f80f0c8 c000000015f63f68 c00000000cdda108
             1 c00000000f80f0c8 c00000000f80ee88 c00000000d8db040
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c000000015f62690 c000000015f63e08 I/P/A     0 NONE  
      ----------------------------------------
      SO: c000000008629660, type: 36, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT PIN: pin=c000000008629660 handle=c00000001d3befb0 mode=S lock=c0000000086350c8
      user=c0000000080a6190 session=c0000000080a6190 count=1 mask=0041 savepoint=2 flags=[00]
      ----------------------------------------
      SO: c0000000086350c8, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c0000000086350c8 handle=c00000001d3befb0 mode=N
      call pin=0 session pin=c000000008629660
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=PNS/[08] savepoint=2
      LIBRARY OBJECT HANDLE: handle=c00000001d3befb0
      namespace=CRSR flags=RON/KGHP/PN0/[10010000]
      kkkk-dddd-llll=0000-0041-0041 lock=N pin=S latch=2
      lwt=c00000001d3befe0[c00000001d3befe0,c00000001d3befe0] ltm=c00000001d3beff0[c00000001d3beff0,c00000001d3beff0]
      pwt=c00000001d3bf010[c00000001d3bf010,c00000001d3bf010] ptm=c00000001d3bf0a0[c00000001d3bf0a0,c00000001d3bf0a0]
      ref=c00000001d3befc0[c00000001b0b2ed0,c00000001b0b2ed0] lnd=c00000001d3bf0b8[c00000001d3bf0b8,c00000001d3bf0b8]
        LIBRARY OBJECT: object=c00000000ea48820
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        DEPENDENCIES: count=1 size=16
        AUTHORIZATIONS: count=1 size=16 minimum entrysize=16
        ACCESSES: count=1 size=16
        TRANSLATIONS: count=1 size=16
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c000000013fd6100 c00000000afcc7e8 I/P/A     0 NONE  
            6 c00000000ea48930 c00000001754ace8 I/P/A     1 NONE  
      ----------------------------------------
      SO: c00000001808ae08, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c00000001808ae08 handle=c00000001641fb70 mode=N
      call pin=c00000001e292f88 session pin=0
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=[00] savepoint=1
      LIBRARY OBJECT HANDLE: handle=c00000001641fb70
      name=
SELECT no, fk_type_no, mtime, ctime, fk_user_no, no_offset, fk_owner_no,
       deleted, remark, code
, ROWID
  FROM attribute_data order by ctime desc
      hash=7610e5d2 timestamp=10-12-2004 09:28:52
      namespace=CRSR flags=RON/KGHP/TIM/PN0/MED/[50010000]
      kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch=2
      lwt=c00000001641fba0[c00000001641fba0,c00000001641fba0] ltm=c00000001641fbb0[c00000001641fbb0,c00000001641fbb0]
      pwt=c00000001641fbd0[c00000001641fbd0,c00000001641fbd0] ptm=c00000001641fc60[c00000001641fc60,c00000001641fc60]
      ref=c00000001641fb80[c00000001641fb80,c00000001641fb80] lnd=c00000001641fc78[c00000001641fc78,c00000001641fc78]
        LIBRARY OBJECT: object=c00000001b0b2c80
        type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
        CHILDREN: size=16
        child#    table reference   handle
        ------ -------- --------- --------
             0 c000000015ee6350 c00000001b0b2ed0 c00000001d3befb0
             1 c000000015ee6350 c00000001b0b2f00 c00000001404a6f0
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c00000001ac065b8 c00000001b0b2d70 I/P/A     0 NONE  
      ----------------------------------------
      SO: c00000000864ca80, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c00000000864ca80 handle=c00000001eee18e0 mode=N
      call pin=c0000000086366d8 session pin=0
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=CBB/[02] savepoint=72
      LIBRARY OBJECT HANDLE: handle=c00000001eee18e0
      name=SYS.DATABASE
      hash=aea96fa0 timestamp=12-31-4712 23:59:59
      namespace=EVNT flags=KGHP/TIM/SML/[02000000]
      kkkk-dddd-llll=0000-0009-0009 lock=N pin=0 latch=1
      lwt=c00000001eee1910[c00000001eee1910,c00000001eee1910] ltm=c00000001eee1920[c00000001eee1920,c00000001eee1920]
      pwt=c00000001eee1940[c00000001eee1940,c00000001eee1940] ptm=c00000001eee19d0[c00000001eee19d0,c00000001eee19d0]
      ref=c00000001eee18f0[c00000001eee18f0,c00000001eee18f0] lnd=c00000001eee19e8[c00000001eee19e8,c00000001eee19e8]
        LIBRARY OBJECT: object=c00000001eee15f8
        type=EVNT flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c00000001eedfe58 c00000001eee1790 I/-/A     0 NONE  
            3 c00000001eee1708 c00000001eee1368 I/-/A     0 NONE  
      ----------------------------------------
      SO: c000000008659ec0, type: 35, owner: c0000000080a6190, flag: INIT/-/-/0x00
      LIBRARY OBJECT LOCK: lock=c000000008659ec0 handle=c00000001e904020 mode=N
      call pin=c000000014c3c260 session pin=0
      user=c0000000080a6190 session=c0000000080a6190 count=1 flags=CBB/[02] savepoint=72
      LIBRARY OBJECT HANDLE: handle=c00000001e904020
      name=PRODUCT.PRODUCT
      hash=f656360c timestamp=12-31-4712 23:59:59
      namespace=EVNT flags=KGHP/TIM/SML/[02000000]
      kkkk-dddd-llll=0000-0009-0009 lock=N pin=0 latch=1
      lwt=c00000001e904050[c00000001e904050,c00000001e904050] ltm=c00000001e904060[c00000001e904060,c00000001e904060]
      pwt=c00000001e904080[c00000001e904080,c00000001e904080] ptm=c00000001e904110[c00000001e904110,c00000001e904110]
      ref=c00000001e904030[c00000001e904030,c00000001e904030] lnd=c00000001e904128[c00000001e904128,c00000001e904128]
        LIBRARY OBJECT: object=c00000001a276e30
        type=EVNT flags=EXS/LOC[0005] pflags= [00] status=VALD load=0
        DATA BLOCKS:
        data#     heap  pointer status pins change
        ----- -------- -------- ------ ---- ------
            0 c00000001a277100 c00000001a276fc8 I/-/A     0 NONE  
            3 c00000001a276f40 c00000001ce6d100 I/-/A     0 NONE  
      ----------------------------------------
      SO: c00000001277b180, type: 34, owner: c0000000080a6190, pt: 0, flag: INIT/-/-/0x00
      user lock: lock=c00000001277b180 mode=S
      user resource: user=c000000008656dc8 uid=26 mode=S
      ----------------------------------------
      SO: c000000014bd4fe0, type: 34, owner: c0000000080a6190, pt: 0, flag: INIT/-/-/0x00
      user lock: lock=c000000014bd4fe0 mode=S
      user resource: user=c000000008656dc8 uid=26 mode=S
    ----------------------------------------
    SO: c00000000814dc40, type: 9, owner: c000000008077370, pt: 0, flag: INIT/-/-/0x00
    (broadcast handle) flag: (2) ACTIVE SUBSCRIBER, owner: c000000008077370,
                       event: 1147969, last message event: 1147969, messages read: 0
                       channel: (c000000008150a00) system events broadcast channel
                                scope: 101, event: 1148030, last mesage event: 0,
                                publishers/subscribers: 0/33,
                                messages published: 0
    ----------------------------------------
    SO: c000000008658690, type: 2, owner: c000000008077370, pt: 0, flag: INIT/-/-/0x00
    (call) sess: cur c0000000080a6190, rec 0, usr c0000000080a6190; depth: 0
===================================================
CURRENT SESSION'S INSTANTIATION STATE
-------------------------------------
current session=c0000000080a6190
-------------------------------------
INSTANTIATION OBJECT: object=80000001000b2400
type="KOKA open cursor"[3] lock=0 handle=0 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=80000001000b2490 size=16
00000000 00000005 0000000a 00000000
-------------------------------------
INSTANTIATION OBJECT: object=80000001000b4f48
type="KOKA pseudo cursor"[4] lock=0 handle=0 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=80000001000b4fd8 size=16
00000000 00000005 0000000a 00000000
-------------------------------------
INSTANTIATION OBJECT: object=80000001000b79e0
type="cursor"[2] lock=c00000001808ae08 handle=c00000001641fb70 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=80000001000b7a70 size=624
c0000000 1d3befb0 c0000000 086350c8 40000000c0000000 8000000108629660 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
0000000b 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000080000001 800003ff000bbc48
00000000 000000ff 80100074 04000408 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 c0000000 1224d9b8 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b3a80 80000001 000b3a38 4000000080000001 80000001000b3a80 4000000000000002 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b2580 00000006 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b0340 00000240 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000020200 800003ff00000000
00000000 00000000 00000000 62696e64 4000000020766172 8000000120686561 4000000070000000 800003ff7fff7fff
7fff7fff 00000000 00000000 00000000 4000000000000000 8000000100000218 4000000080000001 800003ff000b7be8
80000001 000b7be8 00000000 00000418 4000000080000001 80000001000b7c00 4000000080000001 800003ff000b7c00
80000001 000b6f28 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 400000000000001a 800000010000001a 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b0140 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000
-------------------------------------
INSTANTIATION OBJECT: object=80000001000bb8e8
type="cursor"[2] lock=c000000008635528 handle=c0000000110210a8 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=80000001000bb978 size=624
c0000000 0cdda108 c0000000 0863f5a8 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000bb898 80000001 000bb8b8 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
0000000a 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 000000ff 00110424 05000008 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 80000001 000bd3f8 4000000080000001 80000001000bb848 4000000000000002 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b25d0 00000006 03000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b0340 00000240 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000020200 800003ff00000000
00000000 00000000 00000000 62696e64 4000000020766172 8000000120686561 4000000070000000 800003ff7fff7fff
7fff7fff 00000000 00000000 00000000 4000000000000000 8000000100000218 4000000080000001 800003ff000bbaf0
80000001 000bbaf0 00000000 00000418 4000000080000001 80000001000bbb08 4000000080000001 800003ff000bbb08
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 400000000000001a 800000010000001a 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000
-------------------------------------
INSTANTIATION OBJECT: object=80000001000bb530
type="cursor"[2] lock=c00000001dfb4e80 handle=c00000001dcfb2a8 body=0 level=0
flags=FST[60] executions=0
REST OF INSTANTIATION OBJECT:
address=80000001000bb5c0 size=624
c0000000 1063ab80 c0000000 1808aa18 40000000c0000000 80000001086af6b0 4000000000000000 800003ff00000000
80000001 000bb4d8 80000001 000bb500 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00100424 01100808 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000002 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b2620 00000006 06000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
80000001 000b0340 00000240 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000020200 800003ff00000000
00000000 00000000 00000000 62696e64 4000000020766172 8000000120686561 4000000070000000 800003ff7fff7fff
7fff7fff 00000000 00000000 00000000 4000000000000000 8000000100000218 4000000080000001 800003ff000bb738
80000001 000bb738 00000000 00000418 4000000080000001 80000001000bb750 4000000080000001 800003ff000bb750
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 400000000000001a 800000010000001a 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000 4000000000000000 8000000100000000 4000000000000000 800003ff00000000
00000000 00000000 00000000 00000000
********************   Cursor Dump   ************************
Current cursor: 3, pgadep: 0
Cursor Dump:
----------------------------------------
Cursor 1 (80000001000b2580): CURROW  curiob: 80000001000b79e0
 curflg: 46 curpar: 0 curusr: 0 curses c0000000080a6190
 cursor name:
SELECT no, fk_type_no, mtime, ctime, fk_user_no, no_offset, fk_owner_no,
       deleted, remark, code
, ROWID
  FROM attribute_data order by ctime desc
 child pin: c000000008629660, child lock: c0000000086350c8, parent lock: c00000001808ae08
 xscflg: 80100074, parent handle: c00000001641fb70, xscfl2: 4000408
  nxt: 3.0x00000550  nxt: 2.0x00000228  nxt: 1.0x00000598
Cursor frame allocation dump:
frm: -------- Comment --------  Size  Seg Off
 whp size: 314896/316160
Dump of CURRENT WORK HEAP:
******************************************************
HEAP DUMP heap name="cursor work he"  desc=0x80000001000b6f28
 extent sz=0x1068 alt=32767 het=32767 rec=0 flg=2 opc=2
 parent=80000001000b0340 owner=0 nex=0 xsz=0x4070
EXTENT 0
  Chunk 80000001000c25d0 sz=    16480    freeable  "sort subheap   "  ds=80000001000b70b8
000C25D0 10B38F00 00004061 00000000 00000000  [......@a........]
000C25E0 80000001 000B6F28 80000001 000B70B8  [......o(......p.]
000C25F0 80000001 0011E1A0 50B38F00 00004039  [........P.....@9]
000C2600 00000000 00000000 80000001 0011E1B0  [................]
000C2610 00004020 00000000 006C0000 00000000  [..@ .....l......]
000C2620 00000000 00000000 00077868 0A0C0E21  [..........xh...!]

+++++ Much more of that +++++

00119FA0 05020000 9B007B00 02C10200 00000000  [......{.........]
00119FB0 024E4F00 28477261 6E756C61 74204765  [.NO.(Granulat Ge]
00119FC0 77696368 74204772 2E204175 73747269  [wicht Gr. Austri]
00119FD0 74742C20 31346D67 2B2D326D 67000E56  [tt, 14mg+-2mg..V]
00119FE0 30324752 33315F51 4747315F 58746F72  [02GR31_QGG1_Xtor]
00119FF0 56453030 31001056 32303254 5230355F  [VE001..V202TR05_]
0011A000 4D303037 325F5800 00000000 00000000  [M0072_X.........]
0011A010 00000000 00000000 00000000 00000000  [................]
  Repeat 1 times
EXTENT 5
  Chunk 8000000100111f00 sz=    16472    freeable  "sort subheap   "  ds=80000001000b70b8
00111F00 10B38F00 00004059 00000000 00000000  [......@Y........]
00111F10 80000001 000B6F28 80000001 000B70B8  [......o(......p.]
00111F20 80000001 0010DE40 50B38F00 00004031  [.......@P.....@1]
00111F30 00000000 00000000 80000001 0010DE50  [...............P]

+++++ Much more of that +++++

00111E30 66FC7220 42656CFC 6674756E 67204472  [f.r Bel.ftung Dr]
00111E40 75636B66 6C617363 68652031 000E5630  [uckflasche 1..V0]
00111E50 32575433 305F5030 30355F58 00000000  [2WT30_P005_X....]
00111E60 00000000 00000000 00000000 00000000  [................]
  Repeat 1 times
EXTENT 7
  Chunk 8000000100109d50 sz=    16472    freeable  "sort subheap   "  ds=80000001000b70b8
00109D50 10B38F00 00004059 00000000 00000000  [......@Y........]
00109D60 80000001 000B6F28 80000001 000B70B8  [......o(......p.]
00109D70 80000001 00105C90 50B38F00 00004031  [......\.P.....@1]
00109D80 00000000 00000000 80000001 00105CA0  [..............\.]

+++++ Much more of that +++++

000BE690 02000099 0200009D 00000000 00000000  [................]
000BE6A0 00000000 00000000 00000000 00000000  [................]
  Chunk 80000001000be6b0 sz=      664    freeable  "sort           "
000BE6B0 10B38F00 00000299 80000001 000BE5D0  [................]
000BE6C0 40000000 0022EA58 00000006 00000002  [@....".X........]
000BE6D0 40000000 00645608 C0000000 1E8DD078  [@....dV........x]
000BE6E0 00000000 00000000 80000001 000B7158  [..............qX]
000BE6F0 00000000 00000000 00000000 00000000  [................]
000BE700 005B8D80 000F4240 00000002 00000052  [.[....B@.......R]
000BE710 00000052 00048000 00000002 00000002  [...R............]
000BE720 00000000 00003FBC 00000550 00000000  [......?....P....]
000BE730 00000000 00000000 00000000 00000000  [................]
000BE740 00000001 00000000 00000002 00000000  [................]
000BE750 00000000 00004000 00210000 00000000  [......@..!......]
000BE760 00000000 00000000 00000000 00000000  [................]
        Repeat 6 times
000BE7D0 00000000 000008D1 000008D1 00000000  [................]
000BE7E0 00000000 000043CC 00000000 00000000  [......C.........]
000BE7F0 00000000 00000000 00000000 00000000  [................]
        Repeat 16 times
000BE900 80000001 000B6F28 80000001 00004D00  [......o(......M.]
000BE910 00000000 00000000 00000000 00000000  [................]
        Repeat 2 times
000BE940 00000008 00000000                    [........]        
Total heap size    =   315296
FREE LISTS:
 Bucket 0 size=536
  Chunk 80000001000be5d0 sz=      224    free      "               "
  Chunk 80000001000b6ff0 sz=      176    free      "               "
 Bucket 1 size=1048
Total free space   =      400
UNPINNED RECREATABLE CHUNKS (lru first):
PERMANENT CHUNKS:
  Chunk 80000001000be288 sz=      840    perm      "perm           "  alo=840
Permanent space    =      840
******************************************************
----------------------------------------
Cursor 2 (80000001000b25d0): CURBOUND  curiob: 80000001000bb8e8
 curflg: 5c curpar: 0 curusr: 0 curses c0000000080a6190
 cursor name: SELECT * FROM attribute_data WHERE rowid=:nav_rowid FOR UPDATE NOWAIT
 child pin: 0, child lock: c00000000863f5a8, parent lock: c000000008635528
 xscflg: 110424, parent handle: c0000000110210a8, xscfl2: 5000008
 bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=8000000100000010 size=4000 offset=0
   No bind buffers allocated
----------------------------------------
Cursor 3 (80000001000b2620): CURBOUND  curiob: 80000001000bb530
 curflg: 4c curpar: 0 curusr: 0 curses c0000000080a6190
 cursor name: UPDATE attribute_data  SET deleted=:7 WHERE rowid=:nav_rowid
 child pin: c0000000086af6b0, child lock: c00000001808aa18, parent lock: c00000001dfb4e80
 xscflg: 100424, parent handle: c00000001dcfb2a8, xscfl2: 1100808
  nxt: 4.0x00000488  nxt: 3.0x00000708  nxt: 2.0x000003e8  nxt: 1.0x000007c0
Cursor frame allocation dump:
frm: -------- Comment --------  Size  Seg Off
 bind 0: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=8000000100000010 size=4000 offset=0
   No bind buffers allocated
 bind 1: dty=1 mxl=4000(4000) mal=00 scl=00 pre=00 oacflg=01 oacfl2=8000000100000010 size=4000 offset=0
   No bind buffers allocated
End of cursor dump
END OF PROCESS STATE


0
 
LVL 9

Assisted Solution

by:konektor
konektor earned 200 total points
ID: 12288282
search in all of your codes updating table "attribute_data"
check http://www.experts-exchange.com/Databases/Oracle/Tools_Development/Q_21153855.html to find out what deadlocks appears ...
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12288367
ok here is what you need to do:

from this part of message:

UPDATE attribute_data  SET deleted=:7 WHERE rowid=:nav_rowid
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
TM-00006291-00000000        24      38     S             34      58          SX
TM-00006292-00000000        34      58    SX             24      38           S


one of this user account has this information on the operating system:

O/S info: user: oracle, term: UNKNOWN, ospid: 10283
   OSD pid info: 10283

O/S info: user: Administrator, term: EUROPA-ALT, ospid: 612:816, machine: GLOBUS1\EUROPA-ALT

so look for user Oracle in the operating system and
the machine name is GLOBUS1\EUROPA-ALT, and kill this process "10283"



conclusions:

1. this statement cause the deadlock, UPDATE attribute_data  SET deleted=:7 WHERE rowid=:nav_rowid
2. your user sessions that  are deadlocking with each other are session_id 38 and 58.
0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 4

Expert Comment

by:Vinay_dba
ID: 12288434
Check session 38 or 58 and what they are doing.
Also check if you have commit in your procedure's/packages.....
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12288507
Another piece of information I can offer you is that:

you don't have to worry about this, it's probably a coincidence.

look at your information above, this "oracle" user on machine: GLOBUS1\EUROPA-ALT
just use the SQL navigator (SQLNAV4.EXE) and test one query...

your dba is probably trying to help your develpers debugging sth without knowing stepping over eachother's toes.


so kill this process, the other session will be going through
0
 
LVL 47

Expert Comment

by:schwertner
ID: 12295210
The combination of ORA-00600 and something other is a pure sign of bugs in your DB.
The fact that you run same DB on other boxes without troubles shows that something is wrong
with the instance.
If the above remarks do not help you then either reinstall the instance or upgrade it to higher version.
0
 

Author Comment

by:akrueger
ID: 12296182
Some additional information:

- The machine EUROPA-ALT is used  for remote service, and we are using it to debug this problem. It is connected by a remote connection which is disconnected after use, so it is unlikely that it causes the problem.

- Actually, there was a change made to the instance that might have to do with this issue, I should have thought of that. We are now using a funcion which performs the following:
 * select user number from our user table
 * insert some data into attribute_data
 * commit
 * select a value from the new line in attribute_data
 * return
The function uses Pragma AUTONOMOUS_TRANSACTION.

- It is possible that other tables have the deadlock problem too, but we have not yet verified that.
0
 
LVL 23

Expert Comment

by:seazodiac
ID: 12296834
akreuger:

Look what I have said in my last post:
>>>>>>>>>>>

look at your information above, this "oracle" user on machine: GLOBUS1\EUROPA-ALT
just use the SQL navigator (SQLNAV4.EXE) and test one query...

your dba is probably trying to help your develpers debugging sth without knowing stepping over eachother's toes.


so kill this process, the other session will be going through



and your fact in your last post:

>>>>>>>>>>.

- The machine EUROPA-ALT is used  for remote service, and we are using it to debug this problem. It is connected by a remote connection which is disconnected after use, so it is unlikely that it causes the problem.









All you need to do is to examine the Database server machine:

if that process with process id "10283" is running, just Kill it.  


BUt note: most likely Oracle database solved the deadlock itself by rollbacking the offensive statement, leave the other one pass.

so deadlock is a short-lived event, it's most likely gone by now.



and your comments--->
The function uses Pragma AUTONOMOUS_TRANSACTION is the tricky point. I think that's exactly the cause.

0
 

Author Comment

by:akrueger
ID: 12296997
The deadlock happens quite often, always when update or delete operations take place on that table and maybe even on other tables. I will try to find the process id for the latest events, may be it is the same process blocking the table for days... (Although, if Oracle chooses one of the two concurrent operations to be killed randomly, no such process should last for days).

btw, the function performs well, we don't have problems with inserts. We have problems with delete and update operations. But of course, this does not necessarily mean the function is OK. We will try if we can change it and see if things improve.
0
 

Author Comment

by:akrueger
ID: 12306828
We have now tried to use the same function (with the Pragma) on another instance on the same machine and everything works excellent.

We have also found that only two tables are involved in the problem; the two tables are linked by a foreign key and belong close together, so it's not really a surprise that they are both involved.

We will now examine the two instances for any difference concerning these tables.
0
 

Author Comment

by:akrueger
ID: 12319646
Problem solved - it was a performance problem caused by an automatic process that inserted a lot of data. Strangely, the tables with the deadlock problem had nothing to do with these inserts.

Anyway, thank you all for your help. I know it is near impossible to give advice on database problems without access to the database.
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Join & Write a Comment

I remember the day when someone asked me to create a user for an application developement. The user should be able to create views and materialized views and, so, I used the following syntax: (CODE) This way, I guessed, I would ensure that useā€¦
Using SQL Scripts we can save all the SQL queries as files that we use very frequently on our database later point of time. This is one of the feature present under SQL Workshop in Oracle Application Express.
This video shows how to recover a database from a user managed backup
This video shows how to copy an entire tablespace from one database to another database using Transportable Tablespace functionality.

757 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now