07恶守、MySQL鎖

一、簡(jiǎn)介

類似于Java中同步代碼塊用到的鎖贡必,MySQL中為了保證數(shù)據(jù)一致性也使用鎖,一般有

全局鎖:每次操作鎖住數(shù)據(jù)庫(kù)所有的表庸毫,不能進(jìn)行DML仔拟、DDL操作,事務(wù)都阻塞飒赃,
表級(jí)鎖:每次操作鎖住整張表利花,
行級(jí)鎖:每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)

二、全局鎖

1载佳、使用全局鎖

(1)

啟動(dòng)三個(gè)cmd窗口炒事,第一個(gè)登錄切換數(shù)據(jù)庫(kù)后,做鎖操作


image.png

(2)

第二個(gè)登錄切換數(shù)據(jù)庫(kù)后蔫慧,執(zhí)行查詢可以挠乳,執(zhí)行插入就卡住


image.png

(3)

第三個(gè)是cmd指令,執(zhí)行備份數(shù)據(jù)庫(kù)姑躲,不用進(jìn)入mysql指令


image.png

(4)

之后就可以去d盤看有沒有一個(gè)叫scott_copy.sql的文件

(5)

在第一個(gè)窗口中釋放鎖


image.png

(6)

再去第二個(gè)窗口看一下睡扬,發(fā)現(xiàn)剛才卡住的插入語(yǔ)句終于執(zhí)行完了


image.png

2、不使用全局鎖備份數(shù)據(jù)

使用全局鎖有一些弊端

備份時(shí)不能辦理業(yè)務(wù)黍析,
從庫(kù)備份時(shí)卖怜,不能執(zhí)行主庫(kù)傳遞過來的更新日志文件,會(huì)造成主從延遲

為了避免這個(gè)問題阐枣,當(dāng)數(shù)據(jù)庫(kù)引擎是InnoDB時(shí)马靠,可以在備份時(shí)加上參數(shù)--single-transaction,保證不加鎖也能完成一致性


image.png

三蔼两、表級(jí)鎖

鎖定粒度大甩鳄,發(fā)生沖突概率最高,并發(fā)度最低额划,主要有

表鎖
元數(shù)據(jù)鎖
意向鎖

1娩贷、表鎖

(1)表共享讀鎖

為表加鎖后,當(dāng)前客戶端能查詢锁孟,插入報(bào)錯(cuò)


image.png

其他客戶端也能查詢彬祖,插入時(shí)會(huì)卡住


image.png

鎖解除后茁瘦,剛才卡住的插入語(yǔ)句也執(zhí)行了


image.png
image.png

(2)表獨(dú)占寫鎖

寫鎖把上邊的read換成write就可以了,這里記下現(xiàn)象储笑,加鎖的客戶端可以讀可以插入甜熔,另一個(gè)客戶端讀、寫都會(huì)卡住

2突倍、元數(shù)據(jù)鎖(又叫mdl)

元數(shù)據(jù)鎖不用手動(dòng)腔稀,訪問表時(shí)系統(tǒng)自動(dòng)加的,作用是維護(hù)元數(shù)據(jù)表的數(shù)據(jù)一致性羽历,當(dāng)表有活動(dòng)事務(wù)時(shí)焊虏,不可以對(duì)元數(shù)據(jù)寫入操作。
對(duì)表做DML操作時(shí)秕磷,加MDL讀鎖(共享)诵闭,當(dāng)對(duì)表做DDL時(shí),加MDL寫鎖(排它)

sql類型                                                 鎖類型                                  說明
lock tables read/write                shared_read_only/shared_no_read_write
select澎嚣、select...lock in share mode                 shared_read              與shared_write兼容疏尿,與exclusive互斥
insert、update易桃、delete褥琐、select...for update        shared_write            與shared_write兼容,與exclusive互斥  
alter table...                                       exclusive                            與其他mdl都互斥                   

(1)試驗(yàn)一:

兩個(gè)窗口都開啟事務(wù)晤郑,窗口一執(zhí)行select敌呈,窗口二執(zhí)行select和insert,都成功了造寝,說明是互相兼容的


image.png

image.png

最后執(zhí)行commit;即可驱富。

