InnoDB RR隔離等級下INSERT SELECT兩種死結案例剖析，innodb案例剖析

InnoDB RR隔離等級下INSERT SELECT兩種死結案例剖析，innodb案例剖析

作者：高鵬（重慶八怪）

校稿：葉師傅（部分內容有微調）

原文：http://blog.itpub.net/7728585/viewspace-2146183/

有網友遇到了在RR隔離等級下insert A select B where B.COL=** 發生死結的問題。分析死結日誌後，筆者類比重現了2種可能引發死結的情境，本文中將進行詳細的描述。

幾個約定

• 本文使用版本Percona 5.7.14修改版，能夠列印出事務所有的行鎖資訊結構鏈(不包含隱含鎖)；

• 本文中的測試均在RR隔離等級下完成的，RC不存在這樣的問題；

• 筆者對源碼的理解有限，如有錯誤請指正；

• 本文使用了自製工具 innblock 和 bcview，前者用於掃描塊結構，後者用於更加方便的查看二進位檔案資訊。兩個工具：

• innblock，http://pan.baidu.com/s/1qYnyVWo

• bcview，http://pan.baidu.com/s/1num76RJ

感謝葉金榮老師對本文的審核，筆者也曾是一名知數堂的學生

一、基本概念

在開始本文之前我打算介紹一下一些基本概念，特別是鎖模型和相容矩陣會對本文的閱讀有相當大的協助。

1、 innodb lock模型

• [LOCK_ORDINARY[next_key_lock]:] 源碼定義:

  #define LOCK_ORDINARY    0/*!< this flag denotes an ordinarynext-key lock in contrast to LOCK_GAPor LOCK_REC_NOT_GAP */

  預設是LOCK_ORDINARY，即next-keylock，鎖住行及其前面的間隙。

  
  

• [LOCK_GAP:] 源碼定義:

  #define LOCK_GAP    512/*!< when this bit is set, it means that thelock holds only on the gap before the record;for instance, an x-lock on the gap does notgive permission to modify the record on whichthe bit is set; locks of this type are createdwhen records are removed from the index chain

  間隙鎖，鎖住行以前的間隙，不鎖住本行。

  
  

• [LOCK_REC_NOT_GAP:] 源碼定義:

  #define LOCK_REC_NOT_GAP 1024/*!< this bit means that the lock is only onthe index record and does NOT block insertsto the gap before the index record; this isused in the case when we retrieve a recordwith a unique key, and is also used inlocking plain SELECTs (not part of UPDATEor DELETE) when the user has set the READCOMMITTED isolation level */

  行鎖，鎖住行而不鎖住任何間隙。

  
  

• [LOCK_INSERT_INTENTION:] 源碼定義：

  #define LOCK_INSERT_INTENTION 2048/*!< this bit is set when we place a waitinggap type record lock request in order to letan insert of an index record to wait untilthere are no conflicting locks by othertransactions on the gap; note that this flagremains set when the waiting lock is granted,or if the lock is inherited record */

  插入意圖鎖定，如果插入的記錄在某個已經鎖定的間隙內為這個鎖。

2、 innodb lock相容矩陣

/* LOCK COMPATIBILITY MATRIX *    IS IX S  X  AI * IS +  +  +  -  + * IX +  +  -  -  + * S  +  -  +  -  - * X  -  -  -  -  - * AI +  +  -  -  -

3、infimum和supremum

一個page中總是包含這兩個偽記錄。 頁中所有未刪除(或刪除但還未purged)的行邏輯上都連結到這兩個偽記錄之間，表現為一個邏輯鏈表資料結構，其中supremum偽記錄的鎖始終為next-key lock。

4、heap no

heap no儲存在fixed_extrasize 中。 heap no 為實體儲存體填充的序號，頁的空閑空間掛載在page free鏈表中(頭插法），空閑heap可以重用，但是重用時heap no不變。如果一直是insert 則heap no 不斷增加。heap並不是按照ROWID(主鍵)排序的邏輯鏈表順序，而是物理填充順序。 

5、n bits

和這個page相關的鎖位元影像的大小，每一行記錄都有1 bit的位元影像資訊與其對應，用來表示是否加鎖，並且始終預留64bit。 例如我的表有9條資料，外加infimum和supremum虛擬記錄，即 64+9+2 bits = 75bits，但它還必須被8整除（為了向上取整為一個位元組），最後結果也就是80 bits（8 bytes）。 注意：不管是否加鎖，每行都會對應1 bit。

6、lock struct

這是LOCK的記憶體結構體。源碼中用lock_t表示，有2種

lock_table_t    tab_lock;/*!< table lock */lock_rec_t    rec_lock;/*!< record lock */

