RR下select當(dāng)前讀如何讓通過(guò)主鍵索引加鎖

RC是讀已提交，RR是可重復(fù)讀

下面實(shí)驗(yàn)都是在windows mysql8.4.4下進(jìn)行的

查看當(dāng)前mysql的隔離級(jí)別：select @@transaction_isolation;

下面來(lái)探討一下在RR級(jí)別是如何加鎖的

建表語(yǔ)句：

create table user (
    id int primary key ,
    name varchar(10),
    age int, 
	salary int);

插入數(shù)據(jù)：

INSERT INTO user (id, name, age,salary) VALUES
(1, '張三', 20,23),
(2, '李四', 22,24),
(3, '王五', 25,25),
(8, '趙六', 18,26),
(9, '孫七', 30,27);

給 age字段創(chuàng)建二級(jí)索引

create index xxx on user(age)

查看當(dāng)前表的索引

show index from user;

現(xiàn)在一張表中是有主鍵索引和二級(jí)索引的

主鍵索引加鎖方式(RR)

select語(yǔ)句

等值查詢

如果當(dāng)前id在表中記錄存在

begin;
select * from user where id = 2 for update;

使用select * from performance_schema.data_locks\G;查詢當(dāng)前鎖的情況

可以看到這條語(yǔ)句是給user表加了一個(gè)意向排他鎖，給 id為3這條數(shù)據(jù)加了一個(gè)純行鎖

如果當(dāng)前id在表中記錄不存在：

把上一個(gè)事務(wù)提交后執(zhí)行如下sql

begin;

select * from user where id = 4 for update;

使用select * from performance_schema.data_locks\G;查詢當(dāng)前鎖的情況

可以看到已經(jīng)給開區(qū)間為8的這個(gè)區(qū)間加了間隙鎖，即（3，8）這個(gè)區(qū)間加了間隙鎖

再試一下，如果分別查詢id小于最小值和最大值是什么情況，存在于表中的間隙，先提交上面事務(wù)

如果查詢id為0的值（小于當(dāng)前表中id的最小值）：

beginl;
select * from user where id = 0 for update;

查詢小于表中小于最小id的id時(shí)，會(huì)給右邊開區(qū)間為1的間隙加鎖即(負(fù)無(wú)窮大，1)

如果查詢id為999的值（大于當(dāng)前表中id的最大值）：

supremum 表示一個(gè)偽記錄，用來(lái)表示最大索引之后的記錄

如果查詢的值是大于當(dāng)前索引值的最大記錄，就會(huì)給（max(id)，正無(wú)窮）這個(gè)區(qū)間加間隙鎖

但是查詢鎖記錄的時(shí)候，給我們看到的是給這個(gè)虛擬記錄加了一個(gè)臨建鎖，可以這么認(rèn)為是存在一個(gè)記錄，這個(gè)記錄永遠(yuǎn)不可以被查詢到，就給supremum這條記錄上了純行鎖，給（9，supremum）這條記錄加了一個(gè)間隙鎖

查詢id為4

begin;
select * from user where id = 4 for update;

使用select * from performance_schema.data_locks\G;查詢當(dāng)前鎖的情況

可以看到是給有開區(qū)間為8的間隙加了間隙鎖，這樣也是為了防止有其他事務(wù)插入id為4的值出現(xiàn)幻讀

范圍查詢

<=情況

如果范圍條件端點(diǎn)在表中

執(zhí)行如下sql

begin;
select * from user where id <= 3 for update;

使用select * from performance_schema.data_locks\G;查詢當(dāng)前鎖的情況

第一行表示的是給表加鎖，這里我把它省略了

鎖的情況是給id in (1,2,3)的記錄都加了臨建鎖

如果查詢條件的端點(diǎn)不在表中

begin;
select * from user where id <= 4 for update;

查看鎖的信息

select * from performance_schema.data_locks\G;

圖中上鎖的信息和端點(diǎn)在表中的相比，多了一個(gè)鎖住以第一個(gè)大于查詢條件的端點(diǎn)對(duì)應(yīng)的值的值作為右端的間隙

這里需要多鎖一條記錄的原因是可能會(huì)有其他事務(wù)插入id為4的記錄，造成幻讀

如圖：

鎖?。?，8）這個(gè)間隙,其他事務(wù)舊無(wú)法在這個(gè)間隙插入數(shù)據(jù)了這樣就可以可以防止幻讀

當(dāng)執(zhí)行一條語(yǔ)句 select * from ... where id <= x for update時(shí)，如果x存在于表中會(huì)給所有小于等于x的記錄加上臨建鎖，如果不存在于表中，會(huì)往后尋找一條數(shù)據(jù)，鎖住x和這條數(shù)據(jù)對(duì)應(yīng)的間隙

< 情況

如果查詢的端點(diǎn)存在于表中

執(zhí)行如下sql

begin;
select * from user where id < 3 for update;

查詢鎖的信息

select * from performance_schema.data_locks\G;

圖中給所有 id < 3的存在的記錄加了臨建鎖，給開區(qū)間右端點(diǎn)為3的間隙加了間隙鎖

