技術文章 » MySQL（三）、鎖

MySQL（三）、鎖

2020-08-13 01:48:28

目錄

簡介

鎖級別

全域性鎖

表級鎖

頁級鎖

行級鎖

InnoDB鎖

共用鎖S

排他鎖X

意向鎖

記錄鎖

間隙鎖

Next-Key Lock

插入意向鎖

自增鎖

擴充套件

死鎖

活鎖

悲觀鎖

樂觀鎖

併發控制（MVCC）

MySQL系列：

MySQL（一）、InnoDB索引原理及優化

MySQL（二）、事務

簡介

鎖是計算機用以協調多個進程間併發存取同一共用資源的一種機制 機製。MySQL中爲了保證數據存取的一致性與有效性等功能，實現了鎖機制 機製，MySQL中的鎖是在伺服器層或者儲存引擎層實現的。

鎖級別

MySQL引擎預設的鎖級別：

  • MyISAM：表級鎖；
  • Memory：表級鎖；
  • InnoDB：支援行級鎖和表級鎖 ，預設爲行級鎖。
  • BDB：頁面鎖或表級鎖，預設爲頁面鎖。 （MySQL5.1之後棄用）

（在後面的文章中會詳細對比MySQL的常用引擎，下面 下麪對MySQL支援的鎖進行介紹）

全域性鎖

對整個數據庫範例加鎖，加鎖後整個庫處於只讀狀態的時候，之後其他執行緒的以下語句會被阻塞：DML（增刪改）、DDL、事務提交。不會影響DQL（查詢請求），從而獲取一致性檢視，保證數據的完整性。

全域性鎖：https://dev.mysql.com/doc/refman/8.0/en/lock-instance-for-backup.html

全域性鎖命令：

-- 加全域性鎖 （FTWRL）
flush tables with read lock;

-- 釋放全域性鎖
unlock tables;
或斷開加全域性鎖的對談連線

錯誤資訊：[Error Code: 1223, SQL State: HY000]  Can't execute the query because you have a conflicting read lock 

全域性鎖的場景是全庫備份，但前面也介紹了，全域性鎖的時候庫是隻讀狀態。此時如果在主庫加全域性鎖進行備份，業務就會停擺，如果在從庫加全域性鎖，就無法同步主庫的操作導致主從延遲。其實在實際應用中我們可以對不同的儲存引擎使用不同的策略備份。

使用mysqldump工具備份：

  • InnoDB引擎：利用MVCC提供一致性檢視，開啓一個RR級別的事務，保證備份過程中數據可以正常更新，dump對應參數爲：--single-transaction；
  • MyISAM引擎：利用FTWRL加全域性鎖，dump對應參數爲：--lock-all-tables；

表級鎖

表級鎖（Table-level Locking）：表示對當前操作的整張表加鎖，它實現簡單，資源消耗較少，被大部分 MySQL 引擎支援；

  • 優點：開銷小，加鎖快；不會出現死鎖；
  • 缺點：鎖定粒度大，發出鎖衝突的概率最高，併發度最低。

表級鎖： https://dev.mysql.com/doc/refman/8.0/en/lock-tables.html

表鎖命令：

-- 加表鎖
lock tables … read/write;

-- 釋放表鎖
unlock tables;
或斷開加表鎖的對談連線

錯誤資訊：[Error Code: 1099, SQL State: HY000]  Table 'csdn' was locked with a READ lock and can't be updated 

表讀鎖： 

表寫鎖： 

 

元數據鎖（Metadata Locking）：MySQL5.5之後新增，MDL也是表級別的鎖，不需要顯示的使用，當對一個表做增刪改查操作的時候，加 MDL 讀鎖；當要對錶做結構變更操作的時候，加 MDL 寫鎖。

  • 讀鎖之間不互斥，可以有多個執行緒同時對一張表增刪改查；
  • 讀寫鎖之間、寫鎖之間是互斥的，用來保證表結構變更操作的安全性。如果有兩個執行緒要同時給一個表加欄位，其中一個要阻塞等待另一個執行完才能 纔能開始執行。

