久久久久无码专区亚洲av,久久国产乱子精品免费女,久久久久国产一区二区

mysql學習欄目分析mysql語句的加鎖

上篇 MySQL 語句加鎖分析

推薦（免費）：mysql學習（視頻）

事前準備

建立一個存儲三國英雄的hero表：

CREATE TABLE hero (    number INT,    name VARCHAR(100),    country varchar(100),    PRIMARY KEY (number),    KEY idx_name (name)) Engine=InnoDB CHARSET=utf8;

然后向這個表里插入幾條記錄：

INSERT INTO hero VALUES    (1, 'l劉備', '蜀'),    (3, 'z諸葛亮', '蜀'),    (8, 'c曹操', '魏'),    (15, 'x荀彧', '魏'),    (20, 's孫權', '吳');

然后現在hero表就有了兩個索引（一個二級索引，一個聚簇索引），示意圖如下：

上篇 MySQL 語句加鎖分析

語句加鎖分析

其實啊，“XXX語句該加什么鎖”本身就是個偽命題，一條語句需要加的鎖受到很多條件制約，比方說：

事務的隔離級別
語句執行時使用的索引（比如聚簇索引、唯一二級索引、普通二級索引）
查詢條件（比方說=、==等等）
具體執行的語句類型

在繼續詳細分析語句的加鎖過程前，大家一定要有一個全局概念：加鎖只是解決并發事務執行過程中引起的臟寫、臟讀、不可重復讀、幻讀這些問題的一種解決方案（MVCC算是一種解決臟讀、不可重復讀、幻讀這些問題的一種解決方案），一定要意識到加鎖的出發點是為了解決這些問題，不同情景下要解決的問題不一樣，才導致加的鎖不一樣，千萬不要為了加鎖而加鎖，容易把自己繞進去。當然，有時候因為MySQL具體的實現而導致一些情景下的加鎖有些不太好理解，這就得我們死記硬背了～

我們這里把語句分為3種大類：普通的select語句、鎖定讀的語句、INSERT語句，我們分別看一下。

普通的SELECT語句

普通的SELECT語句在：

READ UNCOMMITTED隔離級別下，不加鎖，直接讀取記錄的最新版本，可能發生臟讀、不可重復讀和幻讀問題。
READ COMMITTED隔離級別下，不加鎖，在每次執行普通的SELECT語句時都會生成一個ReadView，這樣解決了臟讀問題，但沒有解決不可重復讀和幻讀問題。
REPEATABLE READ隔離級別下，不加鎖，只在第一次執行普通的SELECT語句時生成一個ReadView，這樣把臟讀、不可重復讀和幻讀問題都解決了。

不過這里有一個小插曲：
```
# 事務T1，REPEATABLE READ隔離級別下 mysql> BEGIN; Query OK, 0 rows affected (0.00 sec) mysql> SELECT * FROM hero WHERE number = 30; Empty set (0.01 sec) # 此時事務T2執行了：INSERT INTO hero VALUES(30, 'g關羽', '魏'); 并提交 mysql> UPDATE hero SET country = '蜀' WHERE number = 30; Query OK, 1 row affected (0.01 sec)Rows matched: 1  Changed: 1  Warnings: 0 mysql> SELECT * FROM hero WHERE number = 30; | number | name    | country | |     30 | g關羽   | 蜀      | 1 row in set (0.01 sec)
```
在REPEATABLE READ隔離級別下，T1第一次執行普通的SELECT語句時生成了一個ReadView，之后T2向hero表中新插入了一條記錄便提交了，ReadView并不能阻止T1執行UPDATE或者delete語句來對改動這個新插入的記錄（因為T2已經提交，改動該記錄并不會造成阻塞），但是這樣一來這條新記錄的trx_id隱藏列就變成了T1的事務id，之后T1中再使用普通的SELECT語句去查詢這條記錄時就可以看到這條記錄了，也就把這條記錄返回給客戶端了。因為這個特殊現象的存在，你也可以認為InnoDB中的MVCC并不能完完全全的禁止幻讀。
SERIALIZABLE隔離級別下，需要分為兩種情況討論：
在系統變量autocommit=0時，也就是禁用自動提交時，普通的SELECT語句會被轉為SELECT … LOCK IN SHARE MODE這樣的語句，也就是在讀取記錄前需要先獲得記錄的S鎖，具體的加鎖情況和REPEATABLE READ隔離級別下一樣，我們后邊再分析。
在系統變量autocommit=1時，也就是啟用自動提交時，普通的SELECT語句并不加鎖，只是利用MVCC來生成一個ReadView去讀取記錄。

