MySQL insert on duplicate key update 死鎖

出現的場景

• 業務上面臨高併發處理，由於資料可能頻繁進行寫入與更新，因此使用的是insert on duplicate key update語句，在高併發場景下，導致死鎖報錯

原因分析

• 死鎖的本質是多個事務等待對方所持有的資源釋放，且不會釋放自身所持有的資源，導致事務之間迴圈等待
• 透過查閱檔案以及資料庫分析，在on duplicate key時，會在前一個索引值到當前值加臨界鎖，而多個事務互相等待對方的鎖釋放，是造成死鎖的原因
• lock_mode X locks gap before rec insert intention waiting

出現的場景

• 業務上面臨高併發處理，由於資料可能頻繁進行寫入與更新，因此使用的是insert on duplicate key update語句，在高併發場景下，導致死鎖報錯

原因分析

• 死鎖的本質是多個事務等待對方所持有的資源釋放，且不會釋放自身所持有的資源，導致事務之間迴圈等待
• 透過查閱檔案以及資料庫分析，在on duplicate key時，會在前一個索引值到當前值加臨界鎖，而多個事務互相等待對方的鎖釋放，是造成死鎖的原因
• lock_mode X locks gap before rec insert intention waiting

舉例說明

在以下結構中，value欄位存在唯一索引

----------------------

    id            value

----------------------

    1                1

    3                3

    5                5

----------------------

事務A

// step1 執行到此處時，由於value=3已存在，此時會對(1，3]加臨鍵鎖insert into table(value) values(3) on duplicate key update value = 7;
// step3 執行到此處時，由於3~5的區間已被鎖住，需要等待事務2釋放臨鍵鎖insert into table(value) values(4) on duplicate key update value = 7;

事務B

// step2 執行到此處時，由於value=5已存在，此時會對(3，5]加臨鍵鎖insert into table(value) values(5) on duplicate key update value = 8;
// step4 執行到此處時，由於1~3的區間已被鎖住，需要等待事務1釋放臨鍵鎖insert into table(value) values(2) on duplicate key update value = 8;

當執行順序按照step1~step4執行時，便會形成死鎖