oracle更新死锁,Oracle中AFTER UPDATE触发器中的死锁方案

将有2个表 - 基表和历史表

更新基表中的任何记录时，在历史记录表中使用旧值插入相同记录(待更新)。

使用基表中的新值更新记录。

醇>

我正在使用以下触发器，给出死锁场景。请建议解决此问题。

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;