Translation of DB2 Trigger to Oracle triggers ORA-04091 Error

0
votes

So I have two tables: JOBS and TASKS.

The TASKS Table has a TAKS_STATUS Column that stores a progression of states (e.g. 'Submitting', 'Submitted', 'Authored', 'Completed').

The JOBS Table has a JOB_STATUS Column which is a roll-up (i.e. minimum state) of the TASK_STATUS Columns in the TASKS Table. The JOBS Table also has a TASK_COUNT value that contains the number of TASKS associated with a Job.

Jobs can have one or more tasks: JOB_ID in each table links them.

In DB2, I have a series of simple triggers to roll-up this state; here's one in the middle:

create or replace trigger JOB_AUTHORED
  after update of TASK_STATUS on TASKS
  referencing NEW as N
  for each row
  when (TASK_STATUS = 'Authored')
    update JOBS
      set JOB_STATUS = 'Authored'
      where JOBS.JOB_ID = N.JOB_ID
        and TASK_COUNT=(
          select count(0) from TASKS
            where TASKS.JOB_ID = N.JOB_ID
              and TASKS.TASK_STATUS in ('Authored','Completed'))

This works fine in DB2 because the trigger runs in the same work unit as the triggering event and thus it can see the work unit's uncommitted changes and can count the TASK_STATUS change that just occurred without hitting a row-lock.

Here's the translated trigger in Oracle:

create or replace trigger JOB_AUTHORED
  after update of TASK_STATUS on TASKS
  for each row
  when (NEW.TASK_STATUS = 'Authored')
  BEGIN
    update JOBS
      set JOB_STATUS='Authored'
      where JOBS.JOB_ID = :NEW.JOB_ID and TASK_COUNT=(
        select count(0) from TASKS
          where TASKS.JOB_ID = :NEW.JOB_ID
            and TASKS.TASK_STATUS in ('Authored','Completed'));
  END;

In Oracle this fails with:

ORA-04091: table MYSCHEMA.TASKS is mutating, trigger/function may not see it#012ORA-06512: at "MYSCHEMA.JOB_AUTHORED", line 1#012ORA-04088: error during execution of trigger 'MYSCHEMA.JOB_AUTHORED'#012] [query: UPDATE TASKS SET TASK_STATUS=:1 where TASK_ID=:2

Apparently Oracle's Triggers do not run in the same context, can not see the uncommitted triggering updates and thus will never be able to count the number of tasks in specific states which include the triggering row.

I guess I could change the AFTER Trigger to an INSTEAD OF Trigger and update the TASK_STATUS (as well as the JOB_STATUS) within the Trigger (so the Job Update can see the Task Update), but would I run into the same error? Maybe not the first Task Update, but what if the triggering program is updating a bunch of TASKS before committing: what happens when the second Task is updated?

I've also considered removing the Trigger and have a program scan active Jobs for the status of its Tasks, but that seems inelegant.

What is the Best Practice with something like this in Oracle?

3
oracletriggersora-04091
Could you explain why do you compare JOBS.TASK_COUNT with a current count of rows in TASKS table ? I can imagine that for some reasons (application erors? someone inserted/deleted some rows directly into/from the table?) the count in JOBS will don't match the count of related rows in TASKS table, and the data in tables is not in consistent state. In this case your trigger will silently fail and will not give expected reasons. How are you enforcing a rule JOBS.TASK_COUNT must always match the number of rows in TASKS table ? – krokodilko
I count the number of TASKS that are at a particular state or later to see if it matches the TASK_COUNT and thus knowing that all the TASKS have made it to a particular point (in this example: Authored). So, for further example, let's say a Job is created with 20 TASKS in Submitting Status and they go off and go through various states. When the first Task hits 'Authored' this Trigger checks to see if there are 20 TASKS in 'Authored' or 'Completed' STATUS and if there are the Job becomes 'Authored' STATUS. Since this was the first 'Authored' Task, the count will be 1 and thus no Job change. – kjpires
When the 20th Task reaches 'Authored' STATUS, the trigger's where with the select count becomes true and set the Job's STATUS to 'Authored'. At this point some of the Tasks may be in 'Authored' and some might be in a later state (e.g. 'Completed'). The trigger fired off exactly 20 times with the count going from 1 to 20 along the way. If some tasks went to 'Error' STATUS either before or after, the who Job would never get to 'Authored' (and another trigger causes the Job to roll-up to 'Error' STATUS). – kjpires

