說說MySQL有哪些鎖

正文

這次，來說說 MySQL 的鎖，主要是 Q&A 的形式，看起來會比較輕松。

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

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

全局鎖

全局鎖是怎么用的？

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

flushtableswithreadlock

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

對數(shù)據(jù)的增刪改操作，比如 insert、delete、update等語句；

對表結構的更改操作，比如 alter table、drop table 等語句。

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

unlocktables

當然，當會話斷開了，全局鎖會被自動釋放。

全局鎖應用場景是什么？

全局鎖主要應用于做全庫邏輯備份，這樣在備份數(shù)據(jù)庫期間，不會因為數(shù)據(jù)或表結構的更新，而出現(xiàn)備份文件的數(shù)據(jù)與預期的不一樣。

舉個例子大家就知道了。

在全庫邏輯備份期間，假設不加全局鎖的場景，看看會出現(xiàn)什么意外的情況。

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

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

先備份了用戶表的數(shù)據(jù)；

然后有用戶發(fā)起了購買商品的操作；

接著再備份商品表的數(shù)據(jù)。

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

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

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

加全局鎖又會帶來什么缺點呢？

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

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

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

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

因為在可重復讀的隔離級別下，即使其他事務更新了表的數(shù)據(jù)，也不會影響備份數(shù)據(jù)庫時的 Read View，這就是事務四大特性中的隔離性，這樣備份期間備份的數(shù)據(jù)一直是在開啟事務時的數(shù)據(jù)。

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

InnoDB 存儲引擎默認的事務隔離級別正是可重復讀，因此可以采用這種方式來備份數(shù)據(jù)庫。

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

表級鎖

MySQL 表級鎖有哪些？具體怎么用的。

MySQL 里面表級別的鎖有這幾種：

表鎖；

元數(shù)據(jù)鎖（MDL）;

意向鎖；

AUTO-INC 鎖；

表鎖

先來說說表鎖。

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

//表級別的共享鎖，也就是讀鎖；
locktablest_studentread;

//表級別的獨占鎖，也就是寫鎖；
locktablest_stuentwrite;

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

也就是說如果本線程對學生表加了「共享表鎖」，那么本線程接下來如果要對學生表執(zhí)行寫操作的語句，是會被阻塞的，當然其他線程對學生表進行寫操作時也會被阻塞，直到鎖被釋放。

要釋放表鎖，可以使用下面這條命令，會釋放當前會話的所有表鎖：

unlocktables

另外，當會話退出后，也會釋放所有表鎖。

不過盡量避免在使用 InnoDB 引擎的表使用表鎖，因為表鎖的顆粒度太大，會影響并發(fā)性能，InnoDB 牛逼的地方在于實現(xiàn)了顆粒度更細的行級鎖。

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

再來說說元數(shù)據(jù)鎖（MDL）。

我們不需要顯示的使用 MDL，因為當我們對數(shù)據(jù)庫表進行操作時，會自動給這個表加上 MDL：

對一張表進行 CRUD 操作時，加的是 MDL 讀鎖；

對一張表做結構變更操作的時候，加的是 MDL 寫鎖；

MDL 是為了保證當用戶對表執(zhí)行 CRUD 操作時，防止其他線程對這個表結構做了變更。

當有線程在執(zhí)行 select 語句（ 加 MDL 讀鎖）的期間，如果有其他線程要更改該表的結構（ 申請 MDL 寫鎖），那么將會被阻塞，直到執(zhí)行完 select 語句（ 釋放 MDL 讀鎖）。

反之，當有線程對表結構進行變更（ 加 MDL 寫鎖）的期間，如果有其他線程執(zhí)行了 CRUD 操作（ 申請 MDL 讀鎖），那么就會被阻塞，直到表結構變更完成（ 釋放 MDL 寫鎖）。

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

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

那如果數(shù)據(jù)庫有一個長事務（所謂的長事務，就是開啟了事務，但是一直還沒提交），那在對表結構做變更操作的時候，可能會發(fā)生意想不到的事情，比如下面這個順序的場景：

首先，線程 A 先啟用了事務（但是一直不提交），然后執(zhí)行一條 select 語句，此時就先對該表加上 MDL 讀鎖；

然后，線程 B 也執(zhí)行了同樣的 select 語句，此時并不會阻塞，因為「讀讀」并不沖突；

接著，線程 C 修改了表字段，此時由于線程 A 的事務并沒有提交，也就是 MDL 讀鎖還在占用著，這時線程 C 就無法申請到 MDL 寫鎖，就會被阻塞，

那么在線程 C 阻塞后，后續(xù)有對該表的 select 語句，就都會被阻塞，如果此時有大量該表的 select 語句的請求到來，就會有大量的線程被阻塞住，這時數(shù)據(jù)庫的線程很快就會爆滿了。

為什么線程 C 因為申請不到 MDL 寫鎖，而導致后續(xù)的申請讀鎖的查詢操作也會被阻塞？

這是因為申請 MDL 鎖的操作會形成一個隊列，隊列中寫鎖獲取優(yōu)先級高于讀鎖，一旦出現(xiàn) MDL 寫鎖等待，會阻塞后續(xù)該表的所有 CRUD 操作。

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

