面試官：MySQL 有哪些鎖？
2021-09-08 16:59:02

大家好，我是小林。

這次，來(lái)說(shuō)說(shuō) MySQL 的鎖，主要是 Q&A 的形式，看起來(lái)會(huì)比較輕松。

不多 BB 了，發(fā)車！

在 MySQL 里，根據(jù)加鎖的范圍，可以分為全局鎖、表級(jí)鎖和行鎖三類。

全局鎖

全局鎖是怎么用的？

要使用全局鎖，則要執(zhí)行這條命：

flush tables with read lock

執(zhí)行后，整個(gè)數(shù)據(jù)庫(kù)就處于只讀狀態(tài)了，這時(shí)其他線程執(zhí)行以下操作，都會(huì)被阻塞：

• 對(duì)數(shù)據(jù)的增刪查改操作，比如 insert、delete、update等語(yǔ)句；
• 對(duì)表結(jié)構(gòu)的更改操作，比如 alter table、drop table 等語(yǔ)句。

如果要釋放全局鎖，則要執(zhí)行這條命令：

unlock tables

當(dāng)然，當(dāng)會(huì)話斷開(kāi)了，全局鎖會(huì)被自動(dòng)釋放。

全局鎖應(yīng)用場(chǎng)景是什么？

全局鎖主要應(yīng)用于做全庫(kù)邏輯備份，這樣在備份數(shù)據(jù)庫(kù)期間，不會(huì)因?yàn)閿?shù)據(jù)或表結(jié)構(gòu)的更新，而出現(xiàn)備份文件的數(shù)據(jù)與預(yù)期的不一樣。

舉個(gè)例子大家就知道了。

在全庫(kù)邏輯備份期間，假設(shè)不加全局鎖的場(chǎng)景，看看會(huì)出現(xiàn)什么意外的情況。

如果在全庫(kù)邏輯備份期間，有用戶購(gòu)買了一件商品，一般購(gòu)買商品的業(yè)務(wù)邏輯是會(huì)涉及到多張數(shù)據(jù)庫(kù)表的更細(xì)，比如在用戶表更新該用戶的余額，然后在商品表更新被購(gòu)買的商品的庫(kù)存。

那么，有可能出現(xiàn)這樣的順序：

1. 先備份了用戶表的數(shù)據(jù)；
2. 然后有用戶發(fā)起了購(gòu)買商品的操作；
3. 接著再備份商品表的數(shù)據(jù)。

也就是在備份用戶表和商品表之間，有用戶購(gòu)買了商品。

這種情況下，備份的結(jié)果是用戶表中該用戶的余額并沒(méi)有扣除，反而商品表中該商品的庫(kù)存被減少了，如果后面用這個(gè)備份文件恢復(fù)數(shù)據(jù)庫(kù)數(shù)據(jù)的話，用戶錢沒(méi)少，而庫(kù)存少了，等于用戶白嫖了一件商品。

所以，在全庫(kù)邏輯備份期間，加上全局鎖，就不會(huì)出現(xiàn)上面這種情況了。

加全局鎖又會(huì)帶來(lái)什么缺點(diǎn)呢？

加上全局鎖，意味著整個(gè)數(shù)據(jù)庫(kù)都是只讀狀態(tài)。

那么如果數(shù)據(jù)庫(kù)里有很多數(shù)據(jù)，備份就會(huì)花費(fèi)很多的時(shí)間，關(guān)鍵是備份期間，業(yè)務(wù)只能讀數(shù)據(jù)，而不能更新數(shù)據(jù)，這樣會(huì)造成業(yè)務(wù)停滯。

既然備份數(shù)據(jù)庫(kù)數(shù)據(jù)的時(shí)候，使用全局鎖會(huì)影響業(yè)務(wù)，那有什么其他方式可以避免？

有的，如果數(shù)據(jù)庫(kù)的引擎支持的事務(wù)支持可重復(fù)讀的隔離級(jí)別，那么在備份數(shù)據(jù)庫(kù)之前先開(kāi)啟事務(wù)，會(huì)先創(chuàng)建 Read View，然后整個(gè)事務(wù)執(zhí)行期間都在用這個(gè) Read View，而且由于 MVCC 的支持，備份期間業(yè)務(wù)依然可以對(duì)數(shù)據(jù)進(jìn)行更新操作。

因?yàn)樵诳芍貜?fù)讀的隔離級(jí)別下，即使其他事務(wù)更新了表的數(shù)據(jù)，也不會(huì)影響備份數(shù)據(jù)庫(kù)時(shí)的 Read View，這就是事務(wù)四大特性中的隔離性，這樣備份期間備份的數(shù)據(jù)一直是在開(kāi)啟事務(wù)時(shí)的數(shù)據(jù)。

