對於MySQL你必須要了解的鎖知識

木木匠發表於2019-04-14
MySql

對於MySQL你必須要了解的鎖知識

一、前言

MySQL 的鎖按照範圍可以分為全域性鎖、表鎖、行鎖，其中行鎖是由資料庫引擎實現的，並不是所有的引擎都提供行鎖，MyISAM 就不支援行鎖，所以文章介紹行鎖會以InnoDB引擎為例來介紹。

二、全域性鎖

MySQL 提供全域性鎖來對整個資料庫例項加鎖。

語法：

FLUSH TABLES WITH READ LOCK
複製程式碼

這條語句一般都是用來備份的，當執行這條語句後，資料庫所有開啟的表都會被關閉，並且使用全域性讀鎖鎖定資料庫的所有表，同時，其他執行緒的更新語句（增刪改），資料定義語句（建表，修改表結構）和更新類的事務提交都會被阻塞。

在mysql 8.0 以後，對於備份，mysql可以直接使用備份鎖。

語句：

LOCK INSTANCE FOR BACKUP

UNLOCK INSTANCE
複製程式碼

這個鎖的作用範圍更廣，這個鎖會阻止檔案的建立，重新命名，刪除，包括 REPAIR TABLE TRUNCATE TABLE, OPTIMIZE TABLE操作以及賬戶的管理都會被阻塞。當然這些操作對於記憶體臨時表來說是可以執行的，為什麼記憶體表不受這些限制呢？因為記憶體表不需要備份，所以也就沒必要滿足這些條件。

三、表鎖

Mysql的表級別鎖分為兩類，一類是後設資料鎖（Metadata Lock，MDL），一種是表鎖。

後設資料鎖(MDL) 不需要顯式使用，在訪問一個表的時候會被自動加上。這個特性需要MySQL5.5版本以上才會支援，當對一個表做增刪改查的時候，該表會被加MDL讀鎖；當對錶做結構變更的時候，加MDL寫鎖。MDL鎖有一些規則：

  • 讀鎖之間不互斥，所以可以多執行緒多同一張表進行增刪改查。
  • 讀寫鎖、寫鎖之間是互斥的，為了保證表結構變更的安全性，所以如果要多執行緒對同一個表加欄位等表結構操作，就會變成序列化，需要進行鎖等待。
  • MDL的寫鎖優先順序比MDL讀鎖的優先順序，但是可以設定max_write_lock_count系統變數來改變這種情況，當寫鎖請求超過這個變數設定的數後，MDL讀鎖的優先順序會比MDL寫鎖的優先順序高。（預設情況下，這個數字會很大，所以不用擔心寫鎖的優先順序下降）
  • MDL的鎖釋放必須要等到事務結束才會釋放

所以我們在運算元據庫表結構時候必須要注意不要使用長事務，這裡具體是什麼意思呢？我舉個例子說明下：

對於MySQL你必須要了解的鎖知識

上圖表示演示了4個session執行語句，首先SessionA開啟了事務沒有提交，接著sessionB執行查詢，因為是獲取MDL讀鎖，所以互相不影響，可以正常執行，SessionC新增一個欄位，由於MDL寫和讀是互斥的，所以SessionC會被阻塞，之後SessionD開始執行一個查詢語句，由於SessionC的阻塞，所以SessionD也阻塞了。所以，我們模擬的SessionA的事務是長事務，然後後面執行了修改表結構，會導致後續對該表所有的讀寫操作都不可行了。所以在實際場景中，如果業務請求比較頻繁的時候，對錶結構進行修改的時候就有可能導致該庫的執行緒被阻塞滿。

表鎖 的語法如下：

LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES
複製程式碼

表鎖分為讀鎖和寫鎖，讀鎖不互斥，但是獲取讀鎖不能寫入資料,其他沒有獲取到讀鎖的session也是可以讀取表的，所以讀鎖的目的就是限制表被寫。如果表被讀鎖鎖住後，再執行插入語句會報錯，報錯如下：

1099 - Table 'XXXX' was locked with a READ lock and can't be updated
複製程式碼

