快捷導(dǎo)航

MySQL的全局鎖和表級(jí)鎖的具體使用

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

在真實(shí)的企業(yè)開發(fā)環(huán)境中使用MySQL，我們應(yīng)該考慮一個(gè)問題：如果保證數(shù)據(jù)并發(fā)訪問的一致性呢？這一篇我就來聊聊MySQL的鎖，感興趣的可以了解一下

前言

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

全局鎖

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

# 全局鎖，簡(jiǎn)稱FTWRL
FLUSH TABLES WITH READ LOCK;

# 解鎖命令
UNLOCK TABLES;

對(duì)FTWRL進(jìn)行實(shí)驗(yàn)：（以下的所有實(shí)驗(yà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); (報(bào)錯(cuò))
	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í)行成功)

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

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

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

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

表級(jí)鎖

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

表鎖

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

# 表讀鎖
lock tables test read;

# 表寫鎖
lock tables test write;

接下來通過實(shí)驗(yàn)看下表讀鎖和表寫鎖有什么區(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); (報(bào)錯(cuò))
	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會(huì)話加上了表讀鎖，這個(gè)時(shí)候session1和session2都可以正常的讀數(shù)據(jù)，但是session1寫數(shù)據(jù)會(huì)報(bào)錯(cuò)，session2寫數(shù)據(jù)會(huì)被阻塞，等到session1解鎖了，session2的寫數(shù)據(jù)才能執(zhí)行成功。

從這個(gè)實(shí)驗(yàn)可以得出：表加上了表讀鎖之后，本線程和其他線程都可以讀數(shù)據(jù)，本線程寫數(shù)據(jù)會(huì)報(bào)錯(cuò)，其他線程寫數(shù)據(jù)會(huì)阻塞。

表寫鎖

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í)行成功)

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

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

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

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

當(dāng)你對(duì)一張表進(jìn)行增刪查改的時(shí)候會(huì)默認(rèn)加上MDL讀鎖；當(dāng)你對(duì)一張表進(jìn)行表結(jié)構(gòu)更改的時(shí)候會(huì)默認(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會(huì)話查詢test的時(shí)候，獲取到了MDL讀鎖，可以正常查詢到數(shù)據(jù)。然后session2會(huì)話查詢數(shù)據(jù)也會(huì)獲取MDL讀鎖，不沖突，也可以正常查詢到數(shù)據(jù)返回。

但是到了session3會(huì)話的時(shí)候，需要獲取MDL寫鎖，這個(gè)時(shí)候因?yàn)閟ession1的MDL讀鎖沒有釋放，所以會(huì)阻塞。后面session4也需要MDL讀鎖，但是因?yàn)閟ession3被阻塞了，所以session4也會(huì)被阻塞。

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

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

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