為啥不加鎖呢？因為啟用自動提交意味著一個事務中只包含一條語句，一條語句也就沒有啥不可重復讀、幻讀這樣的問題了。

鎖定讀的語句

我們把下邊四種語句放到一起討論：

語句一：SELECT … LOCK IN SHARE MODE;
語句二：SELECT … for UPDATE;
語句三：UPDATE …
語句四：DELETE …

我們說語句一和語句二是MySQL中規定的兩種鎖定讀的語法格式，而語句三和語句四由于在執行過程需要首先定位到被改動的記錄并給記錄加鎖，也可以被認為是一種鎖定讀。

READ UNCOMMITTED/READ COMMITTED隔離級別下

在READ UNCOMMITTED下語句的加鎖方式和READ COMMITTED隔離級別下語句的加鎖方式基本一致，所以就放到一塊兒說了。值得注意的是，采用加鎖方式解決并發事務帶來的問題時，其實臟讀和不可重復讀在任何一個隔離級別下都不會發生（因為讀-寫操作需要排隊進行）。

對于使用主鍵進行等值查詢的情況

使用SELECT … LOCK IN SHARE MODE來為記錄加鎖，比方說：
```
SELECT * FROM hero WHERE number = 8 LOCK IN SHARE MODE;
```
這個語句執行時只需要訪問一下聚簇索引中number值為8的記錄，所以只需要給它加一個S型正經記錄鎖就好了，如圖所示：

上篇 MySQL 語句加鎖分析

使用SELECT … FOR UPDATE來為記錄加鎖，比方說：
```
SELECT * FROM hero WHERE number = 8 FOR UPDATE;
```
這個語句執行時只需要訪問一下聚簇索引中number值為8的記錄，所以只需要給它加一個X型正經記錄鎖就好了，如圖所示：

上篇 MySQL 語句加鎖分析

小貼士：為了區分S鎖和X鎖，我們之后在示意圖中就把加了S鎖的記錄染成藍色，把加了X鎖的記錄染成紫色。
使用UPDATE …來為記錄加鎖，比方說：
```
UPDATE hero SET country = '漢' WHERE number = 8;
```
這條UPDATE語句并沒有更新二級索引列，加鎖方式和上邊所說的SELECT … FOR UPDATE語句一致。

如果UPDATE語句中更新了二級索引列，比方說：
```
UPDATE hero SET name = 'cao曹操' WHERE number = 8;
```
該語句的實際執行步驟是首先更新對應的number值為8的聚簇索引記錄，再更新對應的二級索引記錄，所以加鎖的步驟就是：

為number值為8的聚簇索引記錄加上X型正經記錄鎖（該記錄對應的）。
為該聚簇索引記錄對應的idx_name二級索引記錄（也就是name值為’c曹操’，number值為8的那條二級索引記錄）加上X型正經記錄鎖。

畫個圖就是這樣：

上篇 MySQL 語句加鎖分析

小貼士：我們用帶圓圈的數字來表示為各條記錄加鎖的順序。

使用DELETE …來為記錄加鎖，比方說：
```
DELETE FROM hero WHERE number = 8;
```
我們平時所說的“DELETE表中的一條記錄”其實意味著對聚簇索引和所有的二級索引中對應的記錄做DELETE操作，本例子中就是要先把number值為8的聚簇索引記錄執行DELETE操作，然后把對應的idx_name二級索引記錄刪除，所以加鎖的步驟和上邊更新帶有二級索引列的UPDATE語句一致，就不畫圖了。

