玄幻小说完本,将夜猫腻小说

主頁 > 知識庫 > MySQL的全局鎖和表級鎖的具體使用

MySQL的全局鎖和表級鎖的具體使用

前言

在真實的企業(yè)開發(fā)環(huán)境中使用MySQL，MySQL肯定不會只有我一個人使用，而是一個團(tuán)隊顯式的使用MySQL，或者是業(yè)務(wù)隱式的使用MySQL，那么多個用戶或者客戶端連接使用的時候，我們應(yīng)該考慮一個問題：如果保證數(shù)據(jù)并發(fā)訪問的一致性呢？這一篇我就來聊聊MySQL的鎖，不涉及MySQL的事務(wù)隔離級別。

全局鎖

MySQL的全局鎖會關(guān)閉所有打開的表，并使全部的表處于只讀狀態(tài)，它們的命令為：

# 全局鎖，簡稱FTWRL
FLUSH TABLES WITH READ LOCK;

# 解鎖命令
UNLOCK TABLES;

對FTWRL進(jìn)行實驗：（以下的所有實驗都是在MySQL8.0.22完成的）

session1	session2
FLUSH TABLES WITH READ LOCK;
select * from test limit 1; (正常返回結(jié)果)
	select * from test limit 1; (正常返回結(jié)果)
insert into test(a,b,c) values(6,6,6); (報錯)
	insert into test(a,b,c) values(8,8,8);# sql1 (阻塞)
UNLOCK TABLES;
	insert into test(a,b,c) values(8,8,8);# sql1 (session1解鎖后，sql1立馬執(zhí)行成功)

從以上實驗可以得出：當(dāng)執(zhí)行FTWRL后，所有的表變成了只讀狀態(tài)，其他更新的操作將會被阻塞。

全局鎖的主要作用就是做全庫邏輯備份，也就是把數(shù)據(jù)庫的每個表都select出來存成文本。

當(dāng)備份過程中，整個數(shù)據(jù)庫處于只讀狀態(tài)，風(fēng)險也是及其的大。如果是在主庫備份，將會導(dǎo)致所有的業(yè)務(wù)表都不能修改數(shù)據(jù)；如果是在從庫備份，這個時候從庫不能執(zhí)行主庫傳過來的binlog，會導(dǎo)致主從延遲。

好在InnoDB存儲引擎支持事務(wù)，mysqldump有一個參數(shù)single-transaction，可以在事務(wù)中創(chuàng)建一致性快照，然后進(jìn)行所有表備份。在有這個參數(shù)下，備份期間可以進(jìn)行數(shù)據(jù)修改，所以正常開發(fā)中建議使用InnoDB存儲引擎。

表級鎖

表級鎖分為兩種，一種是表鎖，另一種是元數(shù)據(jù)鎖。

表鎖

表鎖分為表讀鎖和表寫鎖，在MySQL的命令是：

# 表讀鎖
lock tables test read;

# 表寫鎖
lock tables test write;

接下來通過實驗看下表讀鎖和表寫鎖有什么區(qū)別吧

表讀鎖

session1	session2
lock tables test read;
select * from test limit1; (正常返回結(jié)果)
	select * from test limit 1; (正常返回結(jié)果)
insert into test(a,b,c) values(6,6,6); (報錯)
	insert into test(a,b,c) values(8,8,8); # sql1 (阻塞)
unlock tables;
	insert into test(a,b,c) values(8,8,8); # sql1 (session1解鎖后，sql1立馬寫入成功)

在session1會話加上了表讀鎖，這個時候session1和session2都可以正常的讀數(shù)據(jù)，但是session1寫數(shù)據(jù)會報錯，session2寫數(shù)據(jù)會被阻塞，等到session1解鎖了，session2的寫數(shù)據(jù)才能執(zhí)行成功。

從這個實驗可以得出：表加上了表讀鎖之后，本線程和其他線程都可以讀數(shù)據(jù)，本線程寫數(shù)據(jù)會報錯，其他線程寫數(shù)據(jù)會阻塞。

表寫鎖

session1	session2
lock tables test write;
select * from test limi1; (正常返回結(jié)果)
	select * from test limit 1; # sql1 (阻塞)
unlock tables;
	select * from test limit; # sql1 (session1解鎖后，sql1立馬返回結(jié)果)
lock tables test write;
insert into test(a,b,c) values(6,6,6); (插入成功)
	insert into test(a,b,c) values(8,8,8);# sql 2 (阻塞)
unlock tables;
	insert into test(a,b,c) values(8,8,8);# sql2 (session1解鎖后，sql2立馬執(zhí)行成功)

