日常項(xiàng)目中有沒有用到mysql的鎖，什么場(chǎng)景用到的？

mysql有哪些鎖呢，從鎖范圍大小可劃分為全局鎖、表級(jí)鎖、行級(jí)鎖，下面按照這個(gè)順序分別給大家介紹一下這些鎖的機(jī)制及其可用的應(yīng)用場(chǎng)景。

說明：本文闡述的mysql鎖機(jī)制是基于mysql的innoDb引擎，未明確指明隔離級(jí)別的默認(rèn)都為可重復(fù)讀。

全局鎖

全局鎖是指對(duì)整個(gè)數(shù)據(jù)庫實(shí)例進(jìn)行加鎖（可理解為庫級(jí)別鎖？），加全局鎖的命令為：flush tables with read lock(FTWRL)。FTWRL鎖定整庫為只讀狀態(tài)，解鎖時(shí)用unlock tables進(jìn)行解鎖，或者客戶端斷開鏈接自動(dòng)釋放鎖。FTWRL做了什么呢？首先這條命令設(shè)置了一個(gè)全局鎖，加完鎖之后要對(duì)內(nèi)存中的臟頁進(jìn)行刷新到磁盤的操作，從而保證磁盤數(shù)據(jù)與內(nèi)存一致，這里設(shè)置全局只讀鎖也是為了保證再刷臟頁的過程中內(nèi)存中的數(shù)據(jù)不會(huì)更改。

    `id` int not null,
    `a` int,
    `b` int,
    primary key(id),
    key(a)
)ENGINE=InnoDB;
create table t2 like t1;

全局讀鎖的設(shè)置要等待數(shù)據(jù)庫的所有查詢都完成之后才能進(jìn)行，全局讀鎖設(shè)置后數(shù)據(jù)庫的任何數(shù)據(jù)修改操作和DDL操作都要等到全局讀鎖釋放后才能繼續(xù)。

全局鎖的典型使用場(chǎng)景是做全庫邏輯備份：1）FTWRL能刷內(nèi)存臟頁到磁盤，使得磁盤的數(shù)據(jù)為最新數(shù)據(jù)。2）FTWRL能鎖定整庫只讀，保證整庫的邏輯視圖一致性。

邏輯備份的其他可能手段：

1. mysqldump
   mysql自帶的mysqldump是采用–single-transaction參數(shù)，獲取一致性視圖，這種方法的優(yōu)勢(shì)在于導(dǎo)數(shù)據(jù)的同時(shí)還能同時(shí)對(duì)數(shù)據(jù)庫進(jìn)行操作。它要求庫中所有的表都支持事務(wù)引擎才行。
2. set global readonly=true
   設(shè)置全庫只讀，也能保證備份過程中數(shù)據(jù)不會(huì)進(jìn)行變更。但這種方式并不適合，原因在于：1）global影響范圍比較大，有些主從模式會(huì)根據(jù)這個(gè)字段的設(shè)置區(qū)分?jǐn)?shù)據(jù)庫的主備。 2）global readonly 對(duì)于有超級(jí)權(quán)限的用戶來說是無效的。 3）一旦備份過程出現(xiàn)異常，readonly變量的值也不會(huì)自動(dòng)恢復(fù)，對(duì)業(yè)務(wù)的影響更大。

表級(jí)鎖

表鎖

表鎖的語法是 lock tables tablename read/write;解除鎖定時(shí)需要手動(dòng)調(diào)用unlock tables或者客戶端斷開時(shí)自動(dòng)釋放。表鎖的不僅限制當(dāng)前線程同時(shí)也限制其他線程的讀寫。讀鎖限制本線程和其他線程可讀，所有線程均不可寫；寫鎖限制只能本線程可讀寫，其他線程不可讀寫表。表鎖的影響范圍還是比較大，常用的場(chǎng)景是在不支持行鎖的表中做線程并發(fā)。

元數(shù)據(jù)鎖

元數(shù)據(jù)鎖（metadata lock)簡(jiǎn)稱MDL鎖，是用來保證表結(jié)構(gòu)在ddl過程中數(shù)據(jù)讀寫的正確性。MDL不需要顯示的調(diào)用添加，在對(duì)數(shù)據(jù)進(jìn)行增刪改查時(shí)會(huì)對(duì)相應(yīng)的表加MDL讀鎖，對(duì)表結(jié)構(gòu)進(jìn)行修改時(shí)，會(huì)對(duì)表加MDL寫鎖。metadata lock讀寫鎖的互斥情況如下：