(2)試驗(yàn)二:

窗口一開啟事務(wù),執(zhí)行select匹舞,窗口二執(zhí)行alter修改表結(jié)構(gòu)褐鸥,窗口一沒提交會(huì)發(fā)現(xiàn)卡住,證明互斥了


image.png

image.png

當(dāng)窗口一提交赐稽,窗口二才會(huì)向下執(zhí)行叫榕。

可以通過select查詢存在的鎖的類型

select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;

(需要指出,mysql5.7中需要開啟鎖監(jiān)控才能記錄)

update performance_schema.setup_instruments set enabled = 'YES' where  name like '%lock%';
image.png

image.png

3姊舵、意向鎖

(1)簡(jiǎn)介

為了避免在執(zhí)行DML語(yǔ)句時(shí)晰绎,加的行鎖與表鎖有沖突,在InnoDB中引入了意向鎖括丁,使得表鎖不用檢查每行是否加了行鎖荞下,使用意向鎖降低表鎖的檢查,提高性能


image.png

假設(shè)一個(gè)窗口要把50部門的地點(diǎn)改為BENXI,會(huì)首先給該行數(shù)據(jù)加一個(gè)行級(jí)鎖尖昏,再給dept表加一個(gè)意向鎖仰税,另一個(gè)窗口假設(shè)要來給dept表加一個(gè)表鎖,此時(shí)若窗口一的意向鎖和窗口二的表鎖兼容抽诉,則可以加陨簇,不兼容,則加不上
意向鎖分為兩種:

意向共享鎖(IS)迹淌,由語(yǔ)句select河绽、select...lock in share mode添加,與表鎖共享鎖(read)兼容唉窃,與表鎖排它鎖(write)互斥
意向排它鎖(IX)耙饰,由語(yǔ)句insert、update纹份、delete苟跪、select...for update添加,與表鎖共享鎖(read)和表鎖排它鎖(write)都互斥矮嫉,意向鎖之間不互斥

使用如下語(yǔ)句檢查意向鎖

//mysql8以上
select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

//mysql8以下
SELECT * FROM performance_schema.data_locks; -- 8.0

(2)試驗(yàn)

①試驗(yàn)一

窗口一開啟事務(wù)并以鎖模式查詢,在查詢語(yǔ)句后加上lock in share mode就會(huì)加上行鎖和意向共享鎖

image.png

窗口二查詢表(意向)鎖和行鎖


image.png

②試驗(yàn)二

窗口一不動(dòng)


image.png

窗口二給表加一個(gè)獨(dú)占寫鎖牍疏,就會(huì)卡住蠢笋,因?yàn)榇翱谝坏逆i沒有釋放

image.png

此時(shí)窗口一提交就好了


image.png

image.png

③試驗(yàn)三

窗口一執(zhí)行DML語(yǔ)句


image.png

窗口二查看鎖的類型


image.png

④試驗(yàn)四

窗口一不動(dòng)


image.png

窗口二給表加一個(gè)共享讀鎖,就會(huì)卡住鳞陨,因?yàn)榇翱谝坏逆i沒有釋放


image.png

此時(shí)窗口一提交就好了
image.png

image.png

最后在窗口二執(zhí)行unlock tables;釋放鎖即可

四昨寞、行級(jí)鎖

行級(jí)鎖,每次執(zhí)行鎖住對(duì)應(yīng)一行數(shù)據(jù)厦滤,鎖定粒度最小援岩,發(fā)生沖突概率最低,并發(fā)度最高掏导,應(yīng)用在InnoDB引擎中
InnoDB引擎中數(shù)據(jù)是基于索引存儲(chǔ)的享怀,行鎖是通過對(duì)索引中的索引項(xiàng)加鎖來實(shí)現(xiàn)的,而不是對(duì)記錄加的鎖趟咆,行級(jí)鎖有以下三種

