-
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:
-
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
-
-
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.
-
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
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|