意向鎖

接著，說說意向鎖。

在使用 InnoDB 引擎的表里對某些記錄加上「共享鎖」之前，需要先在表級別加上一個「意向共享鎖」；

在使用 InnoDB 引擎的表里對某些紀錄加上「獨占鎖」之前，需要先在表級別加上一個「意向獨占鎖」；

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

而普通的 select 是不會加行級鎖的，普通的 select 語句是利用 MVCC 實現(xiàn)一致性讀，是無鎖的。

不過，select 也是可以對記錄加共享鎖和獨占鎖的，具體方式如下：

//先在表上加上意向共享鎖，然后對讀取的記錄加共享鎖
select...lockinsharemode;

//先表上加上意向獨占鎖，然后對讀取的記錄加獨占鎖
select...forupdate;

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

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

如果沒有「意向鎖」，那么加「獨占表鎖」時，就需要遍歷表里所有記錄，查看是否有記錄存在獨占鎖，這樣效率會很慢。

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

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

AUTO-INC 鎖

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

之后可以在插入數(shù)據(jù)時，可以不指定主鍵的值，數(shù)據(jù)庫會自動給主鍵賦值遞增的值，這主要是通過 AUTO-INC 鎖實現(xiàn)的。

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

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

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

但是， AUTO-INC 鎖再對大量數(shù)據(jù)進行插入的時候，會影響插入性能，因為另一個事務中的插入會被阻塞。

因此， 在 MySQL 5.1.22 版本開始，InnoDB 存儲引擎提供了一種輕量級的鎖來實現(xiàn)自增。

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

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

當 innodb_autoinc_lock_mode = 0，就采用 AUTO-INC 鎖，語句執(zhí)行結束后才釋放鎖；

當 innodb_autoinc_lock_mode = 2，就采用輕量級鎖，申請自增主鍵后就釋放鎖，并不需要等語句執(zhí)行后才釋放。

當 innodb_autoinc_lock_mode = 1：

普通 insert 語句，自增鎖在申請之后就馬上釋放；

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

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

舉個例子，考慮下面場景：

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

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

session B 先插入了兩個記錄，(1,1,1)、(2,2,2)；

然后，session A 來申請自增 id 得到 id=3，插入了（3,5,5)；

之后，session B 繼續(xù)執(zhí)行，插入兩條記錄 (4,3,3)、 (5,4,4)。

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

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

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

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

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

行級鎖

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

前面也提到，普通的 select 語句是不會對記錄加鎖的，因為它屬于快照讀。如果要在查詢時對記錄加行鎖，可以使用下面這兩個方式，這種查詢會加鎖的語句稱為鎖定讀。

//對讀取的記錄加共享鎖
select...lockinsharemode;

//對讀取的記錄加獨占鎖
select...forupdate;

上面這兩條語句必須在一個事務中，因為當事務提交了，鎖就會被釋放，所以在使用這兩條語句的時候，要加上 begin、start transaction 或者 set autocommit = 0。

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

行級鎖的類型主要有三類：

Record Lock，記錄鎖，也就是僅僅把一條記錄鎖上；

Gap Lock，間隙鎖，鎖定一個范圍，但是不包含記錄本身；

Next-Key Lock：Record Lock + Gap Lock 的組合，鎖定一個范圍，并且鎖定記錄本身。

Record Lock

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

當一個事務對一條記錄加了 S 型記錄鎖后，其他事務也可以繼續(xù)對該記錄加 S 型記錄鎖（S 型與 S 鎖兼容），但是不可以對該記錄加 X 型記錄鎖（S 型與 X 鎖不兼容）;

當一個事務對一條記錄加了 X 型記錄鎖后，其他事務既不可以對該記錄加 S 型記錄鎖（S 型與 X 鎖不兼容），也不可以對該記錄加 X 型記錄鎖（X 型與 X 鎖不兼容）。

舉個例子，當一個事務執(zhí)行了下面這條語句：

mysql>begin;
mysql>select*fromt_testwhereid=1forupdate;

就是對 t_test 表中主鍵 id 為 1 的這條記錄加上 X 型的記錄鎖，這樣其他事務就無法對這條記錄進行修改了。

當事務執(zhí)行 commit 后，事務過程中生成的鎖都會被釋放。

Gap Lock

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

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

間隙鎖雖然存在 X 型間隙鎖和 S 型間隙鎖，但是并沒有什么區(qū)別，間隙鎖之間是兼容的，即兩個事務可以同時持有包含共同間隙范圍的間隙鎖，并不存在互斥關系，因為間隙鎖的目的是防止插入幻影記錄而提出的。

Next-Key Lock

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

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

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

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

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

雖然相同范圍的間隙鎖是多個事務相互兼容的，但對于記錄鎖，我們是要考慮 X 型與 S 型關系，X 型的記錄鎖與 X 型的記錄鎖是沖突的。

插入意向鎖

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

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

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

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

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

如果說間隙鎖鎖住的是一個區(qū)間，那么「插入意向鎖」鎖住的就是一個點。因而從這個角度來說，插入意向鎖確實是一種特殊的間隙鎖。

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

審核編輯：劉清