MySQL的全局鎖和表級鎖的具體使用
在真實(shí)的企業(yè)開發(fā)環(huán)境中使用MySQL，MySQL肯定不會只有我一個(gè)人使用，而是一個(gè)團(tuán)隊(duì)顯式的使用MySQL，或者是業(yè)務(wù)隱式的使用MySQL，那么多個(gè)用戶或者客戶端連接使用的時(shí)候，我們應(yīng)該考慮一個(gè)問題：如果保證數(shù)據(jù)并發(fā)訪問的一致性呢？這一篇我就來聊聊MySQL的鎖，不涉及MySQL的事務(wù)隔離級別。
全局鎖
MySQL的全局鎖會關(guān)閉所有打開的表，并使全部的表處于只讀狀態(tài)，它們的命令為：
# 全局鎖，簡稱FTWRL FLUSH TABLES WITH READ LOCK; # 解鎖命令 UNLOCK TABLES;
對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)，其他更新的操作將會被阻塞。
全局鎖的主要作用就是做全庫邏輯備份，也就是把數(shù)據(jù)庫的每個(gè)表都select出來存成文本。
當(dāng)備份過程中，整個(gè)數(shù)據(jù)庫處于只讀狀態(tài)，風(fēng)險(xiǎn)也是及其的大。如果是在主庫備份，將會導(dǎo)致所有的業(yè)務(wù)表都不能修改數(shù)據(jù)；如果是在從庫備份，這個(gè)時(shí)候從庫不能執(zhí)行主庫傳過來的binlog，會導(dǎo)致主從延遲。
好在InnoDB存儲引擎支持事務(wù)，mysqldump有一個(gè)參數(shù)single-transaction，可以在事務(wù)中創(chuàng)建一致性快照，然后進(jìn)行所有表備份。在有這個(gè)參數(shù)下，備份期間可以進(jìn)行數(shù)據(jù)修改，所以正常開發(fā)中建議使用InnoDB存儲引擎。
表級鎖
表級鎖分為兩種，一種是表鎖，另一種是元數(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會話加上了表讀鎖，這個(gè)時(shí)候session1和session2都可以正常的讀數(shù)據(jù)，但是session1寫數(shù)據(jù)會報(bào)錯(cuò)，session2寫數(shù)據(jù)會被阻塞，等到session1解鎖了，session2的寫數(shù)據(jù)才能執(zhí)行成功。
從這個(gè)實(shí)驗(yàn)可以得出：表加上了表讀鎖之后，本線程和其他線程都可以讀數(shù)據(jù)，本線程寫數(shù)據(jù)會報(bào)錯(cuò)，其他線程寫數(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í)行成功) |
從以上實(shí)驗(yàn)可以得出：表加上了表寫鎖之后，本線程可以進(jìn)行讀寫操作，其他線程的讀寫操作都會被阻塞。
元數(shù)據(jù)鎖（Metadata Locking，簡稱：MDL鎖）
在MySQL中，數(shù)據(jù)庫的DDL不屬于事務(wù)范疇，如果你在session1中select一行數(shù)據(jù)，這個(gè)時(shí)候session2給這張表新增了一列xxx，這個(gè)時(shí)候可能會出現(xiàn)事務(wù)特性被破壞、binlog順序錯(cuò)亂等bug（MySQL官網(wǎng)上有公布出類似的bug，感興趣可以自行去了解）。
為了解決以上的問題，從MySQL5.5.3引入了元數(shù)據(jù)鎖，MDL鎖不需要顯式使用，MySQL會默認(rèn)加上，它的作用就是保證數(shù)據(jù)庫讀寫正確性。以下全部用MDL表示元數(shù)據(jù)鎖。
當(dāng)你對一張表進(jìn)行增刪查改的時(shí)候會默認(rèn)加上MDL讀鎖；當(dāng)你對一張表進(jìn)行表結(jié)構(gòu)更改的時(shí)候會默認(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的時(shí)候，獲取到了MDL讀鎖，可以正常查詢到數(shù)據(jù)。然后session2會話查詢數(shù)據(jù)也會獲取MDL讀鎖，不沖突，也可以正常查詢到數(shù)據(jù)返回。
但是到了session3會話的時(shí)候，需要獲取MDL寫鎖，這個(gè)時(shí)候因?yàn)閟ession1的MDL讀鎖沒有釋放，所以會阻塞。后面session4也需要MDL讀鎖，但是因?yàn)閟ession3被阻塞了，所以session4也會被阻塞。
假如這是一張線上業(yè)務(wù)表，這種場景將會使后面的任何操作都失效，表現(xiàn)出來就是這張表變得無法寫和讀。如果客戶端配置了MySQL重試機(jī)制的話，會在超時(shí)的時(shí)候重新建立一個(gè)session會話重新請求，然后MySQL就會因?yàn)榫€程不停新增而崩潰。
從上面的例子可以知道MDL鎖是在語句執(zhí)行的時(shí)候默認(rèn)加上的，但是語句執(zhí)行完是不會釋放的，只有等整個(gè)事務(wù)提交了才會釋放MDL鎖。
所以對于我們開發(fā)者來說，在工作中應(yīng)該盡量避免慢查詢、盡量保證事務(wù)及時(shí)提交、避免大事務(wù)等，對于DBA來說，也應(yīng)該盡量避免在業(yè)務(wù)高峰期執(zhí)行DDL操作。
總結(jié)
- 全局鎖會讓所有的表變成只讀狀態(tài)，所有更新操作都會被阻塞
- 表讀鎖是本線程和其他線程都可以讀，本線程寫會報(bào)錯(cuò)，其他線程寫會阻塞
- 表寫鎖是本線程可以讀寫，其他線程讀寫都會阻塞
- 引入MDL鎖解決事務(wù)和DDL同時(shí)執(zhí)行引發(fā)的bug
參考資料
- 《深入淺出MySQL》第二版：20.3.8 什么時(shí)候使用表鎖
- 《MySQL實(shí)戰(zhàn)45講》林曉斌
到此這篇關(guān)于MySQL的全局鎖和表級鎖的具體使用的文章就介紹到這了,更多相關(guān)MySQL 全局鎖和表級鎖內(nèi)容請搜索本站以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持本站！
版權(quán)聲明：本站文章來源標(biāo)注為YINGSOO的內(nèi)容版權(quán)均為本站所有，歡迎引用、轉(zhuǎn)載，請保持原文完整并注明來源及原文鏈接。禁止復(fù)制或仿造本網(wǎng)站，禁止在非www.sddonglingsh.com所屬的服務(wù)器上建立鏡像，否則將依法追究法律責(zé)任。本站部分內(nèi)容來源于網(wǎng)友推薦、互聯(lián)網(wǎng)收集整理而來，僅供學(xué)習(xí)參考，不代表本站立場，如有內(nèi)容涉嫌侵權(quán)，請聯(lián)系alex-e#qq.com處理。
關(guān)注官方微信