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

Thread: Deadlock SItuation Frequently

  1. #1
    Join Date
    Oct 2006
    Location
    Mumbai
    Posts
    184

    Deadlock SItuation Frequently

    Dear All,

    Am getting the below error quite frequently, below is the excerpts of the trace file generated.

    Can anybody come up with some suggesstion on avoiding such deadlock situation.

    *** 2007-02-27 11:39:49.010
    *** SESSION ID:(148.160) 2007-02-27 11:39:49.010
    DEADLOCK DETECTED
    Current SQL statement for this session:
    UPDATE TR_VCH_dtl SET AM_ACC_CD=:1,CT_CD=:2,SB_SUB_CD=:3,CC_CD=:4,VD_CR_DR=:5,ST_CD=:6,VD_TAX_AMT=:7,VD_RECOVERABLE=:8,VD_ NARRATION=:9,CM_COMP_CD=:10,BM_BR_CD=:11,SM_SBU_CD=:12,VH_TYPE=:13,VH_NO=:14,VH_DT=:15,VD_SRL_NO=:16 ,VD_NET_AMT=:17,VD_BILL_NO=:18,VD_BILL_DT=:19,VD_BILL_TYPE=:20,CREATED_BY=:21,CREATED_DT=:22,MODIFIE D_BY=:23,MODIFIED_DT=:24,vd_status=:25 WHERE ROWID=:26
    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-00011c71-00000000 29 148 SX SSX 46 30 SX SSX
    TM-00011c71-00000000 46 30 SX SSX 29 148 SX SSX
    session 148: DID 0001-001D-0000005E session 30: DID 0001-002E-00000011
    session 30: DID 0001-002E-00000011 session 148: DID 0001-001D-0000005E
    Rows waited on:
    Session 30: obj - rowid = 00014033 - AAAanWAANAAAAAAAAA
    (dictionary objn - 81971, file - 13, block - 0, slot - 0)
    Session 148: obj - rowid = 00011BE8 - AAARvoAAMAAAAAAAAA
    (dictionary objn - 72680, file - 12, block - 0, slot - 0)
    Information on the OTHER waiting sessions:
    Session 30:
    pid=46 serial=356 audsid=210947 user: 170/CASNEW
    O/S info: user: nitin, term: VAISHALI, ospid: 704:1828, machine: ILFS-BKC\VAISHALI
    program:
    Current SQL Statement:

    UPDATE TR_VCH_dtl SET AM_ACC_CD=:1,CT_CD=:2,SB_SUB_CD=:3,CC_CD=:4,VD_CR_DR=:5,ST_CD=:6,VD_TAX_AMT=:7,VD_RECOVERABLE=:8,VD_ NARRATION=:9,CM_COMP_CD=:10,BM_BR_CD=:11,SM_SBU_CD=:12,VH_TYPE=:13,VH_NO=:14,VH_DT=:15,VD_SRL_NO=:16 ,VD_NET_AMT=:17,VD_BILL_NO=:18,VD_BILL_DT=:19,VD_BILL_TYPE=:20,CREATED_BY=:21,CREATED_DT=:22,MODIFIE D_BY=:23,MODIFIED_DT=:24,vd_status=:25 WHERE ROWID=:26
    End of information on OTHER waiting sessions.
    ===================================================
    PROCESS STATE
    -------------
    Process global information:
    process: 3af463a98, call: 3af5a1dc8, xact: 3b0600170, curses: 3af521fd0, usrses: 3af521fd0
    ----------------------------------------
    SO: 3af463a98, type: 2, owner: 0, flag: INIT/-/-/0x00
    (process) Oracle pid=29, calls cur/top: 3af5a1dc8/3af5a1dc8, flag: (0) -
    int error: 0, call error: 0, sess error: 0, txn error 0
    (post info) last post received: 0 0 21
    last post received-location: ksqrcl
    last process to post me: 3af473ff8 4 0
    last post sent: 0 0 20
    last post sent-location: ksqcnl
    last process posted by me: 3af46de98 7 0
    (latch info) wait_event=0 bits=10
    holding 38000a1c0 Parent+children enqueue hash chains level=4
    Location from where latch is held: ksqcmi: kslgpl:
    Context saved from call: 0
    state=busy
    recovery area:

  2. #2
    Join Date
    Sep 2002
    Location
    England
    Posts
    7,334
    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:
    you have an application problem

  3. #3
    Join Date
    May 2000
    Location
    ATLANTA, GA, USA
    Posts
    3,135

  4. #4
    Join Date
    Jan 2001
    Posts
    642
    Also if you are using the bitmap indexes on a column and if you are frequently updating that column, that could lead to deadlocks. The bitmap indexes are not generally good when concurrent DMLs are going on.
    We had such a problem with one of our application, and we identified it to bitmap indexes.
    There is always a better way to do the things.

  5. #5
    Deadlocks happen when multiple application sessions trip over themselves. A DBA can't fix this other than limiting the application to just one session.

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