【大廠面試05期】說一說你對MySQL中鎖的瞭解？

NotFound9發表於2020-06-08
面試MySql

這是我總結的一個表格，是本文中涉及到的鎖（因為篇幅有限就沒有包括自增鎖）

加鎖範圍 名稱 用法
資料庫級 全域性讀鎖 執行Flush tables with read lock命令各整個庫接加一個讀鎖，處於只讀狀態。
資料庫級 讓全域性只讀 執行set global readonly=true命令可以讓全庫只能讀
表級別 表鎖 lock tables test read讓test表處於只讀狀態，所有事務都不能改表。lock tables test read讓test表處於只讀狀態，所有事務都不能讀表，只有當前事務可以更新表。
表級別 後設資料鎖（metadata lock，簡寫為MDL) 在MySQL 5.5版本中引入了後設資料鎖，當對一個表做增刪改查操作的時候，加MDL讀鎖；當要對錶做結構變更操作的時候，加MDL寫鎖。主要是為了保證讀寫的正確性，不能一邊讀取表資料，一邊修改表結構。
表級別 意向鎖 意向鎖的作用主要是表明當前表是否存在資料行加了行鎖，在申請行級別的S鎖之前會主動申請表級別的共享意向鎖IS鎖。在申請行級別的X鎖之前會申請表級別的意向鎖IX鎖
行級別 記錄鎖 就是鎖單個索引
行級別 間隙鎖 就是鎖索引之間的間隙，防止其他事務往這個間隙中插入新資料。
行級別 下一鍵鎖（next key lock） 就是記錄鎖+間隙鎖，就是既鎖索引，也鎖索引之間的間隙。

1.資料庫級別的鎖

資料庫級別的鎖有以下兩種：

1.1.全域性讀鎖

對資料庫執行Flush tables with read lock命令讓整個庫處於只讀狀態。

1.2.讓全域性只讀

執行set global readonly=true這個命令也可以讓全庫只能讀，但是第一有些系統會使用readonly來做一個操作，例如根據readonly是否為true判斷資料庫是否是從庫，第二是如果執行這個命令後，客戶端斷開連線後，資料庫會一直處於只讀狀態，如果是FTWRL命令傳送異常會釋放全域性鎖。（如果是從庫，設定read-only對super user許可權無效）

使用場景：

最常用的場景是對資料庫備份。對資料庫加鎖，讓整個資料庫處於只讀狀態，所有更新操作停止（如果是主庫就不能執行更新語句，從庫也不能執行同步過來的bin log），然後對整個資料庫做邏輯備份（就是將所有資料生成SQL寫入備份檔案。）

補充資料：

更好的進行資料庫備份的一種方法

就是通過官方自帶的邏輯備份工具mysqldump來進行邏輯備份時，設定一個引數-single-transaction，這樣導資料的時候就會開啟一個事務，這樣利用innodb的mvcc機制可以保證在事務執行過程中，讀到的資料都跟事務開始時的一致，並且執行過程中，其他事務可以執行更新操作， 不會對他造成影響（因為它就跟普通SELECT查詢一樣是讀取的快照資料），這種方法必須要求資料庫所有表的引擎都是innodb才行。

2.表級別的鎖

表級別的鎖有兩種，一種是表鎖，一種是後設資料鎖MDL。

2.1表鎖 lock table

就是使用lock table user_table read/write命令來對錶進行加讀鎖或者寫鎖。

  • 加讀鎖（也就是表級別共享鎖X鎖）後，表對所有執行緒都是隻能讀，即便是當前執行緒也只能讀表，不然會資料不一致。

  • 加寫鎖後，表是對當前執行緒寫，其他執行緒不能讀，不能回資料不一致。

可以通過unlock tables來解鎖，客戶端斷開時也會自動釋放鎖，但是影響所有執行緒，影響面太大了。這種鎖我們一般也不會主動去呼叫，但是我們去更新一些資料時，如果查詢條件是根據一些沒有索引的欄位去查詢的，那樣更新時會主動申請表鎖中的寫鎖，獲取成功後才能修改資料，事務提交成功之後，才會釋放鎖。（這也是為什麼我們一般強調對於常用的查詢欄位加索引，就是為了提高更新和讀取效率。）

2.2後設資料鎖MDL(MetaData Lock)

分為讀鎖和寫鎖，加讀鎖時，所有的執行緒都可以讀表，加寫鎖時，只能一個執行緒寫，其他的不能讀。
鎖不用顯式使用，是訪問一個表時，自動加上的。
對錶進行增刪改查時，會加讀鎖。
對錶結構做修改時，會加寫鎖。

目的是為了在增刪改查時不能修改表結構，修改表結構時不能去增刪改查。

2.3 意向鎖

