MySQL中的鎖可以分成幾類

本篇內(nèi)容主要講解“MySQL中的鎖可以分成幾類”，感興趣的朋友不妨來看看。本文介紹的方法操作簡(jiǎn)單快捷，實(shí)用性強(qiáng)。下面就讓小編來帶大家學(xué)習(xí)“MySQL中的鎖可以分成幾類”吧!

根據(jù)加鎖的范圍，MySQL里面的鎖大致可以分成全局鎖、表級(jí)鎖和行鎖三類

一、全局鎖

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫實(shí)例加鎖。MySQL提供了一個(gè)加全局讀鎖的方法，命令是Flush tables with read lock。當(dāng)需要讓整個(gè)庫處于只讀狀態(tài)的時(shí)候，可以使用這個(gè)命令，之后其他線程的以下語句會(huì)被阻塞：數(shù)據(jù)更新語句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語句（包括建表、修改表結(jié)構(gòu)等）和更新類事務(wù)的提交語句?！鞠嚓P(guān)推薦：mysql教程(視頻)】

全局鎖的典型使用場(chǎng)景是，做全庫邏輯備份。也就是把整庫每個(gè)表都select出來存成文本

但是讓整個(gè)庫都只讀，可能出現(xiàn)以下問題：

• 如果在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺

• 如果在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的binlog，會(huì)導(dǎo)致主從延遲

在可重復(fù)讀隔離級(jí)別下開啟一個(gè)事務(wù)能夠拿到一致性視圖

官方自帶的邏輯備份工具是mysqldump。當(dāng)mysqldump使用參數(shù)–single-transaction的時(shí)候，導(dǎo)數(shù)據(jù)之前就會(huì)啟動(dòng)一個(gè)事務(wù)，來確保拿到一致性視圖。而由于MVCC的支持，這個(gè)過程中數(shù)據(jù)是可以正常更新的。single-transaction只適用于所有的表使用事務(wù)引擎的庫

1.既然要全庫只讀，為什么不使用set global readonly=true的方式？

• 在有些系統(tǒng)中，readonly的值會(huì)被用來做其他邏輯，比如用來判斷一個(gè)庫是主庫還是備庫。因此修改global變量的方式影響面更大

• 在異常處理機(jī)制上有差異。如果執(zhí)行Flush tables with read lock命令之后由于客戶端發(fā)生異常斷開，那么MySQL會(huì)自動(dòng)釋放這個(gè)全局鎖，整個(gè)庫回到可以正常更新的狀態(tài)。而將整個(gè)庫設(shè)置為readonly之后，如果客戶端發(fā)生異常，則數(shù)據(jù)庫會(huì)一直保持readonly狀態(tài)，這樣會(huì)導(dǎo)致整個(gè)庫長(zhǎng)時(shí)間處于不可寫狀態(tài)，風(fēng)險(xiǎn)較高

二、表級(jí)鎖

MySQL里面表級(jí)別的鎖有兩種：一種是表鎖，一種是元數(shù)據(jù)鎖（meta data lock，MDL）

表鎖的語法是lock tables … read/write?？梢杂胾nlock tables主動(dòng)釋放鎖，也可以在客戶端斷開的時(shí)候自動(dòng)釋放。lock tables語法除了會(huì)限制別的線程的讀寫外，也限定了本線程接下來的操作對(duì)象

如果在某個(gè)線程A中執(zhí)行lock tables t1 read,t2 wirte;這個(gè)語句，則其他線程寫t1、讀寫t2的語句都會(huì)被阻塞。同時(shí)，線程A在執(zhí)行unlock tables之前，也只能執(zhí)行讀t1、讀寫t2的操作。連寫t1都不允許

另一類表級(jí)的鎖是MDL。MDL不需要顯式使用，在訪問一個(gè)表的時(shí)候會(huì)被自動(dòng)加上。MDL的作用是，保證讀寫的正確性。如果一個(gè)查詢正在遍歷一個(gè)表中的數(shù)據(jù)，而執(zhí)行期間另一個(gè)線程對(duì)這個(gè)表結(jié)構(gòu)做了變更，刪了一列，那么查詢線程拿到的結(jié)果跟表結(jié)構(gòu)對(duì)不上，肯定不行

