MySQL鎖介紹

根據加鎖的范圍，MySQL 里面的鎖大致可以分成全局鎖、表級鎖、行鎖三類。

圖片

全局鎖

顧名思義，全局鎖就是對整個數據庫實例加鎖。MySQL 提供了一個加全局讀鎖的方法，命令是 Flush tables with read lock (FTWRL)。當你需要讓整個庫處于只讀狀態的時候，可以使用這個命令，之后其他線程的以下語句會被阻塞：數據更新語句（數據的增刪改）、數據定義語句（包括建表、修改表結構等）和更新類事務的提交語句。全局鎖的典型使用場景是，做全庫邏輯備份。也就是把整庫每個表都 select 出來存成文本。

官方自帶的邏輯備份工具是 mysqldump。當 mysqldump 使用參數–single-transaction 的時候，導數據之前就會啟動一個事務，來確保拿到一致性視圖。而由于 MVCC 的支持，這個過程中數據是可以正常更新的。

一致性讀是好，但前提是引擎要支持這個隔離級別。比如，對于 MyISAM 這種不支持事務的引擎，如果備份過程中有更新，總是只能取到最新的數據，那么就破壞了備份的一致性。這時，我們就需要使用 FTWRL 命令了。

表鎖

元數據鎖

元數據鎖，即MDL全稱為mysql metadata lock，當表有活動事務的時候，不可以對元數據進行寫入操作。所以說MDL作用是維護表元數據的數據一致性

MDL的作用是確保并發事務之間對數據庫對象的操作不會互相沖突或產生不一致的結果。當一個事務對某個數據庫對象執行了鎖定操作時，其他事務對同一對象的鎖請求會被阻塞，直到持有鎖的事務釋放鎖。

以下是MDL的一些特點和使用場景：

1. 1. 讀-寫沖突：MDL具有讀-寫沖突，即一個事務持有寫鎖時會阻塞其他事務的讀和寫操作。這確保了在寫操作進行期間，其他事務無法讀取或修改受鎖保護的對象。
2. 2. 寫-寫沖突：MDL還存在寫-寫沖突，即一個事務持有寫鎖時會阻塞其他事務的寫操作。這保證了同一時間只能有一個事務對一個對象進行寫操作，避免了并發寫操作引起的數據不一致問題。
3. 3. 共享讀鎖：MDL允許多個事務同時獲取讀鎖，因為讀操作之間不會相互沖突。
4. 4. 鎖的級別：MDL的鎖級別是語句級別的，而不是表級別或行級別的。這意味著對于同一表的不同語句，可以同時持有讀鎖和寫鎖，因為它們不會互相沖突。

自增鎖Auto-inc Locks

是特殊的表級別鎖，專門針對事務插入AUTO_INCREMENT類型的列。

原理：每張表自增長值并不保存在磁盤上進行持久化，而是每次InnoDB存儲引擎啟動時，執行以下操作：

SELECT MAX(auto_inc_col) FROM T FOR UPDATE;

之后得到的值會用變量auto_inc_lock作緩存，插入操作會根據這個自增長值加1賦予自增長列。因為每張表只有同一時刻只能有一個自增鎖，可以避免同一表鎖對象在各個事務中不斷地被申請。

為了提高插入的性能，在MySQL5.1版本之后，對于普通的insert語句，自增鎖每次申請完馬上釋放，不是在一個事務完成后才釋放，以便允許別的事務再次申請。

舉個例子：假設Test表有主鍵id和唯一索引列a，有兩個并行事務A和B，為了避免兩個事務申請到相同的主鍵id，必須要加自增鎖順序申請

事務A申請到主鍵id=1之后釋放自增鎖，沒有等事務A提交之后釋放，所以事務B可以插入不被阻塞。

什么情況自增主鍵不是連續的呢？

• ? 事務回滾，如果在事務中插入了帶有自增主鍵的記錄，但該事務最終被回滾（rollback），則該自增值將被釋放，不會被后續事務使用。這可能導致自增主鍵出現間隔或不連續的情況。
• ? 手動插入了自增主鍵的值，而不是使用系統自動生成的自增值，可能會導致自增主鍵的連續性中斷。例如，使用INSERT語句指定了特定的自增主鍵值。
• ? 特殊的批量插入語句insert...select。

