Mysql鎖看這篇就夠了
當(dāng)數(shù)據(jù)庫中有多個操作需要修改同一數(shù)據(jù)時，不可避免的會產(chǎn)生數(shù)據(jù)的臟讀。這時就需要數(shù)據(jù)庫具有良好的并發(fā)控制能力，這一切在 MySQL 中都是由服務(wù)器和存儲引擎來實現(xiàn)的。解決并發(fā)問題最有效的方案是引入了鎖的機(jī)制，鎖在功能上分為共享鎖 (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 語句為當(dāng)前讀，加 S 鎖；
SELECT ... FOR UPDATE 語句為當(dāng)前讀，加 X 鎖；
常見的 DML 語句（如 INSERT、DELETE、UPDATE）為當(dāng)前讀，加 X 鎖；
常見的 DDL 語句（如 ALTER、CREATE 等）加表級鎖，且這些語句為隱式提交，不能回滾；
表鎖
表鎖（分 S 鎖和 X 鎖）
意向鎖（分 IS 鎖和 IX 鎖）
自增鎖（一般見不到，只有在 innodb_autoinc_lock_mode = 0 或者 Bulk inserts 時才可能有）
行鎖分析
行鎖都是加在索引上的，最終都會落在聚簇索引上；
加行鎖的過程是一條一條記錄加的；
鎖沖突
S 鎖和 S 鎖兼容，X 鎖和 X 鎖沖突，X 鎖和 S 鎖沖突；
不同隔離級別下的鎖
上面說 SELECT ... 語句正常情況下為快照讀，不加鎖；但是在 Serializable 隔離級別下為當(dāng)前讀，加 S 鎖；
RC 隔離級別下沒有間隙鎖和 Next-key 鎖
SQL 的加鎖分析
我們使用下面這張 students 表作為實例，其中 id 為主鍵，no（學(xué)號）為二級唯一索引，name（姓名）和 age（年齡）為二級非唯一索引，score（學(xué)分）無索引。
我們只分析最簡單的一種 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 鎖，如下：
聚簇索引，查詢未命中
如果查詢未命中紀(jì)錄，在 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 把鎖。
二級非唯一索引，查詢未命中
如果查詢未命中紀(jì)錄，RR 隔離級別會加 GAP 鎖，RC 無鎖。語句 UPDATE students SET score = 100 WHERE name = 'John' 加鎖情況如下：
無索引
如果 WHERE 條件不能走索引，MySQL 會如何加鎖呢？有的人說會在表上加 X 鎖，也有人說會根據(jù) WHERE 條件將篩選出來的記錄在聚簇索引上加上 X 鎖，那么究竟如何，我們看下圖：
在沒有索引的時候，只能走聚簇索引，對表中的記錄進(jìn)行全表掃描。在 RC 隔離級別下會給所有記錄加行鎖，在 RR 隔離級別下，不僅會給所有記錄加行鎖，所有聚簇索引和聚簇索引之間還會加上 GAP 鎖。
語句 UPDATE students SET score = 100 WHERE score = 22 滿足條件的雖然只有 1 條記錄，但是聚簇索引上所有的記錄，都被加上了 X 鎖。那么，為什么不是只在滿足條件的記錄上加鎖呢？這是由于 MySQL 的實現(xiàn)決定的。如果一個條件無法通過索引快速過濾，那么存儲引擎層面就會將所有記錄加鎖后返回，然后由 MySQL Server 層進(jìn)行過濾，因此也就把所有的記錄都鎖上了。
聚簇索引，范圍查詢
上面所介紹的各種情況其實都是非常常見的 SQL，它們有一個特點：全部都只有一個 WHERE 條件，并且都是等值查詢。那么問題來了，如果不是等值查詢而是范圍查詢，加鎖情況會怎么樣呢？
SQL 語句為 UPDATE students SET score = 100 WHERE id <= 20，按理說我們只需要將 id = 20、18、15 三條記錄鎖住即可，但是看右邊的圖，在 RR 隔離級別下，我們還把 id = 30 這條記錄以及 (20, 30] 之間的間隙也鎖起來了，很顯然這是一個 Next-key 鎖。
二級索引，范圍查詢
然后我們把范圍查詢應(yīng)用到二級非唯一索引上來，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,當(dāng)對一個表做增刪改查操作的時候，加 MDL 讀鎖；當(dāng)要對表做結(jié)構(gòu)變更操作的時候，加 MDL 寫鎖。讀鎖之間不互斥，因此可以有多個線程同時對一張表增刪改查。讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性,因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。
雖然 MDL 鎖是系統(tǒng)默認(rèn)會加的，但卻是你不能忽略的一個機(jī)制,比如下面這個例子，經(jīng)常看到有人掉到這個坑里：給一個小表加個字段，導(dǎo)致整個庫掛了。你肯定知道，給一個表加字段，或者修改字段，或者加索引，需要掃描全表的數(shù)據(jù)。在對大表操作的時候，需要特別小心,以免對線上服務(wù)造成影響。而實際上，即使是小表操作不慎也會出問題。我們來看一下下面的操作序列，假設(shè)表 t 是一個小表。
備注：這里的實驗環(huán)境是 MySQL 5.7
| Session a | Session b | Session c | Session d |
begin; select * from t2 limit 1; | |||
| select * from t2 limit 1; | |||
| alter table t2 add f int;(blocked) | |||
| select * from t2 limit 1;(blocked) |
我們可以看到 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)在完全不可讀寫了。
如果某個表上的查詢語句頻繁，而且客戶端有重試機(jī)制，也就是說超時后會再起一個新 session 再請求的話，這個庫的線程很快就會爆滿。
解決長事務(wù)，事務(wù)不提交，就會一直占著 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寫操作;
在MySQL中使用insert into table1 select * from table2時，會對table2進(jìn)行加鎖，這個加鎖分以下幾種情況：
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é)論：該句式導(dǎo)致寫阻塞
建議：select ...outfile是不阻塞dml操作的，可以用select...into outfile 和 load data infile 的組合來代替insert...select完成插入操作。
實際場景中如何避免鎖的資源競爭
讓 SELECT 速度盡量快，盡量減少大的復(fù)雜的Query，將復(fù)雜的Query分拆成幾個小的Query分步進(jìn)行；
盡可能地建立足夠高效的索引，讓數(shù)據(jù)檢索更迅速；
使用EXPLAIN SELECT來確定對于你的查詢中使用的索引跟預(yù)期一致
死鎖案例
案例1
| Session a | Session b |
| update students set score=100 where id=20; | |
| pdate students set score=100 where id=30; | |
| update students set score=100 where id=30; | |
| update students set score=100 where id=20; |
死鎖的根本原因是有兩個或多個事務(wù)之間加鎖順序的不一致導(dǎo)致的，這個死鎖案例其實是最經(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 的鎖，這個鎖被事務(wù) A 占有，于是兩個事務(wù)之間相互等待，導(dǎo)致死鎖。
案例2
| Session a | Session b |
| update students set score=100 where id<30; | update students set score=100 where age>23; |
這個案例里每個事務(wù)都只有一條 SQL 語句，但可能會導(dǎo)致死鎖問題，其實說起來，這個死鎖和案例一并沒有什么區(qū)別，只不過理解起來要更深入一點。要知道在范圍查詢時，加鎖是一條記錄一條記錄挨個加鎖的，所以雖然只有一條 SQL 語句，如果兩條 SQL 語句的加鎖順序不一樣，也會導(dǎo)致死鎖。
在案例一中，事務(wù) A 的加鎖順序為：id = 20 -> 30，事務(wù) B 的加鎖順序為：id = 30 -> 20，正好相反，所以會導(dǎo)致死鎖。這里的情景也是一樣，事務(wù) A 的范圍條件為 id < 30，加鎖順序為：id = 15 -> 18 -> 20，事務(wù) B 走的是二級索引 age，加鎖順序為：(age, id) = (24, 18) -> (24, 20) -> (25, 15) -> (25, 49)，其中，對 id 的加鎖順序為 id = 18 -> 20 -> 15 -> 49。可以看到事務(wù) A 先鎖 15，再鎖 18，而事務(wù) B 先鎖 18，再鎖 15，從而形成死鎖。
如何避免死鎖
1.如上面的案例一和案例二所示，對索引加鎖順序的不一致很可能會導(dǎo)致死鎖，所以如果可以，盡量以相同的順序來訪問索引記錄和表。在程序以批量方式處理數(shù)據(jù)的時候，如果事先對數(shù)據(jù)排序，保證每個線程按固定的順序來處理記錄，也可以大大降低出現(xiàn)死鎖的可能；
2.為表添加合理的索引，如果不走索引將會為表的每一行記錄加鎖，死鎖的概率就會大大增大；
3.避免大事務(wù)，盡量將大事務(wù)拆成多個小事務(wù)來處理；因為大事務(wù)占用資源多，耗時長，與其他事務(wù)沖突的概率也會變高；
4.避免在同一時間點運(yùn)行多個對同一表進(jìn)行讀寫的腳本，特別注意加鎖且操作數(shù)據(jù)量比較大的語句；我們經(jīng)常會有一些定時腳本，避免它們在同一時間點運(yùn)行；
5.設(shè)置鎖等待超時參數(shù)：innodb_lock_wait_timeout(默認(rèn)50s)，這個參數(shù)并不是只用來解決死鎖問題，在并發(fā)訪問比較高的情況下，如果大量事務(wù)因無法立即獲得所需的鎖而掛起，會占用大量計算機(jī)資源，造成嚴(yán)重性能問題，甚至拖跨數(shù)據(jù)庫。我們通過設(shè)置合適的鎖等待超時閾值，可以避免這種情況發(fā)生。
往期推薦