行鎖添瓷,鎖定單行記錄,防止其他事務(wù)對(duì)該記錄update和delete值纱,在RC和RR隔離級(jí)別下都支持
間隙鎖鳞贷,鎖定索引記錄間的間隙(不含該記錄),確保索引記錄間隙不變防止其他事務(wù)在間隙執(zhí)行insert虐唠,產(chǎn)生幻讀搀愧,RR級(jí)別下支持
臨鍵鎖,行鎖和間隙鎖的結(jié)合,同時(shí)鎖住數(shù)據(jù)和數(shù)據(jù)間的間隙Gap咱筛,RR級(jí)別支持

1搓幌、行鎖

(1)簡(jiǎn)介

行鎖有兩種

共享鎖(S)允許一個(gè)事務(wù)讀一行,防止其它事務(wù)獲取相同數(shù)據(jù)集的排它鎖眷蚓,即當(dāng)前共享鎖兼容其他請(qǐng)求共享鎖鼻种,共享鎖與排它鎖互斥
排它鎖(X)允許獲取排它鎖的事務(wù)更新數(shù)據(jù),阻止其它事務(wù)獲取相同數(shù)據(jù)集的共享鎖和排它鎖沙热,即當(dāng)前排它鎖與其它請(qǐng)求共享鎖和排它鎖都互斥

不同語(yǔ)句鎖的類型具體劃分:

INSERT                                           排它                                  自動(dòng)加鎖
UPDATE                                           排它                                  自動(dòng)加鎖
DELETE                                           排它                                  自動(dòng)加鎖
SELECT                                        不加任何鎖
SELECT...LOCK IN SHARE MODE                      共享                      需要手動(dòng)在SELECT后加LOCK IN SHARE MODE
SELECT...FOR UPDATE                              排它                           需要手動(dòng)在SELECT后加FOR UPDATE

(2)試驗(yàn)

①試驗(yàn)一

窗口一執(zhí)行簡(jiǎn)單查詢


image.png

窗口二查詢鎖狀態(tài)


image.png

結(jié)論:可以發(fā)現(xiàn)沒加鎖

②試驗(yàn)二

窗口一加共享鎖查詢


image.png

窗口二查詢鎖狀態(tài)


image.png

結(jié)論:可以發(fā)現(xiàn)加了一個(gè)共享鎖

窗口二再加共享鎖查詢


image.png

結(jié)論:可以發(fā)現(xiàn)兩個(gè)共享鎖兼容

窗口二提交叉钥,再看鎖狀態(tài)


image.png

結(jié)論:可以發(fā)現(xiàn)只剩窗口一加的共享鎖了

③試驗(yàn)三

窗口一不動(dòng),窗口二執(zhí)行update語(yǔ)句


image.png

結(jié)論:會(huì)卡住篙贸,證明窗口一共享鎖和窗口二排它鎖互斥投队,當(dāng)窗口一提交,窗口二的update才會(huì)執(zhí)行下去


image.png

image.png

窗口二沒提交爵川,排它鎖還在敷鸦,窗口二提交,排他鎖就沒了
image.png

image.png

④試驗(yàn)四

窗口一執(zhí)行update寝贡,沒提交


image.png

窗口二也執(zhí)行update扒披,就會(huì)卡住


image.png

結(jié)論:排它鎖與排它鎖互斥
當(dāng)窗口一commit,窗口二才會(huì)執(zhí)行下去
image.png

image.png

⑤試驗(yàn)五

窗口一以非索引字段dname為查詢條件更新數(shù)據(jù)


image.png

窗口二更改另一條數(shù)據(jù)圃泡,兩個(gè)窗口操作的是不同的數(shù)據(jù)碟案,也被卡住了


image.png

這是因?yàn)?br> 針對(duì)唯一索引進(jìn)行檢索,對(duì)已存在的記錄進(jìn)行等值匹配颇蜡,將自動(dòng)優(yōu)化為行鎖

InnoDB是針對(duì)索引加的鎖价说,如果不通過索引條件檢索記錄,InnoDB會(huì)將表中所有記錄加鎖风秤,此時(shí)就升級(jí)成表鎖


image.png

image.png

當(dāng)窗口一提交鳖目,窗口二才會(huì)執(zhí)行下去
給dname加個(gè)索引,窗口二再執(zhí)行更新就不會(huì)阻塞了
image.png

image.png

2缤弦、間隙鎖领迈、臨鍵鎖

