MySQL鎖機制
1.什麼是鎖
鎖是計算機協調多個程式或執行緒併發訪問某一資源的機制。在資料庫中，除傳統的計算資源（如CPU、RAM、I/O等）的爭用以外，資料也是一種供許多使用者共享的資源。如何保證資料併發訪問的一致性、有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問效能的一個重要因素。從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜.
MySQL鎖概述：
相對其他資料庫而言，MySQL 的鎖機制
比較簡單，其最顯著的特點是不同的儲存引擎支援不同的鎖機制。比如，MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；BDB儲存引擎採用的是頁面鎖（page-levellocking），但也支援表級鎖；InnoDB儲存引擎既支援行級鎖（row-levellocking），也支援表級鎖，但預設情況下是採用行級鎖。
MySQL這3種鎖的特性可大致歸納如下。
- 表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高,併發度最低。
- 行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低,併發度也最高。
- 頁面鎖：開銷和加鎖時間界於表鎖和行鎖之間；會出現死鎖；鎖定粒度界於表鎖和行鎖之間，併發度一般。
3種鎖的使用角度：
- 表級鎖更適合於以查詢為主，只有少量按索引條件更新資料的應用，如Web應用；
- 行級鎖則更適合於有大量按索引條件併發更新少量不同資料，同時又有併發查詢的應用，如一些線上事務處理（OLTP）系統。
- BDB的頁面鎖已經被InnoDB取代，不做討論。
2.lock與latch
在資料中，lock與latch可以被成為“鎖”。但是兩者有著截然不同得含義，這裡主要關注的是lock。
latch一般稱為閂鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的效能會非常差。在InnoDB儲存引擎種，latch又可以分為mutex(互斥量)和rwlock(讀寫鎖)。其目的是用來保證併發執行緒操作臨界資源的正確性，並且通常沒有死鎖檢測的機制。
lock的物件是事務，用來鎖定的是資料庫中的物件，如表、頁、行。並且一般lock的物件僅在事務commit或rollback後進行釋放（不同事物隔離級別釋放的時間可能不同）。另外，lock跟大多數資料庫中一樣，是有死鎖機制的。下表顯示了lock與latch的不同。
| LOCK | latch | |
|---|---|---|
| 物件 | 事務 | 執行緒 |
| 保護 | 資料庫內容 | 記憶體資料結構 |
| 持續時間 | 整個事務過程 | 臨界資源 |
| 模式 | 行鎖、表鎖、意向鎖 | 讀寫鎖、互斥量 |
| 死鎖 | 通過waits-for graph、time out等機制 進行死鎖檢測與處理 |
無死鎖檢測與處理機制。僅通過應用程式 加鎖的順序保證無死鎖的情況發生 |
| 存在於 | Lock Manager的雜湊表中 | 每個資料結構的物件中 |
3.InnoDB儲存引擎中的鎖
3.1鎖的型別
InnoDB儲存引擎實現瞭如下兩種標準的行級鎖：
- 共享鎖（S Lock），允許事務讀一行資料。
- 排他鎖（X Lock），允許事務刪除或更新一行資料。
如果一個事務T1已經獲得了行r的共享鎖，那麼另外的事務T2可以立即獲得行r的共享鎖，因為讀取沒有改變行r的資料，稱這種情況為鎖相容(Lock Compatible)。但若有其他的事務T3想獲得行r的排他鎖，則其必須等待事務T1、T2釋放行r的共享鎖——這種情況稱為鎖不相容。
| X | S | |
|---|---|---|
| X | 不相容 | 不相容 |
| S | 不相容 | 相容 |
此外，InnoDB儲存引擎支援多粒度鎖定，這種鎖定允許事務在行級上鎖和表鎖上的鎖同時存在。為了支援在不同粒度上進行加鎖操作，InnoDB儲存引擎支援一種額外的鎖方式，稱之為意向鎖。意向鎖是將鎖定的物件分為多個層次，意向鎖意味著事務希望在更細粒度上進行加鎖。
InnoDB儲存引擎支援意向鎖設計比較簡練，其意向鎖即為表級別的鎖。設計目的主要是為了在一個事務中揭示下一行將被請求的鎖型別。其支援兩種意向鎖：
- 意向共享鎖（IS Lock），事務想要獲得一張表中某幾行的共享鎖
- 意向排他鎖（IX Lock），事務想要獲得一張表中某幾行的排他鎖
3.2 一致性非鎖定讀
一致性的非鎖定讀（consistant nonlocking read）是指InnoDB儲存引擎通過多版本控制（multi versioning）的方法來讀取當前執行時間資料庫中行的資料。如果讀取的行正在執行Delete或Update操作，這時讀取操作不會因此去等待行上鎖的釋放。相反地，InnoDB儲存引擎會去讀取行的一個快照版本。如下如所示。
上圖直觀地展現了InnoDB儲存引擎一致性的非鎖定讀。之所以稱為非鎖定讀，因為不需要等待訪問的行上X鎖的釋放。快照資料是指該行的之前版本的資料，該實現是通過undo段來完成。而undo用來在事務中回滾資料，因此快照資料本身是沒有額外的開銷。此外，讀取快照資料是不需要上鎖的，因為沒有事務需要對歷史的資料進行修改操作。
通過上圖可以知道，快照資料其實就是當前行資料之前的歷史版本，每行記錄可能有多個版本，一般稱這種技術為行多版本技術。由此帶來的併發控制，稱之為多版本併發控制（Multi Version Concurrency Control, MVCC）。
在事務隔離級別READ COMMITTED和REPEATABLE READ下，InnoDB儲存引擎使用非鎖定的一致性讀。然而，對於快照資料的定義卻不相同。在READ COMMITTED事務隔離級別下，對於快照資料，非一致性讀總是讀取被鎖定行的最新一份快照資料。而在REPEATABLE READ事務隔離級別下，對於快照資料，非一致性讀總是讀取事務開始時的行資料版本。如下表所示示例：
| 時間 | 會話A | 會話B |
|---|---|---|
| 1 | begin | |
| 2 | select * from t_user where id = 1; | |
| 3 | begin | |
| 4 | update t_user set id = 10 where id = 1; | |
| 5 | select * from t_user where id = 1; | |
| 6 | commit; | |
| 7 | select * from t_user where id = 1; | |
| 8 | commit; |
假設原本id = 1的記錄是存在的，大家可以按上表時間順序執行對應的會話，比較及驗證2者的不同。
3.3 一致性鎖定讀
在預設配置下，在事務的隔離級別為REPEATABLE READ模式下，InnoDB儲存引擎的select操作使用一致性非鎖定讀。但是在某些情況下，使用者需要顯示地對資料庫讀取操作進行加鎖以保證資料邏輯的一致性。而這要求資料庫支援加鎖語句，即使時對於select的只讀操作。InnoDB儲存引擎對於select語句支援兩種一致性的鎖定讀（locking read）操作：
- select ··· for update
- select ··· lock in share mode
select ··· for update對讀取的行記錄加一個X鎖，其他事務不能對已鎖定的行加上任何鎖。select ··· lock in share mode對讀取的行記錄加一個S鎖，其他事務可以向被鎖定的行加S鎖，但是如果加X鎖，則會被阻塞。
對於一致性非鎖定讀，即使讀取的行已被執行了select ··· for update，也是可以進行讀取的。此外，select ··· for update或者select ··· lock in share mode必須在一個事務中，當事務提交了，鎖也就釋放了。因此在使用上述兩種select鎖定語句時，務必加上begin,start transaction或者set autocommit=0。
4 鎖的演算法
4.1行鎖的3中演算法
InnoDB儲存引擎有3種行鎖的演算法，其分別是：
- Record Lock：單個行記錄上的鎖
- Gap Lock：間隙鎖，鎖定一個範圍，但不包含記錄本身
- Next-Key Lock：Gap Lock + Record Lock，鎖定一個範圍，並且鎖定記錄本身
Record Lock總是會去鎖住主鍵索引記錄，如果InnoDB儲存引擎表在建立的時候沒有設定任何一個主鍵或唯一非空索引，那麼這時InnoDB儲存引擎會使用隱式的主鍵來進行鎖定。
Next-Key Lock是結合了Gap Lock+Record Lock的一種鎖定演算法，在Next-Key Lock演算法下，InnoDB對於行的查詢都是採用這種鎖定演算法。假如一個索引有10，11，13和20這4個值，那麼該索引可能被Next-Key Locking的區間為：
（-無窮，10] ，（10，11]， （11，13]， （13，20]， （20，+無窮)
__採用Next-Key Lock的鎖定技術稱為Next-Key Locking。其設計的目的是為了解決幻讀問題。而利用這種鎖定技術，鎖定的不是單個值，而是一個範圍。
然而，當查詢的索引含有唯一屬性時，InnoDB儲存引擎會對Next-Key Lock進行優化將其降級為Record Lock，即僅鎖住索引本身，而不是範圍。__下面演示一個例子。
mysql> create table t (a int primary key);
Query OK, 0 rows affected (0.01 sec)
mysql> insert into t select 1;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 2;
Query OK, 1 row affected (0.00 sec)
Records: 1 Duplicates: 0 Warnings: 0
mysql> insert into t select 5;
Query OK, 1 row affected (0.01 sec)
Records: 1 Duplicates: 0 Warnings: 0
接著按下表時間順序執行操作。
| 時間 | 會話A | 會話B |
|---|---|---|
| １ | begin; | |
| ２ | select * from t where a = 5 for update; |
|
| ３ | begin; | |
| ４ | insert into t select 4; | |
| ５ | commit; #成功，不需要等待 |
|
| ６ | commit; |
表t共有1，2，5三個值。在上面的例子中，在會話A中首先對a=5進行X鎖定。而由於a是主鍵且唯一，因此鎖定的僅是5這個值，而不是(2,5)這個範圍，這樣在會話B中插入值4而不會阻塞，可以立即插入並返回。即鎖定由Next-Key Lock演算法降級為了Record Lock，從而提高應用的併發性。
如上，Next-Key Lock降級為Record Lock僅在查詢的列是唯一索引的情況下。若是輔助索引，則情況會完全不同。同樣，首先建立測試表z進行測試：
mysql> create table z (a int ,b int ,primary key(a), key(b));
mysql> insert into z select 1,1;
mysql> insert into z select 3,1;
mysql> insert into z select 5,3;
mysql> insert into z select 7,6;
mysql> insert into z select 10,8;
表z的列b是輔助索引，若在會話A中執行下面的SQL語句：
mysql> select * from z where b = 3 for update;
很明顯，這時SQL語句通過索引列b進行查詢，因此其使用傳統的Next-Key Locking技術加鎖，並且由於有兩個索引，其需要分別進行鎖定。對於聚集索引，其僅對列a等於5的索引加上Record Lock。而對於輔助索引，其加上的是Next-Key Lock,鎖定的範圍是（1，3），特別需要注意的是，InnoDB儲存引擎還會對輔助索引下一個鍵值加上gap lock，即還有一個輔助索引範圍為（3，6）的鎖。因此，若在新會話B中執行下面的SQL語句，都會被阻塞：
mysql> select * from z where a = 5 lock in share mode;
mysql> insert into z select 4,2;
mysql> insert into z select 6,5;
第一個SQL語句不能執行，因為在會話A中執行的SQL語句已經對聚集索引中列a=5的值加上X鎖,因此執行會被阻塞。第二個SQL語句，主鍵插入4，沒有問題，但是插入的輔助索引值2在鎖定的範圍（1,3）中，因此執行同樣會被阻塞。第三個SQL語句，插入的主鍵6沒有被鎖定，5也不在範圍（1,3）之間。但插入的值5在另一個鎖定的範圍（3,6）中，故同樣需要等待。而下面的SQL語句，不會被阻塞，可以立即執行：
mysql> insert into z select 8,6;
mysql> insert into z select 2,0;
mysql> insert into z select 6,7;
從上面的例子可以看到，Gap Lock的作用是為了阻止多個事務將記錄插入到同一個範圍內，而這會導致幻讀問題的產生。假如在上面的例子中，會話A中使用者已經鎖定了b=3的記錄。若此時沒有Gap Lock鎖定（3,6），那麼使用者可以插入索引b列為3的記錄，這會導致會話A中的使用者再次執行同樣查詢時會返回不同的記錄，即幻讀。
4.2 解決幻讀問題
InnoDB儲存引擎預設的事務隔離級別是repeatable read，在該隔離級別下，其採用Next-Key Locking的方式來加鎖。而在事務隔離級別read committed下，其僅採用Record Lock。
5 死鎖
5.1 死鎖的概念
__死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待的現象。__若無外力作用，事務都將無法推進下去。
解決死鎖問題最簡單的一種方法是超時，即當兩個事務互相等待時，當一個等待時間超過設定的某一閾值時，其中一個事務進行回滾，另一個等待的事務就能繼續執行。在InnoDB儲存引擎紅，引數innodb_lock_wait_timeout用來設定超時的時間。
5.2 死鎖的示例
如果程式是序列的，那麼不可能發生死鎖。死鎖只存在於併發的情況，而資料庫本身就是一個併發執行的程式，因此可能會發生死鎖。下表的操作演示了死鎖的一種經典情況：
| 時間 | 會話A | 會話B |
|---|---|---|
| 1 | begin; | |
| 2 | mysql> select * from t where a = 1 for update; a:1 1 row in set (0.00 sec) |
begin; |
| 3 | mysql> select * from t where a = 2 for update; a:2 1 row in set (0.00 sec) |
|
| 4 | mysql> select * from t where a = 2 for update; #等待 |
|
| 5 | mysql> select * from t where a = 1 for update; ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction |
InnoDB儲存引擎並不會回滾大部分的錯誤異常，但是異常除外。發現死鎖後，InnoDB儲存引擎會馬上回滾一個事務，這點需要注意。
5.3 如何避免死鎖
- 如果不同程式會併發存取多個表，儘量約定以相同的順序訪問表，可以大大降低死鎖機會。
- 在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；
- 對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率；