Mysql資料庫鎖（Innodb）

資料庫鎖是Mysql實作資料一致性的基礎之一，是在事務的基礎之上，基于Mysql Server層或存盤引擎層實作的，

鎖日志

前置條件：

set GLOBAL innodb_status_output=ON;  

set GLOBAL innodb_status_output_locks=ON;

查看陳述句：

show engine innodb status\G;

鎖分類

表鎖與行鎖

按照鎖的粒度，可以分為表鎖和行鎖

共享鎖與排他鎖

共享鎖

1. select *** lock in share mode
2. Lock Table *** read

排他鎖

1. select  *** for update
2. Lock Table *** write

意向鎖

意向鎖是表級的
同樣具有意向共享鎖（IS）、意向排他鎖（IX）
TABLE LOCK table *** trx id *** lock mode IX、TABLE LOCK table *** trx id *** lock mode IS
意向鎖不會與行級鎖沖突，并且意向鎖之間沒有互斥關系
意向鎖的意義是用于協調表鎖與行鎖之間的互斥關系，確保事務可以正確的請求和釋放鎖，如果沒有意向鎖，當對全表加鎖時，需要遍歷全表，判斷是否存在某些行記錄被加了行鎖，那么這個加表鎖的操作的性能會差很多，有了意向鎖，A事務對某行記錄加鎖時會先申請意向鎖，申請成功后再加行鎖，加鎖成功后，B事務申請表級鎖時會先判斷表上面的意向鎖是否兼容，
意向共享鎖（IS鎖）：事務在請求S鎖之前，先獲取IS鎖

意向排他鎖（IX鎖）：事務在請求X鎖之前，先獲取IX鎖
兼容性：

意向共享鎖（IS）意向排他鎖（IX）

意向共享鎖（IS）兼容兼容

意向排他鎖（IX）兼容兼容

意向共享鎖（IS）意向排他鎖（IX）

表級共享鎖（S）兼容互斥

表級排他鎖（X）互斥互斥

	意向共享鎖（IS）	意向排他鎖（IX）
意向共享鎖（IS）	兼容	兼容
意向排他鎖（IX）	兼容	兼容

	意向共享鎖（IS）	意向排他鎖（IX）
表級共享鎖（S）	兼容	互斥
表級排他鎖（X）	互斥	互斥

記錄鎖（Record Lock）

RECORD LOCKS *** index uniq_idx of table *** trx id *** lock_mode X locks rec but not gap、RECORD LOCKS *** index uniq_idx of table *** trx id *** lock_mode S locks rec but not gap
基于索引創建的，受索引的影響
同樣具有共享、排他的區別

間隙鎖（Gap Lock）

間隙鎖是RR模式避免幻讀的基礎
顧名思義，鎖住的是范圍，比如(-∞，10),(10，15)等開區間
RECORD LOCKS *** index idx_c of table *** trx id *** lock_mode X locks gap before rec

臨鍵鎖（Next-Key Locks）

記錄鎖、間隙鎖的組合就是臨鍵鎖
臨鍵鎖是申請鎖時，默認先申請的鎖型別，如果申請失敗，則進行降級，將為間隙鎖或記錄鎖
不僅鎖住記錄，還會鎖住間隙，比如(-∞,10],(10,15]等區間，前開后閉區間
RECORD LOCKS *** index idx_c of table *** trx id *** lock_mode X

插入意向鎖（Insert Intention Locks）

RECORD LOCKS *** index PRIMARY of table *** trx id *** lock_mode X insert intention waiting
插入意向鎖可以理解為特殊的Gap鎖的一種，用來提高并發寫的性能，當遇到主鍵或唯一鍵沖突時，會退化為讀鎖
插入意向鎖和插入意向鎖之間不會互斥（只要記錄本身唯一鍵、主鍵不沖突），

示例：

事務A插入資料27時，獲取到的是（25,30）的間隙鎖和27的行鎖，事務B插入資料28時，獲取到的也是（25,30）的間隙鎖和28的行鎖，

因為行鎖27和行鎖28不是同一行，所以不會沖突，然后兩個事務獲取到的插入意向鎖不會互相排斥，所以可以插入成功，

自增鎖（AUTO-INC Locks）

TABLE LOCK table *** trx id *** lock mode AUTO-INC waiting
自增鎖處于表級別的鎖

元資料鎖（metadata lock）

