首頁 > 文章 > 資料庫 > 上篇 MySQL 語句加鎖分析

上篇 MySQL 語句加鎖分析

coldplay.xixi轉載: 2020-12-25 09:22:231869瀏覽

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

語句、鎖定讀取的語句、

語句，我們分別看一下。
普通的SELECT語句的普通的SELECT
語句在：
READ UNCOMMITTED隔離等級下，不加鎖，直接讀取記錄的最新版本，可能發生髒讀、不可重複讀取和幻讀問題。
#在隔離層級下，不加鎖，每次執行普通的
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隱藏列就變成了
的
事務id，之後 T1
中再使用普通的
語句去查詢這條記錄時就可以看到這條記錄了，也就把這條記錄回傳給客戶端了。因為這個特殊現象的存在，你也可以認為
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 <p>这个语句看起来十分简单，但它的执行过程还是有一丢丢小复杂的：</p>

先到聚簇索引中定位到满足number 的第一条记录，也就是<code>number值为1的记录，然后为其加锁。
判断一下该记录是否符合索引条件下推中的条件。

我们前边介绍过一个称之为索引条件下推（ Index Condition Pushdown，简称ICP）的功能，也就是把查询中与被使用索引有关的查询条件下推到存储引擎中判断，而不是返回到server层再判断。不过需要注意的是，索引条件下推只是为了减少回表次数，也就是减少读取完整的聚簇索引记录的次数，从而减少IO操作。而对于聚簇索引而言不需要回表，它本身就包含着全部的列，也起不到减少IO操作的作用，所以设计InnoDB的大叔们规定这个索引条件下推特性只适用于二级索引。也就是说在本例中与被使用索引有关的条件是：number ，而<code>number列又是聚簇索引列，所以本例中并没有符合索引条件下推的查询条件，自然也就不需要判断该记录是否符合索引条件下推中的条件。
判断一下该记录是否符合范围查询的边界条件

因为在本例中是利用主键number进行范围查询，设计InnoDB的大叔规定每从聚簇索引中取出一条记录时都要判断一下该记录是否符合范围查询的边界条件，也就是number 这个条件。如果符合的话将其返回给<code>server层继续处理，否则的话需要释放掉在该记录上加的锁，并给server层返回一个查询完毕的信息。

对于number值为1的记录是符合这个条件的，所以会将其返回到server层继续处理。
将该记录返回到server层继续判断。

server层如果收到存储引擎层提供的查询完毕的信息，就结束查询，否则继续判断那些没有进行索引条件下推的条件，在本例中就是继续判断number 这个条件是否成立。噫，不是在第3步中已经判断过了么，怎么在这又判断一回？是的，设计<code>InnoDB的大叔采用的策略就是这么简单粗暴，把凡是没有经过索引条件下推的条件都需要放到server层再判断一遍。如果该记录符合剩余的条件（没有进行索引条件下推的条件），那么就把它发送给客户端，不然的话需要释放掉在该记录上加的锁。
然后刚刚查询得到的这条记录（也就是number值为1的记录）组成的单向链表继续向后查找，得到了number值为3的记录，然后重复第2，3，4、5这几个步骤。

小贴士：上述步骤是在MySQL 5.7.21这个版本中验证的，不保证其他版本有无出入。

但是这个过程有个问题，就是当找到number值为8的那条记录的时候，还得向后找一条记录（也就是number值为15的记录），在存储引擎读取这条记录的时候，也就是上述的第1步中，就得为这条记录加锁，然后在第3步时，判断该记录不符合number 这个条件，又要释放掉这条记录的锁，这个过程导致<code>number值为15的记录先被加锁，然后把锁释放掉，过程就是这样：

上篇 MySQL 語句加鎖分析

这个过程有意思的一点就是，如果你先在事务T1中执行：

