本文轉(zhuǎn)載自微信公眾號(hào)「碼蟲甲」，作者碼蟲甲。轉(zhuǎn)載本文請聯(lián)系碼蟲甲公眾號(hào)。

背景

當(dāng)數(shù)據(jù)庫中有多個(gè)操作需要修改同一數(shù)據(jù)時(shí)，不可避免的會(huì)產(chǎn)生數(shù)據(jù)的臟讀。這時(shí)就需要數(shù)據(jù)庫具有良好的并發(fā)控制能力，這一切在 MySQL 中都是由服務(wù)器和存儲(chǔ)引擎來實(shí)現(xiàn)的。解決并發(fā)問題最有效的方案是引入了鎖的機(jī)制，鎖在功能上分為共享鎖 (shared lock) 和排它鎖 (exclusive lock) 即通常說的讀鎖和寫鎖; 鎖的粒度上分行鎖和表鎖，表級(jí)鎖MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖(meta data lock，MDL)

行鎖種類

• Next-Key Lock：鎖定一個(gè)范圍，并且鎖定記錄本上;
• Gap Lock：間隙鎖，鎖定一個(gè)范圍，但不包含記錄本上;
• Record Lock：單個(gè)行記錄上的鎖;

加鎖規(guī)則

雖然 MySQL 的鎖各式各樣，但是有些基本的加鎖原則是保持不變的，譬如：快照讀是不加鎖的，更新語句肯定是加排它鎖的，RC 隔離級(jí)別是沒有間隙鎖的等等。這些規(guī)則整理如下：

常見語句的加鎖

• SELECT ... 語句正常情況下為快照讀，不加鎖;
• SELECT ... LOCK IN SHARE MODE 語句為當(dāng)前讀，加 S 鎖;
• SELECT ... FOR UPDATE 語句為當(dāng)前讀，加 X 鎖;
• 常見的 DML 語句(如 INSERT、DELETE、UPDATE)為當(dāng)前讀，加 X 鎖;
• 常見的 DDL 語句(如 ALTER、CREATE 等)加表級(jí)鎖，且這些語句為隱式提交，不能回滾;

表鎖

• 表鎖(分 S 鎖和 X 鎖)
• 意向鎖(分 IS 鎖和 IX 鎖)
• 自增鎖(一般見不到，只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 時(shí)才可能有)

行鎖分析

• 行鎖都是加在索引上的，最終都會(huì)落在聚簇索引上;
• 加行鎖的過程是一條一條記錄加的;

鎖沖突

• S 鎖和 S 鎖兼容，X 鎖和 X 鎖沖突，X 鎖和 S 鎖沖突;

不同隔離級(jí)別下的鎖

• 上面說 SELECT ... 語句正常情況下為快照讀，不加鎖;但是在 Serializable 隔離級(jí)別下為當(dāng)前讀，加 S 鎖;
• RC 隔離級(jí)別下沒有間隙鎖和 Next-key 鎖

SQL 的加鎖分析

我們使用下面這張 students 表為例，其中 id 為主鍵，no(學(xué)號(hào))為二級(jí)唯一索引，name(姓名)和 age(年齡)為二級(jí)非唯一索引，score(學(xué)分)無索引。

我們只分析最簡單的一種 SQL，它只包含一個(gè) WHERE 條件，等值查詢或范圍查詢。雖然 SQL 非常簡單，但是針對不同類型的列，我們還是會(huì)面對各種情況：

• 聚簇索引，查詢命中：UPDATE students SET score = 100 WHERE id = 15;
• 聚簇索引，查詢未命中：UPDATE students SET score = 100 WHERE id = 16;
• 二級(jí)非唯一索引，查詢命中：UPDATE students SET score = 100 WHERE name = 'Tom';
• 二級(jí)非唯一索引，查詢未命中：UPDATE students SET score = 100 WHERE name = 'John';
• 無索引：UPDATE students SET score = 100 WHERE score = 22;
• 聚簇索引，范圍查詢：UPDATE students SET score = 100 WHERE id <= 20;
• 二級(jí)索引，范圍查詢：UPDATE students SET score = 100 WHERE age <= 23;

聚簇索引，查詢命中

語句 UPDATE students SET score = 100 WHERE id = 15 在 RC 和 RR 隔離級(jí)別下加鎖情況一樣，都是對 id 這個(gè)聚簇索引加 X 鎖，如下：