一般來說，innodb表上鎖時都會對錶級加上IX，這佔用一個結構體。然後分別對二級索引和主鍵進行加鎖，每一個BLOCK會佔用這樣一個結構體。

7、row lock(s)

這個資訊描述了當前事務加鎖的行數，它是所有lock struct結構體中排除table lock以外所有加鎖記錄的總和，並且包含了infimum和supremum偽記錄。

8、逐步加鎖

細心的朋友應該會發現在show engine innodb status 輸出中，在對大量行進行加鎖時，事務資訊中的row lock會不斷的增加。這是因為加行鎖最終會調用 lock_rec_lock 逐行加鎖，這也會增加了大資料量加鎖的觸發死結的可能性。

二、INSERT SELECT中對SELECT表的加鎖模式

RR隔離等級下的 insert A select B where B.COL=**，會對B表中滿足條件的資料加鎖，但RC模式下B表記錄不會加任何innodb層的鎖。

具體表現如下：

1. 如果B.COL是NON-UNIQUE SECONDARY KEY，並且是非覆蓋索引(執行計畫中沒有 using index)

• • B表 二級索引 對選中記錄加上 LOCK_S|LOCK_ORDINARY[next-key lock]，並且對下一條記錄加上 LOCK_S|LOCK_GAP 

  • B表 PRIMARY KEY 加上 LOCK_S|LOCK_REC_NOT_GAP

2. 如果B.COL是UNIQUE SECONDARY KEY，並且是非覆蓋索引

• • B表二級索引對選中記錄加上 LOCK_S|LOCK_REC_NOT_GAP

  • B表PRIMARY加上 LOCK_S|LOCK_REC_NOT_GAP

3. 如果B.COL沒有二級索引

• • 對整個B表上的所有記錄加上 LOCK_S|LOCK_ORDINARY[next_key_lock]

三、INSERT SELECT中SELECT表的加鎖測試

我們分別對幾種情況進行測試，觀察鎖資訊：

3.1、B.COL是NON-UNIQUE SECONDARY KEY，並且是非覆蓋索引

測試環境準備：

mysql> create table t1(id int primary key,n1 varchar(20),n2 varchar(20),key(n1));mysql> create table t2 like t1;mysql> insert into t1 values(1,'gao1','gao'),(2,'gao1','gao'),(3,'gao1','gao'),(4,'gao2','gao'),(5,'gao2','gao'),(6,'gao2','gao'),(7,'gao3','gao'),(8,'gao4','gao');

查看執行計畫：

mysql> desc select * from t1 force index(n1) where n1='gao2’\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: refpossible_keys: n1          key: n1      key_len: 23          ref: const         rows: 3     filtered: 100.00        Extra: NULL

執行測試SQL：

mysql> begin;insert into t2 select * from t1 force index(n1) where n1='gao2';

觀察B表加鎖結果

• B.col 上加 LOCK_S|LOCK_ORDINARY[next_key_lock]

• B.PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP

• 對B.二級索引下一條記錄加上LOCK_S|LOCK_GAP

紅色部分都是需要鎖定的記錄 

3.2、B.COL是UNIQUE SECONDARY KEY，並且是非覆蓋索引

測試環境準備：

mysql> create table t1(id int primary key,n1 varchar(20),n2 varchar(20),unique key(n1));mysql> create table t2 like t1;mysql> insert into t1 values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

查看執行計畫：

mysql> desc select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4’)\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: rangepossible_keys: n1          key: n1      key_len: 23          ref: NULL         rows: 3     filtered: 100.00        Extra: Using index condition

執行測試SQL：

mysql> begin;insert into t2 select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4');

觀察B表加鎖結果

• B.col 上加 LOCK_S|LOCK_REC_NOT_GAP

• B.PRIMARY 上加 LOCK_S|LOCK_REC_NOT_GAP 

紅色部分都是需要鎖定的記錄

3.3、B.COL沒有二級索引

測試環境準備：

mysql> create table t1(id int primary key,n1 varchar(20),n2 varchar(20));mysql> create table t2 like t1;mysql> insert into t1 values(1,'gao1','gao'),(2,'gao2','gao'),(3,'gao3','gao'),(4,'gao4','gao'),(5,'gao5','gao'),(6,'gao6','gao'),(7,'gao7','gao'),(8,'gao8','gao');

