日韩无码专区无码一级三级片|91人人爱网站中日韩无码电影|厨房大战丰满熟妇|AV高清无码在线免费观看|另类AV日韩少妇熟女|中文日本大黄一级黄色片|色情在线视频免费|亚洲成人特黄a片|黄片wwwav色图欧美|欧亚乱色一区二区三区

RELATEED CONSULTING
相關(guān)咨詢
選擇下列產(chǎn)品馬上在線溝通
服務(wù)時(shí)間：8:30-17:00
你可能遇到了下面的問(wèn)題
關(guān)閉右側(cè)工具欄

新聞中心

這里有您想知道的互聯(lián)網(wǎng)營(yíng)銷解決方案
selectforupdate行鎖or表鎖，20個(gè)場(chǎng)景分析，還真得看情況

?背景

看到許多寫select for update是行鎖還是表鎖的文章，但每篇文章的結(jié)論好像都不太一樣。同時(shí)，是行鎖還是表鎖的問(wèn)題直接影響著系統(tǒng)的性能，所以特意為大家調(diào)研一番，也就有了本篇文章，一共為大家匯總驗(yàn)證了20個(gè)場(chǎng)景下的結(jié)論。

創(chuàng)新互聯(lián)專注于企業(yè)成都營(yíng)銷網(wǎng)站建設(shè)、網(wǎng)站重做改版、雙江網(wǎng)站定制設(shè)計(jì)、自適應(yīng)品牌網(wǎng)站建設(shè)、H5建站、商城系統(tǒng)網(wǎng)站開發(fā)、集團(tuán)公司官網(wǎng)建設(shè)、外貿(mào)營(yíng)銷網(wǎng)站建設(shè)、高端網(wǎng)站制作、響應(yīng)式網(wǎng)頁(yè)設(shè)計(jì)等建站業(yè)務(wù)，價(jià)格優(yōu)惠性價(jià)比高，為雙江等各大城市提供網(wǎng)站開發(fā)制作服務(wù)。

對(duì)于軟件或框架來(lái)說(shuō)，特別是在有大版本更新的情況下，脫離了具體版本的結(jié)論往往是無(wú)意義的。針對(duì)這個(gè)問(wèn)題，網(wǎng)絡(luò)上之所以有多個(gè)版本的答案，最主要的原因就是脫離MySQL的版本以及事務(wù)隔離級(jí)別。

本文就基于兩個(gè)MySQL版本（5.7.x、8.0.x）、兩種常見事務(wù)隔離級(jí)別（讀已提交、可重復(fù)讀）來(lái)逐一驗(yàn)證?？偣灿兴拇箢惽闆r，20個(gè)小場(chǎng)景。最后，再給大家匯總一個(gè)結(jié)論性的驗(yàn)證結(jié)果。大家可以收藏，已備用到時(shí)查閱對(duì)照。

通過(guò)閱讀本文，你不僅能能夠?qū)W到相關(guān)的結(jié)論，同時(shí)也提供了一套科學(xué)的實(shí)驗(yàn)方法論，個(gè)人覺(jué)得后者對(duì)大家來(lái)說(shuō)更為重要。

環(huán)境準(zhǔn)備

在驗(yàn)證之前，我們先準(zhǔn)備好具體的環(huán)境和數(shù)據(jù)。

建表語(yǔ)句：

