一丶為什么數(shù)據(jù)庫(kù)需要鎖

數(shù)據(jù)庫(kù)鎖設(shè)計(jì)的初衷是處理并發(fā)問(wèn)題。作為多用戶共享 的資源，當(dāng)出現(xiàn)并發(fā)訪問(wèn)的時(shí)候，數(shù)據(jù)庫(kù)需要合理地控制資源的訪問(wèn)規(guī)則。而鎖就是用來(lái)實(shí) 現(xiàn)這些訪問(wèn)規(guī)則的重要數(shù)據(jù)結(jié)構(gòu)。

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

二丶全局鎖&全庫(kù)邏輯備份

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖。全局鎖的典型使用場(chǎng)景是，做全庫(kù)邏輯備份，全庫(kù)邏輯備份有以下幾種方式：

1.Flush tables with read lock (FTWRL)

Flush tables with read lock (FTWRL)可以讓整個(gè)庫(kù)處于只讀狀態(tài)的時(shí)候，使用這個(gè)命令，之后其他線程的數(shù)據(jù)更新語(yǔ)句（數(shù)據(jù)的增刪改）、數(shù)據(jù)定義語(yǔ)句（包括建表、修改表結(jié)構(gòu)等）和更新類(lèi)事務(wù)的提交語(yǔ)句講被阻塞。之所以備份需要加全局鎖是為了保證數(shù)據(jù)的一致性，

2.mysqldump –single-transaction

使用此命令會(huì)先啟動(dòng)一個(gè)事務(wù)拿到一致性視圖。由于 MVCC 的支持， 這個(gè)過(guò)程中其他線程可以進(jìn)行正常操作，但是使用mysqldump –single-transaction的前提是需要支持事務(wù)，如果存在MyISAM引擎的表，并不能保證一致性。

3.set global readonly=true

set global readonly=true也可以保證全庫(kù)只讀，主要有兩個(gè)原因：

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

三丶鎖解決并發(fā)事務(wù)帶來(lái)的問(wèn)題

1.鎖解決臟寫(xiě)

多個(gè)未提交事務(wù)修改相繼對(duì)同一條記錄進(jìn)行改動(dòng)的時(shí)候，需要進(jìn)行排隊(duì)，排隊(duì)過(guò)程其實(shí)就是通過(guò)為記錄加鎖實(shí)現(xiàn)的。當(dāng)一個(gè)事務(wù)想修改記錄的時(shí)候，首先需要看下有沒(méi)有于記錄相關(guān)聯(lián)的鎖結(jié)構(gòu)，如果沒(méi)有，那么會(huì)在內(nèi)存中生成與之對(duì)應(yīng)的鎖結(jié)構(gòu)

可能多個(gè)事務(wù)同時(shí)修改同一記錄，會(huì)產(chǎn)生多個(gè)鎖結(jié)構(gòu)，其中只有一個(gè)事務(wù)可以獲取到鎖，其is_waiting為false，其他事務(wù)is_waiting為true，當(dāng)獲取鎖的事務(wù)結(jié)束后，會(huì)喚醒其他等待的事務(wù)

2.臟讀，幻讀，不可重復(fù)讀如何避免

mysql innodb 在repeatable隔離級(jí)別下很大程度下避免了幻讀（后續(xù)詳細(xì)說(shuō)到）

2.1 mvcc解決臟讀，幻讀，不可重復(fù)讀問(wèn)題

Mysql InnoDB多版本并發(fā)控制MVCC 這篇博客我們?cè)敿?xì)說(shuō)到了mvcc的實(shí)現(xiàn)的原理，簡(jiǎn)單來(lái)說(shuō)就是在事務(wù)運(yùn)行時(shí)會(huì)生成read view，其包含四個(gè)部分

• m_ids:在生成read view時(shí)，當(dāng)前系統(tǒng)中活躍的讀寫(xiě)事務(wù)id列表
• min_trx_id:生成read view時(shí)，當(dāng)前系統(tǒng)中活躍的讀寫(xiě)事務(wù)中最小事務(wù)id，也就是m_ids中的最小值
• max_trx_id:生成read view時(shí)，系統(tǒng)應(yīng)該分配給下一個(gè)事務(wù)的事務(wù)id值
• creator_trx_id:生成該read view的事務(wù)的事務(wù)id

