Home » SQL & PL/SQL » SQL & PL/SQL » Dead Lock In Oracle (Oracle 10g)
Dead Lock In Oracle [message #356887] Mon, 03 November 2008 00:22
raja_014
Messages: 1
Registered: November 2008
Location: chennai
Junior Member
Hi ..

Can any one help me on this deadlock in oracle...

I am not able to understand why its occuring...

*** 2008-10-24 06:53:30.353
*** ACTION NAME:(Initialized) 2008-10-24 06:53:30.353
*** MODULE NAME:(RTVQ_KOMAROM) 2008-10-24 06:53:30.353
*** SERVICE NAME:(SYS$USERS) 2008-10-24 06:53:30.353
*** SESSION ID:(802.18) 2008-10-24 06:53:30.353
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
TX-000b0021-00019dc5 50 802 X 49 830 X
TX-0001001d-000023fa 49 830 X 50 802 S
session 802: DID 0001-0032-00000015 session 830: DID 0001-0031-00000015
session 830: DID 0001-0031-00000015 session 802: DID 0001-0032-00000015
Rows waited on:
Session 830: obj - rowid = 00009AEB - AAAJrrAAFAAAEsjAC8
(dictionary objn - 39659, file - 5, block - 19235, slot - 188)
Session 802: no row
Information on the OTHER waiting sessions:
Session 830:
pid=49 serial=18 audsid=70397 user: 40/<none>
O/S info: user: rtvqws, term: , ospid: 1234, machine: saertvqp11
program:
application name: RTVQ_KOMAROM, hash value=1581048483
action name: Initialized, hash value=90476713
Current SQL Statement:
UPDATE RTVQ_TR_PERSON_LOGIN SET LOGON_TIME = :B5 , LOGOFF_TIME = :B4 , LOGON_STATUS = :B3 , WORKPLACE_ID = :B2 WHERE UPPER(PERSON_ID) = UPPER(:B1 )
End of information on OTHER waiting sessions.
Current SQL statement for this session:
INSERT INTO RTVQ_HST_PERSON_LOGIN(ROW_ID,PERSON_ID,WORKPLACE_NAME,TIMESTAMP,EVENT_TYPE) VALUES((SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_HST_PERSON_LOGIN),:B1 ,(SELECT WORKPLACE_NAME FROM RTVQ_MA_WORKPLACE WHERE WORKPLACE_ID = :B4 ),:B2 ,:B3 )
----- PL/SQL Call Stack -----
object line object
handle number name
3b4e322c0 6 RTVQ_KOMAROM_OWNER.RTVQ_TBL_TG_PRLOGIN_AIU
3b2470928 160 package body RTVQ_KOMAROM_OWNER.RTVQ_PA_INTERNAL_INTERFACE
3ab322478 1 anonymous block

Here I have attached the PL/SQL procedure where the dead lock is occuring..

Thanks&Regards
Rajasekharreddy.G




RTVQ_TBL_TG_PRLOGIN_AIU Trigger I have pasted here

-- TRIGGER ON PERSON LOGIN TABLE
CREATE OR REPLACE TRIGGER RTVQ_TBL_TG_PRLOGIN_AIU
AFTER INSERT OR UPDATE ON RTVQ_TR_PERSON_LOGIN
FOR EACH ROW
DECLARE
BEGIN

IF :NEW.LOGON_STATUS = 'IN' THEN
BEGIN
INSERT INTO RTVQ_HST_PERSON_LOGIN(ROW_ID,PERSON_ID,WORKPLACE_NAME,TIMESTAMP,EVENT_TYPE)
VALUES((SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_HST_PERSON_LOGIN),:NEW.PERSON_ID,(SELECT WORKPLACE_NAME FROM RTVQ_MA_WORKPLACE WHERE WORKPLACE_ID = :NEW.WORKPLACE_ID),:NEW.LOGON_TIME,:NEW.LOGON_STATUS);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;

ELSE

BEGIN
INSERT INTO RTVQ_HST_PERSON_LOGIN(ROW_ID,PERSON_ID,WORKPLACE_NAME,TIMESTAMP,EVENT_TYPE)
VALUES((SELECT NVL(MAX(ROW_ID),0)+1 FROM RTVQ_HST_PERSON_LOGIN),:NEW.PERSON_ID,(SELECT WORKPLACE_NAME FROM RTVQ_MA_WORKPLACE WHERE WORKPLACE_ID = :NEW.WORKPLACE_ID),:NEW.LOGOFF_TIME,:NEW.LOGON_STATUS);
EXCEPTION
WHEN OTHERS THEN
NULL;
END;
END IF;

END RTVQ_TBL_TG_PRLOGIN_AIU;

/

Previous Topic: Variable
Next Topic: show master details ..
Goto Forum:
  


Current Time: Thu Dec 08 06:19:43 CST 2016

Total time taken to generate the page: 0.06713 seconds