PLSQL触发器触发ORA-00060死锁问题排查与修复咨询

实现PLSQL触发器时遇到ORA-00060:检测到死锁等待资源错误。此前为解决触发器中的变异表问题使用了pragma autonomous_transaction，但当前因操作同一张PAYMENT表引发死锁，需调整时序或其他方式解决。

1) 金额校验存储过程 #

CREATE OR REPLACE PROCEDURE CHECK_AMOUNT(f_id INTEGER, p_amt IN OUT NUMBER, r_days INTEGER)
IS
    going_rate NUMBER;
    INVALID_AMT EXCEPTION;
BEGIN
    SELECT RENTAL_RATE INTO going_rate
    FROM FILM
    WHERE FILM_ID = f_id;

    IF p_amt > (going_rate * r_days) THEN
        RAISE INVALID_AMT;
    ELSIF p_amt < 0 THEN
        p_amt := 0;
    END IF;

    EXCEPTION
        WHEN INVALID_AMT THEN
            DBMS_OUTPUT.PUT_LINE('Invalid amount for film (id) ' || f_id || ', maximum is ' || going_rate * r_days || '.');
END;

存储过程测试代码 #

DECLARE
pmt NUMBER;
BEGIN
    pmt := 20; -- triggers error
    --pmt := -20; -- also works as sepcified
    CHECK_AMOUNT(200, pmt, 3);
    DBMS_OUTPUT.put_line (pmt);
END;

2) 金额校验触发器 #

CREATE OR REPLACE TRIGGER CHECK_AMOUNT_TRG
BEFORE INSERT OR UPDATE
ON PAYMENT FOR EACH ROW
DECLARE
    pragma autonomous_transaction;
    pmt NUMBER;
    f_id INTEGER;
BEGIN
    SELECT FILM_ID, AMOUNT INTO f_id, pmt
        FROM INVENTORY
        JOIN RENTAL
        ON INVENTORY.INVENTORY_ID = RENTAL.INVENTORY_ID
        JOIN PAYMENT
        ON RENTAL.RENTAL_ID = PAYMENT.RENTAL_ID
        WHERE PAYMENT_ID = :NEW.PAYMENT_ID;
    CHECK_AMOUNT(f_id, pmt, 3);
END;

触发器测试更新语句 #

UPDATE PAYMENT
SET AMOUNT = 25
WHERE PAYMENT_ID = 6500;

UPDATE PAYMENT
SET AMOUNT = 1
WHERE PAYMENT_ID = 3000;

UPDATE PAYMENT
SET AMOUNT = -10
WHERE PAYMENT_ID = 1200;

ROLLBACK;

3) 操作日志触发器及测试 #

ALTER TABLE PAYMENT ADD user_modified VARCHAR(50);

CREATE OR REPLACE TRIGGER LOG_PAYMENT
AFTER INSERT OR UPDATE
ON PAYMENT FOR EACH ROW
DECLARE
    pragma autonomous_transaction;
BEGIN
    UPDATE PAYMENT
        SET PAYMENT.user_modified = USER, PAYMENT.LAST_UPDATE = SYSTIMESTAMP
        WHERE PAYMENT_ID = :NEW.PAYMENT_ID;
END;

日志触发器测试更新语句 #

UPDATE PAYMENT
SET AMOUNT = 25
WHERE PAYMENT_ID = 6500;

UPDATE PAYMENT
SET AMOUNT = 1
WHERE PAYMENT_ID = 3000;

UPDATE PAYMENT
SET AMOUNT = -10
WHERE PAYMENT_ID = 1200;

ROLLBACK;

PAYMENT_ID, CUSTOMER_ID, STAFF_ID, RENTAL_ID, AMOUNT, PAYMENT_DATE, LAST_UPDATE, USER_MODIFIED（USER_MODIFIED为新增列）

死锁根源是自治事务与主事务操作同一张表的同一行，互相等待对方释放锁，且两个触发器的自治事务都是不必要的，调整方案如下：

1) 重构CHECK_AMOUNT_TRG触发器 #

原触发器错误地在自治事务中查询PAYMENT表，且完全可以通过:NEW直接获取RENTAL_ID，无需关联PAYMENT表，同时去掉自治事务：

CREATE OR REPLACE TRIGGER CHECK_AMOUNT_TRG
BEFORE INSERT OR UPDATE
ON PAYMENT FOR EACH ROW
DECLARE
    f_id INTEGER;
    v_amt NUMBER := :NEW.AMOUNT;
BEGIN
    -- 通过RENTAL_ID关联获取FILM_ID，无需查询PAYMENT表
    SELECT i.FILM_ID INTO f_id
    FROM INVENTORY i
    JOIN RENTAL r ON i.INVENTORY_ID = r.INVENTORY_ID
    WHERE r.RENTAL_ID = :NEW.RENTAL_ID;
    
    CHECK_AMOUNT(f_id, v_amt, 3);
    -- 将校验后的金额赋值回:NEW
    :NEW.AMOUNT := v_amt;
END;

• 去掉pragma autonomous_transaction，避免跨事务锁等待
• 直接使用:NEW.RENTAL_ID关联查询FILM_ID，不再访问PAYMENT表，彻底解决变异表问题

2) 重构LOG_PAYMENT触发器 #

原触发器在AFTER阶段用自治事务更新同一张表，导致主事务锁与自治事务锁冲突。改为在BEFORE阶段直接设置字段值，无需额外UPDATE操作：

CREATE OR REPLACE TRIGGER LOG_PAYMENT
BEFORE INSERT OR UPDATE
ON PAYMENT FOR EACH ROW
BEGIN
    :NEW.user_modified := USER;
    :NEW.LAST_UPDATE := SYSTIMESTAMP;
END;

• 去掉自治事务，直接在BEFORE触发器中修改:NEW字段，无需更新表，完全避免锁冲突
• BEFORE触发器中可以直接修改:NEW的值，提交后会自动写入表，无需额外DML操作

关键说明 #

• 自治事务（autonomous_transaction）是独立于主事务的事务，使用时如果操作主事务已锁定的资源，必然会引发锁等待甚至死锁，非必要不要使用
• 变异表问题的正确解决方式不是依赖自治事务，而是通过:NEW/:OLD直接获取字段值，或使用复合触发器，避免在触发器中直接查询触发表

内容的提问来源于stack exchange，提问作者marie_antoinette