MySQL——InnoDB行鎖

InnoDB行鎖

先從兩階段鎖協議說起

InnoDB的行鎖模式及加鎖方法

InnoDB行鎖實現方式

Next-Key鎖

相關知識點

什么時候使用表鎖

死鎖和死鎖檢測

總結

可以通過檢查InnoDB_row_lock狀態變量來分析系統上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%';

1

先從兩階段鎖協議說起

在 InnoDB 事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

我先給你舉個例子。在下面的操作序列中，事務 B 的 update 語句執行時會是什么現象呢？假設字段 id 是表 t 的主鍵。

這個問題的結論取決于事務 A 在執行完兩條 update 語句后，持有哪些鎖，以及在什么時候釋放。

實際上事務 B 的 update 語句會被阻塞，直到事務 A 執行 commit 之后，事務 B 才能繼續執行。

事務 A 持有的兩個記錄的行鎖，都是在 commit 的時候才釋放的。

也就是說，在 InnoDB 事務中，行鎖是在需要的時候才加上的，但并不是不需要了就立刻釋放，而是要等到事務結束時才釋放。這個就是兩階段鎖協議。

如果你的事務中需要鎖多個行，要把最可能造成鎖沖突、最可能影響并發度的鎖盡量往后放。

InnoDB的行鎖模式及加鎖方法

InnoDB實現了以下兩種類型的行鎖。

共享鎖（S）：允許一個事務去讀一行，阻止其他事務獲得相同數據集的排他鎖。

排他鎖（X）：允許獲得排他鎖的事務更新數據，阻止其他事務取得相同數據集的共享讀鎖和排他寫鎖。

另外，為了允許行鎖和表鎖共存，實現多粒度鎖機制，InnoDB 還有兩種內部使用的意向鎖（Intention Locks），這兩種意向鎖都是表鎖。

意向共享鎖（IS）：事務打算給數據行加行共享鎖，事務在給一個數據行加共享鎖前必須先取得該表的IS鎖。

意向排他鎖（IX）：事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖。

如果一個事務請求的鎖模式與當前的鎖兼容，InnoDB 就將請求的鎖授予該事務；反之，如果兩者不兼容，該事務就要等待鎖釋放。

意向鎖是InnoDB自動加的，不需用戶干預。

對于UPDATE、DELETE和INSERT語句， InnoDB會自動給涉及數據集加排他鎖（X）；對于普通SELECT語句，InnoDB不會加任何鎖；

事務可以通過以下語句顯示給記錄集加共享鎖或排他鎖。

共享鎖（S）：

SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;

1

排他鎖（X）：

SELECT * FROM table_name WHERE ... FOR UPDATE;

1

用SELECT ... IN SHARE MODE獲得共享鎖，主要用在需要數據依存關系時來確認某行記錄是否存在，并確保沒有人對這個記錄進行UPDATE或者DELETE操作。

但是如果當前事務也需要對該記錄進行更新操作，則很有可能造成死鎖，對于鎖定行記錄后需要進行更新操作的應用，應該使用SELECT... FOR UPDATE方式獲得排他鎖。

InnoDB行鎖實現方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現的，如果沒有索引，InnoDB將通過隱藏的聚簇索引來對記錄加鎖。InnoDB行鎖分為3種情形。

Record lock：對索引項加鎖。

Gap lock：對索引項之間的“間隙”、第一條記錄前的“間隙”或最后一條記錄后的“間隙”加鎖。

Next-key lock：前兩種的組合，對記錄及其前面的間隙加鎖。

InnoDB這種行鎖實現特點意味著：如果不通過索引條件檢索數據，那么InnoDB將對表中的所有記錄加鎖，實際效果跟表鎖一樣！

（1）在不通過索引條件查詢時，InnoDB會鎖定表中的所有記錄。

（2）由于 Mysql 的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是訪問不同行的記錄，但是如果是使用相同的索引鍵，是會出現鎖沖突的。

（3）當表有多個索引的時候，不同的事務可以使用不同的索引鎖定不同的行，不論是使用主鍵索引、唯一索引或普通索引，InnoDB都會使用行鎖來對數據加鎖。

（4）即便在條件中使用了索引字段，但是否使用索引來檢索數據是由Mysql通過判斷不同執行計劃的代價來決定的，如果MySQL認為全表掃描效率更高，比如對一些很小的表，它就不會使用索引，這種情況下InnoDB也會對所有記錄加鎖。因此，在分析鎖沖突時，別忘了檢查SQL的執行計劃，以確認是否真正使用了索引。

Next-Key鎖

當我們用范圍條件而不是相等條件檢索數據，并請求共享或排他鎖時，InnoDB 會給符合條件的已有數據記錄的索引項加鎖；對于鍵值在條件范圍內但并不存在的記錄，叫做“間隙（GAP）”，InnoDB也會對這個“間隙”加鎖，這種鎖機制就是所謂的Next-Key鎖。

舉例來說，假如emp表中只有101條記錄，其empid的值分別是1、2、…、100、101，下面的SQL：

Select * from emp where empid > 100 for update;

1

是一個范圍條件的檢索，InnoDB不僅會對符合條件的empid值為101的記錄加鎖，也會對empid大于101（這些記錄并不存在）的“間隙”加鎖。 InnoDB使用Next-Key鎖的目的，是為了防止幻讀，以滿足相關隔離級別的要求。

范圍條件加鎖時使用Next-Key鎖外，如果使用相等條件請求給一個不存在的記錄加鎖，InnoDB也會使用Next-Key鎖！

相關知識點

