溫馨提示×
您好，登錄后才能下訂單哦！
點擊 登錄注冊 即表示同意《億速云用戶服務條款》
您好，登錄后才能下訂單哦！
這篇文章主要介紹“Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析”，在日常操作中，相信很多人在Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析”的疑惑有所幫助！接下來，請跟著小編一起來學習吧！
1、 innodb lock模型
[LOCK_ORDINARY[next_key_lock]:]
源碼定義:
#define LOCK_ORDINARY 0 /*!< this flag denotes an ordinary next-key lock in contrast to LOCK_GAP or LOCK_REC_NOT_GAP */
默認是LOCK_ORDINARY即普通的next_key_lock,鎖住行及以前的間隙。
[LOCK_GAP:]
源碼定義:
#define LOCK_GAP 512 /*!< when this bit is set, it means that the lock holds only on the gap before the record; for instance, an x-lock on the gap does not give permission to modify the record on which the bit is set; locks of this type are created when 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 on the index record and does NOT block inserts to the gap before the index record; this is used in the case when we retrieve a record with a unique key, and is also used in locking plain SELECTs (not part of UPDATE or DELETE) when the user has set the READ COMMITTED isolation level */
行鎖，鎖住行而不鎖住任何間隙。
[LOCK_INSERT_INTENTION:]
源碼定義：
#define LOCK_INSERT_INTENTION 2048 /*!< this bit is set when we place a waiting gap type record lock request in order to let an insert of an index record to wait until there are no conflicting locks by other transactions on the gap; note that this flag remains 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中包含這兩個偽記錄。頁中所有的行未刪除(或刪除未purge)的行邏輯上都連接到這兩個虛列之間，表現為一個邏輯鏈表數據結構，其中supremum偽記錄的鎖始終為next_key_lock。
4、heap no
heap no存儲在fixed_extrasize 中。heap no 為物理存儲填充的序號，頁的空閑空間掛載在page free鏈表中(頭插法)可以重用，但是重用此heap no不變，如果一直是insert 則heap no 不斷增加，并不是按照ROWID(主鍵)排序的邏輯鏈表順序，而是物理填充順序。
5、n bits
和這個page相關的鎖位圖的大小，每一行記錄都有1 bit的位圖信息與其對應，用來表示是否加鎖，并且始終預留64bit。例如我的表有9條數據，同時包含infimum和supremum虛擬記錄即 64+9+2 bits，即75bits但是必須被8整除向上取整為一個字節，結果也就是就是80 bits。注意不管是否加鎖每行都會對應一bit的位圖。
6、lock struct
這是LOCK的內存結構體源碼中用lock_t表示其可以包含
lock_table_t tab_lock;/*!< table lock */ lock_rec_t rec_lock;/*!< record lock */
一般來說innodb上鎖都會對表級加上IX,這占用一個結構體。然后分別對二級索引和主鍵進行加鎖，每一個BLOCK會占用這樣一個結構體。
7、row lock
這個信息描述了當前事務加鎖的行數,他是所有lock struct結構體中排除table lock以外所有加鎖記錄的總和，并且包含了infimum和supremum偽列。
8、逐步加鎖
如果細心的朋友應該會發現在show engine 中事務信息中的row lock在對大量行進行加鎖的時候會不斷的增加，因為加行鎖最終會調用lock_rec_lock逐行加鎖，這也會增加了大數據量加鎖的觸發死鎖的可能性。
RR隔離級別下insert A select B where B.COL=**,innodb層會對B表滿足條件的數據進行加鎖，但是RC模式下B表記錄不會加任何innodb層的鎖，表現如下：
如果B.COL有二級(非唯一),并且執行計劃使用到了(非using index)
B表二級索引對選中記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock],并且對下一條記錄加上LOCK_S|LOCK_GAP
B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP
如果B.COL有二級(唯一),并且執行計劃使用到了(非using index)
B表二級索引對選中記錄加上LOCK_S|LOCK_REC_NOT_GAP
B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP
如果B.COL沒有二級索引
對整個B表上的所有記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock]
下面我們分別對其進行測試和打印輸出：
1. 如果B.COL有二級(唯一),并且執行計劃使用到了(非using index)
使用語句：
drop table t1; drop table t2; create table t1(id int primary key,n1 varchar(20),n2 varchar(20),key(n1)); create table t2 like t1; 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 insert into t2 select * from t1 force index(n1) where n1='gao2'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | INSERT | t2 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL | | 1 | SIMPLE | t1 | NULL | ref | n1 | n1 | 23 | const | 3 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
執行語句：
begin;insert into t2 select * from t1 force index(n1) where n1='gao2';
觀察結果：
二級索引對記錄加上LOCK_S|LOCK_ORDINARY[next_key_lock]
-----TRX NO:28470 LOCK STRUCT(1)(Add by gaopeng) RECORD LOCKS space id 86 page no 4 n bits 80 index n1 of table `test`.`t1` trx id 28470 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock]) Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f32; asc gao2;; 1: len 4; hex 80000004; asc ;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f32; asc gao2;; 1: len 4; hex 80000005; asc ;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f32; asc gao2;; 1: len 4; hex 80000006; asc ;;
PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP
-----TRX NO:28470 LOCK STRUCT(1)(Add by gaopeng) RECORD LOCKS space id 86 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 28470 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000006f20; asc o ;; 2: len 7; hex bc000001300134; asc 0 4;; 3: len 4; hex 67616f32; asc gao2;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000006f20; asc o ;; 2: len 7; hex bc000001300140; asc 0 @;; 3: len 4; hex 67616f32; asc gao2;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000006f20; asc o ;; 2: len 7; hex bc00000130014c; asc 0 L;; 3: len 4; hex 67616f32; asc gao2;; 4: len 3; hex 67616f; asc gao;;
對二級索引下一條記錄加上LOCK_S|LOCK_GAP
-----TRX NO:28470 LOCK STRUCT(1)(Add by gaopeng) RECORD LOCKS space id 86 page no 4 n bits 80 index n1 of table `test`.`t1` trx id 28470 lock mode S(LOCK_S) locks gap before rec(LOCK_GAP) Record lock, heap no 8 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f33; asc gao3;; 1: len 4; hex 80000007; asc ;;
如圖紅色部分都是需要鎖定的記錄
1.png
2. 如果B.COL有二級(唯一),并且執行計劃使用到了(非using index)
使用語句：
drop table t1; drop table t2; create table t1(id int primary key,n1 varchar(20),n2 varchar(20),unique key(n1)); create table t2 like t1; 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 insert into t2 select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4');
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | INSERT | t2 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | t1 | NULL | range | n1 | n1 | 23 | NULL | 3 | 100.00 | Using index condition |
+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+-----------------------+執行語句:
begin;insert into t2 select * from t1 force index(n1) where n1 in ('gao2','gao3','gao4');觀察輸出：
B表二級索引對選中記錄加上LOCK_S|LOCK_REC_NOT_GAP
-----TRX NO:30514 LOCK STRUCT(1)(Add by gaopeng) RECORD LOCKS space id 94 page no 4 n bits 80 index n1 of table `test`.`t1` trx id 30514 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f32; asc gao2;; 1: len 4; hex 80000002; asc ;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f33; asc gao3;; 1: len 4; hex 80000003; asc ;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 2; compact format; info bits 0 0: len 4; hex 67616f34; asc gao4;; 1: len 4; hex 80000004; asc ;;
B表PRIMARY加上LOCK_S|LOCK_REC_NOT_GAP
-----TRX NO:30514 LOCK STRUCT(1)(Add by gaopeng) RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 30514 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a200000115011c; asc ;; 3: len 4; hex 67616f32; asc gao2;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150128; asc (;; 3: len 4; hex 67616f33; asc gao3;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150134; asc 4;; 3: len 4; hex 67616f34; asc gao4;; 4: len 3; hex 67616f; asc gao;;
如圖紅色部分都是需要鎖定的記錄
2.png
3.如果B.COL沒有二級索引
使用語句：
drop table t1; drop table t2; create table t1(id int primary key,n1 varchar(20),n2 varchar(20)); create table t2 like t1; 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 insert into t2 select * from t1 where n1 in ('gao2','gao3','gao4');
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
| 1 | INSERT | t2 | NULL | ALL | NULL | NULL | NULL | NULL | NULL | NULL | NULL |
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 8 | 37.50 | Using where |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+執行語句:
begin;insert into t2 select * from t1 where n1 in ('gao2','gao3','gao4');觀察輸出:
-----TRX NO:30535 LOCK STRUCT(1)(Add by gaopeng) RECORD LOCKS space id 94 page no 3 n bits 80 index PRIMARY of table `test`.`t1` trx id 30535 lock mode S(LOCK_S) locks gap and rec(LOCK_ORDINARY[next_key_lock]) Record lock, heap no 1 PHYSICAL RECORD: n_fields 1; compact format; info bits 0 0: len 8; hex 73757072656d756d; asc supremum;; Record lock, heap no 2 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000001; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150110; asc ;; 3: len 4; hex 67616f31; asc gao1;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 3 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000002; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a200000115011c; asc ;; 3: len 4; hex 67616f32; asc gao2;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 4 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000003; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150128; asc (;; 3: len 4; hex 67616f33; asc gao3;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 5 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000004; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150134; asc 4;; 3: len 4; hex 67616f34; asc gao4;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 6 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000005; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150140; asc @;; 3: len 4; hex 67616f35; asc gao5;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 7 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000006; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a200000115014c; asc L;; 3: len 4; hex 67616f36; asc gao6;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000007; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150158; asc X;; 3: len 4; hex 67616f37; asc gao7;; 4: len 3; hex 67616f; asc gao;; Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000008; asc ;; 1: len 6; hex 000000007728; asc w(;; 2: len 7; hex a2000001150164; asc d;; 3: len 4; hex 67616f38; asc gao8;; 4: len 3; hex 67616f; asc gao;;
如圖紅色部分都是需要鎖定的記錄
3.png
有了上面的理論，我們知道在RR隔離級別下insert...select會對select符合條件的數據加上LOCK_S鎖，我曾經總結過出現死鎖的條件:
至少2個獨立的線程(會話)
單位操作中包含多個相對獨立的加鎖步驟，有一定的時間差
多個線程(會話)之間加鎖對象必須有相互等待的情況發生，并且等待出現環狀。
由于存在對select符合條件的數據加上LOCK_S鎖的情況，RR模式下insert...select出現死鎖的概率無疑更加高，我通過測試模擬出這種情況，嚴格意義上是相同的語句在高并發情況下表現為兩種死鎖情況。
測試腳本:
create table b(id int primary key,name1 varchar(20),name2 varchar(20));
alter table b add key(name1);
DELIMITER //
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//
call test_i()//
create table a like b//
DELIMITER ;語句都是一樣的:
| 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; | - |
但是在高并發下相同的語句卻表現出不同的死鎖情況，現在進行分析:
情況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，只能等待，觸發死鎖檢測
如下圖紅色記錄為不能獲得鎖的記錄:
情況1.jpg
情況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，只能等待，觸發死鎖檢測
如下圖紅色記錄為不能獲得鎖的記錄:
情況2.jpg
情況2的測試需要在高并發下才會出現，因為insert...select語句是一條語句很難人為控制，也就是很讓他在特定條件下停止。但是為了能夠模擬出這種情況筆者對innodb增加了4個參數如下，為了方便識別我都加上了自己的名字的拼音：
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 開頭增加如下代碼:
//add by gaopeng
/*if find index_id heap no page no to sleep srv_gaopeng_sl_time secs*/
if(srv_gaopeng_sl_ind_id && srv_gaopeng_sl_page_no && srv_gaopeng_sl_heap_no)
{
if(heap_no == (ulint)(srv_gaopeng_sl_heap_no) && (block->page.id).page_no() ==(ib_uint32_t)(srv_gaopeng_sl_page_no)
&& index->id ==(index_id_t)(srv_gaopeng_sl_ind_id) )
{
lock_mutex_exit();
sleep(srv_gaopeng_sl_time);
lock_mutex_enter();
}
}
//add end這樣一旦判定為符合條件的記錄，本條記錄的加鎖錢便會休眠指定的秒如果我們擬定在LOCK_S:id=2997之前睡眠30秒，那么情況2能夠必定發生如下圖:
情況3.jpg
情況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
死鎖日志:
*** (1) TRANSACTION: TRANSACTION 48423, ACTIVE 7 sec starting index read mysql tables in use 2, locked 2 LOCK WAIT 5 lock struct(s), heap size 1160, 9 row lock(s), undo log entries 8 MySQL thread id 4, OS thread handle 140737223177984, query id 9110 localhost root Sending data insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999) *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 119 page no 18 n bits 160 index PRIMARY of table `test`.`b` trx id 48423 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT) Record lock, heap no 86 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000bb7; asc ;; 1: len 6; hex 00000000bd26; asc &;; 2: len 7; hex 21000001511e7d; asc ! Q };; 3: len 7; hex 67616f32393939; asc gao2999;; 4: len 4; hex 74657374; asc test;; *** (2) TRANSACTION: TRANSACTION 48422, ACTIVE 24 sec starting index read mysql tables in use 1, locked 1 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1 MySQL thread id 3, OS thread handle 140737223444224, query id 9111 localhost root updating update b set name2='test' where id=999 *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 119 page no 18 n bits 160 index PRIMARY of table `test`.`b` trx id 48422 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 86 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000bb7; asc ;; 1: len 6; hex 00000000bd26; asc &;; 2: len 7; hex 21000001511e7d; asc ! Q };; 3: len 7; hex 67616f32393939; asc gao2999;; 4: len 4; hex 74657374; asc test;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 119 page no 10 n bits 456 index PRIMARY of table `test`.`b` trx id 48422 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT) Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 800003e7; asc ;; 1: len 6; hex 00000000b534; asc 4;; 2: len 7; hex bd000001310110; asc 1 ;; 3: len 6; hex 67616f393939; asc gao999;; 4: len 7; hex 67616f70656e67; asc gaopeng;; *** WE ROLL BACK TRANSACTION (2)
信息提取如下:
TRX1:48423 LOCK HOLD:死鎖信息不提供 LOCK WAIT: 表:b 索引:`PRIMARY` 鎖模式:LOCK_S|LOCK_REC_NOT_GAP|LOCK_WAIT 記錄:主鍵為0Xbb7(2999) 附加信息:space id 119 page no 18 heap no 86 CURRENT SQL: insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999) TRX2:48422(觸發死鎖、權重回滾) LOCK HOLD: 表:b 索引:`PRIMARY` 鎖模式:LOCK_X|LOCK_REC_NOT_GAP 記錄:主鍵為0Xbb7(2999) 附加信息:pace id 119 page no 18 heap no 86 LOCK WAIT: 表:b 索引:`PRIMARY` 鎖模式:LOCK_X|LOCK_REC_NOT_GAP|LOCK_WAIT 記錄:主鍵為0X3e7(999) 附加信息:space id 119 page no 10 heap no 11 CURRENT SQL: update b set name2='test' where id=999
情況2:
如上我們擬定在語句
insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)
對b表記錄加鎖時在2997加鎖前停頓30秒，那么我就需要找到b表主鍵2997的index_id、page_no、heap_no三個信息，這里使用到我的innblock工具
./innblock b.ibd scan 16 ===INDEX_ID:121 level1 total block is (1) block_no: 3,level: 1|*| level0 total block is (9) block_no: 5,level: 0|*|block_no: 6,level: 0|*|block_no: 7,level: 0|*| block_no: 10,level: 0|*|block_no: 11,level: 0|*|block_no: 13,level: 0|*| block_no: 15,level: 0|*|block_no: 17,level: 0|*|block_no: 18,level: 0|*|
因為為順序插入那么2997必定到page 18中然后如下:
./innblock b.ibd 18 16 ==== Block base info ==== block_no:18 space_id:121 index_id:121 .... (84) normal record offset:3287 heapno:83 n_owned 0,delflag:N minflag:0 rectype:0 (85) normal record offset:3326 heapno:84 n_owned 0,delflag:N minflag:0 rectype:0 (86) normal record offset:3365 heapno:85 n_owned 0,delflag:N minflag:0 rectype:0 (87) normal record offset:3404 heapno:86 n_owned 0,delflag:N minflag:0 rectype:0 (88) normal record offset:3443 heapno:87 n_owned 0,delflag:N minflag:0 rectype:0
因為為順序插入heap_no 84就是id為2997的記錄。我們使用另外一個工具bcview進行驗證
./bcview b.ibd 16 3326 4 current block:00000018--Offset:03326--cnt bytes:04--data is:80000bb5
當然0Xbb5就是2997
因此設置參數為：
set global innodb_gaopeng_sl_heap_no=84; set global innodb_gaopeng_sl_ind_id=121; set global innodb_gaopeng_sl_page_no=18; set global 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 | +---------------------------+-------+
那么情況2執行順序如下:
| 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) TRANSACTION: TRANSACTION 51545, ACTIVE 41 sec starting index read mysql tables in use 1, locked 1 LOCK WAIT 3 lock struct(s), heap size 1160, 2 row lock(s), undo log entries 1 MySQL thread id 9, OS thread handle 140737223444224, query id 18310 localhost root updating update b set name2='test' where id=999 *** (1) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 121 page no 10 n bits 456 index PRIMARY of table `test`.`b` trx id 51545 lock_mode X(LOCK_X) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT) Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 800003e7; asc ;; 1: len 6; hex 00000000c167; asc g;; 2: len 7; hex bc000001300110; asc 0 ;; 3: len 6; hex 67616f393939; asc gao999;; 4: len 7; hex 67616f70656e67; asc gaopeng;; *** (2) TRANSACTION: TRANSACTION 51546, ACTIVE 30 sec starting index read mysql tables in use 2, locked 2 5 lock struct(s), heap size 1160, 9 row lock(s), undo log entries 8 MySQL thread id 8, OS thread handle 140737223177984, query id 18309 localhost root Sending data insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999) *** (2) HOLDS THE LOCK(S): RECORD LOCKS space id 121 page no 10 n bits 456 index PRIMARY of table `test`.`b` trx id 51546 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) Record lock, heap no 8 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 800003e4; asc ;; 1: len 6; hex 00000000c164; asc d;; 2: len 7; hex b90000012d0110; asc - ;; 3: len 6; hex 67616f393936; asc gao996;; 4: len 7; hex 67616f70656e67; asc gaopeng;; Record lock, heap no 9 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 800003e5; asc ;; 1: len 6; hex 00000000c165; asc e;; 2: len 7; hex ba0000014f0110; asc O ;; 3: len 6; hex 67616f393937; asc gao997;; 4: len 7; hex 67616f70656e67; asc gaopeng;; Record lock, heap no 10 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 800003e6; asc ;; 1: len 6; hex 00000000c166; asc f;; 2: len 7; hex bb0000012f0110; asc / ;; 3: len 6; hex 67616f393938; asc gao998;; 4: len 7; hex 67616f70656e67; asc gaopeng;; Record lock, heap no 11 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 800003e7; asc ;; 1: len 6; hex 00000000c167; asc g;; 2: len 7; hex bc000001300110; asc 0 ;; 3: len 6; hex 67616f393939; asc gao999;; 4: len 7; hex 67616f70656e67; asc gaopeng;; *** (2) WAITING FOR THIS LOCK TO BE GRANTED: RECORD LOCKS space id 121 page no 18 n bits 160 index PRIMARY of table `test`.`b` trx id 51546 lock mode S(LOCK_S) locks rec but not gap(LOCK_REC_NOT_GAP) waiting(LOCK_WAIT) Record lock, heap no 86 PHYSICAL RECORD: n_fields 5; compact format; info bits 0 0: len 4; hex 80000bb7; asc ;; 1: len 6; hex 00000000c959; asc Y;; 2: len 7; hex 00000002240110; asc $ ;; 3: len 7; hex 67616f32393939; asc gao2999;; 4: len 4; hex 74657374; asc test;; *** WE ROLL BACK TRANSACTION (1)
信息提取如下:
TRX1:51545 LOCK HOLD:死鎖信息不提供 LOCK WAIT: 表:b 索引:`PRIMARY` 鎖模式:LOCK_MODE:LOCK_X|LOCK_REC_NOT_GAP|LOCK_WAIT 記錄:主鍵為0X3e7 附加信息: space id 121 page no 10 heap no 11 CURRENT SQL: update b set name2='test' where id=999 TRX2:51546 LOCK HOLD: 表:b 索引:`PRIMARY` 鎖模式:LOCK_MODE:LOCK_S|LOCK_REC_NOT_GAP 記錄:主鍵為0X3e4到0X3e7的多個行鎖 附加信息:space id 121 page no 10 LOCK WAIT: 表:b 索引:`PRIMARY` 鎖模式:LOCK_MODE:LOCK_S|LOCK_REC_NOT_GAP|LOCK_WAIT 記錄:主鍵為0Xbb7 附加信息:space id 121 page no 10 heap no 86 CURRENT SQL: insert into a select * from b where id in (996,997,998,999,2995,2996,2997,2998,2999)
我們通過死鎖日志明顯的看出同樣的語句報出來的死鎖信息卻不一樣，在高并發下相同語句，兩種死鎖場景都是可能發生的。
到此，關于“Innodb中RR隔離級別下insert...select 對select表加鎖模型和死鎖案列分析”的學習就結束了，希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學習，快去試試吧！若想繼續學習更多相關知識，請繼續關注億速云網站，小編會繼續努力為大家帶來更多實用的文章！
免責聲明：本站發布的內容（圖片、視頻和文字）以原創、轉載和分享為主，文章觀點不代表本網站立場，如果涉及侵權請聯系站長郵箱：is@yisu.com進行舉報，并提供相關證據，一經查實，將立刻刪除涉嫌侵權內容。