聚簇索引，查詢未命中

如果查詢未命中記錄，在 RC 和 RR 隔離級(jí)別下加鎖是不一樣的，因?yàn)?RR 有 GAP 鎖。語句 UPDATE students SET score = 100 WHERE id = 16 在 RC 和 RR 隔離級(jí)別下的加鎖情況如下(RC 不加鎖)：

二級(jí)非唯一索引，查詢命中

如果查詢命中的是二級(jí)非唯一索引，在 RR 隔離級(jí)別下，還會(huì)加 GAP 鎖。語句 UPDATE students SET score = 100 WHERE name = 'Tom' 加鎖如下：

數(shù)一數(shù)上方右圖中的鎖你可能會(huì)覺得一共加了 7 把鎖，實(shí)際情況不是，要注意的是 (Tom, 37) 上的記錄鎖和它前面的 GAP 鎖合起來是一個(gè) Next-key 鎖，這個(gè)鎖加在 (Tom, 37) 這個(gè)索引上，另外 (Tom, 49) 上也有一把 Next-key 鎖。那么最右邊的 GAP 鎖加在哪呢?右邊已經(jīng)沒有任何記錄了啊。其實(shí)，在 InnoDB 存儲(chǔ)引擎里，每個(gè)數(shù)據(jù)頁中都會(huì)有兩個(gè)虛擬的行記錄，用來限定記錄的邊界，分別是：Infimum Record 和 Supremum Record，Infimum 是比該頁中任何記錄都要小的值，而 Supremum 比該頁中最大的記錄值還要大，這兩條記錄在創(chuàng)建頁的時(shí)候就有了，并且不會(huì)刪除。上面右邊的 GAP 鎖就是加在 Supremum Record 上。所以說，上面右圖中共有 2 把 Next-key 鎖，1 把 GAP 鎖，2 把記錄鎖，一共 5 把鎖。

二級(jí)非唯一索引，查詢未命中

如果查詢未命中記錄，RR 隔離級(jí)別會(huì)加 GAP 鎖，RC 無鎖。語句 UPDATE students SET score = 100 WHERE name = 'John' 加鎖情況如下：

無索引

如果 WHERE 條件不能走索引，MySQL 會(huì)如何加鎖呢?有的人說會(huì)在表上加 X 鎖，也有人說會(huì)根據(jù) WHERE 條件將篩選出來的記錄在聚簇索引上加上 X 鎖，那么究竟如何，我們看下圖：

在沒有索引的時(shí)候，只能走聚簇索引，對表中的記錄進(jìn)行全表掃描。在 RC 隔離級(jí)別下會(huì)給所有記錄加行鎖，在 RR 隔離級(jí)別下，不僅會(huì)給所有記錄加行鎖，所有聚簇索引和聚簇索引之間還會(huì)加上 GAP 鎖。

語句 UPDATE students SET score = 100 WHERE score = 22 滿足條件的雖然只有 1 條記錄，但是聚簇索引上所有的記錄，都被加上了 X 鎖。那么，為什么不是只在滿足條件的記錄上加鎖呢?這是由于 MySQL 的實(shí)現(xiàn)決定的。如果一個(gè)條件無法通過索引快速過濾，那么存儲(chǔ)引擎層面就會(huì)將所有記錄加鎖后返回，然后由 MySQL Server 層進(jìn)行過濾，因此也就把所有的記錄都鎖上了。

聚簇索引，范圍查詢

上面所介紹的各種情況其實(shí)都是非常常見的 SQL，它們有一個(gè)特點(diǎn)：全部都只有一個(gè) WHERE 條件，并且都是等值查詢。那么問題來了，如果不是等值查詢而是范圍查詢，加鎖情況會(huì)怎么樣呢?

SQL 語句為 UPDATE students SET score = 100 WHERE id <= 20，按理說我們只需要將 id = 20、18、15 三條記錄鎖住即可，但是看右邊的圖，在 RR 隔離級(jí)別下，我們還把 id = 30 這條記錄以及 (20, 30] 之間的間隙也鎖起來了，很顯然這是一個(gè) Next-key 鎖。

二級(jí)索引，范圍查詢

然后我們把范圍查詢應(yīng)用到二級(jí)非唯一索引上來，SQL 語句為：UPDATE students SET score = 100 WHERE age <= 23，加鎖情況如下圖所示：