InnoDB在RR級(jí)別運(yùn)行,使用next-key鎖進(jìn)行索引和搜索掃描碍沐,防止幻讀

唯一索引上的等值查詢惦费,給不存在的記錄加鎖時(shí),優(yōu)化為間隙鎖
普通索引上的等值查詢抢韭,向右遍歷時(shí)最后一個(gè)元素不滿足查詢條件薪贫,next-key lock退化為間隙鎖
唯一索引上的范圍查詢,會(huì)訪問到不滿足條件的第一個(gè)元素為止

(2)試驗(yàn)

①試驗(yàn)一

deptno為主鍵刻恭,有唯一索引瞧省,55值不存在扯夭,就會(huì)在50和60之間加一個(gè)間隙鎖(GAP)


image.png

窗口二查看鎖


image.png

當(dāng)窗口二執(zhí)行插入語(yǔ)句時(shí),由于窗口一引發(fā)的間隙鎖鞍匾,插入語(yǔ)句被阻塞卡住
image.png

窗口一提交交洗,窗口二的插入才能正常執(zhí)行


image.png

image.png

②試驗(yàn)二

給dept加一個(gè)int型的age字段,加入如下值


image.png

窗口一中給該字段加一個(gè)普通索引


image.png

窗口一中共享鎖模式查詢
image.png
image.png

說明:
①給查詢到的數(shù)據(jù)這一行加一個(gè)行鎖橡淑,鎖住該行
②給查詢到的數(shù)據(jù)這一行加一個(gè)臨鍵鎖构拳,鎖住該行數(shù)據(jù),鎖住該行和上行數(shù)據(jù)間的間隙
③給查詢到的數(shù)據(jù)這一行和下一行之間間隙加一個(gè)間隙鎖梁棠,鎖住這個(gè)間隙

③試驗(yàn)三

窗口一非等值比較


image.png
image.png

說明:
①給40這條數(shù)據(jù)加一個(gè)行鎖
②給60這條數(shù)據(jù)以及它與上一條數(shù)據(jù)之間的間隙加一個(gè)臨鍵鎖
③supremum pseudo-record正無窮置森,給正無窮以及正無窮到60這條數(shù)據(jù)之間的間隙加一個(gè)臨鍵鎖
④分別給50以及與它上一條數(shù)據(jù)的間隙、58以及與它上一條數(shù)據(jù)的間隙各加一個(gè)臨鍵鎖

總結(jié):間隙鎖唯一目的是防止其它事務(wù)插入間隙符糊,間隙鎖可以共存凫海,一個(gè)事務(wù)的間隙鎖不會(huì)組織另一個(gè)事務(wù)在同一個(gè)間隙上采用間隙鎖

