MySQL 全局鎖、表級(jí)鎖、行級(jí)鎖，你搞清楚了嗎？

大家好，我是小林。

最近重新補(bǔ)充了《MySQL 有哪些鎖》文章內(nèi)容：

所以，現(xiàn)在內(nèi)容還是比較全面的，基本把 MySQL 用到的鎖都說(shuō)了一遍，大家可以在復(fù)習(xí)復(fù)習(xí)。

正文

這次，來(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ì)被阻塞：

如果要釋放全局鎖，則要執(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ù)表的更新，比如在用戶表更新該用戶的余額，然后在商品表更新被購(gòu)買的商品的庫(kù)存。

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

也就是在備份用戶表和商品表之間，有用戶購(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í)別的鎖有這幾種：

表鎖

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

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

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

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

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

也就是說(shuō)如果本線程對(duì)學(xué)生表加了「共享表鎖」，那么本線程接下來(lái)如果要對(duì)學(xué)生表執(zhí)行寫操作的語(yǔ)句，是會(huì)被阻塞的，當(dāng)然其他線程對(duì)學(xué)生表進(jìn)行寫操作時(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：

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

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

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

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)景：

那么在線程 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 寫鎖，而導(dǎo)致后續(xù)的申請(qǐng)讀鎖的查詢操作也會(huì)被阻塞？

這是因?yàn)樯暾?qǐng) MDL 鎖的操作會(huì)形成一個(gè)隊(duì)列，隊(duì)列中寫鎖獲取優(yōu)先級(jí)高于讀鎖，一旦出現(xiàn) MDL 寫鎖等待，會(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ō)意向鎖。

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

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

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

//先在表上加上意向共享鎖，然后對(duì)讀取的記錄加共享鎖
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ā)生沖突。

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

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

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

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

AUTO-INC 鎖

表里的主鍵通常都會(huì)設(shè)置成自增的，這是通過(guò)對(duì)主鍵字段聲明 AUTO_INCREMENT 屬性實(shí)現(xiàn)的。

之后可以在插入數(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í)的鎖。

• 普通 insert 語(yǔ)句，自增鎖在申請(qǐng)之后就馬上釋放；

  類似 insert … select 這樣的批量插入數(shù)據(jù)的語(yǔ)句，自增鎖還是要等語(yǔ)句結(jié)束后才被釋放；

當(dāng) innodb_autoinc_lock_mode = 2 是性能最高的方式，但是當(dāng)搭配  binlog 的日志格式是 statement 一起使用的時(shí)候，在「主從復(fù)制的場(chǎng)景」中會(huì)發(fā)生數(shù)據(jù)不一致的問(wèn)題。

舉個(gè)例子，考慮下面場(chǎng)景：

session A 往表 t 中插入了 4 行數(shù)據(jù)，然后創(chuàng)建了一個(gè)相同結(jié)構(gòu)的表 t2，然后兩個(gè) session 同時(shí)執(zhí)行向表 t2 中插入數(shù)據(jù)。

如果 innodb_autoinc_lock_mode = 2，意味著「申請(qǐng)自增主鍵后就釋放鎖，不必等插入語(yǔ)句執(zhí)行完」。那么就可能出現(xiàn)這樣的情況：

可以看到，session B 的 insert 語(yǔ)句，生成的 id 不連續(xù)。

當(dāng)「主庫(kù)」發(fā)生了這種情況，binlog 面對(duì) t2 表的更新只會(huì)記錄這兩個(gè) session 的 insert 語(yǔ)句，如果 binlog_format=statement，記錄的語(yǔ)句就是原始語(yǔ)句。記錄的順序要么先記 session A 的 insert 語(yǔ)句，要么先記 session B 的 insert 語(yǔ)句。

但不論是哪一種，這個(gè) binlog 拿去「從庫(kù)」執(zhí)行，這時(shí)從庫(kù)是按「順序」執(zhí)行語(yǔ)句的，只有當(dāng)執(zhí)行完一條 SQL 語(yǔ)句后，才會(huì)執(zhí)行下一條 SQL。因此，在從庫(kù)上「不會(huì)」發(fā)生像主庫(kù)那樣兩個(gè) session 「同時(shí)」執(zhí)行向表 t2 中插入數(shù)據(jù)的場(chǎng)景。所以，在備庫(kù)上執(zhí)行了 session B 的 insert 語(yǔ)句，生成的結(jié)果里面，id 都是連續(xù)的。這時(shí)，主從庫(kù)就發(fā)生了數(shù)據(jù)不一致。

要解決這問(wèn)題，binlog 日志格式要設(shè)置為 row，這樣在 binlog 里面記錄的是主庫(kù)分配的自增值，到備庫(kù)執(zhí)行的時(shí)候，主庫(kù)的自增值是什么，從庫(kù)的自增值就是什么。

所以，當(dāng) innodb_autoinc_lock_mode = 2 時(shí)，并且 binlog_format = row，既能提升并發(fā)性，又不會(huì)出現(xiàn)數(shù)據(jù)一致性問(wèn)題。

行級(jí)鎖

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

前面也提到，普通的 select 語(yǔ)句是不會(huì)對(duì)記錄加鎖的，因?yàn)樗鼘儆诳煺兆x。如果要在查詢時(shí)對(duì)記錄加行鎖，可以使用下面這兩個(gè)方式，這種查詢會(huì)加鎖的語(yǔ)句稱為鎖定讀。

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

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

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

