MySQL鎖總結

Jacian發表於2021-06-30
MySql

鎖分類

當多個事務或程式訪問同一個資源時，為了保證資料的一致性就會用到鎖機制，在MySQL中鎖有多種不同的分類。

以操作粒度區分

行級鎖、表級鎖和頁級鎖

  • 表級鎖：每次操作鎖住整張表。鎖定的粒度大、開銷小、加鎖快；不會發生死鎖，但發生鎖衝突的概率極高，併發度最低，應用在InnoDB、MyISAM、BDB中；
  • 行級鎖：每次操作鎖住一行資料。鎖定的粒度小、開銷大、加鎖慢；會出現死鎖，發生鎖衝突的概率極低，併發度最高，應用在InnoDB中；
  • 頁級鎖：每次鎖定相鄰的一組記錄。鎖定粒度、開銷、加鎖時間介於行級鎖和表級鎖之間；會出現死鎖，併發度一般，應用在BDB中；
行鎖 表鎖 頁鎖
MyISAM √
BDB √ √
MyISAM √ √

以操作型別區分

讀鎖、寫鎖

  • 讀鎖（S）：共享鎖，針對同一份資料，多個讀操作可以同時進行不會互相影響；
  • 寫鎖（X）：排它鎖，當前寫操作沒有完成時，會阻塞其他讀和寫操作；

為了允許行鎖和表鎖的共存，實現多粒度的鎖機制，InnoDB還有兩種內部使用的意向鎖，這兩種意向鎖都是表鎖：

  • 意向讀鎖（IS）、意向寫鎖（IX）：屬於表級鎖，S和X主要針對行級鎖。在對錶記錄新增S或X鎖之前，會先對錶新增IS和IX鎖，表明某個事務正在持有某些行的鎖、或該事務準備去持有鎖；意向鎖存在是為了協調鎖之間的關係，支援多粒度鎖共存；

為什麼意向鎖是表級鎖？

為了減少確認次數，提升效能：如果意向鎖是行鎖，需要遍歷每一行去確認資料是否已經加鎖；如果是表鎖的話，只需要判斷一次就知道有沒有資料行被鎖定；

意向鎖是如何支援行級鎖、表級鎖共存的？

舉例

  • S鎖：事務A對記錄新增了S鎖，可以對記錄進行讀取操作，不能做修改，其它事務可以對改記錄追加S鎖，但是不能追加X鎖，追加X鎖需要等記錄的S鎖全部釋放；
  • X鎖：事務A對記錄新增了X鎖，可以對記錄進行讀和修改操作，其它事務不能對該記錄做讀和修改操作。

意向鎖、共享鎖和排它鎖之間的相容關係

事務A持有：X IX S IS
事務B獲取：X 衝突 衝突 衝突 衝突
IX 衝突 相容 衝突 相容
S 衝突 衝突 相容 相容
IS 衝突 相容 相容 相容
  • 意向鎖相互相容，因為IX和IS只是表明申請更低層次的級別元素的X、S操作；
  • 表級S和X、IX不相容，因為上了表級S鎖後，不允許其它事務再加X鎖；
  • 上了表級X鎖後，會修改資料，所以表級X鎖和 IS、IX、S、X（即使是行排他鎖，因為表級鎖定的行肯定包括行級鎖定的行，所以表級X和IX、行級X）不相容。

以操作效能區分

樂觀鎖、悲觀鎖

  • 樂觀鎖：一般採用的方式是對資料記錄版本進行對比，在資料更新提交時才會進行衝突檢測，如果發現衝突了，則提示錯誤資訊；
  • 悲觀鎖：在對一條記錄進行修改時，為了避免被其他人修改，在修改資料之前先鎖定再修改的方式。共享鎖和排它鎖是悲觀鎖的不同實現。

InnoDB的行鎖

行鎖的實現原理

意向鎖是InnoDB自動加的，不需要使用者干預；對於 UPDATE 、DELETE 和 INSERT 語句，InnoDB會自動給涉及的資料集增加排他鎖（X）；對於普通的 SELECT 語句，InnoDB不會加任何鎖；事務也可以通過以下語句顯式的給記錄集加共享鎖 SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE 和排它鎖 SELECT * FROM table_name WHERE ... FOR UPDATE 。