關(guān)于這里為什么明明id是int，在兩個(gè)元素之間應(yīng)該不可能插入元素了，為什么還要給這個(gè)間隙加鎖？

當(dāng)id為int的時(shí)候，確實(shí)是不可能往這個(gè)間隙插入元素了，但mysql的設(shè)計(jì)是通用的，如果主鍵是小數(shù)，就會(huì)有可能往2到3之前插入 id為2.5的記錄，主要是為了設(shè)計(jì)統(tǒng)一

如果查詢的端點(diǎn)不在表中

執(zhí)行下列sql

begin;
select * from user where id < 4 for update;

查看鎖的信息

select * from performance_schema.data_locks\G;

和之前 <= 情況討論的一樣，相比于端點(diǎn)存在表中的情況，多加了一個(gè)在表中以比當(dāng)前查詢條件更大的第一條記錄作為右端點(diǎn)的開區(qū)間加間隙鎖，防止出現(xiàn)幻讀

當(dāng)執(zhí)行 < 這種查詢的時(shí)候，會(huì)給所有小于條件的在表中存在的值加鎖，給小于條件的第一條數(shù)據(jù)和條件之間加上間隙鎖

>=情況

如果端點(diǎn)在表中存在

執(zhí)行如下sql

begin;
select * from user where id >= 3 for update;

查看鎖的信息

select * from performance_schema.data_locks\G;

圖中給id為3的記錄加了純行鎖，給大于 3的id對(duì)應(yīng)的記錄加了臨建鎖

這里有一個(gè)現(xiàn)象是 id為3的記錄出現(xiàn)了鎖退化現(xiàn)象

這里出現(xiàn)鎖退化的原因是因?yàn)?我們查詢 di >= 3的數(shù)據(jù)，只需要鎖住 >=3的這個(gè)范圍的數(shù)據(jù)不被修改即可，就沒(méi)必要對(duì)開區(qū)間為3的區(qū)間加鎖了

如果id的范圍條件邊界不存在：

有下列sql

begin;
select *from user where id >= 4 for update;

查看鎖的信息

select * from performance_schema.data_locks\G;

上圖中鎖的信息是給所有 大于等于4的記錄都加上了臨建鎖

這里可以得出：當(dāng)執(zhí)行 >= 的查詢是，如果邊界條件的值存在的話對(duì)應(yīng)值上的鎖會(huì)退化為純行鎖,其他記錄都加上臨建鎖，否則所有滿足在表中滿足條件的記錄全部加上臨鍵鎖

>的情況

如果端點(diǎn)在表中存在

執(zhí)行下面sql

begin;
select * from user where id > 8 for update;

查看鎖的信息

select * from performance_schema.data_locks\G;

這里會(huì)給表中所有大于端點(diǎn)值的記錄全部加上臨鍵鎖，這里可以把supremum一個(gè)虛擬點(diǎn)，對(duì)虛擬點(diǎn)加鎖就是給大于當(dāng)前表中索引最大值的間隙加鎖

如果查詢的端點(diǎn)不存在

執(zhí)行下面sql

begin;
select * from user where id > 7 for update;

查看鎖的信息

select * from performance_schema.data_locks\G;

上面加鎖的信息可以看出給表中所有 id > 7 的對(duì)應(yīng)的值加了臨鍵鎖

會(huì)給表中所有大于id對(duì)應(yīng)的記錄加上臨鍵鎖

總結(jié)

等值查詢

• 如果查詢的id不存在(主鍵)，就給第一條大于id對(duì)應(yīng)的記錄左側(cè)加間隙鎖
• 如果id存在，就會(huì)給對(duì)應(yīng)的id加上純行鎖

范圍查詢

• 大于情況：不管id是否存在，都會(huì)給表中所有大于id的記錄加上臨鍵鎖
• 大于等于情況：
  • id存在：給表中對(duì)應(yīng)的id加上純行鎖，給所有大于id的記錄加上臨鍵鎖
  • id不存在：給表中所有大于id的記錄都加上臨鍵鎖
• 小于情況：
  • id存在：會(huì)給所有小于條件的在表中存在的值加鎖，給小于條件的第一條數(shù)據(jù)和條件之間加上間隙鎖
  • id不存在：會(huì)給第一條小于id對(duì)應(yīng)的記錄和后一條數(shù)據(jù)和加上間隙鎖，其他小于等于id的記錄都加上臨鍵鎖
• 小于等于情況：
  • id存在：如果x存在于表中會(huì)給所有小于等于x的記錄加上臨鍵鎖
  • id不存在：如果不存在于表中，會(huì)往后尋找一條數(shù)據(jù)，鎖住x和這條數(shù)據(jù)對(duì)應(yīng)的間隙，再給小于等于id的記錄加上臨鍵鎖

到此這篇關(guān)于Mysql在RR級(jí)別當(dāng)前讀如何通過(guò)主鍵索引加鎖的文章就介紹到這了,更多相關(guān)Mysql在RR級(jí)別索引加鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評(píng)論