解决Oracle SQL中的变异表问题会产生死锁 [英] Solving the mutating table problem in Oracle SQL produces a deadlock

问题描述

嘿，我正在尝试在Oracle数据库中创建一个触发器，该触发器会更改除刚刚更改的记录以外的所有其他记录，并将触发器启动为0.因为我正在更新与该表相同的表中的记录启动了触发器，但出现了变异表错误.为了解决这个问题，我将代码作为匿名事务处理，但这会导致死锁.

Hey, I'm trying to create a trigger in my Oracle database that changes all other records except the one that has just been changed and launched the trigger to 0. Because I am updating records in the same table as the one that launched the trigger I got the mutating table error. To solve this, I put the code as an anonymous transaction, however this causes a deadlock.

触发代码:

CREATE OR REPLACE TRIGGER check_thumbnail AFTER INSERT OR UPDATE OF thumbnail ON photograph
FOR EACH ROW
BEGIN
  IF :new.thumbnail = 1 THEN
    check_thumbnail_set_others(:new.url);
  END IF;
END;

程序代码:

CREATE OR REPLACE PROCEDURE check_thumbnail_set_others(p_url IN VARCHAR2)
IS PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
    UPDATE photograph SET thumbnail = 0 WHERE url <> p_url;
    COMMIT;
END;

我认为我正在引起僵局，因为触发器在自身内部启动.有什么想法吗?

I assume I'm causing a deadlock because the trigger is launching itself within itself. Any ideas?

推荐答案

使用自主事务处理这种事情几乎肯定是一个错误.如果插入新缩略图的事务需要回滚怎么办?您已经将更改提交到表中的其他行.

Using an autonomous transaction for this sort of thing is almost certainly a mistake. What happens if the transaction that inserted the new thumbnail needs to rollback? You've already committed the change to the other rows in the table.

如果希望数据在事务上保持一致，则需要多个触发器和某种存储状态的方式.最简单的选择是创建一个包含thumbnail.url％type集合的包，然后在表上创建三个触发器. before语句触发器将清除集合.行级触发器会将:new.url值插入集合中.然后，一条after语句触发器将从集合中读取值并调用check_thumbnail_set_others过程(这不是自治事务).

If you want the data to be transactionally consistent, you would need multiple triggers and some way of storing state. The simplest option would be to create a package with a collection of thumbnail.url%type then create three triggers on the table. A before statement trigger would clear out the collection. A row-level trigger would insert the :new.url value into the collection. An after statement trigger would then read the values from the collection and call the check_thumbnail_set_others procedure (which would not be an autonomous transaction).

这篇关于解决Oracle SQL中的变异表问题会产生死锁的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！