在InnoDB中，支援行鎖和表鎖，行鎖又分為共享鎖和排它鎖。InnoDB行鎖是通過對索引資料頁上的記錄加鎖實現的。由於InnoDB行鎖的實現特點，導致只有通過索引條件檢索並且執行計劃中真正使用到索引時InnoDB才會使用行鎖 ；並且不論使用主鍵索引、唯一索引、普通索引，InnoDB都會使用行鎖來進行加鎖，否則InnoDB將使用表鎖。由於InnoDB是針對索引加鎖，而不是針對記錄加鎖，所以即使多個事務訪問不同行的記錄，但如果使用的是相同的索引，還是會出現鎖衝突的情況，甚至出現死鎖。

行鎖的不同實現

行鎖的主要實現有三種： Record Lock 、 Gap Lock 和 Next-Key Lock 。

  • RecordLock：記錄鎖，鎖定單個行記錄的鎖，RC和RR隔離級別支援。
  • GapLock：間隙鎖，鎖定索引記錄間隙，確保索引記錄的間隙不變。範圍鎖，RR隔離級別支援。（加鎖之後間隙範圍內不允許插入資料，防止發生幻讀）
    • Insert Intention：插入意向鎖，插入意向鎖中雖然含有意向鎖三個字，但是它不屬於意向鎖，而是屬於間隙鎖，在insert時產生；意向鎖是表鎖，而插入意向鎖是行鎖。
  • Next-Key Lock：臨鍵鎖，它是記錄鎖和間隙鎖的結合體，鎖住資料的同時鎖住資料前後範圍。記錄鎖+範圍鎖，RR隔離級別支援。

insert 的加鎖流程：

執行 insert 之後，如果沒有任何衝突，在 show engine innodb status 命令中是看不到任何鎖的，這是因為 insert 加的是隱式鎖。什麼是隱式鎖？隱式鎖的意思就是沒有鎖！

所以，根本就不存在先加插入意向鎖，再加排他記錄鎖的說法，在執行 insert 語句時，什麼鎖都不會加。當其他事務執行 select ... lock in share mode 時觸發了隱式鎖的轉換。

InnoDb 在插入記錄時，是不加鎖的。如果事務 A 插入記錄且未提交，這時事務 B 嘗試對這條記錄加鎖：事務 B 會先去判斷記錄上儲存的事務 id 是否活躍，如果活躍的話，那麼就幫助事務 A 去建立一個鎖物件（排他記錄鎖），然後自身進入等待事務 A 狀態，這就是所謂的隱式鎖轉換為顯式鎖。

結論：

  1. 執行 insert 語句，判斷是否有和插入意向鎖衝突的鎖，如果有，加插入意向鎖，進入鎖等待；如果沒有，直接寫資料，不加任何鎖；
  2. 執行 select ... lock in share mode 語句，判斷記錄上是否存在活躍的事務，如果存在，則為 insert 事務建立一個排他記錄鎖，並將自己加入到鎖等待佇列；

MySQL使用間隙鎖的目的

間隙鎖的主要目的是為了防止幻讀，其主要通過兩個方面實現這個目的：

  1. 防止間隙內有新資料被插入
  2. 防止已存在的資料，更新成間隙內的資料

另外一方面，是為了滿足其恢復和複製的需要。對於基於語句的日誌格式的恢復和複製而言，由於MySQL的BINLONG是按照事務提交的先後順序記錄的，因此要正確恢復或者複製資料，就必須滿足：在一個事務未提交前，其他併發事務不能插入滿足其鎖定條件的任何記錄，根本原因還是不允許出現幻讀。

鎖規則

  1. 規則1：加鎖的基本單位是臨鍵鎖（Next-key Lock）
  2. 規則2：查詢過程中訪問的物件才會加鎖
  3. 優化1：索引上的等值查詢，給唯一鍵加索引的時候，如果查詢值存在，臨鍵鎖（Next-key Lock）會退化成記錄鎖（Record Lock）；如果查詢值不存在，會按照優化2進行優化
  4. 優化2：索引上的等值查詢，向右遍歷時且最近一個值不滿足等值條件時，臨鍵鎖（Next-key Lock）會退化成間隙鎖（Gap Lock）
  5. bug1：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

