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

Thread: Dead Lock Issue

  1. #1
    Join Date
    Nov 2002
    Posts
    170

    Dead Lock Issue

    I am running into a dead lock issue almost once everyday for the past one month. There is a process which is creating this deadlock issue on the database. I am not able to identify and resolve it. COuld someone tell me how to go about idetifying this partucluar process which is causing the dead lock ? It is the exact same trace file which is generated with every dead lock. Also even tho I have a huge Temp tablespace and have increased the sort area by double I am getting cannot increase temp segment by xxxx right after the dead lock.

    I am also pasting/attaching the trace file to this thread. ANy help will be appreciated.

    =================================================================
    /oracle/admin/sblprod/udump/ora_13675_sblprod.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 = /oracle
    System name: HP-UX
    Node name: nhsbpdb1
    Release: B.11.11
    Version: U
    Machine: 9000/800
    Instance name: sblprod
    Redo thread mounted by this instance: 1
    Oracle process number: 24
    Unix process pid: 13675, image: oracle@nhsbpdb1 (TNS V1-V3)

    *** 2005-05-02 19:51:12.258
    *** SESSION ID:(127.27879) 2005-05-02 19:51:12.257
    DEADLOCK DETECTED
    Current SQL statement for this session:
    DELETE FROM SIEBEL.S_SRM_REQUEST
    WHERE ROW_ID = :V1
    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-00330000-000713e3 24 127 X 103 503 X
    TX-002d005b-00072a5e 103 503 X 24 127 X
    session 127: DID 0001-0018-00000002 session 503: DID 0001-0067-00000002
    session 503: DID 0001-0067-00000002 session 127: DID 0001-0018-00000002
    Rows waited on:
    Session 503: obj - rowid = 0000F48B - AAAU5RAAQAAAfwUAA+
    Session 127: obj - rowid = 00027B12 - AAAnsSACaAAB3n8AAk
    ===================================================

    select N.ROW_ID, N.DEST_OBJID, N.NOTIFICATION_KEY, N.NTFY_CONTENT,
    N.SRB_FLGS, N.CREATED, N.BUS_SVC_MTHD, N.BUS_SVC_NAME,
    N.CODE_PAGE, N.LANG_ID
    from SIEBEL.S_SRM_NTFY N
    where NTFY_STATUS_CD = 'QUEUED'
    and (EXPIRATION_DT >= SYSDATE
    or EXPIRATION_DT is NULL)
    and (upper (ENTERPRISE_NAME) = upper (:V1)
    or ENTERPRISE_NAME IS NULL
    or ENTERPRISE_NAME is NULL) and N.DEST_NTFY_KEY in ( '0#0' , 'ANONEMP7#0' )
    hash=1ba51a67 timestamp=05-02-2005 16:11:01
    namespace=CRSR flags=RON/KGHP/TIM/PN0/LRG/[10010001]
    kkkk-dddd-llll=0000-0001-0001 lock=N pin=0 latch=2
    lwt=c0000002095de6b0[c0000002095de6b0,c0000002095de6b0] ltm=c0000002095de6c0[c0000002095de6c0,c0000002095de6c0]
    pwt=c0000002095de6e0[c0000002095de6e0,c0000002095de6e0] ptm=c0000002095de770[c0000002095de770,c0000002095de770]
    ref=c0000002095de690[c0000002095de690,c0000002095de690] lnd=c0000002095de788[c0000002095de788,c0000002095de788]
    LIBRARY OBJECT: object=c00000021163c058
    type=CRSR flags=EXS[0001] pflags= [00] status=VALD load=0
    CHILDREN: size=16
    child# table reference handle
    ------ -------- --------- --------
    0 c0000002143ef9b8 c0000002143ef788 c0000002104b7f18
    DATA BLOCKS:
    data# heap pointer status pins change
    ----- -------- -------- ------ ---- ------
    0 c00000021163c2b0 c00000021163c148 I/P/A 0 NONE
    Attached Files Attached Files

  2. #2
    Join Date
    Nov 2000
    Location
    greenwich.ct.us
    Posts
    9,092

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