本篇內(nèi)容介紹了“如何理解MySQL行鎖、表鎖、間隙鎖”的有關(guān)知識(shí)，在實(shí)際案例的操作過(guò)程中，不少人都會(huì)遇到這樣的困境，接下來(lái)就讓小編帶領(lǐng)大家學(xué)習(xí)一下如何處理這些情況吧！希望大家仔細(xì)閱讀，能夠?qū)W有所成！

創(chuàng)新互聯(lián)建站公司2013年成立，先為齊齊哈爾等服務(wù)建站，齊齊哈爾等地企業(yè)，進(jìn)行企業(yè)商務(wù)咨詢服務(wù)。為齊齊哈爾企業(yè)網(wǎng)站制作PC+手機(jī)+微官網(wǎng)三網(wǎng)同步一站式服務(wù)解決您的所有建站問(wèn)題。

準(zhǔn)備工作

創(chuàng)建表 tb_innodb_lock

drop table if exists test_innodb_lock;  CREATE TABLE test_innodb_lock (      a INT (11),      b VARCHAR (20)  ) ENGINE INNODB DEFAULT charset = utf8;  insert into test_innodb_lock values (1,'a');  insert into test_innodb_lock values (2,'b');  insert into test_innodb_lock values (3,'c');  insert into test_innodb_lock values (4,'d');  insert into test_innodb_lock values (5,'e');

創(chuàng)建索引

create index idx_lock_a on test_innodb_lock(a);  create index idx_lock_b on test_innodb_lock(b);

MySQL 各種鎖演示

•  先將自動(dòng)提交事務(wù)改成手動(dòng)提交：set autocommit=0;

•  我們啟動(dòng)兩個(gè)會(huì)話窗口 A 和 B，模擬一個(gè)搶到鎖，一個(gè)沒(méi)搶到被阻塞住了。

行鎖（寫&讀）

• A 窗口執(zhí)行 

update test_innodb_lock set b='a1' where a=1;

SELECT * from test_innodb_lock;

我們可以看到 A 窗口可以看到更新后的結(jié)果

•  B 窗口執(zhí)行 

SELECT * from test_innodb_lock;

我們可以看到 B 窗口不能看到更新后的結(jié)果，看到的還是老數(shù)據(jù)，這是因?yàn)?a = 1 的這行記錄被 A 窗口執(zhí)行的 SQL 語(yǔ)句搶到了鎖，并且沒(méi)有執(zhí)行 commit 提交操作。所以窗口 B 看到的還是老數(shù)據(jù)。這就是 MySQL 隔離級(jí)別中的"讀已提交"。

•  窗口 A 執(zhí)行 commit 操作 

COMMIT;

•  窗口 B 查詢 

SELECT * from test_innodb_lock;

這個(gè)時(shí)候我們發(fā)現(xiàn)窗口 B 已經(jīng)讀取到最新數(shù)據(jù)了

行鎖（寫&寫）

•  窗口 A 執(zhí)行更新 a = 1 的記錄 

update test_innodb_lock set b='a2' where a=1;

這時(shí)候并沒(méi)有 commit 提交，鎖是窗口 A 持有。

•  窗口 B 也執(zhí)行更新 a = 1 的記錄 

update test_innodb_lock set b='a3' where a=1;

可以看到，窗口 B 一直處于阻塞狀態(tài)，因?yàn)榇翱?A 還沒(méi)有執(zhí)行 commit，還持有鎖。窗口 B 搶不到 a = 1 這行記錄的鎖，所以一直阻塞等待。

•  窗口 A 執(zhí)行 commit 操作 

COMMIT;

  窗口 B 的變化

可以看到這個(gè)時(shí)候窗口 B 已經(jīng)執(zhí)行成功了

表鎖

當(dāng)索引失效的時(shí)候，行鎖會(huì)升級(jí)成表鎖，索引失效的其中一個(gè)方法是對(duì)索引自動(dòng) or 手動(dòng)的換型。a 字段本身是 integer，我們加上引號(hào)，就變成了 String，這個(gè)時(shí)候索引就會(huì)失效了。

•  窗口 A 更新 a = 1 的記錄 

update test_innodb_lock set b='a4' where a=1 or a=2;

•  窗口 B 更新 a = 2 的記錄 

update test_innodb_lock set b='b1' where a=3;

這個(gè)時(shí)候發(fā)現(xiàn)，雖然窗口 A 和 B 更新的行不一樣，但是窗口 B 還是被阻塞住了，就是因?yàn)榇翱?A 的索引失效，導(dǎo)致行鎖升級(jí)成了表鎖，把整個(gè)表鎖住了，索引窗口 B 被阻塞了。

•  窗口 A 執(zhí)行 commit 操作 

COMMIT;

•  窗口 B 的變化

可以看到這個(gè)時(shí)候窗口 B 已經(jīng)執(zhí)行成功了

間隙鎖

•  什么是間隙鎖

當(dāng)我們采用范圍條件查詢數(shù)據(jù)時(shí)，InnoDB 會(huì)對(duì)這個(gè)范圍內(nèi)的數(shù)據(jù)進(jìn)行加鎖。比如有 id 為：1、3、5、7 的 4 條數(shù)據(jù)，我們查找 1-7 范圍的數(shù)據(jù)。那么 1-7 都會(huì)被加上鎖。2、4、6 也在 1-7 的范圍中，但是不存在這些數(shù)據(jù)記錄，這些 2、4、6 就被稱為間隙。

• 間隙鎖的危害

范圍查找時(shí)，會(huì)把整個(gè)范圍的數(shù)據(jù)全部鎖定住，即便這個(gè)范圍內(nèi)不存在的一些數(shù)據(jù)，也會(huì)被無(wú)辜的鎖定住，比如我要在 1、3、5、7 中插入 2，這個(gè)時(shí)候 1-7 都被鎖定住了，根本無(wú)法插入 2。在某些場(chǎng)景下會(huì)對(duì)性能產(chǎn)生很大的影響

•  間隙鎖演示

我們先把字段 a 的值修改成 1、3、5、7、9

•  窗口 A 更新 a = 1~7 范圍的數(shù)據(jù) 

update test_innodb_lock set b='b5' where a>1 and a<7;

•  窗口 B 在 a = 2 的位置插入數(shù)據(jù) 

insert into test_innodb_lock values(2, "b6");

這個(gè)時(shí)候發(fā)現(xiàn)窗口 B 更新 a = 2 的操作一直在等待，因?yàn)?1~7 范圍的數(shù)據(jù)被間隙鎖，鎖住了。只有等窗口 A 執(zhí)行 commit，窗口 B 的 a = 2 才能更新成功

行鎖分析

•  執(zhí)行 SQL 分析命令 

show status like 'innodb_row_lock%';

•  Variable_name 說(shuō)明

•  Innodb_row_lock_current_waits：當(dāng)前正在等待鎖定的數(shù)量。

•  Innodb_row_lock_time：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定的時(shí)長(zhǎng)。

•  Innodb_row_lock_time_avg：每次等待鎖所花平均時(shí)間。

•  Innodb_row_lock_time_max：從系統(tǒng)啟動(dòng)到現(xiàn)在鎖等待最長(zhǎng)的一次所花的時(shí)間。

•  Innodb_row_lock_waits：系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待鎖的次數(shù)。

“如何理解MySQL行鎖、表鎖、間隙鎖”的內(nèi)容就介紹到這里了，感謝大家的閱讀。如果想了解更多行業(yè)相關(guān)的知識(shí)可以關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編將為大家輸出更多高質(zhì)量的實(shí)用文章！