頁級鎖

頁級鎖（Page-level Locking）：鎖定表中某些行集合（稱做頁），被鎖定的行只對鎖定最初的執行緒是可行。如果另外一個執行緒想要向這些行寫數據，它必須等到鎖被釋放。不過其他頁的行仍然可以使用。在MySQL5.1之前BDB引擎預設頁級鎖，之後被棄用。

行級鎖

行級鎖（Row-level Locking）：基於索引數據結構實現，是 MySQL 中鎖定粒度最細的一種鎖，只有執行緒當前使用的行被鎖定，其他行對於其他執行緒都是可用的；InnoDB引擎預設的鎖級別。

  • 優點：發生鎖衝突機率低，併發高
  • 缺點：開銷大，加鎖慢；會出現死鎖的情況； 

行級鎖包括共用鎖、排他鎖和更新鎖。在下面 下麪的內容中具體介紹。

InnoDB行級鎖是通過鎖索引記錄實現的，如果更新的列沒建索引會鎖住整個表。

InnoDB鎖

InnoDB鎖：https://dev.mysql.com/doc/refman/8.0/en/innodb-locking.html

共用鎖S

共用鎖（Shared Locks）：簡稱S鎖，也被稱爲讀鎖。讀鎖允許多個連線可以同一時刻併發的讀取同一資源,互不幹 不乾擾。

語法：

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

排他鎖X

排他鎖（Exclusive Locks）：簡稱X鎖，也叫獨佔鎖、寫鎖。一個獨佔鎖會阻塞其他的寫鎖或讀鎖，保證同一時刻只有一個連線可以寫入數據，同時防止其他使用者對這個數據的讀寫。執行數據更新命令，即INSERT、UPDATE 或DELETE 命令時，MySQL 會自動使用獨佔鎖。但當物件上有其它鎖存在時，無法對其加獨佔鎖。獨佔鎖一直到事務結束才能 纔能被釋放。

語法：

SELECT * FROM table_name WHERE ... FOR UPDATE

意向鎖

意向鎖（Intention Locks）：提前宣告一個意向，並獲取表級別的意向鎖（共用意向鎖 IS 或排他意向鎖 IX），如果獲取成功，則稍後將要或正在(才被允許)，對該表的某些行加鎖(S或X)了。（除了 LOCK TABLES ... WRITE,會鎖住表中所有行，其他場景意向鎖實際不鎖住任何行)。

意向鎖包含：

  • 意向共用鎖（Intention Shared Lock）：簡稱IS鎖，事務打算給數據行加行共用鎖，事務在給一個數據行加共用鎖前必須先取得該表的IS鎖.
  • 意向獨佔鎖（Intention Exclusive Lock）：簡稱IX鎖，事務打算給數據行加行排他鎖，事務在給一個數據行加排他鎖前必須先取得該表的IX鎖.

意向鎖協定：在事務能夠獲取表中行上的共用鎖之前，它必須首先獲取表上的IS鎖或更強的鎖。 在事務能夠獲取表中的行上的獨佔鎖之前，它必須首先獲取表上的IX鎖。 

InnoDB鎖相容性列表

 

 X IX S IS
X        
IX   相容   相容
S     相容 相容
IS   相容 相容 相容

共用鎖和排他鎖都是標準的行級鎖，意向鎖是表級鎖。
意向鎖是比X、S更弱的鎖，存在一種預判的意義！先獲取更弱的IX、IS鎖，如果獲取失敗就不必再花費更大的開銷獲取更強的X、S鎖。

記錄鎖

記錄鎖（Record Locks）：最簡單的行鎖，行鎖是加在索引上的，如果查詢命令不走索引時，這條語句會鎖住所有記錄也就是鎖表，如果語句的執行能夠執行某一個欄位的索引，那麼僅會鎖住滿足 where 的行(Record Lock)。