共享鎖（S鎖）滿足讀讀共享，讀寫互斥。獨(dú)占鎖（X鎖）滿足寫寫互斥、讀寫互斥。

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

Record Lock

Record Lock 稱為記錄鎖，鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分的：

舉個(gè)例子，當(dāng)一個(gè)事務(wù)執(zhí)行了下面這條語(yǔ)句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是對(duì)  t_test 表中主鍵 id 為 1 的這條記錄加上 X 型的記錄鎖，這樣其他事務(wù)就無(wú)法對(duì)這條記錄進(jìn)行修改了。

當(dāng)事務(wù)執(zhí)行 commit 后，事務(wù)過(guò)程中生成的鎖都會(huì)被釋放。

Gap Lock

Gap Lock 稱為間隙鎖，只存在于可重復(fù)讀隔離級(jí)別，目的是為了解決可重復(fù)讀隔離級(jí)別下幻讀的現(xiàn)象。

假設(shè)，表中有一個(gè)范圍 id 為（3，5）間隙鎖，那么其他事務(wù)就無(wú)法插入 id = 4 這條記錄了，這樣就有效的防止幻讀現(xiàn)象的發(fā)生。

間隙鎖雖然存在  X 型間隙鎖和 S 型間隙鎖，但是并沒(méi)有什么區(qū)別，間隙鎖之間是兼容的，即兩個(gè)事務(wù)可以同時(shí)持有包含共同間隙范圍的間隙鎖，并不存在互斥關(guān)系，因?yàn)殚g隙鎖的目的是防止插入幻影記錄而提出的。

Next-Key Lock

Next-Key Lock 稱為臨鍵鎖，是 Record Lock + Gap Lock 的組合，鎖定一個(gè)范圍，并且鎖定記錄本身。

假設(shè)，表中有一個(gè)范圍 id 為（3，5] 的 next-key lock，那么其他事務(wù)即不能插入 id = 4 記錄，也不能修改 id = 5 這條記錄。

所以，next-key lock 即能保護(hù)該記錄，又能阻止其他事務(wù)將新紀(jì)錄插入到被保護(hù)記錄前面的間隙中。

next-key lock 是包含間隙鎖+記錄鎖的，如果一個(gè)事務(wù)獲取了 X 型的 next-key lock，那么另外一個(gè)事務(wù)在獲取相同范圍的 X 型的 next-key lock 時(shí)，是會(huì)被阻塞的。

比如，一個(gè)事務(wù)持有了范圍為 (1, 10] 的 X 型的 next-key lock，那么另外一個(gè)事務(wù)在獲取相同范圍的 X 型的 next-key lock 時(shí)，就會(huì)被阻塞。

雖然相同范圍的間隙鎖是多個(gè)事務(wù)相互兼容的，但對(duì)于記錄鎖，我們是要考慮 X 型與 S 型關(guān)系，X 型的記錄鎖與 X 型的記錄鎖是沖突的。

插入意向鎖

一個(gè)事務(wù)在插入一條記錄的時(shí)候，需要判斷插入位置是否已被其他事務(wù)加了間隙鎖（next-key lock 也包含間隙鎖）。

如果有的話，插入操作就會(huì)發(fā)生阻塞，直到擁有間隙鎖的那個(gè)事務(wù)提交為止（釋放間隙鎖的時(shí)刻），在此期間會(huì)生成一個(gè)插入意向鎖，表明有事務(wù)想在某個(gè)區(qū)間插入新記錄，但是現(xiàn)在處于等待狀態(tài)。

舉個(gè)例子，假設(shè)事務(wù) A 已經(jīng)對(duì)表加了一個(gè)范圍 id 為（3，5）間隙鎖。

當(dāng)事務(wù) A 還沒(méi)提交的時(shí)候，事務(wù) B 向該表插入一條 id = 4 的新記錄，這時(shí)會(huì)判斷到插入的位置已經(jīng)被事務(wù) A 加了間隙鎖，于是事物 B 會(huì)生成一個(gè)插入意向鎖，然后將鎖的狀態(tài)設(shè)置為等待狀態(tài)（PS：MySQL 加鎖時(shí)，是先生成鎖結(jié)構(gòu)，然后設(shè)置鎖的狀態(tài)，如果鎖狀態(tài)是等待狀態(tài)，并不是意味著事務(wù)成功獲取到了鎖，只有當(dāng)鎖狀態(tài)為正常狀態(tài)時(shí)，才代表事務(wù)成功獲取到了鎖），此時(shí)事務(wù) B 就會(huì)發(fā)生阻塞，直到事務(wù) A 提交了事務(wù)。

插入意向鎖名字雖然有意向鎖，但是它并不是意向鎖，它是一種特殊的間隙鎖，屬于行級(jí)別鎖。

如果說(shuō)間隙鎖鎖住的是一個(gè)區(qū)間，那么「插入意向鎖」鎖住的就是一個(gè)點(diǎn)。因而從這個(gè)角度來(lái)說(shuō)，插入意向鎖確實(shí)是一種特殊的間隙鎖。

插入意向鎖與間隙鎖的另一個(gè)非常重要的差別是：盡管「插入意向鎖」也屬于間隙鎖，但兩個(gè)事務(wù)卻不能在同一時(shí)間內(nèi)，一個(gè)擁有間隙鎖，另一個(gè)擁有該間隙區(qū)間內(nèi)的插入意向鎖（當(dāng)然，插入意向鎖如果不在間隙鎖區(qū)間內(nèi)則是可以的）。

參考資料：