備份數(shù)據(jù)庫(kù)的工具是 mysqldump，在使用 mysqldump 時(shí)加上 –single-transaction 參數(shù)的時(shí)候，就會(huì)在備份數(shù)據(jù)庫(kù)之前先開(kāi)啟事務(wù)。這種方法只適用于支持「可重復(fù)讀隔離級(jí)別的事務(wù)」的存儲(chǔ)引擎。

InnoDB 存儲(chǔ)引擎默認(rèn)的事務(wù)隔離級(jí)別正是可重復(fù)讀，因此可以采用這種方式來(lái)備份數(shù)據(jù)庫(kù)。

但是，對(duì)于 MyISAM 這種不支持事務(wù)的引擎，在備份數(shù)據(jù)庫(kù)時(shí)就要使用全局鎖的方法。

表級(jí)鎖

MySQL 表級(jí)鎖有哪些？具體怎么用的。

MySQL 里面表級(jí)別的鎖有這幾種：

• 表鎖；
• 元數(shù)據(jù)鎖（MDL）;
• 意向鎖；
• AUTO-INC 鎖；

表鎖

先來(lái)說(shuō)說(shuō)***表鎖***。

如果我們想對(duì)學(xué)生表（t_student）加表鎖，可以使用下面的命令：

//表級(jí)別的共享鎖，也就是讀鎖；
lock tables t_student read;

//表級(jí)別的獨(dú)占鎖，也就是寫(xiě)鎖；
lock tables t_stuent wirte;

需要注意的是，表鎖除了會(huì)限制別的線程的讀寫(xiě)外，也會(huì)限制本線程接下來(lái)的讀寫(xiě)操作。

也就是說(shuō)如果本線程對(duì)學(xué)生表加了「共享表鎖」，那么本線程接下來(lái)如果要對(duì)學(xué)生表執(zhí)行寫(xiě)操作的語(yǔ)句，是會(huì)被阻塞的，當(dāng)然其他線程對(duì)學(xué)生表進(jìn)行寫(xiě)操作時(shí)也會(huì)被阻塞，直到鎖被釋放。

要釋放表鎖，可以使用下面這條命令，會(huì)釋放當(dāng)前會(huì)話的所有表鎖：

unlock tables

另外，當(dāng)會(huì)話退出后，也會(huì)釋放所有表鎖。

不過(guò)盡量避免在使用 InnoDB 引擎的表使用表鎖，因?yàn)楸礞i的顆粒度太大，會(huì)影響并發(fā)性能，InnoDB 牛逼的地方在于實(shí)現(xiàn)了顆粒度更細(xì)的行級(jí)鎖。

元數(shù)據(jù)鎖

再來(lái)說(shuō)說(shuō)***元數(shù)據(jù)鎖（MDL）***。

我們不需要顯示的使用 MDL，因?yàn)楫?dāng)我們對(duì)數(shù)據(jù)庫(kù)表進(jìn)行操作時(shí)，會(huì)自動(dòng)給這個(gè)表加上 MDL：

• 對(duì)一張表進(jìn)行 CRUD 操作時(shí)，加的是 MDL 讀鎖；
• 對(duì)一張表做結(jié)構(gòu)變更操作的時(shí)候，加的是 MDL 寫(xiě)鎖；

MDL 是為了保證當(dāng)用戶對(duì)表執(zhí)行 CRUD 操作時(shí)，防止其他線程對(duì)這個(gè)表結(jié)構(gòu)做了變更。

當(dāng)有線程在執(zhí)行 select 語(yǔ)句（ 加 MDL 讀鎖）的期間，如果有其他線程要更改該表的結(jié)構(gòu)（ 申請(qǐng) MDL 寫(xiě)鎖），那么將會(huì)被阻塞，直到執(zhí)行完 select 語(yǔ)句（ 釋放 MDL 讀鎖）。

反之，當(dāng)有線程對(duì)表結(jié)構(gòu)進(jìn)行變更（ 加 MDL 寫(xiě)鎖）的期間，如果有其他線程執(zhí)行了 CRUD 操作（ 申請(qǐng) MDL 讀鎖），那么就會(huì)被阻塞，直到表結(jié)構(gòu)變更完成（ 釋放 MDL 寫(xiě)鎖）。

MDL 不需要顯示調(diào)用，那它是在什么時(shí)候釋放的?

MDL 是在事務(wù)提交后才會(huì)釋放，這意味著事務(wù)執(zhí)行期間，MDL 是一直持有的。

