在當(dāng)今數(shù)字化的時代，數(shù)據(jù)庫管理系統(tǒng)的重要性不言而喻，而 MySQL 作為廣泛應(yīng)用的數(shù)據(jù)庫之一，更是有著舉足輕重的地位。在 MySQL 的復(fù)雜世界中，鎖機(jī)制宛如一把關(guān)鍵的鑰匙，它既是保障數(shù)據(jù)一致性和完整性的堅(jiān)實(shí)衛(wèi)士，也是影響數(shù)據(jù)庫性能和并發(fā)處理能力的重要因素。

當(dāng)我們深入探索 MySQL 的領(lǐng)域時，不得不將目光聚焦于這看似神秘卻又至關(guān)重要的鎖。它究竟是如何運(yùn)作的？有哪些不同的類型和特點(diǎn)？又如何在各種業(yè)務(wù)場景中發(fā)揮著關(guān)鍵作用？讓我們一同開啟這場關(guān)于 MySQL 鎖的探索之旅，逐步揭開它的神秘面紗，去洞悉它背后的深刻原理和廣泛應(yīng)用，為我們更好地駕馭 MySQL 數(shù)據(jù)庫奠定堅(jiān)實(shí)基礎(chǔ)。

注意:本文所有知識點(diǎn)都是從MySQL8.0版本進(jìn)行討論，針對5.7版本筆者會在特定知識點(diǎn)中點(diǎn)出區(qū)別。

一、詳解MySQL鎖原理

1. 詳解共享鎖和排他鎖

共享鎖(Share Lock,S 鎖):又稱讀鎖，進(jìn)行讀取操作時會上的鎖，上讀鎖之后的鎖其他事務(wù)上讀鎖也沒問題。

排他鎖(Exclusive Lock,X 鎖):又稱寫鎖，進(jìn)行修改操作前就需要上一把X鎖，上了寫鎖的數(shù)據(jù)，其他事務(wù)則無法上讀鎖或者寫鎖。對于已經(jīng)上了讀鎖的數(shù)據(jù)，寫鎖自然也是不能上鎖的。

有了上述基礎(chǔ)，我們針對x鎖對應(yīng)delete和update操作進(jìn)行一定的拓展，當(dāng)事務(wù)進(jìn)行delete的操作時，對應(yīng)的步驟為：

• 定位要刪除的數(shù)據(jù)的B+樹。
• 對其上X鎖。
• 上鎖成功后將該位置標(biāo)志位delete mark。
• 提交事務(wù)。

可以看到刪除操作本質(zhì)就是定位、標(biāo)記、刪除對應(yīng)3個步驟，而且刪除也并非實(shí)際意義上的刪除，而是標(biāo)記刪除。

針對update操作，我們可以從以下3個維度探討：

(1) 普通更新，即更新的字段物理空間不會變化，例如update tb set age=19 where id=1，該操作操作和上述操作差不多，定位到B+樹上的數(shù)據(jù)后上X鎖，并進(jìn)行修改操作：

(2) 更新可變字段，假設(shè)我們基于主鍵更新某個varchar字段，例如update tb set name='aaaaaaaaaaaaaa' where id=1，將name由varchar(1)更新為varchar(14)，那么對應(yīng)的操作就是定位到數(shù)據(jù)后上X鎖，將數(shù)據(jù)刪除(這里的刪除就是將記錄移動到垃圾鏈表)，然后執(zhí)行insert操作。

(3) 更新主鍵，該操作因?yàn)闀薷腷+樹，在定位到數(shù)據(jù)上X鎖之后，先按照delete的方式將數(shù)據(jù)刪除，然后再執(zhí)行insert操作即可。

需要了解的是MySQL在進(jìn)行讀寫操作為保證并發(fā)性能并非一定會用到讀寫鎖，在可重復(fù)讀和讀已提交兩個級別下，由于mvcc機(jī)制的存在，所以MySQL事務(wù)的讀操作都是基于readview進(jìn)行數(shù)據(jù)查詢的。

《深入解讀MySQL的MVCC與事務(wù)隔離級別》

2. 詳解意向鎖

假如我們要上全表鎖，我們就必須知道這張表有沒有上過讀鎖或者寫鎖的行級鎖，要想做到這點(diǎn)，常規(guī)做法是一行一行遍歷過去看看，針對大表而言，這種遍歷的效率是非常低的。

