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