行級鎖

InnoDB 引擎是支持行級鎖的，而 MyISAM 引擎并不支持行級鎖。

顧名思義，行鎖就是針對數(shù)據(jù)表中行記錄的鎖。這很好理解，比如事務(wù) A 更新了一行，而這時候事務(wù) B 也要更新同一行，則必須等事務(wù) A 的操作完成后才能進(jìn)行更新。
前面也提到，普通的 select 語句是不會對記錄加鎖的，因為它屬于快照讀。如果要在查詢時對記錄加行鎖，可以使用下面這兩個方式，這種查詢會加鎖的語句稱為鎖定讀。

//對讀取的記錄加共享鎖
select ... lock in share mode;
//對讀取的記錄加獨(dú)占鎖
select ... for update;

Record Lock

Record Lock 稱為記錄鎖，鎖住的是一條記錄。而且記錄鎖是有 S 鎖和 X 鎖之分的：

• 當(dāng)一個事務(wù)對一條記錄加了 S 型記錄鎖后，其他事務(wù)也可以繼續(xù)對該記錄加 S 型記錄鎖（S 型與 S 鎖兼容），但是不可以對該記錄加 X 型記錄鎖（S 型與 X 鎖不兼容）;
• 當(dāng)一個事務(wù)對一條記錄加了 X 型記錄鎖后，其他事務(wù)既不可以對該記錄加 S 型記錄鎖（S 型與 X 鎖不兼容），也不可以對該記錄加 X 型記錄鎖（X 型與 X 鎖不兼容）。

舉個例子，當(dāng)一個事務(wù)執(zhí)行了下面這條語句：

mysql > begin;
mysql > select * from t_test where id = 1 for update;

就是對 t_test 表中主鍵 id 為 1 的這條記錄加上 X 型的記錄鎖，這樣其他事務(wù)就無法對這條記錄進(jìn)行修改了。

當(dāng)事務(wù)執(zhí)行 commit 后，事務(wù)過程中生成的鎖都會被釋放。

Gap Lock

Gap Lock 稱為間隙鎖，存在于可重復(fù)讀隔離級別和串行化隔離級別，目的是為了解決可重復(fù)讀隔離級別下幻讀的現(xiàn)象。

假設(shè)，表中有一個范圍 id 為（3，5）間隙鎖，那么其他事務(wù)就無法插入 id = 4 這條記錄了，這樣就有效的防止幻讀現(xiàn)象的發(fā)生。

Next-Key Lock

Next-Key Lock 稱為臨鍵鎖，是 Record Lock + Gap Lock 的組合，鎖定一個范圍，并且鎖定記錄本身。

假設(shè)，表中有一個范圍 id 為（3，5] 的 next-key lock，那么其他事務(wù)即不能插入 id = 4 記錄，也不能修改 id = 5 這條記錄。

所以，next-key lock 即能保護(hù)該記錄，又能阻止其他事務(wù)將新紀(jì)錄插入到被保護(hù)記錄前面的間隙中。

next-key lock 是包含間隙鎖+記錄鎖的，如果一個事務(wù)獲取了 X 型的 next-key lock，那么另外一個事務(wù)在獲取相同范圍的 X 型的 next-key lock 時，是會被阻塞的。

select … for update有啥用？我不加for update不行嗎？

可以解決“快照讀”在特定場景下的不足

在 MySQL 的默認(rèn)隔離級別“可重復(fù)讀”下，普通的SELECT語句是“快照讀”。它基于 MVCC 讀取一個歷史快照，不會加鎖。這雖然保證了高并發(fā)下的讀取性能，但在某些業(yè)務(wù)場景下會產(chǎn)生問題。

經(jīng)典場景：庫存扣減

假設(shè)商品 A 的庫存為 1。

