MySQL的鎖

小強Zzz發表於2021-01-31
MySql

MySQL

1、什麼是鎖

鎖機制用於管理對共享資源的併發訪問。

lock與latch

  • latch一般稱為閂鎖（輕量級的鎖），因為其要求鎖定的時間必須非常短。若持續的時間長，則應用的效能會非常差。在InnoDB儲存引擎中，latch又可以分為mutex（互斥量）和rwlock（讀寫鎖）。其目的是用來保證併發執行緒操作臨界資源的正確性，並且通常沒有死鎖檢測的機制。
  • lock的物件是事務，用來鎖定的是資料庫中的物件，如表、頁、行。並且一般lock的物件僅在事務commit或rollback後進行釋放。不同事務隔離級別釋放的時間可能不同，所以分析lock問題，要先看事務隔離級別（select @@tx_isolation;）。

2、MyISAM儲存引擎中的鎖

MyISAM儲存引擎只支援表鎖。

MyISAM儲存引擎的讀鎖和寫鎖是互斥的，讀寫操作是序列的。
但它認為寫鎖的優先順序比讀鎖高，所以即使讀請求先到鎖等待佇列，寫請求後到，寫鎖也會插到讀鎖請求之前！
這也正是MyISAM表不太適合於有大量更新操作和查詢操作應用的原因，因為，大量的更新操作會造成查詢操作很難獲得讀鎖，從而可能永遠阻塞。
可以通過一些設定來調節MyISAM的排程行為。

3、InnoDB儲存引擎中的鎖

3.1 鎖的型別

InnoDB儲存引擎實現瞭如下兩種標準的行級鎖：

  • 共享鎖（S Lock），允許事務讀一行資料。
  • 排他鎖（X Lock），允許事務刪除或更新一行資料。
    X鎖與任何的鎖都不相容，而S鎖僅和S鎖相容，S和X鎖都是行鎖，相容是指對同一記錄（row）鎖的相容性情況。

意向鎖設計目的主要是為了在一個事務中揭示下一行將被請求的鎖型別。
如果需要對頁上的記錄r進行上X鎖，那麼分別需要對資料庫A、表、頁上意向鎖IX，最後對記錄r上X鎖。若其中任何一個部分導致等待，那麼該操作需要等待粗粒度鎖的完成。

兩種意向鎖：

  • 意向共享鎖（IS Lock），事務想要獲得一張表中某幾行的共享鎖
  • 意向排他鎖（IX Lock），事務想要獲得一張表中某幾行的排他鎖

意向鎖其實不會阻塞除全表掃以外的任何請求。

鎖的相容性情況

type IS IX S X
IS 相容 相容 相容 不相容
IX 相容 相容 不相容 不相容
S 相容 不相容 相容 不相容
X 不相容 不相容 不相容 不相容

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

3.2 一致性非鎖定讀

一致性的非鎖定讀（consistent nonlocking read）是指InnoDB儲存引擎通過行多版本控制（multi versioning）的方式來讀取當前執行時間資料庫中行的資料。如果讀取的行正在執行DELETE或UPDATE操作，這時讀取操作不會因此去等待行上鎖的釋放。相反地，InnoDB儲存引擎會去讀取行的一個快照資料。

InnoDB儲存引擎的預設設定下，這是預設的讀取方式，即讀取不會佔用和等待表上的鎖。

在不同事務隔離級別下，讀取的方式不同，並不是在每個事務隔離級別下都是採用非鎖定的一致性讀。此外，即使都是使用非鎖定的一致性讀，但是對於快照資料的定義也各不相同。

3.3 一致性鎖定讀

InnoDB儲存引擎對於SELECT語句支援兩種一致性的鎖定讀（locking read）操作：

  • SELECT…FOR UPDATE
  • SELECT…LOCK IN SHARE MODE

3.4 自增長與鎖

AUTO-INC Locking，這種鎖其實是採用一種特殊的表鎖機制，為了提高插入的效能，鎖不是在一個事務完成後才釋放，而是在完成對自增長值插入的SQL語句後立即釋放。

3.5 外來鍵和鎖

在InnoDB儲存引擎中，對於一個外來鍵列，如果沒有顯式地對這個列加索引，InnoDB儲存引擎自動對其加一個索引，因為這樣可以避免表鎖。

4、鎖的演算法