一種修改表結(jié)構(gòu)引起查詢異常的場(chǎng)景，sessionA開啟了事務(wù)進(jìn)行簡(jiǎn)單查詢，這時(shí)候會(huì)對(duì)t1表加一個(gè)MDL讀鎖，此時(shí)sessionB請(qǐng)求對(duì)t1表進(jìn)行表結(jié)構(gòu)變更，要對(duì)t1表加MDL寫鎖，寫鎖與讀鎖互斥所以要等到sessionA釋放MDL讀鎖，此后其他線程再請(qǐng)求對(duì)t1表的增刪改查均會(huì)因等待MDL寫鎖釋放而被blcoked。這里解釋一下，sessionB雖然還未獲得MDL寫鎖，但由于mysql的鎖隊(duì)列問題，在sessionA釋放鎖之后sessionB會(huì)優(yōu)先獲得鎖。

所以，對(duì)于線上表結(jié)構(gòu)的修改不論大小表，都應(yīng)該謹(jǐn)慎對(duì)待，我們?cè)匍_發(fā)過程中也應(yīng)該注意，1）盡量不要使用長(zhǎng)事務(wù)，能盡早提交的事務(wù)一定要盡早提前，2）熱點(diǎn)表ddl時(shí)設(shè)置等待時(shí)間。

意向鎖

上面所講述的全局鎖、表鎖、MDL鎖都是mysql的server層鎖，意向鎖則是innodb引擎層的鎖，我們知道innodb的數(shù)據(jù)存儲(chǔ)是樹狀的結(jié)構(gòu)，我們?cè)僬?qǐng)求對(duì)樹的最底層（行）添加鎖時(shí)，同時(shí)又要進(jìn)行全表的鎖定讀操作 ，這時(shí)就需要對(duì)數(shù)據(jù)庫的行進(jìn)行遍歷查詢是否有行并鎖住，這是個(gè)非常耗時(shí)且繁瑣的過程，所以innodb引入了意向表鎖，粗粒度的表鎖，當(dāng)線程請(qǐng)求表鎖時(shí)不需要對(duì)表中的每一行查詢是否有排他鎖，直接確認(rèn)表是否有排他意向鎖即可。意向鎖不需要顯示的添加，當(dāng)對(duì)數(shù)據(jù)庫的行請(qǐng)求共享鎖時(shí)，會(huì)自動(dòng)給相應(yīng)的表加上意向共享鎖(IS)，當(dāng)我們請(qǐng)求互斥鎖時(shí)，會(huì)自動(dòng)給相應(yīng)的表加意向互斥鎖(IX)，當(dāng)進(jìn)行全表掃描時(shí)優(yōu)先

#sessionA select * from t1 where id = 1 for update;
#sessionB select * from performance_schema.data_locks\G

意向鎖.png

意向鎖和表鎖的互斥情況如下：

小結(jié)：

• 表鎖是myISam常用的控制并發(fā)的手段。
• MDL鎖保證了數(shù)據(jù)庫讀寫和表結(jié)構(gòu)變更之間的一致性。
• 意向鎖提高了添加表鎖的效率。

行級(jí)鎖

行級(jí)鎖是在MySQL的引擎上實(shí)現(xiàn)的，InnoDB引擎支持行級(jí)鎖，而MyISAM不支持。行級(jí)鎖，顧名思義加在數(shù)據(jù)行上的鎖，mysql的行級(jí)鎖又分為共享鎖和排他鎖。行級(jí)鎖是粒度比較細(xì)的鎖了，它在保證數(shù)據(jù)一致性的同時(shí)也提高了MySQL的最大并發(fā)程度。

兩階段鎖協(xié)議

行鎖總是再需要的時(shí)候加上，但在事務(wù)提交時(shí)才釋放。

共享鎖

共享鎖的啟用方式 select * from t1 lock in share mode。共享鎖常用應(yīng)用場(chǎng)景之一是有外鍵的數(shù)據(jù)行更新和修改時(shí)，外鍵關(guān)聯(lián)的表會(huì)被mysql自動(dòng)加上共享鎖，以防止依賴庫與當(dāng)前數(shù)據(jù)庫數(shù)據(jù)不一致的情況發(fā)生。另一種應(yīng)用場(chǎng)景為當(dāng)前事務(wù)需要讀到行最新的數(shù)據(jù)時(shí)，也可加lock in share mode。

共享鎖與排他鎖互斥，與共享鎖之間兼容，如下sessionA對(duì)id=1加了共享鎖之后，sessionB的Q2語句也要請(qǐng)求id=1行的共享鎖，查詢成功，Q3語句請(qǐng)求id=1行的排他鎖則被阻塞。