查看執行計畫：

mysql> desc select * from t1  where n1 in ('gao2','gao3','gao4’)\G*************************** 1. row ***************************           id: 1  select_type: SIMPLE        table: t1   partitions: NULL         type: ALLpossible_keys: NULL          key: NULL      key_len: NULL          ref: NULL         rows: 8     filtered: 37.50        Extra: Using where

執行測試SQL：

mysql> begin;insert into t2 select * from t1  where n1 in ('gao2','gao3','gao4');

觀察B表加鎖結果

紅色部分都是需要鎖定的記錄 

現在，我們確認在RR隔離等級下 INSERT SELECT 會對 SELECT 表中合格資料加上 LOCK_S 鎖。

四、INSERT SELECT由於SELECT表引起的死結

我曾經總結過出現死結的條件:

1. 至少2個獨立的線程(會話)；

2. 單位操作中包含多個相對獨立的加鎖步驟，有一定的時間差；

3. 多個線程(會話)之間加鎖對象必須有相互等待的情況發生，並且等待出現環狀。

由於存在對 SELECT 合格資料加上LOCK_S鎖的情況，RR模式下 INSERT SELECT 出現死結的機率無疑更高。我通過測試類比出死結結果，嚴格意義上說，這是相同的語句在高並發情況下表現為兩種死結結果。

測試環境準備：

mysql> create table b(id int primary key,name1 varchar(20),name2 varchar(20),key(name1));mysql> DELIMITER //  mysql> CREATE PROCEDURE test_i()begin   declare num int;  set num = 1; while num <= 3000 do  insert into b values(num,concat('gao',num),'gaopeng');  set num=num+1;end while;end// mysql> call test_i()//create table a like b//

類比下面兩個並發事務：

TX1	TX2
begin;	-
update b set name2='test' where id=2999;	-
-	insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);
update b set name2='test' where id=999;	-

但是在高並發下，相同的並發語句卻表現出不同的死結情況。

見下面詳細過程分析。

4.1、情境一

• TX1：執行update將表b主鍵id=2999的記錄加上LOCK_X

• TX2：執行insert...select語句b表上的記錄(996,997,998,999,2995,2996,2997,2998,2999)會申請加上LOCK_S, 但是id=2999已經加上LOCK_X,顯然不能獲得只能等待.

• TX1：執行update需要獲得表b主鍵id=999的LOCK_X顯然這個記錄已經被TX2加鎖LOCK_S，只能等待，觸發死結檢測

如紅色記錄為不能獲得鎖的記錄:

4.2、情境二

這種情況比較極端只能在高並發上出現

• TX1：執行update將表b主鍵id=2999的記錄加上LOCK_X

• TX2：執行insert...select語句b表上的記錄(996,997,998,999,2995,2996,2997,2998,2999)會申請加上LOCK_S，因為上鎖是有一個逐步加鎖的過程,假設此時加鎖到2997前那麼TX2並不會等待

• TX1：執行update需要獲得表b主鍵id=999的LOCK_X顯然這個記錄已經被TX2加鎖LOCK_S，只能等待

• TX2：繼續加鎖LOCK_S 2997、2998、2999 發現2999已經被TX1加鎖LOCK_X，只能等待，觸發死結檢測 

如紅色記錄為不能獲得鎖的記錄:

五、源碼修改和參數增加

情境二需要在特定的高並發下才會出現，因為在 高並發情境下，很難認為控制 INSERT SELECT （逐行加鎖的）過程，沒辦法讓它在特定條件下停止，好讓我們對其進行觀察。

因此，為了能夠類比出這種情況，筆者對innodb增加了4個參數用於設定加鎖斷點（加鎖過程中臨時sleep下）：

mysql> show variables like '%gaopeng%';+---------------------------+-------+| Variable_name             | Value |+---------------------------+-------+| innodb_gaopeng_sl_heap_no | 0     || innodb_gaopeng_sl_ind_id  | 0     || innodb_gaopeng_sl_page_no | 0     || innodb_gaopeng_sl_time    | 0     |+---------------------------+-------+

這幾個參數預設情況都是0，即不啟用。它們的作用如下：

• innodb_gaopeng_sl_heap_no:記錄所在的heap no

• innodb_gaopeng_sl_ind_id:記錄所在的index_id

• innodb_gaopeng_sl_page_no:記錄所在的page_no

• innodb_gaopeng_sl_time:睡眠多少秒 有了index_id、page_no、heap no 就能唯一限定某條記錄了，並且睡眠等待時間也可以人為指定的。

