深入了解MySQL中的鎖（全局鎖、表級鎖、行鎖）

本篇文章帶大家了解一下mysql中的鎖，介紹一下mysql的全局鎖、表級鎖和行鎖，希望對大家有所幫助！

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

一、全局鎖

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

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

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

• 如果在主庫上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺
• 如果在從庫上備份，那么在備份期間從庫不能執(zhí)行主庫同步過來的binlog，會導(dǎo)致主從延遲

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

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

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

• 在有些系統(tǒng)中，readonly的值會被用來做其他邏輯，比如用來判斷一個(gè)庫是主庫還是備庫。因此修改global變量的方式影響面更大
• 在異常處理機(jī)制上有差異。如果執(zhí)行Flush tables with read lock命令之后由于客戶端發(fā)生異常斷開，那么MySQL會自動釋放這個(gè)全局鎖，整個(gè)庫回到可以正常更新的狀態(tài)。而將整個(gè)庫設(shè)置為readonly之后，如果客戶端發(fā)生異常，則數(shù)據(jù)庫會一直保持readonly狀態(tài)，這樣會導(dǎo)致整個(gè)庫長時(shí)間處于不可寫狀態(tài)，風(fēng)險(xiǎn)較高

二、表級鎖

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

表鎖的語法是lock tables … read/write。可以用unlock tables主動釋放鎖，也可以在客戶端斷開的時(shí)候自動釋放。lock tables語法除了會限制別的線程的讀寫外，也限定了本線程接下來的操作對象

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

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

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

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

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

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

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

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

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

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

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

三、行鎖

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

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

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