在MySQL5.5版本引入了MDL，當(dāng)對(duì)一個(gè)表做增刪改查操作的時(shí)候，加MDL讀鎖；當(dāng)要對(duì)表做結(jié)構(gòu)變更操作的時(shí)候，加MDL寫鎖

• 讀鎖之間不互斥，因此可以有多個(gè)線程同時(shí)對(duì)一張表增刪改查

• 讀寫鎖之間、寫鎖之間是互斥的，用來保證變更表結(jié)構(gòu)操作的安全性。因此，如果有兩個(gè)線程要同時(shí)給一個(gè)表加字段，其中一個(gè)要等另一個(gè)執(zhí)行完才能開始執(zhí)行

給一個(gè)表加字段，或者修改字段，或者加索引，需要掃描全表的數(shù)據(jù)。在對(duì)大表操作的時(shí)候，需要特別小心，以免對(duì)線上服務(wù)造成影響

session A先啟動(dòng)，這時(shí)候會(huì)對(duì)表t加一個(gè)MDL讀鎖。由于session B需要的也是MDL讀鎖，因此可以正常執(zhí)行。之后sesession C會(huì)被blocked，是因?yàn)閟ession A的MDL讀鎖還沒有釋放，而session C需要MDL寫鎖，因此只能被阻塞。如果只有session C自己被阻塞還沒什么關(guān)系，但是之后所有要在表t上新申請(qǐng)MDL讀鎖的請(qǐng)求也會(huì)被session C阻塞。所有對(duì)表的增刪改查操作都需要先申請(qǐng)MDL讀鎖，就都被鎖住，等于這個(gè)表現(xiàn)在完全不可讀寫了

事務(wù)中的MDL鎖，在語句執(zhí)行開始時(shí)申請(qǐng)，但是語句結(jié)束后并不會(huì)馬上釋放，而會(huì)等到整個(gè)事務(wù)提交后再釋放

1.如果安全地給小表加字段？

首先要解決長(zhǎng)事務(wù)，事務(wù)不提交，就會(huì)一直占著DML鎖。在MySQL的information_schema庫的innodb_trx表中，可以查到當(dāng)前執(zhí)行的事務(wù)。如果要做DDL變更的表剛好有長(zhǎng)事務(wù)在執(zhí)行，要考慮先暫停DDL，或者kill掉這個(gè)長(zhǎng)事務(wù)

2.如果要變更的表是一個(gè)熱點(diǎn)表，雖然數(shù)據(jù)量不大，但是上面的請(qǐng)求很頻繁，而又不得不加個(gè)字段，該怎么做？

在alter table語句里面設(shè)定等待時(shí)間，如果在這個(gè)指定的等待時(shí)間里面能夠拿到MDL寫鎖最好，拿不到也不要阻塞后面的業(yè)務(wù)語句，先放棄。之后再通過重試命令重復(fù)這個(gè)過程

三、行鎖

MySQL的行鎖是在引擎層由各個(gè)引擎自己實(shí)現(xiàn)的。但不是所有的引擎都支持行鎖，比如MyISAM引擎就不支持行鎖

行鎖就是針對(duì)數(shù)據(jù)表中行記錄的鎖。比如事務(wù)A更新了一行，而這時(shí)候事務(wù)B也要更新同一行，則必須等事務(wù)A的操作完成后才能進(jìn)行更新

1、兩階段鎖協(xié)議

事務(wù)A持有的兩個(gè)記錄的行鎖都是在commit的時(shí)候才釋放的，事務(wù)B的update語句會(huì)被阻塞，直到事務(wù)A執(zhí)行commit之后，事務(wù)B才能繼續(xù)執(zhí)行

在InnoDB事務(wù)中，行鎖是在需要的時(shí)候才加上的，但并不是不需要了就立刻釋放，而是要等到事務(wù)結(jié)束時(shí)才釋放。這個(gè)就是兩階段鎖協(xié)議

如果事務(wù)中需要鎖多個(gè)行，要把最可能造成鎖沖突、最可能影響并發(fā)度的鎖盡量往后放

假設(shè)要實(shí)現(xiàn)一個(gè)電影票在線交易業(yè)務(wù)，顧客A要在影院B購買電影票。業(yè)務(wù)需要涉及到以下操作：

1.從顧客A賬戶余額中扣除電影票價(jià)