1. 事務(wù) A 查詢庫存：SELECT stock FROM products WHERE id = 1;（返回 stock=1）
2. 事務(wù) B 也查詢庫存：SELECT stock FROM products WHERE id = 1;（返回 stock=1）
3. 事務(wù) A 下單，執(zhí)行：UPDATE products SET stock = stock - 1 WHERE id = 1>并提交。
4. 此時庫存已為 0。
5. 事務(wù) B 也執(zhí)行：UPDATE products SET stock = stock - 1 WHERE id = 1并提交。

最終結(jié)果是庫存變成了 -1，這就是典型的“超賣”問題。

使用SELECT … FOR UPDATE解決：

1. 事務(wù) A 查詢并鎖定庫存：SELECT stock FROM products WHERE id = 1 FOR UPDATE;（對 id=1 的記錄加排他鎖）
2. 事務(wù) B 也嘗試執(zhí)行SELECT … FOR UPDATE查詢同一件商品，但會被阻塞，直到事務(wù) A 釋放鎖。
3. 事務(wù) A 完成下單和更新操作，提交事務(wù)，釋放鎖。
4. 事務(wù) B 獲得鎖，執(zhí)行查詢，此時它讀到的 stock 已經(jīng)是事務(wù) A 更新后的 0，因此可以阻止后續(xù)的扣減操作。

MySQL 是怎么加行級鎖的？

加鎖的對象是索引，加鎖的基本單位是 next-key lock。

但是，next-key lock 在一些場景下會退化成記錄鎖或間隙鎖。

那到底是什么場景呢？總結(jié)一句，在能使用記錄鎖或者間隙鎖就能避免幻讀現(xiàn)象的場景下， next-key lock 就會退化成記錄鎖或間隙鎖。

這次會以下面這個表結(jié)構(gòu)來進(jìn)行實驗說明：

CREATE TABLE `user` (
  `id` bigint NOT NULL AUTO_INCREMENT,
  `name` varchar(30) COLLATE utf8mb4_unicode_ci NOT NULL,
  `age` int NOT NULL,
  PRIMARY KEY (`id`),
  KEY `index_age` (`age`) USING BTREE
) ENGINE=InnoDB  DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

其中，id 是主鍵索引（唯一索引），age 是普通索引（非唯一索引），name 是普通的列。

表中的有這些行記錄：

我本篇文章的「唯一索引」是用「主鍵索引」作為案例說明的，加鎖只加在主鍵索引項上。

然后，很多同學(xué)誤以為如果是二級索引的「唯一索引」，加鎖也是只加在二級索引項上。

其實這是不對的，所以這里特此說明下，如果是用二級索引（不管是不是非唯一索引，還是唯一索引）進(jìn)行鎖定讀查詢的時候，除了會對二級索引項加行級鎖（如果是唯一索引的二級索引，加鎖規(guī)則和主鍵索引的案例相同），而且還會對查詢到的記錄的主鍵索引項上加「記錄鎖」。

在文章的「非唯一索引」的案例中，我就是用二級索引作為例子，在后面的章節(jié)我有說明，對二級索引進(jìn)行鎖定讀查詢的時候，因為存在兩個索引（二級索引和主鍵索引），所以兩個索引都會加鎖。

唯一索引（主鍵索引）等值查詢

當(dāng)我們用唯一索引進(jìn)行等值查詢的時候，查詢的記錄存不存在，加鎖的規(guī)則也會不同：

• 當(dāng)查詢的記錄是「存在」的，在索引樹上定位到這一條記錄后，將該記錄的索引中的 next-key lock 會退化成「記錄鎖」。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 1 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飛   |  19 |
+----+--------+-----+
1 row in set (0.02 sec)

接下來，如果有其他事務(wù)，對 id 為 1 的記錄進(jìn)行更新或者刪除操作的話，這些操作都會被阻塞，因為更新或者刪除操作也會對記錄加 X 型的記錄鎖，而 X 鎖和 X 鎖之間是互斥關(guān)系。

