2

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?

4
  • there are really deadlocks? So one session kills itself? Did you try to analyze the trace file? Is it really caused by the replication table or is it already caused by the original table?
    – miracle173
    Aug 20, 2018 at 18:58
  • could you sketch a szenario were the dealock is caused by the replication table and not by the original table?
    – miracle173
    Aug 20, 2018 at 19:14
  • Well, you were right. There were real deadlocks, however they occured on the original table, from some external application. I wasted half of my day and feel like a moron. Thank you.
    – plunz
    Aug 20, 2018 at 19:14
  • Do you think this logic is robust against any data change scenarios? I had some kind of feeling that these UPDATEs could potentially always lead to deadlocks, if only the worst case occurs.
    – plunz
    Aug 20, 2018 at 19:16

2 Answers 2

0

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.

-1

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.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.