Question

删除数据后，再对表进行操作，出现了死锁。。

create or replace trigger change_total
after insert or delete on stock
for each row
declare
v_counter int;
v_numbers number(8);
v_disrupt number(3);
v_name total.name%type;
cursor name_cur
IS
select goods_name from stock
where basket_id=:old.basket_id;
PRAGMA AUTONOMOUS_TRANSACTION;
begin
case
when inserting then
select count(*) into v_counter from stock
where goods_name=:new.goods_name;
if v_counter <>0 then
select sum(numbers) into v_numbers from stock
where goods_name=:new.goods_name;
v_numbers:= v_numbers + :new.numbers;
update total set sum_total=v_numbers
where name=:new.goods_name;
select disrupt into v_disrupt from total
where name=:new.goods_name;
update total set disrupt=v_disrupt+1
where name=:new.goods_name;
if v_numbers>=15 and v_numbers<=50 then
update total set warn='正常'
where name=:new.goods_name;
end if;
if v_numbers<15 then
update total set warn='库存不足'
where name=:new.goods_name;
end if;
if v_numbers>50 then
update total set warn='库存过剩'
where name=:new.goods_name;
end if;
else
if :new.numbers>=15 and :new.numbers<=50 then
insert into total values(:new.goods_name,:new.numbers,'正常',1);
end if;
if :new.numbers<15 then
insert into total values(:new.goods_name,:new.numbers,'库存不足',1);
end if;
if :new.numbers>50 then
insert into total values(:new.goods_name,:new.numbers,'库存过剩',1);
end if;
end if;

when deleting then
delete from stock where basket_id=:old.basket_id;
open name_cur;
loop
fetch name_cur into v_name;
exit when name_cur%notfound;
select sum(numbers) into v_numbers from stock
where goods_name=v_name;

if v_numbers=0 then
delete from total where name=v_name;
else
update total set sum_total=v_numbers
where name=v_name;
select disrupt into v_disrupt from total
where name=v_name;
update total set disrupt=v_disrupt-1
where name=v_name;
if v_numbers>=15 and v_numbers<=50 then
update total set warn='正常'
where name=v_name;
end if;
if v_numbers<15 then
update total set warn='库存不足'
where name=v_name;
end if;
if v_numbers>50 then
update total set warn='库存过剩'
where name=v_name;
end if;
end if;
end loop;
close name_cur;
end case;
commit;
end change_total;
就在 when deleting then
delete from stock where basket_id=:old.basket_id;
这里资源被咬住了好像。。在这报死锁了。。

Answer 1

呵呵，锁的问题确实是让人头疼，我也是经常遇到，因为平时总涉及比较复杂的业务，所以经常对某一个表不停的操作，经常出现这倒霉的死锁。
试试把DELETE删除操作放后边，中间处理的时候，滤掉这个要删除的数据吧
呵呵，先无耻的回答一个。