資料庫鎖設計的初衷是為了處理併發問題。作為多使用者共享的資源，當出現併發訪問的時候，資料庫需要合理地控制資源訪問的規則，鎖就是用來實現這些訪問規則的重要資料結構。
在MySQL裡鎖分為三類，全域性鎖，表級鎖，行鎖。
全域性鎖
全域性鎖就是對整個資料庫例項加鎖，MySQL提供了一個加全域性讀鎖的方法，命令是 Flush tables with read lock (FTWRL)，使用了全域性鎖，會讓整個庫處於只讀狀態。
我們只要一聽到整個庫加鎖，就感覺很危險。為什麼這樣講呢？
1、如果在主庫上進行備份的話，備份期間更新操作是無法執行的，業務基本上就處於停擺狀態；
2、如果在從庫上進行備份，備份期間從庫不能執行主庫同步過來的binlog，會導致主從延遲。
這時你可能會想，既然這麼不好，為什麼還要使用全域性鎖呢？
全域性鎖的典型使用場景是用來做全庫備份使用。設想一下，不加全域性鎖會有什麼問題？
比如：使用者買東西，首先會從餘額里扣除金額，然後在訂單裡新增商品。如果備份資料庫，不加鎖，並且備份順序為先備份用餘額，再備份訂單商品，有可能備份了使用者餘額後，使用者下訂單買東西提交事務，然後再備份訂單商品表， 此時訂單商品已存在。最後備份出來的資料為。最後使用者餘額為買東西前的餘額，沒有減少，但是訂單商品卻多了。
也就是說，如果不加鎖，不同表之間執行順序不同，進而導致備份時間不同，此時備份系統可能會得到不是一個時間的點的資料，檢視是不一致的。
看到這裡，是不是會想到一個機制，我們讓檢視一致不就好了嗎？是的，說的沒錯。保證檢視一致的事務隔離級別是什麼？可重複讀。但是不要忘記了，不是所有的引擎都是支援事物的，所以也就是說，不支援事務的引擎沒有辦法使用可重複讀隔離級別，來保證一致性讀。
官方自帶的邏輯備份工具是 MySQLdump， 當mysqldump 使用引數** –single-transaction 時候，會啟用一個事務，來確保拿到一致性檢視。**
思考
問
既然是全庫只讀，為什麼不使用set global readonly=true的方式？
1、在有些系統中，readonly是被用作其他邏輯使用的, 比如判斷一個庫是否為主庫還是備庫, 修改 global 變數方式影響太大。
2、異常處理機制上有差異.如果FTWRL命令執行之後客戶端發生異常斷開, MySQL會自動釋放這個全域性鎖, 整個庫是可以正常更新的狀態。但是如果設定了readonly，即使發生異常，資料庫會一直保持只讀狀態，長時間處於不可寫的狀態，風險極大
表級鎖
MySQL中，表級鎖有兩種，一種是表鎖，一種是後設資料鎖。
表鎖
表鎖的語法是 lock tables 表名 read、write，可以使用unlock tables 進行主動釋放鎖，也可以在客戶端斷開時自動釋放。
lock tables 語法除了會限制別的執行緒讀寫外，也會限制本執行緒的操作物件。
例子：
在某個執行緒A 中執行 lock tables t1 read， t2 write；這個語句，其他執行緒寫t1、讀寫t2的語句都會被阻塞。同時，執行緒A在執行unlock tables 之前，也只能執行讀 t1， 讀寫t2的操作。寫t1都是不允許的。可以理解為寫是排它鎖，寫鎖意味著其他執行緒不能讀也不能寫。讀鎖是共享鎖，加上後其他鎖只能讀，不能寫，本執行緒也不能寫。
2
後設資料鎖(metadata lock, MDL)
MDL 不需要顯式使用, 在訪問一個表時會自動加上。MDL的作用是，保證讀寫的正確性。
如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。
MDL 是server層的表級鎖，也是表結構鎖，主要是用於隔離 DML和DDL操作之間的干擾。對一個表進行正刪改查操作的時候，加MDL讀鎖；對一個表做表結構變更的時候，加MDL寫鎖。（MDL加鎖過程是系統自動控制，無法直接干預，讀讀共享，讀寫互斥，寫寫互斥）
讀鎖之間不互斥，可以有多個執行緒同時對一張表進行增刪改查
讀寫鎖之間，寫鎖之間是互斥的，用來保證變更表結構操作的安全性。如果有兩個執行緒同時給一個表加欄位，其中一個要等另一個完成後才能執行
思考
問
這裡我會產生一個疑問，為什麼讀鎖之間不互斥，其他執行緒還可以進行增刪改查？
因為在對一個表進行增刪改查的時候，系統會自動加上一個MDL讀鎖，這個讀鎖是表結構的讀鎖，增刪改查並不會改變表結構，讀鎖自然會不會進行互斥，多個執行緒可以同時進行增刪改查操作；這個時候如果加了讀鎖，其它執行緒中有需要改變表結構的，這時改表結構的執行緒會加上一個MDL寫鎖，現在讀鎖和寫鎖就會進行互斥，所以讀鎖加了之後，寫鎖是需要等待的，同理，加了寫鎖，讀鎖也需要等待的，其他的寫鎖也是需要等待的。問
為什麼我給一個小表加個欄位，導致整個庫掛掉了？
我們知道，給一個表加欄位，修改索引，新增索引，都會進行全表掃描。舉個例子：
（事務 示意圖）
session A 啟動，這時會對錶 t 加上一個 MDL 讀鎖。
session B 進行查詢，這時也會對錶 t 加上一個 MDL 讀鎖，讀鎖與讀鎖之間並不互斥，因此可以正常執行。
session C 進行表結構修改，此時會對錶** t 加上一個 MDL 寫鎖**，這時session A 的 MDL 讀鎖還未釋放，寫鎖和讀鎖同時存在，造成互斥，目前只有等待 讀鎖釋放，所以需要等待。
這時session D 進行查詢，申請MDL讀鎖，這時候也會被阻塞，處於等待狀態。
所有對錶操作增刪改查都需要申請MDL讀鎖，就都被鎖住，此時的表完全不可讀寫了。
這裡為什麼C等待拿鎖之後，D也會被阻塞呢？如果按照併發理解的話，C,D應該是同一等級，都有可能拿到鎖，但C讀寫鎖互斥，D為讀讀鎖應該是共享的呀，並不互斥啊？
** 因為MDL鎖在申請時會形成一個佇列，佇列中 寫鎖獲取優先順序高於讀鎖。一但寫鎖出現等待，不但當前事務會造成阻塞，同時還會阻塞後續該表的所有操作。**
** 事務一旦申請到MDL鎖後，一直等到事務結束後才會進行釋放鎖。**
如何安全的給小表加欄位
事務一旦申請到MDL鎖後，在語句執行開始申請，語句結束並不會釋放鎖，**一直等到事務結束後才會進行釋放鎖。**
首先要解決長事務，事務不提交，就會一直站著MDL鎖。如果要做DDL變更的表剛好有長事務在執行，要考慮暫停DDL，或者kill掉這個長事務。
思考
問
要變更的表是熱點表，資料不大，但請求頻繁，現在不得不加欄位，該怎麼辦？
這時候kill掉長事務未必可用了，因為請求很頻繁。在 alter table 語句裡面設定等待時間，如果在這個指定等待時間拿到 MDL 寫鎖最好， 拿不到就先放棄，之後等DBA重試命令重複這個過程。** MariaDB10.3 增補AliSQL補丁-DDL FAST FAIL，讓其DDL操作快速失敗。**
ALTER TABLE tbl_name NOWAIT add column ...
ALTER TABLE tbl_name WAIT N add column ...本作品採用《CC 協議》，轉載必須註明作者和本文連結