加鎖之后的讀操作是讀當(dāng)前已提交的最新版本，這與可重復(fù)度的隔離范圍有點(diǎn)沖突，如下事例，再sessionA中查詢id=1行的結(jié)果為(1,1,1)，此時(shí)并沒有對(duì)該行加鎖，所以sessionB可以對(duì)該行再進(jìn)行修改，修改完后，再隔離級(jí)別為RR的情況下再查詢?cè)撔?Q4),顯示結(jié)果為(1,1,1),加了lock in share mode 一致性讀之后的查詢(Q5),結(jié)果則為(1,4,1)。

排他鎖

排他鎖的顯示啟用方式為select * from t1 where id = 1 for update,會(huì)給對(duì)應(yīng)索引行加一個(gè)互斥寫鎖，排他鎖與共享鎖的互斥情況同MDL的讀寫鎖一致。數(shù)據(jù)的任何增刪改都會(huì)給對(duì)應(yīng)行加一個(gè)排他鎖。當(dāng)需要對(duì)數(shù)據(jù)行進(jìn)行獨(dú)占式修改時(shí)可以使用for update獨(dú)占該行進(jìn)行處理。例如，庫存的車票、商品庫存的修改等。加了排他鎖之后的讀也是讀當(dāng)前已提交的最新版本，同共享鎖一致，這里不再就做事例了。排他鎖與其他線程的共享鎖和排他鎖都互斥。

間隙鎖

幻讀

幻讀是指在同一事務(wù)中兩次不同的查詢出現(xiàn)的結(jié)果可能不一致的情況。如下事例，如果我們?cè)趕essionA中使用了當(dāng)然讀的查詢，在sessionB中再插入新的數(shù)據(jù)滿足sessionA的查詢條件，我們假設(shè)mysql只有行鎖的情況下，在Q1執(zhí)行時(shí)會(huì)產(chǎn)生id = 2的行鎖，此時(shí)插入vlaues(3,3,3)可以成功，那么就出現(xiàn)了Q1和Q3查詢結(jié)果不一致的問題，也就是幻讀。

間隙鎖

mysql解決幻讀問題的機(jī)制是引入間隙鎖，以上執(zhí)行的真實(shí)情況為，sessionB在執(zhí)行Q2時(shí)會(huì)被阻塞，原因在于Q1執(zhí)行的過程中不僅會(huì)給查詢的行加上行鎖，還會(huì)給查詢到的所有行周圍都加上間隙鎖。t1表當(dāng)前的值有(1,1,1) ,(2,2,2)兩個(gè)，所以加鎖的地方有兩個(gè)a=2的行鎖和a索引上（2,+∞)的間隙鎖。間隙鎖與間隙鎖之間是共享的，與間隙鎖互斥的是在間隙區(qū)間插入數(shù)據(jù)。

加鎖原則

• 查詢過程中訪問的對(duì)象才會(huì)加鎖
• 加鎖的基本單位是next-key lock,行鎖+間隙鎖（前開后閉區(qū)間）
• 唯一索引的范圍查詢會(huì)訪問到第一個(gè)不滿足條件的行為止
• 優(yōu)化1：唯一索引上的等值查詢會(huì)退化為行鎖
• 優(yōu)化2：非唯一的普通索引上的查詢會(huì)退化為間隙鎖，即最后一個(gè)不滿足條件的行不會(huì)加鎖。

事例1：

全表掃描，驗(yàn)證是否所有被訪問的記錄都加了鎖&所有間隙都加了鎖。結(jié)果如下所示，驗(yàn)證了規(guī)則1，2

#sessionA
truncate table t1; 
insert into t1 values (1,1,1),(3,3,3),(5,5,5); 
begin; 
select * from t1 for update; 
#sessionB 
select THREAD_ID,OBJECT_SCHEMA,OBJECT_NAME,INDEX_NAME,LOCK_TYPE,LOCK_MODE,LOCK_STATUS,LOCK_DATA from data_locks; 
#sessionC 
insert into t1 values(2,2,2); //blocked

圖1.png

事例2：

