Mysql數(shù)據(jù)庫(kù)鎖（Innodb）

數(shù)據(jù)庫(kù)鎖是Mysql實(shí)現(xiàn)數(shù)據(jù)一致性的基礎(chǔ)之一，是在事務(wù)的基礎(chǔ)之上，基于Mysql Server層或存儲(chǔ)引擎層實(shí)現(xiàn)的。

鎖日志

前置條件：

set GLOBAL innodb_status_output=ON;  

set GLOBAL innodb_status_output_locks=ON;

查看語(yǔ)句：

show engine innodb status\G;

鎖分類

表鎖與行鎖

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

共享鎖與排他鎖

• 共享鎖

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

• 排他鎖

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

意向鎖

1. 意向鎖是表級(jí)的

2. 同樣具有意向共享鎖（IS）、意向排他鎖（IX）

3. TABLE LOCK table *** trx id *** lock mode IX、TABLE LOCK table *** trx id *** lock mode IS

4. 意向鎖不會(huì)與行級(jí)鎖沖突，并且意向鎖之間沒(méi)有互斥關(guān)系

5. 意向鎖的意義是用于協(xié)調(diào)表鎖與行鎖之間的互斥關(guān)系，確保事務(wù)可以正確的請(qǐng)求和釋放鎖。如果沒(méi)有意向鎖，當(dāng)對(duì)全表加鎖時(shí)，需要遍歷全表，判斷是否存在某些行記錄被加了行鎖，那么這個(gè)加表鎖的操作的性能會(huì)差很多。有了意向鎖，A事務(wù)對(duì)某行記錄加鎖時(shí)會(huì)先申請(qǐng)意向鎖，申請(qǐng)成功后再加行鎖，加鎖成功后，B事務(wù)申請(qǐng)表級(jí)鎖時(shí)會(huì)先判斷表上面的意向鎖是否兼容。

6. 意向共享鎖（IS鎖）：事務(wù)在請(qǐng)求S鎖之前，先獲取IS鎖

   意向排他鎖（IX鎖）：事務(wù)在請(qǐng)求X鎖之前，先獲取IX鎖

7. 兼容性：

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

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

記錄鎖（Record Lock）

1. 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
2. 基于索引創(chuàng)建的，受索引的影響
3. 同樣具有共享、排他的區(qū)別

間隙鎖（Gap Lock）

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

臨鍵鎖（Next-Key Locks）

1. 記錄鎖、間隙鎖的組合就是臨鍵鎖
2. 臨鍵鎖是申請(qǐng)鎖時(shí)，默認(rèn)先申請(qǐng)的鎖類型，如果申請(qǐng)失敗，則進(jìn)行降級(jí)，將為間隙鎖或記錄鎖
3. 不僅鎖住記錄，還會(huì)鎖住間隙，比如(-∞,10],(10,15]等區(qū)間，前開后閉區(qū)間
4. RECORD LOCKS *** index idx_c of table *** trx id *** lock_mode X

插入意向鎖（Insert Intention Locks）

1. RECORD LOCKS *** index PRIMARY of table *** trx id *** lock_mode X insert intention waiting

2. 插入意向鎖可以理解為特殊的Gap鎖的一種，用來(lái)提高并發(fā)寫的性能。當(dāng)遇到主鍵或唯一鍵沖突時(shí)，會(huì)退化為讀鎖

3. 插入意向鎖和插入意向鎖之間不會(huì)互斥（只要記錄本身唯一鍵、主鍵不沖突）。

   示例：

   事務(wù)A插入數(shù)據(jù)27時(shí)，獲取到的是（25,30）的間隙鎖和27的行鎖，事務(wù)B插入數(shù)據(jù)28時(shí)，獲取到的也是（25,30）的間隙鎖和28的行鎖。

   因?yàn)樾墟i27和行鎖28不是同一行，所以不會(huì)沖突，然后兩個(gè)事務(wù)獲取到的插入意向鎖不會(huì)互相排斥，所以可以插入成功。

自增鎖（AUTO-INC Locks）

1. TABLE LOCK table *** trx id *** lock mode AUTO-INC waiting
2. 自增鎖處于表級(jí)別的鎖

元數(shù)據(jù)鎖（metadata lock）

1. Server層實(shí)現(xiàn)的鎖，與引擎層無(wú)關(guān)

2. 執(zhí)行select時(shí)，如果有ddl語(yǔ)句，那么ddl會(huì)被阻塞（非online ddl），因?yàn)閟elect語(yǔ)句有metadata lock，防止元數(shù)據(jù)被改掉。

   非online ddl的問(wèn)題：

   select操作會(huì)先獲取 元數(shù)據(jù)共享鎖（shared MDL），而DDL 操作會(huì)先獲取元數(shù)據(jù)排他鎖（exclusive MDL）, 而且不僅僅是先select后ddl 導(dǎo)致ddl阻塞，

   而且ddl后面的select也會(huì)被阻塞，因?yàn)閐dl申請(qǐng)?jiān)獢?shù)據(jù)排他鎖的優(yōu)先級(jí)要比select操作的優(yōu)先級(jí)要高，只有ddl操作完成后，后面的select才會(huì)順利獲取元數(shù)據(jù)共享鎖，才能繼續(xù)執(zhí)行，因此ddl操作的花費(fèi)是昂貴的，因此才出現(xiàn)online DDL；

