RR下select當前讀如何讓通過主鍵索引加鎖

RC是讀已提交，RR是可重復讀

下面實驗都是在windows mysql8.4.4下進行的

查看當前mysql的隔離級別：select @@transaction_isolation;

下面來探討一下在RR級別是如何加鎖的

建表語句：

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)建二級索引

create index xxx on user(age)

查看當前表的索引

show index from user;

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

主鍵索引加鎖方式(RR)

select語句

等值查詢

如果當前id在表中記錄存在

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

使用select * from performance_schema.data_locks\G;查詢當前鎖的情況

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

如果當前id在表中記錄不存在：

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

begin;

select * from user where id = 4 for update;

使用select * from performance_schema.data_locks\G;查詢當前鎖的情況

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

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

如果查詢id為0的值（小于當前表中id的最小值）：

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

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

如果查詢id為999的值（大于當前表中id的最大值）：

supremum 表示一個偽記錄，用來表示最大索引之后的記錄

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

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

查詢id為4

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

使用select * from performance_schema.data_locks\G;查詢當前鎖的情況

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

范圍查詢

<=情況

如果范圍條件端點在表中

執(zhí)行如下sql

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

使用select * from performance_schema.data_locks\G;查詢當前鎖的情況

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

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

如果查詢條件的端點不在表中

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

查看鎖的信息

select * from performance_schema.data_locks\G;

圖中上鎖的信息和端點在表中的相比，多了一個鎖住以第一個大于查詢條件的端點對應的值的值作為右端的間隙

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

如圖：

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

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

< 情況

如果查詢的端點存在于表中

執(zhí)行如下sql

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

查詢鎖的信息

select * from performance_schema.data_locks\G;

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

關于這里為什么明明id是int，在兩個元素之間應該不可能插入元素了，為什么還要給這個間隙加鎖？

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

如果查詢的端點不在表中

執(zhí)行下列sql

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

查看鎖的信息

select * from performance_schema.data_locks\G;

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

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

>=情況

如果端點在表中存在

執(zhí)行如下sql

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

查看鎖的信息

select * from performance_schema.data_locks\G;

圖中給id為3的記錄加了純行鎖，給大于 3的id對應的記錄加了臨建鎖

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

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

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

有下列sql

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

查看鎖的信息

select * from performance_schema.data_locks\G;

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

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

>的情況

如果端點在表中存在

執(zhí)行下面sql

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

查看鎖的信息

select * from performance_schema.data_locks\G;

這里會給表中所有大于端點值的記錄全部加上臨鍵鎖，這里可以把supremum一個虛擬點，對虛擬點加鎖就是給大于當前表中索引最大值的間隙加鎖

如果查詢的端點不存在

執(zhí)行下面sql

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

查看鎖的信息

select * from performance_schema.data_locks\G;

上面加鎖的信息可以看出給表中所有 id > 7 的對應的值加了臨鍵鎖

會給表中所有大于id對應的記錄加上臨鍵鎖

總結

等值查詢

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

范圍查詢

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

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