时间戳

首页 > 解决方案 > 触发器不会释放锁

oracle - 触发器不会释放锁

问题描述

我正在开发一个将替换现有旧项目的项目。一方面，我从外部视图中获取数据，另一方面，我正在获取现有的生产数据。两个系统之间的数据应该是同步的，但它们没有达到任何有意义的水平。在大多数情况下，这并不是一个大问题，因为我在可能的情况下已将它们基本合并。在某些情况下，这些行与员工 ID 匹配，而在其他情况下，仅使用姓氏和出生日期。

外部视图数据始终具有两条不同的信息，一个员工 ID 和另一个标识符。生产数据将始终具有一个不同的 ID，即 file_number。有时生产会有外部视图键，但这是不正常的。这些键是两边的主键，而不是姓氏和出生日期。

最后一个比较导致了这个问题，因为我们有几个要求，我们只被允许加入这个标准，例如当员工存在于生产端时，可以获得外部视图的条目。由于姓氏和出生日期不是特别不同，我为这些记录创建了一个排除表，否则这些记录会导致问题但有效（例如双胞胎）。排除表从双方获取所有违规记录。

由于我无法找到一种方法来获得某种约束，使这些重复记录会自动输入到排除表中（对数据输入的控制很差），所以我求助于触发器。

错误

错误报告 - SQL 错误：ORA-04021：等待锁定对象时发生超时 ORA-06512：在“USER.EXCLUSION_TRG”，第 4 行 ORA-04088：执行触发器“USER.EXCLUSION_TRG”时出错 ORA-06512：在“ USER.VIEW_DUPLICATE_TRG”，第 4 行 ORA-04088：执行触发器 'USER.VIEW_DUPLICATE_TRG' 04021 期间出错。00000 -“等待锁定对象 %s%s%s%s%s 时发生超时” *原因：在等待锁定库对象，发生超时。*操作：稍后重试该操作。

VIEW 表的第一个触发器

CREATE OR REPLACE TRIGGER VIEW_DUPLICATE_TRG
AFTER INSERT OR UPDATE ON VIEW_PERSON
BEGIN

INSERT INTO VIEW_EXCLUSION_PERSON (EMPLID, PRI, COMMENTS)
select  emplid, PRI, 'VIEW CREATED '||SYSDATE from (
select upper(CONVERT(last_name, 'US7ASCII')) LAST_NAME, birthdate,first_name,emplid, pri,  count(*) over (partition by upper(CONVERT(last_name, 'US7ASCII')), birthdate) duplicate_count from VIEW_PERSON 
) K where duplicate_count > 1 
and NOT EXISTS (select emplid from exclusion_person Z WHERE K.EMPLID=Z.EMPLID);
END;

Prod 表的第二个触发器

CREATE OR REPLACE TRIGGER PROD_DUPLICATE_TRG
AFTER INSERT OR UPDATE ON BACKGROUND_INFO
BEGIN
INSERT INTO EXCLUSION_PERSON (FILE_NUMBER, COMMENTS)
SELECT FILE_NUMBER, 'PROD CREATED '||SYSDATE 
FROM BACKGROUND_INFO
WHERE (SURNAME, BIRTHDATE) IN
    (SELECT SURNAME, BIRTHDATE
    FROM BACKGROUND_INFO
    GROUP BY SURNAME, BIRTHDATE
    HAVING COUNT(*) > 1
    )
    AND FILE_NUMBER NOT IN (SELECT FILE_NUMBER FROM exclusion_person WHERE FILE_NUMBER IS NOT NULL);
END;

排除表的第三个触发器

CREATE OR REPLACE TRIGGER EXCLUSION_TRG
AFTER INSERT ON EXCLUSION_PERSON
DECLARE 
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
EXECUTE IMMEDIATE 'ALTER TRIGGER EXCLUSION_TRG DISABLE';

merge into EXCLUSION_PERSON E
using (select file_number, DECODE(TRIM(PRI), '99999999', NULL, PRI) PRI from administrative_info) A
on (E.PRI=A.PRI)
when matched then update set E.file_number = A.file_number, E.COMMENTS = E.COMMENTS||', MATCHED ON PRI ON '||SYSDATE
WHERE E.FILE_NUMBER IS NULL AND E.PRI IS NOT NULL AND A.PRI IS NOT NULL;

MERGE INTO VIEW_EXCLUSION_PERSON E
USING (SELECT FILE_NUMBER, EMPLID FROM VIEW_PERSON) P
ON (P.EMPLID = E.EMPLID)
WHEN MATCHED THEN UPDATE SET 
    E.FILE_NUMBER = P.FILE_NUMBER,
    E.COMMENTS = E.COMMENTS||' MATCHED FROM PERSON '||SYSDATE
WHERE E.FILE_NUMBER IS NULL AND E.EMPLID IS NOT NULL AND P.FILE_NUMBER IS NOT NULL;

EXECUTE IMMEDIATE 'ALTER TRIGGER VIEW_DUPLICATE_TRG DISABLE';

MERGE INTO VIEW_PERSON P
USING (SELECT FILE_NUMBER, EMPLID FROM EXCLUSION_PERSON ) E
ON (P.EMPLID = E.EMPLID)
WHEN MATCHED THEN UPDATE SET P.FILE_NUMBER = E.FILE_NUMBER
WHERE P.FILE_NUMBER IS NULL AND E.EMPLID IS NOT NULL AND E.FILE_NUMBER IS NOT NULL;

EXECUTE IMMEDIATE 'ALTER TRIGGER VIEW_DUPLICATE_TRG ENABLE';
EXECUTE IMMEDIATE 'ALTER TRIGGER EXCLUSION_TRG ENABLE';
END;

所以问题似乎是第一个触发器 VIEW_DUPLICATER_TRG 正在锁定某些东西，而不是在 EXCLUSION_TRG 尝试运行时释放该锁定。当我去寻找这个 %S%S%S%S%S 对象时，我找不到它，而且我的代码都没有调用这个对象。

标签： oracletriggerslocking

解决方案

主要问题在这里：

EXECUTE IMMEDIATE 'ALTER TRIGGER EXCLUSION_TRG DISABLE';

这会在触发器本身执行时尝试禁用触发器！

这永远不会起作用：您不能在触发器执行时禁用它。所以试图从它自己的执行中改变它的状态将被阻止。

您可以通过以下方式看到这一点：

create table t (
  c1 int
);

create or replace trigger trg
after insert on t
declare
  pragma autonomous_transaction;
begin
  execute immediate 'alter trigger trg disable';
end;
/

insert into t values ( 1 );

将insert被卡住，等待尝试禁用触发器。但是触发器正在执行。所以你不能禁用它。啊啊啊啊！

这整个过程需要重新设计。理想情况下没有任何触发器！

推荐阅读