在mysql8.0.18及以上已經沒有這個bug

鎖結構

對不同記錄加鎖時，如果符合下邊這些條件：

  • 在同一個事務中進行加鎖操作
  • 被加鎖的記錄在同一個頁面中
  • 加鎖的型別是一樣的
  • 等待狀態是一樣的

那麼這些記錄的鎖就可以被放到一個鎖結構中。

鎖的相容性

事務A持有：Gap Insert Intention Record Next-Key
事務B獲取：Gap 相容 相容 相容 相容
Insert Intention 衝突 相容 相容 衝突
Record 相容 相容 衝突 衝突
Next-Key 相容 相容 衝突 衝突

從圖中可以看出，橫向為事務A擁有的鎖，豎向為事務B想要獲取的鎖；舉例： 如果前一個事務A 持有 gap 鎖 或者 next-key 鎖的時候，後一個事務B如果想要持有 Insert Intention 鎖的時候會不相容，出現鎖等待。

加鎖

  1. SELECT ... FROM ... ：InnoDB採用MVCC機制實現非阻塞讀，對於普通的 SELECT 語句，InnoDB不加鎖。
  2. SELECT ... FROM ... LOCK In SHARE MODE ：顯式追加共享鎖，InnoDB會使用臨鍵鎖（Next-key Lock）進行處理，如果發現了唯一索引，可以降級為記錄鎖（RecordLock）。
  3. SELECT ... FROM ... FOR UPDATE ：顯式追加排它鎖，InnoDB會使用Next-Key Lock鎖進行處理，如果發現唯一索引，可以降級為RecordLock鎖。
  4. UPDATE ... WHERE ：InnoDB會使用臨鍵鎖（Next-key Lock）進行處理，如果掃描發現唯一索引，可以降級為記錄鎖（RecordLock）。
  5. DELETE ... WHERE：InnoDB會使用臨鍵鎖（Next-key Lock）進行處理，如果掃描發現唯一索引，可以降級為記錄鎖（RecordLock）。
  6. insert：InnoDB會在將要插入的那一行設定一個排他的記錄鎖（RecordLock）。

以 update t1 set name=‘XX’ where id=10 操作為例：

主鍵加鎖

主鍵加鎖

加鎖行為：僅在id=10的主鍵索引記錄上加X鎖。

唯一鍵加鎖

唯一鍵加鎖

加鎖行為：先在唯一索引id上加X鎖，然後在id=10的主鍵索引記錄上加X鎖。

非唯一鍵加鎖

非唯一鍵加鎖

加鎖行為：對滿足id=10條件的記錄和主鍵分別加X鎖，然後在(6,c)-(10,b)、(10,b)-(10,d)、(10,d)(11,f)範圍分別加Gap Lock。

無索引加鎖

無索引加鎖

加鎖行為：表裡所有行和間隙都會加X鎖。（當沒有索引時，會導致全表鎖定，因為InnoDB引擎 鎖機制是基於索引實現的記錄鎖定）。

鎖模擬

檢視事務、鎖的語句：

-- 當前執行的所有事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 當前出現的鎖
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 鎖等待的對應關係
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

輸出結果解析：