2.給影院B的賬戶余額增加這張電影票價(jià)

3.記錄一條交易日志

為了保證交易的原子性，要把這三個(gè)操作放在一個(gè)事務(wù)中。如何安排這三個(gè)語句在事務(wù)中的順序呢？

如果同時(shí)有另外一個(gè)顧客C要在影院B買票，那么這兩個(gè)事務(wù)沖突的部分就是語句2了。因?yàn)樗鼈円峦粋€(gè)影院賬戶的余額，需要修改同一行數(shù)據(jù)。根據(jù)兩階段鎖協(xié)議，所有的操作需要的行鎖都是在事務(wù)提交的時(shí)候才釋放的。所以，如果把語句2安排在最后，比如按照3、1、2這樣的順序，那么影院賬戶余額這一行的鎖時(shí)間就最少。這就最大程度地減少了事務(wù)之間的鎖等待，提升了并發(fā)度

2、死鎖和死鎖檢測(cè)

在并發(fā)系統(tǒng)中不同線程出現(xiàn)循環(huán)資源依賴，涉及的線程都在等待別的線程釋放資源時(shí)，就會(huì)導(dǎo)致這幾個(gè)線程都進(jìn)入無限等待的狀態(tài)，稱為死鎖

事務(wù)A在等待事務(wù)B釋放id=2的行鎖，而事務(wù)B在等待事務(wù)A釋放id=1的行鎖。事務(wù)A和事務(wù)B在互相等待對(duì)方的資源釋放，就是進(jìn)入了死鎖狀態(tài)。當(dāng)出現(xiàn)死鎖以后，有兩種策略：

• 一種策略是，直接進(jìn)入等待，直到超時(shí)。這個(gè)超時(shí)時(shí)間可以通過參數(shù)innodb_lock_wait_timeout來設(shè)置

• 另一種策略是，發(fā)起死鎖檢測(cè)，發(fā)現(xiàn)死鎖后，主動(dòng)回滾死鎖鏈條中的某一個(gè)事務(wù)，讓其他事務(wù)得以繼續(xù)執(zhí)行。將參數(shù)innodb_deadlock_detect設(shè)置為on，表示開啟這個(gè)邏輯

在InnoDB中，innodb_lock_wait_timeout的默認(rèn)值是50s，意味著如果采用第一個(gè)策略，當(dāng)出現(xiàn)死鎖以后，第一個(gè)被鎖住的線程要過50s才會(huì)超時(shí)退出，然后其他線程才有可能繼續(xù)執(zhí)行。對(duì)于在線服務(wù)來說，這個(gè)等待時(shí)間往往是無法接受的

正常情況下還是要采用主動(dòng)死鎖檢查策略，而且innodb_deadlock_detect的默認(rèn)值本身就是on。主動(dòng)死鎖監(jiān)測(cè)在發(fā)生死鎖的時(shí)候，是能夠快速發(fā)現(xiàn)并進(jìn)行處理的，但是它有額外負(fù)擔(dān)的。每當(dāng)一個(gè)事務(wù)被鎖的時(shí)候，就要看看它所依賴的線程有沒有被別人鎖住，如此循環(huán)，最后判斷是否出現(xiàn)了循環(huán)等待，也就是死鎖

如果所有事務(wù)都要更新同一行的場(chǎng)景，每個(gè)新來的被堵住的線程都要判斷會(huì)不會(huì)由于自己的加入導(dǎo)致死鎖，這是一個(gè)時(shí)間復(fù)雜度是O(n)的操作

怎么解決由這種熱點(diǎn)行更新導(dǎo)致的性能問題？

1.如果確保這個(gè)業(yè)務(wù)一定不會(huì)出現(xiàn)死鎖，可以臨時(shí)把死鎖檢測(cè)關(guān)掉

2.控制并發(fā)度

3.將一行改成邏輯上的多行來減少鎖沖突。以影院賬戶為例，可以考慮放在多條記錄上，比如10個(gè)記錄，影院的賬戶總額等于這10個(gè)記錄的值的總和。這樣每次要給影院賬戶加金額的時(shí)候，隨機(jī)選其中一條記錄來加。這樣每次沖突概率變成員原來的1/10，可以減少鎖等待個(gè)數(shù)，也就減少了死鎖檢測(cè)的CPU消耗

