前言
在真實的企業開發環境中使用MySQL，MySQL肯定不會只有我一個人使用，而是一個團隊顯式的使用MySQL，或者是業務隱式的使用MySQL，那么多個用戶或者客戶端連接使用的時候，我們應該考慮一個問題：如果保證資料并發訪問的一致性呢？這一篇我就來聊聊MySQL的鎖，不涉及MySQL的事務隔離級別，
全域鎖
MySQL的全域鎖會關閉所有打開的表，并使全部的表處于只讀狀態，它們的命令為：
# 全域鎖，簡稱FTWRL
FLUSH TABLES WITH READ LOCK;
# 解鎖命令
UNLOCK TABLES;
對FTWRL進行實驗：（以下的所有實驗都是在MySQL8.0.22完成的）
| session1 | session2 |
|---|---|
| FLUSH TABLES WITH READ LOCK; | |
| select * from test limit 1; (正常回傳結果) | |
| select * from test limit 1; (正常回傳結果) | |
| 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立馬執行成功) |
從以上實驗可以得出：當執行FTWRL后，所有的表變成了只讀狀態，其他更新的操作將會被阻塞，
全域鎖的主要作用就是做全庫邏輯備份，也就是把資料庫的每個表都select出來存成文本，
當備份程序中，整個資料庫處于只讀狀態，風險也是及其的大，如果是在主庫備份，將會導致所有的業務表都不能修改資料；如果是在從庫備份，這個時候從庫不能執行主庫傳過來的binlog，會導致主從延遲，
好在InnoDB存盤引擎支持事務，mysqldump有一個引數single-transaction，可以在事務中創建一致性快照，然后進行所有表備份，在有這個引數下，備份期間可以進行資料修改，所以正常開發中建議使用InnoDB存盤引擎，
表級鎖
表級鎖分為兩種，一種是表鎖，另一種是元資料鎖，
表鎖
表鎖分為表讀鎖和表寫鎖，在MySQL的命令是：
# 表讀鎖
lock tables test read;
# 表寫鎖
lock tables test write;
接下來通過實驗看下表讀鎖和表寫鎖有什么區別吧
表讀鎖
| session1 | session2 |
|---|---|
| lock tables test read; | |
| select * from test limit1; (正常回傳結果) | |
| select * from test limit 1; (正常回傳結果) | |
| 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都可以正常的讀資料，但是session1寫資料會報錯，session2寫資料會被阻塞，等到session1解鎖了，session2的寫資料才能執行成功，
從這個實驗可以得出：表加上了表讀鎖之后，本執行緒和其他執行緒都可以讀資料，本執行緒寫資料會報錯，其他執行緒寫資料會阻塞，
表寫鎖
| session1 | session2 |
|---|---|
| lock tables test write; | |
| select * from test limi1; (正常回傳結果) | |
| select * from test limit 1; # sql1 (阻塞) | |
| unlock tables; | |
| select * from test limit; # sql1 (session1解鎖后，sql1立馬回傳結果) | |
| 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立馬執行成功) |
從以上實驗可以得出：表加上了表寫鎖之后，本執行緒可以進行讀寫操作，其他執行緒的讀寫操作都會被阻塞，
元資料鎖（Metadata Locking，簡稱：MDL鎖）
在MySQL中，資料庫的DDL不屬于事務范疇，如果你在session1中select一行資料，這個時候session2給這張表新增了一列xxx，這個時候可能會出現事務特性被破壞、binlog順序錯亂等bug（MySQL官網上有公布出類似的bug，感興趣可以自行去了解），
為了解決以上的問題，從MySQL5.5.3引入了元資料鎖，MDL鎖不需要顯式使用，MySQL會默認加上，它的作用就是保證資料庫讀寫正確性，以下全部用MDL表示元資料鎖，
當你對一張表進行增刪查改的時候會默認加上MDL讀鎖；當你對一張表進行表結構更改的時候會默認加上MDL寫鎖，
| session1 | session2 | session3 | session4 |
|---|---|---|---|
| begin; | |||
| select * from test lmi1; (正常回傳結果) | |||
| select * from test limit 1; (正常回傳結果) | |||
| alter table test add d int; (阻塞) | |||
| select * from test limit 1; (阻塞) |
一開始session1會話查詢test的時候，獲取到了MDL讀鎖，可以正常查詢到資料，然后session2會話查詢資料也會獲取MDL讀鎖，不沖突，也可以正常查詢到資料回傳，
但是到了session3會話的時候，需要獲取MDL寫鎖，這個時候因為session1的MDL讀鎖沒有釋放，所以會阻塞，后面session4也需要MDL讀鎖，但是因為session3被阻塞了，所以session4也會被阻塞，
假如這是一張線上業務表，這種場景將會使后面的任何操作都失效，表現出來就是這張表變得無法寫和讀，如果客戶端配置了MySQL重試機制的話，會在超時的時候重新建立一個session會話重新請求，然后MySQL就會因為執行緒不停新增而崩潰，
從上面的例子可以知道MDL鎖是在陳述句執行的時候默認加上的，但是陳述句執行完是不會釋放的，只有等整個事務提交了才會釋放MDL鎖，
所以對于我們開發者來說，在作業中應該盡量避免滿查詢、盡量保證事務及時提交、避免大事務等，對于DBA來說，也應該盡量避免在業務高峰期執行DDL操作，
總結
- 全域鎖會讓所有的表變成只讀狀態，所有更新操作都會被阻塞
- 表讀鎖是本執行緒和其他執行緒都可以讀，本執行緒寫會報錯，其他執行緒寫會阻塞
- 表寫鎖是本執行緒可以讀寫，其他執行緒讀寫都會阻塞
- 引入MDL鎖解決事務和DDL同時執行引發的bug
參考資料
- 《深入淺出MySQL》第二版：20.3.8 什么時候使用表鎖
- 《MySQL實戰45講》林曉斌
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/264884.html
標籤：其他