查詢語(yǔ)句只可以查詢生成read view 時(shí)刻可以看到的數(shù)據(jù)，及事務(wù)id小于min_trx_id中的數(shù)據(jù)，寫(xiě)操作則針對(duì)最新的數(shù)據(jù)。非當(dāng)前讀的普通語(yǔ)句在讀數(shù)據(jù)的時(shí)候，生成read view的時(shí)機(jī)不同，在可重復(fù)讀的時(shí)候只會(huì)在第一次讀?。ㄈ绻褂?code>start transaction with consistent snapshot則是在事務(wù)啟動(dòng)時(shí)）生成一個(gè)read view 后續(xù)不變，達(dá)到可重復(fù)讀的目的，對(duì)于讀已提交，則是每次讀取都會(huì)生成新的read view，從刷新未提交事務(wù)集合，和min_trx_id，讀取到已經(jīng)提交的數(shù)據(jù)。使用mvcc讓讀寫(xiě)并不沖突，數(shù)據(jù)庫(kù)的并發(fā)能力更強(qiáng)。

2.2讀寫(xiě)均加鎖

比如在銀行存款業(yè)務(wù)中，我們需要先讀取賬戶余額，然后加上新增存款，然后進(jìn)行寫(xiě)回操作，整個(gè)流程中，我們不希望存在另外一個(gè)ATM進(jìn)行存取的操作，讀寫(xiě)都需要排隊(duì)，這時(shí)候就得使用鎖。

上面我們說(shuō)了，寫(xiě)操作進(jìn)行排隊(duì)可以解決臟寫(xiě)，那么臟讀，不可重復(fù)讀，幻讀是怎么使用讀寫(xiě)加鎖解決昵？

臟讀產(chǎn)生的原因是，當(dāng)前事務(wù)讀取到了另外一個(gè)事務(wù)沒(méi)有提交的數(shù)據(jù)，那么只需要另外一個(gè)事務(wù)對(duì)操作的記錄加鎖，當(dāng)前事務(wù)無(wú)法獲取到鎖，自然就不會(huì)發(fā)生臟讀。

不可重復(fù)讀產(chǎn)生的原因是，當(dāng)前事務(wù)先讀取了一條記錄，然后存在另外一個(gè)事務(wù)修改了此記錄的數(shù)據(jù)，那么只要當(dāng)前事務(wù)對(duì)記錄進(jìn)行加鎖，自然后續(xù)的事務(wù)將無(wú)法修改，自然不會(huì)發(fā)生不可重復(fù)讀。

幻讀產(chǎn)生的原因是，當(dāng)前事務(wù)根據(jù)條件查詢得到一批數(shù)據(jù)，然后后續(xù)事務(wù)新增了滿足這些條件的數(shù)據(jù)，導(dǎo)致再次查詢時(shí)發(fā)現(xiàn)多了一些數(shù)據(jù)，如同出現(xiàn)了幻覺(jué)。這里加鎖則不是單對(duì)記錄加鎖，而是要鎖住一個(gè)范圍，讓其他事務(wù)無(wú)法插入數(shù)據(jù)，從而解決幻讀（后續(xù)會(huì)說(shuō)到這種鎖）

很明顯讀寫(xiě)都加鎖，并發(fā)能力不及mvcc

3.一致性讀

事務(wù)利用MVCC進(jìn)行讀取操作稱(chēng)為一致性讀（又稱(chēng)一致性無(wú)鎖讀，快照讀）,基本上所有的普通讀在可重復(fù)讀，讀提交隔離級(jí)別下，都是一致性讀。

4.鎖定讀

4.1共享鎖&獨(dú)占鎖

共享鎖：簡(jiǎn)稱(chēng)S鎖，事務(wù)要讀取一條記錄的時(shí)候需要先獲取到記錄的共享鎖

獨(dú)占鎖：簡(jiǎn)稱(chēng)x鎖，當(dāng)事務(wù)需要改動(dòng)記錄的時(shí)候，需要先獲取記錄的獨(dú)占鎖

共享鎖和共享鎖兼容，獨(dú)占鎖和獨(dú)占鎖，獨(dú)占鎖和共享鎖互斥

4.2鎖定讀的語(yǔ)句

select xxx lock in share mode可以對(duì)讀取的記錄加S鎖

select xxx for update可以對(duì)讀取的記錄加X(jué)鎖

4.3 寫(xiě)操作

delete

刪除記錄首先要在b+數(shù)中定位到這條記錄的位置，然后獲取x鎖，然后指向delete mark（標(biāo)記記錄被刪除）

update

如果未修改鍵值，并且修改前后數(shù)據(jù)的存儲(chǔ)空間大小不變，那么現(xiàn)在b+樹(shù)上定位記錄，然后加x鎖。反之需要在b+樹(shù)上定位數(shù)據(jù)，然后把記錄徹底刪除嗎，然后再插入一條新的記錄，對(duì)新增的這條數(shù)據(jù)加x鎖

insert

