深入理解MySQL的行級(jí)鎖
??概述
鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問某一資源的機(jī)制。在數(shù)據(jù)庫中,除傳統(tǒng)的計(jì)算資源(CPU、RAM、I/O)的爭(zhēng)用以外,數(shù)據(jù)也是一種供許多用戶共享的資源。
如何保證數(shù)據(jù)并發(fā)訪問的一致性、有效性是所有數(shù)據(jù)庫必須解決的一個(gè)問題,鎖沖突也是影響數(shù)據(jù)庫并發(fā)訪問性能的一個(gè)重要因素。從這個(gè)角度來說,鎖對(duì)數(shù)據(jù)庫而言顯得尤其重要,也更加復(fù)雜。
MySQL中的鎖,按照鎖的粒度分,分為以下三類:
- 全局鎖:鎖定數(shù)據(jù)庫中的所有表。
- 表級(jí)鎖:每次操作鎖住某一張表。
- 行級(jí)鎖:每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。
??行級(jí)鎖介紹
行級(jí)鎖,每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。鎖定粒度最小,發(fā)生鎖沖突的概率最低,并發(fā)度最高。應(yīng)用在InnoDB存儲(chǔ)引擎中。
InnoDB的數(shù)據(jù)是基于索引組織的,行鎖是通過對(duì)索引上的索引項(xiàng)加鎖來實(shí)現(xiàn)的,而不是對(duì)記錄加的鎖。
對(duì)于行級(jí)鎖,主要分為以下三類:
行鎖(Record Lock):鎖定單個(gè)行記錄的鎖,防止其他事務(wù)對(duì)此行進(jìn)行update和delete。在RC、RR隔離級(jí)別下都支持。
- 間隙鎖(Gap Lock):鎖定索引記錄間隙(不含該記錄),確保索引記錄間隙不變,防止其他事務(wù)在這個(gè)間隙進(jìn)行insert,產(chǎn)生幻讀。在RR隔離級(jí)別下支持。
臨鍵鎖(Next-Key Lock):行鎖和間隙鎖組合,同時(shí)鎖住數(shù)據(jù),并鎖住數(shù)據(jù)前面的間隙Gap。在RR隔離級(jí)別下支持。
??行鎖
????介紹
InnoDB
實(shí)現(xiàn)了以下兩種類型的行鎖:
- 共享鎖(S):允許一個(gè)事務(wù)去讀一行,阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖。
- 排他鎖(X):允許獲取排他鎖的事務(wù)更新數(shù)據(jù),阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排他鎖。
兩種行鎖的兼容情況如下:
鎖類型 | S | X |
---|---|---|
S | 兼容 | 沖突 |
X | 沖突 | 兼容 |
常見的SQL語句,在執(zhí)行時(shí),所加的行鎖如下:
????演示
默認(rèn)情況下,InnoDB
在 REPEATABLE READ
事務(wù)隔離級(jí)別運(yùn)行,InnoDB
使用 next-key
鎖進(jìn)行搜索和索引掃描,以防止幻讀。
● 針對(duì)唯一索引進(jìn)行檢索時(shí),對(duì)已存在的記錄進(jìn)行等值匹配時(shí),將會(huì)自動(dòng)優(yōu)化為行鎖。
● InnoDB的行鎖是針對(duì)于索引加的鎖,不通過索引條件檢索數(shù)據(jù),那么InnoDB將對(duì)表中的所有記錄加鎖,此時(shí) 就會(huì)升級(jí)為表鎖。
可以通過以下SQL,查看意向鎖及行鎖的加鎖情況:
A. 普通的select語句,執(zhí)行時(shí),不會(huì)加鎖。
B. select…lock in share mode,加共享鎖,共享鎖與共享鎖之間兼容。
共享鎖與排他鎖之間互斥。
客戶端一獲取的是id為1這行的共享鎖,客戶端二是可以獲取id為3這行的排它鎖的,因?yàn)椴皇峭恍袛?shù)據(jù)。 而如果客戶端二想獲取id為1這行的排他鎖,會(huì)處于阻塞狀態(tài),以為共享鎖與排他鎖之間互斥。
C. 排它鎖與排他鎖之間互斥
當(dāng)客戶端一,執(zhí)行update語句,會(huì)為id為1的記錄加排他鎖; 客戶端二,如果也執(zhí)行update語句更新id為1的數(shù)據(jù),也要為id為1的數(shù)據(jù)加排他鎖,但是客戶端二會(huì)處于阻塞狀態(tài),因?yàn)榕潘i之間是互斥的。 直到客戶端一,把事務(wù)提交了,才會(huì)把這一行的行鎖釋放,此時(shí)客戶端二,解除阻塞。
D. 無索引行鎖升級(jí)為表鎖
我們?cè)趦蓚€(gè)客戶端中執(zhí)行如下操作:
在客戶端一中,開啟事務(wù),并執(zhí)行update語句,更新name為L(zhǎng)ily的數(shù)據(jù),也就是id為19的記錄 。然后在客戶端二中更新id為3的記錄,卻不能直接執(zhí)行,會(huì)處于阻塞狀態(tài),為什么呢?
原因就是因?yàn)榇藭r(shí),客戶端一,根據(jù)name字段進(jìn)行更新時(shí),name字段是沒有索引的,如果沒有索引,此時(shí)行鎖會(huì)升級(jí)為表鎖(因?yàn)樾墟i是對(duì)索引項(xiàng)加的鎖,而name沒有索引)。
接下來,我們?cè)籴槍?duì)name字段建立索引,索引建立之后,再次做一個(gè)測(cè)試:
此時(shí)我們可以看到,客戶端一,開啟事務(wù),然后依然是根據(jù)name進(jìn)行更新。而客戶端二,在更新id為3的數(shù)據(jù)時(shí),更新成功,并未進(jìn)入阻塞狀態(tài)。 這樣就說明,我們根據(jù)索引字段進(jìn)行更新操作,就可以避免行鎖升級(jí)為表鎖的情況。
??間隙鎖&臨鍵鎖
默認(rèn)情況下,InnoDB在 REPEATABLE READ事務(wù)隔離級(jí)別運(yùn)行,InnoDB使用 next-key 鎖進(jìn)行搜索和索引掃描,以防止幻讀。
- 索引上的等值查詢(唯一索引),給不存在的記錄加鎖時(shí), 優(yōu)化為間隙鎖 。
- 索引上的等值查詢(非唯一普通索引),向右遍歷時(shí)最后一個(gè)值不滿足查詢需求時(shí),next-key lock 退化為間隙鎖。
- 索引上的范圍查詢(唯一索引)–會(huì)訪問到不滿足條件的第一個(gè)值為止。
注意:
間隙鎖唯一目的是防止其他事務(wù)插入間隙。間隙鎖可以共存,一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用間隙鎖。
示例演示
A. 索引上的等值查詢(唯一索引),給不存在的記錄加鎖時(shí), 優(yōu)化為間隙鎖 。
B. 索引上的等值查詢(非唯一普通索引),向右遍歷時(shí)最后一個(gè)值不滿足查詢需求時(shí),next-key lock 退化為間隙鎖。
介紹分析一下:
我們知道InnoDB的B+樹索引,葉子節(jié)點(diǎn)是有序的雙向鏈表。 假如,我們要根據(jù)這個(gè)二級(jí)索引查詢值為18的數(shù)據(jù),并加上共享鎖,我們是只鎖定18這一行就可以了嗎? 并不是,因?yàn)槭欠俏ㄒ凰饕?,這個(gè)結(jié)構(gòu)中可能有多個(gè)18的存在,所以,在加鎖時(shí)會(huì)繼續(xù)往后找,找到一個(gè)不滿足條件的值(當(dāng)前案例中也就是29)。此時(shí)會(huì)對(duì)18加臨鍵鎖,并對(duì)29之前的間隙加鎖。
C. 索引上的范圍查詢(唯一索引)–會(huì)訪問到不滿足條件的第一個(gè)值為止。
查詢的條件為id>=19,并添加共享鎖。 此時(shí)我們可以根據(jù)數(shù)據(jù)庫表中現(xiàn)有的數(shù)據(jù),將數(shù)據(jù)分為三個(gè)部分:
[19]
(19,25]
(25,+∞]
所以數(shù)據(jù)庫數(shù)據(jù)在加鎖是,就是將19加了行鎖,25的臨鍵鎖(包含25及25之前的間隙),正無窮的臨鍵鎖(正無窮及之前的間隙)。
到此這篇關(guān)于深入理解MySQL的行級(jí)鎖的文章就介紹到這了,更多相關(guān)MySQL 行級(jí)鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
MySQL窗口函數(shù)OVER使用示例詳細(xì)講解
這篇文章主要介紹了MySQL窗口函數(shù)OVER()用法及說明,具有很好的參考價(jià)值,希望對(duì)大家有所幫助。如有錯(cuò)誤或未考慮完全的地方,望不吝賜教2023-01-01Mysql數(shù)據(jù)庫之常用sql語句進(jìn)階與總結(jié)
這篇文章主要介紹了Mysql數(shù)據(jù)庫之常用sql語句,總結(jié)分析了MySQL數(shù)據(jù)庫常用的查詢、條件查詢、排序、連接查詢、子查詢等相關(guān)操作技巧,需要的朋友可以參考下2019-11-11- 在講MySQL的Join語法前還是先回顧一下聯(lián)結(jié)的語法,呵呵,其實(shí)連我自己都忘得差不多了,那就大家一起溫習(xí)吧,這里我有個(gè)比較簡(jiǎn)便的記憶方法,內(nèi)外聯(lián)結(jié)的區(qū)別是內(nèi)聯(lián)結(jié)將去除所有不符合條件的記錄,而外聯(lián)結(jié)則保留其中部分。外左聯(lián)結(jié)與外右聯(lián)結(jié)的區(qū)別在于如果用A左聯(lián)結(jié)B則A中所有記錄都會(huì)保留在結(jié)果中,此時(shí)B中只有符合聯(lián)結(jié)條件的記錄,而右聯(lián)結(jié)相反,這樣也就不會(huì)混淆了。2014-05-05
Windows下通過MySQL Installer安裝MySQL服務(wù)的教程圖解
MYSQL官方提供了Installer方式安裝MYSQL服務(wù)以及其他組件,使的Windows下安裝,卸載,配置MYSQL變得特別簡(jiǎn)單。接下來通過圖文并茂的形式給大家介紹Windows下通過MySQL Installer安裝MySQL服務(wù)的教程,一起看看吧2018-10-10