快捷導(dǎo)航

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

更新時間：2021年08月20日 12:54:01 作者：god-jiang

在真實的企業(yè)開發(fā)環(huán)境中使用MySQL，我們應(yīng)該考慮一個問題：如果保證數(shù)據(jù)并發(fā)訪問的一致性呢？這一篇我就來聊聊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; (阻塞)