意向鎖就是用于解決這個問題的，它是表級鎖，在事務(wù)需要上讀鎖(S鎖)或者寫鎖(X鎖)前，首先必須取得意向鎖，這樣某些事務(wù)需要上全表鎖時，只需要看看有沒有事務(wù)持有意向鎖即可：

需要補(bǔ)充的是，意向鎖由數(shù)據(jù)引擎自行維護(hù)，用戶是無法操作的。

說完意向鎖的作用，我們就可以再來聊聊意向鎖的種類，意向鎖分為意向共享鎖和意向排他鎖：

• 意向共享鎖(Intention Shared Lock，IS 鎖)：當(dāng)事務(wù)需要上S鎖時，就需要先嘗試獲取IS鎖。
• 意向排他鎖(Intention Exclusive Lock，IX 鎖)：當(dāng)事務(wù)需要針對某條數(shù)據(jù)上X鎖的時候，就需要先上一把IX鎖。 而IS和IX之間互相兼容，彼此不互斥，例如：一個事務(wù)上了IS，其他事務(wù)同樣可以上IS和IX，因?yàn)镮S本質(zhì)就是一個共享讀鎖，某個事務(wù)持有這個IS鎖之后上的可能是id為1的讀鎖。不影響其他事務(wù)上IS或IX后對id為2的數(shù)據(jù)上X鎖：

本質(zhì)上來說IS和IX鎖都是表級鎖，它們主要是解決在事務(wù)上S鎖或者X鎖前意向詢問來避免掃表的開銷，所以彼此之間是兼容的。

接下來我們從表記鎖的角度探討以下意向鎖和讀寫鎖之間的關(guān)系，假設(shè)我們的事務(wù)需要上IS鎖之后針對表上了S表級鎖，那么就需要查看是否有事務(wù)上了IX鎖，如果有則說明有事務(wù)正在進(jìn)行寫操作，此時我們的S表級鎖操作就會阻塞：

同理假設(shè)我們的嘗試IX鎖進(jìn)行修改操作，需要針對全表上了X鎖，此時我們就需要檢查是否有其他事務(wù)上了IS或者IX以確定是否存在數(shù)據(jù)操作，如果有則阻塞等待，這就是IS、IX鎖對于數(shù)據(jù)讀寫操作的巧妙設(shè)計(jì)：

3. 詳解表記鎖

表級鎖(table-level locking)即可每一次操作時，鎖的是整張表，鎖的粒度大，上鎖速度快，開銷低，一旦某個事務(wù)上了表級鎖，那么其他事務(wù)就無法再上行級鎖或者表鎖，這也就意味著高并發(fā)場景下性能非常差:

如下所示，這條SQL語句查詢上的就是表級鎖：

SELECT * FROM s1   for update;

這一點(diǎn)我們可以通過查看performance_schema.data_locks表印證這一點(diǎn)，可以看到這個事務(wù)上的是IX意向?qū)戞i，并將表中的所有的記錄即都上了record寫鎖：

4.詳解行級鎖

行級鎖(table-level locking)鎖的粒度相對小一些，是針對索引字段加鎖，鎖的是選定的行數(shù)據(jù)，相比前者上鎖速度會慢一些，因?yàn)樾枰ㄎ坏疆?dāng)前行才能鎖定，并且因?yàn)榱６群瓦壿嬙O(shè)計(jì)問題有可能會導(dǎo)致死鎖，但是對于高并發(fā)多事務(wù)會相對友好一些：

對應(yīng)我們給出行級鎖的使用示例：

SELECT * FROM s1 WHERE id=1 for update;

通過performance_schema.data_locks表，可以看到這條該事務(wù)上X鎖前上了一把IX鎖之后，針對這條記錄上了X,REC_NOT_GAP即上了一把行級鎖，但是不鎖住間隙：

注意：我們上文所討論的都是針對innodb這個存儲引擎，如果是MyISAM這個存儲引擎僅僅支持表級鎖，而InnoDB支持行級鎖。

5.行級鎖實(shí)際使用示例

我們先給出行鎖的使用示例：首先創(chuàng)建一張測試表，注意id被設(shè)置為主鍵是自帶索引的。

drop  table user;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;

插入模擬數(shù)據(jù)：