3 Answers

2
votes

The best practice is to avoid triggers if you can.
See these links for an answer why one shoud not use triggers:
http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html
http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html

Use a procedure (an API) in place of triggers - you can create a package with a few procedures like add_new_job, add_new_task, change_task_status etc., and place all logic in these procedures (checking, changing tasks' states, changing jobs' states etc.) in one place. Easy to understand, easy to maintain, easy to debug and easy to tracking errors.

If you insist on using triggers, then you can create the compound trigger, as Tom Kyte mentioned in the frist link above as workaround, for example:

create or replace TRIGGER JOB_AUTHORED
FOR UPDATE OF TASK_STATUS on TASKS
COMPOUND TRIGGER

  TYPE JOB_ID_TABLE_TYPE IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;  
  JOB_ID_TABLE JOB_ID_TABLE_TYPE;
  dummy CHAR;

    AFTER EACH ROW IS
     BEGIN
       -- SELECT null INTO dummy FROM JOBS WHERE job_id = :NEW.JOB_ID FOR UPDATE;
        JOB_ID_TABLE( :NEW.JOB_ID ) := :NEW.JOB_ID;
     END AFTER EACH ROW;

     AFTER STATEMENT IS
     BEGIN
       FORALL x IN INDICES OF JOB_ID_TABLE
         UPDATE jobs set JOB_STATUS='Authored'
         WHERE JOBS.JOB_ID = JOB_ID_TABLE( x )
           and TASK_COUNT=(
                  select count(0) from TASKS
                  where TASKS.JOB_ID = JOBS.JOB_ID
                    and TASKS.TASK_STATUS in ('Authored','Completed')
          );
     END AFTER STATEMENT;

END JOB_AUTHORED;

but .....
I am not sure if there are no any pitfals in this example we are not aware at this time.

For example one pitfall is in this scenario:
Suposse there are 18 tasks with status Authored

  • At time X user A runs UPDATE TASK SET status ' Authored' WHERE task_id = 2. The trigger is fired and sees 18+1 commited tasks with status Authored
  • At time X+10ms user B runs UPDATE TASK1 SET status ' Authored' task_id = 4. The trigger is fired and sees 18+1 commited tasks with status Authored
  • At time X+20ms user A commits
  • At time X+30ms user B commits
  • At the end we have 21 tasks with status authored. but job's status have not been changed to Authored (but should be changed to Authored if number of tasks = 20).

To avoid this trap you can use SELECT null INTO dummy FROM JOBS WHERE job_id = :NEW.JOB_ID FOR UPDATE; in the after each row part of the trigger in order to place a lock at corresponding record in JOBS table in order to serialize access (I am commented it in the example above).
But I'am still not sure if this is correct solution - it may cause in turn some deadlocks in scenarious I cannot imagine and predict at this time.

0
votes

In short - in Oracle, in triggers you cannot select from table on which trigger is constructed. Otherwise you may/you will get mutating table error.

You have several options:

1) No triggers at all - in my humble opinion this is the best, I would do it this way (but probably topic is wider and we don't know everything). Create view, which replaces the need of triggers, something like:

create or replace view v_jobs_done as 
  select * from jobs where not exists 
    select 1 from tasks 
      where TASKS.JOB_ID = jobs.JOB_ID
        and TASKS.TASK_STATUS not in ('Authored','Completed')

2) Instead of increasing value use decreasing value - so when jobs.tasks_count reaches zero you know everything's done. In this case you have to build/rebuild your other triggers,