# 事务T1BEGIN;SELECT * FROM hero WHERE number <p>然后再到事务<code>T2</code>中执行：</p><pre class="brush:php;toolbar:false"># 事务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 <p>则会对<code>number</code>值为<code>1</code>、<code>3</code>、<code>8</code>聚簇索引记录以及它们对应的二级索引记录加<code>X型正经记录锁</code>，加锁顺序和上边语句中的加锁顺序类似，都是先对一条聚簇索引记录加锁后，再给对应的二级索引记录加锁。之后会继续对<code>number</code>值为<code>15</code>的聚簇索引记录加锁，但是随后<code>InnoDB</code>存储引擎判断它不符合边界条件，随即会释放掉该聚簇索引记录上的锁（注意这个过程中没有对<code>number</code>值为<code>15</code>的聚簇索引记录对应的二级索引记录加锁）。具体示意图就不画了。</p>

使用DELETE ...来为记录加锁，比方说：

DELETE FROM hero WHERE number >= 8;

和

DELETE FROM hero WHERE number <p>这两个语句的加锁情况和更新带有二级索引列的<code>UPDATE</code>语句一致，就不画图了。</p>

对于使用二级索引进行等值查询的情况

小贴士：在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 <p>这个语句的加锁情况就有点儿有趣了。前边说在使用<code>number 这个条件的语句中，需要把<code>number</code>值为<code>15</code>的记录也加一个锁，之后又判断它不符合边界条件而把锁释放掉。而对于查询条件<code>name 的语句来说，执行该语句需要使用到二级索引，而与二级索引相关的条件是可以使用<code>索引条件下推</code>这个特性的。设计<code>InnoDB</code>的大叔规定，如果一条记录不符合<code>索引条件下推</code>中的条件的话，直接跳到下一条记录（这个过程根本不将其返回到<code>server层</code>），如果这已经是最后一条记录，那么直接向<code>server层</code>报告查询完毕。但是这里头有个问题呀：先对一条记录加了锁，然后再判断该记录是不是符合索引条件下推的条件，如果不符合直接跳到下一条记录或者直接向server层报告查询完毕，这个过程中并没有把那条被加锁的记录上的锁释放掉呀！！！。本例中使用的查询条件是<code>name ，在为<code>name</code>值为<code>'c曹操'</code>的二级索引记录以及它对应的聚簇索引加锁之后，会接着二级索引中的下一条记录，也就是<code>name</code>值为<code>'l刘备'</code>的那条二级索引记录，由于该记录不符合<code>索引条件下推</code>的条件，而且是范围查询的最后一条记录，会直接向<code>server层</code>报告查询完毕，重点是这个过程中并不会释放<code>name</code>值为<code>'l刘备'</code>的二级索引记录上的锁，也就导致了语句执行完毕时的加锁情况如下所示：</code></code></code></p>

上篇 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 <p>我们前边说的<code>索引条件下推</code>这个特性只适用于<code>SELECT</code>语句，也就是说<code>UPDATE</code>语句中无法使用，那么这个语句就会为<code>name</code>值为<code>'c曹操'</code>和<code>'l刘备'</code>的二级索引记录以及它们对应的聚簇索引进行加锁，之后在判断边界条件时发现<code>name</code>值为<code>'l刘备'</code>的二级索引记录不符合<code>name 条件，再把该二级索引记录和对应的聚簇索引记录上的锁释放掉。这个过程如下图所示：</code></p>

上篇 MySQL 語句加鎖分析

使用DELETE ...来为记录加锁，比方说：

DELETE FROM hero WHERE name >= 'c曹操';

和

DELETE FROM hero WHERE name <p>如果这两个语句采用二级索引来进行<code>锁定读</code>，那么它们的加锁情况和更新带有二级索引列的<code>UPDATE</code>语句一致，就不画图了。</p>

全表扫描的情况

比方说：

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语句只会为更新的二级索引列对应的二级索引记录加锁）。

以上是上篇 MySQL 語句加鎖分析的詳細內容。更多資訊請關注PHP中文網其他相關文章！

mysql for 子类 select delete 并发 number

陳述：

本文轉載於：learnku.com。如有侵權，請聯絡admin@php.cn刪除

上一篇：Pinterest MySQL實踐利用分片來解決百億資料的儲存問題下一篇：Pinterest MySQL實踐利用分片來解決百億資料的儲存問題

看更多