表級共享與排他鎖

• ? 表級共享鎖,又稱為表共享讀鎖，既在表的層級上對數據加以共享鎖，實現讀讀共享
• ? 表級排他鎖,又稱為表獨占寫鎖，既在表的層級上對數據加以排他鎖，實現讀寫互斥，寫寫互斥

表級意向鎖

表級意向鎖（Table-level Intention Lock）是MySQL中一種用于管理表級鎖的機制。它是一種輕量級的鎖，用于指示事務對表的意向操作，即事務打算在表級別上執行讀操作或寫操作。

表級意向鎖分為兩種類型：

1. 1. 意向共享鎖（Intention Shared Lock，IS）：事務打算在表級別上執行讀操作時，會申請意向共享鎖。意向共享鎖不會阻止其他事務獲取表級共享鎖或意向共享鎖，但會阻止事務獲取表級排他鎖。
2. 2. 意向排他鎖（Intention Exclusive Lock，IX）：事務打算在表級別上執行寫操作時，會申請意向排他鎖。意向排他鎖會阻止其他事務獲取表級共享鎖、意向共享鎖或意向排他鎖。

表級意向鎖的作用是協調并發事務對表的鎖定操作，以確保數據一致性和避免死鎖。事務在對表進行鎖定操作之前，首先獲取意向鎖，并根據需要再獲取具體的行級鎖。它們存在的目的是幫助其他事務確定是否可以安全地獲取表級共享鎖或排他鎖，以避免沖突和死鎖的發生。

行鎖

行級共享與排他鎖

因為InnoDB支持表鎖和行鎖。所以在數據庫層次結構的表級和行級，都可以對數據進行鎖定。

• ? 行級共享鎖,行級共享鎖既在行的層級上，對數據加以共享鎖，實現對該行數據的讀讀共享
• ? 行級排他鎖,行級排他鎖既在行的層級上，對數據加以排他鎖，實現對該行數據的讀寫互斥，寫寫互斥

顯式地加共享鎖或排他鎖？

• ? select * from table lock in share mode 為table的所有數據加上共享鎖，既表級共享鎖
• ? select * from table for update 為table的所有數據加上排他鎖，既表級排他鎖
• ? select * from table where id = 1 for update 為table中id為1的那行數據加上排他鎖，既行級排他鎖
• ? select * from table where id = 1 lock in share mode為table中id為1的那行數據加上共享鎖，既行級共享鎖

以上加的是行鎖的前提是，id為主鍵且在查詢命中，否則行鎖會升級為表鎖。共享鎖之間兼容，排它鎖與任何鎖都不兼容

自增鎖、意向鎖和行級鎖的兼容性

自增鎖、意向鎖和行級鎖的兼容性如下：

意向鎖是一個比較弱的鎖，所以意向鎖之間互不排斥

InnoDB鎖算法

記錄鎖Record Locks

單個行記錄上的鎖，用來封鎖索引記錄。

如：假設Test表有主鍵id和唯一索引列a，已經有了(1,1)這條記錄，執行

select * from Test where id=1 for update；

會在id=1的索引記錄上加鎖，以阻止其他事物插入更新、刪除id=1這一行。

間隙鎖Gap Locks

間隙鎖（Gap Lock），它會封鎖索引記錄中的“縫隙”，不讓其他事務在“縫隙”中插入數據。它鎖定的是一個不包含索引本身的開區間范圍 (index1,index2)。間隙鎖是封鎖索引記錄之間的間隙，或者封鎖第一條索引記錄之前的范圍，又或者最后一條索引記錄之后的范圍.鎖定一個范圍，但不包含記錄本身。

封鎖索引記錄中的間隙，確保索引記錄的間隙不變。間隙鎖是針對事務隔離級別為可重復讀(RR)或以上級別而已的，如果隔離級別降級為讀提交(RC)，間隙鎖會自動失效。

MySQL事務實現原理中我們了解到幻讀是指一個事務在前后兩次查詢同一個范圍的時候，后一次查詢看到了前一次查詢沒看到的行，具體例子如下：