• 當(dāng)查詢的記錄是「不存在」的，在索引樹找到第一條大于該查詢記錄的記錄后，將該記錄的索引中的 next-key lock 會退化成「間隙鎖」。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id = 2 for update;
Empty set (0.03 sec)

此時事務(wù) A 在 id = 5 記錄的主鍵索引上加的是間隙鎖，鎖住的范圍是 (1, 5)。

唯一索引（主鍵索引）范圍查詢

實驗一：針對「大于」的范圍查詢的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語句：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id > 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 20 | 香克斯    |  39 |
+----+-----------+-----+
1 row in set (0.01 sec)

• 在 id = 20 這條記錄的主鍵索引上，加了范圍為 (15, 20] 的 next-key 鎖，意味著其他事務(wù)即無法更新或者刪除 id = 20 的記錄，同時無法插入 id 值為 16、17、18、19 的這一些新記錄。
• 在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了范圍為 (20, +∞] 的 next-key 鎖，意味著其他事務(wù)無法插入 id 值大于 20 的這一些新記錄。

實驗二：針對「大于等于」的范圍查詢的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語句：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id >= 15 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 15 | 烏索普    |  20 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.00 sec)

• 在 id = 15 這條記錄的主鍵索引上，加了記錄鎖，范圍是 id = 15 這一行記錄；意味著其他事務(wù)無法更新或者刪除 id = 15 的這一條記錄；
• 在 id = 20 這條記錄的主鍵索引上，加了 next-key 鎖，范圍是 (15, 20] 。意味著其他事務(wù)即無法更新或者刪除 id = 20 的記錄，同時無法插入 id 值為 16、17、18、19 的這一些新記錄。
• 在特殊記錄（ supremum pseudo-record）的主鍵索引上，加了 next-key 鎖，范圍是 (20, +∞] 。意味著其他事務(wù)無法插入 id 值大于 20 的這一些新記錄。

實驗三：針對「小于」的范圍查詢時，查詢條件值的記錄「不存在」表中的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語句，注意查詢條件值的記錄（id 為 6）并不存在于表中。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id < 6 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飛   |  19 |
|  5 | 索隆   |  21 |
+----+--------+-----+
3 rows in set (0.00 sec)

• 在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖，意味著其他事務(wù)即無法更新或者刪除 id = 1 的這一條記錄，同時也無法插入 id 小于 1 的這一些新記錄。
• 在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖，意味著其他事務(wù)即無法更新或者刪除 id = 5 的這一條記錄，同時也無法插入 id 值為 2、3、4 的這一些新記錄。
• 在 id = 10 這條記錄的主鍵索引上，加了范圍為 (5, 10) 的間隙鎖，意味著其他事務(wù)無法插入 id 值為 6、7、8、9 的這一些新記錄。

讀者提問：請教一個問題 在看mysql加行級鎖中 select *from user where id<6 for update; 為什么id(5,10)要加間隙鎖呢？ 不加 也不會發(fā)生幻讀吧 ？

回答：第三個間隙鎖是加在id=10 索引上的，這個例子不太好說明，如果是id<7，id=10索引不加間隙鎖的話，中途插入一個 6，就發(fā)生幻讀了

實驗四：針對「小于等于」的范圍查詢時，查詢條件值的記錄「存在」表中的情況。

假設(shè)事務(wù) A 執(zhí)行了這條范圍查詢語句，注意查詢條件值的記錄（id 為 5）存在于表中。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where id <= 5 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
|  1 | 路飛   |  19 |
|  5 | 索隆   |  21 |
+----+--------+-----+
2 rows in set (0.00 sec)

• 在 id = 1 這條記錄的主鍵索引上，加了范圍為 (-∞, 1] 的 next-key 鎖。意味著其他事務(wù)即無法更新或者刪除 id = 1 的這一條記錄，同時也無法插入 id 小于 1 的這一些新記錄。
• 在 id = 5 這條記錄的主鍵索引上，加了范圍為 (1, 5] 的 next-key 鎖。意味著其他事務(wù)即無法更新或者刪除 id = 5 的這一條記錄，同時也無法插入 id 值為 2、3、4 的這一些新記錄。

