開發多用戶、資料庫驅動的應用時，最大的難點是：一方面要最大程度的利用資料庫的并發訪問，一方面還要確保每個用戶能以一致的方式讀取和修改資料，為此有了鎖的機制，

6.1 什么是鎖

鎖機制用于管理對共享資源的并發訪問，InnoDB除了會在行級別上對表資料上鎖，也會在資料庫內部其他多個地方上鎖，從而允許對多種不同資源提供并發訪問，例如，操作緩沖池中的LRU串列，洗掉、添加移動LRU串列上的元素等，

InnoDB 鎖的實作和 Oracle資料庫非常類似，提供一致性的非鎖定讀、行級鎖支持，行級鎖沒有相關的額外開銷，并可以得到并發性和一致性，

6.2 lock 與 latch（閂）

latch 一般稱為閂鎖（輕量級），因為其要求鎖定的時間非常短，若持續時間長，性能會很差，在 InnoDB中，latch 又可以分為 mutex 和rwlock （讀寫鎖），目的是用來保證并發執行緒操作臨界資源的正確性，并且通常沒有死鎖檢驗，

lock 一般鎖定的是資料庫中的物件，如表、頁、行，并且lock的物件僅在事務 commit 或 rollback后釋放，

6.3 InnoDB存盤引擎中的鎖

6.3.1 鎖的型別

InnoDB 實作了以下兩種標準的行級鎖：

1. 共享鎖，S Lock，允許事務讀一行資料
2. 排它鎖， X Lock，允許事務洗掉或更新一行資料

S和X 鎖都是行鎖，兼容是指對同一行記錄的兼容性情況，

InnoDB 支持多粒度鎖定，這種鎖定支持行級別的鎖和表級別的鎖同時存在，即意向鎖，意向鎖是將鎖定的物件分為多個層次，意向鎖意味著事務希望在更細粒度上進行加鎖，

如果需要對頁上的記錄r加X鎖，需要分別對資料庫A、表、頁上意向鎖IX，最后對r上X鎖，若其中任意一個部分導致等待，那么該操作需要等待粗粒度的鎖完成，InnoDB支持意向鎖比較簡單，其意向鎖就是表級別的鎖，主要是為了在一個事務中揭示下一行將被請求的鎖型別，

1. 意向共享鎖（IS Lock），事務想要獲得表中某幾行的共享鎖
2. 意向排它鎖（IX Lock），事務想要獲得表中某幾行的排它鎖

InnoDB支持的是行級別的鎖，因此意向鎖不會阻塞全表掃描以外的任何請求，

6.3.2 一致性非鎖定讀

一致性的非鎖定讀（consistent nonlocking read）是指 InnoDB 通過多版本并發控制（multi versioning Concurrency control）的方式來讀取當前執行時間資料庫中行的資料，如果此時，讀取的行正在執行 delete 或 update，這時讀取操作不會因此等待行上的鎖釋放，相反，InnoDB會去讀取行的一個快照資料（該實作是通過 undo 段來完成），而undo段是用來在事務中回滾資料，因此沒有額外開銷，此外，讀取快照資料是不需要上鎖的，因為沒有事務需要對歷史資料進行修改，

一致性的非鎖定讀，是InnoDB的默認讀取方式（Repeatable隔離級別），即讀取不會占用和等待表上的鎖，

一個行記錄不止一個快照資料，一般稱這種技術為行多版本并發控制技術，在事務隔離級別為 READ COMMITED 和 REPEATABLE READ下，InnoDB使用的是非鎖定的一致性讀，

在 READ COMMITED事務隔離級別下，對于快照資料總是讀取被鎖定行的最新一份的快照資料，從資料庫理論的角度看，違反了ACID中的隔離性I，而 REPEATABLE READ 讀取的是事務開始時的行資料版本，

在 READ COMMITED 隔離級別下，1，5 回傳id=1的資料，7回傳null，因為會話B提交后，id=1的資料被更新為3，此時的資料是最新的，而在REPEATABLE READ 下，1，5，7 總是回傳id=1 的資料，因為總是讀取事務開始前的資料，

6.3.3 一致性鎖定讀

某些情況下，用戶需要顯式的對資料庫讀取操作進行加鎖以保證資料的一致性，而這要求資料庫支持加鎖陳述句，即使是對于select 的查詢陳述句，InnoDB對于 select陳述句支持兩種一致性的鎖定讀操作：

1. select …… for update
2. select …… lock in share mode

select …… for update 對讀取的行加X鎖，其他事務不能再對該行加任何鎖，select …… share in mode 對讀取的行記錄加S鎖，其他事務可以向被鎖定的行加S鎖，但不能加X鎖，X鎖只能阻塞，

 select …… for update 和 select …… share in mode必須在事務中，事務提交，鎖釋放，

6.3.4 自增長與鎖

在 InnoDB的記憶體結構中，對每個含有自增長值的表都有一個自增長計數器，執行如下的陳述句得到計數器的值：

select MAX(auto_inc_col) from t for update;

