Home » RDBMS Server » Performance Tuning » ORA-00060 Deadlock issue with trigger (10.2.0.4.0, Linux)
ORA-00060 Deadlock issue with trigger [message #421608] Wed, 09 September 2009 07:08 Go to next message
KaushalPanjwani
Messages: 4
Registered: September 2009
Junior Member
Hello Everybody,

We are facing a deadlock issue with a trigger,here is the snippet from the trace file:

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
 ...



The trigger we have is :

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;
/



What my confusion is that the deadlock is happening on two different tables:

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

how come then we are having competing resources. Though I see that insert in notification_instance_persons would invoke a trigger to update NOTIF_EXCEPTION_COUNT but still why would a lock in row of NOTIF_EXCEPTION_COUNT affects insertion in notification_instance_persons.

Secondly, even the waiting rows(rowid mentioned in trace file) corresponds to two different rows.

I also read about ITL shortage being one of the reasons for the ORA-00060 issue however I ve verified that MAXTRANS is 255 for the two tables in question.

Please let me know how do I move forward in fixing the cause of the issue.
Autonomous Triggers comes to my mind (so that update can release lock immediately?) , but i ve read more against the Autonomous triggers than in favor of it.

Also NOTIF_EXCEPTION_COUNT does not have any index/constraints on it. One of the column is not null. Though there are few Indexes and Foreign keys on NOTIFICATION_INSTANCE_PERSONS. I can provide the details if that helps.

Thanks in advance!
Kaushal
Re: ORA-00060 Deadlock issue with trigger [message #421614 is a reply to message #421608] Wed, 09 September 2009 08:04 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Deadlocks usually involve two tables, it's rare for them to involve only one.

Basic logic of a deadlock is:

1) Session 1 locks row a in table c.
2) Session 2 locks row b in table d (which could be the same as table c but probably not).
3) Session 1 tries to lock row b in table d - gets blocked.
4) Session 2 tries to lock row a in table c - deadlock occurs.

I'm guessing the trace file might be slightly misleading here. One of your locks is definitely the update of NOTIF_EXCEPTION_COUNT (presumably from the trigger). You need to find the other lock.
The insert statement in the trace file probably indicates it's trying to run the trigger but can't because of the lock.
You need to look at the code that does the insert into notification_instance_persons to see if it locks anything else.

Also check if there are any triggers on NOTIF_EXCEPTION_COUNT.

Whatever you do don't try and use an autonomous transaction - that'll just swap one problem for another.