鎖解讀

RR下的有二級(jí)索引的情況

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

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

對(duì)a表的idx_c二級(jí)索引加臨鍵鎖
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
二級(jí)索引idx_c的7加鎖
 0: len 4; hex 80000007; asc     ;;
主鍵索引上的3也會(huì)被加鎖
 1: len 4; hex 80000003; asc     ;;

對(duì)主鍵索引加記錄鎖，對(duì)3進(jìn)行加鎖
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
第一個(gè)字段是主鍵,被加鎖
 0: len 4; hex 80000003; asc     ;;
最近一次被更新的事務(wù)id
 1: len 6; hex 000000009c29; asc      );;
回滾指針
 2: len 7; hex be00000147011c; asc     G  ;;
該行第2、3、4、5個(gè)字段
 3: len 4; hex 80000005; asc     ;;
 4: len 4; hex 80000007; asc     ;;
 5: len 4; hex 80000009; asc     ;;
 6: len 8; hex 8000000000000000; asc         ;;

對(duì)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
二級(jí)索引idx_c上對(duì)9加鎖
0: len 4; hex 80000009; asc     ;;
主鍵索引上的5也會(huì)被鎖住
 1: len 4; hex 80000005; asc     ;;

RR下的無(wú)二級(jí)索引的情況

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

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

由于表定義沒(méi)有顯示的索引，而InnoDB又是索引組織表，會(huì)自動(dòng)創(chuàng)建一個(gè)索引，這里面叫index GEN_CLUST_INDEX  
由于沒(méi)有索引，那么會(huì)對(duì)每條記錄都加上臨鍵鎖
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 指的是頁(yè)里面的最后一條記錄（偽記錄，通過(guò)select查不到，并不是真實(shí)記錄）；還有Infimum表示頁(yè)面中的第一個(gè)記錄（偽記錄）
通過(guò)supremum 鎖住index GEN_CLUST_INDEX的最大值到正無(wú)窮大的區(qū)間，這樣就可以鎖住全部記錄，以及全部間隙，相當(dāng)于表鎖
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     ;;

鎖算法

自增鎖

? 自增列的維護(hù)與數(shù)據(jù)的新增有關(guān)，任何產(chǎn)生新數(shù)據(jù)的語(yǔ)句都可以稱為”Insert like“，大致分3種，分別是simple insert、bulk inserts、mixed-mode inserts

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

bulk inserts：插入的記錄行數(shù)不能馬上確定的，比如：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

死鎖

自增鎖死鎖常常出現(xiàn)在數(shù)據(jù)遷移過(guò)程中。常見的數(shù)據(jù)遷移大多以雙寫來(lái)實(shí)現(xiàn)，類似一個(gè)進(jìn)程負(fù)責(zé)從舊表往新表寫（insert ... select ...），而應(yīng)用程序則繼續(xù)往新表寫，此時(shí)新表可能會(huì)發(fā)生死鎖。

鎖模式

• innodb_autoinc_lock_mode = 0 (“traditional” lock mode)

  任何一種insert-like語(yǔ)句，都會(huì)產(chǎn)生一個(gè)表級(jí)的自增鎖，性能差，但是足夠安全

• innodb_autoinc_lock_mode = 1 (“consecutive” lock mode)

? 這是默認(rèn)的鎖模式，當(dāng)發(fā)生bulk inserts時(shí)，會(huì)產(chǎn)生一個(gè)表級(jí)的自增鎖直到語(yǔ)句執(zhí)行結(jié)束，注意不是事務(wù)結(jié)束。對(duì)于simple insert，則使用輕量鎖，只要獲取相應(yīng)的auto increment就釋放鎖，不會(huì)等待語(yǔ)句結(jié)束。當(dāng)表被加上自增鎖后，這種輕量鎖不會(huì)加鎖成功，會(huì)等待。

? 優(yōu)點(diǎn)是性能較好，缺點(diǎn)還是會(huì)產(chǎn)生表級(jí)的自增鎖，因?yàn)橐ＷC自增id的連續(xù)性，防止bulk inserts時(shí)，被其他insert 語(yǔ)句搶走 auto increment值。

• innodb_autoinc_lock_mode = 2 (“interleaved” lock mode)

? 當(dāng)進(jìn)行bulk insert 時(shí)，不會(huì)產(chǎn)生表級(jí)別的自增鎖，因?yàn)樗窃试S其他insert 插入的，新增一條記錄，插入分配一個(gè)auto increment值，不會(huì)預(yù)分配。

? 優(yōu)點(diǎn)是性能較好，缺點(diǎn)是一次bulk inserts 產(chǎn)生的自增列并不是連續(xù)的，同時(shí)SBR模式下的主從復(fù)制可能會(huì)產(chǎn)生數(shù)據(jù)不一致錯(cuò)誤，該錯(cuò)誤可以通過(guò)將主從復(fù)制改為RBR模式。

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