對于使用主鍵進行范圍查詢的情況

使用SELECT … LOCK IN SHARE MODE來為記錄加鎖，比方說：
```
SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;
```
這個語句看起來十分簡單，但它的執行過程還是有一丟丟小復雜的：

先到聚簇索引中定位到滿足number
判斷一下該記錄是否符合索引條件下推中的條件。

我們前邊介紹過一個稱之為索引條件下推（ Index Condition Pushdown，簡稱ICP）的功能，也就是把查詢中與被使用索引有關的查詢條件下推到存儲引擎中判斷，而不是返回到server層再判斷。不過需要注意的是，索引條件下推只是為了減少回表次數，也就是減少讀取完整的聚簇索引記錄的次數，從而減少IO操作。而對于聚簇索引而言不需要回表，它本身就包含著全部的列，也起不到減少IO操作的作用，所以設計InnoDB的大叔們規定這個索引條件下推特性只適用于二級索引。也就是說在本例中與被使用索引有關的條件是：number
判斷一下該記錄是否符合范圍查詢的邊界條件

因為在本例中是利用主鍵number進行范圍查詢，設計InnoDB的大叔規定每從聚簇索引中取出一條記錄時都要判斷一下該記錄是否符合范圍查詢的邊界條件，也就是number

對于number值為1的記錄是符合這個條件的，所以會將其返回到server層繼續處理。
將該記錄返回到server層繼續判斷。

server層如果收到存儲引擎層提供的查詢完畢的信息，就結束查詢，否則繼續判斷那些沒有進行索引條件下推的條件，在本例中就是繼續判斷number
然后剛剛查詢得到的這條記錄（也就是number值為1的記錄）組成的單向鏈表繼續向后查找，得到了number值為3的記錄，然后重復第2，3，4、5這幾個步驟。

小貼士：上述步驟是在MySQL 5.7.21這個版本中驗證的，不保證其他版本有無出入。

但是這個過程有個問題，就是當找到number值為8的那條記錄的時候，還得向后找一條記錄（也就是number值為15的記錄），在存儲引擎讀取這條記錄的時候，也就是上述的第1步中，就得為這條記錄加鎖，然后在第3步時，判斷該記錄不符合number

上篇 MySQL 語句加鎖分析

這個過程有意思的一點就是，如果你先在事務T1中執行：

# 事務T1BEGIN;SELECT * FROM hero WHERE number <= 8 LOCK IN SHARE MODE;

然后再到事務T2中執行：

# 事務T2BEGIN;SELECT * FROM hero WHERE number = 15 FOR UPDATE;

是沒有問題的，因為在T2執行時，事務T1已經釋放掉了number值為15的記錄的鎖，但是如果你先執行T2，再執行T1，由于T2已經持有了number值為15的記錄的鎖，事務T1將因為獲取不到這個鎖而等待。

我們再看一個使用主鍵進行范圍查詢的例子：

SELECT * FROM hero WHERE number >= 8 LOCK IN SHARE MODE;

這個語句的執行過程其實和我們舉的上一個例子類似。也是先到聚簇索引中定位到滿足number >= 8這個條件的第一條記錄，也就是number值為8的記錄，然后就可以沿著由記錄組成的單向鏈表一路向后找，每找到一條記錄，就會為其加上鎖，然后判斷該記錄符不符合范圍查詢的邊界條件，不過這里的邊界條件比較特殊：number >= 8，只要記錄不小于8就算符合邊界條件，所以判斷和沒判斷是一樣一樣的。最后把這條記錄返回給server層，server層再判斷number >= 8這個條件是否成立，如果成立的話就發送給客戶端，否則的話就結束查詢。不過InnoDB存儲引擎找到索引中的最后一條記錄，也就是Supremum偽記錄之后，在存儲引擎內部就可以立即判斷這是一條偽記錄，不必要返回給server層處理，也沒必要給它也加上鎖（也就是說在第1步中就壓根兒沒給這條記錄加鎖）。整個過程會給number值為8、15、20這三條記錄加上S型正經記錄鎖，畫個圖表示一下就是這樣：