Server層實作的鎖，與引擎層無關
執行select時，如果有ddl陳述句，那么ddl會被阻塞（非online ddl），因為select陳述句有metadata lock，防止元資料被改掉，

非online ddl的問題：

select操作會先獲取元資料共享鎖（shared MDL），而DDL 操作會先獲取元資料排他鎖（exclusive MDL）, 而且不僅僅是先select后ddl 導致ddl阻塞，

而且ddl后面的select也會被阻塞，因為ddl申請元資料排他鎖的優先級要比select操作的優先級要高，只有ddl操作完成后，后面的select才會順利獲取元資料共享鎖，才能繼續執行，因此ddl操作的花費是昂貴的，因此才出現online DDL；

鎖解讀

RR下的有二級索引的情況

CREATE TABLE `a` (
  `a` int(11) NOT NULL,
  `b` int(11) DEFAULT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  `aaa` bigint(20) DEFAULT '0',
  PRIMARY KEY (`a`),
  UNIQUE KEY `idx_b` (`b`),
  KEY `idx_c` (`c`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

select * from a WHERE c = 7 for UPDATE;

MySQL thread id 10, OS thread handle 139897622177536, query id 377 172.18.0.1 wss

對a表添加意向排他鎖
TABLE LOCK table `demo`.`a` trx id 480393 lock mode IX

對a表的idx_c二級索引加臨鍵鎖
RECORD LOCKS space id 97 page no 5 n bits 72 index idx_c of table `demo`.`a` trx id 480393 lock_mode X
Record lock, heap no 3 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
二級索引idx_c的7加鎖
 0: len 4; hex 80000007; asc     ;;
主鍵索引上的3也會被加鎖
 1: len 4; hex 80000003; asc     ;;

對主鍵索引加記錄鎖，對3進行加鎖
RECORD LOCKS space id 97 page no 3 n bits 72 index PRIMARY of table `demo`.`a` trx id 480393 lock_mode X locks rec but not gap
Record lock, heap no 3 PHYSICAL RECORD: n_fields 7; compact format; info bits 0
第一個欄位是主鍵,被加鎖
 0: len 4; hex 80000003; asc     ;;
最近一次被更新的事務id
 1: len 6; hex 000000009c29; asc      );;
回滾指標
 2: len 7; hex be00000147011c; asc     G  ;;
該行第2、3、4、5個欄位
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;
 6: len 8; hex 8000000000000000; asc         ;;

對idx_c索引樹上加間隙鎖
RECORD LOCKS space id 97 page no 5 n bits 72 index idx_c of table `demo`.`a` trx id 480393 lock_mode X locks gap before rec
Record lock, heap no 4 PHYSICAL RECORD: n_fields 2; compact format; info bits 0
二級索引idx_c上對9加鎖
0: len 4; hex 80000009; asc     ;;
主鍵索引上的5也會被鎖住
 1: len 4; hex 80000005; asc     ;;

RR下的無二級索引的情況

CREATE TABLE `tm` (
  `i` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

SELECT * FROM tm WHERE i = 1 FOR UPDATE;

MySQL thread id 25, OS thread handle 139897622718208, query id 556 172.18.0.1 wss

對tm表添加意向排他鎖
TABLE LOCK table `demo`.`tm` trx id 480412 lock mode IX

由于表定義沒有顯示的索引，而InnoDB又是索引組織表，會自動創建一個索引，這里面叫index GEN_CLUST_INDEX  
由于沒有索引，那么會對每條記錄都加上臨鍵鎖
RECORD LOCKS space id 110 page no 3 n bits 80 index GEN_CLUST_INDEX of table `demo`.`tm` trx id 480412 lock_mode X
supremum 指的是頁里面的最后一條記錄（偽記錄，通過select查不到，并不是真實記錄）；還有Infimum表示頁面中的第一個記錄（偽記錄）
通過supremum 鎖住index GEN_CLUST_INDEX的最大值到正無窮大的區間，這樣就可以鎖住全部記錄，以及全部間隙，相當于表鎖
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 4; compact format; info bits 0
 0: len 6; hex 000000000300; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea000001960110; asc        ;;
 3: len 4; hex 80000001; asc     ;;

Record lock, heap no 3 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000301; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea00000196011e; asc        ;;
 3: len 4; hex 80000002; asc     ;;

Record lock, heap no 4 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000302; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea00000196012c; asc       ,;;
 3: len 4; hex 80000003; asc     ;;

Record lock, heap no 5 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000303; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea00000196013a; asc       :;;
 3: len 4; hex 80000004; asc     ;;

Record lock, heap no 6 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000304; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea000001960148; asc       H;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 7 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000305; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea000001960156; asc       V;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 8 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000306; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea000001960164; asc       d;;
 3: len 4; hex 80000005; asc     ;;

Record lock, heap no 9 PHYSICAL RECORD: n_fields 4; compact format; info bits 0
 0: len 6; hex 000000000307; asc       ;;
 1: len 6; hex 00000007548e; asc     T ;;
 2: len 7; hex ea000001960172; asc       r;;
 3: len 4; hex 80000005; asc     ;;

鎖演算法

自增鎖

? 自增列的維護與資料的新增有關，任何產生新資料的陳述句都可以稱為”Insert like“，大致分3種，分別是simple insert、bulk inserts、mixed-mode inserts

simple insert：插入記錄的行數時確定的，比如：insert into values、replace

bulk inserts：插入的記錄行數不能馬上確定的，比如：insert ... select ... ，replace ... select 和load data

mixed-mode inserts：部分自增列的值給定或者不給定，比如INSERT INTO t1 (c1,c2) VALUES (1,'a'), (NULL,'b'), (5,'c'), (NULL,'d');和INSERT ... ON DUPLICATE KEY UPDATE

死鎖

自增鎖死鎖常常出現在資料遷移程序中，常見的資料遷移大多以雙寫來實作，類似一個行程負責從舊表往新表寫（insert ... select ...），而應用程式則繼續往新表寫，此時新表可能會發生死鎖，

鎖模式

innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

任何一種insert-like陳述句，都會產生一個表級的自增鎖，性能差，但是足夠安全
innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

? 這是默認的鎖模式，當發生bulk inserts時，會產生一個表級的自增鎖直到陳述句執行結束，注意不是事務結束，對于simple insert，則使用輕量鎖，只要獲取相應的auto increment就釋放鎖，不會等待陳述句結束，當表被加上自增鎖后，這種輕量鎖不會加鎖成功，會等待，

? 優點是性能較好，缺點還是會產生表級的自增鎖，因為要保證自增id的連續性，防止bulk inserts時，被其他insert 陳述句搶走 auto increment值，

innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

? 當進行bulk insert 時，不會產生表級別的自增鎖，因為他是允許其他insert 插入的，新增一條記錄，插入分配一個auto increment值，不會預分配，

? 優點是性能較好，缺點是一次bulk inserts 產生的自增列并不是連續的，同時SBR模式下的主從復制可能會產生資料不一致錯誤，該錯誤可以通過將主從復制改為RBR模式，

? PS：SBR模式的主從復制：binlog格式以statement的日志格式；RBR模式的主從復制：binlog格式以基于行（Row）的日志格式（推薦），

優化

如果binlog-format是row模式的，而且不關心一條bulk insert的自增列的值連續且提交順序與自增列值大小的順序一致，那么可以設定innodb_autoinc_lock_mode = 2 來提高性能

一條bulk insert 自增列是否連續有時候會影響分頁查詢，有時候為了解決深分頁查詢問題，會采用每次分頁查詢的最大值來進行分頁，比如

select * from xx where id>1 limit N
select * from xx where id>1+N limit N
select * from xx where id>1+N+N limit N

當id=101的記錄先提交，該記錄的值剛好是當前頁的最大值，此時id=100資料被提交，那么下次分頁查詢會從101開始查詢，就會造成這次翻頁的資料存在缺失的情況，如果分頁查詢中包含oder by id的查詢或者有and create_time < (now() - INTERVAL 5 second)，那么可以通過往前翻頁來找到，但是歸根結底當前分頁的資料需要等待100資料被提交后重繪分頁來解決，

通用鎖

鎖是在索引上實作的
假設有一個key，有5條記錄， 1，3，5，7，9. 如果where id<5 ，那么鎖住的區間不是（-∞，5），而是(-∞,1],(1,3],(3,5] 多個區間組合而成；如果where id =5，那么鎖住的區間是(3,5],(5,9]
next-key lock 降級為 record lock的情況:

如果是唯一索引，且查詢條件得到的結果集是1條記錄（等值，而不是范圍），那么會降級為記錄鎖

典型的案例：where primary_key = 1 (會降級), 而不是 where primary_key < 10 （由于回傳的結果集不僅僅一條，那么不會降級）
上鎖，不僅僅對輔助索引加鎖，還需要對主鍵索引加鎖；不僅僅對主鍵索引加鎖，還需要對輔助索引加鎖

RR下的Update鎖機制

如果

select * from xx where col <比較運算子> M for update
M->next-rec: 表示M的下一條記錄
M->pre-rec: 表示M的前一條記錄

第一輪總結

等值查詢M，非唯一索引的加鎖邏輯

? (M->pre-rec,M],(M,M->next-rec]

等值查詢M，唯一鍵的加鎖邏輯

? [M], next-lock 降級為 record locks

>= ，非唯一索引的加鎖邏輯

? (M->pre_rec,M],(M,M->next-rec]....(∞]

>= ，唯一索引的加鎖邏輯
(M->pre_rec,M],(M,M->next-rec]....(∞]
<= , 非唯一索引的加鎖邏輯
(-∞] ... (M,M->next-rec]
<= , 唯一索引的加鎖邏輯
(-∞] ... (M,M->next-rec]
>, 非唯一索引的加鎖邏輯
(M,M->next-rec] ... (∞]
>, 唯一索引的加鎖邏輯
(M,M->next-rec] ... (∞]
< , 非唯一索引的加鎖邏輯
(-∞] ... (M->pre_rec,M]
< , 唯一索引的加鎖邏輯
(-∞] ... (M->pre_rec,M]

第二輪總結合并

等值查詢M，非唯一索引的加鎖邏輯
(M->pre-rec,M],(M,M->next-rec]
等值查詢M，唯一鍵的加鎖邏輯
[M], next-lock 降級為 record locks
這里大家還記得之前講過的通用演算法嗎：
next-key lock 降級為 record lock的情況：
如果是唯一索引，且查詢條件得到的結果集是1條記錄（等值，而不是范圍），那么會降級為記錄鎖
>= ，加鎖邏輯
(M->pre_rec,M],(M,M->next-rec]....(∞]
>, 加鎖邏輯
(M,M->next-rec] ... (∞]
<= , 加鎖邏輯
(-∞] ... (M,M->next-rec]
< , 加鎖邏輯
(-∞] ... (M->pre_rec,M]

最后的疑問和總結

為什么要對M->next-rec 或者 M->pre-rec ？

因為為了防止幻讀，

RR下的Insert鎖機制

Insert 的流程(沒有唯一索引的情況)： insert N

找到大于N的第一條記錄M
如果M上面沒有gap ， next-key locking的話，可以插入，否則等待 (對其next-rec加insert intension lock，由于有gap鎖，所以等待)

Insert 的流程(有唯一索引的情況)： insert N

找到大于N的第一條記錄M，以及前一條記錄P
如果M上面沒有gap ， next-key locking的話，進入第三步驟，否則等待(對其next-rec加insert intension lock，由于有gap鎖，所以等待)
檢查p：
判斷p是否等于n：
如果不等: 則完成插入（結束）
如果相等：
再判斷P 是否有鎖，
如果沒有鎖:
報1062錯誤（duplicate key） --說明該記錄已經存在，報重復值錯誤
加S-lock --說明該記錄被標記為洗掉, 事務已經提交，還沒來得及purge
如果有鎖: 則加S-lock --說明該記錄被標記為洗掉，事務還未提交.

insert intension lock 有什么用呢？鎖的兼容矩陣是啥？

insert intension lock 是一種特殊的Gap lock，記住非常特殊哦
insert intension lock 和 insert intension lock 是兼容的，其次都是不兼容的
Gap lock 是為了防止insert， insert intension lock 是為了insert并發更快，兩者是有區別的
什么情況下會出發insert intension lock ？
當insert的記錄M的 next-record 加了Gap lock才會發生，record lock并不會觸發

參考資料

http://keithlan.github.io/2017/06/05/innodb_locks_1/
https://www.hhcycj.com/post/item/441.html
https://baijiahao.baidu.com/s?id=1744632613955812703&wfr=spider&for=pc

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/555809.html

標籤：其他

上一篇：全球唯一云廠商！華為云高分入選2023Gartner Peer Insights?云資料庫管理系統“客戶之選”

下一篇：返回列表

InnoDB鎖初探（一）：鎖分類和RR不同場景下的鎖機制