那如果數(shù)據(jù)庫(kù)有一個(gè)長(zhǎng)事務(wù)（所謂的長(zhǎng)事務(wù)，就是開(kāi)啟了事務(wù)，但是一直還沒(méi)提交），那在對(duì)表結(jié)構(gòu)做變更操作的時(shí)候，可能會(huì)發(fā)生意想不到的事情，比如下面這個(gè)順序的場(chǎng)景：

1. 首先，線程 A 先啟用了事務(wù)（但是一直不提交），然后執(zhí)行一條 select 語(yǔ)句，此時(shí)就先對(duì)該表加上 MDL 讀鎖；
2. 然后，線程 B 也執(zhí)行了同樣的 select 語(yǔ)句，此時(shí)并不會(huì)阻塞，因?yàn)椤缸x讀」并不沖突；
3. 接著，線程 C 修改了表字段，此時(shí)由于線程 A 的事務(wù)并沒(méi)有提交，也就是 MDL 讀鎖還在占用著，這時(shí)線程 C 就無(wú)法申請(qǐng)到 MDL 寫(xiě)鎖，就會(huì)被阻塞，

那么在線程 C 阻塞后，后續(xù)有對(duì)該表的 select 語(yǔ)句，就都會(huì)被阻塞，如果此時(shí)有大量該表的 select 語(yǔ)句的請(qǐng)求到來(lái)，就會(huì)有大量的線程被阻塞住，這時(shí)數(shù)據(jù)庫(kù)的線程很快就會(huì)爆滿了。

為什么線程 C 因?yàn)樯暾?qǐng)不到 MDL 寫(xiě)鎖，而導(dǎo)致后續(xù)的申請(qǐng)讀鎖的查詢操作也會(huì)被阻塞？

這是因?yàn)樯暾?qǐng) MDL 鎖的操作會(huì)形成一個(gè)隊(duì)列，隊(duì)列中寫(xiě)鎖獲取優(yōu)先級(jí)高于讀鎖，一旦出現(xiàn) MDL 寫(xiě)鎖等待，會(huì)阻塞后續(xù)該表的所有 CRUD 操作。

所以為了能安全的對(duì)表結(jié)構(gòu)進(jìn)行變更，在對(duì)表結(jié)構(gòu)變更前，先要看看數(shù)據(jù)庫(kù)中的長(zhǎng)事務(wù)，是否有事務(wù)已經(jīng)對(duì)表加上了 MDL 讀鎖，如果可以考慮 kill 掉這個(gè)長(zhǎng)事務(wù)，然后再做表結(jié)構(gòu)的變更。

意向鎖

接著，說(shuō)說(shuō)***意向鎖***。

• 在使用 InnoDB 引擎的表里對(duì)某些記錄加上「共享鎖」之前，需要先在表級(jí)別加上一個(gè)「意向共享鎖」；
• 在使用 InnoDB 引擎的表里對(duì)某些紀(jì)錄加上「獨(dú)占鎖」之前，需要先在表級(jí)別加上一個(gè)「意向獨(dú)占鎖」；

也就是，當(dāng)執(zhí)行插入、更新、刪除操作，需要先對(duì)表加上「意向獨(dú)占鎖」，然后對(duì)該記錄加獨(dú)占鎖。

而普通的 select 是不會(huì)加行級(jí)鎖的，普通的 select 語(yǔ)句是利用 MVCC 實(shí)現(xiàn)一致性讀，是無(wú)鎖的。

不過(guò)，select 也是可以對(duì)記錄加共享鎖和獨(dú)占鎖的，具體方式如下：

//先在表上加上意向共享鎖，然后對(duì)讀取的記錄加獨(dú)占鎖
select ... lock in share mode;

//先表上加上意向獨(dú)占鎖，然后對(duì)讀取的記錄加獨(dú)占鎖
select ... for update;

意向共享鎖和意向獨(dú)占鎖是表級(jí)鎖，不會(huì)和行級(jí)的共享鎖和獨(dú)占鎖發(fā)生沖突，而且意向鎖之間也不會(huì)發(fā)生沖突，只會(huì)和共享表鎖（lock tables … read）和獨(dú)占表鎖（lock tables … write）發(fā)生沖突。

表鎖和行鎖是滿足讀讀共享、讀寫(xiě)互斥、寫(xiě)寫(xiě)互斥的。

如果沒(méi)有「意向鎖」，那么加「獨(dú)占表鎖」時(shí)，就需要遍歷表里所有記錄，查看是否有記錄存在獨(dú)占鎖，這樣效率會(huì)很慢。

那么有了「意向鎖」，由于在對(duì)記錄加獨(dú)占鎖前，先會(huì)加上表級(jí)別的意向獨(dú)占鎖，那么在加「獨(dú)占表鎖」時(shí)，直接查該表是否有意向獨(dú)占鎖，如果有就意味著表里已經(jīng)有記錄被加了獨(dú)占鎖，這樣就不用去遍歷表里的記錄。