並且在源碼 lock_rec_lock 開頭增加如下代碼:

這樣一旦判定為合格記錄，本條記錄加鎖前便會睡眠指定時間長度。如果我們設定在LOCK_S:id=2997之前睡眠30秒，那麼情境二必定發生如所示加鎖過程：

六、實際測試6.1、情境一

TX1	TX2
begin;
update b set name2='test' where id=2999;對id:2999加LOCK_X鎖
	insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);對id:996,997,998,999,2995,2996,2997,2998加LOCK_S鎖，但是對id:2999加LOCK_S鎖時發現已經加LOCK_X鎖，需等待
update b set name2='test' where id=999;對id:999加LOCK_X鎖，但是發現已經加LOCK_S鎖，需等待，觸發死結檢測
TX1觸發死結，TX1在權重判定下復原

死結報錯語句:

mysql> update b set name2='test' where id=999;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死結日誌:

死結資訊提取如下:

6.1、情境二

我們設定在下面的語句加斷點：

mysql> insert into a  select * from b
where id in (996,997,998,999,2995,2996,2997,2998,2999)

對B表記錄加鎖時在id = 2997加鎖前停頓30秒，那麼我就需要找到B表主鍵2997的index_id、page_no、heap_no三個資訊，這裡使用到我的innblock工具

因為初始化時是順序插入資料，那麼 id = 2997必定到page 18中。 掃描page 18：

我們按照插入順序推斷出heap_no 84就是id=2997的記錄。我們使用另外一個工具bcview進行驗證：

 ./bcview b.ibd 16 3326 4current block:00000018--Offset:03326--cnt bytes:04--data is:80000bb5

當然16進位 0Xbb5 的值就是 2997。

因此設定參數為：

innodb_gaopeng_sl_heap_no=84;innodb_gaopeng_sl_ind_id=121;innodb_gaopeng_sl_page_no=18;innodb_gaopeng_sl_time=30;mysql> show variables like '%gaopeng%';+---------------------------+-------+| Variable_name             | Value |+---------------------------+-------+| innodb_gaopeng_sl_heap_no | 84    || innodb_gaopeng_sl_ind_id  | 121   || innodb_gaopeng_sl_page_no | 18    || innodb_gaopeng_sl_time    | 30    |+---------------------------+-------+

那麼 情境二 的執行順序如下：

TX1	TX2
begin;
update b set name2='test' where id=2999; 對id:2999加LOCK_X鎖
	insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999);對id:在加鎖到996,997,998,999,2995,2996加LOCK_S鎖，在對id:2997加鎖前睡眠30秒，為下面的update語句騰出時間)
update b set name2='test' where id=999;對id:999加LOCK_X鎖等待但發現已經加LOCK_S鎖，需等待
	醒來後繼續對2997、2998、2999加LOCK_S鎖，但是發現id:2999已經加LOCK_X鎖,需等待，觸發死結檢測
TX1權重復原

死結報錯語句:

mysql> update b set name2='test' where id=999;ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

死結日誌:

死結資訊提取如下:

通過死結日誌明顯能看出同樣的語句報出來的死結資訊卻不一樣，確認在高並發下相同語句，兩種死結情境都是可能發生的。

七、總結

分析死結一般要從死結日誌中擷取如下資訊

• 1、加鎖發生在主鍵還是輔助索引；

• 2、加鎖的模式是什麼；

• 3、是單行還是多行加鎖；

• 4、觸發死結事務最後的語句；

• 5、死結資訊中事務順序是怎麼樣的；

在重現死結過程的時候，必須要做到和線上死結資訊完全符合，這個死結情境才算測試成功了。從本次的例子我們就發現，同樣的語句產生的死結資訊卻不一樣，我們當然就要按照不同的情境去考慮。 本文中的 情境二 比較複雜，一般只是在高並發先出現，測試也相對麻煩。本文通過修改源碼的方式進行測試的，否則很難重現。

最後，找到死結原因後就需要採取必要的措施，比如本文中的例子需要考慮幾個方案：

• 對INSERT SELECT中SELECT表的修改是否及時提交；

• INSERT SELECT是否可以用其他方式代替，因為這種語句在自增鎖上也存在一定風險；

• 是否考慮使用RC隔離等級，在RC隔離等級下不存在對SELECT表記錄加鎖的情況。

最後再強調一點，對於出現LOCK_S這樣的鎖最好深入分析，因為這種鎖並不多見。

對本文有任何疑問可掃碼添加原文作者