首頁 > 軟體

高並行狀態下Replace Into造成的死鎖問題解決

2023-01-20 14:02:51

目錄

1.問題出現：
2.分析解決
3.解決方案：

1.問題出現：

在測試階段，巨量資料並行的情況下，發現sql語句造成表的死鎖，過一段時間，死鎖消失。於是進行排查

報錯如下：

對應的sql語句如下：

    @Insert("replace into ${tableName}( windcode,date, n" +
            "      code, high, open, low, n" +
            "      `close`, volume, turnover,gtm_modify) "
            + "values (#{obj.windcode},#{obj.date},#{obj.code},#{obj.high},#{obj.open},#{obj.low},#{obj.close},#{obj.volume},#{obj.turnover},#{obj.updateTime})" )
    int insertOne(@Param("obj") KDTO obj, @Param("tableName") String tableName);

在排除了資料問題和執行緒重複呼叫以後，我們關注了一下sql語句本身。看了網上很多經驗分享，覺得問題可能出現在 Replace Into 語句上。

2.分析解決

首先我們分析一下為什麼並行replace into導致MySQL死鎖

Replace into 一般作用是，當存在衝突時，會把舊記錄替換成新的記錄。也就是說這條語句執行，分為了兩個大步：判斷和執行

1.判斷：

首先判斷我們需要操作的記錄是否存在（根據主鍵或者唯一索引判斷）

2.操作：

針對不存在的記錄，語句會執行insert，插入操作。
針對已經存在的記錄，語句可以拆分為delete+insert操作

測試：

建立表

插入資料：

我們使用replace into語句去執行一個已經存在的資料：

可以清楚的發現，影響的行數是兩行

第一行的資料被修改了

我們使用replace into語句去執行一個不存在的資料：

可以清楚的發現，影響的行數是一行

執行了插入操作：

邏輯非常的清晰，但是這種單條sql語句在什麼情況下會出現死鎖呢？我們就要去考慮這個加鎖的時機。

正常的插入邏輯是：

首先插入聚集索引記錄，在上例中id列為自增列。
隨後插入二級索引num，由於其是唯一索引，在檢查duplicate key時，為其加上型別為LOCK_X的記錄鎖。

發現錯誤：

由於檢測到duplicate key，因此第一步插入的聚集索引記錄需要被回滾掉（row_undo_ins）。
從InnoDB層失敗返回到Server層後，收到duplicate key錯誤，首先檢索唯一鍵衝突的索引，並對衝突的索引記錄（及聚集索引記錄）加鎖。

轉換模式：

如果發生uk衝突的索引是最後一個唯一索引、沒有外來鍵參照、且不存在delete trigger時，使用UPDATE ROW的方式來解決衝突；
否則，使用DELETE ROW + INSERT ROW的方式解決衝突。

更新記錄：

對於聚集索引，由於PK列發生變化，採用delete + insert 聚集索引記錄的方式更新。
對於二級uk索引，同樣採用標記刪除 + 插入的方式。

所以死鎖的問題多半就會出現在X記錄鎖上面。

死鎖分析：

所以再多執行緒高並行的環境狀態下，存在兩個事務同時去獲取一個記錄的修改的情況：

事務1拿到X記錄鎖，
事務2檢測到衝突，獲取X|NK鎖，被事務1阻塞
事務1檢測到衝突，申請獲取S|NK，被事務2阻塞

事務1	事務2
LOCK_X LOCK_NOT_GAP	-
-	LOCK_X-LOCK_NEXT_KEY 阻塞
LOCK_S-LOCK_NEXT_KEY	死鎖回滾

所以在等待執行期間sql會有死鎖報錯，高並行環境下的死鎖也就出現了，再事務執行完成回滾操作以後，死鎖回滾，也就解釋了死鎖消失的問題。

3.解決方案：

經過多方討論，最終決定使用 insetr + ON DUPLICATE KEY UPDATE語句替換高並行環境下的Replace Into語句解決死鎖問題。

ON DUPLICATE KEY UPDATE語句的作用是：

若該資料的主鍵值/ UNIQUE KEY 已經在表中存在,則執行更新操作, 即UPDATE 後面的操作。

否則插入一條新的記錄。

實現了Replace Into有相同的查重替換功能，而避免了高並行的死鎖問題。

但是UPDATE操作效能相比DELETE操作會有一定的效能上的影響，需要後續測試跟進。

到此這篇關於高並行狀態下Replace Into造成的死鎖問題解決的文章就介紹到這了,更多相關Replace Into死鎖內容請搜尋it145.com以前的文章或繼續瀏覽下面的相關文章希望大家以後多多支援it145.com！

IT145.com E-mail:sddin#qq.com