四、為什么我只查一行的語句，也執(zhí)行這么慢？

構(gòu)造一個(gè)表，這個(gè)表有兩個(gè)字段id和c，并且在里面插入了10萬行記錄

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;

CREATE DEFINER=`root`@`%` PROCEDURE `idata`()
BEGIN
	declare i int;
  set i=1;
  while(i<=100000) do
    insert into t values(i,i);
    set i=i+1;
  end while;
END

1、第一類：查詢長(zhǎng)時(shí)間不返回

select * from t3 where id=1;

查詢結(jié)果長(zhǎng)時(shí)間不返回，使用show processlist命令，查看當(dāng)前語句處于什么狀態(tài)

1）、等MDL鎖

如下圖所示，使用show processlist;命令查看Waiting for table metadata lock的示意圖

這個(gè)狀態(tài)表示現(xiàn)在有一個(gè)線程正在表t上請(qǐng)求或者持有MDL寫鎖，把select語句堵住了

場(chǎng)景復(fù)現(xiàn)：

sessionA通過lock table命令持有表t的MDL寫鎖，而sessionB的查詢需要獲取MDL讀鎖。所以，sessionB進(jìn)入等待狀態(tài)

這類問題的處理方式，就是找到誰持有MDL寫鎖，然后把它kill掉。但是由于show processlist的結(jié)果里，sessionA的Command列是Sleep，導(dǎo)致查找起來很不方便，可以通過查詢sys.schema_table_lock_waits這張表直接找出造成阻塞的process id，把這個(gè)連接kill命令斷開即可（MySQL啟動(dòng)時(shí)需要設(shè)置performance_schema=on，相比于設(shè)置為off會(huì)有10%左右的性能損失）

select blocking_pid from sys.schema_table_lock_waits;

2）、等flush

在表t上執(zhí)行如下的SQL語句：

select * from information_schema.processlist where id=1;

查出來某個(gè)線程狀態(tài)為Waiting for table flush

這個(gè)狀態(tài)表示的是，現(xiàn)在有一個(gè)線程政要對(duì)表t做flush操作。MySQL里面對(duì)表做flush操作的用法，一般有以下兩個(gè)：

flush tables t with read lock;flush tables with read lock;

這兩個(gè)flush語句，如果指定表t的話，代表的是只關(guān)閉表t；如果沒有指定具體的表名，則表示關(guān)閉MySQL里所有打開的表

但是正常情況下這兩個(gè)語句執(zhí)行起來都很快，除非它們被別的線程堵住了

所以，出現(xiàn)Waiting for table flush狀態(tài)的可能情況是：有一個(gè)flush tables命令被別的語句堵住了，然后它有堵住了select語句

場(chǎng)景復(fù)現(xiàn)：

sessionA中，每行調(diào)用一次sleep(1)，這樣這個(gè)語句默認(rèn)要執(zhí)行10萬秒，在這期間表t一直是被sessionA打開著。然后，sessionB的flush tables t再去關(guān)閉表t，就需要等sessionA的查詢結(jié)束。這樣sessionC要再次查詢的話，就會(huì)被flush命令堵住了

3）、等行鎖

select * from t where id=1 lock in share mode;

由于訪問id=1這個(gè)記錄時(shí)要加讀鎖，如果這時(shí)候已經(jīng)有一個(gè)事務(wù)在這行記錄上持有一個(gè)寫鎖，select語句就會(huì)被堵住

場(chǎng)景復(fù)現(xiàn)：

sessionA啟動(dòng)了事務(wù)，占有寫鎖，還不提交，是導(dǎo)致sessionB被堵住的原因

2、第二類：查詢慢

sessionA先用start transaction with consistent snapshot命令開啟一個(gè)事務(wù)，建立事務(wù)的一致性讀（又稱為快照讀。使用的是MVCC機(jī)制讀取undo log中的已經(jīng)提交的數(shù)據(jù)。所以它的讀取是非阻塞的），之后sessionB執(zhí)行update語句

sessionB執(zhí)行完100萬次update語句后，生成100萬個(gè)回滾日志

帶lock in share mode的語句是當(dāng)前讀，因此會(huì)直接讀到1000001這個(gè)結(jié)果，速度很快；而select * from t where id=1這個(gè)語句是一致性讀，因此需要從1000001開始，依次執(zhí)行undo log，執(zhí)行了100萬次以后，才將1這個(gè)結(jié)果返回

