MySQL 筆記整理（6） --全局鎖和表鎖：給表加個欄位怎麼有這麼多阻礙

筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》 6） --全局鎖和表鎖：給表加個欄位怎麼有這麼多阻礙資料庫鎖設計的初衷是處理併發問題。作為多用戶共用的資源，當出現併發訪問的時候，資料庫需要合理地控制資源的訪問規則。而鎖就是用來實現這些訪問規則的重要結構。根據加鎖的範圍，MySQL裡面的鎖大 ...

筆記記錄自林曉斌（丁奇）老師的《MySQL實戰45講》

6） --全局鎖和表鎖：給表加個欄位怎麼有這麼多阻礙

　　資料庫鎖設計的初衷是處理併發問題。作為多用戶共用的資源，當出現併發訪問的時候，資料庫需要合理地控制資源的訪問規則。而鎖就是用來實現這些訪問規則的重要結構。根據加鎖的範圍，MySQL裡面的鎖大致可以分為全局鎖，表級鎖和行鎖三類。這篇筆記主要包含全局鎖和表級鎖。行鎖的內容會在之後再進行分享。

全局鎖：

　　全局鎖就是對整個資料庫實例加鎖。可以通過命令 Flush tables with read lock (FTWRL)對全局進行加鎖。使用全局鎖後，其他線程的以下語句會被阻塞：數據更新語句（增刪改），數據定義語句（包括建表，修改表結構等）和更新類事務的提交語句。

　　全局鎖的典型使用場景是，做全庫邏輯備份。但如果你直接用上面的FTWRL去進行備份，會讓整庫都處於只讀狀態，這光想想就覺得危險。如果你在主庫上備份，那麼在備份期間都不能執行更新，業務基本停擺；如果你在從庫上備份，那麼備份期間從庫不能執行從主庫同步過來的binlog，會導致主從延遲。那麼不加鎖備份資料庫可以嗎？答案是不可以的，比如你有兩張表，餘額表和商品表。如果不加鎖，在備份期間時先備份餘額表，再備份商品表會出現什麼情況呢？假設在這兩張表備份的間隔時間內進行了數據更新，則餘額表數據在備份表中沒有減少，但是商品表在備份表中的數據卻減少了，造成了邏輯上的數據不一致問題。當然這兩張表的備份順序顛倒過來也是一樣會有這個問題。

　　官方自帶的邏輯備份工具是mysqldump.當mysqldump使用參數-single-transaction時，備份數據之前會啟動一個事務，來確保拿到一致性視圖。而由於MVCC（多版本併發控制）的支持，這個過程中數據是可以正常更新的。

　　至此，你可能會有個疑問，為什麼有了上述這個功能，還需要FTWRL呢？此處有個細節是可重覆讀是很好，但並不是所有的MySQL引擎支持這個隔離級別的。（如MyISAM這種不支持事務的引擎，就需要執行FTWRL命令了。）所以，single-transaction方法只適用於所有的表使用事務引擎的庫。

　　既然要保證備份時全庫只讀，為什麼不使用set global readonly = true的方式呢？不建議使用這種方式有以下兩方面原因：

在有些系統中，readonly的值會被用來處理其他邏輯。
在異常處理機制上的差異。FTWRL命令後如果客戶端發生異常斷開連接，那麼MySQL會自動釋放這個全局鎖。而readonly=true會保持資料庫長時間處於只讀狀態。

表級鎖：

　　MySQL裡面表級別的鎖有兩種：一種是表鎖，一種是元數據鎖（meta data lock，MDL）

　　表鎖的語法是 lock tables ... read/write。與FTWRL類似，可以用unlock tables主動釋放鎖，也可以在客戶端斷開的時候自動釋放。需要註意的是，lock tables語法除了會限制別的現成的讀寫外，也限定了本線程接下來的操作對象。在沒有更細粒度的鎖的時候，表鎖是最常用的處理併發的方式。而對於InnoDB這種支持行鎖的引擎，一般不使用lock tables命令來控制併發，畢竟鎖住整個表的影響面還是太大。

　　另一類表級鎖MDL不要顯示使用，在訪問一個表的時候會自動加上。它的作用是保證讀寫的正確性。如一個查詢正在遍歷一個表中的數據，而同時另一個線程對這個表結構做了變更，刪了一列，那麼查詢線程拿到的結果就會和表結構對不上，肯定是不行的。因此，在MySQL5.5引入了MDL，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對錶結構變更時，加MDL寫鎖。

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

　　另外，MDL會知道事務提交才釋放，在做表結構變更的時候，一定要小心不要導致鎖住線上查詢和更新。

　　表級鎖一般是在資料庫引擎不支持行鎖的時候才會被用到。如果你發現你的應用程式里有lock tables這樣的語句，你需要追查一下，比較可能的情況是：

你的系統還在使用MyISAM這類不支持事務的引擎，需要安排升級更換引擎了
你的引擎升級了，但是代碼還沒有升級。這樣把lock tables和unlock tables更換成begin和commit，問題就解決了。

上篇問題答案：

　　表結構如下所示：

CREATE TABLE `geek` (
  `a` int(11) NOT NULL,
  `b` int(11) NOT NULL,
  `c` int(11) NOT NULL,
  `d` int(11) NOT NULL,
  PRIMARY KEY (`a`,`b`),
  KEY `c` (`c`),
  KEY `ca` (`c`,`a`),
  KEY `cb` (`c`,`b`)
) ENGINE=InnoDB;

由於歷史原因， a和b需要做聯合主鍵。那麼既然主鍵包括了a,b這兩個欄位，又單獨在c上創建了一個索引，索引就已經包含了三個欄位了，為什麼還要創建ca，cb索引呢？有人給出的理由是業務里有這樣兩個查詢：

select * from geek where c=N order by a limit 1;
select * from geek where c=N order by b limit 1;

這個理由對嗎？為了這兩個查詢，這兩個索引是否都必須呢？為什麼呢？

　　我們以一組記錄來回答這個問題。

a	b	c	d
1	2	3	d
1	3	2	d
1	4	3	d
2	1	3	d
2	2	2	d
2	3	4	d

　　主鍵a,b的聚簇索引組織順序相當於order by a,b。也就是先按a排序，再按b排序，c無序。

　　索引ca的組織順序如下（先按c排序，再按a排序），同時記錄主鍵部分b的值

c	a	b
2	1	3
2	2	2
3	1	2
3	1	4
3	2	1
4	2	3

　　你可能會發現，ca的組織順序和索引c的順序是一模一樣的。

　　索引cb的組織順序如下（先按c排序，再按b排序，同時記錄主鍵a）

c	b	a
2	2	2
2	3	1
3	1	2
3	2	1
3	4	1
4	3	2

　　所以，結論是ca可以去掉，cb索引仍要保留。ca去掉的原因是組織順序與索引c相比完全相同。

問題：

　　備份一般都會在備庫上執行，你在用-single-transaction方法做邏輯備份的過程中，如果主庫上的一個小表做了一個DDL，比如給一個表上加了一列。這時候，從備庫上會看到什麼現象呢？