基於觸發器的複制的死鎖

存在具有以下結構的表ORIGINAL ：

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

存在具有以下結構的表REPLICATION ：

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

我想用最新的更改時間戳將每個更改的主鍵記錄到REPLICATION表中。

因此我在 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;

從功能上講，這工作得很好。但是在具有多個會話的生產環境中，任意數據更改可能隨時發生，這很少會導致死鎖。大概是因為 UPDATE 語句。

另一種方法是將CHANGE_TIMESTAMP欄位添加為附加鍵欄位，僅在REPLICATION表中執行 INSERTS 並在重複的情況下跳過 UPDATE。這在功能上可以正常工作，但顯然會導致產生更多我想避免的數據。

我還可以做些什麼？

在更新 REFERENCE 表中的行之前，必須更新 ORIGINAL 表中的相應行。如果會話等待 REFERENCE 表的行鎖更新，則它必須獲得相應 ORIGINAL 表行鎖的鎖。但這是由持有 REFERENCE 表行鎖的會話持有的。我看不出這種設計有任何問題，但我不是 100% 確定。儘管如此，我認為如果您創建一個為更新而觸發的更新語句和一個為插入而觸發的插入語句，我認為更容易閱讀。但也許這是一個品味問題。

我對 oracle 內部的了解有限，但是，根據我對死鎖的經驗，您可以為插入/更新創建兩個單獨的觸發器，並且不要檢查“在 dup_val_on_index 時出現異常”。我認為因此檢查它對索引的鎖定時間更長。

引用自：https://dba.stackexchange.com/questions/215413