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ì)被阻塞。
全局鎖的主要作用就是做全庫(kù)邏輯備份,也就是把數(shù)據(jù)庫(kù)的每個(gè)表都select出來(lái)存成文本。
當(dāng)備份過(guò)程中,整個(gè)數(shù)據(jù)庫(kù)處于只讀狀態(tài),風(fēng)險(xiǎn)也是及其的大。如果是在主庫(kù)備份,將會(huì)導(dǎo)致所有的業(yè)務(wù)表都不能修改數(shù)據(jù);如果是在從庫(kù)備份,這個(gè)時(shí)候從庫(kù)不能執(zhí)行主庫(kù)傳過(guò)來(lái)的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í)鎖分為兩種,一種是表鎖,另一種是元數(shù)據(jù)鎖。
表鎖分為表讀鎖和表寫鎖,在MySQL的命令是:
# 表讀鎖 lock tables test read; # 表寫鎖 lock tables test write;
接下來(lái)通過(guò)實(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ì)被阻塞。
在MySQL中,數(shù)據(jù)庫(kù)的DDL不屬于事務(wù)范疇,如果你在session1中select一行數(shù)據(jù),這個(gè)時(shí)候session2給這張表新增了一列xxx,這個(gè)時(shí)候可能會(huì)出現(xiàn)事務(wù)特性被破壞、binlog順序錯(cuò)亂等bug(MySQL官網(wǎng)上有公布出類似的bug,感興趣可以自行去了解)。
為了解決以上的問(wèn)題,從MySQL5.5.3引入了元數(shù)據(jù)鎖,MDL鎖不需要顯式使用,MySQL會(huì)默認(rèn)加上,它的作用就是保證數(shù)據(jù)庫(kù)讀寫正確性。以下全部用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)出來(lái)就是這張表變得無(wú)法寫和讀。如果客戶端配置了MySQL重試機(jī)制的話,會(huì)在超時(shí)的時(shí)候重新建立一個(gè)session會(huì)話重新請(qǐng)求,然后MySQL就會(huì)因?yàn)榫€程不停新增而崩潰。
從上面的例子可以知道MDL鎖是在語(yǔ)句執(zhí)行的時(shí)候默認(rèn)加上的,但是語(yǔ)句執(zhí)行完是不會(huì)釋放的,只有等整個(gè)事務(wù)提交了才會(huì)釋放MDL鎖。
所以對(duì)于我們開發(fā)者來(lái)說(shuō),在工作中應(yīng)該盡量避免慢查詢、盡量保證事務(wù)及時(shí)提交、避免大事務(wù)等,對(duì)于DBA來(lái)說(shuō),也應(yīng)該盡量避免在業(yè)務(wù)高峰期執(zhí)行DDL操作。
到此這篇關(guān)于MySQL的全局鎖和表級(jí)鎖的具體使用的文章就介紹到這了,更多相關(guān)MySQL 全局鎖和表級(jí)鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
標(biāo)簽:阿里 山西 溫州 揚(yáng)州 福州 定西 三明 無(wú)錫
巨人網(wǎng)絡(luò)通訊聲明:本文標(biāo)題《MySQL的全局鎖和表級(jí)鎖的具體使用》,本文關(guān)鍵詞 MySQL,的,全局,鎖,和,表級(jí),;如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問(wèn)題,煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們,我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò),涉及言論、版權(quán)與本站無(wú)關(guān)。