快捷導(dǎo)航

MySQL全局鎖和表鎖的深入理解

更新時(shí)間：2019年06月23日 11:45:12 作者：keme

這篇文章主要給大家介紹了關(guān)于MySQL全局鎖和表鎖的相關(guān)資料，文中通過(guò)示例代碼介紹的非常詳細(xì)，對(duì)大家學(xué)習(xí)或者使用MySQL具有一定的參考學(xué)習(xí)價(jià)值，需要的朋友們下面來(lái)一起學(xué)習(xí)學(xué)習(xí)吧

前言

根據(jù)加鎖的范圍，MySQL 里面的鎖大致可以分成全局鎖，表級(jí)鎖，行鎖。

行鎖已經(jīng)在前面幾篇文章說(shuō)過(guò)

1. 全局鎖

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖。MySQL 提供了一個(gè)加全局讀鎖的方法，命令是Flush tables with read lock (FTWRL)。

當(dāng)你需要讓整個(gè)庫(kù)處于只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線程的以下語(yǔ)句會(huì)被阻塞：數(shù)據(jù)更新語(yǔ)句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語(yǔ)句（包括建表、修改表結(jié)構(gòu)等）和更新類(lèi)事務(wù)的提交語(yǔ)句。

1.1 全局鎖使用場(chǎng)景

全局鎖的典型使用場(chǎng)景是，做全庫(kù)邏輯備份（mysqldump）。重新做主從時(shí)候

也就是把整庫(kù)每個(gè)表都 select 出來(lái)存成文本。

以前有一種做法，是通過(guò) FTWRL 確保不會(huì)有其他線程對(duì)數(shù)據(jù)庫(kù)做更新，然后對(duì)整個(gè)庫(kù)做備份。注意，在備份過(guò)程中整個(gè)庫(kù)完全處于只讀狀態(tài)。

數(shù)據(jù)庫(kù)只讀狀態(tài)的危險(xiǎn)性：

如果你在主庫(kù)上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就能停止。如果你在從庫(kù)上備份，那么備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過(guò)來(lái)的binlog，會(huì)導(dǎo)致主從延遲。

注：上面邏輯備份，是不加--single-transaction參數(shù)

看來(lái)加全局鎖不太好。但是細(xì)想一下，備份為什么要加鎖呢？來(lái)看一下不加鎖會(huì)有什么問(wèn)題？

1.2 不加鎖產(chǎn)生的問(wèn)題

比如手機(jī)卡，購(gòu)買(mǎi)套餐信息

這里分為兩張表 u_acount (用于余額表)，u_pricing (資費(fèi)套餐表)
步驟:
1 . u_account 表中數(shù)據(jù) 用戶(hù)A 余額：300
u_pricing 表中數(shù)據(jù) 用戶(hù)A 套餐：空

2. 發(fā)起備份，備份過(guò)程中先備份u_account表，備份完了這個(gè)表，這個(gè)時(shí)候u_account 用戶(hù)余額是300
3. 這個(gè)時(shí)候套用戶(hù)購(gòu)買(mǎi)了一個(gè)資費(fèi)套餐100，餐購(gòu)買(mǎi)完成，寫(xiě)入到u_print套餐表購(gòu)買(mǎi)成功，備份期間的數(shù)據(jù)。
4. 備份完成

可以看到備份的結(jié)果是，u_account 表中的數(shù)據(jù)沒(méi)有變， u_pricing 表中的數(shù)據(jù) 已近購(gòu)買(mǎi)了資費(fèi)套餐100.

哪這時(shí)候用這個(gè)備份文件來(lái)恢復(fù)數(shù)據(jù)的話(huà)，用戶(hù)A 賺了100 ，用戶(hù)是不是很舒服啊。但是你的想想公司利益啊?！　?/p>

也就是說(shuō)，不加鎖的話(huà)，備份系統(tǒng)備份的得到的庫(kù)不是一個(gè)邏輯時(shí)間點(diǎn)，這個(gè)數(shù)據(jù)是邏輯不一致的。

1.3 為什么需要全局讀鎖(FTWRL)

可能有的人在疑惑，官方自帶的邏輯備份工具是 mysqldump。當(dāng) mysqldump 使用參數(shù)--single-transaction的時(shí)候，導(dǎo)數(shù)據(jù)之前就會(huì)啟動(dòng)一個(gè)事務(wù)，來(lái)確保拿到一致性快照視圖。而由于 MVCC 的支持，這個(gè)過(guò)程中數(shù)據(jù)是可以正常更新的。

為什么還需要 FTWRL 呢？
一致性讀是好，但前提是引擎要支持這個(gè)隔離級(jí)別。比如，對(duì)于 MyISAM 這種不支持事務(wù)的引擎，如果備份過(guò)程中有更新，總是只能取到最新的數(shù)據(jù)，那么就破壞了備份的一致性。這時(shí)，我們就需要使用FTWRL 命令了。