-- 當前執行的所有事務 INFORMATION_SCHEMA.INNODB_TRX 表資訊
root@127.0.0.1 : information_schema 13:29:05> desc innodb_trx ;
+—————————-+———————+——+—–+———————+——-+
| Field | Type | Null | Key | Default | Extra |
+—————————-+———————+——+—–+———————+——-+
| trx_id | varchar(18) | NO | | | |#事務ID
| trx_state | varchar(13) | NO | | | |#事務狀態：
| trx_started | datetime | NO | | 0000-00-00 00:00:00 | |#事務開始時間；
| trx_requested_lock_id | varchar(81) | YES | | NULL | |#innodb_locks.lock_id
| trx_wait_started | datetime | YES | | NULL | |#事務開始等待的時間
| trx_weight | bigint(21) unsigned | NO | | 0 | |#
| trx_mysql_thread_id | bigint(21) unsigned | NO | | 0 | |#事務執行緒ID
| trx_query | varchar(1024) | YES | | NULL | |#具體SQL語句
| trx_operation_state | varchar(64) | YES | | NULL | |#事務當前操作狀態
| trx_tables_in_use | bigint(21) unsigned | NO | | 0 | |#事務中有多少個表被使用
| trx_tables_locked | bigint(21) unsigned | NO | | 0 | |#事務擁有多少個鎖
| trx_lock_structs | bigint(21) unsigned | NO | | 0 | |#
| trx_lock_memory_bytes | bigint(21) unsigned | NO | | 0 | |#事務鎖住的記憶體大小（B）
| trx_rows_locked | bigint(21) unsigned | NO | | 0 | |#事務鎖住的行數
| trx_rows_modified | bigint(21) unsigned | NO | | 0 | |#事務更改的行數
| trx_concurrency_tickets | bigint(21) unsigned | NO | | 0 | |#事務併發票數
| trx_isolation_level | varchar(16) | NO | | | |#事務隔離級別
| trx_unique_checks | int(1) | NO | | 0 | |#是否唯一性檢查
| trx_foreign_key_checks | int(1) | NO | | 0 | |#是否外來鍵檢查
| trx_last_foreign_key_error | varchar(256) | YES | | NULL | |#最後的外來鍵錯誤
| trx_adaptive_hash_latched | int(1) | NO | | 0 | |#
| trx_adaptive_hash_timeout | bigint(21) unsigned | NO | | 0 | |#
+—————————-+———————+——+—–+———————+——-+

-- 當前出現的鎖 INFORMATION_SCHEMA.INNODB_LOCKS 資訊
root@127.0.0.1 : information_schema 13:28:38> desc innodb_locks;
+————-+———————+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+————-+———————+——+—–+———+——-+
| lock_id | varchar(81) | NO | | | |#鎖ID
| lock_trx_id | varchar(18) | NO | | | |#擁有鎖的事務ID
| lock_mode | varchar(32) | NO | | | |#鎖模式
| lock_type | varchar(32) | NO | | | |#鎖型別
| lock_table | varchar(1024) | NO | | | |#被鎖的表
| lock_index | varchar(1024) | YES | | NULL | |#被鎖的索引
| lock_space | bigint(21) unsigned | YES | | NULL | |#被鎖的表空間號
| lock_page | bigint(21) unsigned | YES | | NULL | |#被鎖的頁號
| lock_rec | bigint(21) unsigned | YES | | NULL | |#被鎖的記錄號
| lock_data | varchar(8192) | YES | | NULL | |#被鎖的資料
+————-+———————+——+—–+———+——-+

--  鎖等待的對應關係 INFORMATION_SCHEMA.INNODB_LOCK_WAITS 資訊
root@127.0.0.1 : information_schema 13:28:56> desc innodb_lock_waits;
+——————-+————-+——+—–+———+——-+
| Field | Type | Null | Key | Default | Extra |
+——————-+————-+——+—–+———+——-+
| requesting_trx_id | varchar(18) | NO | | | |#請求鎖的事務ID
| requested_lock_id | varchar(81) | NO | | | |#請求鎖的鎖ID
| blocking_trx_id | varchar(18) | NO | | | |#當前擁有鎖的事務ID
| blocking_lock_id | varchar(81) | NO | | | |#當前擁有鎖的鎖ID
+——————-+————-+——+—–+———+——-+

資料準備：

-- 建表語句
CREATE TABLE `t` (
	`id` int(11) NOT NULL,
	`c` int(11) DEFAULT NULL,
	`d` int(11) DEFAULT NULL,
	PRIMARY KEY (`id`),
	KEY `c` (`c`)
) ENGINE = InnoDB CHARSET = utf8 COLLATE utf8_bin;
-- 資料準備
INSERT INTO `t`(`id`, `c`, `d`) VALUES (0, 0, 0);
INSERT INTO `t`(`id`, `c`, `d`) VALUES (5, 5, 5);
INSERT INTO `t`(`id`, `c`, `d`) VALUES (10, 10, 11);
INSERT INTO `t`(`id`, `c`, `d`) VALUES (15, 15, 15);
INSERT INTO `t`(`id`, `c`, `d`) VALUES (20, 20, 20);
INSERT INTO `t`(`id`, `c`, `d`) VALUES (25, 25, 25);