五、間隙鎖

建表和初始化語句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

這個(gè)表除了主鍵id外，還有一個(gè)索引c

為了解決幻讀問題，InnoDB引入了間隙鎖，鎖的就是兩個(gè)值之間的空隙

當(dāng)執(zhí)行select * from t where d=5 for update的時(shí)候，就不止是給數(shù)據(jù)庫中已有的6個(gè)記錄加上了行鎖，還同時(shí)加了7個(gè)間隙鎖。這樣就確保了無法再插入新的記錄

行鎖分成讀鎖和寫鎖

跟間隙鎖存在沖突關(guān)系的是往這個(gè)間隙中插入一個(gè)記錄這個(gè)操作。間隙鎖之間不存在沖突關(guān)系

這里sessionB并不會(huì)被堵住。因?yàn)楸韙里面并沒有c=7會(huì)這個(gè)記錄，因此sessionA加的是間隙鎖(5,10)。而sessionB也是在這個(gè)間隙加的間隙鎖。它們用共同的目標(biāo)，保護(hù)這個(gè)間隙，不允許插入值。但它們之間是不沖突的

間隙鎖和行鎖合稱next-key lock，每個(gè)next-key lock是前開后閉區(qū)間。表t初始化以后，如果用select * from t for update要把整個(gè)表所有記錄鎖起來，就形成了7個(gè)next-key lock，分別是(-∞,0]、(0,5]、(5,10]、(10,15]、(15,20]、(20, 25]、(25, +supremum]。因?yàn)?∞是開區(qū)間，在實(shí)現(xiàn)上，InnoDB給每個(gè)索引加了一個(gè)不存在的最大值supremum，這樣才符合都是前開后閉區(qū)間

間隙鎖和next-key lock的引入，解決了幻讀的問題，但同時(shí)也帶來了一些困擾

間隙鎖導(dǎo)致的死鎖：

1.sessionA執(zhí)行select … for update語句，由于id=9這一行并不存在，因此會(huì)加上間隙鎖(5,10)

2.sessionB執(zhí)行select … for update語句，同樣會(huì)加上間隙鎖(5,10)，間隙鎖之間不會(huì)沖突

3.sessionB試圖插入一行(9,9,9)，被sessionA的間隙鎖擋住了，只好進(jìn)入等待

4.sessionA試圖插入一行(9,9,9)，被sessionB的間隙鎖擋住了

兩個(gè)session進(jìn)入互相等待狀態(tài)，形成了死鎖

間隙鎖的引入可能會(huì)導(dǎo)致同樣的語句鎖住更大的范圍，這其實(shí)是影響并發(fā)度的

在讀提交隔離級(jí)別下，不存在間隙鎖

六、next-key lock

表t的建表語句和初始化語句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、next-key lock加鎖規(guī)則

• 原則1：加鎖的基本單位是next-key lock，next-key lock是前開后閉區(qū)間

• 原則2：查找過程中訪問到的對(duì)象才會(huì)加鎖

• 優(yōu)化1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock退化為行鎖

• 優(yōu)化2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock退化為間隙鎖

• 一個(gè)bug：唯一索引上的范圍查詢會(huì)訪問到不滿足條件的第一個(gè)值為止

這個(gè)規(guī)則只限于MySQL5.x系列<=5.7.24，8.0系列<=8.0.13

2、案例一：等值查詢間隙鎖

1.由于表t中沒有id=7的記錄，根據(jù)原則1，加鎖單位是next-key lock，sessionA加鎖范圍就是(5,10]

2.根據(jù)優(yōu)化2，這是一個(gè)等值查詢(id=7)，而id=10不滿足查詢條件，next-key lock退化成間隙鎖，因此最終加鎖的范圍是(5,10)

所以，sessionB要往這個(gè)間隙里面插入id=8的記錄會(huì)被鎖住，但是sessionC修改id=10這行是可以的

3、案例二：非唯一索引等值鎖

1.根據(jù)原則1，加鎖單位是next-key lock，因此會(huì)給(0,5]加上next-key lock

