MySQL中怎么實現(xiàn)全局鎖和表鎖-創(chuàng)新互聯(lián)

今天就跟大家聊聊有關(guān)MySQL中怎么實現(xiàn)全局鎖和表鎖，可能很多人都不太了解，為了讓大家更加了解，小編給大家總結(jié)了以下內(nèi)容，希望大家根據(jù)這篇文章可以有所收獲。

創(chuàng)新互聯(lián)"三網(wǎng)合一"的企業(yè)建站思路。企業(yè)可建設(shè)擁有電腦版、微信版、手機版的企業(yè)網(wǎng)站。實現(xiàn)跨屏營銷，產(chǎn)品發(fā)布一步更新，電腦網(wǎng)絡+移動網(wǎng)絡一網(wǎng)打盡，滿足企業(yè)的營銷需求！創(chuàng)新互聯(lián)具備承接各種類型的網(wǎng)站制作、做網(wǎng)站項目的能力。經(jīng)過十余年的努力的開拓，為不同行業(yè)的企事業(yè)單位提供了優(yōu)質(zhì)的服務，并獲得了客戶的一致好評。

1. 全局鎖

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

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

1.1 全局鎖使用場景

全局鎖的典型使用場景是，做全庫邏輯備份（mysqldump）。重新做主從時候

也就是把整庫每個表都 select 出來存成文本。

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

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

如果你在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務基本上就能停止。如果你在從庫上備份，那么備份期間從庫不能執(zhí)行主庫同步過來的binlog，會導致主從延遲。

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

看來加全局鎖不太好。但是細想一下，備份為什么要加鎖呢？來看一下不加鎖會有什么問題？

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

比如手機卡，購買套餐信息

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

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

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

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

也就是說，不加鎖的話，備份系統(tǒng)備份的得到的庫不是一個邏輯時間點，這個數(shù)據(jù)是邏輯不一致的。

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

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

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

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

1.4 全局鎖兩種方法

一.FLUSH TABLES WRITE READ LOCK

二.set global readonly=true

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

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

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

三是，readonly 對super用戶權(quán)限無效

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

即使沒有被全局鎖住，加字段也不是就能一帆風順的，還有表級鎖了

2. 表級鎖

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

2.1 表鎖

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

lock tables 表名 read;# 既不能讀，也不能寫

表鎖的語法是 lock tables … read/write。與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象。

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

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

2.2 MDL 鎖

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

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

• 讀鎖之間不互斥，因此你可以有多個線程同時對一張表增刪改查。

• 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個線程要同時給一個表加字段，其中一個要等另一個執(zhí)行完才能開始執(zhí)行。

雖然 MDL 鎖是系統(tǒng)默認會加的，但卻是你不能忽略的一個機制。

比如下面這個例子，我經(jīng)常看到有人掉到這個坑里：給一個小表加個字段，導致整個庫掛了。

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

show full processlist查看mdl 鎖詳情

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

之后 session C 會被 blocked，是因為 session A 的 MDL 讀鎖還沒有釋放，而 sessionC 需要MDL 寫鎖，因此只能被阻塞。

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

如果某個表上的查詢語句頻繁，而且客戶端有重試機制，也就是說超時后會再起一個新session 再請求的話，這個庫的線程很快就會爆滿。

事務中的 MDL 鎖，在語句執(zhí)行開始時申請，但是語句結(jié)束后并不會馬上釋放，而會等到整個事務提交后再釋放。
注 ： 一般行鎖都有鎖超時時間。但是MDL鎖沒有超時時間的限制，只要事務沒有提交就會一直鎖注。

2.2.1 怎么解決了這個MDL鎖

上面不是說了嗎，提交或者回滾這個事務。所以要找到這個事務

怎么找到這個事務， 通過information_schema.innodb_trx查看事務的執(zhí)行時間

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

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

查看這個線程id

怎么處理了這個長事務的線程id了

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

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

上次有個DBA 問我， 造成很大主從延遲，說要怎么解決。

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

ta給我說，開啟了多線程， 但是延遲還是很大，基本沒怎么用到多線程。

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

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

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

然后跟ta 說 找長事務， 找到以后跟開發(fā)商議，這個長事務在做什么操作可不可以kill掉。

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

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

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

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

2.4 online ddl 過程

• 拿MDL寫鎖

• 降級成MDL讀鎖

• 真正做DDL

• 升級成MDL寫鎖

• 釋放MDL鎖

1、2、4、5如果沒有鎖沖突，執(zhí)行時間非常短。第3步占用了DDL絕大部分時間，這期間這個個表可以正常讀寫數(shù)據(jù)，是因此稱為”online”

看完上述內(nèi)容，你們對MySQL中怎么實現(xiàn)全局鎖和表鎖有進一步的了解嗎？如果還想了解更多知識或者相關(guān)內(nèi)容，請關(guān)注創(chuàng)新互聯(lián)成都網(wǎng)站設(shè)計公司行業(yè)資訊頻道，感謝大家的支持。

另外有需要云服務器可以了解下創(chuàng)新互聯(lián)scvps.cn，海內(nèi)外云服務器15元起步，三天無理由+7*72小時售后在線，公司持有idc許可證，提供“云服務器、裸金屬服務器、高防服務器、香港服務器、美國服務器、虛擬主機、免備案服務器”等云主機租用服務以及企業(yè)上云的綜合解決方案，具有“安全穩(wěn)定、簡單易用、服務可用性高、性價比高”等特點與優(yōu)勢，專為企業(yè)上云打造定制，能夠滿足用戶豐富、多元化的應用場景需求。

文章標題：MySQL中怎么實現(xiàn)全局鎖和表鎖-創(chuàng)新互聯(lián)
文章鏈接：http://bm7419.com/article16/ihgdg.html

成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供服務器托管、網(wǎng)站制作、Google、營銷型網(wǎng)站建設(shè)、ChatGPT、品牌網(wǎng)站制作

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)