資料

鎖舉例

鎖等待超時：ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

死鎖：1213 Deadlock found when trying to get lock

等值查詢間隙鎖

Session 1 Session 2 Session 1
begin;
UPDATE t SET d = d+1 WHERE id=7;
begin;
INSERT INTO t VALUES(8,8,8); -- 阻塞
begin;
INSERT INTO t VALUES(11, 11, 11); -- 不阻塞

分析：

  1. 由於表T沒有 id=7 這條記錄，加鎖單位是 Next-key Lock ，事務1加鎖範圍是 (5, 10] ，因為 id=7 是一個等值查詢，根據優化規則， id=10 不滿足條件， Next-key Lock 退化成 Gap Lock ，因此最終加鎖範圍是 (5, 10) 。
  2. Session2 想要向這個間隙中插入 id=8 的記錄必須等待 Session1 事務提交後才可以。
  3. Session3 想要插入 id=11 ，不在加鎖範圍，所以可以插入成功。

這是如果有 Session4 想要更新 id=8 的記錄，是可以執行成功的，因為間隙鎖之間互不衝突；

非唯一鍵等值鎖

Session 1 Session 2 Session 3
begin;
SELECT id FROM t WHERE c=5 LOCK IN SHARE MODE;
begin;
INERT INTO t VALUES(7,7,7); -- 阻塞
begin;
UPDATE t SET d=d+1 WHERE id=5; -- 不阻塞

分析：

  1. Session1 給索引 c 上的 c=5 這一列加上讀鎖，根據規則1，加鎖單位為 Next-key Lock ，因此會給 (0, 5] 區間加上 Next-key Lock
  2. 因為c是普通索引，所以訪問 c=5 之後還要向右遍歷，直到 c=10 停止，根據規則2訪問到的都要加鎖，所以加鎖範圍為 (5, 10] ，根據優化2，等值查詢退化為 Gap Lock ，變為 (5, 10)，所以最終的加鎖範圍是 (0, 10)；
  3. Session2 想要插入 id=7 的記錄，要等待 Session1 提交之後才可以成功插入，因為 Session1 的間隙範圍是(5, 10)；
  4. 根據原則2，訪問到的物件才會加鎖，這個查詢使用覆蓋索引，並不需要訪問主鍵索引，所以主鍵索引上沒有加任何鎖。所以 Session3 的語句可以正常執行；

LOCK IN SHARE MODE; 只鎖覆蓋索引，FOR UPDATE; 會順便鎖上主鍵索引；

主鍵索引範圍鎖

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

對於以上兩條SQL，加鎖的範圍不一致，第一條是id=10 的行鎖，第二條是 (10, 15] 的 Next-key Lock

Session1 Session2 Session3
begin;
select * from t where id>=10 and id<11 for update;
begin;
insert into t values(9,9,9); -- 不阻塞
insert into t values(11,11,11); -- 阻塞
update t set d=d+1 where id=15; -- 阻塞

分析：

  1. Session1 根據規則1，加鎖單位為 Next-key Lock ，因為 id>=10 是範圍查詢，直到找到 id=15 停止，最終 Session1 的加鎖範圍是 (10, 15]
  2. Session3 當去 update 一個存在的值是，給該行新增 Record Lock ，由於 Record Lock 和 Next-key Lock 不相容，所以阻塞

如果 Session3 更新一個 (10, 15) 的值，則會阻塞；

非唯一索引範圍鎖

Session1 Session2 Session3
begin;
select * from t where c>=10 and c<11 for update;
begin;
insert into t values(8,8,8); -- 阻塞
begin;
update t set d=d+1 where c=15; -- 阻塞

分析：

  1. Session1 給索引c加上了 (5,10], (10,15] 兩個 Next-key Lock ；由於是範圍查詢，不觸發優化，不會退化成間隙鎖

非唯一索引等值鎖for Update

資料準備：

