Home » RDBMS Server » Performance Tuning » Deadlock interpretation (Oracle 10g)
| Deadlock interpretation [message #436376] |
Wed, 23 December 2009 03:34  |
vipull.sayal
Messages: 4
Registered: February 2008
|
Junior Member |
|
|
Hi,
Can you pls help me to analyze a deadlock situation.
I have following two tables on which deadlock is occuring-
A parent table -
CREATE TABLE REFERENTIE_EXPRESSIE
(
PROGNOSEKANAAL_CODE VARCHAR2(5 BYTE),
REFERENTIE_EXPRESSIE_CODE NUMBER(5),
EXPRESSIE_TYPE VARCHAR2(10 BYTE),
TECHNISCH_VERKOOPSARTIKEL_NR NUMBER(9),
INVLOED_PRECENTAGE NUMBER(5,4),
OPERAND_CODE NUMBER(3),
REFERENTIE_EXPRESSIE_TERM1 NUMBER(5),
REFERENTIE_EXPRESSIE_TERM2 NUMBER(5)
);
Primary key on the parent table-
ALTER TABLE REFERENTIE_EXPRESSIE ADD (
CONSTRAINT REX_PK
PRIMARY KEY
(PROGNOSEKANAAL_CODE, REFERENTIE_EXPRESSIE_CODE));
A child table-
CREATE TABLE FUNCTIE_DATUM_TERMIJN_U_EXPRE
(
PROGNOSEKANAAL_CODE VARCHAR2(5 BYTE),
FUNCTIE_TYPE_CODE NUMBER(5),
PROGNOSE_ELEMENT_CODE NUMBER(9),
INVOER_DATUM DATE,
REFERENTIE_EXPRESSIE_CODE NUMBER(5),
LAST_UPDATED_DATE DATE,
AUTO_CALCULATED VARCHAR2(1 BYTE)
);
Primary key on the child table-
ALTER TABLE FUNCTIE_DATUM_TERMIJN_U_EXPRE ADD (
CONSTRAINT DTE_PK
PRIMARY KEY
(PROGNOSEKANAAL_CODE, FUNCTIE_TYPE_CODE, PROGNOSE_ELEMENT_CODE, INVOER_DATUM));
Foreign key from the parent table-
ALTER TABLE FUNCTIE_DATUM_TERMIJN_U_EXPRE ADD (
CONSTRAINT DTE_REX_FK
FOREIGN KEY (PROGNOSEKANAAL_CODE, REFERENTIE_EXPRESSIE_CODE)
REFERENCES REFERENTIE_EXPRESSIE (PROGNOSEKANAAL_CODE,REFERENTIE_EXPRESSIE_CODE));
Index on the foreign key-
CREATE INDEX I_DTE_PROGN_REFER_CODE ON FUNCTIE_DATUM_TERMIJN_U_EXPRE
(PROGNOSEKANAAL_CODE, REFERENTIE_EXPRESSIE_CODE);
There are some more foreign keys on child table (FUNCTIE_DATUM_TERMIJN_U_EXPRE) from other tables but I think they are not important. In deadlock trace file it's mentioned only about these two tables.
My first thought was that problem could be because of unindexed foreign keys as advised by Michel in many of the forums, but here my foreign keys are also indexed and still the deadlock is occuring.
Can you pls help me to analyze the deadlock graph and how I can solve the deadlock.
DEADLOCK DETECTED ( ORA-00060 )
[Transaction Deadlock]
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-0000b3b4-00000000 191 508 SX 187 439 SS SSX
TX-00090026-000d336c 187 439 X 191 508 S
session 508: DID 0001-00BF-00000AAA session 439: DID 0001-00BB-00000C27
session 439: DID 0001-00BB-00000C27 session 508: DID 0001-00BF-00000AAA
Rows waited on:
Session 439: no row
Session 508: obj - rowid = 0000B358 - AAALNYAA1AAAVdwAAA
(dictionary objn - 45912, file - 53, block - 87920, slot - 0)
Information on the OTHER waiting sessions:
Session 439:
pid=187 serial=7471 audsid=1112381186 user: 156/PROGVERKPROC
O/S info: user: was, term: unknown, ospid: , machine: svlipc36
program: JDBC Thin Client
application name: JDBC Thin Client, hash value=2546894660
Current SQL Statement:
DELETE FROM REFERENTIE_EXPRESSIE WHERE PROGNOSEKANAAL_CODE = :B2 AND REFERENTIE_EXPRESSIE_CODE = :B1
End of information on OTHER waiting sessions.
Current SQL statement for this session:
INSERT INTO FUNCTIE_DATUM_TERMIJN_U_EXPRE (PROGNOSEKANAAL_CODE, FUNCTIE_TYPE_CODE, PROGNOSE_ELEMENT_CODE, INVOER_DATUM, REFERENTIE_EXPRESSIE_CODE, LAST_UPDATED_DATE, AUTO_CALCULATED) VALUES (:B5 , :B4 , :B3 , :B2 , :B1 , SYSDATE, 'N')
Regards,
Vipull
[Updated on: Wed, 23 December 2009 03:38] Report message to a moderator |
|
|
|
|
|
|
|
Goto Forum:
Current Time: Mon May 13 14:28:32 CDT 2024
|
] 
Blog
Search
Help
Members
Register
Login
Home