innodb對于行的查詢使用next-key lock

Next-locking keying為了解決Phantom Problem幻讀問題

當查詢的索引含有唯一屬性時，將next-key lock降級為record key

Gap鎖設計的目的是為了阻止多個事務將記錄插入到同一范圍內，而這會導致幻讀問題的產生

有兩種方式顯式關閉gap鎖：（除了外鍵約束和唯一性檢查外，其余情況僅使用record lock） A. 將事務隔離級別設置為RC B.將參數innodb_locks_unsafe_for_binlog設置為1

什么時候使用表鎖

對于InnoDB表，在絕大部分情況下都應該使用行級鎖，因為事務和行鎖往往是我們選擇InnoDB表的理由。

但在個別特殊事務中，也可以考慮使用表級鎖。

第一種情況是：事務需要更新大部分或全部數據，表又比較大，如果使用默認的行鎖，不僅這個事務執行效率低，而且可能造成其他事務長時間鎖等待和鎖沖突，這種情況下可以考慮使用表鎖來提高該事務的執行速度。

第二種情況是：事務涉及多個表，比較復雜，很可能引起死鎖，造成大量事務回滾。這種情況也可以考慮一次性鎖定事務涉及的表，從而避免死鎖，減少數據庫因事務回滾帶來的開銷。

死鎖和死鎖檢測

當并發系統中不同線程出現循環資源依賴，涉及的線程都在等待別的線程釋放資源時，就會導致這幾個線程都進入無限等待的狀態，稱為死鎖。

這時候，事務 A 在等待事務 B 釋放 id=2 的行鎖，而事務 B 在等待事務 A 釋放 id=1 的行鎖。 事務 A 和事務 B 在互相等待對方的資源釋放，就是進入了死鎖狀態。當出現死鎖以后，有兩種策略：

一種策略是，直接進入等待，直到超時。這個超時時間可以通過參數 innodb_lock_wait_timeout 來設置。

另一種策略是，發起死鎖檢測，發現死鎖后，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數

innodb_deadlock_detect 設置為 on，表示開啟這個邏輯。

在 InnoDB 中，innodb_lock_wait_timeout 的默認值是 50s，意味著如果采用第一個策略，當出現死鎖以后，第一個被鎖住的線程要過 50s 才會超時退出，然后其他線程才有可能繼續執行。

所以，正常情況下我們還是要采用第二種策略，即：主動死鎖檢測，而且 innodb_deadlock_detect 的默認值本身就是 on。主動死鎖檢測在發生死鎖的時候，是能夠快速發現并進行處理的，但是它也是有額外負擔的。

問題的癥結在于，死鎖檢測要耗費大量的 CPU 資源。

一種頭痛醫頭的方法，就是如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關掉。但是這種操作本身帶有一定的風險，因為業務設計的時候一般不會把死鎖當做一個嚴重錯誤，畢竟出現死鎖了，就回滾，然后通過業務重試一般就沒問題了，這是業務無損的。而關掉死鎖檢測意味著可能會出現大量的超時，這是業務有損的。

另一個思路是控制并發度。根據上面的分析，如果并發能夠控制住，比如同一行同時最多只有 10 個線程在更新，那么死鎖檢測的成本很低，就不會出現這個問題。一個直接的想法就是，在客戶端做并發控制。但是，你會很快發現這個方法不太可行，因為客戶端很多。

因此，這個并發控制要做在數據庫服務端。如果你有中間件，可以考慮在中間件實現；如果你的團隊有能修改 MySQL 源碼的人，也可以做在 MySQL 里面。基本思路就是，對于相同行的更新，在進入引擎之前排隊。這樣在 InnoDB 內部就不會有大量的死鎖檢測工作了。

總結

對于InnoDB表，本章主要討論了以下幾項內容。

InnoDB的行鎖是基于索引實現的，如果不通過索引訪問數據，InnoDB會對所有數據加鎖。

InnoDB Next-Key鎖機制，以及 InnoDB使用Next-Key鎖的原因。

在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。

MySQL的恢復和復制對InnoDB鎖機制和一致性讀策略也有較大影響。

鎖沖突甚至死鎖很難完全避免。

在了解InnoDB鎖特性后，用戶可以通過設計和SQL調整等措施減少鎖沖突和死鎖，包括以下幾項：

盡量使用較低的隔離級別；

精心設計索引，并盡量使用索引訪問數據，使加鎖更精確，從而減少鎖沖突的機會；

選擇合理的事務大小，小事務發生鎖沖突的幾率也更小；

給記錄集顯式加鎖時，最好一次性請求足夠級別的鎖。比如要修改數據，最好直接申請排他鎖，而不是先申請共享鎖，修改時再請求排他鎖，這樣容易產生死鎖；

不同的程序訪問一組表時，應盡量約定以相同的順序訪問各表，對一個表而言，盡可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；

盡量用相等條件訪問數據，這樣可以避免Next-Key鎖對并發插入的影響；

不要申請超過實際需要的鎖級別；

除非必須，查詢時不要顯示加鎖；

對于一些特定的事務，可以使用表鎖來提高處理速度或減少發生死鎖的幾率。

注：文章內容參考自

極客時間《MySQL實戰45講》

書籍《深入淺出MySQL》

MySQL 任務調度

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。

版權聲明：本文內容由網絡用戶投稿，版權歸原作者所有，本站不擁有其著作權，亦不承擔相應法律責任。如果您發現本站中有涉嫌抄襲或描述失實的內容，請聯系我們jiasou666@gmail.com 處理，核實后本網站將在24小時內刪除侵權內容。