從以上實驗可以得出：表加上了表寫鎖之后，本線程可以進(jìn)行讀寫操作，其他線程的讀寫操作都會被阻塞。

元數(shù)據(jù)鎖（Metadata Locking，簡稱：MDL鎖）

在MySQL中，數(shù)據(jù)庫的DDL不屬于事務(wù)范疇，如果你在session1中select一行數(shù)據(jù)，這個時候session2給這張表新增了一列xxx，這個時候可能會出現(xiàn)事務(wù)特性被破壞、binlog順序錯亂等bug（MySQL官網(wǎng)上有公布出類似的bug，感興趣可以自行去了解）。

為了解決以上的問題，從MySQL5.5.3引入了元數(shù)據(jù)鎖，MDL鎖不需要顯式使用，MySQL會默認(rèn)加上，它的作用就是保證數(shù)據(jù)庫讀寫正確性。以下全部用MDL表示元數(shù)據(jù)鎖。

當(dāng)你對一張表進(jìn)行增刪查改的時候會默認(rèn)加上MDL讀鎖；當(dāng)你對一張表進(jìn)行表結(jié)構(gòu)更改的時候會默認(rèn)加上MDL寫鎖。

session1	session2	session3	session4
begin;
select * from test lmi1; (正常返回結(jié)果)
	select * from test limit 1; (正常返回結(jié)果)
		alter table test add d int; (阻塞)
			select * from test limit 1; (阻塞)

一開始session1會話查詢test的時候，獲取到了MDL讀鎖，可以正常查詢到數(shù)據(jù)。然后session2會話查詢數(shù)據(jù)也會獲取MDL讀鎖，不沖突，也可以正常查詢到數(shù)據(jù)返回。

但是到了session3會話的時候，需要獲取MDL寫鎖，這個時候因為session1的MDL讀鎖沒有釋放，所以會阻塞。后面session4也需要MDL讀鎖，但是因為session3被阻塞了，所以session4也會被阻塞。

假如這是一張線上業(yè)務(wù)表，這種場景將會使后面的任何操作都失效，表現(xiàn)出來就是這張表變得無法寫和讀。如果客戶端配置了MySQL重試機(jī)制的話，會在超時的時候重新建立一個session會話重新請求，然后MySQL就會因為線程不停新增而崩潰。

從上面的例子可以知道MDL鎖是在語句執(zhí)行的時候默認(rèn)加上的，但是語句執(zhí)行完是不會釋放的，只有等整個事務(wù)提交了才會釋放MDL鎖。

所以對于我們開發(fā)者來說，在工作中應(yīng)該盡量避免慢查詢、盡量保證事務(wù)及時提交、避免大事務(wù)等，對于DBA來說，也應(yīng)該盡量避免在業(yè)務(wù)高峰期執(zhí)行DDL操作。

總結(jié)

全局鎖會讓所有的表變成只讀狀態(tài)，所有更新操作都會被阻塞
表讀鎖是本線程和其他線程都可以讀，本線程寫會報錯，其他線程寫會阻塞
表寫鎖是本線程可以讀寫，其他線程讀寫都會阻塞
引入MDL鎖解決事務(wù)和DDL同時執(zhí)行引發(fā)的bug

參考資料

《深入淺出MySQL》第二版：20.3.8 什么時候使用表鎖
《MySQL實戰(zhàn)45講》林曉斌

到此這篇關(guān)于MySQL的全局鎖和表級鎖的具體使用的文章就介紹到這了,更多相關(guān)MySQL 全局鎖和表級鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

您可能感興趣的文章:

MySQL 到底是如何做到多版本并發(fā)的？
mysql過濾復(fù)制思路詳解
MySQL 外鍵(FOREIGN KEY)用法案例詳解
MySQL如何利用存儲過程快速生成100萬條數(shù)據(jù)詳解
Python接口自動化淺析pymysql數(shù)據(jù)庫操作流程
MySQL事務(wù)控制流與ACID特性
Mysql使用存儲過程快速添加百萬數(shù)據(jù)的示例代碼
MySQL去除重疊時間求時間差和的實現(xiàn)
Mysql數(shù)據(jù)庫中datetime、bigint、timestamp來表示時間選擇,誰來存儲時間效率最高
基于Redo Log和Undo Log的MySQL崩潰恢復(fù)解析

標(biāo)簽：阿里山西溫州揚州福州定西三明無錫

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL的全局鎖和表級鎖的具體使用》，本文關(guān)鍵詞 MySQL,的,全局,鎖,和,表級,；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請?zhí)峁┫嚓P(guān)信息告之我們，我們將及時溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。