Skip to Main Content

SQL & PL/SQL

Announcement

For appeals, questions and feedback about Oracle Forums, please email oracle-forums-moderators_us@oracle.com. Technical questions should be asked in the appropriate category. Thank you!

ORA-00060: deadlock detected while waiting for resource - DB trigger while insert-update

User_3ZQRHJun 10 2020 — edited Jun 12 2020

I am working on Oracle 12c on IBM AIX linux machine.

I have a requirement that if there is a update/insert in a table (base table) , say col1 and col2 then all the old historical records should get updated with the new values of col1 and col2. [ in addition of auditing functionality ]

So I create a "after update each row" trigger on base table and inside the trigger I have written

If (new.col1 <>  old.col1) OR (new.col2 <> old.col2) then

procedure A

end if;

inside procedure A

pragma autonomous transaction

begin

update base table set

col1 -new value,

col2 =new value

where date between "1st day of the month" between "current date";

This is leading to ORA-00060: deadlock detected while waiting for resource as it is leading to circular recursive transaction because each of the the update of the historical record on the table itself is again triggering the DB trigger leading to deadlock/lack of resources.

Any idea how people go ahead with solving this issue ?

Thanks and regard

Comments
Post Details
Added on Jun 10 2020
13 comments
11,621 views