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-000d002c-0001ee62        34      55     X             64      91           X
TX-00060009-0003b401        64      91     X             34      55           X
session 55: DID 0001-0022-000059F0      session 91: DID 0001-0040-00000046
session 91: DID 0001-0040-00000046      session 55: DID 0001-0022-000059F0
Rows waited on:
Session 91: obj - rowid = 0001E66D - AAAeZtAAGAAChtsAAX
  (dictionary objn - 124525, file - 6, block - 662380, slot - 23)
Session 55: obj - rowid = 0001E66D - AAAeZtAAGAAChtsAAM
  (dictionary objn - 124525, file - 6, block - 662380, slot - 12)
Information on the OTHER waiting sessions:
Session 91:
  pid=64 serial=31379 audsid=6773930 user: 66/XXXXXXXX
  O/S info: user: , term: , ospid: 1234, machine: XXXXX
            program:
  Current SQL Statement:
  insert into notification_instance_persons (notif_msg_instance_id, person_id, notification_status_id, id) values (:1, :2, :3, :4)
End of information on OTHER waiting sessions.
Current SQL statement for this session:
UPDATE NOTIF_EXCEPTION_COUNT SET NOTIF_COUNT = NOTIF_COUNT + 1 WHERE PERSON_ID = :B2 AND STATUS_ID = :B1
.
.
.
    service name: SYS$USERS
    O/S info: user: , term: , ospid: 1234, machine: XXXXXXX
              program:
    last wait for 'enq: TX - row lock contention' blocking sess=0x0x5feca304 seq=27697 wait_time=10 seconds since wait started=0
                name|mode=54580006, usn<<16 | slot=60009, sequence=3b401
    Dumping Session Wait History
     for 'enq: TX - row lock contention' count=1 wait_time=10
                name|mode=54580006, usn<<16 | slot=60009, sequence=3b401
     for 'buffer busy waits' count=1 wait_time=5
                file#=2, block#=10b6, class#=1c
     for 'buffer busy waits' count=1 wait_time=4
 ...

CREATE OR REPLACE
TRIGGER TRIGGER_NOTIF_EXCEPTION_COUNT AFTER INSERT OR UPDATE OR DELETE ON NOTIFICATION_INSTANCE_PERSONS 
FOR EACH ROW 
DECLARE
    listCount  INTEGER;
    listCheckCount  INTEGER;
BEGIN
    SELECT COUNT(*) INTO listCount FROM NOTIF_EXCEPTION_COUNT NEC 
    WHERE NEC.PERSON_ID = :old.PERSON_ID 
        AND NEC.STATUS_ID =  :old.NOTIFICATION_STATUS_ID;
    SELECT COUNT(*) INTO listCheckCount 
    FROM NOTIFICATION_MESSAGE_INSTANCE,
        NOTIFICATION_DETAIL_XREF ,
        NOTIFICATION  
    WHERE NOTIFICATION.SEVERITY='Exception'  
        AND :old.NOTIF_MSG_INSTANCE_ID=NOTIFICATION_MESSAGE_INSTANCE.ID 
        AND NOTIFICATION_MESSAGE_INSTANCE.NOTIFICATION_DETAIL_XREF_ID=NOTIFICATION_DETAIL_XREF.ID 
        AND NOTIFICATION_DETAIL_XREF.NOTIFICATION_ID=NOTIFICATION.ID;
    IF listCheckCount > 0 THEN
        IF listCount > 0 AND :old.ID IS NOT NULL THEN
            UPDATE NOTIF_EXCEPTION_COUNT SET NOTIF_COUNT = NOTIF_COUNT - 1 
            WHERE PERSON_ID = :old.PERSON_ID 
                AND STATUS_ID =  :old.NOTIFICATION_STATUS_ID;
        END IF;
    END IF;

    SELECT COUNT(*) INTO listCount FROM NOTIF_EXCEPTION_COUNT NEC 
    WHERE NEC.PERSON_ID = :new.PERSON_ID 
        AND NEC.STATUS_ID =  :new.NOTIFICATION_STATUS_ID;
    SELECT COUNT(*) INTO listCheckCount 
    FROM NOTIFICATION_MESSAGE_INSTANCE,
        NOTIFICATION_DETAIL_XREF ,
        NOTIFICATION  
    WHERE NOTIFICATION.SEVERITY='Exception'  
        AND :new.NOTIF_MSG_INSTANCE_ID=NOTIFICATION_MESSAGE_INSTANCE.ID 
        AND NOTIFICATION_MESSAGE_INSTANCE.NOTIFICATION_DETAIL_XREF_ID=NOTIFICATION_DETAIL_XREF.ID 
        AND NOTIFICATION_DETAIL_XREF.NOTIFICATION_ID=NOTIFICATION.ID;
    IF listCheckCount > 0 THEN
        IF listCount > 0 AND :new.ID IS NOT NULL THEN
            UPDATE NOTIF_EXCEPTION_COUNT SET NOTIF_COUNT = NOTIF_COUNT + 1 
            WHERE PERSON_ID = :new.PERSON_ID 
                AND STATUS_ID =  :new.NOTIFICATION_STATUS_ID;
        ELSE
            INSERT INTO NOTIF_EXCEPTION_COUNT VALUES (1,:new.PERSON_ID,:new.NOTIFICATION_STATUS_ID);
        END IF;
    END IF;
END TRIGGER_NOTIF_EXCEPTION_COUNT;
/

insert into notification_instance_persons...
and
UPDATE NOTIF_EXCEPTION_COUNT....