CREATE TABLE `t1` (
  `id` int(11) NOT NULL,
  `a` int(11) DEFAULT NULL,
  `b` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `a` (`a`)
) ENGINE=InnoDB;
insert into t values(0,0,5),(5,5,5),(10,10,5);

如果只在 id=5 這一行加鎖，而其他行的不加鎖的話，就會發生以下情況

Q3讀到了id=1這一行，就叫“幻讀”。

如何解決幻讀？

比如繼續使用上面例子的表，執行select * from t1 where b=5 for update時候，由于b沒有索引，就會在插入3個記錄鎖，和4個間隙鎖，這樣就確保了無法再插入新的記錄，以此防止幻讀的發生，如下：

(-∞,0),(0,5),(5,10),(10,+∞)

間隙鎖在往間隙中插入一個記錄才會沖突，間隙鎖之間不存在沖突關系。

臨鍵鎖Next-key Locks

臨鍵鎖是記錄鎖與間隙鎖的組合。

為了避免幻讀，當InnoDB掃描索引記錄的時候，會首先對索引記錄加上行鎖（Record Lock），再對索引記錄兩邊的間隙加上間隙鎖（Gap Lock）

innodb只有在RR隔離級別下、并且參數innodb_locks_unsafe_for_binlog關閉下，才有通過next-key locks來避免幻讀。

如果是RC隔離級別，間隙鎖就會失效，只剩下行鎖部分，而且對于語句執行過程也有優化，使得鎖的范圍也會更小，時間更短，不容易死鎖。

插入意向鎖Insert Intention Locks

是間隙鎖的一種，專門針對insert操作。同一個索引，同一個范圍區間插入記錄，插入的位置不沖突，不會阻塞彼此，可以提高插入并發。

由于插入意向鎖和其他的臨建鎖/間隙鎖本身會沖突，下面的兩個事務會沖突：

圖片

插入意向鎖經常和間隙鎖引發死鎖問題，死鎖是指兩個或者多個事務在同一資源上相互占用，并請求鎖定對方占用的資源，從而導致惡性循環的現象。

簡單模擬一個死鎖的場景：

事務A在等待事務B釋放id=2的行鎖，事務B在等待A釋放id=1的行鎖，事務A和事務B互相等待對方釋放資源，于是進入了死鎖狀態。

插入不會主動加顯示的X Record鎖，只有檢測到Key沖突的時候才會把隱式鎖轉為顯式鎖。

隱式鎖你可以理解為樂觀鎖，也就是正常來說不加鎖或共享鎖，但是遇到沖突則加鎖或升級為排它鎖。顯式鎖，那就是真的鎖上了。

MySQL加鎖規則

林曉斌總結MySQL加鎖規則：包含了兩個“原則”、兩個“優化”和一個“bug”。

? 原則 1：加鎖的基本單位是 next-key lock。next-key lock 是前開后閉區間。

? 原則 2：查找過程中訪問到的對象才會加鎖。

? 優化 1：索引上的等值查詢，給唯一索引加鎖的時候，next-key lock 退化為行鎖。

? 優化 2：索引上的等值查詢，向右遍歷時且最后一個值不滿足等值條件的時候，next-key lock 退化為間隙鎖。

? 一個 bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

下面我們通過例子看一下這些規則：我們建個表，插入一些初始化數據：

CREATE TABLE `t` ( 
    `id` int(11) NOT NULL, 
    `c` int(11) DEFAULT NULL, 
    `d` int(11) DEFAULT NULL, 
    PRIMARY KEY (`id`), 
    KEY `c` (`c`)
) ENGINE=InnoDB;
insert into t values(0,0,0),(5,5,5),(10,10,10),(15,15,15),(20,20,20),(25,25,25);

等值查詢間隙鎖

由于表 t 中沒有 id=7 的記錄，所以用我們上面提到的加鎖規則判斷一下的話：

1. 根據原則 1，加鎖單位是 next-key lock，session A 加鎖范圍就是 (5,10]；

2. 同時根據優化 2，這是一個等值查詢 (id=7)，而 id=10 不滿足查詢條件，next-key lock 退化成間隙鎖，因此最終加鎖的范圍是 (5,10)。