insert into `user`(username, age)values
 ('tom',23),
('joey',22),
('James',21),
('William',20),
('David',24);

關(guān)閉事務(wù)自動提交，并確認(rèn)：

-- 關(guān)閉事務(wù)自動提交
set  autocommit = 0;

-- 查看自動提交是否生效
show VARIABLES like 'autocommit';

此時，在窗口鍵入一下SQL進(jìn)行查詢，可以看到筆者用了for update對id為1的數(shù)據(jù)上了排他鎖。

begin;
select * from `user` u where id=1 for update ;

此時我們再開一個排他鎖查詢，可以看到事務(wù)阻塞：

begin;
select * from `user` u where id=1 for update ;

由此可知，使用排他鎖對索引列上鎖時，其他排他鎖是無法操作對應(yīng)數(shù)據(jù)行的:

補(bǔ)充一個聯(lián)合索引的行級鎖使用示例，基于上述表格我們創(chuàng)建一個表級鎖：

create index index_username_age on user(username,age);

開啟一個事務(wù)進(jìn)行查詢：

begin;
select * from user where username ='tom' and age=23  for update ;

此時另一個事務(wù)進(jìn)行相同查詢會阻塞，走不相同的查詢條件不會阻塞，說明聯(lián)合索引也是走行級鎖：

begin;
select * from user where username ='tom' and age=23  for update ;

我們通過performance_schema.data_locks表可以看到，第一個事務(wù)針對tom數(shù)據(jù)上了一把行級寫鎖：

6. 表級鎖示例

上文提到，只要不走索引的查詢鎖定的都是整張表，所以我們使用age對應(yīng)22的SQL查詢語句上排他鎖：

begin;
select * from user where age=20 for update;

再打開另一個窗口就會發(fā)現(xiàn)，窗口被阻塞：

begin;
select * from user where age=22 for update ;

很明顯因?yàn)闆]有命中索引所以無法通過索引方式進(jìn)行定位，所以上了表級鎖，這一點(diǎn)我們也可以通過information_schema.INNODB_TRX表查看，可以看到第一個事務(wù)將所有數(shù)據(jù)行都上了鎖：

7. 行級鎖使用的注意事項(xiàng)

我們都知道行級鎖鎖的是索引字段，而表級鎖鎖的是非索引字段，這就意味著如果我們進(jìn)行update或者delete操作 (這兩個操作會上寫鎖互斥的，后文會說明)時where條件沒有命中唯一索引或者索引失效的話，就會上表級鎖，進(jìn)而出現(xiàn)性能問題。

當(dāng)然了，有時候MySQL優(yōu)化器也會不走索引，例如范圍索引檢索范圍區(qū)間超過30%，優(yōu)化器就會走全表掃描，那就無能為力了。

8. InnoDB有哪幾類鎖

• 記錄鎖(Record Lock)：這個鎖鎖定的是單個記錄行上的鎖。
• 間隙鎖(Gap Lock)：這個鎖鎖定的是一個范圍，例如查找id>21的用戶，那么間隙鎖鎖定的就是大于21的記錄，不包括21本身。
• 臨建鎖(Next-key Lock)：這個鎖我們可以理解為是記錄鎖和間隙鎖的綜合，它可以保證當(dāng)前鎖定的記錄及其間隙都上鎖，這個鎖可以保證可重復(fù)讀場景下事務(wù)讀避免幻讀問題，例如我們數(shù)據(jù)正在讀取id為8的數(shù)據(jù)，針對該鎖上一把Next-key Lock可以阻塞當(dāng)前數(shù)據(jù)及其前后區(qū)間被其他事務(wù)操作導(dǎo)致幻讀：

9. 可重復(fù)讀是基于上面那種鎖避免幻讀的呢？

可重讀讀避免幻讀的方式有兩種:

• 快照讀(一次性非鎖定讀):這種方式就是通過mvcc的方式僅僅在啟動時創(chuàng)建一個readView確保不會出現(xiàn)幻讀。
• 當(dāng)前讀(一次性鎖定讀):這種方式就是通過臨建鎖，避免新的記錄插入從而避免幻讀的情況發(fā)生。

具體可以參考筆者的這篇文章：《深入解讀MySQL的MVCC與事務(wù)隔離級別》

10. 詳解間隙鎖