2.c是普通索引，因此訪問c=5這一條記錄是不能馬上停下來的，需要向右遍歷，查到c=10才放棄。根據(jù)原則2，訪問到的都要加鎖，因此要給(5,10]加next-key lock

3.根據(jù)優(yōu)化2，等值判斷，向右遍歷，最后一個(gè)值不滿足c=5這個(gè)等值條件，因此退化成間隙鎖(5,10)

4.根據(jù)原則2，只有訪問到的對(duì)象才會(huì)加鎖，這個(gè)查詢使用覆蓋索引，并不需要訪問主鍵索引，所以主鍵索引上沒有任何鎖，這就是為什么sessionB的update語句可以執(zhí)行完成

鎖是加在索引上的，在這個(gè)例子中，lock in share mode只鎖覆蓋索引，但是如果是for update，系統(tǒng)會(huì)認(rèn)為你接下來要更新數(shù)據(jù)，因此會(huì)順便給主鍵索引上滿足條件的行加上行鎖，這樣的話sessionB的update語句會(huì)被阻塞住。如果你要用 lock in share mode 來給行加讀鎖避免數(shù)據(jù)被更新的話，就必須得繞過覆蓋索引的優(yōu)化，在查詢字段中加入索引中不存在的字段

4、案例三：主鍵索引范圍鎖

1.開始執(zhí)行的時(shí)候，要找到第一個(gè)id=10的行，因此本該是next-key lock(5,10]。根據(jù)優(yōu)化1，主鍵id上的等值條件，退化成行鎖，只加了id=10這一行的行鎖

2.范圍查詢就往后繼續(xù)找，找到id=15這一行停下來，因此需要加next-key lock(10,15]

所以，sessionA這時(shí)候鎖的范圍就是主鍵索引上，行鎖id=10和next-key lock(10,15]

5、案例四：非唯一索引范圍鎖

這次sessionA用字段c來判斷，加鎖規(guī)則跟案例三唯一的不同是：在第一次用c=10定位記錄的時(shí)候，索引c上加上(5,10]這個(gè)next-key lock后，由于索引c是非唯一索引，沒有優(yōu)化規(guī)則，因此最終sessionA加的鎖是索引c上的(5,10]和(10,15]這兩個(gè)next-key lock

6、案例五：唯一索引范圍鎖bug

sessionA是一個(gè)范圍查詢，按照原則1的話，應(yīng)該是索引id上只加(10,15]這個(gè)next-key lock，并且因?yàn)閕d是唯一鍵，所以循環(huán)判斷到id=15這一行就應(yīng)該停止了

但是實(shí)現(xiàn)上，InnoDB會(huì)掃描到第一個(gè)不滿足條件的行為止，也就是id=20。而且由于這是個(gè)范圍掃描，因此索引id上的(15,20]這個(gè)next-key lock也會(huì)被鎖上

所以，sessionB要更新id=20這一行是會(huì)被鎖住的。同樣地，sessionC要插入id=16的一行，也會(huì)被鎖住

7、案例六：非唯一索引上存在等值的例子

insert into t values(30,10,30);

新插入的這一行c=10，現(xiàn)在表里有兩個(gè)c=10的行。雖然有兩個(gè)c=10，但是它們的主鍵值id是不同的，因此這兩個(gè)c=10的記錄之間也是有間隙的

sessionA在遍歷的時(shí)候，先訪問第一個(gè)c=10的記錄。根據(jù)原則1，這里加的是(c=5,id=5)到(c=10,id=10)這個(gè)next-key lock。然后sessionA向右查找，直到碰到(c=15,id=15)這一行，循環(huán)才結(jié)束。根據(jù)優(yōu)化2，這是一個(gè)等值查詢，向右查找到了不滿足條件的行，所以會(huì)退化成(c=10,id=10)到(c=15,id=15)的間隙鎖

也就是說，這個(gè)delete語句在索引c上的加鎖范圍，就是下圖中藍(lán)色區(qū)域覆蓋的部分，這個(gè)藍(lán)色區(qū)域左右兩邊都是虛線，表示開區(qū)間

8、案例七：limit語句加鎖

加了limit 2的限制，因此在遍歷到(c=10,id=30)這一行之后，滿足條件的語句已經(jīng)有兩條，循環(huán)就結(jié)束了。因此，索引c上的加鎖范圍就變成了從(c=5,id=5)到(c=10,id=30)這個(gè)前開后閉區(qū)間，如下圖所示：