所以，session B 要往這個間隙里面插入 id=8 的記錄會被鎖住，但是 session C 修改 id=10 這行是可以的。

非唯一索引等值鎖

image.png

這里 session A 要給索引 c 上 c=5 的這一行加上讀鎖。

1. 根據原則 1，加鎖單位是 next-key lock，因此會給 (0,5]加上 next-key lock。要注意 c 是普通索引，因此僅訪問 c=5 這一條記錄是不能馬上停下來的，需要向右遍歷，查到 c=10 才放棄。

2. 根據原則 2，訪問到的都要加鎖，因此要給 (5,10]加 next-key lock。

3. 但是同時這個符合優化 2：等值判斷，向右遍歷，最后一個值不滿足 c=5 這個等值條件，因此退化成間隙鎖 (5,10)。

4. 根據原則 2 ，只有訪問到的對象才會加鎖，這個查詢使用覆蓋索引，并不需要訪問主鍵索引，所以主鍵索引上沒有加任何鎖，這就是為什么 session B 的 update 語句可以執行完成。

但 session C 要插入一個 (7,7,7) 的記錄，就會被 session A 的間隙鎖 (5,10) 鎖住。需要注意，在這個例子中，lock in share mode 只鎖覆蓋索引，但是如果是 for update 就不一樣了。執行 for update 時，系統會認為你接下來要更新數據，因此會順便給主鍵索引上滿足條件的行加上行鎖。

主鍵索引范圍鎖

舉例之前，我們先思考一下這個問題：對于我們這個表 t，下面這兩條查詢語句，加鎖范圍相同嗎？

select * from t where id=10 for update;
select * from t where id>=10 and id<11 for update;

你可能會想，id 定義為 int 類型，這兩個語句就是等價的吧？其實，它們并不完全等價。在邏輯上，這兩條查語句肯定是等價的，但是它們的加鎖規則不太一樣?，F在，我們就讓 session A 執行第二個查詢語句，來看看加鎖效果。

圖片

現在我們就用前面提到的加鎖規則，來分析一下 session A 會加什么鎖呢？

1. 開始執行的時候，要找到第一個 id=10 的行，因此本該是 next-key lock(5,10]。根據優化 1， 主鍵 id 上的等值條件，退化成行鎖，只加了 id=10 這一行的行鎖。

2. 范圍查找就往后繼續找，找到 id=15 這一行停下來，因此需要加 next-key lock(10,15]。

所以，session A 這時候鎖的范圍就是主鍵索引上，行鎖 id=10 和 next-key lock(10,15]。

這里需要注意一點，首次 session A 定位查找 id=10 的行的時候，是當做等值查詢來判斷的，而向右掃描到 id=15 的時候，用的是范圍查詢判斷。

非唯一索引范圍鎖

需要注意的是，與主鍵范圍鎖不同的是，下面查詢語句的 where 部分用的是字段 c。

圖片

這次 session A 用字段 c 來判斷，加鎖規則不同是：在第一次用 c=10 定位記錄的時候，索引 c 上加了 (5,10]這個 next-key lock 后，由于索引 c 是非唯一索引，沒有優化規則，也就是說不會蛻變為行鎖，因此最終 sesion A 加的鎖是，索引 c 上的 (5,10] 和 (10,15] 這兩個 next-key lock。

所以從結果上來看，sesson B 要插入（8,8,8) 的這個 insert 語句時就被堵住了。這里需要掃描到 c=15 才停止掃描，是合理的，因為 InnoDB 要掃到 c=15，才知道不需要繼續往后找了。

唯一索引范圍鎖 bug

接下來再看一個關于加鎖規則中 bug 的案例

圖片

session A 是一個范圍查詢，按照原則 1 的話，應該是索引 id 上只加 (10,15]這個 next-key lock，并且因為 id 是唯一鍵，所以循環判斷到 id=15 這一行就應該停止了。但是實際上，InnoDB 會往前掃描到第一個不滿足條件的行為止，也就是 id=20。而且由于這是個范圍掃描，因此索引 id 上的 (15,20]這個 next-key lock 也會被鎖上。所以我們看到了，session B 要更新 id=20 這一行，是會被鎖住的。同樣地，session C 要插入 id=16 的一行，也會被鎖住。照理說，這里鎖住 id=20 這一行的行為，其實是沒有必要的。因為掃描到 id=15，就可以確定不用往后再找了。但實際上還是這么做了，因此認為這是個 bug。

