全域性鎖和表鎖

資料庫鎖設計的初衷是處理併發問題。作為多使用者共享的資源，當出現併發訪問的時候，資料庫需要合理地控制資源的訪問規則。而鎖就是用來實現這些訪問規則的重要資料結構。

根據加鎖的範圍，MySQL 裡面的鎖大致可以分成全域性鎖、表級鎖和行鎖三類。

全域性鎖

全域性鎖就是對整個資料庫例項加鎖。MySQL 提供了一個加全域性讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處於只讀狀態的時候，可以使用這個命令，之後其他執行緒的以下語句會被阻塞：資料更新語句（資料的增刪改）、資料定義語句（包括建表、修改表結構等）和更新類事務的提交語句。

全域性鎖的典型使用場景是：做全庫邏輯備份。也就是把整庫每個表都 select 出來存成文字。

以前有一種做法，是通過 FTWRL 確保不會有其他執行緒對資料庫做更新，然後對整個庫做備份。注意，在備份過程中整個庫完全處於只讀狀態。

但是讓整庫都只讀，聽上去就很危險：

• 如果你在主庫上備份，那麼在備份期間都不能執行更新；
• 如果你在從庫上備份，那麼備份期間從庫不能執行主庫同步過來的 binlog，會導致主從延遲。

官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用引數–single-transaction 的時候，導資料之前就會啟動一個事務，來確保拿到一致性檢視。而由於 MVCC 的支援，這個過程中資料是可以正常更新的。single-transaction方法只適用於所有的表使用事務引擎的庫。

既然要全庫只讀，為什麼不使用 set global readonly=true 的方式呢？主要有下面兩個原因

• 在有些系統中，readonly 的值會被用來做其他邏輯，比如用來判斷一個庫是主庫還是備庫。因此，修改 global 變數的方式影響面更大，我不建議你使用。
• 在異常處理機制上有差異。如果執行 FTWRL 命令之後由於客戶端發生異常斷開，那麼 MySQL 會自動釋放這個全域性鎖，整個庫回到可以正常更新的狀態。而將整個庫設定為 readonly 之後，如果客戶端發生異常，則資料庫就會一直保持 readonly 狀態，這樣會導致整個庫長時間處於不可寫狀態，風險較高。

表級鎖

MySQL 裡面表級別的鎖有兩種：一種是表鎖，一種是後設資料鎖（meta data lock，MDL)。

**表鎖的語法是 lock tables … read/write。**與 FTWRL 類似，可以用 unlock tables 主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要注意，lock tables 語法除了會限制別的執行緒的讀寫外，也限定了本執行緒接下來的操作物件。

**另一類表級的鎖是 MDL（metadata lock)。**MDL 不需要顯式使用，在訪問一個表的時候會被自動加上。MDL 的作用是，保證讀寫的正確性。你可以想象一下，如果一個查詢正在遍歷一個表中的資料，而執行期間另一個執行緒對這個表結構做變更，刪了一列，那麼查詢執行緒拿到的結果跟表結構對不上，肯定是不行的。

因此，在 MySQL 5.5 版本中引入了 MDL，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。

• 讀鎖之間不互斥，因此你可以有多個執行緒同時對一張表增刪改查。
• 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結構操作的安全性。因此，如果有兩個執行緒要同時給一個表加欄位，其中一個要等另一個執行完才能開始執行。

如何安全地給表加欄位

首先我們要解決長事務，事務不提交，就會一直佔著 MDL 鎖。在 MySQL 的 information_schema 庫的 innodb_trx 表中，你可以查到當前執行中的事務。如果你要做 DDL 變更的表剛好有長事務在執行，要考慮先暫停 DDL，或者 kill 掉這個長事務。

如果你要變更的表是一個熱點表，雖然資料量不大，但是上面的請求很頻繁，而你不得不加個欄位，你該怎麼做呢？這時候 kill 可能未必管用，因為新的請求馬上就來了。比較理想的機制是，在 alter table 語句裡面設定等待時間，如果在這個指定的等待時間裡面能夠拿到 MDL 寫鎖最好，拿不到也不要阻塞後面的業務語句，先放棄。之後開發人員或者 DBA 再通過重試命令重複這個過程。