對比上一篇，這篇聊聊【讀已提交】隔離級別下，唯一索引衝突怎麼加鎖。

作者：操盛春，愛可生技術專家，公眾號『一樹一溪』作者，專注於研究 MySQL 和 OceanBase 源碼。

愛可生開源社區出品，原創內容未經授權不得隨意使用，轉載請聯繫小編並註明來源。

本文基於 MySQL 8.0.32 源碼，存儲引擎為 InnoDB。

目錄
[TOC]

正文

1. 準備工作

創建測試表：

CREATE TABLE `t4` (
  `id` int unsigned NOT NULL AUTO_INCREMENT,
  `i1` int DEFAULT '0',
  `i2` int DEFAULT '0',
  PRIMARY KEY (`id`) USING BTREE,
  UNIQUE KEY `uniq_i1` (`i1`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb3;

插入測試數據：

INSERT INTO `t4` (`id`, `i1`, `i2`) VALUES
(1, 11, 21), (2, 12, 22), (3, 13, 23),
(4, 14, 24), (5, 15, 25), (6, 16, 26);

把事務隔離級別設置為 READ-COMMITTED（如已設置，忽略此步驟）：

SET transaction_isolation = 'READ-COMMITTED';

-- 確認設置成功
SHOW VARIABLES like 'transaction_isolation';
+-----------------------+----------------+
| Variable_name         | Value          |
+-----------------------+----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+----------------+

2. 加鎖情況

t4 表除了有主鍵索引，i1 字段上還有個唯一索引 uniq_i1，有一條 <i1 = 12> 的記錄。

我們執行以下 insert 語句，再插入一條 <i1 = 12> 的記錄。

begin;
insert into t4(i1, i2) values (12, 2000);

因為新插入記錄和表中原有記錄存在唯一索引衝突，報錯如下：

(1062, "Duplicate entry '12' for key 't4.uniq_i1'")

執行以下 select 語句查詢加鎖情況：

select
   engine_transaction_id, object_name, index_name,
   lock_type, lock_mode, lock_status, lock_data
 from performance_schema.data_locks
 where object_name = 't4'
 and lock_type = 'RECORD'\G
 
***************************[ 1. row ]***************************
engine_transaction_id | 247925
object_name           | t4
index_name            | uniq_i1
lock_type             | RECORD
lock_mode             | S
lock_status           | GRANTED
lock_data             | 12, 2

lock_data = 12,2，lock_mode = S 表示對唯一索引 uniq_i1 中 <i1 = 12, id = 2> 的記錄加了共享 Next-Key 鎖。

和可重複讀隔離級別不一樣，讀已提交隔離級別沒有對 supremum 記錄加排他 Next-Key 鎖。

3. 原理分析

示例 SQL 中，我們插入了一條 <i1 = 12, i2 = 2000> 的記錄，沒有指定 id 字段值。

MySQL 會自動生成 id 字段值，根據表中數據可以推導出，新插入記錄的 id 字段值為 7。

那麼，我們插入的完整記錄為 <id = 7, i1 = 12, i2 = 2000>，插入到唯一索引 uniq_i1 中的記錄為 <i1 = 12, id = 7>。

找到插入記錄的目標位置是 <i1 = 12, id = 2> 這條記錄之後，此時，InnoDB 也就發現了表中已經存在 <i1 = 12> 的記錄。

因為 i1 字段上有唯一索引，自然不允許再插入一條 <i1 = 12> 的記錄了。

和可重複讀隔離級別一樣，InnoDB 發現表中已經存在 <i1 = 12> 的記錄之後，並不會直接報 Duplicate entry xxx 錯誤，也需要進一步檢查。

首先，會檢查要插入到唯一索引中的記錄，是否有哪個字段值為 NULL。

因為對於用户普通表，NULL 值和 NULL 值被認為不相等。

如果要插入的記錄中存在值為 NULL 的字段，雖然從存儲內容上來説，發現了同樣的記錄，但是也會被認為是不同的記錄。這種情況下，新記錄可以繼續插入到唯一索引中。

也就是説，對於唯一索引 uniq_i1，可以插入任意條 <i1 = NULL> 的記錄。

對於示例 SQL，因為 i1 字段值為 12，從這項檢查來看，和表中 <i1 = 12, id = 2> 的記錄衝突。

但是，InnoDB 還要再做最後一次嘗試，看看錶中 <i1 = 12, id = 2> 的記錄是否已經被標記刪除，只是還沒有被清理。

如果表中 <i1 = 12, id = 2> 的記錄已經被標記刪除，新記錄就可以繼續插入到唯一索引 uniq_i1 中，否則，新記錄不能插入，需要報錯。

為了防止其它事務更新或者刪除這條記錄、或者往這條記錄前面的間隙裏插入記錄，開始進一步檢查之前，InnoDB 會對這條記錄加共享 Next-Key 鎖。

這就是示例 SQL 執行過程中對 <i1 = 12, id = 2> 的記錄加共享 Next-Key 鎖的原因。

到這裏就結束了嗎？

當然不能就這麼結束。

雖然讀已提交隔離級別下，沒有對主鍵索引中的 supremum 記錄加鎖，但是我們也不能把主鍵索引忘了。

insert 語句插入記錄時，會先插入記錄到主鍵索引，再插入記錄到二級索引。

InnoDB 插入記錄到唯一索引 uniq_i1 中發現存在衝突，也就不能繼續插入了，但是，主鍵索引中已經插入記錄成功，要怎麼辦呢？

那必須要把主鍵索引恢復原樣，也就是要刪除剛剛插入到主鍵索引的記錄。

刪除記錄時，InnoDB 發現這條記錄沒有被顯式加鎖，並且記錄的 DB_TRX_ID 字段值對應的事務還沒有提交，説明這條記錄上存在隱式鎖。

因為要刪除這條記錄，為了防止其它事務讀寫這條記錄，InnoDB 會把記錄上的隱式鎖轉換為顯式鎖。

當 InnoDB 準備開始轉換時，發現當前事務的隔離級別為讀已提交，後面的轉換步驟就不再進行了，轉換操作就此終止。

剛剛插入到主鍵索引的記錄上，隱式鎖沒有被轉換為顯式鎖，刪除這條記錄時，它的下一條記錄（supremum 記錄）也就不需要繼承這條記錄上的鎖了。

所以，和可重複讀隔離級別不一樣，讀已提交隔離級別沒有對 supremum 記錄加排他 Next-Key 鎖。

4. 總結

沒有需要總結的內容。