優(yōu)化

如果binlog-format是row模式的，而且不關(guān)心一條bulk insert的自增列的值連續(xù)且提交順序與自增列值大小的順序一致，那么可以設(shè)置innodb_autoinc_lock_mode = 2 來(lái)提高性能

一條bulk insert 自增列是否連續(xù)有時(shí)候會(huì)影響分頁(yè)查詢，有時(shí)候?yàn)榱私鉀Q深分頁(yè)查詢問(wèn)題，會(huì)采用每次分頁(yè)查詢的最大值來(lái)進(jìn)行分頁(yè)，比如

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

當(dāng)id=101的記錄先提交，該記錄的值剛好是當(dāng)前頁(yè)的最大值，此時(shí)id=100數(shù)據(jù)被提交，那么下次分頁(yè)查詢會(huì)從101開始查詢，就會(huì)造成這次翻頁(yè)的數(shù)據(jù)存在缺失的情況。如果分頁(yè)查詢中包含oder by id的查詢或者有and create_time < (now() - INTERVAL 5 second)，那么可以通過(guò)往前翻頁(yè)來(lái)找到，但是歸根結(jié)底當(dāng)前分頁(yè)的數(shù)據(jù)需要等待100數(shù)據(jù)被提交后刷新分頁(yè)來(lái)解決。

通用鎖

• 鎖是在索引上實(shí)現(xiàn)的

• 假設(shè)有一個(gè)key，有5條記錄， 1，3，5，7，9. 如果where id<5 ， 那么鎖住的區(qū)間不是（-∞，5），而是(-∞,1],(1,3],(3,5] 多個(gè)區(qū)間組合而成；如果where id =5，那么鎖住的區(qū)間是(3,5],(5,9]

• next-key lock 降級(jí)為 record lock的情況:

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

  典型的案例：where primary_key = 1 (會(huì)降級(jí)), 而不是 where primary_key < 10 （由于返回的結(jié)果集不僅僅一條，那么不會(huì)降級(jí)）

• 上鎖，不僅僅對(duì)輔助索引加鎖，還需要對(duì)主鍵索引加鎖；不僅僅對(duì)主鍵索引加鎖，還需要對(duì)輔助索引加鎖

RR下的Update鎖機(jī)制

如果

1. select * from xx where col <比較運(yùn)算符> M for update
2. M->next-rec: 表示M的下一條記錄
3. M->pre-rec: 表示M的前一條記錄

第一輪總結(jié)

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

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

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

? [M], next-lock 降級(jí)為 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]

第二輪總結(jié)合并

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

最后的疑問(wèn)和總結(jié)

為什么要對(duì)M->next-rec 或者 M->pre-rec ？

因?yàn)闉榱朔乐够米x。

RR下的Insert鎖機(jī)制

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

1. 找到大于N的第一條記錄M
2. 如果M上面沒(méi)有g(shù)ap ， next-key locking的話，可以插入 ， 否則等待 (對(duì)其next-rec加insert intension lock，由于有g(shù)ap鎖，所以等待)

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

1. 找到大于N的第一條記錄M，以及前一條記錄P
2. 如果M上面沒(méi)有g(shù)ap ， next-key locking的話，進(jìn)入第三步驟 ， 否則等待(對(duì)其next-rec加insert intension lock，由于有g(shù)ap鎖，所以等待)
3. 檢查p：
   判斷p是否等于n：
   如果不等: 則完成插入（結(jié)束）
   如果相等：
   再判斷P 是否有鎖，
   如果沒(méi)有鎖:
   報(bào)1062錯(cuò)誤（duplicate key） --說(shuō)明該記錄已經(jīng)存在，報(bào)重復(fù)值錯(cuò)誤
   加S-lock --說(shuō)明該記錄被標(biāo)記為刪除, 事務(wù)已經(jīng)提交，還沒(méi)來(lái)得及purge
   如果有鎖: 則加S-lock --說(shuō)明該記錄被標(biāo)記為刪除，事務(wù)還未提交.

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

1. insert intension lock 是一種特殊的Gap lock，記住非常特殊哦
2. insert intension lock 和 insert intension lock 是兼容的，其次都是不兼容的
3. Gap lock 是為了防止insert， insert intension lock 是為了insert并發(fā)更快，兩者是有區(qū)別的
4. 什么情況下會(huì)出發(fā)insert intension lock ？
   當(dāng)insert的記錄M的 next-record 加了Gap lock才會(huì)發(fā)生，record lock并不會(huì)觸發(fā)

參考資料文章來(lái)源地址http://www.zghlxwxcb.cn/news/detail-493762.html

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

到了這里，關(guān)于InnoDB鎖初探（一）：鎖分類和RR不同場(chǎng)景下的鎖機(jī)制的文章就介紹完了。如果您還想了解更多內(nèi)容，請(qǐng)?jiān)谟疑辖撬阉鱐OY模板網(wǎng)以前的文章或繼續(xù)瀏覽下面的相關(guān)文章，希望大家以后多多支持TOY模板網(wǎng)！