DROP TABLE IF EXISTS `t`;
CREATE TABLE `t1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `a` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_a` (`a`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

BEGIN;
INSERT INTO `t1` VALUES (2, 1);
INSERT INTO `t1` VALUES (3, 3);
INSERT INTO `t1` VALUES (4, 5);
INSERT INTO `t1` VALUES (5, 8);
INSERT INTO `t1` VALUES (6, 11);
COMMIT;

在表t中，a列有普通索引，所以可能鎖定的範圍有：

(-∞, 1], (1, 3], (3, 5], (5, 8], (8, 11], (11, +∞)
session1 session2
begin;
select * from t1 where a=8 for update;
begin;
insert into t1 (a) values (12); -- 不會阻塞
insert into t1 (a) values (11); -- 不會阻塞
insert into t1 (a) values (4); -- 不會阻塞
insert into t1 (a) values (5); -- 阻塞

Session1 執行完成之後預期加鎖範圍為 (5, 8] 和 (8, 11]，由於鎖優化策略，退化成間隙鎖，範圍變成 (5, 8] 和 (8, 11) ，也就是 (5, 11) ，插入12和4不會阻塞很好理解。但是 5不在鎖的範圍內，還是被鎖上了

是因為如果索引值相同的話，會根據id進行排序加鎖，所以最終的加鎖範圍是索引a的 (5, 4) 到 (11, 6) 的範圍

鎖範圍

死鎖模擬

死鎖模擬-場景1

AB BA操作問題

資料準備：

CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `stu_num` int(11) DEFAULT NULL,
  `score` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `idx_uniq_stu_num` (`stu_num`),
  KEY `idx_score` (`score`)
) ENGINE=InnoDB;

insert into t2(id, stu_num, score) values (1, 11, 111);
insert into t2(id, stu_num, score) values (2, 22, 222);
insert into t2(id, stu_num, score) values (3, 33, 333);
Session1 Session2
begin;
select * from t2 where id = 1 for update;
begin;
select * from t2 where id = 2 for update;
select * from t2 where id = 2 for update;
select * from t2 where id = 1 for update; -- Deadlock

死鎖模擬-場景2

S-lock 升級 X-lock

資料準備：

沿用簡單場景1資料

Session1 Session2
begin;
SELECT * FROM t2 WHERE id = 1 LOCK IN SHARE MODE;
begin;
DELETE FROM t2 WHERE id = 1;
DELETE FROM t2 WHERE id = 1;

分析：

  1. Session1 獲取到 S-Lock
  2. Session2 嘗試獲取到 X-Lock ，但是被 Session1 的S-Lock 阻塞
  3. Session1 想要獲取到 X-Lock，本身擁有一個 S-Lock ，但是Session2 申請 X-Lock 在前，需要等待 Session2 釋放之後才能提升到 X-Lock，兩個事務造成資源爭搶導致死鎖

死鎖模擬-場景3

資料準備：

create table t3(
  id int not null primary key auto_increment,
  a int not null ,
  unique key ua(a)
) engine=innodb;
insert into t3(id,a) values(1,1),(5,4),(20,20),(25,12);
Session 1 Session 2
insert into t3(id,a) values(26,10);
insert into t3(id,a) values(30,10);
insert into t3(id,a) values(40,9);

分析：

事務一在插入時由於跟事務二插入的記錄唯一鍵衝突，所以對 a=10 這個唯一索引加 S 鎖（Next-key）並處於鎖等待，事務二再插入 a=9 這條記錄，需要獲取插入意向鎖（lock_mode X locks gap before rec insert intention）和事務一持有的 Next-key 鎖衝突，從而導致死鎖。

死鎖模擬-場景4

事務1 事務2
begin; begin;
UPDATE t SET d = d+1 WHERE id=7;
UPDATE t SET d = d+1 WHERE id=9;
INSERT INTO t VALUES(8,8,8);
INSERT INTO t VALUES(8,8,8);

兩條語句的執行計劃：

語句執行計劃

事務1執行 INSERT 時的鎖情況：

鎖情況

死鎖日誌：

------------------------
LATEST DETECTED DEADLOCK
------------------------
2021-05-28 00:07:00 0x70000a09a000
*** (1) TRANSACTION:
TRANSACTION 1518, ACTIVE 17 sec inserting
mysql tables in use 1, locked 1
LOCK WAIT 3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 24, OS thread handle 123145470435328, query id 2673 localhost root update
INSERT INTO t VALUES(8,8,8)
*** (1) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `lock_demo`.`t` trx id 1518 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000005d0; asc       ;;
 2: len 7; hex 2c0000014a01ca; asc ,   J  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000b; asc     ;;

