MySQL鎖介紹

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

圖片

全局鎖

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

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

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

表鎖

元數(shù)據(jù)鎖

元數(shù)據(jù)鎖，即MDL全稱為mysql metadata lock，當(dāng)表有活動事務(wù)的時候，不可以對元數(shù)據(jù)進(jìn)行寫入操作。所以說MDL作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性

MDL的作用是確保并發(fā)事務(wù)之間對數(shù)據(jù)庫對象的操作不會互相沖突或產(chǎn)生不一致的結(jié)果。當(dāng)一個事務(wù)對某個數(shù)據(jù)庫對象執(zhí)行了鎖定操作時，其他事務(wù)對同一對象的鎖請求會被阻塞，直到持有鎖的事務(wù)釋放鎖。

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

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

自增鎖Auto-inc Locks

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

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

SELECT MAX(auto_inc_col) FROM T FOR UPDATE;

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

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

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

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

什么情況自增主鍵不是連續(xù)的呢？

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

表級共享與排他鎖

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

表級意向鎖

表級意向鎖（Table-level Intention Lock）是MySQL中一種用于管理表級鎖的機(jī)制。它是一種輕量級的鎖，用于指示事務(wù)對表的意向操作，即事務(wù)打算在表級別上執(zhí)行讀操作或?qū)懖僮鳌?/p>

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

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

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

行鎖

行級共享與排他鎖

因為InnoDB支持表鎖和行鎖。所以在數(shù)據(jù)庫層次結(jié)構(gòu)的表級和行級，都可以對數(shù)據(jù)進(jìn)行鎖定。

• ? 行級共享鎖,行級共享鎖既在行的層級上，對數(shù)據(jù)加以共享鎖，實現(xiàn)對該行數(shù)據(jù)的讀讀共享
• ? 行級排他鎖,行級排他鎖既在行的層級上，對數(shù)據(jù)加以排他鎖，實現(xiàn)對該行數(shù)據(jù)的讀寫互斥，寫寫互斥

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

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

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

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

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

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

InnoDB鎖算法

記錄鎖Record Locks

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

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

select * from Test where id=1 for update；

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

間隙鎖Gap Locks

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

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

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

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 這一行加鎖，而其他行的不加鎖的話，就會發(fā)生以下情況

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

如何解決幻讀？

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

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

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

臨鍵鎖Next-key Locks

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

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

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

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

插入意向鎖Insert Intention Locks

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

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

圖片

插入意向鎖經(jīng)常和間隙鎖引發(fā)死鎖問題，死鎖是指兩個或者多個事務(wù)在同一資源上相互占用，并請求鎖定對方占用的資源，從而導(dǎo)致惡性循環(huán)的現(xiàn)象。

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

事務(wù)A在等待事務(wù)B釋放id=2的行鎖，事務(wù)B在等待A釋放id=1的行鎖，事務(wù)A和事務(wù)B互相等待對方釋放資源，于是進(jìn)入了死鎖狀態(tài)。

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

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

MySQL加鎖規(guī)則

林曉斌總結(jié)MySQL加鎖規(guī)則：包含了兩個“原則”、兩個“優(yōu)化”和一個“bug”。

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

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

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

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

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

下面我們通過例子看一下這些規(guī)則：我們建個表，插入一些初始化數(shù)據(jù)：

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 的記錄，所以用我們上面提到的加鎖規(guī)則判斷一下的話：

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

2. 同時根據(jù)優(yōu)化 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. 根據(jù)原則 1，加鎖單位是 next-key lock，因此會給 (0,5]加上 next-key lock。要注意 c 是普通索引，因此僅訪問 c=5 這一條記錄是不能馬上停下來的，需要向右遍歷，查到 c=10 才放棄。

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

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

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

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

主鍵索引范圍鎖

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

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

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

圖片

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

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

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

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

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

非唯一索引范圍鎖

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

圖片

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

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

唯一索引范圍鎖 bug

接下來再看一個關(guān)于加鎖規(guī)則中 bug 的案例

圖片

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

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

插入一條數(shù)據(jù)

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

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

圖片

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

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

圖片

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

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

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

圖片

limit 語句加鎖

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

圖片

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

一個死鎖的例子

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

圖片

現(xiàn)在，我們按時間順序來分析一下為什么是這樣的結(jié)果。

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

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

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

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

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

避免死鎖有哪些方法？

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

? 大事務(wù)拆小。大事務(wù)更容易發(fā)生死鎖，如果業(yè)務(wù)允許，將大事務(wù)拆小。

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

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

? 為表添加合理的索引。