Hi,
Below is a deadlock graph from the trace file generated.
Kindly help me to derive exact cause and recommended changes in
database to reduce the probability of these happening.
- The tablespaces are locally managed migrated from dictionary managed
tablespaces (segment space management : USER)
- The database access is through application servers with connection
pooling.
- Primary key of table SK_TB_ANS is (NM_CID, SK_SV_ID, USR_ID, SK_QNO)
- There is one more index on this table on (NM_CID, SK_SV_ID, USR_ID)
- There is no foreign key on SK_TB_ANS
- File 16 : Undo tablespace
- File 14 : Tablespace of table SK_TB_ANS
- Object# 42293 : table SK_TB_ANS
- This has happened during a time when large number of users were
accessing the application
- Removing deletes is a major change in application and can be
considered only if this situation can not be reduced by any
improvements at database level.
TIA,
Gautrak
Oracle9i Enterprise Edition Release 9.2.0.5.0 - 64bit Production
With the Partitioning, OLAP and Oracle Data Mining options
JServer Release 9.2.0.5.0 - Production
ORACLE_HOME = /sklc/oracle/product/9.2
System name: SunOS
Node name: skdb
Release: 5.9
Version: Generic_117171-02
Machine: sun4u
Instance name: sklc
Redo thread mounted by this instance: 1
Oracle process number: 33
Unix process pid: 13651, image: oracle_at_skdb (TNS V1-V3)
- 2004-10-15 06:09:27.286
- SESSION ID:(63.29) 2004-10-15 06:09:27.285
DEADLOCK DETECTED
Current SQL statement for this session:
DELETE FROM SK_TB_ANS WHERE NM_CID = :1 AND SK_SV_ID = :2 AND USR_ID
= :3 AND SK_QNO = :4
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-008f000c-00000002 33 63 X 49 55
S
TX-00110012-000030e5 49 55 X 34 35
X
TX-008f000c-00000002 34 35 X 33 63
X
session 63: DID 0001-0021-0000000A session 55: DID
0001-0031-00000008
session 55: DID 0001-0031-00000008 session 35: DID
0001-0022-00000009
session 35: DID 0001-0022-00000009 session 63: DID
0001-0021-0000000A
Rows waited on:
Session 55: obj - rowid = 00000000 - D/////AAQAAAFJNAAA
(dictionary objn - 0, file - 16, block - 21069, slot - 0)
Session 35: obj - rowid = 0000A535 - AAAKU1AAOAAB2LiAAV
(dictionary objn - 42293, file - 14, block - 484066, slot - 21)
Session 63: obj - rowid = 0000A535 - AAAKU1AAOAAB2LiABD
(dictionary objn - 42293, file - 14, block - 484066, slot - 67)
Information on the OTHER waiting sessions:
Session 55:
pid=49 serial=29 audsid=1105150 user: 78/sklc_user
O/S info: user: admuser, term: , ospid: 9715, machine: skappser
program: .kjs.memopt_at_skappser (TNS V1-V3)
application name: .kjs.memopt_at_skappser (TNS V1-V3), hash value=0
Current SQL Statement:
INSERT INTO SK_TB_ANS (USR_ID , SK_ANS_TXT , SK_QNO ,SK_SV_ID,
NM_CID,VC_CRTD_BY, DT_CRTD_DT, VC_MNTN_BY, DT_MNTN_DT) VALUES ( :1 ,
:2 , :3 , :4 , :5 ,:6,SYSDATE,:7,SYSDATE)
Session 35:
pid=34 serial=29 audsid=1105135 user: 78/sklc_user
O/S info: user: admuser, term: , ospid: 9715, machine: skappser
program: .kjs.memopt_at_skappser (TNS V1-V3)
application name: .kjs.memopt_at_skappser (TNS V1-V3), hash value=0
Current SQL Statement:
UPDATE SK_TB_ANS SET SK_ANS_TXT = :1 , VC_MNTN_BY = :2 ,
DT_MNTN_DT = SYSDATE WHERE NM_CID = :3 AND SK_SV_ID = :4 AND USR_ID =
:5 AND SK_QNO = :6
End of information on OTHER waiting sessions.
Received on Mon Oct 18 2004 - 21:45:05 CDT