非唯一索引等值查詢

當(dāng)我們用非唯一索引進(jìn)行等值查詢的時候，因為存在兩個索引，一個是主鍵索引，一個是非唯一索引（二級索引），所以在加鎖時，同時會對這兩個索引都加鎖，但是對主鍵索引加鎖的時候，只有滿足查詢條件的記錄才會對它們的主鍵索引加鎖。

先說結(jié)論：

• 當(dāng)查詢的記錄「不存在」時，掃描到第一條不符合條件的二級索引記錄，該二級索引的 next-key 鎖會退化成間隙鎖。因為不存在滿足查詢條件的記錄，所以不會對主鍵索引加鎖。
• 當(dāng)查詢的記錄「存在」時，由于不是唯一索引，所以肯定存在索引值相同的記錄，于是非唯一索引等值查詢的過程是一個掃描的過程，直到掃描到第一個不符合條件的二級索引記錄就停止掃描，然后在掃描的過程中，對掃描到的二級索引記錄加的是 next-key 鎖，而對于第一個不符合條件的二級索引記錄，該二級索引的 next-key 鎖會退化成間隙鎖。同時，在符合查詢條件的記錄的主鍵索引上加記錄鎖。

1、記錄不存在的情況

假設(shè)事務(wù) A 對非唯一索引（age）進(jìn)行了等值查詢，且表中不存在 age = 25 的記錄。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 25 for update;
Empty set (0.00 sec)

2、記錄存在的情況

假設(shè)事務(wù) A 對非唯一索引（age）進(jìn)行了等值查詢，且表中存在 age = 22 的記錄。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age = 22 for update;
+----+--------+-----+
| id | name   | age |
+----+--------+-----+
| 10 | 山治   |  22 |
+----+--------+-----+
1 row in set (0.00 sec)

非唯一索引范圍查詢

非唯一索引和主鍵索引的范圍查詢的加鎖也有所不同，不同之處在于非唯一索引范圍查詢，索引的 next-key lock 不會有退化為間隙鎖和記錄鎖的情況，都是加next-key 鎖。

就帶大家簡單分析一下，事務(wù) A 的這條范圍查詢語句：

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from user where age >= 22  for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
| 10 | 山治      |  22 |
| 20 | 香克斯    |  39 |
+----+-----------+-----+
2 rows in set (0.01 sec)

在 age >= 22 的范圍查詢中，明明查詢 age = 22 的記錄存在并且屬于等值查詢，為什么不會像唯一索引那樣，將 age = 22 記錄的二級索引上的 next-key 鎖退化為記錄鎖？

因為 age 字段是非唯一索引，不具有唯一性，所以如果只加記錄鎖（記錄鎖無法防止插入，只能防止刪除或者修改），就會導(dǎo)致其他事務(wù)插入一條 age = 22 的記錄，這樣前后兩次查詢的結(jié)果集就不相同了，出現(xiàn)了幻讀現(xiàn)象。

讀者問題：這里的查詢條件如果是age <= 22，驗證了一下確實都是臨鍵鎖，不過感覺age=39那里加一個間隙鎖就可以避免幻讀了，為什么age=39也要加臨鍵鎖額外的把39也鎖住了。

回答：這里屬于能優(yōu)化，但是 mysql 沒有做這方面的優(yōu)化，具體原因未知，官方文檔也沒解釋，大概率是作者忘記了??

沒有加索引的查詢

當(dāng)前讀查詢語句沒有使用索引列作為查詢條件，或者查詢語句沒有走索引查詢，導(dǎo)致掃描是全表掃描。那么，每一條記錄的索引上都會加 next-key 鎖，這樣就相當(dāng)于鎖住的全表，這時如果其他事務(wù)對該表進(jìn)行增、刪、改操作的時候，都會被阻塞。