間隙鎖

間隙鎖（Gap Locks）：鎖定索引記錄之間的間隙([2])，或者鎖定一個索引記錄之前的間隙([1])，或者鎖定一個索引記錄之後的間隙([3])。

例：

如圖[1]、[2]、[3]部分。一般作用於我們的範圍篩選查詢> 、< 、between...... 

例如， SELECT userId FROM t1 WHERE userId BETWEEN 1 and 4 FOR UPDATE; 阻止其他事務將值3插入到列 userId 中。因爲該範圍內所有現有值之間的間隙都是鎖定的。

對於使用唯一索引來搜尋唯一行的語句 select a from ，不產生間隙鎖定。(不包含組合唯一索引，也就是說 gapLock 不作用於單列唯一索引）。

例如，如果id列有唯一的索引，下面 下麪的語句只對id值爲100的行使用索引記錄鎖，其他對談是否在前一個間隙中插入行並不重要:

SELECT * FROM t1 WHERE id = 100; 如果id沒有索引或具有非唯一索引，則語句將鎖定前面的間隙。

間隙可以跨越單個索引值、多個索引值(如上圖2,3)，甚至是空的；

間隙鎖是效能和併發性之間權衡的一種折衷，用於某些特定的事務隔離級別，如RC級別（RC級別：REPEATABLE READ，我司爲了減少死鎖，關閉了gap鎖，使用RR級別）；

在重疊的間隙中（或者說重疊的行記錄）中允許gap共存；

例如，同一個 gap 中，允許一個事務持有 gap X-Lock（gap 寫鎖\排他鎖)，同時另一個事務在這個 gap 中持有(gap 寫鎖\排他鎖)；

Next-Key Lock

記錄鎖與與間隙鎖的結合。

例：存在一個查詢匹配 b=3 的行(b上有個非唯一索引)，那麼所謂 NextLock 就是：在b=3 的行加了 RecordLock 並且使用 GapLock 鎖定了 b=3 之前（「之前」：索引排序）的所有行記錄。

 innodb 中預設隔離級別(RR)下，next key Lock 自動開啓；

插入意向鎖

插入意向鎖（Insert Intention Locks）：是一種間隙鎖，在行執行 INSERT 之前的插入操作設定。如果多個事務 INSERT 到同一個索引間隙之間，但沒有在同一位置上插入，則不會產生任何的衝突。假設有值爲4和7的索引記錄，現在有兩事務分別嘗試插入值爲 5 和 6 的記錄，在獲得插入行的排他鎖之前，都使用插入意向鎖鎖住 4 和 7 之間的間隙，但兩者之間並不會相互阻塞，因爲這兩行並不衝突。

插入意向鎖只會和 間隙或者 Next-key 鎖衝突，正如上面所說，間隙鎖作用就是防止其他事務插入記錄造成幻讀，正是由於在執行 INSERT 語句時需要加插入意向鎖，而插入意向鎖和間隙鎖衝突，從而阻止了插入操作的執行。

 

不同類型的鎖之間的相容如下表所示：

  RECORED GAP NEXT-KEY II GAP（插入意向鎖）
RECORED   相容   相容
GAP 相容 相容 相容 相容
NEXT-KEY   相容   相容
II GAP 相容     相容

自增鎖

自增鎖（Auto-inc Locks）：是一種特殊的表級別鎖，專門針對事務插入AUTO_INCREMENT型別的列。如果一個事務正在往表中插入記錄，所有其他事務的插入必須等待，以便第一個事務插入的行，是連續的主鍵值。

 

站內摘來的一張時序圖，幫助理解MySQL的事務和鎖：

擴充套件

死鎖

