歡迎光臨
每天分享高質量文章

MySQL InnoDB 鎖介紹,及不同 SQL 陳述句分別加什麼樣的鎖

(給資料分析與開發加星標,提升資料技能

來源:iceman1952

blog.csdn.net/iceman1952/article/details/85504278

本文中,我們詳細介紹MySQL InnoDB儲存引擎各種不同型別的鎖,以及不同SQL陳述句分別會加什麼樣的鎖。

 

閱讀提示

 

1、本文所參考的MySQL文件版本是8.0,做實驗的MySQL版本是8.0.13

 

2、本文主要參考了MySQL官方文件 InnoDB鎖定和事務機制

 

3、本文還參考了何登成的 MySQL加鎖處理分析、一個最不可思議的MySQL死鎖分析 以及阿裡雲RDS-資料庫內核組的 常用SQL陳述句的MDL加鎖原始碼分析

 

4、MySQL是插件式的表儲存引擎,資料庫的鎖是和儲存引擎相關的,本文討論的鎖都是InnoDB儲存引擎的鎖

 

文章正文開始

 

“加什麼樣的鎖”與以下因素相關

 

1、當前事務的隔離級別

 

2、SQL是一致性非鎖定讀(consistent nonlocking read)還是DML(INSERT/UPDATE/DELETE)或鎖定讀(locking read)

 

3、SQL執行時是否使用了索引,所使用索引的型別(主鍵索引,輔助索引、唯一索引)

 

我們先分別介紹這幾個因素

 

一、隔離級別(isolation level)

 

資料庫事務需要滿足ACID原則,“I”即隔離性,它要求兩個事務互不影響,不能看到對方尚未提交的資料。資料庫有4種隔離級別(isolation level),按著隔離性從弱到強(相應的,性能和併發性從強到弱)分別是

 

1、Read Uncommitted。下麵簡稱RU

2、Read Committed。下麵簡稱RC

3、Repeatable Read(MySQL的預設隔離級別)。下麵簡稱RR

4、Serializable

 

“I”即隔離性正是通過鎖機制來實現的。提到鎖就會涉及到死鎖,需要明確的是死鎖的可能性並不受隔離級別的影響,因為隔離級別改變的是讀操作的行為,而死鎖是由於寫操作產生的。

 

— 查看事務的 全域性和session 隔離級別( MySQL 5.7.19及之前使用tx_isolation)

 

select @@global.transaction_isolation, @@session.transaction_isolation;

 

— 設置 全域性 事務隔離級別為repeatable read

 

set global transaction isolation level repeatable read

 

— 設置 當前session 事務隔離級別為read uncommitted

 

set session transaction isolation level read uncommitted

 

事務隔離級別設置和查看的詳細語法請見:

https://dev.mysql.com/doc/refman/8.0/en/set-transaction.html

 

二、一致性非鎖定讀和鎖定讀

 

InnoDB有兩種不同的SELECT,即普通SELECT 和 鎖定讀SELECT。鎖定讀SELECT 又有兩種,即SELECT … FOR SHARE 和 SELECT … FOR UPDATE;鎖定讀SELECT 之外的則是 普通SELECT 。

 

不同的SELECT是否都需要加鎖呢?

 

1、普通SELECT 時使用一致性非鎖定讀,不加鎖;

 

2、鎖定讀SELECT 使用鎖定讀,加鎖;

 

3、此外,DML(INSERT/UPDATE/DELETE)時,需要先查詢表中的記錄,此時也使用鎖定讀,加鎖;

 

FOR SHARE 語法是 MySQL 8.0 時加入的,FOR SHARE 和 LOCK IN SHARE MODE 是等價的,但,FOR SHARE 用於替代 LOCK IN SHARE MODE,不過,為了向後兼容,LOCK IN SHARE MODE依然可用。

 

1、一致性非鎖定讀(consistent nonlocking read)

 

InnoDB採用多版本併發控制(MVCC, multiversion concurrency control)來增加讀操作的併發性。MVCC是指,InnoDB使用基於時間點的快照來獲取查詢結果,讀取時在訪問的表上不設置任何鎖,因此,在事務T1讀取的同一時刻,事務T2可以自由的修改事務T1所讀取的資料。這種讀操作被稱為一致性非鎖定讀。這裡的讀操作就是普通SELECT。

 

隔離級別為RU和Serializable時不需要MVCC,因此,只有RC和RR時,才存在MVCC,才存在一致性非鎖定讀。

 

一致性非鎖定讀在兩種隔離級別RC和RR時,是否有什麼不同呢?是的,兩種隔離級別下,拍得快照的時間點不同

 

1、RC時,同一個事務內的每一個一致性讀總是設置和讀取它自己的最新快照。也就是說,每次讀取時,都再重新拍得一個最新的快照(所以,RC時總是可以讀取到最新提交的資料)。

 

2、RR時,同一個事務內的所有的一致性讀 總是讀取同一個快照,此快照是執行該事務的第一個一致性讀時所拍得的。

 

2、鎖定讀(locking read)

 

如果你先查詢資料,然後,在同一個事務內 插入/更新 相關資料,普通的SELECT陳述句是不能給你足夠的保護的。其他事務可以 更新/刪除 你剛剛查出的資料行。InnoDB提供兩種鎖定讀,即:SELECT … FOR SHARE 和 SELECT … FOR UPDATE。它倆都能提供額外的安全性。

 

這兩種鎖定讀在搜索時所遇到的(註意:不是最終結果集中的)每一條索引記錄(index record)上設置排它鎖或共享鎖。此外,如果當前隔離級別是RR,它還會在每個索引記錄前面的間隙上設置排它的或共享的gap lock(排它的和共享的gap lock沒有任何區別,二者等價)。

 

看完背景介紹,我們再來看一下InnoDB提供的各種鎖。

 

三、InnoDB提供的8種不同型別的鎖

 

InnoDB一共有8種鎖型別,其中,意向鎖(Intention Locks)和自增鎖(AUTO-INC Locks)是表級鎖,剩餘全部都是行級鎖。此外,共享鎖或排它鎖(Shared and Exclusive Locks)儘管也作為8種鎖型別之一,它卻並不是具體的鎖,它是鎖的樣式,用來“修飾”其他各種型別的鎖。

 

MySQL5.7及之前,可以通過information_schema.innodb_locks查看事務的鎖情況,但,只能看到阻塞事務的鎖;如果事務並未被阻塞,則在該表中看不到該事務的鎖情況。

 

MySQL8.0刪除了information_schema.innodb_locks,添加了performance_schema.data_locks,可以通過performance_schema.data_locks查看事務的鎖情況,和MySQL5.7及之前不同,performance_schema.data_locks不但可以看到阻塞該事務的鎖,還可以看到該事務所持有的鎖,也就是說即使事務並未被阻塞,依然可以看到事務所持有的鎖(不過,正如文中最後一段所說,performance_schema.data_locks並不總是能看到全部的鎖)。表名的變化其實還反映了8.0的performance_schema.data_locks更為通用了,即使你使用InnoDB之外的儲存引擎,你依然可以從performance_schema.data_locks看到事務的鎖情況。

 

performance_schema.data_locks的列LOCK_MODE表明瞭鎖的型別,下麵在介紹各種鎖時,我們同時指出鎖的LOCK_MODE。

 

1、共享鎖或排它鎖(Shared and Exclusive Locks)

 

它並不是一種鎖的型別,而是其他各種鎖的樣式,每種鎖都有shard或exclusive兩種樣式。

 

當我們說到共享鎖(S鎖)或排它鎖(X鎖)時,一般是指行上的共享鎖或者行上的排它鎖。需要註意的是,表鎖也存在共享鎖和排它鎖,即表上的S鎖和表上的X鎖,表上的鎖除了這兩種之外,還包括下麵將會提到的意向共享鎖(Shard Intention Locks)即IS鎖、意向排它鎖(Exclusive Intention Locks)即IX鎖。表上的鎖,除了這四種之外,還有其他型別的鎖,這些鎖都是在訪問表的元信息時會用到的(create table/alter table/drop table等),本文不討論這些鎖,詳細可見:常用SQL陳述句的MDL加鎖原始碼分析。

 

資料行r上共享鎖(S鎖)和排它鎖(X鎖)的兼容性如下:

 

假設T1持有資料行r上的S鎖,則當T2請求r上的鎖時:

 

1、T2請求r上的S鎖,則,T2立即獲得S鎖。T1和T2同時都持有r上的S鎖。

 

2、T2請求r上的X鎖,則,T2無法獲得X鎖。T2必須要等待直到T1釋放r上的S鎖。

 

假設T1持有r上的X鎖,則當T2請求r上的鎖時:

 

T2請求r上的任何型別的鎖時,T2都無法獲得鎖,此時,T2必須要等待直到T1釋放r上的X鎖

 

2、意向鎖(Intention Locks)

 

表鎖。含義是已經持有了表鎖,稍候將獲取該表上某個/些行的行鎖。有shard或exclusive兩種樣式。

 

LOCK_MODE分別是:IS或IX。

 

意向鎖用來鎖定層級資料結構,獲取子層級的鎖之前,必須先獲取到父層級的鎖。可以這麼看InnoB的層級結構:InnoDB所有資料是schema的集合,schema是表的集合,表是行的集合。意向鎖就是獲取子層級(資料行)的鎖之前,需要首先獲取到父層級(表)的鎖。

 

意向鎖的目的是告知其他事務,某事務已經鎖定了或即將鎖定某個/些資料行。事務在獲取行鎖之前,首先要獲取到意向鎖,即:

 

1、事務在獲取行上的S鎖之前,事務必須首先獲取 表上的 IS鎖或表上的更強的鎖。

 

2、事務在獲取行上的X鎖之前,事務必須首先獲取 表上的 IX鎖。

 

事務請求鎖時,如果所請求的鎖 與 已存在的鎖兼容,則該事務 可以成功獲得 所請求的鎖;如果所請求的鎖 與 已存在的鎖衝突,則該事務 無法獲得 所請求的鎖。

 

表級鎖(table-level lock)的兼容性矩陣如下:

 

 

對於上面的兼容性矩陣,一定註意兩點:

 

1、在上面的兼容性矩陣中,S是表的(不是行的)共享鎖,X是表的(不是行的)排它鎖。

 

2、意向鎖IS和IX 和任何行鎖 都兼容(即:和行的X鎖或行的S鎖都兼容)。

 

所以,意向鎖只會阻塞 全表請求(例如:LOCK TABLES … WRITE),不會阻塞其他任何東西。因為LOCK TABLES … WRITE需要設置X表鎖,這會被意向鎖IS或IX所阻塞。

 

InnoDB允許表鎖和行鎖共存,使用意向鎖來支持多粒度鎖(multiple granularity locking)。意向鎖如何支持多粒度鎖呢,我們舉例如下

 

T1: SELECT * FROM t1 WHERE i=1 FOR UPDATE;

 

T2: LOCK TABLE t1 WRITE;

 

T1執行時,需要獲取i=1的行的X鎖,但,T1獲取行鎖前,T1必須先要獲取t1表的IX鎖,不存在衝突,於是T1成功獲得了t1表的IX鎖,然後,又成功獲得了i=1的行的X鎖;T2執行時,需要獲取t1表的X鎖,但,T2發現,t1表上已經被設置了IX鎖,因此,T2被阻塞(因為表的X鎖和表的IX鎖不兼容)。

 

假設不存在意向鎖,則:

 

T1執行時,需要獲取i=1的行的X鎖(不需要獲取t1表的意向鎖了);T2執行時,需要獲取t1表的X鎖,T2能否獲取到T1表的X鎖呢?T2無法立即知道,T2不得不遍歷表t1的每一個資料行以檢查,是否某個行上已存在的鎖和自己即將設置的t1表的X鎖衝突,這種的判斷方法效率實在不高,因為需要遍歷整個表。

 

所以,使用意向鎖,實現了“表鎖是否衝突”的快速判斷。意向鎖就是協調行鎖和表鎖之間的關係的,或者也可以說,意向鎖是協調表上面的讀寫鎖和行上面的讀寫鎖(也就是不同粒度的鎖)之間的關係的。

 

3、索引記錄鎖(Record Locks)

 

也就是所謂的行鎖,鎖定的是索引記錄。行鎖就是索引記錄鎖,所謂的“鎖定某個行”或“在某個行上設置鎖”,其實就是在某個索引的特定索引記錄(或稱索引條目、索引項、索引入口)上設置鎖。有shard或exclusive兩種樣式。

 

LOCK_MODE分別是:S,REC_NOT_GAP或X,REC_NOT_GAP。

 

行鎖就是索引記錄鎖,索引記錄鎖總是鎖定索引記錄,即使表上並未定義索引。表未定義索引時,InnoDB自動創建隱藏的聚集索引(索引名字是GEN_CLUST_INDEX),使用該索引執行record lock。

 

4、間隙鎖(Gap Locks)

 

索引記錄之間的間隙上的鎖,鎖定尚未存在的記錄,即索引記錄之間的間隙。有shard或exclusive兩種樣式,但,兩種樣式沒有任何區別,二者等價。

 

LOCK_MODE分別是:S,GAP或X,GAP。

 

gap lock可以共存(co-exist)。事務T1持有某個間隙上的gap lock 並不能阻止 事務T2同時持有 同一個間隙上的gap lock。shared gap lock和exclusive gap lock並沒有任何的不同,它倆並不衝突,它倆執行同樣的功能。

 

gap lock鎖住的間隙可以是第一個索引記錄前面的間隙,或相鄰兩條索引記錄之間的間隙,或最後一個索引記錄後面的間隙。

 

索引是B+樹組織的,因此索引是從小到大按序排列的,在索引記錄上查找給定記錄時,InnoDB會在第一個不滿足查詢條件的記錄上加gap lock,防止新的滿足條件的記錄插入。

 

 

上圖演示了:InnoDB在索引上掃描時,找到了c2=11的記錄,然後,InnoDB接著掃描,它發現下一條記錄是c2=18,不滿足條件,InnoDB遇到了第一個不滿足查詢條件的記錄18,於是InnoDB在18上設置gap lock,此gap lock鎖定了區間(11, 18)。

 

為什麼需要gap lock呢?gap lock存在的唯一目的就是阻止其他事務向gap中插入資料行,它用於在隔離級別為RR時,阻止幻影行(phantom row)的產生;隔離級別為RC時,搜索和索引掃描時,gap lock是被禁用的,只在 外鍵約束檢查 和 重覆key檢查時gap lock才有效,正是因為此,RC時會有幻影行問題。

 

gap lock是如何阻止其他事務向gap中插入資料行的呢?看下圖

 

 

索引是B+樹組織的,因此索引是從小到大按序排列的,如果要插入10,那麼能插入的位置只能是上圖中標紅的區間。在10和10之間插入時,我們就認為是插入在最後面的10的後面。如果封鎖了標紅的區間,那麼其他事務就無法再插入10啦。

 

問題一:當T2要插入 10時,上圖哪些地方允許插入(註意:索引是有序的哦)?

 

答:(8, 10)和(10,11)。在10和10之間插入,我們就認為是插入在最後的10後面。

 

只要封鎖住圖中標紅的區間,T2就無法再插入10啦。上面這兩個區間有什麼特點嗎?對,這兩個區間就是:滿足條件的每一條記錄前面的間隙,及,最後一條不滿足條件的記錄前面的間隙。InnoDB使用下一個鍵鎖(Next-Key Locks)或間隙鎖(Gap Locks)來封鎖這種區間。

 

問題二:gap lock是用來阻塞插入新資料行的,那麼,T2, insert into g values(‘z’, 9) 會被阻塞嗎?插入(‘z’, 8),(‘z’, 10),(‘z’, 11)呢?

 

答:上圖中,T1的update設置的gap lock是 (8, 10)和(10,11),而,insert intention lock的範圍是(插入值, 向下的一個索引值)。insert intention lock的詳細介紹請見下麵的6. 插入意向鎖(Insert Intention Locks)。

 

於是,對於上面這些插入值,得到的insert intention lock如下:

 

插入 (‘z’, 8)時,insert intention lock 是 (8, 10)       —  衝突,與gap lock (8, 10)重疊了

 

插入 (‘z’, 9)時,insert intention lock 是 (9, 10)       —  衝突,與gap lock (8, 10)重疊了

 

插入 (‘z’, 10)時,insert intention lock 是 (10, 11)   —  衝突,與gap lock (10, 11)重疊了

 

插入 (‘z’, 11)時,insert intention lock 是 (11, 15)   —  不衝突

 

事實是不是這樣呢,看下圖

 

 

是的,和我們分析的一致,為了看的更清楚,我們把結果列成圖表如下

 

 

問題三:“gap是解決phantom row問題的”,插入會導致phantom row,但更新也一樣也會產生phantom row啊。

 

 

例如,上圖的T1和T2,T1把所有i=8的行更新為108,T2把i=15的行更新為8,如果T2不被阻塞,T1的WHERE條件豈不是多出了一行,即:T1出現了phantom row?

 

答:nice question。我們自己來分析下T1和T2分別加了哪些鎖

 

T1加的鎖:idx_i上的next-key lock (5, 8],PRIMARY上的’b’,以及idx_i上的gap lock (8,10)

 

T2加的鎖:idx_i上的next-key lock (11, 15],PRIMARY上的’f’,以及idx_i上的gap lock (15,108),最後這個gap lock是因為T1在idx_i上加了新值108

 

根據上面的分析,T1和T2的鎖並沒有重疊,即我們分析的結果是:T2不會被阻塞。

 

但,上圖清楚的表明T2確實被阻塞了,原因竟然是:T2 insert intention lock和T1 gap lock(8, 10)衝突了。很奇怪,T2是更新陳述句,為什麼會有insert intention lock呢?

 

我不知道確切的原因,因為我沒找到文件說這事。根據我的推斷,update … set 成功找到結果集然後執行更新時,在即將被更新進入行的新值上設置了insert intention lock(如果找不到結果集,則就不存在insert intention lock啦),因此,T2在idx_i上的新值8上設置了insert intention lock(8, 10)。最終,T2 insert intention lock(8, 10) 與 T1 gap lock(8, 10)衝突啦,T2被阻塞。

 

因此,update … set 成功找到結果集時,會在即將被更新進入行的新值上設置 index record lock 以及 insert intention lock。如前所述,insert intention lock的範圍是(插入值,下一個值),如果T2是 update g set i=9 where i=15; 那麼update … set 所設置的新值是9,則T2 insert intention lock就是(9, 10)啦,它依然會和 T1 gap lock(8, 10)衝突,是這樣嗎?確實是的,感興趣的同學可以試試。

 

5、下一個鍵鎖(Next-Key Locks)

 

next-key lock 是 (索引記錄上的索引記錄鎖) + (該索引記錄前面的間隙上的鎖) 二者的合體,它鎖定索引記錄以及該索引記錄前面的間隙。有shard或exclusive兩種樣式。

 

LOCK_MODE分別是:S或X。

 

當InnoDB 搜索或掃描索引時,InnoDB在它遇到的索引記錄上所設置的鎖就是next-key lock,它會鎖定索引記錄本身以及該索引記錄前面的gap(“gap” immediately before that index record)。即:如果事務T1 在索引記錄r 上有一個next-key lock,則T2無法在 緊靠著r 前面的那個間隙中 插入新的索引記錄(gap immediately before r in the index order)。

 

next-key lock還會加在“supremum pseudo-record”上,什麼是supremum pseudo-record呢?它是索引中的偽記錄(pseudo-record),代表此索引中可能存在的最大值,設置在supremum pseudo-record上的next-key lock鎖定了“此索引中可能存在的最大值”,以及 這個值前面的間隙,“此索引中可能存在的最大值”在索引中是不存在的,因此,該next-key lock實際上鎖定了“此索引中可能存在的最大值”前面的間隙,也就是此索引中當前實際存在的最大值後面的間隙。例如,下圖中,supremum pseudo-record上的next-key lock鎖定了區間(18, 正無窮),正是此next-key lock阻止其他事務插入例如19, 100等更大的值。

 

supremum pseudo-record上的next-key lock鎖定了“比索引中當前實際存在的最大值還要大”的那個間隙,“比大還大”,“bigger than bigger”

 

 

6、插入意向鎖(Insert Intention Locks)

 

一種特殊的gap lock。INSERT操作插入成功後,會在新插入的行上設置index record lock,但,在插入行之前,INSERT操作會首先在索引記錄之間的間隙上設置insert intention lock,該鎖的範圍是(插入值, 向下的一個索引值)。有shard或exclusive兩種樣式,但,兩種樣式沒有任何區別,二者等價。

 

LOCK_MODE分別是:S,GAP,INSERT_INTENTION或X,GAP,INSERT_INTENTION。

 

insert intention lock發出按此方式進行插入的意圖:多個事務向同一個index gap併發進行插入時,多個事務無需相互等待。

 

假設已存在值為4和7的索引記錄,事務T1和T2各自嘗試插入索引值5和6,在得到被插入行上的index record lock前,倆事務都首先設置insert intention lock,於是,T1 insert intention lock (5, 7),T2 insert intention lock (6, 7),儘管這兩個insert intention lock重疊了,T1和T2並不互相阻塞。

 

如果gap lock或next-key lock 與 insert intention lock 的範圍重疊了,則gap lock或next-key lock會阻塞insert intention lock。隔離級別為RR時正是利用此特性來解決phantom row問題;儘管insert intention lock也是一種特殊的gap lock,但它和普通的gap lock不同,insert intention lock相互不會阻塞,這極大的提供了插入時的併發性。總結如下:

 

1、gap lock會阻塞insert intention lock。事實上,gap lock的存在只是為了阻塞insert intention lock

 

2、gap lock相互不會阻塞

 

3、insert intention lock相互不會阻塞

 

4、insert intention lock也不會阻塞gap lock

 

INSERT插入行之前,首先在索引記錄之間的間隙上設置insert intention lock,操作插入成功後,會在新插入的行上設置index record lock。

 

我們用下麵三圖來說明insert intention lock的範圍和特性

 

 

上圖演示了:T1設置了gap lock(13, 18),T2設置了insert intention lock(16, 18),兩個鎖的範圍重疊了,於是T1 gap lock(13, 18)阻塞了T2 insert intention lock(16, 18)。

 

 

上圖演示了:T1設置了insert intention lock(13, 18)、index record lock 13;T2設置了gap lock(17, 18)。儘管T1 insert intention lock(13, 18) 和 T2 gap lock(17, 18)重疊了,但,T2並未被阻塞。因為 insert intention lock 並不阻塞 gap lock。

 

 

上圖演示了:T1設置了insert intention lock(11, 18)、index record lock 11;T2設置了next-key lock(5, 11]、PRIMARY上的index record lock ‘b’、gap lock(11, 18)。此時:T1 index record lock 11 和 T2 next-key lock(5, 11]衝突了,因此,T2被阻塞。

 

7、自增鎖(AUTO-INC Locks)

 

表鎖。向帶有AUTO_INCREMENT列 的表時插入資料行時,事務需要首先獲取到該表的AUTO-INC表級鎖,以便可以生成連續的自增值。插入陳述句開始時請求該鎖,插入陳述句結束後釋放該鎖(註意:是陳述句結束後,而不是事務結束後)。

 

你可能會想,日常開發中,我們所有表都使用AUTO_INCREMENT作主鍵,所以會非常頻繁的使用到該鎖。不過,事情可能並不像你想的那樣。在介紹AUTO-INC表級鎖之前,我們先來看下和它密切相關的SQL陳述句以及系統變數innodb_autoinc_lock_mode

 

INSERT-like陳述句

 

1、insert

2、insert … select

3、replace

4、replace … select

5、load data

 

外加,simple-inserts, bulk-inserts, mixed-mode-inserts

 

simple-inserts

 

待插入記錄的條數,提前就可以確定(陳述句初始被處理時就可以提前確定)因此所需要的自增值的個數也就可以提前被確定。

 

包括:不帶嵌入子查詢的 單行或多行的insert, replace。不過,insert … on duplicate key update不是

 

bulk-inserts

 

待插入記錄的條數,不能提前確定,因此所需要的自增值的個數 也就無法提前確定

 

包括:insert … select, replace … select, load data

 

在這種情況下,InnoDB只能每次一行的分配自增值。每當一個資料行被處理時,InnoDB為該行AUTO_INCREMENT列分配一個自增值

 

mixed-mode-inserts

 

也是simple-inserts陳述句,但是指定了某些(非全部)自增列的值。也就是說,待插入記錄的條數提前能知道,但,指定了部分的自增列的值。

 

INSERT INTO t1 (c1,c2) VALUES (1,’a’), (NULL,’b’), (5,’c’), (NULL,’d’);

 

INSERT … ON DUPLICATE KEY UPDATE也是mixed-mode,最壞情況下,它就是INSERT緊跟著一個UPDATE,此時,為AUTO_INCREMENT列所分配的值在UPDATE階段可能用到,也可能用不到。

 

再看一下系統變數innodb_autoinc_lock_mode,它有三個候選值0,1,和2

 

8.0.3之前,預設值是1,即“連續性的鎖定樣式(consecutive lock mode)”;8.0.3及之後預設值是2,即“交織性鎖定樣式(interleaved lock mode)”

 

a. 當innodb_autoinc_lock_mode=0時,INSERT-like陳述句都需要獲取到AUTO-INC表級鎖;

 

b. 當innodb_autoinc_lock_mode=1時,如果插入行的條數可以提前確定,則無需獲得AUTO-INC表級鎖;如果插入行的條數無法提前確定,則就需要獲取AUTO-INC表級鎖。因此,simple-inserts和mixed-mode inserts都無需AUTO-INC表級鎖,此時,使用輕量級的mutex來互斥獲得自增值;bulk-inserts需要獲取到AUTO-INC表級鎖;

 

c. 當innodb_autoinc_lock_mode=2時,完全不再使用AUTO-INC表級鎖;

 

我們生產資料庫版本是5.6.23-72.1,innodb_autoinc_lock_mode=1,而且,我們日常開發中用到大都是simple-inserts,此時根本就不使用AUTO-INC表級鎖,所以,AUTO-INC表級鎖用到的並不多哦。

 

LOCK_MODE:AUTO-INC表級鎖用到的並不多,且,AUTO-INC鎖是在陳述句結束後被釋放,較難在performance_schema.data_locks中查看到,因此,沒有進行捕獲。感興趣的同學可以使用INSERT … SELECT捕獲試試。

 

8、空間索引(Predicate Locks for Spatial Indexes)

 

我們平時很少用到MySQL的空間索引。所以,本文忽略此型別的鎖 

 

到此為止,MySQL InnoDB 8種型別的鎖我們就介紹完了。我們以一個例子結束8種型別的介紹。

 

 

T1先執行,事務ID是8428;T2後執行,事務ID是8429

 

上圖演示了:

 

1、任何事務,在鎖定行之前,都需要先加表級鎖intention lock,即:第三行的IX和第一行的IX。

 

2、idx_c是輔助索引,InnoDB掃描idx_c時遇到了c=222,於是,在idx_c上加了next-key lock,即:第四行的X。next-key lock就是 index record lock+gap lock,於是此next-key lock鎖定了idx_c上值為222的索引記錄,以及222前面的間隙,也就是間隙(22, 222)。

 

3、idx_c是輔助索引,在主鍵索引之外的任何索引上加index record lock時,都需要在該行的主鍵索引上再加index record lock,於是,又在PRIMARY上添加了index record lock,即:第五行的X,REC_NOT_GAP。

 

4、InnoDB掃描完c=222後,又掃描到了c=2222,這是idx_c上,第一個不滿足索引掃描條件的索引記錄,於是InnoDB在c=2222上加gap lock,c=2222上的gap lock鎖定的範圍是“idx_c上2222前面的間隙”,這本應該是(222, 2222),但,T1即將在idx_c上插入c=224,於是,c=2222上的gap lock鎖定的範圍是(224, 2222)。即:第六行的X,GAP。

 

5、InnoDB即將在idx_c上插入c=224,224也是不滿足c=222的,於是InnoDB在c=224上加gap lock,該gap lock鎖定了224前面的間隙,也就是(222, 224),即,第七行的X,GAP。

 

6、T2執行INSERT成功後,會在新插入行的加index record lock,但,T2在插入之前,首先要作的是得到表級鎖intention lock以及設置表的每個索引的insert intention lock,該鎖的範圍是(插入值, 向下的一個索引值),於是,在設置idx_c上的insert intention lock範圍就是(226, 2222),這個範圍和事務T1第六行gap lock範圍(224, 2222)重疊。於是,事務T2被阻塞了,T2必須等待,直到T1釋放第六行的gap lock。

 

performance_schema.data_locks表中並不能看到T2的全部鎖,比如,T2也得在iux_b上設置insert intention lock,但,performance_schema.data_locks中並沒有這個鎖。關於performance_schema.data_locks中顯示了哪些鎖,請見本文最後一段。

 

把這些鎖及其範圍列出來如下圖所示

 

 

四、不同的SQL加了什麼樣的鎖?

 

OK,我們已經瞭解了InnoDB各種不同型別的鎖,那麼,不同SQL陳述句各加了什麼樣的鎖呢

 

我們用最朴素的想法來思考一下,用鎖作什麼呢?鎖要作的就是達到事務隔離的目的,即:兩個併發執行的事務T1和T2,如果T1正在修改某些行,那麼,T2要併發 讀取/修改/插入 滿足T1查詢條件的行時,T2就必須被阻塞,這是鎖存在的根本原因。index record lock, gap lock, next-key lock都是實現手段,這些手段使得鎖既能達到目的,還能實現最大的併發性。所以,當我們考慮事務T1中的SQL上加了什麼鎖時,就想一下,當T1執行時,如果併發的事務 T2不會觸及到T1的行,則T2無需被阻塞,如果T2的要 讀取/修改/插入 滿足T1條件的行時,T2就得被T1阻塞。而T1阻塞T2的具體實現就是:T1在已存在的行上加index record lock使得T2無法觸碰已存在的行,以及,T1在不存在的行上加gap lock使得T2無法插入新的滿足條件的行。

 

前面我們說過“加什麼樣的鎖”與以下因素相關

 

1、當前事務的隔離級別

 

2、SQL是一致性非鎖定讀(consistent nonlocking read)還是DML或鎖定讀(locking read)

 

3、SQL執行時是否使用了索引,所使用索引的型別(主鍵索引,輔助索引、唯一索引)

 

我們來看一下,不同的隔離級別下,使用不同的索引時,分別加什麼鎖。在討論之前,我們先剔除無需討論的情況

 

首先,普通SELECT 使用一致性非鎖定讀,因此根本不存在鎖。無需討論;

 

再者,作為開發者,我們幾乎從來不會使用到隔離級別RU和Serializable。這兩個隔離級別無需討論。

 

於是,剩下的就是 給定鎖定讀SELECT或DML(INSERT/UPDATE/DELETE)陳述句,在不同隔離級別下,使用不同型別的索引時,分別會加什麼樣的鎖?直接給出答案,其加鎖原則如下

 

一、RR時,如果使用非唯一索引進行搜索或掃描,則在所掃描的每一個索引記錄上都設置next-key lock。

 

這裡“所掃描的每一個索引記錄”是指當掃描執行計劃中所使用的索引時,搜索遇到的每一條記錄。WHERE條件是否排除掉某個資料行並沒有關係,InnoDB並不記得確切的WHERE條件,InnoDB倔強的只認其掃描的索引範圍(index range) 。

 

你可能覺得InnoDB在設置鎖時蠻不講理,竟然不管WHERE條件排除掉的某些行,這不是大大增加了鎖的範圍了嘛。不過,等我們瞭解了MySQL執行SQL時的流程,這就好理解了。MySQL的執行計劃只會選擇一個索引,使用一個索引來進行掃描,MySQL執行SQL陳述句的流程是,先由InnoDB引擎執行索引掃描,然後,把結果傳回給MySQL服務器,MySQL服務器會再對該索引條件之外的其他查詢條件進行求值,從而得到最終結果集,而加鎖時只考慮InnoDB掃描的索引,由MySQL服務器求值的其他WHERE條件並不考慮。當然,MySQL使用index_merge優化時會同時使用多個索引的,不過,這個時候設置鎖時也並不特殊,同樣,對於所用到的每一個索引,InnoDB在所掃描的每一個索引記錄上都設置next-key lock。

 

加的鎖一般是next-key lock,這種鎖住了索引記錄本身,還鎖住了每一條索引記錄前面的間隙,從而阻止其他事務 向 索引記錄前面緊接著的間隙中插入記錄。

 

如果在搜索中使用了輔助索引(secondary index),並且在輔助索引上設置了行鎖,則,InnoDB還會在 相應的 聚集索引 上設置鎖;表未定義聚集索引時,InnoDB自動創建隱藏的聚集索引(索引名字是GEN_CLUST_INDEX),當需要在聚集索引上設置鎖時,就設置到此自動創建的索引上。

 

二、RR時,如果使用了唯一索引的唯一搜索條件,InnoDB只在滿足條件的索引記錄上設置index record lock,不鎖定索引記錄前面的間隙;如果用唯一索引作範圍搜索,依然會鎖定每一條被掃描的索引記錄前面的間隙,並且再在聚集索引上設置鎖。

 

三、RR時,在第一個不滿足搜索條件的索引記錄上設置gap lock或next-key lock。

 

一般,等值條件時設置gap lock,範圍條件時設置next-key lock。此gap lock或next-key lock鎖住第一個不滿足搜索條件的記錄前面的間隙。

 

四、RR時,INSERT在插入新行之前,必須首先為表上的每個索引設置insert intention lock。

 

每個insert intention lock的範圍都是(待插入行的某索引列的值, 此索引上從待插入行給定的值向下的第一個索引值)。只有當insert intention lock與某個gap lock或next-key lock衝突時,才能在performance_schema.data_locks看到insert intention lock。

 

五、RC時,InnoDB只在完全滿足WHERE條件的行上設置index record lock。

 

六、RC時,禁用了gap lock。

 

正因為此,RC時不存在gap lock或next-key lock。這是為什麼呢?我們想一想啊,gap lock是用來解決phantom row問題的,gap lock封鎖的區間內不能插入新的行,因為插入時的insert intention lock會和gap lock衝突,從而阻止了新行的插入。但,隔離級別RC是允許phantom row的,因此RC時gap lock是被禁用的。

 

七、RR或RC時,對於主鍵或唯一索引,當有重覆鍵錯誤(duplicate-key error)時,會在 重覆的索引記錄上 設置 shared next-key lock或shared index record lock。這可能會導致死鎖。

 

假設T1, T2, T3三個事務,T1已經持有了X鎖,T2和T3發生了重覆鍵錯誤,因此T2和T3都在等待獲取S鎖,這個時候,當T1回滾或提交釋放掉了X鎖,則T2和T3就都獲取到了S鎖,並且,T2和T3都請求X鎖,“T2和T3同時持有S鎖,且都在請求X鎖”,於是死鎖就產生了。

 

好了,規則都列出來了,是時候實踐一把了。下麵在展示鎖時,我們同時指出了當前所使用的隔離級別,表上的索引以及事務的SQL陳述句。

 

實踐一:搜索時無法使用索引,即全表掃描時,InnoDB在表的全部行上都加鎖

 

 

上圖演示了:搜索條件無法使用索引時,InnoDB不得不在表的全部行上都加鎖。所以,索引實在太重要了,查詢時,它能加快查詢速度;更新時,除了快速找到指定行,它還能減少被鎖定行的範圍,提高插入時的併發性。 

 

實踐二:唯一索引和非唯一索引、等值查詢和範圍查詢加鎖的不同

 

搜索時使用 唯一索引 作等值查詢時,InnoDB只需要加index record lock;搜索時使用 唯一索引作範圍查詢時 或 使用非唯一索引作任何查詢時 ,InnoDB需要加next-key lock或gap lock。

 

 

示例1演示了:使用非唯一索引 idx_c 搜索或掃描時,InnoDB要鎖住索引本身,還要鎖住索引記錄前面的間隙,即next-key lock: X 和 gap lock: X,GAP。next-key lock既鎖住索引記錄本身,還鎖住該索引記錄前面的間隙,gap lock只鎖住索引記錄前面的間隙。等值條件時,在最後一個不滿足條件的索引記錄上設置gap lock。

 

示例2演示了:使用唯一索引 iux_b 的唯一搜索條件,即,使用唯一索引執行等值查找時,InnoDB只需鎖住索引本身,即index record lock: X, REC_NOT_GAP,並不鎖索引前面的間隙。

 

示例3演示了:使用唯一索引 iux_b 進行範圍掃描時,依然需要鎖定掃描過的每一個索引記錄,並且鎖住每一條索引記錄前面的間隙,即next-key lock: X。範圍條件時,在最後一個不滿足條件的索引記錄上設置next-key lock。

 

實踐三:不同隔離級別加鎖的不同

 

無論何種隔離級別,SQL陳述句執行時,都是先由InnoDB執行索引掃描,然後,傳回結果集給MySQL服務器,MySQL服務器再對該索引條件之外的其他查詢條件進行求值,從而得到最終結果集。

 

 

上圖中,在不同的隔離級別下,執行了相同的SQL。無論何種隔離級別,PRIMARY上的index record lock總是會加的,我們不討論它。在idx_b上,隔離級別為RC時,InnoDB加了index record lock,即:X,REC_NOT_GAP,隔離級別為RR時,InnoDB加了next-key lock,即X。註意:RC時沒有gap lock或next-key lock哦。

 

上圖演示了:事務的隔離級別也會影響到設置哪種鎖。如我們前面所說,gap lock是用來阻止phantom row的,而RC時是允許phantom row,所以,RC時禁用了gap lock。因此,上圖中,RC時沒有在索引上設置gap lock或next-key lock。

 

實踐四:操作不存在的索引記錄時,也需要加鎖

 

 

上圖中,idx_b上並不存在b=266的索引記錄,那麼,當更新b=266的記錄時,是否需要加鎖呢?是的,也需要加鎖

 

無論b=266是否存在,RR時,InnoDB在第一個不滿足搜索條件的索引記錄上設置gap lock或next-key lock。一般,等值條件時設置gap lock,範圍條件時設置next-key lock。上圖中是等值條件,於是InnoDB設置gap lock,即上圖的X,GAP,其範圍是(226, 2222),正是此gap lock使得併發的事務無法插入b列大於等於266的值,RC時,由於gap lock是被禁止的,因此,並不會加gap lock,併發的事務可以插入b列大於等於266的值。

 

上圖演示了:操作不存在的索引記錄時,也需要加鎖。

 

實踐五:重覆鍵錯誤(duplicate-key error)時,會加共享鎖。這可能會導致死鎖。

 

對於主鍵或唯一索引,當有重覆鍵錯誤(duplicate-key error)時,會在 重覆的索引記錄上 設置 shared next-key lock或shared index record lock。這可能會導致死鎖。

 

 

上圖演示了:T1在主鍵1上設置exclusive index record lock。T2和T3插入時,會產生重覆鍵錯誤,於是T2和T3都在主鍵1上設置了shared next-key lock。如上圖所示

 

如果此時,T1 rollback釋放掉其所持有的index record lock,則T2和T3等待獲取的shared next-key lock都成功了,然後,T2和T3爭奪主鍵1上的index record lock,於是T2和T3就死鎖了,因為它倆都持有shard next-key lock,雙方誰都不會放棄已經得到的shared next-key lock,於是,誰都無法得到主鍵1的index record lock。

 

需要明確的是死鎖的可能性並不受隔離級別的影響,因為隔離級別改變的是讀操作的行為,而死鎖是由於寫操作產生的。死鎖並不可怕,MySQL會選擇一個犧牲者,然後,在系統變數innodb_lock_wait_timeout指定的秒數達到後,自動回滾犧牲者事務;從MySQL5.7開始,新加入了系統變數innodb_deadlock_detect(預設ON),如果開啟此變數,則MySQL不會再等待,一旦探測到死鎖,就立即回滾犧牲者事務。

 

 

上圖演示了:在上圖的狀態下,當T1 commit時,T1釋放了主鍵1上的index record lock,於是T2和T3等待獲取的shared next-key lock都成功了,然後,T2和T3爭奪主鍵1上的index record lock,於是T2和T3死鎖了,因為它倆都持有shard next-key lock,雙方誰都不會放棄已經得到的shared next-key lock,於是,誰都無法得到主鍵1的index record lock。

 

五、performance_schema.data_locks中能看到全部的鎖嗎?

 

顯而易見,performance_schema.data_locks並未顯示全部的鎖,那麼,它顯示了哪些鎖呢?很不幸,我並未找到文件說這事,儘管文件(https://dev.mysql.com/doc/refman/8.0/en/innodb-information-schema-transactions.html)說:“事務持有的每一個鎖 以及 事務被阻塞的每一個鎖請求,都在該表中占據一行”,但,我們很多例子都表明,它並未顯示全部的鎖。根據我的試驗,我猜測performance_schema.data_locks顯示的是WHERE條件所觸碰到的索引上的鎖,“WHERE條件所觸碰到的索引”是指SQL實際執行時所使用的索引,也就是SQL執行計劃的key列所顯示的索引,正因為此,INSERT時看不到任何鎖,update g set a=a+1 where b=22時只看到idx_b上的鎖。需要強調的是,這是我自己試驗並猜測的,我並未在文件中看到這種說法。

 

假設T1和T2兩個事務操作同一個表,先執行T1,此時儘管performance_schema.data_locks中只顯示T1的WHERE條件所觸碰到的索引上的鎖,但是,事實上在T1的WHERE條件觸碰不到的索引上,也是會設置鎖的。儘管表的索引idx並未被T1所觸碰到,即performance_schema.data_locks顯示T1在索引idx並沒有設置任何鎖,但,當T2執行 鎖定讀/插入/更新/刪除 時觸碰到了索引idx,T2才恍然發現,原來T1已經在索引idx上加鎖了。

 

我們來看下麵的三個例子

 

“performance_schema.data_locks無法看到全部鎖”示例一

 

 

上圖演示了:T1執行時,只觸碰到了索引idx_b,T1執行完後,在performance_schema.data_locks中只能看到idx_b上的鎖,看起來T1並未在idx_a上設置任何鎖;但,當T2執行觸碰到了索引idx_a時,T2才恍然發現,原來T1已經在idx_a上設置了index record lock啦。

 

“performance_schema.data_locks無法看到全部鎖”示例二

 

 

插入新行時,會先設置insert intention lock,插入成功後再在插入完成的行上設置index record lock。

 

上圖演示了:T1插入了新行,但,在performance_schema.data_locks中,我們既看不到T1設置的insert intention lock,也看不到T1設置的index record lock。這是因為T1的WHERE條件並未觸碰到任何索引(T1根本不存在WHERE條件),因此我們看不到T1的這兩個鎖;但,當T2要刪除T1新插入的行時,T2才恍然發現,原來T1已經在索引c2上設置了index record lock啦。

 

“performance_schema.data_locks無法看到全部鎖”示例三

 


插入新行時,本來是不會在performance_schema.data_locks中顯示insert intention lock的,因為插入時WHERE條件並未觸碰到任何索引(插入時根本不存在WHERE條件)。

 

上圖演示了:T2插入新行時的insert intention lock 和 T1的gap lock衝突了,於是,我們得以在performance_schema.data_locks中觀察到T2插入新行時需要請求insert intentin lock。

推薦閱讀

(點擊標題可跳轉閱讀)

MySQL 死鎖與日誌二三事

記錄一次 Mysql 死鎖排查過程

架構師帶你玩轉分佈式鎖

看完本文有收穫?請轉發分享給更多人

關註「資料分析與開發」加星標,提升資料技能

喜歡就點一下「好看」唄~

    閱讀原文

    赞(0)

    分享創造快樂