A quick note on your trigger - when checking for the existence of a row it's a lot more efficient to do SELECT 1 WHERE rownum =1 than COUNT(*)
Re: ORA-00060 Deadlock issue with trigger [message #421615 is a reply to message #421608] Wed, 09 September 2009 08:12 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
Actually - just remove the select on NOTIF_EXCEPTION_COUNT completely. Just do the update and check sql%rowcount - if it's 0 do the insert.
Re: ORA-00060 Deadlock issue with trigger [message #421617 is a reply to message #421608] Wed, 09 September 2009 08:40 Go to previous messageGo to next message
KaushalPanjwani
Messages: 4
Registered: September 2009
Junior Member
Thanks cookiemonster for your inputs.

I ll incorporate changes mentioned by you to improve trigger. However, I think that would not solve our deadlock issue.

Regarding inserts : We are doing inserts in notification_instance_persons using ORM tool hibernate.
I noticed that we have hibernate session which does multiple inserts for notification_instance_persons and commit it later.

Is this the issue that trigger would take locks on all the rows of NOTIF_EXCEPTION_COUNT that corresponds to multiple inserts above, another simultaneous request of multiple inserts in notification_instance_persons could then lead to deadlock?

Besides, I would like to add that we did not this issue until few days back when there was a scenario which lead to very large number of inserts for notification_instance_persons.

Re: ORA-00060 Deadlock issue with trigger [message #421620 is a reply to message #421608] Wed, 09 September 2009 08:50 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
No the suggestions I made for the triggers won't solve the deadlock.

If you've got two different sessions inserting multiple rows into NOTIFICATION_INSTANCE_PERSONS which'll update an overlapping set of NOTIF_EXCEPTION_COUNT records that might do it.

Session one updates row a in NOTIF_EXCEPTION_COUNT.
Session two updates row b in NOTIF_EXCEPTION_COUNT.
Session one tries to update row b in NOTIF_EXCEPTION_COUNT.
Session two tries to update row a in NOTIF_EXCEPTION_COUNT.

Does the NOTIF_EXCEPTION_COUNT table hold anything other than calculated values? If not the best thing to do would probably be to get rid of the table and just calculate the values when you need then - use a view if you like.

Otherwise you're going to have to make sure that only one session can do multiple inserts into this table at a time.

Re: ORA-00060 Deadlock issue with trigger [message #421623 is a reply to message #421608] Wed, 09 September 2009 09:47 Go to previous messageGo to next message
KaushalPanjwani
Messages: 4
Registered: September 2009
Junior Member
Getting rid of NOTIF_EXCEPTION_COUNT is not an option since this table was added to improve performance, it stores count of Notifications per person and help us while displaying the Paging quickly without need to fire count(*) query every time.

But I believe the scenario you mentioned below is the one causing deadlock to us.Isn't Autonomous trigger solving out problem here?

Session one updates row a in NOTIF_EXCEPTION_COUNT.
- a commit here would release lock on row a.
Re: ORA-00060 Deadlock issue with trigger [message #421630 is a reply to message #421608] Wed, 09 September 2009 10:06 Go to previous messageGo to next message
cookiemonster
Messages: 12422
Registered: September 2008
Location: Rainy Manchester
Senior Member
If you add an autonomous transaction the most likely result is you'll end up with inaccurate counts, because if you rollback (manually or due to error) the changes that fired the trigger it won't rollback the changes to NOTIF_EXCEPTION_COUNT. There might be other nasty side effects as well.

I'm guessing this is a summary table and if so I strongly recommend you get rid of it. They're a great source of deadlocks - as you've found - and they've got a tendency to be inaccurate - if someone adds some code that doesn't update them properly for example.

If the query to get the totals is too slow then you should really concentrate on speeding it up. Might be all you need is an index and that query will become lightning fast.

If you really can't speed the query up enough then I suggest you look at using a materialised view instead.
icon14.gif  Re: ORA-00060 Deadlock issue with trigger [message #421793 is a reply to message #421608] Thu, 10 September 2009 10:54 Go to previous messageGo to next message
KaushalPanjwani
Messages: 4
Registered: September 2009
Junior Member
Thanks cookiemonster for sharing all the valuable knowledge.

I resolved the issue by synchronizing the code that does the inserts, removing the table would have been a very big change
To minimize the impact of fix we decided on synchronizing approach, ready to take a bit of overhead.
Re: ORA-00060 Deadlock issue with trigger [message #431351 is a reply to message #421608] Tue, 17 November 2009 00:14 Go to previous messageGo to next message
orafaqu1
Messages: 1
Registered: November 2009
Junior Member
Hi,

I have a table called A and whenever there is a insert or update on table A , trigger inserts a record into table B. Table is having a bitmap index on a column called line_Status. When there is a insert ore update the line_Status would be changed to 'R'. We have a procedure proc1 which process all 'R' records and updates the line_status to 'P'. And we have a another procedure proc2 which deletes all 'P' records from table B.

When proc2 is trying delete the 'P' records from table B , at the same time there is insert/update transaction is happening and the proc2 is coming out saying that "ORA-00060: deadlock detected while waiting for resource". Because of this issue we are missing the updates in table 'B' from the trigger.

Could you please help me to resolve/ how can i avoid this issue.

Thanks indvance
Re: ORA-00060 Deadlock issue with trigger [message #431478 is a reply to message #431351] Tue, 17 November 2009 09:52 Go to previous message
BlackSwan
Messages: 25050
Registered: January 2009
Location: SoCal
Senior Member
>Could you please help me to resolve/ how can i avoid this issue
Change to non-bitmapped index.

Bitmapped index should not be used when frequent DML occurs.
Previous Topic: Profile Limits
Next Topic: query delay
Goto Forum:
  


Current Time: Sun Dec 11 08:10:04 CST 2016

Total time taken to generate the page: 0.13843 seconds