可以看出和聚簇索引的范圍查詢一樣，除了 WHERE 條件范圍內(nèi)的記錄加鎖之外，后面一條記錄也會(huì)加上 Next-key 鎖，這里有意思的一點(diǎn)是，盡管滿足 age = 24 的記錄有兩條，但只有第一條被加鎖，第二條沒有加鎖，并且第一條和第二條之間也沒有加鎖。

metadata lock

元數(shù)據(jù)鎖(meta data lock，MDL)不需要顯式使用，在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。MDL 的作用是，保證讀寫的正確性。可以想象一下，如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對這個(gè)表結(jié)構(gòu)做變更,刪了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對不上，肯定是不行的。因此，在 MySQL 5.5 版本中引入了 MDL,當(dāng)對一個(gè)表做增刪改查操作的時(shí)候，加 MDL 讀鎖;當(dāng)要對表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL 寫鎖。讀鎖之間不互斥，因此可以有多個(gè)線程同時(shí)對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性,因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開始執(zhí)行。

雖然 MDL 鎖是系統(tǒng)默認(rèn)會(huì)加的，但卻是你不能忽略的一個(gè)機(jī)制,比如下面這個(gè)例子，經(jīng)?？吹接腥说舻竭@個(gè)坑里：給一個(gè)小表加個(gè)字段，導(dǎo)致整個(gè)庫掛了。你肯定知道，給一個(gè)表加字段，或者修改字段，或者加索引，需要掃描全表的數(shù)據(jù)。在對大表操作的時(shí)候，需要特別小心,以免對線上服務(wù)造成影響。而實(shí)際上，即使是小表操作不慎也會(huì)出問題。

我們來看一下下面的操作序列，假設(shè)表 t2 是一個(gè)小表。備注：這里的實(shí)驗(yàn)環(huán)境是 MySQL 5.7

我們可以看到 session A 先啟動(dòng)，這時(shí)候會(huì)對表 t加一個(gè) MDL 讀鎖。由于 session B 需要的也是MDL 讀鎖，因此可以正常執(zhí)行

之后 session C 會(huì)被 blocked，是因?yàn)?session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。

如果只有 session C 自己被阻塞還沒什么關(guān)系，但是之后所有要在表 t 上新申請 MDL 讀鎖的請求也會(huì)被session C 阻塞。前面我們說了，所有對表的增刪改查操作都需要先申請 MDL 讀鎖，就都被鎖住，等于這個(gè)表現(xiàn)在完全不可讀寫了。

如果某個(gè)表上的查詢語句頻繁，而且客戶端有重試機(jī)制，也就是說超時(shí)后會(huì)再起一個(gè)新 session 再請求的話，這個(gè)庫的線程很快就會(huì)爆滿。

解決長事務(wù)，事務(wù)不提交，就會(huì)一直占著 MDL 鎖;

Online DDL的過程是這樣的：

1. 拿MDL寫鎖

2. 降級(jí)成MDL讀鎖

3. 真正做DDL

4. 升級(jí)成MDL寫鎖

5. 釋放MDL鎖

1、2、4、5如果沒有鎖沖突，執(zhí)行時(shí)間非常短。第3步占用了DDL絕大部分時(shí)間，這期間這個(gè)表可以正常讀寫數(shù)據(jù)，是因此稱為“online ”

我們上面的例子，是在第一步就堵住了，拿不到MDL寫鎖

在MySQL中使用 create table2 as select * from table1時(shí)，語句執(zhí)行完成之前 table1的mdl鎖不會(huì)釋放，且因持續(xù)持有S鎖造成阻塞table1寫操作;

insert into table1 select * from table2

在MySQL中使用insert into table1 select * from table2時(shí)，會(huì)對table2進(jìn)行加鎖，這個(gè)加鎖分以下幾種情況：

1.后面不帶查詢條件，不帶排序方式

insert into table1 select * from table2： 此時(shí)MySQL是逐行加鎖，每一行都鎖

2.查詢使用主鍵排序

insert into table1 select * from table2 order by id： 使用主鍵排序時(shí)，MySQL逐行加鎖，每一行都鎖

3.使用非主鍵排序

insert into table1 select * from table2 order by modified_date：使用非主鍵排序時(shí)，MySQL鎖整個(gè)表

