三分鐘入門 InnoDB 存儲引擎中的表鎖和行鎖
各位對 ”鎖“ 這個概念應該都不是很陌生吧，Java 語言中就提供了兩種鎖：內置的 synchronized 鎖和 Lock 接口，使用鎖的目的就是管理對共享資源的并發訪問，保證數據的完整性和一致性，數據庫中的鎖也不例外。
“鎖" 是數據庫系統區別于文件系統的一個關鍵特性，其對象是事務，用來鎖定的是數據庫中的對象，如表、頁、行等。需要注意的是，每種數據庫對于鎖的實現都是不同的，并且對于 MySQL 來說，每種存儲引擎都可以實現自己的鎖策略和鎖粒度，比如 InnoDB 引擎支持行鎖和表鎖，而 MyISAM 引擎只支持表鎖。
本文所講的鎖針對的是我們最常用的 InnoDB 存儲引擎。
表鎖與行鎖
所謂 “表鎖 (Table Lock)”，就是會鎖定整張表，它是 MySQL 中最基本的鎖策略，并不依賴于存儲引擎，就是說不管你是 MySQL 的什么存儲引擎，對于表鎖的策略都是一樣的，并且表鎖是開銷最小的策略(因為粒度比較大)。
由于表級鎖一次會將整個表鎖定，所以可以很好的避免死鎖問題。當然，鎖的粒度大所帶來最大的負面影響就是出現鎖資源爭用的概率也會最高，導致并發率大打折扣。
而所謂 “行鎖(Row Lock)”，也稱為記錄鎖，顧名思義，就是鎖住某一行(某條記錄 row)。需要的注意的是，MySQL 服務器層并沒有實現行鎖機制，行級鎖只在存儲引擎層實現 !!!
讀鎖和寫鎖
首先說明一點，對于 InnoDB 引擎來說，讀鎖和寫鎖可以加在表上，也可以加在行上。
對于并發讀和并發寫的問題，可以通過實現一個由兩種類型的鎖組成的鎖系統來解決。這兩種類型的鎖通常被稱為 共享鎖(Shared Lock，S Lock) 和 排他鎖(Exclusive Lock，X Lock)，也叫 讀鎖(readlock) 和 寫鎖(write lock)：
- 共享鎖 / 讀鎖：允許事務讀(select)數據
- 排他鎖 / 寫鎖：允許事務刪除(delete)或更新(update)數據
讀鎖是共享的，或者說是相互不阻塞的。多個事務在同一時刻可以同時讀取同一個資源，而互不干擾。寫鎖是排他的，也就是說一個寫鎖會阻塞其他的讀鎖和寫鎖，這樣就能確保在給定的時間里，只有一個事務能執行寫入，并防止其他用戶讀取正在寫入的同一資源。
用行級讀寫鎖來舉個例子吧：如果一個事務 T1 已經獲得了某個行 r 的讀鎖，那么此時另外的一個事務 T2 是可以去獲得這個行 r 的讀鎖的，因為讀取操作并沒有改變行 r 的數據;但是，如果某個事務 T3 想獲得行 r 的寫鎖，則它其必須等待事務 T1、T2 釋放掉行 r 上的讀鎖才行。
兼容關系如下表(兼容是指對同一張表或記錄的鎖的兼容性情況)：
| X 鎖 | S 鎖 | |
|---|---|---|
| X 鎖 | 不兼容 | 不兼容 |
| S 鎖 | 不兼容 | 兼容 |
從上表可以看出，只有共享鎖和共享鎖是兼容的，而排他鎖和誰都是不兼容的。
意向鎖
InnoDB 存儲引擎支持 多粒度(granular)鎖定，就是說允許事務在行級上的鎖和表級上的鎖同時存在。
那么為了實現行鎖和表鎖并存，InnoDB 存儲引擎就設計出了 意向鎖(Intention Lock) 這個東西：
Intention locks are table-level locks that indicate which type of lock (shared or exclusive) a transaction requires later for a row in a table.
很好理解：意向鎖是一個表級鎖，其作用就是指明接下來的事務將會用到哪種鎖。
有兩種意向鎖：
- 意向共享鎖(IS Lock)：當事務想要獲得一張表中某幾行的共享鎖行級鎖)時，InnoDB 存儲引擎會自動地先獲取該表的意向共享鎖(表級鎖)
- 意向排他鎖(IX Lock)：當事務想要獲得一張表中某幾行的排他鎖(行級鎖)時，InnoDB 存儲引擎會自動地先獲取該表的意向排他鎖(表級鎖)
各位其實可以直接把 ”意向“ 翻譯成 ”想要“，想要共享鎖、想要排他鎖，你就會發現原來就這東西啊(滑稽)。
意向鎖之間是相互兼容的：
| IS 鎖 | IX 鎖 | |
|---|---|---|
| IS 鎖 | 兼容 | 兼容 |
| IX 鎖 | 兼容 | 兼容 |
但是與表級讀寫鎖之間大部分都是不兼容的：
| X 鎖 | S 鎖 | |
|---|---|---|
| IS 鎖 | 不兼容 | 兼容 |
| IX 鎖 | 不兼容 | 不兼容 |
注意，這里強調一點：上表中的讀寫鎖指的是表級鎖，意向鎖不會與行級的讀寫鎖互斥!!!
來理解一下為什么說意向鎖不會與行級的讀寫鎖互斥。舉個例子，事務 T1、事務 T2、事務 T3 分別想對某張表中的記錄行 r1、r2、r3 進行修改，很普通的并發場景對吧，這三個事務之間并不會發生干擾，所以是可以正常執行的。
這三個事務都會先對這張表加意向寫鎖，因為意向鎖之間是兼容的嘛，所以這一步沒有任何問題。那如果意向鎖和行級讀寫鎖互斥的話，豈不是這三個事務都沒法再執行下去了，對吧。
OK，看到這里，我們來思考兩個問題：
1)為什么沒有意向鎖的話，表鎖和行鎖不能共存?
2)意向鎖是如何讓表鎖和行鎖共存的?
首先來看第一個問題，假設行鎖和表鎖能共存，舉個例子：事務 T1 鎖住表中的某一行(行級寫鎖)，事務 T2 鎖住整個表(表級寫鎖)。
問題很明顯，既然事務 T1 鎖住了某一行，那么其他事務就不可能修改這一行。這與 ”事務 T2 鎖住整個表就能修改表中的任意一行“ 形成了沖突。所以，沒有意向鎖的時候，行鎖與表鎖是無法共存的。
再來看第二個問題，有了意向鎖之后，事務 T1 在申請行級寫鎖之前，MySQL 會先自動給事務 T1 申請這張表的意向排他鎖，當表上有意向排他鎖時其他事務申請表級寫鎖會被阻塞，也即事務 T2 申請這張表的寫鎖就會失敗。
如何加鎖
在說加鎖之前，我們有必要了解下解鎖機制。對于 InnoDB 來說，隨時都可以加鎖，但是并非隨時都可以解鎖。具體來說，InnoDB 采用的是兩階段鎖定協議(two-phase locking protocol)：即在事務執行過程中，隨時都可以執行加鎖操作，但是只有在事務執行 COMMIT 或者 ROLLBACK 的時候才會釋放鎖，并且所有的鎖是在同一時刻被釋放。
說完了解鎖機制，再來講講加鎖機制。
先來看如何加意向鎖，它比較特殊，是由 InnoDB 存儲引擎自己維護的，用戶無法手動操作意向鎖，在為數據行加讀寫鎖之前，InooDB 會先獲取該數據行所在在數據表的對應意向鎖。
再來看如何加表級鎖：
1)隱式鎖定：對于常見的 DDL 語句(如 ALTER、CREATE 等)，InnoDB 會自動給相應的表加表級鎖
2)顯示鎖定：在執行 SQL 語句時，也可以明確顯示指定對某個表進行加鎖(lock table user read(write))
- lock table user read; # 加表級讀鎖
- unlock tables; # 釋放表級鎖
如何加行級鎖：
1)對于常見的 DML 語句(如 UPDATE、DELETE 和 INSERT )，InnoDB 會自動給相應的記錄行加寫鎖
2)默認情況下對于普通 SELECT 語句，InnoDB 不會加任何鎖，但是在 Serializable 隔離級別下會加行級讀鎖
上面兩種是隱式鎖定，InnoDB 也支持通過特定的語句進行顯式鎖定，不過這些語句并不屬于 SQL 規范：
3)SELECT * FROM table_name WHERE ... FOR UPDATE，加行級寫鎖
4)SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE，加行級讀鎖
另外，需要注意的是，InnoDB 存儲引擎的行級鎖是基于索引的(這個下篇文章會詳細解釋)，也就是說當索引失效或者說根本沒有用索引的時候，行鎖就會升級成表鎖。
舉個例子(這里就以比較典型的索引失效情況 “使用 or" 來舉例)，有數據庫如下，id 是主鍵索引：
- CREATE TABLE `test` (
- `id` int(11) NOT NULL AUTO_INCREMENT,
- `username` varchar(255) DEFAULT NULL,
- PRIMARY KEY (`id`)
- ) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;
新建兩個事務，先執行事務 T1 的前兩行，也就是不要執行 rollback 也不要 commit：
這個時候事務 T1 沒有釋放鎖，并且由于索引失效事務 T1 其實是鎖住了整張表，此時再來執行事務 2，你會發現事務 T2 會卡住，最后超時關閉事務：