查詢b<5的所有記錄，可以看到c=5這一行也加了排他鎖，結(jié)合剛說的加鎖原則我們可以分析一下這個(gè)過程，首先我們查詢的是b的值,b沒有索引，所以從頭遍歷所有數(shù)據(jù)，第一個(gè)數(shù)據(jù)(1,1,1)加上row1的排他鎖,加鎖單位為next-key lock,所以為(-∞,1],符合規(guī)則繼續(xù)下一個(gè)(3,3,3),加鎖(1,3]，符合規(guī)則繼續(xù)下一個(gè)(5,5,5),加鎖(3,5]不符合規(guī)則。繼續(xù)下一個(gè)，數(shù)據(jù)庫已經(jīng)沒有行，加(5,+∞)的間隙鎖。由此可見，沒有索引的查詢是全表掃描，再?zèng)]有索引的列上的一致性查詢會(huì)造成全表的鎖定。

#sessionA select * from t1 where b < 5 for update;

圖2.png

事例3：

把上述事例換成唯一索引上的查詢,結(jié)果如下所示，加鎖范圍為(-1∞],(1,3],間隙鎖(3,5)?？梢姷絘=5行之后并沒有再繼續(xù)，且id=5行也沒有上鎖，驗(yàn)證規(guī)則3、5.

select * from t1 where a < 5 for update ;

圖3.png

事例4：

那我們把條件換成唯一索引上的等值查詢?cè)僭囈幌履?，如下，結(jié)果加鎖只有id=5這一行。驗(yàn)證規(guī)則4。

select * from t1 where id = 5 for update;

圖4.png

鎖問題

mysql的鎖為數(shù)據(jù)庫的并發(fā)帶來了保障的同時(shí)也會(huì)隨之而來帶來很多問題，在不了解鎖或者使用不當(dāng)?shù)那闆r下也會(huì)對(duì)我們的業(yè)務(wù)系統(tǒng)造成影響。

阻塞

查詢阻塞，我們?cè)陂_發(fā)過程中也會(huì)遇到一些即使很簡(jiǎn)單的SQL執(zhí)行起來卻很慢的問題，上面在講述MDL鎖時(shí)就提到過一個(gè)場(chǎng)景，可以再回顧一下，這就是等待MDL鎖造成的查詢阻塞問題。

再比如我們上面總結(jié)片段的事例，一個(gè)事務(wù)在沒有索引的列上進(jìn)行查詢時(shí)，會(huì)造成全表的鎖定，從而導(dǎo)致其他客戶端的修改操作都會(huì)被鎖定，這是行鎖和間隙鎖造成的查詢阻塞。

長(zhǎng)事務(wù)和加表級(jí)鎖是造成阻塞的大部分原因，所以我們?cè)匍_發(fā)過程中應(yīng)盡量避免長(zhǎng)事務(wù)的存在，對(duì)于表級(jí)鎖的使用也要謹(jǐn)慎。一旦出現(xiàn)這種阻塞問題，我們也可以通過mysql的一些系統(tǒng)表信息進(jìn)行查看，及時(shí)kill掉影響業(yè)務(wù)的線程從而避免對(duì)業(yè)務(wù)系統(tǒng)造成比較大的影響。

查看mysql鎖現(xiàn)象的幾種手段

1. show engine innodb status

可以看到當(dāng)然mysql實(shí)例的狀態(tài)，其中就有當(dāng)前正在等待的事務(wù)信息。如下紅框所示，語句select * from t1 where id = 5 for update語句正在等待記錄X鎖，僅行鎖非間隙鎖。

圖5.png

2. select * from information_schema.innodb_trx 可以看到當(dāng)前正在執(zhí)行的事務(wù)及其使用的鎖的個(gè)數(shù)

圖6.png

3. select * from performance_schema.data_locks , metadata_locks, data_lock_waits 分別可以看到行鎖、表鎖及行鎖等待時(shí)間等信息。

圖7.png

死鎖

死鎖產(chǎn)生的原因在于并發(fā)進(jìn)程中兩個(gè)進(jìn)程互相等待對(duì)方釋放自己所需要的鎖從而陷入循環(huán)之中造成死鎖。mysql處理死鎖的手段立馬回滾造成死鎖的事務(wù)中權(quán)重最低的一個(gè)事務(wù)，從而保證其他事務(wù)能夠正常進(jìn)行。

事例1：兩個(gè)進(jìn)程同時(shí)多個(gè)資源的鎖且順序不一致，并發(fā)情況下有可能觸發(fā)。

事例2：兩個(gè)進(jìn)程同時(shí)請(qǐng)求多個(gè)資源鎖分布進(jìn)行。

事例3：查詢數(shù)據(jù)產(chǎn)生間隙鎖，與另一并發(fā)進(jìn)程同時(shí)插入數(shù)據(jù)造成死鎖。