所以，single-transaction 方法只適用于所有的表使用事務(wù)引擎的庫(kù)。如果有的表使用了不支持事務(wù)的引擎，那么備份就只能通過(guò) FTWRL 方法。這往往是 DBA 要求業(yè)務(wù)開(kāi)發(fā)人員使用 InnoDB 替代 MyISAM 的原因之一。

1.4 全局鎖兩種方法

一.FLUSH TABLES WRITE READ LOCK

二.set global readonly=true

既然要全庫(kù)只讀，為什么不使用 set global readonly=true 的方式呢？確實(shí) readonly 方式也可以讓全庫(kù)進(jìn)入只讀狀態(tài)，但我還是會(huì)建議你用 FTWRL 方式，主要有幾個(gè)原因：

一是，在有些系統(tǒng)中，readonly 的值會(huì)被用來(lái)做其他邏輯，比如用來(lái)判斷一個(gè)庫(kù)是主庫(kù)還是備庫(kù)。因此，修改 global 變量的方式影響面更大，我不建議你使用。

二是，在異常處理機(jī)制上有差異。如果執(zhí)行FTWRL 命令之后由于客戶(hù)端發(fā)生異常斷開(kāi)，那么 MySQL 會(huì)自動(dòng)釋放這個(gè)全局鎖，整個(gè)庫(kù)回到可以正常更新的狀態(tài)。而將整個(gè)庫(kù)設(shè)置為 readonly 之后，如果客戶(hù)端發(fā)生異常，則數(shù)據(jù)庫(kù)就會(huì)一直保持 readonly 狀態(tài)，這樣會(huì)導(dǎo)致整個(gè)庫(kù)長(zhǎng)時(shí)間處于不可寫(xiě)狀態(tài)，風(fēng)險(xiǎn)較高。

三是，readonly 對(duì)super用戶(hù)權(quán)限無(wú)效

注：業(yè)務(wù)的更新不只是增刪改數(shù)據(jù)（DML)，還有可能是加字段等修改表結(jié)構(gòu)的操作（DDL）。不論是哪種方法，一個(gè)庫(kù)被全局鎖上以后，你要對(duì)里面任何一個(gè)表做加字段操作，都是會(huì)被鎖住的。

即使沒(méi)有被全局鎖住，加字段也不是就能一帆風(fēng)順的，還有表級(jí)鎖了

2. 表級(jí)鎖

MySQL 里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lock，MDL)。

2.1 表鎖

lock tables 表名 read;#該表可以讀，不能ddl 和 dml 中增刪改，只能讀取表數(shù)據(jù)

lock tables 表名 read;# 既不能讀，也不能寫(xiě)

表鎖的語(yǔ)法是 lock tables … read/write。與 FTWRL 類(lèi)似，可以用 unlock tables 主動(dòng)釋放鎖，也可以在客戶(hù)端斷開(kāi)的時(shí)候自動(dòng)釋放。需要注意，lock tables 語(yǔ)法除了會(huì)限制別的線程的讀寫(xiě)外，也限定了本線程接下來(lái)的操作對(duì)象。

舉個(gè)例子, 如果在某個(gè)線程 A 中執(zhí)行 lock tables t1 read, t2 write; 這個(gè)語(yǔ)句，則其他線程寫(xiě) t1、讀寫(xiě) t2 的語(yǔ)句都會(huì)被阻塞。同時(shí)，線程 A 在執(zhí)行 unlock tables 之前，也只能執(zhí)行讀 t1、讀寫(xiě) t2 的操作。連寫(xiě) t1 都不允許，自然也不能訪問(wèn)其他表。

在還沒(méi)有出現(xiàn)更細(xì)粒度的鎖的時(shí)候，表鎖是最常用的處理并發(fā)的方式。而對(duì)于 InnoDB 這種支持行鎖的引擎，一般不使用 lock tables 命令來(lái)控制并發(fā)，畢竟鎖住整個(gè)表的影響面還是太大

2.2 MDL 鎖

另一類(lèi)表級(jí)的鎖是 MDL（metadata lock)。MDL 不需要顯式使用，在訪問(wèn)一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。MDL 的作用是，保證讀寫(xiě)的正確性。你可以想象一下，如果一個(gè)查詢(xún)正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對(duì)這個(gè)表結(jié)構(gòu)做變更，刪了一列，那么查詢(xún)線程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加 MDL讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加 MDL 寫(xiě)鎖

讀鎖之間不互斥，因此你可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查。
讀寫(xiě)鎖之間、寫(xiě)鎖之間是互斥的，用來(lái)保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開(kāi)始執(zhí)行。

雖然 MDL 鎖是系統(tǒng)默認(rèn)會(huì)加的，但卻是你不能忽略的一個(gè)機(jī)制。

比如下面這個(gè)例子，我經(jīng)?？吹接腥说舻竭@個(gè)坑里：給一個(gè)小表加個(gè)字段，導(dǎo)致整個(gè)庫(kù)掛了。