上篇 MySQL 語句加鎖分析

使用SELECT … FOR UPDATE語句來為記錄加鎖：

和SELECT … FOR UPDATE語句類似，只不過加的是X型正經記錄鎖。
使用UPDATE …來為記錄加鎖，比方說：
```
UPDATE hero SET country = '漢' WHERE number >= 8;
```
這條UPDATE語句并沒有更新二級索引列，加鎖方式和上邊所說的SELECT … FOR UPDATE語句一致。

如果UPDATE語句中更新了二級索引列，比方說：
```
UPDATE hero SET name = 'cao曹操' WHERE number >= 8;
```
這時候會首先更新聚簇索引記錄，再更新對應的二級索引記錄，所以加鎖的步驟就是：

為number值為8的聚簇索引記錄加上X型正經記錄鎖。
然后為上一步中的記錄索引記錄對應的idx_name二級索引記錄加上X型正經記錄鎖。
為number值為15的聚簇索引記錄加上X型正經記錄鎖。
然后為上一步中的記錄索引記錄對應的idx_name二級索引記錄加上X型正經記錄鎖。
為number值為20的聚簇索引記錄加上X型正經記錄鎖。
然后為上一步中的記錄索引記錄對應的idx_name二級索引記錄加上X型正經記錄鎖。

畫個圖就是這樣：

上篇 MySQL 語句加鎖分析

如果是下邊這個語句：

UPDATE hero SET namey = '漢' WHERE number <= 8;

則會對number值為1、3、8聚簇索引記錄以及它們對應的二級索引記錄加X型正經記錄鎖，加鎖順序和上邊語句中的加鎖順序類似，都是先對一條聚簇索引記錄加鎖后，再給對應的二級索引記錄加鎖。之后會繼續對number值為15的聚簇索引記錄加鎖，但是隨后InnoDB存儲引擎判斷它不符合邊界條件，隨即會釋放掉該聚簇索引記錄上的鎖（注意這個過程中沒有對number值為15的聚簇索引記錄對應的二級索引記錄加鎖）。具體示意圖就不畫了。

使用DELETE …來為記錄加鎖，比方說：
```
DELETE FROM hero WHERE number >= 8;
```
和
```
DELETE FROM hero WHERE number <= 8;
```
這兩個語句的加鎖情況和更新帶有二級索引列的UPDATE語句一致，就不畫圖了。

對于使用二級索引進行等值查詢的情況

小貼士：在READ UNCOMMITTED和READ COMMITTED隔離級別下，使用普通的二級索引和唯一二級索引進行加鎖的過程是一樣的，所以我們也就不分開討論了。

使用SELECT … LOCK IN SHARE MODE來為記錄加鎖，比方說：
```
SELECT * FROM hero WHERE name = 'c曹操' LOCK IN SHARE MODE;
```
這個語句的執行過程是先通過二級索引idx_name定位到滿足name = ‘c曹操’條件的二級索引記錄，然后進行回表操作。所以先要對二級索引記錄加S型正經記錄鎖，然后再給對應的聚簇索引記錄加S型正經記錄鎖，示意圖如下：

上篇 MySQL 語句加鎖分析

這里需要再次強調一下這個語句的加鎖順序：

先對name列為’c曹操’二級索引記錄進行加鎖。
再對相應的聚簇索引記錄進行加鎖