　　插入操作會根據這個自增長的計數器值加1 賦予自增長列，這個實作方式被稱為 AUTO-INC Locking、這種鎖其實采用一種特殊的表鎖機制，為了提高插入性能，鎖不是在事務完成后釋放，而是完成對自增長值的插入的SQL陳述句后立即釋放，

　　雖然 AUTO-INC Locking在一定程度上提高了并發性，但對于有自增長值的列的并發插入性能較差，事務必須等待前一個插入的完成（雖然不需要等待事務的完成），其次，對于 Insert…… select 的大資料量的插入會影響插入的性能，因為另一個事務會被阻塞，

　　MySQL 5.1.22 版本開始，InnoDB提供了一種輕量級互斥量的自增長的實作機制，從該版本開始，InnoDB提供了一個引數 innodb_autoinc_lock_mode來控制自增長的模式，該引數默認為1.

另外，在 InnoDB中，自增長的列必須是索引，同時是索引的第一個列，否則MySQL會拋出例外，

6.3.5 外鍵和鎖

外鍵主要用于參考完整性的約束檢查，在InnoDB中，對于一個外鍵列，如果沒有顯示的對這個列添加索引，InnoDB會自動對其加索引，避免表鎖，

對于外鍵的插入或更新，需要先查詢父表，但是對于父表的查詢不是使用一致性非鎖定讀（MVCC），因為這樣會發生資料不一致的問題，使用的是 select ……lock in share mode，主動為付表加S鎖，如果這時父表上已有X鎖，則阻塞，

6.4 鎖的演算法

6.4.1 行鎖的三種演算法

　　InnoDB 有三種行鎖演算法，分別是

1. Record Lock：單個記錄上的鎖
2. Gap Lock：間隙鎖，鎖定一個范圍，但不包含記錄本身
3. Next-Key Lock：Gap Lock + Record Lock，鎖定一個范圍，并鎖定記錄本身

Record Lock 總是鎖住索引記錄，如果 InnoDB在建立時沒有設定任何一個索引，那么 InnoDB會使用隱士的主鍵來鎖定，

InnoDB對于行的查詢都是采用 Next-Key Lock，其目的是為了解決幻讀 Phantom Problem，是謂詞鎖的一種改進，

當查詢的索引含有唯一屬性時，InnoDB 會對Next-Key Lock優化，降級為 Record Key，以此提高應用的并發性，

如前面介紹的，next-key lock 降級為record lock是在查詢的列是唯一索引的情況下，若是輔助索引，則情況不同：

create table z (a int, b int , Primary key(a), key(b))
insert into z select 1,1;
insert into z select 3,1;
insert into z select 5,3;
insert into z select 7,6;
insert into z select 10,8;

　　其中b是輔助索引，此時執行 select * from z where b=3 for update;

　　此時，sql 陳述句通過索引列b進行查詢，因此其使用傳統的 next-key lock 進行加鎖，并且由于有兩個索引，其需要分別進行鎖定，對于聚集索引，僅對列a=5的索引加record lock，而輔助索引，加的是next-key lock，鎖定的是（1，3）范圍，特別注意的是，InnoDB還會對輔助索引下一個鍵值加上 gap lock，即還有一個（3，6）范圍的鎖，因此，一下SQL都會被阻塞：

select * from z where a=5 lock in share mode;
insert into z select 4,2;
insert into z select 6,5;

　　從上面可以看出，gap lock的作用是為了阻止多個事務將記錄插入到同一個范圍內，而這會導致幻讀問題的產生，用戶可以通過以下兩種方式來關閉 Gap Lock：

1. 將事務隔離級別改為 READ COMMITED 
2. 將引數 innodb_locks_unsafe_for_binlog設為1

在上述的配置下，除了外鍵約束和唯一性檢查依然需要的Gap Lock，其余情況僅使用 Record Lock進行鎖定，需要牢記的是，上述配置破壞了事務的隔離性，并且對 replication可能會導致不一致，且從性能上看，READ COMMITED也不會優于默認的 READ REPEATABLE；

在 InnoDB中，對Insert的操作，其會檢查插入記錄的下一條記錄是否被鎖定，若已鎖定，則不允許查詢，對于上面的例子，會話A已經鎖定了表中b=3的記錄，即已鎖定了（1，3）的范圍，這時如果在其他會話中進行如下的插入同樣會導致阻塞

insert into z select 2，2；

　　因為檢測到輔助索引列b上插入2的記錄時，發現3已經被索引，而將插入修改為如下值，可以立即執行：

insert into z select 2，0；

　　最后，對于唯一鍵值的鎖定，next-key lock降級為record ke僅存在于查詢所有的唯一索引列，若唯一索引由多個列組成，而查詢僅是查找多個唯一索引列中的一個，那么查詢其實是range型別，而不是point查詢，故InnoDB依然采用 next-key lock進行鎖定，

6.4.2 解決 Phantom Problem

