Hi,
We are using 11.2.0.3 and have a trigger which acts upon a mutex table.
Trying to catch when value from other program has changed locked value from 0 to 1 to indicate they have finished.
Once they finished we need to lock the mutex table by updating locked value.
Code below gives error ORA-00060 - deadlock.
Any idea how to avoid but allow us to updat ethe mutex table in trigger at star t to say we are locking and at end to indicate we are finished.
Thanks
CREATE OR REPLACE TRIGGER example_trg AFTER UPDATE
ON por_zic_flags
FOR EACH ROW
when (NEW.locked = '0' and old.locked = '1')
declare
PRAGMA AUTONOMOUS_TRANSACTION;
I INTEGER;
v_locked por_zic_flags.locked%type;
begin
select locked
into v_locked
from por_zic_flags
--where tabname = ‘table name’
for update;
update por_zic_flags set locked = '2';--, guid = '100'; -- unique seq#
commit;
-- call procedure do processing on other tables
-- Now indicate finished processing
select locked
into v_locked
from por_zic_flags
for update;
update por_zic_flags set locked = '0';--, guid = '100'; -- unique seq#
commit;
end;