再刪除數(shù)據(jù)的時(shí)候盡量加limit，這樣不僅可以控制刪除數(shù)據(jù)的條數(shù)，讓操作更安全，還可以減小加鎖的范圍

9、案例八：一個(gè)死鎖的例子

1.sessionA啟動(dòng)事務(wù)后執(zhí)行查詢語句加lock in share mode，在索引c上加了next-key lock(5,10]和間隙鎖(10,15)

2.sessionB的update語句也要在索引c上加next-key lock(5,10]，進(jìn)入鎖等待

3.然后sessionA要再插入(8,8,8)這一行，被sessionB的間隙鎖鎖住。由于出現(xiàn)了死鎖，InnoDB讓sessionB回滾

sessionB的加next-key lock(5,10]操作，實(shí)際上分成了兩步，先是加(5,10)間隙鎖，加鎖成功；然后加c=10的行鎖，這時(shí)候才被鎖住的

七、用動(dòng)態(tài)的觀點(diǎn)看加鎖

表t的建表語句和初始化語句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(0,0,0),(5,5,5),
(10,10,10),(15,15,15),(20,20,20),(25,25,25);

1、不等號(hào)條件里的等值查詢

begin;
select * from t where id>9 and id<12 order by id desc for update;

利用上面的加鎖規(guī)則，這個(gè)語句的加鎖范圍是主鍵索引上的(0,5]、(5,10]和(10,15)。加鎖單位是next-key lock，這里用到了優(yōu)化2，即索引上的等值查詢，向右遍歷的時(shí)候id=15不滿足條件，所以next-key lock退化為了間隙鎖(10,15)

1.首先這個(gè)查詢語句的語義是order by id desc，要拿到滿足條件的所有行，優(yōu)化器必須先找到第一個(gè)id<12的值

2.這個(gè)過程是通過索引樹的搜索過程得到的，在引擎內(nèi)部，其實(shí)是要找到id=12的這個(gè)值，只是最終沒找到，但找到了(10,15)這個(gè)間隙

3.然后根據(jù)order by id desc，再向左遍歷，在遍歷過程中，就不是等值查詢了，會(huì)掃描到id=5這一行，所以會(huì)加一個(gè)next-key lock (0,5]

在執(zhí)行過程中，通過樹搜索的方式定位記錄的時(shí)候，用的是等值查詢的方法

2、等值查詢的過程

begin;
select id from t where c in(5,20,10) lock in share mode;

這條in語句使用了索引c并且rows=3，說明這三個(gè)值都是通過B+樹搜索定位的

在查找c=5的時(shí)候，先鎖住了(0,5]。但是因?yàn)閏不是唯一索引，為了確認(rèn)還有沒有別的記錄c=5，就要向右遍歷，找到c=10確認(rèn)沒有了，這個(gè)過程滿足優(yōu)化2，所以加了間隙鎖(5,10)。執(zhí)行c=10會(huì)這個(gè)邏輯的時(shí)候，加鎖的范圍是(5,10]和(10,15)，執(zhí)行c=20這個(gè)邏輯的時(shí)候，加鎖的范圍是(15,20]和(20,25)

這條語句在索引c上加的三個(gè)記錄鎖的順序是：先加c=5的記錄鎖，再加c=10的記錄鎖，最后加c=20的記錄鎖

select id from t where c in(5,20,10) order by c desc for update;

由于語句里面是order by c desc，這三個(gè)記錄鎖的加鎖順序是先鎖c=20，然后c=10，最后是c=5。這兩條語句要加鎖相同的資源，但是加鎖順序相反。當(dāng)這兩條語句并發(fā)執(zhí)行的時(shí)候，就可能出現(xiàn)死鎖

八、insert語句的鎖為什么這么多？

1、insert … select語句

表t和t2的表結(jié)構(gòu)、初始化數(shù)據(jù)語句如下：

CREATE TABLE `t` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `c` int(11) DEFAULT NULL,
  `d` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `c` (`c`)
) ENGINE=InnoDB;

insert into t values(null, 1,1);
insert into t values(null, 2,2);
insert into t values(null, 3,3);
insert into t values(null, 4,4);

create table t2 like t;