不只是當(dāng)前讀查詢語句不加索引才會導(dǎo)致這種情況，update 和 delete 語句如果查詢條件不加索引，那么由于掃描的方式是全表掃描，于是就會對每一條記錄的索引上都會加 next-key 鎖，這樣就相當(dāng)于鎖住的全表

問題：為什么select…for update/ update / delete這些語句的查詢語句沒有走索引就會對每一條記錄的索引上加next-key 鎖（鎖全表）呢？

答案：是為了防止在事務(wù)執(zhí)行期間，其他事務(wù)修改或刪除某一條記錄，或者在其前后插入新記錄，從而出現(xiàn)了不可重復(fù)讀和幻讀的問題。

死鎖

死鎖的發(fā)生

我建了一張訂單表，其中 id 字段為主鍵索引，order_no 字段普通索引，也就是非唯一索引：

CREATE TABLE `t_order` (
  `id` int NOT NULL AUTO_INCREMENT,
  `order_no` int DEFAULT NULL,
  `create_date` datetime DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `index_order` (`order_no`) USING BTREE
) ENGINE=InnoDB ;

然后，先t_order表里現(xiàn)在已經(jīng)有了 6 條記錄：

假設(shè)這時有兩事務(wù)，一個事務(wù)要插入訂單 1007 ，另外一個事務(wù)要插入訂單 1008，因為需要對訂單做冪等性校驗，所以兩個事務(wù)先要查詢該訂單是否存在，不存在才插入記錄，過程如下：

對于A事務(wù)而言，因為表中最大記錄是1006，所以會對 (1006, +∞]加next-key 鎖

對于B事務(wù)而言，也是會對 (1006, +∞]加next-key鎖

兩個事務(wù)在插入的時候都在等待對方事務(wù)的間隙鎖釋放，于是就造成了循環(huán)等待，導(dǎo)致死鎖。

如何避免死鎖？

死鎖的四個必要條件：互斥、占有且等待、不可強(qiáng)占用、循環(huán)等待。只要系統(tǒng)發(fā)生死鎖，這些條件必然成立，但是只要破壞任意一個條件就死鎖就不會成立。

在數(shù)據(jù)庫層面，有兩種策略通過「打破循環(huán)等待條件」來解除死鎖狀態(tài)：

• 設(shè)置事務(wù)等待鎖的超時時間。當(dāng)一個事務(wù)的等待時間超過該值后，就對這個事務(wù)進(jìn)行回滾，于是鎖就釋放了，另一個事務(wù)就可以繼續(xù)執(zhí)行了。在 InnoDB 中，參數(shù)innodb_lock_wait_timeout是用來設(shè)置超時時間的，默認(rèn)值時 50 秒。

當(dāng)發(fā)生超時后，就出現(xiàn)下面這個提示：

• 開啟主動死鎖檢測。主動死鎖檢測在發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈條中的某一個事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行。將參數(shù)innodb_deadlock_detect設(shè)置為 on，表示開啟這個邏輯，默認(rèn)就開啟。

當(dāng)檢測到死鎖后，就會出現(xiàn)下面這個提示：

上面這個兩種策略是「當(dāng)有死鎖發(fā)生時」的避免方式。

我們可以回歸業(yè)務(wù)的角度來預(yù)防死鎖，對訂單做冪等性校驗的目的是為了保證不會出現(xiàn)重復(fù)的訂單，那我們可以直接將 order_no 字段設(shè)置為唯一索引列，利用它的唯一性來保證訂單表不會出現(xiàn)重復(fù)的訂單，不過有一點(diǎn)不好的地方就是在我們插入一個已經(jīng)存在的訂單記錄時就會拋出異常。

到此這篇關(guān)于MySQL一文搞懂行級鎖經(jīng)典版的文章就介紹到這了,更多相關(guān)mysql行級鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論