最后編輯于
?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請(qǐng)聯(lián)系作者
  • 序言:七十年代末,一起剝皮案震驚了整個(gè)濱河市男娄,隨后出現(xiàn)的幾起案子行贪,更是在濱河造成了極大的恐慌,老刑警劉巖模闲,帶你破解...
    沈念sama閱讀 211,743評(píng)論 6 492
  • 序言:濱河連續(xù)發(fā)生了三起死亡事件建瘫,死亡現(xiàn)場(chǎng)離奇詭異,居然都是意外死亡尸折,警方通過查閱死者的電腦和手機(jī)啰脚,發(fā)現(xiàn)死者居然都...
    沈念sama閱讀 90,296評(píng)論 3 385
  • 文/潘曉璐 我一進(jìn)店門,熙熙樓的掌柜王于貴愁眉苦臉地迎上來翁授,“玉大人拣播,你說我怎么就攤上這事晾咪∈詹粒” “怎么了?”我有些...
    開封第一講書人閱讀 157,285評(píng)論 0 348
  • 文/不壞的土叔 我叫張陵谍倦,是天一觀的道長(zhǎng)塞赂。 經(jīng)常有香客問我,道長(zhǎng)昼蛀,這世上最難降的妖魔是什么宴猾? 我笑而不...
    開封第一講書人閱讀 56,485評(píng)論 1 283
  • 正文 為了忘掉前任,我火速辦了婚禮叼旋,結(jié)果婚禮上仇哆,老公的妹妹穿的比我還像新娘。我一直安慰自己夫植,他們只是感情好讹剔,可當(dāng)我...
    茶點(diǎn)故事閱讀 65,581評(píng)論 6 386
  • 文/花漫 我一把揭開白布油讯。 她就那樣靜靜地躺著,像睡著了一般延欠。 火紅的嫁衣襯著肌膚如雪陌兑。 梳的紋絲不亂的頭發(fā)上,一...
    開封第一講書人閱讀 49,821評(píng)論 1 290
  • 那天由捎,我揣著相機(jī)與錄音兔综,去河邊找鬼。 笑死狞玛,一個(gè)胖子當(dāng)著我的面吹牛软驰,可吹牛的內(nèi)容都是我干的。 我是一名探鬼主播为居,決...
    沈念sama閱讀 38,960評(píng)論 3 408
  • 文/蒼蘭香墨 我猛地睜開眼碌宴,長(zhǎng)吁一口氣:“原來是場(chǎng)噩夢(mèng)啊……” “哼!你這毒婦竟也來了蒙畴?” 一聲冷哼從身側(cè)響起贰镣,我...
    開封第一講書人閱讀 37,719評(píng)論 0 266
  • 序言:老撾萬榮一對(duì)情侶失蹤,失蹤者是張志新(化名)和其女友劉穎膳凝,沒想到半個(gè)月后碑隆,有當(dāng)?shù)厝嗽跇淞掷锇l(fā)現(xiàn)了一具尸體,經(jīng)...
    沈念sama閱讀 44,186評(píng)論 1 303
  • 正文 獨(dú)居荒郊野嶺守林人離奇死亡蹬音,尸身上長(zhǎng)有42處帶血的膿包…… 初始之章·張勛 以下內(nèi)容為張勛視角 年9月15日...
    茶點(diǎn)故事閱讀 36,516評(píng)論 2 327
  • 正文 我和宋清朗相戀三年上煤,在試婚紗的時(shí)候發(fā)現(xiàn)自己被綠了。 大學(xué)時(shí)的朋友給我發(fā)了我未婚夫和他白月光在一起吃飯的照片著淆。...
    茶點(diǎn)故事閱讀 38,650評(píng)論 1 340
  • 序言:一個(gè)原本活蹦亂跳的男人離奇死亡劫狠,死狀恐怖,靈堂內(nèi)的尸體忽然破棺而出永部,到底是詐尸還是另有隱情独泞,我是刑警寧澤,帶...
    沈念sama閱讀 34,329評(píng)論 4 330
  • 正文 年R本政府宣布苔埋,位于F島的核電站懦砂,受9級(jí)特大地震影響,放射性物質(zhì)發(fā)生泄漏组橄。R本人自食惡果不足惜荞膘,卻給世界環(huán)境...
    茶點(diǎn)故事閱讀 39,936評(píng)論 3 313
  • 文/蒙蒙 一、第九天 我趴在偏房一處隱蔽的房頂上張望玉工。 院中可真熱鬧羽资,春花似錦、人聲如沸遵班。這莊子的主人今日做“春日...
    開封第一講書人閱讀 30,757評(píng)論 0 21
  • 文/蒼蘭香墨 我抬頭看了看天上的太陽(yáng)。三九已至弥激,卻和暖如春进陡,著一層夾襖步出監(jiān)牢的瞬間,已是汗流浹背微服。 一陣腳步聲響...
    開封第一講書人閱讀 31,991評(píng)論 1 266
  • 我被黑心中介騙來泰國(guó)打工趾疚, 沒想到剛下飛機(jī)就差點(diǎn)兒被人妖公主榨干…… 1. 我叫王不留,地道東北人以蕴。 一個(gè)月前我還...
    沈念sama閱讀 46,370評(píng)論 2 360
  • 正文 我出身青樓糙麦,卻偏偏與公主長(zhǎng)得像,于是被迫代替她去往敵國(guó)和親丛肮。 傳聞我的和親對(duì)象是個(gè)殘疾皇子赡磅,可洞房花燭夜當(dāng)晚...
    茶點(diǎn)故事閱讀 43,527評(píng)論 2 349

推薦閱讀更多精彩內(nèi)容