上文說過，間隙鎖就是在范圍查詢時對索引項(xiàng)上鎖，但不包括范圍本身的一種鎖，這種機(jī)制在RR這個隔離級別可以一定程度上避免幻讀，注意是一定程度上。 對此我們不妨舉個例子:

首先我們創(chuàng)建一個用戶表，并對age加個索引：

drop  table user;

CREATE TABLE `user` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `username` varchar(255) NOT NULL,
  `age` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB CHARSET=utf8;


create index age_index on user(age);

然后插入數(shù)據(jù)：

insert into `user`(username, age)values ('tom',23),('joey',22),('James',21),('William',20),('David',24);

由于MySQL默認(rèn)隔離級別為RR，所以我們開啟一個窗口設(shè)置關(guān)閉自動提交，并查看其是否生效。

-- 關(guān)閉事務(wù)自動提交
set  autocommit = 0;

-- 查看自動提交是否生效
show VARIABLES like 'autocommit';

若生效，我們則輸入一個begin，并進(jìn)行一個范圍查詢，搜索大于24范圍的數(shù)據(jù)。

begin;
select * from user where age >24 for update;

當(dāng)我們在開啟一個新的窗口并進(jìn)行插入操作時可以發(fā)現(xiàn)，操作被阻塞了。

begin;
insert into `user`(username, age)values ('tom',26);

因?yàn)槟挲g上了索引，所以查詢時走了范圍索引，從performance_schema.data_locks可以看出該操作針對id為5、10這兩條年齡為24的數(shù)據(jù)都上了行級鎖和間隙鎖，導(dǎo)致數(shù)據(jù)插入失敗：

11.詳解悲觀鎖和樂觀鎖

悲觀鎖就我們上面所說的互斥鎖，它認(rèn)為自己每次拿到的數(shù)據(jù)都很可能被人修改，通過上鎖確?；コ庑员苊鈹?shù)據(jù)一致性問題。在MySQL中select...for update，insert，update、delete語句用的都是用排他鎖實(shí)現(xiàn)悲觀鎖。 而樂觀鎖則是一種業(yè)務(wù)鎖，通過用戶手動對表增加一個版本號的字段來解決并發(fā)數(shù)據(jù)正確性問題，如下圖所示，某個時間點(diǎn)兩個相同的事務(wù)讀取到相同版本號的當(dāng)行數(shù)據(jù)，彼此都執(zhí)行更新邏輯，為了保證并發(fā)更新保證數(shù)據(jù)準(zhǔn)確性，我們就需要通過版本號確保自己更新的數(shù)據(jù)是最新數(shù)據(jù)，如下圖所示，左邊的更新SQL最先執(zhí)行，這就使得右邊的更新失敗了，這樣右邊的事務(wù)就知道自己更新條件數(shù)據(jù)已過期，就會修改版本號再次進(jìn)行更新。

12. 當(dāng)前讀和快照讀有什么區(qū)別？

答:  先說說當(dāng)前讀(一致性非鎖定讀)吧，當(dāng)前讀發(fā)生在讀已提交(RC)或者可重復(fù)讀(RR)這兩個隔離級別下，我們使用的select使用的就是快照讀:

若在RC這個隔離級別下，用戶每次進(jìn)行讀操作時，都會創(chuàng)建一個readView，然后通過這個readView獲取數(shù)據(jù)。

若在RR這個隔離級別下，僅僅在啟動事務(wù)時創(chuàng)建一個readVew，后續(xù)無論其他事務(wù)無論修改用戶讀取的數(shù)據(jù)，用戶都只會讀取當(dāng)前readView的數(shù)據(jù)，這就是為什么RR可以保證可重入讀。

而當(dāng)前讀(一致性鎖定讀)則基于S鎖或者X鎖實(shí)現(xiàn)的一種讀取最新數(shù)據(jù)，快照讀的select語句如下:

SELECT ... FOR UPDATE
SELECT ... LOCK IN SHARE MODE

當(dāng)然，我們的insert、update、delete語句也是使用當(dāng)前讀。

二、詳解MySQL中死鎖問題

1. 詳解定位事務(wù)各種鎖的幾張表

在正式演示死鎖定位與排查思路之前，我們先簡單介紹幾張比較重要的表，首先是INNODB_TRX 這張表，它會記錄當(dāng)前活躍事務(wù)所持有的鎖的情況：