新插入記錄一般都是加隱式鎖（后面說(shuō)）不需要在內(nèi)存中生成對(duì)應(yīng)的鎖結(jié)構(gòu)。

三丶InnoDB表鎖

1.表級(jí)S，X鎖

innodb支持表級(jí)鎖，也支持行級(jí)鎖，表級(jí)鎖粒度相對(duì)更粗，占用資源較少。使用表級(jí)鎖效果相當(dāng)于為表中的所有記錄加鎖，所以性能比較差。

表級(jí)S鎖，X鎖

使用Lock Tables t Read，innodb存儲(chǔ)引擎會(huì)對(duì)表t加共享鎖

使用Lock tables t write，innodb存儲(chǔ)引擎會(huì)對(duì)表t加獨(dú)占鎖

類(lèi)似于Java中的讀寫(xiě)鎖，共享鎖和共享鎖不互斥，獨(dú)占鎖和獨(dú)占鎖，獨(dú)占鎖和共享鎖互斥。

2.表級(jí)意向鎖

innodb存儲(chǔ)引擎中，當(dāng)對(duì)表中某些記錄加S鎖之前，會(huì)在表上加上一個(gè)IS鎖，同樣加X(jué)鎖之前會(huì)加表級(jí)IX鎖，這里的I表示意向鎖，SX表示共享還是互斥，表級(jí)意向鎖存在的目的是后續(xù)對(duì)表加S鎖，X鎖的時(shí)候，可以快速判斷表中是否存在加鎖的記錄，避免遍歷每一個(gè)記錄查看是否被加鎖。

3.表級(jí)AUTO-INC鎖

mysql可以為某列執(zhí)行Auto Increment自增，系統(tǒng)給自增列賦值的實(shí)現(xiàn)方式主要存在兩種

使用AUTO-INC鎖，執(zhí)行插入語(yǔ)句的時(shí)候加一個(gè)表級(jí)AUTO-INC鎖，然后為每條待插入的記錄中的自增列，進(jìn)行遞增賦值，單個(gè)插入語(yǔ)句執(zhí)行結(jié)束后釋放AUTO-INC鎖。這樣會(huì)導(dǎo)致其他事務(wù)的插入被阻塞。 采用輕量級(jí)AUTO-INC鎖，在為插入列賦值結(jié)束后，就釋放輕量級(jí)鎖，而不是插入語(yǔ)句執(zhí)行完后才釋放 四.MDL

一般情況下 對(duì)某一個(gè)表執(zhí)行增刪改查的時(shí)候，都不會(huì)加表鎖，但是執(zhí)行一些DDL修改表結(jié)構(gòu)，刪除表時(shí)，其他事務(wù)對(duì)這表的增刪改查發(fā)生阻塞。這是由MDL鎖實(shí)現(xiàn)的，MDL鎖也分為讀鎖和寫(xiě)鎖，在進(jìn)行crud操作的時(shí)候，會(huì)加MDL讀鎖，進(jìn)行DDL的時(shí)候會(huì)加MDL寫(xiě)鎖。

我們需要注意MDL讀鎖寫(xiě)鎖是互斥的

如圖四個(gè)不同的session先后依次執(zhí)行語(yǔ)句，其中A，B都是獲取MDL讀鎖，互不阻塞，隨即C獲取MDL寫(xiě)鎖，這時(shí)候C會(huì)被阻塞，這一阻塞不打緊，還會(huì)阻塞后續(xù)獲取MDL讀鎖的事務(wù)，造成整個(gè)表不可用。這啟發(fā)了我們，在做DDL的時(shí)候要解決長(zhǎng)事務(wù)，事務(wù)不提交，就會(huì)一直占著MDL鎖。在 MySQL 的 information_schema 庫(kù)的innodb_trx 表中，可以查到當(dāng)前執(zhí)行中的事務(wù)。如果要做 DDL 變更的表剛好有長(zhǎng)事務(wù)在執(zhí)行，要考慮先暫停 DDL，或者 kill 掉這個(gè)長(zhǎng)事務(wù)。

五丶innodb 行鎖

1.Record Lock

官方名稱(chēng)Lock_REC_NOT_GAP

記錄鎖有S鎖和X鎖，S型記錄鎖之間可以共享，X型記錄鎖和S型記錄鎖，X型記錄鎖互斥

2.GAP Lock

innodb的可重復(fù)讀級(jí)別，使用詞鎖解決幻讀問(wèn)題，前面我們說(shuō)過(guò)，其難點(diǎn)在于，加鎖的時(shí)候幻影記錄還未出現(xiàn)。官方使用Lock_GAP實(shí)現(xiàn)如下操作