*** (2) TRANSACTION:
TRANSACTION 1519, ACTIVE 12 sec inserting
mysql tables in use 1, locked 1
3 lock struct(s), heap size 1136, 2 row lock(s)
MySQL thread id 25, OS thread handle 123145470713856, query id 2674 localhost root update
INSERT INTO t VALUES(8,8,8)
*** (2) HOLDS THE LOCK(S):
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `lock_demo`.`t` trx id 1519 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000005d0; asc       ;;
 2: len 7; hex 2c0000014a01ca; asc ,   J  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000b; asc     ;;

*** (2) WAITING FOR THIS LOCK TO BE GRANTED:
RECORD LOCKS space id 27 page no 3 n bits 80 index PRIMARY of table `lock_demo`.`t` trx id 1519 lock_mode X locks gap before rec insert intention waiting
Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0
 0: len 4; hex 8000000a; asc     ;;
 1: len 6; hex 0000000005d0; asc       ;;
 2: len 7; hex 2c0000014a01ca; asc ,   J  ;;
 3: len 4; hex 8000000a; asc     ;;
 4: len 4; hex 8000000b; asc     ;;

*** WE ROLL BACK TRANSACTION (2)
  1. UPDATE 的 WHERE 子句沒有滿足條件的記錄，而對於不存在的記錄 並且在RR級別下，UPDATE 加鎖型別為間隙鎖（Gap Lock），間隙鎖（Gap Lock）之間是相容的，所以兩個事務都能成功執行 UPDATE；這裡的gap範圍是索引id列 (5, 10) 的範圍。
  2. INSERT 時，其加鎖過程為先在插入間隙上獲取插入意向鎖，插入資料後再獲取插入行上的排它鎖。又插入意向鎖與間隙鎖（Gap Lock）和臨鍵鎖（Next-key Lock）衝突，即一個事務想要獲取插入意向鎖，如果有其他事務已經加了（Gap Lock）或臨鍵鎖（Next-key Lock），則會阻塞。
  3. 場景中兩個事務都持有間隙鎖（Gap Lock），然後又申請插入意向鎖，此時都被阻塞，迴圈等待造成死鎖。

  • 記錄鎖（LOCK_REC_NOT_GAP）: lock_mode X locks rec but not gap

  • 間隙鎖（LOCK_GAP）: lock_mode X locks gap before rec

  • Next-key 鎖（LOCK_ORNIDARY）: lock_mode X

  • 插入意向鎖（LOCK_INSERT_INTENTION）: lock_mode X locks gap before rec insert intention

並不是在日誌裡看到 lock_mode X 就認為這是 Next-key 鎖，因為還有一個例外：如果在 supremum record 上加鎖，locks gap before rec 會省略掉，間隙鎖會顯示成 lock_mode X，插入意向鎖會顯示成 lock_mode X insert intention。

INSERT 語句，會嘗試獲取 lock mode S waiting 鎖，這是為了檢測唯一鍵是否重複，必須進行一次當前讀，要加 S 鎖。

INSERT 加鎖分幾個階段：先檢查唯一鍵約束，加 S 鎖，再加插入意向鎖，最後插入成功時升級為 X 鎖。

-- 檢視當前事務資訊：
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
-- 檢視當前鎖定的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;
-- 檢視當前等待鎖的事務
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS;

站在巨人的肩膀上

向大佬低頭

參考連結：

MySQL官方團隊：https://mysqlserverteam.com/innodb-data-locking-part-3-deadlocks/

MySQL鎖官方文件：https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

死鎖排查分析：https://www.aneasystone.com/archives/2018/04/solving-dead-locks-four.html

死鎖場景羅列：https://github.com/aneasystone/mysql-deadlocks

插入意向鎖和Next-Key引起的死鎖：https://segmentfault.com/a/1190000019745324

透過原始碼分析INSERT加鎖流程：https://www.aneasystone.com/archives/2018/06/insert-locks-via-mysql-source-code.html

相關文章