4.1 InnoDB儲存引擎有3種行鎖的演算法

  • Record Lock：單個行記錄上的鎖
  • Gap Lock：間隙鎖，鎖定一個範圍，但不包含記錄本身，是一個左開右閉的空間。如索引值有1，3，5，8，GAP的區間：（-∞,1]，(1,3]，(3,5]，(5,8]，(8,+∞）。GAP Lock的目的，是為了防止同一事務的兩次當前讀，出現幻讀的情況。
  • Next-Key Lock∶Gap Lock+Record Lock，鎖定一個範圍，並且鎖定記錄本身。對於行的查詢，都是採用該方法，主要目的是解決幻讀的問題。

4.2 解決Phantom Problem（幻像問題）

在預設的事務隔離級別下，即REPEATABLE READ下，InnoDB儲存引擎採用Next-Key Locking機制來避免Phantom Problem（幻像問題）。

Phantom Problem是指在同一事務下，連續執行兩次同樣的SQL語句可能導致不同的結果，第二次的SQL語句可能會返回之前不存在的行。

InnoDB儲存引擎預設的事務隔離級別是REPEATABLE READ，在該隔離級別下，其採用Next-Key Locking的方式來加鎖。而在事務隔離級別READ COMMITTED下，其僅採用Record Lock。

5、鎖的問題

5.1 幻讀

在同一個事務中，同一個查詢多次返回的結果不一致。事務A新增了一條記錄，事務B在事務A提交前後各執行了一次查詢操作，發現後一次比前一次多了一條記錄。幻讀是由於併發事務增加記錄導致的，這個不能像不可重複讀通過記錄加鎖解決，因為對於新增的記錄根本無法加鎖。需要將事務序列化，才能避免幻讀。

5.2 髒讀

髒資料是指事務對緩衝池中行記錄的修改，並且還沒有被提交（commit）。
髒讀發生的條件是需要事務的隔離級別為READ UNCOMMITTED。
違反了事務的隔離性。
髒讀隔離看似毫無用處，但在一些比較特殊的情況下還是可以將事務的隔離級別設定為READ UNCOMMITTED。例如replication環境中的slave節點，並且在該slave上的查詢並不需要特別精確的返回值。

5.3 不可重複讀

在一個事務內兩次讀到的資料是不一樣的情況，這種情況稱為不可重複讀。
不可重複讀和髒讀的區別是：髒讀是讀到未提交的資料，而不可重複讀讀到的卻是已經提交的資料，但是其違反了資料庫事務一致性的要求。
InnoDB儲存引擎的預設事務隔離級別是READ REPEATABLE，採用Next-Key Lock演算法，避免了不可重複讀的現象。

5.4 丟失更新

一個事務的更新操作會被另一個事務的更新操作所覆蓋，從而導致資料的不一致。
要避免丟失更新發生，需要讓事務在這種情況下的操作變成序列化，而不是並行的操作。

6、死鎖

死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪鎖資源而造成的一種互相等待的現象。除了超時機制，當前資料庫還都普遍採用wait-for graph（等待圖）的方式來進行死鎖檢測。

MyISAM總是一次獲得SQL語句所需要的全部鎖。這也正是MyISAM表不會出現死鎖（Deadlock Free）的原因。

產生死鎖的必要條件

  • 多個併發事務
  • 每個事務都持有鎖
  • 每個事務都需要再持有鎖
  • 事務之間產生加鎖的迴圈等待，形成死鎖

死鎖檢測

1.InnoDB的初始化一個事務，當事務嘗試申請加一個鎖，並且需要等待時(wait_lock)，innodb會開始進行死鎖檢測(deadlock_mark)
2.進入到lock_deadlock_check_and_resolve()函式進行檢測死鎖和解決死鎖。
3.檢測死鎖過程中，是有計數器來進行限制的，在等待wait-for graph 檢測過程中遇到超時或者超過閾值，則停止檢測。
4.死鎖檢測的邏輯之一是等待圖的處理過程，如果通過鎖的資訊和事務等待鏈構造出一個圖，如果圖中出現迴路，就認為發生了死鎖。
5.死鎖的回滾，內部程式碼的處理邏輯之一是比較undo的數量，回滾undo數量少的事務。

死鎖日誌

update `xxx` set xxx where xxx = xxx

RECORD LOCKS space id 123 page no 13726 n bits 248 index idx_xxx of table `xxx` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 123 PHYSICAL RECORD: n_fields 13; compact format; info bits 0

update `xxx` set xxx where xxx = xxx

RECORD LOCKS space id 123 page no 123456 n bits 128 index PRIMARY of table `xxx` trx id 123456 lock_mode X locks rec but not gap
Record lock, heap no 456 PHYSICAL RECORD: n_fields 10; compact format; info bits 0

上面的日誌，精簡了很多日誌，只保留了部分重要資訊，從死鎖日誌中，可以看出，執行哪條SQL，哪個頁，哪個索引，鎖的模式、鎖的屬性。

鎖的屬性

  • LOCK_REC_NOT_GAP （鎖記錄）
  • LOCK_GAP （鎖記錄前的GAP）
  • LOCK_ORDINARY （同時鎖記錄+記錄前的GAP 。傳說中的Next Key鎖）
  • LOCK_INSERT_INTENTION（插入意向鎖，其實是特殊的GAP鎖）

死鎖案例分析，最簡單的、最經典的死鎖案例，加鎖順序不一致導致死鎖。

session1:

begin;
select * from user where id = 3 for update;
select * from user where id = 5 for update;
commit;

session2:

begin;
select * from user where id = 5 for update;
select * from user where id = 3 for update;
commit;

參考資料

相關文章