PLSQL Deadlock on Trigger

Question

I am trying to implement a trigger and reach a deadlock state, specifically: ORA-00060: deadlock detected while waiting for resource. The issue might be related to a trick I found to treat the previous mutating table error I had in the trigger I use to call my procedure, pragma autonomous_transaction (see below).

I am trying to work on the same table, which is the issue here: but can I fix the problem via timing or in another way?

The complete procedure, trigger and call code:

-- 1) 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;


-- 1) 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;

--1) 3)

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;

--1) 4)

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;

Table PAYMENT: {PAYMENT_ID, CUSTOMER_ID, STAFF_ID, RENTAL_ID, AMOUNT, PAYMENT_DATE, LAST_UPDATE, USER_MODIFED*}

*USER_MODIFIED is added as a column in the course of the script.

Answer 1

Here is a link to some good resources on triggers. Triggers: You Say Stop, I Say Go Your trick, PRAGMA AUTONOMOUS_TRANSACTION, is not needed with either trigger. Deadlock can be avoiding by changing when one of the trigger fires and how the columns are updated. If triggers are the only option for this program logic, these two triggers could be combined into one trigger.

CHECK_AMOUNT_TRG Trigger

The join to the PAYMENT table is unnecessary. Get rid of it and you can get rid of the PRAGMA AUTONOMOUS_TRANSACTION.

1. The payment.amount value is already available via the :NEW.AMOUNT pseudocolumn.
2. The :NEW.RENTAL_ID pseudocolumn can be used to remove the join to the PAYMENT table.

CREATE OR REPLACE TRIGGER CHECK_AMOUNT_TRG
BEFORE INSERT OR UPDATE
ON PAYMENT FOR EACH ROW
DECLARE
    pmt NUMBER;
    f_id INTEGER;
BEGIN

  pmt := :NEW.amount;

  -- handle exception if SELECT INTO returns no data
  SELECT 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, pmt, 3);

END;

LOG_PAYMENT Trigger

The update in the AUTONOMOUS_TRANSACTION will never complete. It is the source of your ORA-00060 error. The update inside the trigger will always be blocked by the update (or insert) that caused the trigger to fire.

Also, the trigger is defined to fire at the wrong time. A column on the triggering table can only be updated in a BEFORE trigger.

1. Change timing to BEFORE INSERT OR UPDATE
2. Assign the audit values to the NEW pseudocolumns for USER_MODIFIED and LAST_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;

Combined Logic in One Trigger

CREATE OR REPLACE TRIGGER CHECK_AMT_AND_LOG_PMT_TRG
BEFORE INSERT OR UPDATE
ON PAYMENT FOR EACH ROW
DECLARE
    pmt NUMBER;
    f_id INTEGER;
BEGIN

  pmt := :NEW.amount;

  -- handle exception if SELECT INTO returns no data
  SELECT 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, pmt, 3);

  :NEW.user_modified := USER;
  :NEW.last_update := SYSTIMESTAMP;
  
END;