CREATE TABLE `user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `user_no` varchar(16) DEFAULT NULL COMMENT '用戶編號(hào)',
  `user_name` varchar(16) DEFAULT NULL COMMENT '用戶名',
  `age` int(3) DEFAULT NULL COMMENT '年齡',
  `address` varchar(128) DEFAULT NULL COMMENT '地址',
  PRIMARY KEY (`id`),
  UNIQUE KEY `un_idx_user_no` (`user_no`),
  KEY `idx_user_name` (`user_name`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8;

初始化數(shù)據(jù)：

insert into user values(null,'0001','user01',18,'北京');
insert into user values(null,'0002','user02',19,'上海');
insert into user values(null,'0003','user03',20,'廣州');
insert into user values(null,'0004','user04',21,'深圳');
insert into user values(null,'0005','user05',22,'杭州');

數(shù)據(jù)庫(kù)版本：

版本一：
>select @@version;
5.7.22

版本二：
>select @@version;
8.0.18

查詢數(shù)據(jù)事務(wù)隔離級(jí)別：

>select @@transaction_isolation;
REPEATABLE-READ

MySQL innodb支持的四種事務(wù)隔離級(jí)別：

  • READ_UNCOMMITTED：讀未提交；
  • READ_COMMITTED：讀已提交，后文簡(jiǎn)稱為RC；
  • REPEATABLE_READ：可重復(fù)讀，MySQL默認(rèn)的事務(wù)隔離級(jí)別。后文簡(jiǎn)稱為RR；
  • SERIALIZABLE：串行讀；

設(shè)置全局隔離級(jí)別：

set global transaction isolation level REPEATABLE READ;
set global transaction isolation level READ COMMITTED;

設(shè)置會(huì)話隔離級(jí)別：

set session transaction isolation level REPEATABLE READ;
set session transaction isolation level READ COMMITTED;

關(guān)閉自動(dòng)提交：

> set @@autocommit=0;  //設(shè)置自動(dòng)提交關(guān)閉

在執(zhí)行完鎖語(yǔ)句之后，可執(zhí)行commit命令進(jìn)行事務(wù)提交。

commit;

準(zhǔn)備完以上數(shù)據(jù)，便可以開始每一個(gè)場(chǎng)景的驗(yàn)證了。每個(gè)場(chǎng)景都起了一個(gè)編號(hào)，比如：V5.x-RR-主鍵，表示在MySQL 5.7.x，事務(wù)隔離級(jí)別為RR（可重復(fù)讀），條件字段為主鍵的場(chǎng)景下進(jìn)行的實(shí)驗(yàn)。

場(chǎng)景1.1：V5.x-RR-主鍵

操作：使用主鍵ID作為條件查詢，然后新開啟一個(gè)事務(wù)去更新數(shù)據(jù)。

分析思路：一，如果更新數(shù)據(jù)被阻塞，則說(shuō)明加鎖成功；二，如果更新其他數(shù)據(jù)成功，則說(shuō)明是行鎖，如果更新其他數(shù)據(jù)失敗則說(shuō)明是表鎖。三，部分場(chǎng)景會(huì)測(cè)試插入操作；后續(xù)所有操作基本雷同。

執(zhí)行悲觀鎖查詢：

select * from user where id = 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

在此場(chǎng)景下，來(lái)看一下數(shù)據(jù)庫(kù)加的什么鎖。

當(dāng)?shù)诙l語(yǔ)句被阻塞時(shí)，執(zhí)行查看鎖信息語(yǔ)句：

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

注意，必須是正在執(zhí)行第二條語(yǔ)句，且第二條語(yǔ)句處于阻塞狀態(tài)下，上述語(yǔ)句才能查詢到數(shù)據(jù)。

查詢結(jié)果如下：

鎖信息

第二條記錄為for update鎖表語(yǔ)句，第一條記錄為單純的update語(yǔ)句?？梢钥闯觯藞?chǎng)景下，lock_mode為X，lock_type為RECORD，lock_data為1。

lock_mode為X（排他鎖）：即寫鎖，允許獲得排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)取得相同數(shù)據(jù)集的共享讀鎖和排他寫鎖。

lock_type為RECORD，說(shuō)是是行級(jí)鎖，lock_data表示鎖定了1條記錄。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為主鍵時(shí)，select for update為行級(jí)鎖。

當(dāng)我們執(zhí)行完一個(gè)場(chǎng)景之后，我們需要執(zhí)行commit命令將當(dāng)前事物提交。

場(chǎng)景1.2：V5.x-RR-唯一索引

執(zhí)行悲觀鎖操作：

select * from user where user_no = '0001' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息，同場(chǎng)景一的主鍵一致。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為唯一索引時(shí)，select for update為行級(jí)鎖。

場(chǎng)景1.3：V5.x-RR-普通索引

執(zhí)行悲觀鎖操作：

select * from user where user_name = 'user01' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息：

鎖信息

此時(shí)，鎖類型不僅僅是X排他鎖，同時(shí)還添加了GAP（間隙鎖），也就是說(shuō)針對(duì)數(shù)據(jù)添加了排他間隙鎖。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

此時(shí)再進(jìn)行一筆插入操作：

insert into user values(null,'0006','user05',23,'重慶');

執(zhí)行成功。

由于存在了間隙鎖，再執(zhí)行一筆user_name與查詢條件相同的插入操作：

insert into user values(null,'0008','user01',24,'成都');

執(zhí)行阻塞，說(shuō)明此時(shí)有排他間隙鎖的存在。

結(jié)論：當(dāng)查詢條件為普通索引時(shí)，select for update為行級(jí)鎖，同時(shí)會(huì)有排他間隙鎖存在，當(dāng)插入數(shù)據(jù)滿足鎖語(yǔ)句查詢條件（相等、范圍等）時(shí)，會(huì)發(fā)生阻塞。

場(chǎng)景1.4：V5.x-RR-無(wú)索引

執(zhí)行悲觀鎖操作：

select * from user where address = '北京' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行被阻塞。

此時(shí)查詢鎖表信息展示如下：

鎖信息

這里比較奇怪是lock_type，很明顯，上述鎖操作已經(jīng)鎖住了整張表，但lock_type依舊為RECORD。出處暫時(shí)有些費(fèi)解。

結(jié)論：當(dāng)查詢條件無(wú)索引時(shí)，select for update為表級(jí)鎖。

場(chǎng)景1.5：V5.x-RR-索引-范圍查詢

執(zhí)行悲觀鎖操作：

select * from user where id > 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

執(zhí)行成功，說(shuō)明并沒(méi)有鎖定id為1的記錄。

執(zhí)行插入操作：

insert into user values(null,'0007','user07',24,'武漢');

插入操作被阻塞。這是因?yàn)椴迦氲臄?shù)據(jù)生成的id滿足大于1的條件，會(huì)被阻塞。

所信息如下：

鎖信息

此時(shí)，lock_type雖然是RECORD，但是lock_data顯示supremum pseudo-record ，這就是InnoDB為了解決幻讀問(wèn)題的臨鍵鎖(Next-key Lock)，這里間隙鎖和臨鍵鎖可以看做是一樣的。

需要注意的是：supremum pseudo-record有可能是間隙鎖，需要結(jié)合死鎖日志里的heap no判斷。heap no 1是間隙鎖。

結(jié)論：當(dāng)查詢條件有索引且查詢條件為范圍時(shí)，select for update會(huì)采用間隙鎖或臨鍵鎖，對(duì)指定范圍內(nèi)的數(shù)據(jù)進(jìn)行加鎖。當(dāng)然，當(dāng)查詢條件無(wú)索引時(shí)，與場(chǎng)景1.4一致，為表鎖。

場(chǎng)景2.1：V8.x-RR-主鍵

執(zhí)行悲觀鎖查詢：

select * from user where id = 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查看數(shù)據(jù)庫(kù)對(duì)應(yīng)的鎖：

SELECT * FROM performance_schema.data_locks;

注意，在MySQL 8中，采用了performance_schema替代了MySQL5中基于INFORMATION_SCHEMA的鎖查詢方式。

鎖信息

上述查詢結(jié)果中，有兩條記錄。lock_type字段展示鎖范圍，lock_mode字段展示了鎖的類型。可以看到，該SQL語(yǔ)句先是在表范圍上加了一把IX（意向排他鎖，表鎖）。然后，在記錄（Record）范圍上添加了一把X（排他鎖），一把REC_NOT_GAP（行鎖），綜合起來(lái)就是對(duì)這條記錄添加了行級(jí)排他鎖，其他事務(wù)不能夠再對(duì)其添加任何鎖了。

這里，既然在表的層面上添加了IX（意向排他鎖），為什么不是表鎖呢？這是因?yàn)橐庀蚺潘i的作用僅僅表名意向的鎖，當(dāng)其他事務(wù)要對(duì)全表的數(shù)據(jù)進(jìn)行加鎖時(shí)，那么就不需要判斷每一條數(shù)據(jù)是否被加鎖了。

事務(wù)在給一行記錄加排他鎖前，必須先取得該表的IX鎖，意向排他鎖之間相互兼容，可以并行，不會(huì)產(chǎn)生沖突。意向排他鎖存在的意義是為了更高效的獲取表鎖，主要目的是顯示事務(wù)正在鎖定某行或者試圖鎖定某行。

繼續(xù)實(shí)驗(yàn)，執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為主鍵時(shí)，select for update為行級(jí)鎖。

場(chǎng)景2.2：V8.x-RR-唯一索引

執(zhí)行悲觀鎖操作：

select * from user where user_no = '0001' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息：

鎖信息

此時(shí)，可以看到三把鎖，一把表級(jí)別的IX鎖，一把基于唯一索引的行級(jí)排他鎖，一把基于主鍵的行級(jí)排他鎖。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為唯一索引時(shí)，select for update為行級(jí)鎖。

場(chǎng)景2.3：V8.x-RR-普通索引

執(zhí)行悲觀鎖操作：

select * from user where user_name = 'user01' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息：

鎖信息

此時(shí)，可以看到四把鎖，一把表級(jí)別的IX鎖，一把基于普通索引的X排他鎖，一把基于主鍵的行級(jí)排他鎖，一把基于普通索引的X,GAP排他間隙鎖。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功，說(shuō)明更新操作沒(méi)有影響。

既然有排他間隙鎖，此時(shí)需再測(cè)試一筆插入操作：

insert into user values(null,'0006','user05',23,'重慶');

執(zhí)行成功。

再執(zhí)行一筆插入操作：

insert into user values(null,'0007','user01',24,'武漢');

注意這里插入的記錄user_name與鎖查詢條件相同，發(fā)現(xiàn)操作被阻塞。

通過(guò)兩筆插入操作可以看出，排他間隙鎖會(huì)阻塞符合查詢條件（user_name='user01'）的數(shù)據(jù)的插入。

結(jié)論：當(dāng)查詢條件為普通索引時(shí)，select for update為行級(jí)鎖，同時(shí)會(huì)多一把排他間隙鎖，如果插入數(shù)據(jù)滿足鎖語(yǔ)句的查詢條件（等于、范圍條件等），則無(wú)法插入。

場(chǎng)景2.4：V8.x-RR-無(wú)索引

執(zhí)行悲觀鎖操作：

select * from user where address = '北京' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息：

鎖信息

此時(shí)，數(shù)據(jù)庫(kù)一共加了8把鎖，一把表級(jí)別的IX意向排他鎖，6把基于主鍵的針對(duì)數(shù)據(jù)記錄（總共6條）的X鎖，一把針對(duì)記錄的supremum pseudo-record鎖。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行被阻塞。

結(jié)論：當(dāng)查詢條件無(wú)索引時(shí)，select for update為表級(jí)鎖。

場(chǎng)景2.5：V8.x-RR-索引-范圍查詢

執(zhí)行悲觀鎖操作：

select * from user where id > 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

執(zhí)行成功，說(shuō)明并沒(méi)有鎖定id為1的記錄。

執(zhí)行插入操作：

insert into user values(null,'0007','user07',24,'武漢');

插入操作被阻塞。這是因?yàn)椴迦氲臄?shù)據(jù)生成的id滿足大于1的條件，會(huì)被阻塞。

查詢鎖信息如下：

鎖信息

此時(shí)，鎖信息對(duì)比場(chǎng)景2.4，少了一條不滿足條件記錄（id=1）的鎖，其他符合條件的數(shù)據(jù)均被鎖。

結(jié)論：當(dāng)查詢條件有索引且查詢條件為范圍時(shí)，select for update會(huì)采用間隙鎖或臨鍵鎖，對(duì)指定范圍內(nèi)的數(shù)據(jù)進(jìn)行加鎖。

完成了上面針對(duì)RR事務(wù)隔離級(jí)別的驗(yàn)證，下面將數(shù)據(jù)庫(kù)事務(wù)隔離級(jí)別切換為RC。

set global transaction isolation level READ COMMITTED;

注意，此處可能需要重啟數(shù)據(jù)庫(kù)，如果通過(guò)命令配置無(wú)效，可通過(guò)數(shù)據(jù)庫(kù)配置文件進(jìn)行配置，重啟。

另外，也可以通過(guò)在所有命令窗口執(zhí)行session級(jí)別的設(shè)置，也可以達(dá)到效果，設(shè)置完成之后注意需要進(jìn)行驗(yàn)證。

場(chǎng)景3.1：V5.x-RC-主鍵

執(zhí)行悲觀鎖查詢：

select * from user where id = 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

鎖信息與RR事務(wù)相同。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為主鍵時(shí)，select for update為行級(jí)鎖。

場(chǎng)景3.2：V5.x-RC-唯一索引

執(zhí)行悲觀鎖操作：

select * from user where user_no = '0001' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息，與RR一致。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為唯一索引時(shí)，select for update為行級(jí)鎖。

場(chǎng)景3.3：V5.x-RC-普通索引

執(zhí)行悲觀鎖操作：

select * from user where user_name = 'user01' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息如下：

鎖信息

再把RR場(chǎng)景下的鎖信息貼出來(lái)：

鎖信息

可以看出，RC事務(wù)隔離級(jí)別時(shí)比RR事務(wù)隔離級(jí)別時(shí)少了一個(gè)GAP（間隙鎖）。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

此時(shí)再進(jìn)行一筆插入操作：

insert into user values(null,'0009','user01',24,'鄭州');

執(zhí)行成功。

再驗(yàn)證下間隙鎖是否真的不存在，執(zhí)行一筆user_name與查詢條件相同的插入操作：

insert into user values(null,'0008','user01',24,'成都');

執(zhí)行成功，說(shuō)明此時(shí)間隙鎖的不存在了。

結(jié)論：當(dāng)查詢條件為普通索引時(shí)，select for update為行級(jí)鎖，無(wú)間隙鎖。

場(chǎng)景3.4：V5.x-RC-無(wú)索引

執(zhí)行悲觀鎖操作：

select * from user where address = '北京' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

鎖信息如下：

鎖信息

顯示基于主鍵的排他鎖，這塊挺出乎意料的，并沒(méi)有進(jìn)行表鎖。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

再執(zhí)行一筆插入操作，插入數(shù)據(jù)與查詢條件address一致：

insert into user values(null,'0011','user01',24,'北京');

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件無(wú)索引時(shí)，select for update為行級(jí)鎖，也就說(shuō)，在RC事務(wù)隔離級(jí)別下，即便無(wú)索引，也是只鎖記錄，與通常的直知覺(jué)不同。

原因：會(huì)出現(xiàn)上述情況的原因是，本來(lái)如果鎖條件上沒(méi)有索引，MySQL會(huì)走聚簇（主鍵）索引進(jìn)行全表掃描過(guò)濾，每條記錄都會(huì)添加上X鎖。但為了效率，MySQL會(huì)對(duì)掃描過(guò)程中不滿足條件的記錄進(jìn)行解鎖操作。

場(chǎng)景3.5：V5.x-RC-索引-范圍查詢

執(zhí)行悲觀鎖操作：

select * from user where id > 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

執(zhí)行成功，說(shuō)明并沒(méi)有鎖定id為1的記錄。

執(zhí)行更新操作：

update user set age = age +1 where id = 2;

操作被阻塞。這是因?yàn)椴僮鞯臄?shù)據(jù)的id滿足大于1的條件，會(huì)被阻塞。

所信息如下：

鎖信息

結(jié)論：當(dāng)查詢條件有索引且查詢條件為范圍時(shí)，select for update對(duì)指定范圍內(nèi)的數(shù)據(jù)進(jìn)行加鎖。

場(chǎng)景4.1：V8.x-RC-主鍵

執(zhí)行悲觀鎖查詢：

select * from user where id = 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

鎖信息同RR。

繼續(xù)實(shí)驗(yàn)，執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為主鍵時(shí)，select for update為行級(jí)鎖。

場(chǎng)景4.2：V8.x-RC-唯一索引

執(zhí)行悲觀鎖操作：

select * from user where user_no = '0001' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

鎖信息同RR。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件為唯一索引時(shí)，select for update為行級(jí)鎖。

場(chǎng)景4.3：V8.x-RC-普通索引

執(zhí)行悲觀鎖操作：

select * from user where user_name = 'user01' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息：

鎖信息

對(duì)照一下RR場(chǎng)景下的鎖信息：

鎖信息

可以看出RC場(chǎng)景下筆RR場(chǎng)景下少了一條行級(jí)間隙鎖。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功，說(shuō)明更新操作沒(méi)有影響。

驗(yàn)證一下是否有排他間隙鎖，此時(shí)需再測(cè)試一筆插入操作：

insert into user values(null,'0010','user05',23,'重慶');

執(zhí)行成功。

再執(zhí)行一筆插入操作：

insert into user values(null,'0007','user01',24,'武漢');

注意這里插入的記錄user_name與鎖查詢條件相同，執(zhí)行成功，說(shuō)明真的不存在X，GAP（排他間隙鎖）。

結(jié)論：當(dāng)查詢條件為普通索引時(shí)，select for update為行級(jí)鎖。

場(chǎng)景4.4：V8.x-RC-無(wú)索引

執(zhí)行悲觀鎖操作：

select * from user where address = '北京' for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

此處更新操作被阻塞，說(shuō)明數(shù)據(jù)鎖定成功。

查詢鎖信息：

鎖信息

對(duì)照一下RR場(chǎng)景：

鎖信息

對(duì)于RR場(chǎng)景，RC場(chǎng)景下，只有一條排他行鎖（X,REC_NOT_GAP）。

執(zhí)行更新其他記錄操作：

update user set age = age +1 where id = 2;

執(zhí)行成功。

結(jié)論：當(dāng)查詢條件無(wú)索引時(shí)，select for update為行級(jí)鎖。這里的原因與場(chǎng)景3.4一致。

場(chǎng)景4.5：V8.x-RC-索引-范圍查詢

執(zhí)行悲觀鎖操作：

select * from user where id > 1 for update;

執(zhí)行更新操作：

update user set age = age +1 where id = 1;

執(zhí)行成功，說(shuō)明并沒(méi)有鎖定id為1的記錄。

執(zhí)行插入操作：

insert into user values(null,'0012','user12',24,'--');

執(zhí)行成功。

查詢鎖信息如下：

鎖信息

對(duì)照RR場(chǎng)景下的鎖信息：

鎖信息

此時(shí)，RC場(chǎng)景下，少了臨鍵鎖，排他鎖也變?yōu)榱诵屑?jí)排他鎖。

結(jié)論：當(dāng)查詢條件有索引且查詢條件為范圍時(shí)，select for update會(huì)對(duì)指定范圍內(nèi)的數(shù)據(jù)進(jìn)行加鎖，只會(huì)阻塞符合條件的記錄，不影響插入操作。

場(chǎng)景及結(jié)論

完成了上面的實(shí)驗(yàn)之后，我們通過(guò)一個(gè)表格來(lái)總結(jié)一下所有的場(chǎng)景和結(jié)論。

版本

主鍵

唯一索引

普通索引

無(wú)索引

范圍查詢

MySQL 5.7.x - RR

X：行鎖

X，行鎖

X,GAP：行鎖，間隙鎖，條件范圍內(nèi)會(huì)阻塞

表鎖

指定范圍加鎖，insert阻塞

MySQL 8.0.x - RR

X,REC_NOT_GAP：行級(jí)排他鎖

X,REC_NOT_GAP：行級(jí)排他鎖

X；X,REC_NOT_GAP；X,GAP：行鎖+排他間隙鎖，阻塞范圍內(nèi)insert；

表鎖，每條記錄一個(gè)X鎖

指定范圍加鎖，insert阻塞

MySQL 5.7.x - RC

X：行鎖

X，行鎖

X，行鎖，無(wú)間隙鎖；

行鎖

指定范圍加鎖，更新、insert阻塞

MySQL 8.0.x - RC

X,REC_NOT_GAP：行級(jí)排他鎖

X,REC_NOT_GAP：行級(jí)排他鎖

X,REC_NOT_GAP：行鎖，無(wú)間隙鎖；

X,REC_NOT_GAP：行鎖

指定范圍加鎖，不阻塞insert

從上面表中我們可以總結(jié)出以下結(jié)論（基于RR、RC兩種事務(wù)隔離級(jí)別）：

  • 無(wú)論哪個(gè)版本的MySQL，查詢條件為主鍵、唯一索引、普通索引的情況下，為行鎖；
  • 查詢條件為普通索引時(shí)，事務(wù)隔離級(jí)別為RR時(shí)，MySQL還會(huì)添加一個(gè)間隙鎖，條件內(nèi)的插入、更新會(huì)被阻塞；
  • 事務(wù)隔離級(jí)別為RR時(shí)，查詢條件無(wú)索引，為表鎖；
  • 事務(wù)隔離級(jí)別為RC時(shí)，查詢條件無(wú)索引，為行鎖；
  • 查詢條件為范圍時(shí)，有索引的情況下，除MySQL 8.0.x RC場(chǎng)景下不阻塞插入操作，其他場(chǎng)景均阻塞指定范圍更新、插入操作；

通過(guò)上面的結(jié)論，我們可以看出，并不是簡(jiǎn)單的說(shuō)“有索引就是行鎖，無(wú)索引就是表鎖”，因?yàn)樵谑聞?wù)隔離級(jí)別為RC時(shí)，無(wú)索引，同樣表現(xiàn)（被優(yōu)化）為行鎖。

至于，根據(jù)范圍條件（大于、小于、不等于、between、like等）查詢、查詢無(wú)結(jié)果等情況，大家可根據(jù)上述實(shí)驗(yàn)方法進(jìn)行自行驗(yàn)證。

本文為大家提供了實(shí)驗(yàn)方法，并針對(duì)常見的場(chǎng)景給出了結(jié)論，希望能夠幫到你，也希望大家能夠點(diǎn)贊、轉(zhuǎn)發(fā)、收藏，以備不時(shí)之需。


名稱欄目：selectforupdate行鎖or表鎖，20個(gè)場(chǎng)景分析，還真得看情況
文章位置：http://m.5511xx.com/article/djsesep.html