MySQL學(xué)習(xí)之MySQL基本架構(gòu)與鎖

更新時(shí)間：2023年03月30日 14:55:21 作者：摳腳的大灰狼

這篇文章主要介紹了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鎖）（寫鎖）

鎖的演示

表鎖

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

-- 對(duì)product表加讀鎖
-- 其他進(jìn)程仍然可以對(duì)product表進(jìn)行讀取，但不能寫(包括加讀鎖本身這個(gè)進(jìn)程，也無(wú)法寫)
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表加寫鎖
lock table product write;
-- 其他線程對(duì)product表既不能讀，也不能寫


-- 查看表鎖狀態(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的作用是為了保證讀寫的正確性。

MDL讀鎖：在對(duì)某個(gè)表進(jìn)行增刪改查操作時(shí)，加MDL讀鎖。

MDL寫鎖：在對(duì)某個(gè)表的結(jié)構(gòu)進(jìn)行修改(DDL)時(shí)，加MDL寫鎖。

讀鎖之間不互斥，讀寫，寫寫之間互斥。這樣是為了保證對(duì)表結(jié)構(gòu)操作的安全性。

MDL可以認(rèn)為是表結(jié)構(gòu)鎖。需要改表結(jié)構(gòu)時(shí)，自動(dòng)加MDL寫鎖，其他時(shí)候加MDL讀鎖

DML,DQL語(yǔ)句，會(huì)自動(dòng)加MDL的讀鎖

DDL語(yǔ)句，會(huì)自動(dòng)加MDL的寫鎖

-- 線程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寫鎖

-- 再回到線程A
commit;
-- 線程A提交事務(wù)后，釋放MDL讀鎖
-- 此時(shí)能看到線程B的sql執(zhí)行成功

注意：如上圖所示，session A 和 session B可以正常執(zhí)行，session C 就被阻塞了，因?yàn)閟ession C需要申請(qǐng)MDL寫鎖，關(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寫鎖）。

申請(qǐng)MDL鎖的操作會(huì)形成一個(gè)隊(duì)列，隊(duì)列中寫鎖獲取優(yōu)先級(jí)高于讀鎖。一旦出現(xiàn)寫鎖等待，不但當(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：記錄鎖+間隙鎖組合

按功能分為

共享讀鎖
排他寫鎖

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;

-- 排他寫鎖
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)了某表某一行的寫鎖X，事務(wù)B申請(qǐng)了該表的寫鎖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ú)論是讀鎖還是寫鎖），事務(wù)B嘗試加表鎖，這時(shí)如果沒(méi)有意向鎖，就需要遍歷檢測(cè)每一行是否持有行鎖，這樣性能是極低的。

意向鎖只和表鎖互斥。