肯定知道，給一個(gè)表加字段，或者修改字段，或者加索引，需要掃描全表的數(shù)據(jù)。在對(duì)大表操作的時(shí)候，你肯定會(huì)特別小心，以免對(duì)線上服務(wù)造成影響。而實(shí)際上，即使是小表，操作不慎也會(huì)出問(wèn)題。我們來(lái)看一下下面的操作序列，假設(shè)表 t 是一個(gè)小表。

show full processlist查看mdl 鎖詳情

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

之后 session C 會(huì)被 blocked，是因?yàn)?session A 的 MDL 讀鎖還沒(méi)有釋放，而 sessionC 需要MDL 寫(xiě)鎖，因此只能被阻塞。

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

如果某個(gè)表上的查詢(xún)語(yǔ)句頻繁，而且客戶(hù)端有重試機(jī)制，也就是說(shuō)超時(shí)后會(huì)再起一個(gè)新session 再請(qǐng)求的話(huà)，這個(gè)庫(kù)的線程很快就會(huì)爆滿(mǎn)。

事務(wù)中的 MDL 鎖，在語(yǔ)句執(zhí)行開(kāi)始時(shí)申請(qǐng)，但是語(yǔ)句結(jié)束后并不會(huì)馬上釋放，而會(huì)等到整個(gè)事務(wù)提交后再釋放。
注：一般行鎖都有鎖超時(shí)時(shí)間。但是MDL鎖沒(méi)有超時(shí)時(shí)間的限制，只要事務(wù)沒(méi)有提交就會(huì)一直鎖注。

2.2.1 怎么解決了這個(gè)MDL鎖

上面不是說(shuō)了嗎，提交或者回滾這個(gè)事務(wù)。所以要找到這個(gè)事務(wù)

怎么找到這個(gè)事務(wù)，通過(guò)information_schema.innodb_trx查看事務(wù)的執(zhí)行時(shí)間

# 查看事務(wù)超過(guò)60s的事務(wù)
mysql> select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60\G;
trx_started 表示什么時(shí)候執(zhí)行的這個(gè)事務(wù)
 
#查看系統(tǒng)當(dāng)前時(shí)間
mysql> select now();

事務(wù)開(kāi)始時(shí)間和系統(tǒng)現(xiàn)在時(shí)間，一看事務(wù)執(zhí)行了這么久。

查看這個(gè)線程id

怎么處理了這個(gè)長(zhǎng)事務(wù)的線程id了

首先看show full processlist; 中host 哪個(gè)字段，到底是誰(shuí)連接了數(shù)據(jù)庫(kù)。例：我上面是localhost環(huán)境，進(jìn)去commit或者/rollback ,哪如果不是localhost 環(huán)境了，是程序連接了這時(shí)候就要kill掉了

2.2.2 我身上發(fā)生的趣事

上次有個(gè)DBA 問(wèn)我，造成很大主從延遲，說(shuō)要怎么解決。

我說(shuō)你怎么解決的延遲，知道主從延遲造成的具體的原因嗎？

ta給我說(shuō)，開(kāi)啟了多線程，但是延遲還是很大，基本沒(méi)怎么用到多線程。

我說(shuō)你怎么知道主從延遲，需要開(kāi)啟多線程復(fù)制來(lái)解決， ta給我說(shuō)，網(wǎng)上別人blog這莫說(shuō)的，我一口老血吐出來(lái)。

后來(lái)又問(wèn)ta, 主從延遲正常情況下，做了什么操作， ta給我說(shuō)修改了alter 表結(jié)構(gòu)。

然后讓ta 看是不是mdl 鎖造成的，讓tashow full processlist，一看果然是mdl 鎖原因。

然后跟ta 說(shuō) 找長(zhǎng)事務(wù)，找到以后跟開(kāi)發(fā)商議，這個(gè)長(zhǎng)事務(wù)在做什么操作可不可以kill掉。

注：這是我真實(shí)遇到，別人問(wèn)我這樣問(wèn)題。首先你要知道造成這個(gè)結(jié)果，你事先做了什么操作，然后解決這個(gè)問(wèn)題，最根本你還是要知道原因，然后下次避免。

還有網(wǎng)上的環(huán)境，系統(tǒng)版本，應(yīng)用版本，遇到問(wèn)題的情況，跟你是不是一樣，有時(shí)候不要盲目相信。

2.3 如何安全地給小表加字段？

首先我們要解決長(zhǎng)事務(wù)，事務(wù)不提交，就會(huì)一直占著 MDL 鎖。在 MySQL 的information_schema 庫(kù)的 innodb_trx 表中，你可以查到當(dāng)前執(zhí)行中的事務(wù)。如果你要做 DDL 變更的表剛好有長(zhǎng)事務(wù)在執(zhí)行，要考慮先暫停 DDL，或者 kill 掉這個(gè)長(zhǎng)事務(wù)。這也是為什么需要在低峰期做ddl 變更，當(dāng)然也要考慮具體做什么ddl，參考官方的online ddl。

2.4 online ddl 過(guò)程