MySQL學(xué)習(xí)之MySQL基本架構(gòu)與鎖
MySql架構(gòu)
- SQL Layer
- Connection Pool : 連接池,用于接收連接請(qǐng)求和管理連接。
- ManagementService&Utilities 管理服務(wù)組件和工具組件,主要提供了一些備份,安全,主從,集群,等功能。
- SQL Interface:主要提供了SQL語(yǔ)句接口。包括SQL解析器,優(yōu)化器,緩存等。將我們輸入的SQL語(yǔ)句,解析成節(jié)點(diǎn)樹(shù),然后傳遞給存儲(chǔ)引擎執(zhí)行。
- Storage Engine Layer:可插拔式的存儲(chǔ)引擎。常用的有InnoDB,MyISAM等
- File System : 底層文件系統(tǒng)。保存數(shù)據(jù),索引,日志等文件。
MySQL鎖
鎖的分類
按粒度分
全局鎖(鎖database,由SQL Layer層實(shí)現(xiàn))
表級(jí)鎖(由SQL Layer層實(shí)現(xiàn))
- 表數(shù)據(jù)鎖
- 元數(shù)據(jù)鎖
行級(jí)鎖(由存儲(chǔ)引擎實(shí)現(xiàn),如InnoDB):
可以鎖行,也可以鎖行與行之間的間隙
按功能分
- 共享鎖(S鎖)(讀鎖)
- 排他鎖(X鎖)(寫(xiě)鎖)
鎖的演示
表鎖
表數(shù)據(jù)鎖
-- 對(duì)product表加讀鎖 -- 其他進(jìn)程仍然可以對(duì)product表進(jìn)行讀取,但不能寫(xiě)(包括加讀鎖本身這個(gè)進(jìn)程,也無(wú)法寫(xiě)) lock table product read; -- 加鎖后,該進(jìn)程只能訪問(wèn)product表,無(wú)法訪問(wèn)其他表。 select * from seller; ERROR 1100 (HY000): Table 'seller' was not locked with LOCK TABLES -- 當(dāng)然可以讓當(dāng)前進(jìn)程給seller表加鎖 lock table seller read; -- 如此以來(lái),便可以訪問(wèn)seller表,但由于一個(gè)進(jìn)程只能持有一個(gè)表鎖,故原先的product表鎖被釋放,product表無(wú)法訪問(wèn) -- 其他未持有鎖的session可以訪問(wèn)任意表 -- 釋放鎖 unlock tables; -- 或者 unlock table; -- 上面兩句效果一樣 -- 只會(huì)釋放當(dāng)前連接進(jìn)程所持有的表鎖,而不是釋放所有鎖, -- 對(duì)product表加寫(xiě)鎖 lock table product write; -- 其他線程對(duì)product表既不能讀,也不能寫(xiě) -- 查看表鎖狀態(tài) show open tables; -- 注意,一個(gè)連接進(jìn)程,最多只能持有1個(gè)表鎖
- 元數(shù)據(jù)鎖(Meta Data Lock)
MDL無(wú)需顯式使用,在訪問(wèn)一個(gè)表時(shí),會(huì)自動(dòng)加元數(shù)據(jù)鎖。MDL的作用是為了保證讀寫(xiě)的正確性。
MDL讀鎖:在對(duì)某個(gè)表進(jìn)行增刪改查操作時(shí),加MDL讀鎖。
MDL寫(xiě)鎖:在對(duì)某個(gè)表的結(jié)構(gòu)進(jìn)行修改(DDL)時(shí),加MDL寫(xiě)鎖。
讀鎖之間不互斥,讀寫(xiě),寫(xiě)寫(xiě)之間互斥。這樣是為了保證對(duì)表結(jié)構(gòu)操作的安全性。
MDL可以認(rèn)為是表結(jié)構(gòu)鎖。需要改表結(jié)構(gòu)時(shí),自動(dòng)加MDL寫(xiě)鎖,其他時(shí)候加MDL讀鎖
DML,DQL語(yǔ)句,會(huì)自動(dòng)加MDL的讀鎖
DDL語(yǔ)句,會(huì)自動(dòng)加MDL的寫(xiě)鎖
-- 線程A begin; select * from product; -- 在一個(gè)事務(wù)內(nèi),MDL鎖是一直被持有的 -- 此時(shí)另起一個(gè),線程B alter table product add type varchar(10); -- 執(zhí)行上面的sql,會(huì)發(fā)現(xiàn)被阻塞住,因?yàn)榫€程B這一句需要MDL寫(xiě)鎖 -- 再回到線程A commit; -- 線程A提交事務(wù)后,釋放MDL讀鎖 -- 此時(shí)能看到線程B的sql執(zhí)行成功
注意:如上圖所示,session A 和 session B可以正常執(zhí)行,session C 就被阻塞了,因?yàn)閟ession C需要申請(qǐng)MDL寫(xiě)鎖,關(guān)鍵是,session D也會(huì)被阻塞。當(dāng)session A 提交后,會(huì)先執(zhí)行session D,最后再執(zhí)行session C。
觀察發(fā)現(xiàn),如果先開(kāi)啟事務(wù),在事務(wù)里執(zhí)行DDL,先不提交當(dāng)前事務(wù)。再另起一個(gè)線程,執(zhí)行DML,發(fā)現(xiàn)DML不會(huì)被阻塞。
這是因?yàn)镈DL在執(zhí)行完成后,會(huì)自動(dòng)立刻commit(自動(dòng)commit后會(huì)釋放MDL寫(xiě)鎖)。
申請(qǐng)MDL鎖的操作會(huì)形成一個(gè)隊(duì)列,隊(duì)列中寫(xiě)鎖獲取優(yōu)先級(jí)高于讀鎖。一旦出現(xiàn)寫(xiě)鎖等待,不但當(dāng)前操作會(huì)被阻塞,同時(shí)還會(huì)阻塞后續(xù)該表的所有操作。事務(wù)一旦申請(qǐng)到MDL鎖后,直到事務(wù)執(zhí)行完才會(huì)將鎖釋放。(這里有種特殊情況如果事務(wù)中包含DDL操作,mysql會(huì)在DDL操作語(yǔ)句執(zhí)行前,隱式提交commit,以保證該DDL語(yǔ)句操作作為一個(gè)單獨(dú)的事務(wù)存在,同時(shí)也保證元數(shù)據(jù)排他鎖的釋放。
行鎖
行鎖是由存儲(chǔ)引擎實(shí)現(xiàn)的。InnoDB支持行鎖和事務(wù),MyISAM不支持行鎖和事務(wù)。
InnoDB的行鎖是通過(guò)給索引項(xiàng)加鎖實(shí)現(xiàn)的。所以,若不是通過(guò)索引條件檢索的數(shù)據(jù),InnoDB會(huì)使用表鎖。
InnoDB的行鎖
按照鎖定范圍分3種:
- Record Lock:記錄鎖,鎖定索引中的一條記錄
- Gap Lock:間隙鎖,鎖定記錄間的間隙
- Next-Key Locks:記錄鎖+間隙鎖組合
按功能分為
- 共享讀鎖
- 排他寫(xiě)鎖
DML語(yǔ)句(INSERT/UPDATE/DELETE)會(huì)自動(dòng)加上排他鎖
對(duì)于普通SELECT語(yǔ)句,InnoDB不加鎖(是通過(guò)MVCC的一致性非鎖定讀的方式完成的,這個(gè)后序再做總結(jié)),可以通過(guò)以下方式,手動(dòng)添加鎖
-- 共享讀鎖 SELECT * FROM product LOCK IN SHARE MODE; -- 排他寫(xiě)鎖 SELECT * FROM product FOR UPDATE; -- 查看行鎖情況 show status like '%innodb_row_lock%';
意向鎖
是InnoDB實(shí)現(xiàn)的表級(jí)鎖,在內(nèi)部使用,無(wú)需用戶干預(yù)。
MySQL有多粒度的鎖實(shí)現(xiàn),即行鎖和表鎖。那么意向鎖存在的意義是為了協(xié)調(diào)行鎖和表鎖。試想事務(wù)A申請(qǐng)了某表某一行的寫(xiě)鎖X,事務(wù)B申請(qǐng)了該表的寫(xiě)鎖X,那么事務(wù)B按理說(shuō)也能修改事務(wù)A鎖定的某一行,這就產(chǎn)生了沖突。如果沒(méi)有意向鎖,某事務(wù)申請(qǐng)表鎖時(shí),可能就得一行一行的掃描,看看是不是所有行都沒(méi)有鎖,所有行都沒(méi)鎖時(shí),才能成功加表鎖。這樣效率就會(huì)很低。
所以意向鎖的作用就是表明某個(gè)事務(wù)有加行鎖的意圖,即,有人鎖住了某一行,或者將要鎖住某一行,這樣在其他人在加表鎖時(shí),就能直接根據(jù)意向鎖的情況,判斷是否能夠加表鎖,而不必一行一行掃描了。
意向共享鎖 (IS):加行共享鎖前,必須先取得IS鎖
意向排他鎖(IX):加行排他鎖前,必須先取得IX鎖
意向鎖的作用主要是為了在針對(duì)全表操作時(shí)獲得性能提升。
比如:事務(wù)A對(duì)某一行加了鎖(無(wú)論是讀鎖還是寫(xiě)鎖),事務(wù)B嘗試加表鎖,這時(shí)如果沒(méi)有意向鎖,就需要遍歷檢測(cè)每一行是否持有行鎖,這樣性能是極低的。
意向鎖只和表鎖互斥。
對(duì)于上表,可以做如下理解:
若某個(gè)表存在IS鎖,說(shuō)明有個(gè)事務(wù)對(duì)某一行加了讀鎖,此時(shí)若要對(duì)該表加表鎖,只能加S鎖,不能加X(jué)鎖。所以IS和S兼容,和X互斥。
若某個(gè)表存在IX鎖,說(shuō)明有個(gè)事務(wù)對(duì)某一行加了寫(xiě)鎖,此時(shí)若要對(duì)該表加表鎖,都會(huì)被阻塞,S鎖和X鎖都不能加。所以IX和S和X都互斥。
IX和IX可以共存,可以理解為,有2個(gè)事務(wù)分別對(duì)不同的行加了寫(xiě)鎖。
到此這篇關(guān)于MySQL學(xué)習(xí)之MySQL基本架構(gòu)與鎖的文章就介紹到這了,更多相關(guān)MySQL基本架構(gòu)與鎖內(nèi)容請(qǐng)搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!
相關(guān)文章
5個(gè)常用的MySQL數(shù)據(jù)庫(kù)管理工具詳細(xì)介紹
本篇文章是對(duì)5個(gè)常用的MySQL數(shù)據(jù)庫(kù)管理工具進(jìn)行了詳細(xì)的分析介紹,需要的朋友參考下2013-06-06阿里云 Centos7.3安裝mysql5.7.18 rpm安裝教程
這篇文章主要介紹了阿里云 Centos7.3安裝mysql5.7.18 rpm安裝教程,需要的朋友可以參考下2017-06-06關(guān)于join?on和where執(zhí)行順序分析
這篇文章主要介紹了join?on和where執(zhí)行順序,如果是inner?join,?放on和放where產(chǎn)生的結(jié)果一樣,?執(zhí)行計(jì)劃也是一樣,但推薦使用on,本文對(duì)join?on和where執(zhí)行順序給大家詳細(xì)講解,需要的朋友可以參考下2023-03-03詳解mysql的limit經(jīng)典用法及優(yōu)化實(shí)例
這篇文章詳細(xì)介紹了mysql的limit經(jīng)典用法及優(yōu)化實(shí)例,有需要的朋友可以參考一下2013-09-09MySQL數(shù)據(jù)庫(kù)維護(hù)中監(jiān)控所用到的常用命令
這篇文章主要介紹額MySQL監(jiān)控時(shí)常用的的幾個(gè)MySQL命令,需要的朋友可以收藏下2013-08-08MySql獲取某個(gè)字段存在于哪個(gè)表的sql語(yǔ)句
本文為大家詳細(xì)介紹下通過(guò)MySql查詢某個(gè)字段所在表是哪一個(gè),具體的sql語(yǔ)句如下,感興趣的朋友可以參考下,希望對(duì)大家有所幫助2013-07-07