MySQL并發(fā)Replace into導(dǎo)致死鎖場景簡析

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

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

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

但是這樣的操作在并發(fā)場景, 當(dāng)存在唯一鍵的時(shí)候容易有死鎖問題場景, 那么為什么會(huì)這樣, 我們來看一個(gè)簡單的 case:

通過GDB 和腳本可以復(fù)現(xiàn)以下死鎖場景.

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

當(dāng)然也可以通過這個(gè)腳本, 不需要 GDB 就可以隨機(jī)復(fù)現(xiàn):

#! /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

這里在并發(fā)session1 和 session2 插入的時(shí)候, 就容易出現(xiàn) Deadlock Lock 的問題, 類似用戶并發(fā)插入數(shù)據(jù)的場景.

上面的死鎖信息 Trx HOLDS THE LOCK 和 WAITING FOR THIS LOCK TO BE GRANTED 是一個(gè)錯(cuò)誤的誤導(dǎo)信息, 官方版本在新的版本中已經(jīng)修復(fù), 這里 HOLDS THE LOCK 是不對的, 其實(shí)還未持有 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.

那么為什么會(huì)有死鎖呢?

我們先看一下單個(gè) replace into 的流程

整體而言, 如果replace into 第1遍insert 操作的時(shí)候, 遇到unique index 沖牧, 那么需要重新執(zhí)行update 操作或者delete + 重新insert 操作, 但是第1遍insert 操作失敗添加的事務(wù)鎖并不會(huì)釋放, 而是等到整個(gè)事務(wù)提交才會(huì)釋放, 原因當(dāng)然是現(xiàn)在MySQL 2Phase Lock 機(jī)制要做的保證

replace into 大概代碼如下:

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

這里replace into/on duplicate key update 執(zhí)行在這個(gè)循環(huán)里面  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 進(jìn)行更新
          } else /* DUP_REPLACE */ {
    duplicate_handling == DUP_REPLACE 就是處理 replace into 錯(cuò)誤場景
    在replace into場景中, 如果插入的key 遇到?jīng)_突的, 是如何處理的, 其實(shí)是分2種場景的:
    如果是 replace into 邏輯, 遇到 error 以后, 如果是沖突的是最后一個(gè) unique index, 并且沒有外鍵約束, 并且沒有delete trigger 的時(shí)候, 那么和 on duplicate key update 一樣, 使用 ha_update_row 通過 update 進(jìn)行更新

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

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

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

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

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

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



在函數(shù)rec_lock_check_conflict() 解釋了這個(gè)問題, 因?yàn)槿绻暾?intention lock 成功, 那么接下來的 insert 操作也就會(huì)成功, 那么原來等待這個(gè) record 上面的trx 就變成需要等待 2 個(gè) record 了.

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

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

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

審核編輯：劉清