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

背景

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

行鎖種類

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

加鎖規(guī)則

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

常見語句的加鎖

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

表鎖

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

行鎖分析

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

鎖沖突

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

不同隔離級別下的鎖

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

SQL 的加鎖分析

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

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

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

聚簇索引，查詢命中

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

聚簇索引，查詢未命中

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

二級非唯一索引，查詢命中

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

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

二級非唯一索引，查詢未命中

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

無索引

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

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

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

聚簇索引，范圍查詢

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

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

二級索引，范圍查詢

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

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

metadata lock

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

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

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

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

之后 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 session C 需要 MDL 寫鎖，因此只能被阻塞。

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

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

解決長事務，事務不提交，就會一直占著 MDL 鎖;

Online DDL的過程是這樣的：

1. 拿MDL寫鎖

2. 降級成MDL讀鎖

3. 真正做DDL

4. 升級成MDL寫鎖

5. 釋放MDL鎖

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

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

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

insert into table1 select * from table2

在MySQL中使用insert into table1 select * from table2時，會對table2進行加鎖，這個加鎖分以下幾種情況：

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

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

2.查詢使用主鍵排序

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

3.使用非主鍵排序

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

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

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

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

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

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

實際場景中如何避免鎖的資源競爭

讓 SELECT 速度盡量快，盡量減少大的復雜的Query，將復雜的Query分拆成幾個小的Query分步進行;

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

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

死鎖案例

案例1

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

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

案例2

Session aSession b

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

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

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

如何避免死鎖

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

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

避免大事務，盡量將大事務拆成多個小事務來處理;因為大事務占用資源多，耗時長，與其他事務沖突的概率也會變高;

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

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