此處的gap鎖可以反之其他事務(wù)在number為8記錄前面的間隙插入新的記錄，在區(qū)間（3，8）內(nèi)無(wú)法進(jìn)行插入操作，當(dāng)另外一個(gè)事務(wù)要插入number為4的記錄時(shí)，首先需要定位到該條記錄的下一條記錄，也就是number為8的記錄，此時(shí)number為8的記錄具備gap鎖，所以將阻塞插入操作，直到gap鎖被釋放，其他事務(wù)才能進(jìn)行插入。
gap鎖出現(xiàn)的目的，就是為了防止插入幻影記錄，如果對(duì)記錄上gap鎖，并不會(huì)限制其他事務(wù)對(duì)記錄加記錄鎖。

innodb有兩個(gè)虛擬的記錄Infimum(虛擬最小)，Supermun(虛擬最大)當(dāng)我們想在(xx,正無(wú)窮)范圍鎖住幻影記錄時(shí)就可以對(duì)Supermun加gap鎖。

3.Next-Key Lock

Next-Key Lock = 記錄鎖 + gap鎖，既鎖住記錄，也鎖住記錄之前的間隙

4.Insert Intention Lock

插入意向鎖，表示事務(wù)想在某個(gè)間隙插入新的記錄，但是當(dāng)前處于等待狀態(tài)。

比如事務(wù)A持有（4，8）范圍內(nèi)的gap鎖，事務(wù)B和C，想插入（4，8）范圍內(nèi)的記錄，就會(huì)在內(nèi)存中生成事務(wù)B，C對(duì)應(yīng)的插入意向鎖，當(dāng)前事務(wù)A釋放gap鎖的時(shí)候，將喚醒事務(wù)B和C，事務(wù)B和C可以同時(shí)獲取插入意向鎖，然后進(jìn)行插入。插入意向鎖并不會(huì)阻止對(duì)記錄繼續(xù)上鎖。

5.隱式鎖

為事務(wù)生成內(nèi)存中的鎖結(jié)構(gòu)并不是一個(gè)0成本的事情，為了節(jié)省這個(gè)成本，提出隱式鎖的概念。

當(dāng)一個(gè)事務(wù)插入語(yǔ)一條記錄A，其他事務(wù)

select xxx Lock in share mode讀取記錄A（獲取記錄A的S鎖），或者使用select xxx for update（獲取記錄A的X鎖） 立即修改記錄A（獲取x鎖）

對(duì)于聚簇索引來(lái)說(shuō)，有一個(gè)隱藏列trx_id此列存儲(chǔ)著最后更改記錄的事務(wù)id，在當(dāng)前事務(wù)A插入記錄后，便是存儲(chǔ)著當(dāng)前事務(wù)A的id，其他事務(wù)B企圖獲取x鎖，s鎖的時(shí)候，就需要下先看一下，trx_id隱藏列對(duì)應(yīng)的事務(wù)是否存活，如果不是那么正常獲取，反之需要為當(dāng)前事務(wù)A創(chuàng)建一個(gè)x鎖內(nèi)存結(jié)構(gòu)，并標(biāo)記is_waiting為false，然后事務(wù)B將為自己創(chuàng)建一個(gè)鎖結(jié)構(gòu)，is_waiting 為true然后事務(wù)B進(jìn)入等待狀態(tài)

對(duì)于二級(jí)索引來(lái)說(shuō)，其不具備隱藏列trx_id但是在二級(jí)索引頁(yè)面的page header中的page_maxt_trx_id屬性，記錄了改動(dòng)頁(yè)面最大的事務(wù)id，如果其屬性值小于當(dāng)前最小的活躍事務(wù)id，那么說(shuō)明對(duì)頁(yè)面的改動(dòng)事務(wù)已經(jīng)提交，否則需要定位到二級(jí)索引記錄，然后回表對(duì)聚簇索引進(jìn)行上述聚簇索引的操作。

一個(gè)事務(wù)對(duì)新插入的記錄不需要顯示的加鎖，由于事務(wù)id的存在相當(dāng)于加了一個(gè)隱式鎖，別的事務(wù)需要加S鎖或者X鎖的時(shí)候，先幫之前的事務(wù)生成鎖結(jié)構(gòu)，然后為自己生成鎖結(jié)構(gòu)，再進(jìn)入阻塞狀態(tài)。隱式鎖起到了延遲加鎖的作用，也許別的事務(wù)不會(huì)獲取于隱式鎖沖突的鎖，這時(shí)候可以減少內(nèi)存中生成鎖結(jié)構(gòu)。

到此這篇關(guān)于一篇文章帶你了解清楚Mysql 鎖的文章就介紹到這了,更多相關(guān)Mysql 鎖詳解內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！