意向鎖的作用主要是表明當前表是否存在資料行加了行鎖。這樣事務可以根據當前表是否有意向鎖來快速判斷當前表是否存在資料行加了行鎖，這樣再加表級別的排斥鎖X，共享鎖S時，避免了去查詢每一行資料，判斷是否加了行鎖，減小了效能開銷。

意向共享鎖（IS鎖）

事務讓一行資料只能讀，需要申請對這行資料加行級別的共享鎖S鎖，在申請行級別的S鎖之前會主動申請表級別的共享意向鎖IS鎖。

意向排斥鎖(IX鎖)

事務在更新某一行資料時，需要申請對這行資料加行級別的排斥鎖X鎖，在申請行級別的X鎖之前會申請表級別的意向鎖IX鎖。

意向鎖之間是相容的，IS鎖和IX是相容，因為可能我們對第一行資料加S鎖，那麼會申請IS鎖，對第二行資料加X鎖，此時跟第一行的資料的S鎖不衝突，所以也會先申請IX鎖，由此可見，IS鎖和IX之間不衝突，IS鎖，IX鎖與行級別的S，行級別的X之間也不衝突。

意向鎖只是跟表級別的S，X鎖可能會衝突。

表級別的S鎖 表級別的X鎖
意向共享鎖IS 相容 不相容
意向排斥鎖IX 不相容 不相容

行級別的鎖

行鎖是innodb引擎特有的鎖，也是分為共享鎖（也就是通常說的讀鎖）和互斥鎖（也就是通常說的寫鎖）

  • 共享鎖 S鎖，就是讀鎖，允許事務讀一行資料，不能被修改。所以讀鎖之間不排斥

  • 互斥鎖 X鎖，就是寫鎖，就是讓當前事務可以修改這行資料，其他事務不能修改這行資料

如果是從加鎖的範圍來區分，行鎖主要分為記錄鎖(鎖單個索引)，間隙鎖（鎖索引之間的間隙），下一鍵鎖（等於記錄鎖+間隙鎖）

記錄鎖 record lock

記錄鎖鎖定的是單條索引記錄。例如 SELECT c1 FROM t WHERE c1 = 10 FOR UPDATE; ，如果c是主鍵或者是一個唯一性索引的欄位，由於在表內唯一，所以只需要對c=10這個索引進行加鎖，可以防止其他事務插入，更新或刪除這個資料行。

間隙鎖 gap lock

間隙鎖就會對記錄之間的間隙加鎖，防止資料插入。

下一鍵鎖 next-key lock

next-key lock是 record lock 和 gap lock的組合，就是會對索引記錄加記錄鎖 + 索引記錄前面間隙上的鎖”，就是對要更新的資料的左右兩個端點加間隙鎖。

具體案例：

因為innodb預設的隔離級別是可重複讀，我們在執行更新語句和使用當前讀語句（SELECT…FOR UPDATE）時，都是需要加一些行鎖的，來防止其他事務插入或者刪除資料，導致在事務內多次讀取到的資料行不同。針對行鎖的加鎖規則，極客時間中丁奇老師總結了以下四條規則：

  1. 原則1：加鎖的基本單位是next-key lock。希望你還記得，next-key lock是前開後閉區間。
  2. 原則2：查詢過程中訪問到的物件才會加鎖。
  3. 優化1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock退化為記錄鎖。
  4. 優化2：索引上的等值查詢，向右遍歷時且最後一個值不滿足等值條件的時候，next-key lock退化為間隙鎖。
  5. 一個bug：唯一索引上的範圍查詢會訪問到不滿足條件的第一個值為止。

簡單的來說，我認為就是next-key lock就是加鎖的基本單位，只不過innodb做了很多優化，在不需要對那麼大範圍的資料行加鎖時，會進行降級，降級為間隙鎖，或者是記錄鎖。

下面就來看一個具體的例子

例如:

a是一個普通欄位，對它建了索引，在資料庫中對應a這個欄位存在的值已經有1，5，10，20，30

那麼根據next-key lock來劃分割槽間，next-key lock是根據已有資料行來劃分割槽間，並且是左開右閉區間，所以可以鎖定的區間是

(負無窮,1]
(1,5]
(5,10]
(10,20]
(20,30]
(30,正無窮)

//更新操作
update table set b = '1' where a = 10;

在innodb中執行更新操作，

  • 如果a是唯一性索引，根據原則3那麼只需要對a為10的這條索引加記錄鎖就行了，因為不用擔心其他事務再插入一條a為10的資料，因為插入時會有唯一性判斷。
  • 但是如果a是非唯一性索引，如果只是對a=10這個索引加鎖，可能會有其他事務插入a=10的資料行，所以會對（5，10]和（10，20]這兩個區間加鎖，並且根據上面的原則4，會將（10，20]降級為間隙鎖，也就是隻對（10，20）加鎖，因為a=20這個索引是否加鎖都不影響當前的事務。
  • 如果a沒有索引，需要插入時會先申請表級別的互斥鎖X鎖，然後進行插入。

相關文章