死鎖（Dead Lock）：當併發系統中不同線程出現回圈資源依賴，涉及的執行緒都在等待別的執行緒釋放資源時，就會導致這幾個執行緒都進入無限等待的狀態，稱爲死鎖。

產生死鎖的4個必要條件：

  • 互斥條件：一個資源每次只能被一個執行緒使用
  • 請求與保持條件：一個執行緒因請求資源而阻塞時，對已獲得的資源保持不放
  • 不剝奪條件：進程已經獲得的資源，在未使用完之前，不能強行剝奪
  • 回圈等待條件：若幹線程之間形成一種頭尾相接的回圈等待資源關係

死鎖檢測：       

對於死鎖，我們可以通過參數 innodb_lock_wait_timeout 根據實際業務場景來設定超時時間，InnoDB引擎預設值是50s。
發起死鎖檢測，發現死鎖後，主動回滾死鎖鏈條中的某一個事務，讓其他事務得以繼續執行。將參數 innodb_deadlock_detect 設定爲 on，表示開啓這個邏輯（預設是開啓狀態）。

wait-for graph 演算法來主動進行死鎖檢測：innodb 還提供了 wait-for graph 演算法來主動進行死鎖檢測，每當加鎖請求無法立即滿足需要並進入等待時，wait-for graph 演算法都會被觸發。

避免死鎖的方法：

  1. 如果不同程式會併發存取多個表，儘量約定以相同的順序存取表，可以大大降低發生死鎖的可能性；
  2. 在同一個事務中，儘可能做到一次鎖定所需要的所有資源，減少死鎖產生概率；
  3. 對於非常容易產生死鎖的業務部分，可以嘗試使用升級鎖定顆粒度，通過表級鎖定來減少死鎖產生的概率。

如何解決熱點行更新導致的效能問題？

  1. 如果你能確保這個業務一定不會出現死鎖，可以臨時把死鎖檢測關閉掉。一般不建議採用；
  2. 控制併發度，對應相同行的更新，在進入引擎之前排隊。這樣在InnoDB內部就不會有大量的死鎖檢測工作了；
  3. 將熱更新的行數據拆分成邏輯上的多行來減少鎖衝突，但是業務複雜度可能會大大提高；

MyISAM 中是不會產生死鎖的，因爲 MyISAM 總是一次性獲得所需的全部鎖，要麼全部滿足，要麼全部等待。

活鎖

活鎖、死鎖本質上是一樣的：都是多個執行緒任務沒有任何進展。原因是在獲取共用資源時，併發多執行緒或多進程宣告資源佔用(即加鎖)的順序有衝突，死鎖是加不上就死等（多執行緒都處於阻塞狀態）；活鎖是加不上就放開已獲得的資源重試，這種情況下執行緒並沒有阻塞所以是活的狀態，但是隻是在做無用功（每次重試都是失敗的），其實多核活鎖不太常見（資源分配充足）。

例：資源A和B，進程P1和P2

start：
P1 lock A
P2 lock B
P1 lock B fail context switch
P2 lock A fail context switch
P1 release A
P2 release B
goto start    #活鎖，不斷嘗試，做無用功

悲觀鎖

顧名思義，就是很悲觀，它對於數據被外界修改持保守態度，認爲數據隨時會修改，所以整個數據處理中需要將數據加鎖。悲觀鎖一般都是依靠關係數據庫提供的鎖機制 機製，事實上關係數據庫中的行鎖，表鎖，讀寫鎖都是悲觀鎖。

樂觀鎖

樂觀鎖一般是指使用者自己實現的一種鎖機制 機製。樂觀鎖是指操作數據庫時(更新操作)，想法很樂觀，認爲這次的操作不會導致衝突，在操作數據時，並不進行任何其他的特殊處理（也就是不加鎖），而在進行更新後，再去判斷是否有衝突了。樂觀鎖適用於多讀的應用型別，這樣可以提高吞吐量。

