超全面 MySQL 語句加鎖分析（下篇）

zhangdeTalk發表於2020-02-16
MySql
系列文章：
超全面 MySQL 語句加鎖分析（上篇） 超全面 MySQL 語句加鎖分析（中篇） 超全面 MySQL 語句加鎖分析（下篇）

INSERT語句

前邊嘮叨鎖的細節時說過，INSERT語句一般情況下不加鎖，不過當前事務在插入一條記錄前需要先定位到該記錄在B+樹中的位置，如果該位置的下一條記錄已經被加了gap鎖（next-key鎖也包含gap鎖，之後就不強調了），那麼當前事務會在該記錄上加上一種型別為插入意向鎖的鎖，並且事務進入等待狀態。關於插入意向鎖由於我們之前已經詳細嘮叨過了，就不多說了。

下邊要看的是兩種INSERT語句遇到的特殊情況：

遇到重複鍵（duplicate key）

在插入一條新記錄時，首先要做的事情其實是定位到這條新記錄應該插入到B+樹的哪個位置。如果定位位置時發現了有已存在記錄的主鍵或者唯一二級索引列與待插入記錄的主鍵或者唯一二級索引列相同（不過可以有多條記錄的唯一二級索引列的值同時為NULL），那麼此時此時是會報錯的。比方說我們插入新記錄，該記錄的主鍵值已經被包含在hero表中了：

mysql> BEGIN;Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO hero VALUES(20, 'g關羽', '蜀');ERROR 1062 (23000): Duplicate entry '20' for key 'PRIMARY'

當然，在生成報錯資訊前，其實還需要做一件非常重要的事情 —— 對聚簇索引中number值為20的那條記錄加S鎖。不過具體的行鎖型別在不同隔離級別下是不一樣的：

  • 在READ UNCOMMITTED/READ COMMITTED隔離級別下，加的是S型正經記錄鎖。

  • 在REPEATABLE READ/SERIALIZABLE隔離級別下，加的是S型next-key鎖。

如果是唯一二級索引列值重複，比方說我們再把普通二級索引idx_name改為唯一二級索引uk_name：

ALTER TABLE hero DROP INDEX idx_name, ADD UNIQUE KEY uk_name (name);

然後執行

mysql> BEGIN;Query OK, 0 rows affected (0.01 sec)mysql> INSERT INTO hero VALUES(30, 'c曹操', '魏');ERROR 1062 (23000): Duplicate entry 'c曹操' for key 'uk_name'

很顯然，hero表中之前就包含name值為'c曹操'的記錄，如果再插入一條name值為'c曹操'的新記錄時，雖然插入對應的聚簇索引記錄沒問題，但是在插入uk_name唯一二級索引記錄時便會報錯，不過在報錯之前還是會把name值為'c曹操'那條二級索引記錄加一個S鎖。需要注意的是，不管是哪個隔離級別，針對在插入新記錄時遇到重複的唯一二級索引列的情況，會對已經在B+樹中的唯一二級索引記錄加next-key鎖。

小貼士： 按理說在READ UNCOMMITTED/READ COMMITTED隔離級別下，不應該出現next-key鎖，這主要是考慮到如果只加正經記錄鎖的話，在一些情況下可能出現有多條記錄的唯一二級索引列都相同的情況。當然，出現這種情況的原因比較複雜，我們這裡就不多說了。

另外，如果我們使用的是INSERT ... ON DUPLICATE KEY ...這樣的語法來插入記錄時，如果遇到主鍵或者唯一二級索引列值重複的情況，會對B+樹中已存在的相同鍵值的記錄加X鎖，而不是S鎖。

外來鍵檢查

大家別忘了MySQL還是一個支援外來鍵的資料庫，比方說我們再為三國英雄的戰馬建一個表：

CREATE TABLE horse (    number INT PRIMARY KEY,    horse_name VARCHAR(100),    FOREIGN KEY (number) REFERENCES hero(number))Engine=InnoDB CHARSET=utf8;

這樣hero表就算是一個父表，新建的horse表就算一個子表，其中horse表的number列是參照hero表的number列。現在如果我們向子表中插入一條記錄時：

  • 如果待插入記錄的number值在hero表中能找到。

    比方說我們為horse表中新插入的記錄的number值為8，而在hero表中number值為8的記錄代表曹操，他的馬是絕影：

    mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO horse VALUES(8, '絕影');Query OK, 1 row affected (5 min 58.04 sec)

    在插入成功之前，不論當前事務的隔離級別是什麼，只需要直接給父表hero的number值為3的記錄加一個S型正經記錄鎖。

  • 如果待插入記錄的number值在hero表中找不到。

    比方說我們為horse表中新插入的記錄的number值為2，而在hero表中不存在number值為2的記錄：

    mysql> BEGIN;Query OK, 0 rows affected (0.00 sec)mysql> INSERT INTO horse VALUES(5, '絕影');Query OK, 1 row affected (5 min 58.04 sec)

    雖然插入失敗了，但是這個過程中需要對父表hero的某些記錄進行加鎖：

  • 在READ UNCOMMITTED/READ COMMITTED隔離級別下，並不對記錄加鎖。

  • 在REPEATABLE READ/SERIALIZABLE隔離級別下，加的是gap鎖。

小結

MySQL的語句加鎖分析暫時告一段落，但並沒有把所有的情形都列舉出來，只是給出了一個大致輪廓，希望各位在之後的工作學習中再多總結學些，如有疑問，可以聯絡小孩子哈～ 關注小青蛙，全都是技術乾貨哈：

文章系轉載
文章來源：我們都是小青蛙（微信公眾號）

本作品採用《CC 協議》，轉載必須註明作者和本文連結

阿德

相關文章