非唯一索引上存在"等值"的例子

插入一條數據

insert into t values(30,10,30);

新插入的這一行 c=10，也就是說現在表里有兩個 c=10 的行。那么，這時候索引 c 上的間隙是什么狀態了呢？由于非唯一索引上包含主鍵的值，所以是不可能存在“相同”的兩行的。

圖片

可以看到，雖然有兩個 c=10，但是它們的主鍵值 id 是不同的（分別是 10 和 30），因此這兩個 c=10 的記錄之間，也是有間隙的。

圖中索引 c 上的主鍵 id。為了跟間隙鎖的開區間形式進行區別，用 (c=10,id=30) 這樣的形式，來表示索引上的一行。

圖片

這時，session A 在遍歷的時候，先訪問第一個 c=10 的記錄。

1. 1. 同樣地，根據原則 1，這里加的是 (c=5,id=5) 到 (c=10,id=10) 這個 next-key lock。然后，session A 向右查找，直到碰到 (c=15,id=15) 這一行，循環才結束。
2. 2. 根據優化 2，這是一個等值查詢，向右查找到了不滿足條件的行，所以會退化成 (c=10,id=10) 到 (c=15,id=15) 的間隙鎖。

也就是說，這個 delete 語句在索引 c 上的加鎖范圍，就是下圖中藍色區域覆蓋的部分。

圖片

limit 語句加鎖

這個例子里，session A 的 delete 語句加了 limit 2。你知道表 t 里 c=10 的記錄其實只有兩條，因此加不加 limit 2，刪除的效果都是一樣的，但是加鎖的效果卻不同?？梢钥吹剑瑂ession B 的 insert 語句執行通過了，跟案例六的結果不同。這是因為，案例七里的 delete 語句明確加了 limit 2 的限制，因此在遍歷到 (c=10, id=30) 這一行之后，滿足條件的語句已經有兩條，循環就結束了。因此，索引 c 上的加鎖范圍就變成了從（c=5,id=5) 到（c=10,id=30) 這個前開后閉區間，如下圖所示：

圖片

可以看到，(c=10,id=30）之后的這個間隙并沒有在加鎖范圍里，因此 insert 語句插入 c=12 是可以執行成功的。這個例子對我們實踐的指導意義就是，在刪除數據的時候盡量加 limit。這樣不僅可以控制刪除數據的條數，讓操作更安全，還可以減小加鎖的范圍。

一個死鎖的例子

next-key lock 實際上是間隙鎖和行鎖加起來的結果。

圖片

現在，我們按時間順序來分析一下為什么是這樣的結果。

1. session A 啟動事務后執行查詢語句加 lock in share mode，在索引 c 上加了 next-key lock(5,10] 和間隙鎖 (10,15)；

2. session B 的 update 語句也要再索引 c 上加 next-key lock(5,10] ，進入鎖等待；

3. 然后 session A 要再插入 (8,8,8) 這一行，被 session B 的間隙鎖鎖住。由于出現了死鎖，InnoDB 讓 session B 回滾。

你可能會問，session B 的 next-key lock 不是還沒申請成功嗎？其實是這樣的，session B 的“加 next-key lock(5,10] ”操作，實際上分成了兩步，先是加 (5,10) 的間隙鎖，加鎖成功；然后加 c=10 的行鎖，這時候才被鎖住的。

也就是說，我們在分析加鎖規則的時候可以用 next-key lock 來分析。但是要知道，具體執行的時候，是要分成間隙鎖和行鎖兩段來執行的。

避免死鎖有哪些方法？

? 以固定的順序訪問表和行。

? 大事務拆小。大事務更容易發生死鎖，如果業務允許，將大事務拆小。

? 在同一個事務中，盡可能做到一次鎖定所需要的所有資源，減少死鎖概率。

? 降低隔離級別。如果業務允許，將隔離級別調低也是較好的選擇，比如將隔離級別從RR調整為RC，可以避免掉很多因為gap鎖造成的死鎖。

? 為表添加合理的索引。