常見樂觀鎖實現方式：

  • 版本號機制 機製：一般是在數據表中加上一個數據版本號version欄位，表示數據被修改的次數，當數據被修改時，version值會加一。當執行緒A要更新數據值時，在讀取數據的同時也會讀取version值，在提交更新時，若剛纔讀取到的version值爲當前數據庫中的version值相等時才更新，否則重試更新操作，直到更新成功。
  • CAS演算法：比較與交換（compare and swap），是一種很有名的無鎖演算法。無鎖程式設計，即不使用鎖的情況下實現多執行緒之間的變數同步，也就是在沒有執行緒被阻塞的情況下實現變數的同步，所以也叫非阻塞同步（Non-blocking Synchronization）。CAS演算法涉及到三個操作值：
    • 需要讀寫的記憶體值 V
    • 進行比較的值 A
    • 擬寫入的新值 B

當且僅當 V 的值等於 A時，CAS通過原子方式用新值B來更新V的值，否則不會執行任何操作（比較和替換是一個原子操作）。一般情況下是一個自旋操作，即不斷的重試。

注意：樂觀鎖只能用於本系 本係統控制，無法阻止外系統更新；

併發控制（MVCC）

MVCC (multiple-version-concurrency-control）：行級鎖的變種， 在普通讀情況下避免了加鎖操作，因此開銷更低。雖然實現不同，但通常都是實現非阻塞讀，對於寫操作只鎖定必要的行。

  • 一致性讀 （就是讀取快照）select * from table ....

  • 當前讀（就是讀取實際的持久化的數據），特殊的讀操作，插入/更新/刪除操作，屬於當前讀，處理的都是當前的數據，需要加鎖。 select * from table where ... lock in share mode; select * from table where ... for update; insert; update ; delete;

注意：select ...... from table ...... （沒有額外加鎖後綴）使用MVCC，保證了讀快照(MySQL 稱爲 consistent read)，所謂一致性讀或者讀快照就是讀取當前事務開始之前的數據快照，在這個事務開始之後的更新不會被讀到。

InnoDB的 MVCC 通常是通過在每行數據後邊儲存兩個隱藏的列來實現（其實是三列，第三列是用於事務回滾），一個儲存了行的建立版本號，另一個儲存了行的更新版本號（上一次被更新數據的版本號） 這個版本號是每個事務的版本號，遞增的。這樣保證了 InnoDB對讀操作不需要加鎖也能保證正確讀取數據。

在MySQL 預設的 Repeatable Read 隔離級別下， MVCC 的具體操作：

  • Select（快照讀，所謂讀快照就是讀取當前事務之前的數據）：

    a.InnoDB 只 select 查詢版本號早於當前版本號的數據行，這樣保證了讀取的數據要麼是在這個事務開始之前就已經 commit 了的（早於當前版本號），要麼是在這個事務自身中執行建立操作的數據（等於當前版本號）。

    b.查詢行的更新版本號要麼未定義，要麼大於當前的版本號(爲了保證事務可以讀到老數據)，這樣保證了事務讀取到在當前事務開始之後未被更新的數據。

    注意： 這裏的 select 不能有 for update、lock in share 語句。 總之要只返回滿足以下條件的行數據，達到了快照讀的效果：

(行建立版本號< =當前版本號 && (行更新版本號==null or 行更新版本號>當前版本號 ) )
  • Insert：InnoDB爲這個事務中新插入的行，儲存當前事務版本號的行作爲行的行建立版本號。
  • Delete：InnoDB 爲每一個刪除的行儲存當前事務版本號，作爲行的刪除標記。
  • Update：將存在兩條數據，保持當前版本號作爲更新後的數據的新增版本號，同時儲存當前版本號作爲老數據行的更新版本號。
當前版本號—寫—>新數據行建立版本號 && 當前版本號—寫—>老數據更新版本號();

 

希望本文對你有幫助，請點個贊鼓勵一下作者吧~ 謝謝！