3) Proposition close to yours - you can use modern compound trigger - I doubt in performance here, but it works:

create or replace trigger Job_Authored
for update of task_status on tasks compound trigger

  type t_ids   is table of tasks.job_id%type;
  type t_cnts  is table of number;
  type t_job_counts is table of number index by varchar2(10);
  v_ids        t_ids;
  v_cnts       t_cnts;
  v_job_counts t_job_counts;

before statement is
  begin
    select job_id, count(1) 
      bulk collect into v_ids, v_cnts
      from tasks where tasks.task_status in ('Authored','Completed')
      group by job_id;

    for i in 1..v_ids.count() loop
      v_job_counts(v_ids(i)) := v_cnts(i);
    end loop;
  end before statement;

after each row is
  begin
    if :new.task_status = 'Authored' then 
      update jobs set job_status='Authored'
        where job_id = :new.job_id
          and task_count = v_job_counts(:new.job_id);
    end if;
  end after each row;
end Job_Authored;
0
votes

The best practice is to avoid triggers if you can.
See this links for an answer why one shoud not use triggers:
http://www.oracle.com/technetwork/testcontent/o58asktom-101055.html
http://rwijk.blogspot.com/2007/09/database-triggers-are-evil.html

Use a procedure (an API) in place of triggers - you can create a package with a few procedures like add_new_job, add_new_task, change_task_status etc., and place all logic in these procedures (checking, changing tasks' states, changing jobs' states etc.) in one place. Easy to understand, easy to maintain, easy to debug and easy to track errors.

If you insist on using triggers, then you can create the compound trigger, as Tom Kyte mentioned in the frist link above as workaround, for example:

create or replace TRIGGER JOB_AUTHORED
FOR UPDATE OF TASK_STATUS on TASKS
COMPOUND TRIGGER

  TYPE JOB_ID_TABLE_TYPE IS TABLE OF PLS_INTEGER INDEX BY PLS_INTEGER;  
  JOB_ID_TABLE JOB_ID_TABLE_TYPE;
  dummy CHAR;

    AFTER EACH ROW IS
     BEGIN
       -- SELECT null INTO dummy FROM JOBS WHERE job_id = :NEW.JOB_ID FOR UPDATE;
        JOB_ID_TABLE( :NEW.JOB_ID ) := :NEW.JOB_ID;
     END AFTER EACH ROW;

     AFTER STATEMENT IS
     BEGIN
       FORALL x IN INDICES OF JOB_ID_TABLE
         UPDATE jobs set JOB_STATUS='Authored'
         WHERE JOBS.JOB_ID = JOB_ID_TABLE( x )
           and TASK_COUNT=(
                  select count(0) from TASKS
                  where TASKS.JOB_ID = JOBS.JOB_ID
                    and TASKS.TASK_STATUS in ('Authored','Completed')
          );
     END AFTER STATEMENT;

END JOB_AUTHORED;

but .....
I am not sure if there are no any pitfals in this example we are not aware at this time.

For example one pitfall is in this scenario:
Suposse there are 18 tasks with status Authored

  • At time X user A runs UPDATE TASK SET status ' Authored' WHERE task_id = 2. The trigger is fired and sees 18+1 commited tasks with status Authored
  • At time X+10ms user B runs UPDATE TASK1 SET status ' Authored' task_id = 4. The trigger is fired and sees 18+1 commited tasks with status Authored
  • At time X+20ms user A commits
  • At time X+30ms user A commits
  • At the end we have 21 tasks with status authored. but job's status have not been changed to Authored (but should be changed to Authored if number of tasks = 20).

To avoid this trap you can use SELECT null INTO dummy FROM JOBS WHERE job_id = :NEW.JOB_ID FOR UPDATE; in the after each row part of the trigger in order to place a lock at corresponding record in JOBS table in order to serialize access (I am commented it in the example above).
But I'am still not sure if this is correct solution - it may cause in turn deadlocks in scenarious I cannot imagine and predict at now.