MySQL并發Replace into導致死鎖場景簡析

在之前的文章 #issue 68021 MySQL unique check 問題中, 我們已經介紹了在 MySQL 里面, 由于唯一鍵的檢查(unique check), 導致 MySQL 在 Read Commit 隔離級別也需要添加 GAP lock, 導致有些比較奇怪情況下有一些鎖等待.

另外一類問題是由于唯一鍵檢查導致的死鎖問題, 這類問題也非常多, 也是我們處理線上經常收到用戶反饋的問題, 這里我們就分析幾個這樣死鎖的 Case.

Replace into 操作是非常常用的操作, 很多時候在插入數據的時候, 不確定表中是否已經存在數據, 有沒有唯一性的沖突, 所以會使用 replace into 或者 insert .. on duplicate update 這樣的操作, 如果沖突就把對應的行給自動更新.

但是這樣的操作在并發場景, 當存在唯一鍵的時候容易有死鎖問題場景, 那么為什么會這樣, 我們來看一個簡單的 case:

通過GDB 和腳本可以復現以下死鎖場景.

create table t(a int AUTO_INCREMENT, b int, PRIMARY KEY (a), UNIQUE KEY (b));

insert into t(a, b) values (100, 8);

session1:
replace into t(a, b) values (10, 8);

session2:
replace into t(a, b) values (11, 8);
(40001): Deadlock found when trying to get lock; try restarting transaction

當然也可以通過這個腳本, 不需要 GDB 就可以隨機復現:

#! /bin/bash

MYSQL="mysql -h127.0.0.1 -P2255 -uroot test"

$MYSQL -e "create table t(a int AUTO_INCREMENT, b int, PRIMARY KEY (a), UNIQUE KEY (b))"

while true
do

  $MYSQL -e "replace into t(b) values (8)" &
  $MYSQL -e "replace into t(b) values (8)" &
  $MYSQL -e "replace into t(b) values (8)" &

  wait;
done

這里在并發session1 和 session2 插入的時候, 就容易出現 Deadlock Lock 的問題, 類似用戶并發插入數據的場景.

上面的死鎖信息 Trx HOLDS THE LOCK 和 WAITING FOR THIS LOCK TO BE GRANTED 是一個錯誤的誤導信息, 官方版本在新的版本中已經修復, 這里 HOLDS THE LOCK 是不對的, 其實還未持有 X lock.

這里看到 Trx 1 waiting 在 8, 100 next-key X lock 上.

然后 Trx2 持有 8, 100 next-key X lock, 但是 WAITING FOR 8, 100 insert_intention lock.

那么為什么會有死鎖呢?

我們先看一下單個 replace into 的流程

整體而言, 如果replace into 第1遍insert 操作的時候, 遇到unique index 沖牧, 那么需要重新執行update 操作或者delete + 重新insert 操作, 但是第1遍insert 操作失敗添加的事務鎖并不會釋放, 而是等到整個事務提交才會釋放, 原因當然是現在MySQL 2Phase Lock 機制要做的保證

replace into 大概代碼如下:

所有replace into/on duplicate key update 這里execute_inner 執行的是Sql_cmd_insert_values => execute_inner() 方法

這里replace into/on duplicate key update 執行在這個循環里面  if (duplicate_handling == DUP_REPLACE || duplicate_handling == DUP_UPDATE) {
    DBUG_ASSERT(duplicate_handling != DUP_UPDATE || update != NULL);    while ((error = table->file->ha_write_row(table->record[0]))) {        // ...
      if (duplicate_handling == DUP_UPDATE) {

    這里 branch 就是處理 on duplicate key update 的duplicate key 場景
    判斷如果是 on duplicate key update 邏輯, 那么遇到error 以后, 就是用 table->file->ha_update_row 通過 update 進行更新
          } else /* DUP_REPLACE */ {
    duplicate_handling == DUP_REPLACE 就是處理 replace into 錯誤場景
    在replace into場景中, 如果插入的key 遇到沖突的, 是如何處理的, 其實是分2種場景的:
    如果是 replace into 邏輯, 遇到 error 以后, 如果是沖突的是最后一個 unique index, 并且沒有外鍵約束, 并且沒有delete trigger 的時候, 那么和 on duplicate key update 一樣, 使用 ha_update_row 通過 update 進行更新

    否則通過 delete + 重新 insert 來進行更新, 操作更多, 消耗也就更多.

    具體代碼:
    如果ha_write_row() 失敗, 那么會執行delete_row() 操作, 等這個操作執行完成以后, 又跳到這個while 循環進行重新insert    if ((error = table->file->ha_delete_row(table->record[1]))) goto err;    /* Let us attempt do write_row() once more */
    }

接下來是2個replace into 操作的時候, 如果Thread 1 停在replace into 第一個階段, 也就是insert 遇到unique index 沖突, 此時持有8, 100 next-key lock.

這個時候第2個Thread 2也進行replace into 操作, 在進行唯一鍵沖突檢測, 執行row_ins_scan_sec_index_for_duplicate() 的時候需要申請8, 100 next-key lock. 該lock 被thread 1持有, 那么只能進行等待.

接下來Thread 1 繼續執行, 執行update 操作, 在InnoDB 里面, 對于二級索引而言需要執行delete, 然后再insert 操作, 在insert 的時候需要持有8, 100 insert intention lock. 目前 InnoDB insert intention lock 判斷是否沖突的時候, 對應的 record 不論是有事務等待或者已經持有 next-key lock, 都算沖突. 此時Thread 已經等在8, 100 next-key lock 上, 那么 Thread 1 就無法獲得 insert intention lock, 只能進行等待.

這里有一個問題: 為什么申請insert_intention 的時候, 如果有其他事務提前等待在這個 lock 的 next-key lock 上面, 那么這個 insert_intention 會申請失敗?



在函數rec_lock_check_conflict() 解釋了這個問題, 因為如果申請 intention lock 成功, 那么接下來的 insert 操作也就會成功, 那么原來等待這個 record 上面的trx 就變成需要等待 2 個 record 了.

比如如果之前 trx2 wait 在(4, 10] 這個 next-key lock 上, 如果允許 trx1 插入了 7,這個 record, 那么根據鎖繼承機制, 7 會繼承 10 這個 record 上面的 next-key lock, 那么 trx2 就變成 wait 在兩個 record 上, 也就變成 2 個 waiting lock 了, 那么現有這套鎖等待喚醒機制就也要改了, 現在這套鎖等待喚醒機制因此一個 trx 只會等待一個 lock, 在一個 lock 釋放以后, 相應等待在這個 Lock 上面的 trx 就可以喚醒了.

因此為了規避這樣的問題, MySQL InnoDB 里面如果申請 insert_intention lock 的時候, 如果有其他事務提前等待在這個 lock 的 next-key lock 上, 那么 insert_intention lock 是無法申請成功的.

那么現在的就過就是 Thread 2 等待 Thread 1 next-key lock 釋放, Thread 1 等待 Thread 2 next-key lock 獲得并釋放, 出現了 Thread1 <=> Thread2 互相等待的情況 因此出現的死鎖.

審核編輯：劉清