所以，意向鎖的目的是為了快速判斷表里是否有記錄被加鎖。

AUTO-INC 鎖

最后，說(shuō)說(shuō) AUTO-INC 鎖。

在為某個(gè)字段聲明 AUTO_INCREMENT 屬性時(shí)，之后可以在插入數(shù)據(jù)時(shí)，可以不指定該字段的值，數(shù)據(jù)庫(kù)會(huì)自動(dòng)給該字段賦值遞增的值，這主要是通過(guò) AUTO-INC 鎖實(shí)現(xiàn)的。

AUTO-INC 鎖是特殊的表鎖機(jī)制，鎖不是再一個(gè)事務(wù)提交后才釋放，而是再執(zhí)行完插入語(yǔ)句后就會(huì)立即釋放。

在插入數(shù)據(jù)時(shí)，會(huì)加一個(gè)表級(jí)別的 AUTO-INC 鎖，然后為被 AUTO_INCREMENT 修飾的字段賦值遞增的值，等插入語(yǔ)句執(zhí)行完成后，才會(huì)把 AUTO-INC 鎖釋放掉。

那么，一個(gè)事務(wù)在持有 AUTO-INC 鎖的過(guò)程中，其他事務(wù)的如果要向該表插入語(yǔ)句都會(huì)被阻塞，從而保證插入數(shù)據(jù)時(shí)，被 AUTO_INCREMENT 修飾的字段的值是連續(xù)遞增的。

但是， AUTO-INC 鎖再對(duì)大量數(shù)據(jù)進(jìn)行插入的時(shí)候，會(huì)影響插入性能，因?yàn)榱硪粋€(gè)事務(wù)中的插入會(huì)被阻塞。

因此， 在 MySQL 5.1.22 版本開(kāi)始，InnoDB 存儲(chǔ)引擎提供了一種輕量級(jí)的鎖來(lái)實(shí)現(xiàn)自增。

一樣也是在插入數(shù)據(jù)的時(shí)候，會(huì)為被 AUTO_INCREMENT 修飾的字段加上輕量級(jí)鎖，然后給該字段賦值一個(gè)自增的值，就把這個(gè)輕量級(jí)鎖釋放了，而不需要等待整個(gè)插入語(yǔ)句執(zhí)行完后才釋放鎖。

InnoDB 存儲(chǔ)引擎提供了個(gè) innodb_autoinc_lock_mode 的系統(tǒng)變量，是用來(lái)控制選擇用 AUTO-INC 鎖，還是輕量級(jí)的鎖。

• 當(dāng) innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 鎖；
• 當(dāng) innodb_autoinc_lock_mode = 2，就采用輕量級(jí)鎖；
• 當(dāng) innodb_autoinc_lock_mode = 1，這個(gè)是默認(rèn)值，兩種鎖混著用，如果能夠確定插入記錄的數(shù)量就采用輕量級(jí)鎖，不確定時(shí)就采用 AUTO-INC 鎖。

不過(guò)，當(dāng) innodb_autoinc_lock_mode = 2 是性能最高的方式，但是會(huì)帶來(lái)一定的問(wèn)題。因?yàn)椴l(fā)插入的存在，在每次插入時(shí)，自增長(zhǎng)的值可能不是連續(xù)的，這在有主從復(fù)制的場(chǎng)景中是不安全的。

行級(jí)鎖有哪些？

InnoDB 引擎是支持行級(jí)鎖的，而 MyISAM 引擎并不支持行級(jí)鎖。

行級(jí)鎖的類型主要有三類：

• Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；
• Gap Lock，間隙鎖，鎖定一個(gè)范圍，但是不包含記錄本身；
• Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個(gè)范圍，并且鎖定記錄本身。

前面也提到，普通的 select 語(yǔ)句是不會(huì)對(duì)記錄加鎖的，如果要在查詢時(shí)對(duì)記錄加行鎖，可以使用下面這兩個(gè)方式：

//對(duì)讀取的記錄加共享鎖
select ... lock in share mode;

//對(duì)讀取的記錄加獨(dú)占鎖
select ... for update;

上面這兩條語(yǔ)句必須再一個(gè)事務(wù)中，當(dāng)事務(wù)提交了，鎖就會(huì)被釋放，因此在使用這兩條語(yǔ)句的時(shí)候，要加上 begin、start transaction 或者 set autocommit = 0。

那具體跟在哪些紀(jì)錄上加鎖，就跟具體的 select 語(yǔ)句有關(guān)系了，比較復(fù)雜，這個(gè)留到下篇再講啦。

?