| 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í)鎖分為兩種，一種是表鎖，另一種是元數(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ì)被阻塞。
在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操作。
到此這篇關(guān)于MySQL的全局鎖和表級(jí)鎖的具體使用的文章就介紹到這了,更多相關(guān)MySQL 全局鎖和表級(jí)鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！
標(biāo)簽：阿里 山西 溫州 揚(yáng)州 福州 定西 三明 無錫
巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《MySQL的全局鎖和表級(jí)鎖的具體使用》，本文關(guān)鍵詞 MySQL,的,全局,鎖,和,表級(jí),；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。
