解决Oracle SQL中的变异表问题会产生死锁
嘿，我正在尝试在 Oracle 数据库中创建一个触发器，该触发器会更改除刚刚更改的记录之外的所有其他记录，并将触发器启动为 0。因为我正在更新与启动触发器的表相同的表中的记录我收到了变异表错误。为了解决这个问题，我将代码作为匿名事务，但这会导致死锁。
触发器代码：
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;
我认为我造成了死锁，因为触发器正在自身内部启动。有什么想法吗？
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.
Trigger code:
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;
Procedure code:
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?
如果你对这篇内容有疑问，欢迎到本站社区发帖提问 参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱，如果其他用户或者作者回复了您的评论，将不能在第一时间通知您！
发布评论
评论（1）
使用自主交易来处理这类事情几乎肯定是一个错误。如果插入新缩略图的事务需要回滚，会发生什么情况？您已将更改提交到表中的其他行。
如果您希望数据在事务上保持一致，则需要多个触发器和某种存储状态的方法。最简单的选择是创建一个包含thumbnail.url%type 集合的包，然后在表上创建三个触发器。 before 语句触发器将清除集合。行级触发器会将 :new.url 值插入到集合中。然后，after 语句触发器将从集合中读取值并调用 check_thumbnail_set_others 过程（这不是一个自治事务）。
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.
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_othersprocedure (which would not be an autonomous transaction).