INSERT…ON DUPLICATE KEY UPDATE ..在 MySQL 5.7.26（Aurora MySQL 2.10.3）後的行為差異

Database
09 Jun, 2023

MySQL 5.7.26 修復了一個 bug #2596684（release note），Aurora MySQL 在 2.10.3（release note）也修復了這個 bug。

行為差異

這個 bug fix 後，會有一些行為影響變化：

在 table 有 primary key 和 unique index 的 column 時，insert on duplicated update 遇到 duplicated key record 後，會上一個 primary index 的 lock 在 supremum pseudo-record（最後一個 record 到無窮大的 gap）。因而導致其他 insert 需要等這個 lock 釋放。

在多個 insert on duplicated update 一次 insert 多筆資料時，會很容易產生出 deadlock。

對此，有人回報了另一個 bug，覺得因為 #2596684 bug 修復後，導致使用 insert on duplicate key update 會更頻繁的產生 deadlock。在這個 bug report 內，Jakub Lopuszanski 很詳細的解釋了關於 bug #2596684 修復的脈絡跟如何避免 deadlock 的建議，很推薦可以詳細看。

簡單說明

第一點 supremum pseudo-record 的 lock，借用裡面的例子來解釋。

假設有一表：

create table t1(
 f1 int auto_increment primary key,
 f2 int unique key,
 f3 int
);

有一筆資料：

(1, 10, 100)

此時如果有兩個 connection

con1:
    begin;
    insert into t1 values(2, 10, 200) on duplicate key update f3 = 120;
    commit;
    
con2:
    begin;
    insert into t1 values(2, 20, 300) on duplicate key update f3 = 500;
    commit;

在只知道 con1 con2 transaction 最後 commit 的順序，但不確定實際兩個 insert 的順序時，會因為 con2 的 insert 跟 con1 的 insert 的執行順序導致不一樣的結果。

如果 con1 的 insert 先執行，會因為 con1 insert 的 duplicate key 是 f2。所以有這樣的結果：

(1,10,120)
(2,20,300)

但如果是 con2 的 insert 執行，因為 con1 insert 的 duplicate key 變成是 f1（primary key）了，所以結果變成：

(1,10,100)
(2,20,120)

為了防止這個情況，一個做法就是要在 insert on duplicate key update 時，遇到 duplicate key 後，要 lock 住 primary index 的 supremum pseudo-record。防止被插入新的 primary key 因而導致會影響這種不確定的結果。（我講的可能不太精確，推薦大家去看原始的解說）。

至於第二點，更頻繁的 deadlock。原因就是第一點的 primary index supremum pseudo-record lock。如果有兩個 insert on duplicate key update，同時先 insert 了第一筆資料後，都拿到 primary index supremum pseudo-record lock，要繼續 insert 第二筆資料，就會因為要等對方的 primary index supremum pseudo-record lock 釋放，所以導致 deadlock。

如何解決

對於怎麼要避開 insert on duplicated update 導致的問題，Jakub Lopuszanski 也有很多建議（在 bug report 搜尋 **[4 Sep 2020 10:11] **），例如：

改用 insert & update 取代 insert on duplicate key update ：BEGIN; SELECT rows conflicting on secondary indexes; UPDATE found conflicting rows; INSERT new rows; COMMIT;
直接把 unique column 當成 id
用 READ COMMITTED isolation level