小貼士：我們知道idx_name是一個普通的二級索引，到idx_name索引中定位到滿足name= ‘c曹操’這個條件的第一條記錄后，就可以沿著這條記錄一路向后找。可是從我們上邊的描述中可以看出來，并沒有對下一條二級索引記錄進行加鎖，這是為什么呢？這是因為設計InnoDB的大叔對等值匹配的條件有特殊處理，他們規定在InnoDB存儲引擎層查找到當前記錄的下一條記錄時，在對其加鎖前就直接判斷該記錄是否滿足等值匹配的條件，如果不滿足直接返回（也就是不加鎖了），否則的話需要將其加鎖后再返回給server層。所以這里也就不需要對下一條二級索引記錄進行加鎖了。

現在要介紹一個非常有趣的事情，我們假設上邊這個語句在事務T1中運行，然后事務T2中運行下邊一個我們之前介紹過的語句：

UPDATE hero SET name = '曹操' WHERE number = 8;

這兩個語句都是要對number值為8的聚簇索引記錄和對應的二級索引記錄加鎖，但是不同點是加鎖的順序不一樣。這個UPDATE語句是先對聚簇索引記錄進行加鎖，后對二級索引記錄進行加鎖，如果在不同事務中運行上述兩個語句，可能發生一種賊奇妙的事情 ——

事務T2持有了聚簇索引記錄的鎖，事務T1持有了二級索引記錄的鎖。
事務T2在等待獲取二級索引記錄上的鎖，事務T1在等待獲取聚簇索引記錄上的鎖。

兩個事務都分別持有一個鎖，而且都在等待對方已經持有的那個鎖，這種情況就是所謂的死鎖，兩個事務都無法運行下去，必須選擇一個進行回滾，對性能影響比較大。

使用SELECT … FOR UPDATE語句時，比如：
```
SELECT * FROM hero WHERE name = 'c曹操' FOR UPDATE;
```
這種情況下與SELECT … LOCK IN SHARE MODE語句的加鎖情況類似，都是給訪問到的二級索引記錄和對應的聚簇索引記錄加鎖，只不過加的是X型正經記錄鎖罷了。
使用UPDATE …來為記錄加鎖，比方說：

與更新二級索引記錄的SELECT … FOR UPDATE的加鎖情況類似，不過如果被更新的列中還有別的二級索引列的話，對應的二級索引記錄也會被加鎖。
使用DELETE …來為記錄加鎖，比方說：

與SELECT … FOR UPDATE的加鎖情況類似，不過如果表中還有別的二級索引列的話，對應的二級索引記錄也會被加鎖。

對于使用二級索引進行范圍查詢的情況

使用SELECT … LOCK IN SHARE MODE來為記錄加鎖，比方說：
```
SELECT * FROM hero FORCE INDEX(idx_name)  WHERE name >= 'c曹操' LOCK IN SHARE MODE;
```
小貼士：因為優化器會計算使用二級索引進行查詢的成本，在成本較大時可能選擇以全表掃描的方式來執行查詢，所以我們這里使用FORCE INDEX(idx_name)來強制使用二級索引idx_name來執行查詢。

這個語句的執行過程其實是先到二級索引中定位到滿足name >= ‘c曹操’的第一條記錄，也就是name值為c曹操的記錄，然后就可以沿著這條記錄的鏈表一路向后找，從二級索引idx_name的示意圖中可以看出，所有的用戶記錄都滿足name >= ‘c曹操’的這個條件，所以所有的二級索引記錄都會被加S型正經記錄鎖，它們對應的聚簇索引記錄也會被加S型正經記錄鎖。不過需要注意一下加鎖順序，對一條二級索引記錄加鎖完后，會接著對它相應的聚簇索引記錄加鎖，完后才會對下一條二級索引記錄進行加鎖，以此類推～畫個圖表示一下就是這樣：

上篇 MySQL 語句加鎖分析

再來看下邊這個語句：
```
SELECT * FROM hero FORCE INDEX(idx_name) WHERE name <= 'c曹操' LOCK IN SHARE MODE;
```
這個語句的加鎖情況就有點兒有趣了。前邊說在使用number

上篇 MySQL 語句加鎖分析

