Best practice for updating column specific triggers

user_8466853 0
Viewed 379

Welcome Oracle pro's

In an Oracle 12 database (upgrade is already scheduled ;-)) we have a setup of different tables updating a common base table via "after update" triggers like following:

Search_Flat
ID Field_A Field_B Field_C

Now table1 contains n columns where let's say 2 out of n are relevant for the Search_Flat table. As the update of table1 may only affect columns not relevant for Seach_Flat we want to add checks to the trigger. So our first approach is like following:

CREATE OR REPLACE TRIGGER tr_tbl_1_au_search
    AFTER UPDATE OF
        field_a,
        field_b
    ON schemauser.search_flat
FOR EACH ROW
    BEGIN
        IF :new.field_a <> :old.field_a THEN 
            UPDATE schemauser.search_flat SET field_a = :new.field_a WHERE id = :new.ID;
        END IF;
        IF :new.field_b <> :old.field_b THEN 
            UPDATE schemauser.search_flat SET field_b = :new.field_b WHERE id = :new.ID;
        END IF;
    END;

Alternatively we could also setup the trigger like following:

CREATE OR REPLACE TRIGGER tr_tbl_1_au_search
    AFTER UPDATE OF
        field_a,
        field_b
    ON schemauser.search_flat
FOR EACH ROW
    BEGIN
        IF :new.field_a <> :old.field_a OR :new.field_b <> :old.field_b THEN 
            UPDATE schemauser.search_flat 
            SET field_a = :new.field_a, 
                field_b = :new.field_b 
            WHERE id = :new.ID;
        END IF;
    END;

The question now is about the setup of the triggers themselves. Which approach is the better with respect to:

  • locking time of search_flat rows
  • overall performance of affected components (i.e., table_1, trigger and search_flat)

In production we are talking about 4 tables with 10 fields each considered in the triggers. And we have independent app servers accessing the shared database updating the 4 tables simultaneously. From time to time we detect the following error which is the reason we wan't to optimize the triggers:

ORA-02049: timeout: distributed transaction waiting for lock

Sidenote: This setup has been chosen instead of a view or materialized view due to performance reasons as the base table is used in gui with the requirement to be instantly updated and the number of records of the 4 feeding tables are too high for updating materialized view on update.

I'm looking forward to the discussion and your thoughts.

optimistic-locking triggers oracle
1 Answers

As I understand your post, you have 4 live tables (called "table1", "table2", etc.) that you want to search on, but querying from them is too slow, so you want to maintain a single, flattened table to search on instead and have triggers to keep that flattened table always up-to-date. You want to know which of two trigger approaches is better.

I think the answer is "neither", since both are prone to deadlocks. Imagine this scenario

User 1 -

UPDATE table1 
SET field_a = 500 
WHERE <condition effecting 200 distinct IDs>

User 2 at about the same time -

UPDATE table1 
SET field_b = 700 
WHERE <condition effecting 200 distinct IDs>

Triggers start processing. You cannot control the order in which the rows are updated. Maybe it goes like this:

User 1's trigger, time index 100 ->

UPDATE search_flat SET field_a = 500 WHERE id = 90;

User 2's trigger, time index 101 ->

UPDATE search_flat SET field_b = 700 WHERE id = 91;

User 1's trigger, time index 102 ->

UPDATE search_flat SET field_a = 500 WHERE id = 91;  (waits on user 2's session)

User 2's trigger, time index 103 ->

UPDATE search_flat SET field_b = 700 WHERE id = 90;  (deadlock error)

User 2's original update fails and rolls back.

You have multiple concurrent processes all updating the same set of rows in search_flat with no control over the processing order. That is a recipe for deadlocks.

If you wanted to do this safely, you should consider neither of the FOR EACH ROW trigger approaches you outlines. Rather, make a compound trigger to do this.

Here's some sample code to illustrate the idea. Be sure to read the comments.

-- Aside: consider setting this at the system level if on 12.2 or later
--   alter system set temp_undo_enabled=false;

CREATE GLOBAL TEMPORARY TABLE table1_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80),
  field_b     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

CREATE GLOBAL TEMPORARY TABLE table2_updates_gtt (
  id          NUMBER,
  field_a     VARCHAR2(80)
) ON COMMIT DELETE ROWS;

-- .. so on for table3 and 4.

CREATE OR REPLACE TRIGGER table1_search_maint_trg
  FOR INSERT OR UPDATE OR DELETE ON table1  -- with similar compound triggers for table2, 3, 4.
    COMPOUND TRIGGER

  AFTER EACH ROW IS
  BEGIN
    -- Update the table-1 specific GTT with the changes.
    CASE WHEN INSERTING OR UPDATING THEN
      -- Assumes ID is immutable primary key
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:new.id, :new.field_a);
         WHEN DELETING THEN
      INSERT INTO table1_updates_gtt (id, field_a) VALUES (:old.id, null);  -- or figure out what you want to do about deletes.
    END CASE;
  END AFTER EACH ROW;

  AFTER STATEMENT IS
  BEGIN
    -- Write the data from the GTT to the search_flat table.
    -- NOTE: The ORDER BY in the next line is what saves us from deadlocks.
    FOR r IN ( SELECT id, field_a, field_b FROM table1_updates_gtt ORDER BY id ) LOOP
      -- TODO: replace with BULK processing for better performance, if DMLs can affect a lot of rows
      UPDATE search_flat sf
      SET    sf.field_a = r.field_a,
             sf.field_b = r.field_b
      WHERE  sf.id = r.id
      AND    ( sf.field_a <> r.field_a 
               OR (sf.field_a IS NULL AND r.field_a IS NOT NULL) 
               OR (sf.field_a IS NOT NULL AND r.field_a IS NULL)
               OR sf.field_b <> r.field_b 
               OR (sf.field_b IS NULL AND r.field_b IS NOT NULL) 
               OR (sf.field_b IS NOT NULL AND r.field_b IS NULL)
             );
    END LOOP;             
          
  END AFTER STATEMENT;

END table1_search_maint_trg;

Also, as numerous commenters have pointed out, it's probably better to use a materialized view for this. If you are on 12.2 or later, real-time materialized views (aka "ENABLE ON QUERY COMPUTATION") offer a lot of promise for this sort of thing. No COMMIT overhead to your application and real-time search results. It's just that search time degrades slightly if there are a lot of recent updates to the underlying tables.

user_51744360
Related