Deadlock scenario in AFTER UPDATE trigger in Oracle

Question

As part of audit/history functionality I want to handle following scenario using AFTER UPDATE trigger or any other trigger, please let me know. Scenario --

There will 2 tables -- Base table and history table
On update of any record in Base table 1st insert same record (to be updated) in History table with old values.
Update record with new values in Base table.

I am using following trigger, giving deadlock scenario. Please advice to resolve this issue.

create table Base_table(
SYMBOL_ID            NUMBER(9)  primary key,  
SYMBOL_NAME         VARCHAR2(20) ,
PRICE           NUMBER(9) ,   
VERSION              NUMBER(1)
)
organization index;

create table base_table_hist(
ID               NUMBER(9) primary key,
SYMBOL_ID            NUMBER(9) ,   
SYMBOL_NAME          VARCHAR2(20) ,
PRICE             NUMBER(9),    
VERSION              NUMBER(1) ,
constraint other_symbolid  foreign key(symbol_id) references test_symbol(symbol_id)
)
organization index;
************************************************************

create or replace Trigger Symbol_Ver
AFTER UPDATE ON Base_table
REFERENCING NEW AS New OLD AS Old
FOR EACH ROW

DECLARE
new_version number(5);
--Pragma AUTONOMOUS_TRANSACTION;
Sid number(9);
begin
  if (:New.symbol_id <> :Old.symbol_id)    OR (:New.price <> :Old.price)  then


    new_version:= :Old.version+1;

    --insert into history table
    insert into base_table_hist (id, symbol_id, symbol_name,price,version) 
      values (symbol_seq.nextval, :OLD.symbol_id, :OLD.symbol_name, :OLD.price, :OLD.version);
  commit;
  DBMS_OUTPUT.put_line('new_version..'||new_version);
end if;

if (:New.symbol_id <> :Old.symbol_id)    OR (:New.price <> :Old.price)  then
    update base_table set version=new_version where symbol_id=:Old.symbol_id;
end if;

end;

Really a deadlock? Not a mutuating table error, or running out of resources (since the trigger will fire repeatedly from the recursove update within it)? — Alex Poole
Its a deadlock error ( ORA-00060) displayed. To avoid repeatedly fire condition changed as (if :New.version = Old.Version) then INSERT and UPDATE operation. — SandeepS
But you're still firing an extra update, which is ugly and I'm surprised works. Why aren't you adjusting the version (setting :new.version), and creating the history record, in a before trigger rather than doing extra DML? — Alex Poole

Vincent Malgrat Vincent Malgrat · Accepted Answer · 2014-03-19T10:46:43

An autonomous transaction creates a new, independent transaction. So you're updating the same row with two transactions, leading to a deadlock.

You don't need an autonomous trigger here. In fact you don't want to touch the base table with DML in a trigger. That's always problematic.

Fortunately, here you can use a regular BEFORE trigger (since you're updating a field):

CREATE OR REPLACE TRIGGER Symbol_Ver
   BEFORE UPDATE ON Base_table
   FOR EACH ROW
BEGIN
   IF (:New.symbol_id <> :Old.symbol_id) OR (:New.price <> :Old.price) THEN

      -- this will change the value in the row being updated
      :new.version := :Old.version + 1;

      --insert into history table
      INSERT INTO base_table_hist
         (id, symbol_id, symbol_name, price, version)
      VALUES
         (symbol_seq.nextval, :OLD.symbol_id, 
          :OLD.symbol_name, :OLD.price, :OLD.version);
      -- COMMIT <-- don't commit in a trigger!
      DBMS_OUTPUT.put_line('new_version..' || new_version);
   END IF;
END;

An additional update on the base table would be both redundant and problematic since this would lead to a circular infinite recursion.

Also you can't commit in a trigger. You don't want to commit anyway, this breaks the transaction logic. Not committing allows the main transaction to roll back both the history table and the main table in one nice atomic chunk.

Deadlock scenario in AFTER UPDATE trigger in Oracle

1 Answers