這樣子會造成一個尷尬情況，假如T1執行了上述語句并且尚未提交，T2再執行這個語句：
```
SELECT * FROM hero WHERE name = 'l劉備' FOR UPDATE;
```
T2中的語句需要獲取name值為l劉備的二級索引記錄上的X型正經記錄鎖，而T1中仍然持有name值為l劉備的二級索引記錄上的S型正經記錄鎖，這就造成了T2獲取不到鎖而進入等待狀態。

小貼士：為啥不能釋放不符合索引條件下推中的條件的二級索引記錄上的鎖呢？這個問題我也沒想明白，人家就是這么規定的，如果有明白的小伙伴可以加我微信 xiaohaizi4919 來討論一下哈～再強調一下，我使用的MySQL版本是5.7.21，不保證其他版本中的加鎖情景是否完全一致。
使用SELECT … FOR UPDATE語句時：

和SELECT … FOR UPDATE語句類似，只不過加的是X型正經記錄鎖。
使用UPDATE …來為記錄加鎖，比方說：
```
UPDATE hero SET country = '漢' WHERE name >= 'c曹操';
```
小貼士： FORCE INDEX只對SELECT語句起作用，UPDATE語句雖然支持該語法，但實質上不起作用，DELETE語句壓根兒不支持該語法。

假設該語句執行時使用了idx_name二級索引來進行鎖定讀，那么它的加鎖方式和上邊所說的SELECT … FOR UPDATE語句一致。如果有其他二級索引列也被更新，那么也會為對應的二級索引記錄進行加鎖，就不贅述了。不過還有一個有趣的情況，比方說：
```
UPDATE hero SET country = '漢' WHERE name <= 'c曹操';
```
我們前邊說的索引條件下推這個特性只適用于SELECT語句，也就是說UPDATE語句中無法使用，那么這個語句就會為name值為’c曹操’和’l劉備’的二級索引記錄以及它們對應的聚簇索引進行加鎖，之后在判斷邊界條件時發現name值為’l劉備’的二級索引記錄不符合name

上篇 MySQL 語句加鎖分析

使用DELETE …來為記錄加鎖，比方說：
```
DELETE FROM hero WHERE name >= 'c曹操';
```
和
```
DELETE FROM hero WHERE name <= 'c曹操';
```
如果這兩個語句采用二級索引來進行鎖定讀，那么它們的加鎖情況和更新帶有二級索引列的UPDATE語句一致，就不畫圖了。

全表掃描的情況

比方說：

SELECT * FROM hero WHERE country  = '魏' LOCK IN SHARE MODE;

由于country列上未建索引，所以只能采用全表掃描的方式來執行這條查詢語句，存儲引擎每讀取一條聚簇索引記錄，就會為這條記錄加鎖一個S型正常記錄鎖，然后返回給server層，如果server層判斷country = ‘魏’這個條件是否成立，如果成立則將其發送給客戶端，否則會釋放掉該記錄上的鎖，畫個圖就像這樣：

上篇 MySQL 語句加鎖分析

使用SELECT … FOR UPDATE進行加鎖的情況與上邊類似，只不過加的是X型正經記錄鎖，就不贅述了。

對于UPDATE …和DELETE …的語句來說，在遍歷聚簇索引中的記錄，都會為該聚簇索引記錄加上X型正經記錄鎖，然后：

如果該聚簇索引記錄不滿足條件，直接把該記錄上的鎖釋放掉。
如果該聚簇索引記錄滿足條件，則會對相應的二級索引記錄加上X型正經記錄鎖（DELETE語句會對所有二級索引列加鎖，UPDATE語句只會為更新的二級索引列對應的二級索引記錄加鎖）。? ? ? ? ? ? ? ? ? ? ? ? ? ?? ? ? ? ? ? ? ? ? ? ? ? ? ? ?

文章版權歸作者所有，未經允許請勿轉載。

THE END

數據庫
# mysql # 并發 # select # for # delete # 子類 # number