寫鎖被獲取後可以對錶進行讀寫，寫鎖是互斥的，一旦某個session獲取到表的寫鎖，另外的session無法訪問這個表，直到寫鎖被釋放。

表的解鎖可以使用unlock tables解鎖，也可以客戶埠自動解鎖。lock tables鎖表會獨佔式的鎖住表，除了限制其他執行緒對該表的讀寫，也會限制本執行緒接下來的操作物件。

四、行鎖(InnoDB)

MySQL的行鎖是在引擎層面實現的，所以這裡討論的也是InnoDB引擎下的行鎖，下面會詳細介紹InnoDB下常見的幾種行鎖

4.1 共享鎖

共享鎖能允許事務獲取到鎖後進行讀操作，共享鎖是不互斥的，一個事務獲取到共享鎖後，另外一個事務也可以獲取共享鎖，獲取共享鎖後不能進行寫操作。

4.2 排它鎖

排他鎖允許事務獲取到鎖後進行更新一行或者刪除某一行操作，排他鎖顧名思義是互斥的，一個事務獲取到排他鎖後，其他事務不能獲取到排他鎖，直到這個鎖被釋放。

4.3 意向鎖

InnoDB支援多種粒度的鎖，允許行鎖和表鎖共存，這裡說的意向鎖其實是一種表級別的鎖，但是我把它放在行鎖裡面是因為它不會單獨存在，它的出現肯定會伴隨著行鎖（共享鎖或者排他鎖），它主要的目的就是表示將要鎖定表中的行或者正在鎖定表中的行。

意向鎖根據和行鎖的組合可以分為：

  • 意向排他鎖：表明將要在表中的某些行獲取排他鎖

  • 意向共享鎖：表明將要在表中的某些行獲取共享鎖

意向鎖的獲取必須在行鎖獲取之前，也就是說獲取共享鎖之前必須先要獲取共享意向鎖，對於排他鎖也是一樣的道理。

那麼這個意向鎖到底有什麼作用呢？

解釋這個之前，我們先看看意向鎖和行鎖之前的相容關係：

- 排他鎖（X） 意向排他鎖（IX) 共享鎖(S) 意向共享鎖(IS)
排他鎖(X） 衝突 衝突 衝突 衝突
意向排他鎖（IX) 衝突 相容 衝突 相容
共享鎖(S) 衝突 衝突 相容 相容
意向共享鎖(IS) 衝突 相容 相容 相容

我們假設有2個事務A和事務B，事務獲取到了共享鎖，鎖住了表中的某一行，這一行只能讀，不能寫，現在事務B要申請整個表的寫鎖。如果事務B申請成功，那麼肯定是可以對錶中所有的行進行寫操作的，那麼肯定與A獲取的行鎖衝突。資料庫為了避免這種衝突，就會進行衝突檢測，那麼如何去檢測呢？有兩種方式：

  • 判斷表是否已經被其他事務用表級鎖鎖住。
  • 判斷表中的每一行是否被行鎖鎖住。

判斷表中的每一行需要遍歷所有記錄，效率太差，所以資料庫就用第一種方式去做衝突檢測，也就是用到了意向鎖。

總結

本文主要從MySQL的加鎖範圍來分析了MySQL的鎖，MySQL根據加鎖範圍可以分為全域性鎖、表鎖、行鎖。全域性鎖和表鎖是MySQL自己實現，行鎖都是由引擎層面去實現。InnoDB下的行鎖主要分為共享鎖和排他鎖。共享鎖請求後，行只能讀，共享鎖之間不互斥。排他鎖獲取後能更新和刪除行，排他鎖與其他鎖都互斥。最後我在行鎖的基礎上提到了意向鎖，意向鎖主要表示正在鎖住行或者即將鎖住行，為了在鎖衝突檢測中提高效率。當然InnoDB下還有其他鎖，比如間隙鎖，記錄鎖，Next-Key鎖等，這些都不在本文的探討範圍之內，如有興趣的同學可以自行研究。

參考

相關文章