Deadlocks With Trigger Based Replication

Question

A table ORIGINAL exists with the following structure:

ID    VARCHAR Length 10 (key)
VALUE VARCHAR Length 10 (non-key)

A table REPLICATION exists with the following structure:

ID               VARCHAR Length 10 (key)
CHANGE_TIMESTAMP NUMBER  Length 15 (non-key)

I want to log every changed primary key to the REPLICATION table, with the latest change time stamp.

Therefore I have created this trigger in oracle:

CREATE OR REPLACE TRIGGER REPLICATION_TEST
  AFTER INSERT OR UPDATE ON ORIGINAL
  FOR EACH ROW 

  DECLARE timestamp DEC(15);
  BEGIN 
    SELECT TO_NUMBER(TO_CHAR(SYSDATE, 'yyyymmddhh24miss')) 
      INTO timestamp 
      FROM dual; 

    INSERT INTO REPLICATION
      FIELDS ("id", "change_timestamp") 
      VALUES (:NEW."id", timestamp); 
    EXCEPTION WHEN dup_val_on_index THEN 
      UPDATE REPLICATION
        SET "change_timestamp" = timestamp 
        WHERE "id" = :NEW."id"
END;

Functionally, this works just fine. But in a productive environment with multiple sessions where arbitrary data changes can happen at any time, this infrequently leads to deadlocks. Presumably because of the UPDATE statement.

An alternative approach would be to add the CHANGE_TIMESTAMP field as additional key field to do only INSERTS into the REPLICATION table and skip the UPDATE in case of duplicates. This would work functionally just fine, but would obviously result in much more data being produced which I'd like to avoid.

What else can I do?

Answer 1

Before rows are updated in the REFERENCE table the corresponding rows must be updated in the ORIGINAL table. If a session waits for a lock of a row of the REFERENCE table to update it must get the lock of the corresponding ORIGINAL table row lock. But this is hold by the session that holds the REFERENCE table row lock. I can't see any problem with this design, but I am not 100% sure. Nevertheless I think it i easier to read if you create an update statement that is triggered for updates and an insert statement that is triggered for inserts. But maybe this is a matter of taste.

Answer 2

I have limited knowledge of oracle internals, however, based on my experience of deadlocks you can create two separate triggers for Inserts / Update and do not check the "EXCEPTION WHEN dup_val_on_index". I think because of that check its holding lock on index longer.