在可重復(fù)讀隔離級(jí)別下，binlog_format=statement時(shí)執(zhí)行下面這個(gè)語句時(shí)，需要對(duì)表t的所有行和間隙加鎖

insert into t2(c,d) select c,d from t;

2、insert循環(huán)寫入

要往表t2中插入一行數(shù)據(jù)，這一行的c值是表t中c值的最大值加1，SQL語句如下：

insert into t2(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

這個(gè)語句的加鎖范圍，就是表t索引c上的(3,4]和(4,supermum]這兩個(gè)next-key lock，以及主鍵索引上id=4這一行

執(zhí)行流程是從表t中按照索引c倒序嗎，掃描第一行，拿到結(jié)果寫入到表t2中，因此整條語句的掃描行數(shù)是1

但如果要把這一行的數(shù)據(jù)插入到表t中的話：

insert into t(c,d)  (select c+1, d from t force index(c) order by c desc limit 1);

explain結(jié)果中的Extra字段中Using temporary字段，表示這個(gè)語句用到了臨時(shí)表

執(zhí)行流程如下：

1.創(chuàng)建臨時(shí)表，表里有兩個(gè)字段c和d

2.按照索引c掃描表t，依次取c=4、3、2、1，然后回表，讀到c和d的值寫入臨時(shí)表

3.由于語義里面有l(wèi)imit 1，所以只取了臨時(shí)表的第一行，再插入到表t中

這個(gè)語句會(huì)導(dǎo)致在表t上做全表掃描，并且會(huì)給索引c上的所有間隙都加上共享的next-key lock。所以，這個(gè)語句執(zhí)行期間，其他事務(wù)不能在這個(gè)表上插入數(shù)據(jù)

需要臨時(shí)表是因?yàn)檫@類一邊遍歷數(shù)據(jù)，一邊更新數(shù)據(jù)的情況，如果讀出來的數(shù)據(jù)直接寫回原表，就可能在遍歷過程中，讀到剛剛插入的記錄，新插入的記錄如果參與計(jì)算邏輯，就跟語義不符

3、insert唯一鍵沖突

sessionA執(zhí)行的insert語句，發(fā)生唯一鍵沖突的時(shí)候，并不只是簡(jiǎn)單地報(bào)錯(cuò)返回，還在沖突的索引上加了鎖，sessionA持有索引c上的(5,10]共享next-key lock（讀鎖）

在sessionA執(zhí)行rollback語句回滾的時(shí)候，sessionC幾乎同時(shí)發(fā)現(xiàn)死鎖并返回

1.在T1時(shí)刻，啟動(dòng)sessionA，并執(zhí)行insert語句，此時(shí)在索引c的c=5上加了記錄鎖。這個(gè)索引是唯一索引，因此退化為記錄鎖

2.在T2時(shí)刻，sessionA回滾。這時(shí)候，sessionB和sessionC都試圖繼續(xù)執(zhí)行插入操作，都要加上寫鎖。兩個(gè)session都要等待對(duì)方的行鎖，所以就出現(xiàn)了死鎖

4、insert into … on duplicate key update

上面這個(gè)例子是主鍵沖突后直接報(bào)錯(cuò)，如果改寫成

insert into t values(11,10,10) on duplicate key update d=100;

就會(huì)給索引c上(5,10]加一個(gè)排他的next-key lock（寫鎖）

insert into … on duplicate key update的語義邏輯是，插入一行數(shù)據(jù)，如果碰到唯一鍵約束，就繼續(xù)執(zhí)行后面的更新語句。如果有多個(gè)列違反了唯一性索引，就會(huì)按照索引的順序，修改跟第一個(gè)索引沖突的行

表t里面已經(jīng)有了(1,1,1)和(2,2,2)這兩行，執(zhí)行這個(gè)語句效果如下：

主鍵id是先判斷的，MySQL認(rèn)為這個(gè)語句跟id=2這一行沖突，所以修改的是id=2的行

到此，相信大家對(duì)“MySQL中的鎖可以分成幾類”有了更深的了解，不妨來實(shí)際操作一番吧！這里是億速云網(wǎng)站，更多相關(guān)內(nèi)容可以進(jìn)入相關(guān)頻道進(jìn)行查詢，關(guān)注我們，繼續(xù)學(xué)習(xí)！