DBAsupport.com Forums - Powered by vBulletin
Results 1 to 4 of 4

Thread: DEADLOCK trace file ...

  1. #1
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818

    DEADLOCK trace file ...

    Shows that the statement causing the deadlock (and the one 'rolled back') was an insert:

    INSERT INTO Label_sequences (sequence_number, date)
    VALUES (:B1, SYSDATE)

    Why would an insert create a DEADLOCK? Could it be firing a trigger maybe ... but then the statement in the trigger would be the deadlock statement and not this one ... no?

  2. #2
    Join Date
    Jan 2001
    Posts
    2,828
    Hi

    Post the trace file here.

    Are the inserts being done parallely

    regards
    Hrishy

  3. #3
    Join Date
    Dec 2002
    Location
    Bangalore ( India )
    Posts
    2,434
    you have pk/uk constraints? and mulitple sessions trying to do dml on the table/tables in a package/anonymous code? then u have every possibility of getting deadlock.


    rgds
    abhay
    funky...

    "I Dont Want To Follow A Path, I would Rather Go Where There Is No Path And Leave A Trail."

    "Ego is the worst thing many have, try to overcome it & you will be the best, if not good, person on this earth"

  4. #4
    Join Date
    Jan 2000
    Location
    Chester, England.
    Posts
    818
    Quote Originally Posted by hrishy
    Hi

    Post the trace file here.

    Are the inserts being done parallely

    regards
    Hrishy

    I don't have the source code, but its an OAS application, running Oracle Forms: 2 web servers, balancing the load.

    Here's the top of the trace file - do you need all of it?

    ****************************************************

    Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - 64bit Production
    With the Partitioning, OLAP and Data Mining options
    ORACLE_HOME = /u01/app/oracle/product/10.2.0
    System name: HP-UX
    Node name: kcmsdbd1
    Release: B.11.11
    Version: U
    Machine: 9000/800
    Instance name: ccopiat
    Redo thread mounted by this instance: 1
    Oracle process number: 36
    Unix process pid: 5881, image: oracle@kcmsdbd1

    *** 2007-09-03 05:56:39.647
    *** SERVICE NAME:(SYS$USERS) 2007-09-03 05:56:39.616
    *** SESSION ID:(276.11101) 2007-09-03 05:56:39.616
    DEADLOCK DETECTED
    [Transaction Deadlock]
    Current SQL statement for this session:
    INSERT INTO LABEL_SEQUENCES (SEQUENCE_NUMBER,DATE_CREATED) VALUES (:B1 ,SYSDATE)
    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-00080004-0000081f 36 276 X 36 276 S
    session 276: DID 0001-0024-00000245 session 276: DID 0001-0024-00000245
    Rows waited on:
    Session 276: obj - rowid = 00000000 - D/////AACAAABNmAAA
    (dictionary objn - 0, file - 2, block - 4966, slot - 0)
    Information on the OTHER waiting sessions:
    End of information on OTHER waiting sessions.
    ===================================================
    PROCESS STATE
    -------------
    Process global information:
    process: c00000002f3ecf20, call: c00000001bae2d78, xact: c00000002de59190, curses: c00000002ea0c4b0, usrses: c00000002ea0c4b0
    ----------------------------------------
    SO: c00000002f3ecf20, type: 2, owner: 0000000000000000, flag: INIT/-/-/0x00
    (process) Oracle pid=36, calls cur/top: c00000001bae2d78/c000000018b4e4c0, flag: (0) -
    int error: 0, call error: 0, sess error: 0, txn error 0
    (post info) last post received: 108 0 4
    last post received-location: kslpsr
    last process to post me: c00000002f3de100 1 6
    last post sent: 0 0 24
    last post sent-location: ksasnd
    last process posted by me: c00000002f3de100 1 6
    (latch info) wait_event=0 bits=10
    holding (efd=23) c000000010612410 Parent+children enqueue hash chains level=4
    Location from where latch is held: ksqcmi: kslgpl:
    Context saved from call: 0
    state=busy, wlstate=free
    recovery area:
    Dump of memory from 0xC00000002F3B20F0 to 0xC00000002F3B2110
    C00000002F3B20F0 00000000 00000000 00000000 00000000 [................]
    Repeat 1 times
    Process Group: DEFAULT, pseudo proc: c00000002e8a4f90
    O/S info: user: oracle, term: UNKNOWN, ospid: 5881
    OSD pid info: Unix process pid: 5881, image: oracle@kcmsdbd1
    Dump of memory from 0xC00000002F3B9920 to 0xC00000002F3B9B28
    C00000002F3B9920 00000010 00000000 C0000000 188A7A38 [..............z8]
    C00000002F3B9930 00000010 0003139D C0000000 18B4E4C0 [................]
    C00000002F3B9940 00000003 0003139D C0000000 2E54BB40 [.............T.@]
    C00000002F3B9950 0000000B 0003139D C0000000 2EA0C4B0 [................]
    C00000002F3B9960 00000004 00031291 C0000000 2D43BA40 [............-C.@]
    C00000002F3B9970 00000007 0003139D C0000000 2D43BB20 [............-C. ]
    C00000002F3B9980 00000007 0003139D C0000000 2D43BC00 [............-C..]
    C00000002F3B9990 00000007 0003139D C0000000 2D43BCE0 [............-C..]
    C00000002F3B99A0 00000007 0003139D C0000000 2D43C698 [............-C..]
    C00000002F3B99B0 00000007 0003139D C0000000 2D43C778 [............-C.x]
    C00000002F3B99C0 00000007 0003139D C0000000 2D43D9F0 [............-C..]
    C00000002F3B99D0 00000007 0003139D C0000000 2D43DAD0 [............-C..]
    C00000002F3B99E0 00000007 0003139D C0000000 2D43E028 [............-C.(]
    C00000002F3B99F0 00000007 0003139D C0000000 2D43E1E8 [............-C..]
    C00000002F3B9A00 00000007 0003139D C0000000 2D43E488 [............-C..]
    C00000002F3B9A10 00000007 0003139D C0000000 2D43FFD8 [............-C..]
    C00000002F3B9A20 00000007 0003139D 00000000 00000000 [................]
    C00000002F3B9A30 00000000 00000000 00000000 00000000 [................]
    Repeat 14 times
    C00000002F3B9B20 00000000 00000000 [........]
    (FOB) flags=2 fib=c00000002d6e1b90 incno=0 pending i/o cnt=0
    fname=/u04/oradata/ccopiat/undotbs_01.dbf
    fno=2 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e3c60 incno=0 pending i/o cnt=0
    fname=/u10/oradata/ccopiat/clinicopia_invt.dbf
    fno=11 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e4000 incno=0 pending i/o cnt=0
    fname=/u04/oradata/ccopiat/temp_01.dbf
    fno=201 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e3508 incno=0 pending i/o cnt=0
    fname=/u11/oradata/ccopiat/clinicopia_data_02.dbf
    fno=9 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e2a28 incno=0 pending i/o cnt=0
    fname=/u13/oradata/ccopiat/clinicopia_jrnl_02.dbf
    fno=6 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e2dc8 incno=0 pending i/o cnt=0
    fname=/u06/oradata/ccopiat/clinicopia_jrnl_01.dbf
    fno=7 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e3168 incno=0 pending i/o cnt=0
    fname=/u12/oradata/ccopiat/clinicopia_data_01.dbf
    fno=8 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e1438 incno=0 pending i/o cnt=0
    fname=/backups/oradata/ccopiat/control03.ctl
    fno=2 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e1098 incno=0 pending i/o cnt=0
    fname=/u02/oradata/ccopiat/control02.ctl
    fno=1 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e0cf8 incno=0 pending i/o cnt=0
    fname=/u01/oradata/ccopiat/control01.ctl
    fno=0 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e38c0 incno=0 pending i/o cnt=0
    fname=/u02/oradata/ccopiat/clinicopia_indx_01.dbf
    fno=10 lblksz=4 fsiz=7798248
    (FOB) flags=2 fib=c00000002d6e17d8 incno=0 pending i/o cnt=0
    fname=/u08/oradata/ccopiat/system_01.dbf
    fno=1 lblksz=4 fsiz=7798248
    ----------------------------------------
    SO: c00000002ea0c4b0, type: 4, owner: c00000002f3ecf20, flag: INIT/-/-/0x00
    (session) sid: 276 trans: c00000002de59190, creator: c00000002f3ecf20, flag: (8100041) USR/- BSY/-/-/-/-/-
    DID: 0001-0024-00000245, short-term DID: 0000-0000-00000000
    txn branch: 0000000000000000
    oct: 0, prv: 0, sql: 0000000000000000, psql: c000000029d88638, user: 45/
    O/S info: user: SYSTEM, term: USKCM-SAPP11, ospid: 888:8028, machine: APTUIT\USKCM-SAPP11
    program: frmweb.exe
    application name: frmweb.exe, hash value=0
    last wait for 'enq: TX - row lock contention' blocking sess=0xc00000002ea0c4b0 seq=4245 wait_time=2944690 seconds since wait started=2
    name|mode=54580004, usn<<16 | slot=80004, sequence=81f
    Dumping Session Wait History
    for 'enq: TX - row lock contention' count=1 wait_time=2944690
    name|mode=54580004, usn<<16 | slot=80004, sequence=81f
    for 'db file sequential read' count=1 wait_time=58
    file#=2, block#=1366, blocks=1
    for 'db file sequential read' count=1 wait_time=24
    file#=8, block#=89ca, blocks=1
    for 'db file sequential read' count=1 wait_time=47
    file#=8, block#=89e1, blocks=1
    for 'db file sequential read' count=1 wait_time=16786
    file#=a, block#=8471, blocks=1
    for 'db file sequential read' count=1 wait_time=19437
    file#=a, block#=8480, blocks=1
    for 'db file sequential read' count=1 wait_time=10440
    file#=a, block#=8465, blocks=1
    for 'db file sequential read' count=1 wait_time=60360
    file#=a, block#=8476, blocks=1
    for 'db file sequential read' count=1 wait_time=12820
    file#=a, block#=8473, blocks=1
    for 'db file sequential read' count=1 wait_time=13968
    file#=a, block#=846d, blocks=1
    temporary object counter: 2

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  


Click Here to Expand Forum to Full Width