事務(wù)A持有的兩個(gè)記錄的行鎖都是在commit的時(shí)候才釋放的，事務(wù)B的update語句會被阻塞，直到事務(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、死鎖和死鎖檢測

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

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

• 一種策略是，直接進(jìn)入等待，直到超時(shí)。這個(gè)超時(shí)時(shí)間可以通過參數(shù)innodb_lock_wait_timeout來設(shè)置
• 另一種策略是，發(fā)起死鎖檢測，發(fā)現(xiàn)死鎖后，主動回滾死鎖鏈條中的某一個(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才會超時(shí)退出，然后其他線程才有可能繼續(xù)執(zhí)行。對于在線服務(wù)來說，這個(gè)等待時(shí)間往往是無法接受的

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

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

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

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

2.控制并發(fā)度

3.將一行改成邏輯上的多行來減少鎖沖突。以影院賬戶為例，可以考慮放在多條記錄上，比如10個(gè)記錄，影院的賬戶總額等于這10個(gè)記錄的值的總和。這樣每次要給影院賬戶加金額的時(shí)候，隨機(jī)選其中一條記錄來加。這樣每次沖突概率變成員原來的1/10，可以減少鎖等待個(gè)數(shù)，也就減少了死鎖檢測的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<h3><strong>1、第一類：查詢長時(shí)間不返回</strong></h3><pre class="brush:sql;toolbar:false;">select?*?from?t3?where?id=1;

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

1）、等MDL鎖

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

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

場景復(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啟動時(shí)需要設(shè)置performance_schema=on，相比于設(shè)置為off會有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è)線程政要對表t做flush操作。MySQL里面對表做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語句

場景復(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要再次查詢的話，就會被flush命令堵住了

3）、等行鎖

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

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

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

sessionA啟動了事務(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)前讀，因此會直接讀到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并不會被堵住。因?yàn)楸韙里面并沒有c=7會這個(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這一行并不存在，因此會加上間隙鎖(5,10)

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

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

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

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

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

在讀提交隔離級別下，不存在間隙鎖

六、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：查找過程中訪問到的對象才會加鎖
• 優(yōu)化1：索引上的等值查詢，給唯一索引加鎖的時(shí)候，next-key lock退化為行鎖
• 優(yōu)化2：索引上的等值查詢，向右遍歷時(shí)且最后一個(gè)值不滿足等值條件的時(shí)候，next-key lock退化為間隙鎖
• 一個(gè)bug：唯一索引上的范圍查詢會訪問到不滿足條件的第一個(gè)值為止

這個(gè)規(guī)則只限于MySQL5.x系列

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的記錄會被鎖住，但是sessionC修改id=10這行是可以的

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

1.根據(jù)原則1，加鎖單位是next-key lock，因此會給(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，只有訪問到的對象才會加鎖，這個(gè)查詢使用覆蓋索引，并不需要訪問主鍵索引，所以主鍵索引上沒有任何鎖，這就是為什么sessionB的update語句可以執(zhí)行完成

鎖是加在索引上的，在這個(gè)例子中，lock in share mode只鎖覆蓋索引，但是如果是for update，系統(tǒng)會認(rèn)為你接下來要更新數(shù)據(jù)，因此會順便給主鍵索引上滿足條件的行加上行鎖，這樣的話sessionB的update語句會被阻塞住。如果你要用 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會掃描到第一個(gè)不滿足條件的行為止，也就是id=20。而且由于這是個(gè)范圍掃描，因此索引id上的(15,20]這個(gè)next-key lock也會被鎖上

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

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è)等值查詢，向右查找到了不滿足條件的行，所以會退化成(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啟動事務(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í)候才被鎖住的

七、用動態(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、不等號條件里的等值查詢

begin; select?*?from?t?where?id&gt;9?and?id<p>利用上面的加鎖規(guī)則，這個(gè)語句的加鎖范圍是主鍵索引上的(0,5]、(5,10]和(10,15)。加鎖單位是next-key lock，這里用到了優(yōu)化2，即索引上的等值查詢，向右遍歷的時(shí)候id=15不滿足條件，所以next-key lock退化為了間隙鎖(10,15)</p><p><img src="https://img.php.cn/upload/article/000/000/024/7bf85b91cea9e2dddc8a22e887dfc75e-27.png" alt="在這里插入圖片描述"></p><p>1.首先這個(gè)查詢語句的語義是order by id desc，要拿到滿足條件的所有行，優(yōu)化器必須先找到第一個(gè)id</p><p>2.這個(gè)過程是通過索引樹的搜索過程得到的，在引擎內(nèi)部，其實(shí)是要找到id=12的這個(gè)值，只是最終沒找到，但找到了(10,15)這個(gè)間隙</p><p>3.然后根據(jù)order by id desc，再向左遍歷，在遍歷過程中，就不是等值查詢了，會掃描到id=5這一行，所以會加一個(gè)next-key lock (0,5]</p><p>在執(zhí)行過程中，通過樹搜索的方式定位記錄的時(shí)候，用的是等值查詢的方法</p><h3><strong>2、等值查詢的過程</strong></h3><pre class="brush:sql;toolbar:false;">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會這個(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ù)讀隔離級別下，binlog_format=statement時(shí)執(zhí)行下面這個(gè)語句時(shí)，需要對表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è)語句會導(dǎo)致在表t上做全表掃描，并且會給索引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í)候，并不只是簡單地報(bào)錯(cuò)返回，還在沖突的索引上加了鎖，sessionA持有索引c上的(5,10]共享next-key lock（讀鎖）

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

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

2.在T2時(shí)刻，sessionA回滾。這時(shí)候，sessionB和sessionC都試圖繼續(xù)執(zhí)行插入操作，都要加上寫鎖。兩個(gè)session都要等待對方的行鎖，所以就出現(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;

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

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

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

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

思考題：

1、如果要刪除一個(gè)表里面的前10000行數(shù)據(jù)，有以下三種方法可以做到：

• 第一種，直接執(zhí)行delete from T limit 10000;
• 第二種，在一個(gè)連接中循環(huán)執(zhí)行20次delete from T limit 500；
• 第三種，在20個(gè)連接中同時(shí)執(zhí)行delete from T limit 500；

選擇哪一種方式比較好？

參考答案：

第一種方式，單個(gè)語句占用時(shí)間長，鎖的時(shí)間也比較長，而且大事務(wù)還會導(dǎo)致主從延遲

第三種方式，會人為造成鎖沖突

第二種方式相對較好

更多編程相關(guān)知識，請?jiān)L問：mysql教程！！