SELECT * FROM information_schema.INNODB_TRX it ;

如下圖，可以看到我們567584這個事務(wù)，這里我們著重查看trx_tables_locked、trx_lock_structs、trx_rows_locked三個字段，其含義分別是：

• trx_tables_locked：當(dāng)前事務(wù)對幾張表上鎖，以本條數(shù)據(jù)為例就上了一把鎖。
• trx_rows_locked：標(biāo)識當(dāng)前事務(wù)鎖定幾行數(shù)據(jù)，下圖表示當(dāng)前事務(wù)鎖定了一行數(shù)據(jù)。
• trx_lock_structs：當(dāng)前事務(wù)生成幾個鎖的結(jié)構(gòu)體，顯示為2，即生成兩個鎖的結(jié)構(gòu)體。

我們再來看看data_locks表(對應(yīng)MySQL5.7版本表明為innodb_locks)，這張表在MySQL中活躍事務(wù)的上鎖情況：

select * from performance_schema.data_locks;

如下圖，可以看到567834事務(wù)的線程號、事件id以及這個事務(wù)在tb_1表上了一把IX意向讀鎖：

最后再來看看 data_lock_waits表（對應(yīng)MySQL5.7是innodb_waits表），這張表就比較重要了，它代表了當(dāng)前事務(wù)中處于等待鎖狀態(tài)的事務(wù)情況：

select * from performance_schema.data_lock_waits;

如下圖，可以看到本文的567585事務(wù)正在等待567584的事務(wù)的鎖：

2. (實(shí)踐)線上定位MySQL死鎖與解決思路

接下來我們就基于一個簡單的例子來掩飾一下如何定位死鎖問題，我們都知道for update上的是寫鎖，這意味著一旦上了X鎖的數(shù)據(jù)，其他事務(wù)就無法針對該數(shù)據(jù)上S鎖或者X鎖：

如下圖這個說明，假設(shè)的我們的事務(wù)1先針對id為1的值上了一把寫鎖，對應(yīng)事務(wù)的SQL如下：

begin;
SELECT * FROM  tb_1 t WHERE id =1 for UPDATE ;
SELECT * FROM  tb_1 t WHERE id =2 for UPDATE ;

同理第二個事務(wù)現(xiàn)針對id為2的數(shù)據(jù)上寫鎖，在針對id為1的數(shù)據(jù)上寫鎖，由此雙方循環(huán)等待，造成死鎖。

begin;
SELECT * FROM  tb_1 t WHERE id =2 for UPDATE ;
SELECT * FROM  tb_1 t WHERE id =1 for UPDATE ;

接下來我們就基于上述所說的3張表進(jìn)行死鎖的問題的定位，首先我們查看INNODB_TRX可以看到我們本次SQL的食物號為567585它處于鎖等待狀態(tài)，可以看到它正在執(zhí)行的SQL語句以及上鎖的信息。

然后我們到data_locks表查看當(dāng)前數(shù)據(jù)庫的鎖情況，這個事務(wù)正在等待X鎖和REC_NOT_GAP鎖，說明這個事務(wù)存在死鎖：

最后再到data_lock_waits可以看到567585的事務(wù)等待567584的事務(wù)。

明確定位了兩個事務(wù)的，查看innodb 狀態(tài)信息定位到這兩個事務(wù)號的執(zhí)行執(zhí)行語句：

show engine innodb status;

最終，可以看到處于等待的事務(wù)567585鎖等待的事務(wù)567584所執(zhí)行的SQL語句，很明顯是因?yàn)樯狭送粋€行級鎖造成事務(wù)567585等待造成死鎖：

針對死鎖問題這個問題，我們先得說說造成死鎖的4個條件:

• 互斥
• 不可剝奪
• 請求和保持條件
• 鎖之間構(gòu)成環(huán)路

所以MySQL解決死鎖的方式大抵有以下幾種：

• 每個事務(wù)按照順序到表中上鎖(破壞環(huán)路條件)。
• 將大事務(wù)拆小。
• 邏輯上要求事務(wù)必須一次性取得兩張表的鎖才能操作數(shù)據(jù)。
• 降低隔離級別，例如將RR級別降低為RC避免上間隙鎖確保降低發(fā)生死鎖的概率。