4.在查詢條件中使用非主鍵篩選條件，

insert into table1 select * from table2 where modified_date>='2017-10-01 00:00:00'

使用非主鍵篩選條件時(shí)，MySQL逐行加鎖，每一行都鎖

結(jié)論：該句式導(dǎo)致寫阻塞

建議：select ...outfile是不阻塞dml操作的，可以用select...into outfile 和 load data infile 的組合來代替insert...select完成插入操作。

實(shí)際場景中如何避免鎖的資源競爭

讓 SELECT 速度盡量快，盡量減少大的復(fù)雜的Query，將復(fù)雜的Query分拆成幾個(gè)小的Query分步進(jìn)行;

盡可能地建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速;

使用EXPLAIN SELECT來確定對于你的查詢中使用的索引跟預(yù)期一致

死鎖案例

案例1

死鎖的根本原因是有兩個(gè)或多個(gè)事務(wù)之間加鎖順序的不一致導(dǎo)致的，這個(gè)死鎖案例其實(shí)是最經(jīng)典的死鎖場景。

首先，事務(wù) A 獲取 id = 20 的鎖(lock_mode X locks rec but not gap)，事務(wù) B 獲取 id = 30 的鎖;然后，事務(wù) A 試圖獲取 id = 30 的鎖，而該鎖已經(jīng)被事務(wù) B 持有，所以事務(wù) A 等待事務(wù) B 釋放該鎖，然后事務(wù) B 又試圖獲取 id = 20 的鎖，這個(gè)鎖被事務(wù) A 占有，于是兩個(gè)事務(wù)之間相互等待，導(dǎo)致死鎖。

案例2

Session aSession b

update students set score=100 where id<30; update students set score=100 where age>23;

這個(gè)案例里每個(gè)事務(wù)都只有一條 SQL 語句，但可能會(huì)導(dǎo)致死鎖問題，其實(shí)說起來，這個(gè)死鎖和案例一并沒有什么區(qū)別，只不過理解起來要更深入一點(diǎn)。要知道在范圍查詢時(shí)，加鎖是一條記錄一條記錄挨個(gè)加鎖的，所以雖然只有一條 SQL 語句，如果兩條 SQL 語句的加鎖順序不一樣，也會(huì)導(dǎo)致死鎖。

在案例一中，事務(wù) A 的加鎖順序?yàn)椋篿d = 20 -> 30，事務(wù) B 的加鎖順序?yàn)椋篿d = 30 -> 20，正好相反，所以會(huì)導(dǎo)致死鎖。這里的情景也是一樣，事務(wù) A 的范圍條件為 id < 30，加鎖順序?yàn)椋篿d = 15 -> 18 -> 20，事務(wù) B 走的是二級(jí)索引 age，加鎖順序?yàn)椋?age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49)，其中，對 id 的加鎖順序?yàn)?id = 18 -> 20 -> 15 -> 49。可以看到事務(wù) A 先鎖 15，再鎖 18，而事務(wù) B 先鎖 18，再鎖 15，從而形成死鎖。

如何避免死鎖

如上面的案例一和案例二所示，對索引加鎖順序的不一致很可能會(huì)導(dǎo)致死鎖，所以如果可以，盡量以相同的順序來訪問索引記錄和表。在程序以批量方式處理數(shù)據(jù)的時(shí)候，如果事先對數(shù)據(jù)排序，保證每個(gè)線程按固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能;

為表添加合理的索引，如果不走索引將會(huì)為表的每一行記錄加鎖，死鎖的概率就會(huì)大大增大;

避免大事務(wù)，盡量將大事務(wù)拆成多個(gè)小事務(wù)來處理;因?yàn)榇笫聞?wù)占用資源多，耗時(shí)長，與其他事務(wù)沖突的概率也會(huì)變高;

避免在同一時(shí)間點(diǎn)運(yùn)行多個(gè)對同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語句;我們經(jīng)常會(huì)有一些定時(shí)腳本，避免它們在同一時(shí)間點(diǎn)運(yùn)行;

設(shè)置鎖等待超時(shí)參數(shù)：innodb_lock_wait_timeout(默認(rèn)50s)，這個(gè)參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會(huì)占用大量計(jì)算機(jī)資源，造成嚴(yán)重性能問題，甚至拖跨數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時(shí)閾值，可以避免這種情況發(fā)生。