Part1前言

MySQL 在使用 for update 進(jìn)行查詢(xún)操作時(shí)，鎖的是 row 還是 table 呢？答案見(jiàn)文末。

本文測(cè)試的環(huán)境為 MySQL 8.0.21

Part2驗(yàn)證

MySQL for update 時(shí)使用索引 檢索數(shù)據(jù)的情況下，使用的是 row lock，而不使用索引檢索數(shù)據(jù)的話，是 table lock，下面我們先來(lái)通過(guò)實(shí)驗(yàn)驗(yàn)證這個(gè)說(shuō)法。

打開(kāi)兩個(gè)MySQL連接，將其中一個(gè)連接關(guān)閉自動(dòng)提交事務(wù)。

-- 查詢(xún)事務(wù)提交方式
select @@autocommit;
-- 關(guān)閉自動(dòng)提交事務(wù)
set autocommit = 0;

現(xiàn)在有一個(gè) user 表，表中存儲(chǔ)數(shù)據(jù)如下：

表中索引結(jié)構(gòu)是只有主鍵為聚集索引。

1主鍵索引檢索數(shù)據(jù)

連接1

begin;

select * from user where id = "1" for update;

連接2

update `user` set `name` = "feiyangyang" where id = "1";

由于連接1未提交事務(wù)，所以 id="1" 的行記錄被加了鎖，導(dǎo)致連接2寫(xiě)數(shù)據(jù)失敗。

update `user` set `name` = "feiyangyang" where id = "1"
> 1205 - Lock wait timeout exceeded; try restarting transaction
> 時(shí)間: 50.403s

再對(duì) id="2" 的數(shù)據(jù)進(jìn)行 update 操作

update `user` set `name` = "feiyangyang" where id = "2";

update `user` set `name` = "feiyangyang" where id = "2"
> Affected rows: 1
> 時(shí)間: 0.002s

上述實(shí)驗(yàn)測(cè)試的有數(shù)據(jù)的情況，如果無(wú)數(shù)據(jù)的情況呢？

連接1

begin;

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

連接2

update `user` set `name` = "feiyangyang" where id = "4";

發(fā)現(xiàn)沒(méi)有數(shù)據(jù)的情況下沒(méi)有鎖

update `user` set `name` = "feiyangyang" where id = "4"
> Affected rows: 0
> 時(shí)間: 0s

現(xiàn)在確定了 id="1"的記錄是被加了行鎖（row lock），得出結(jié)論：

結(jié)論：根據(jù)主鍵索引檢索數(shù)據(jù)時(shí)，row lock（有數(shù)據(jù)），no lock（無(wú)數(shù)據(jù)）

2根據(jù)主鍵索引和非索引字段檢索數(shù)據(jù)

連接1

begin;

select * from `user` where id = "1" and `name` = "test" for update;

連接2

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "1";

由于連接1未提交事務(wù)，數(shù)據(jù)行被鎖，導(dǎo)致連接2 update 失敗

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 時(shí)間: 51.451s

此時(shí)，不提交連接1的事務(wù)，使用連接2查詢(xún)其他行記錄，執(zhí)行成功。

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "2";

再來(lái)看看無(wú)數(shù)據(jù)的情況

UPDATE `user` 
SET `name` = "feiyangyang" 
WHERE id = "4";

> Affected rows: 0
> 時(shí)間: 0s

結(jié)論：根據(jù)主鍵索引和普通字段檢索數(shù)據(jù)，row lock（有數(shù)據(jù)）, no lock（無(wú)數(shù)據(jù)）

3根據(jù)非索引字段檢索數(shù)據(jù)

連接1

begin;

select * from `user` where `name` = "test" for update;

連接2

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "test";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 時(shí)間: 50.385s

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "feiyangyang";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 時(shí)間: 50.385s

連接2 中執(zhí)行的第一個(gè) sql 語(yǔ)句與連接1中操作中的是同一個(gè)記錄行，由于連接1未提交事務(wù)，所以連接2 更新失敗; 但第二個(gè) sql 語(yǔ)句與連接1并不是同一個(gè)記錄行，依然更新失敗。

再來(lái)看看無(wú)數(shù)據(jù)的情況：

連接2

UPDATE `user` SET pwd = "feiyangyang" WHERE `name`= "xiyangyang"

> Affected rows: 0
> 時(shí)間: 0.001s

結(jié)論：根據(jù)非索引字段檢索數(shù)據(jù)，table lock（有數(shù)據(jù)） no lock（無(wú)數(shù)據(jù)）

4根據(jù)普通索引檢索數(shù)據(jù)

為 1.3 中的 name 字段添加索引

create index idx_name on `user`(`name`);

連接1

begin;

select * from `user` where `name` = "test" for update;

連接2

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "test";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 時(shí)間: 50.385s

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "feiyangyang";

> Affected rows: 1
> 時(shí)間: 0.013s

無(wú)數(shù)據(jù)的情況同上，略。

結(jié)論：根據(jù)普通索引檢索，row lock（有數(shù)據(jù)），no lock（無(wú)數(shù)據(jù)）

5根據(jù) unique 索引檢索數(shù)據(jù)

將 name 字段的索引修改為 唯一索引

drop index idx_name on `user`;
create unique index idx_name on `user`(name);

連接1

begin;

select * from `user` where `name` = "test" for update;

連接2

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "test";

> 1205 - Lock wait timeout exceeded; try restarting transaction
> 時(shí)間: 50.374s

UPDATE `user` 
SET pwd = "feiyangyang" 
WHERE `name`= "feiyangyang";

> Affected rows: 1
> 時(shí)間: 0.05s

無(wú)數(shù)據(jù)的情況同上，略。

結(jié)論：根據(jù)唯一索引檢索數(shù)據(jù)時(shí)，row lock （有數(shù)據(jù)），no lock（無(wú)數(shù)據(jù)）

Part3總結(jié)

當(dāng)對(duì)索引列檢索數(shù)據(jù)時(shí)，有數(shù)據(jù)的情況下，鎖的級(jí)別是記錄行；而根據(jù)非索引字段檢索數(shù)據(jù)時(shí)，有數(shù)據(jù)的情況下，鎖的級(jí)別是整個(gè)表。

MySQL 進(jìn)行 row lock 還是 table lock 只取決于是否使用了索引，當(dāng)進(jìn)行一些讓索引失效的操作時(shí)，自然進(jìn)行的是 table lock 了。

那問(wèn)題來(lái)了，為什么對(duì)索引字段加排他鎖，鎖的是行記錄，對(duì)非索引字段加鎖，鎖的是整個(gè)表？

排他鎖鎖的是索引項(xiàng)，個(gè)人理解就是B+樹(shù)的葉子節(jié)點(diǎn)，當(dāng)對(duì)同一棵B+樹(shù)的其他葉子節(jié)點(diǎn)進(jìn)行寫(xiě)操作時(shí)，是互不影響的。而如果是非索引字段，是沒(méi)有索引樹(shù)的結(jié)構(gòu)的，只能鎖整個(gè)表。