幻讀指的是在同一個事務下，連續執行兩次相同的SQL陳述句可能回傳不一樣的結果，第二次的SQL陳述句可能會回傳之前不存在的行，

InnoDB采用 next-key lock 的演算法解決了 Phantom Problem，對 select * from t where id > 2 for update，鎖住的不單是5這個值，而是對（2，+∞）這個范圍加了X鎖，因此，對這個范圍的插入是不允許的，從而避免幻讀，

REPEATABLE  READ 采用的是 next-key locking加鎖，而 READCOMMITED 采用的是 record lock .

此外，用戶可以通過 InnoDB的 next-key lock在應用層面實作唯一性的檢查：

select * from table where col=xxx lock in share mode;
if not found any row :
#unique for insert value
insert into table values(……)；

　　如果用戶通過一個索引查詢一個值，并對該行加上了S lock，那么即使查詢的值不存在，其鎖定的也是一個范圍，因此若沒有回傳任何行，那么新插入的值一定是唯一的，

那，如果在第一步select lock in share mode時，有多個事務并發操作，那么這種唯一性檢查是否會有問題，其實不會，因為會發生死鎖，只有一個事務會成功，其他的事務會拋出死鎖錯誤，

6.5 鎖問題

6.5.1 臟讀

臟資料是指未提交的資料，如果讀到了臟資料，即一個事務可以讀到另一個事務未提交的資料，則顯然違反了資料庫的隔離性，

臟讀指的是在不同事務下，當前事務可以讀到另外事務未提交的資料，即臟資料，

臟讀發生條件是需要事務的隔離級別為 read uncommited；目前大部分資料庫至少設定為 read COMMITED；

6.5.2 不可重復讀

不可重復讀和臟讀的區別是：臟讀是讀到未提交的資料，而不可重復讀讀到的是已提交的資料，但是違反了事務一致性的要求，

一般來說，不可重復讀是可接受的，因為讀到的是已提交的資料，本身沒有帶來很大問題，在 InnoDB中使用 next-key lock避免不可重復讀問題，即 幻讀（Phantom Problem），在 Next-Key lock演算法下，對索引的掃描，不僅是鎖住掃描到的索引，還有這些索引覆寫的范圍，因此在這個范圍內插入是不允許的，這樣則避免了另外的事務在這個范圍內的插入導致不可重讀的問題，

6.5.3 丟失更新

丟失更新就是一個事務的更新操作被另一個事務的更新操作覆寫，從而導致資料不一致，

1. 事務1將行記錄r更新為v1，但是事務未提交
2. 事務2將行記錄r更新為v2，事務未提交
3. 事務1提交
4. 事務2提交

當前資料庫的任何隔離級別下，以上情況都不會導致資料庫理論意義上的丟失更新問題，因為，對于行的DML操作，需要對行貨其他粗粒度級別的物件加鎖，步驟2，事務2并不能對記錄進行更新，被阻塞，直到事務1提交，

但在生產應用中，還有一個邏輯意義的丟失更新問題，而導致該問題的不是因為資料庫本身的問題，簡單來說，下面情況會發生丟失更新：

1. 事務T1查詢一行資料，放入本地記憶體，回傳給User1
2. 事務T2查詢一行資料，放入本地記憶體，回傳給User2
3. User1修改后，更新資料庫提交
4. User2修改后，更新資料庫提交

顯然，這個程序中，User1的修改操作”丟失“了，在銀行操作中，尤為恐怖，要避免丟失，需要讓事務串行化，

6.6 阻塞

因為不同鎖之間的兼容性關系，在有些時刻一個事務中的鎖需要等待另一個事務中的鎖釋放它所占用的資源，這就是阻塞，

阻塞并不是一件壞事，其是為了確保事務可以并發且正常地運行，

需要牢記的是，默認情況下，InnoDB存盤引擎不會回滾超時引發的錯誤例外，其實，InnoDB在大部分情況下都不會對例外進行回滾，

6.7 死鎖

死鎖是指兩個或兩個以上的事務在執行程序中，因爭奪資源而造成的一種相互等待的現象，解決死鎖的辦法：

1. 超時回滾
2. wait-for graph 等待圖的方式進行死鎖檢測，采用深度有限的演算法，選擇回滾undo量最小的事務，

死鎖的概率與以下因素有關：

1. 事務的數量n，數量越多死鎖概率越大
2. 事務操作的數量r，數量越多，死鎖概率越大
3. 操作資料的集合R，越小，死鎖概率越大

6.8 鎖升級

鎖升級就是將當前鎖的粒度降低，例如把行鎖升級為頁鎖，把頁鎖升級為表鎖，

InnoDB不存在鎖升級，因為其不是根據每個記錄來產生行鎖的，相反，其根據每個事務訪問的每個頁對鎖進行管理，采用的是位圖的方式，因此不管一個事務鎖住頁中的一個記錄還是多個記錄，開銷都是一樣的，

標籤：MySQL

上一篇：mysql 使用技巧 分頁limit

下一篇：精準營銷如何確定最終營銷名單