MySQL學習之MySQL基本架構與鎖

2023-03-31 06:02:00

MySql架構

SQL Layer
- Connection Pool : 連線池，用於接收連線請求和管理連線。
- ManagementService&Utilities 管理服務元件和工具元件，主要提供了一些備份，安全，主從，叢集，等功能。
- SQL Interface：主要提供了SQL語句介面。包括SQL解析器，優化器，快取等。將我們輸入的SQL語句，解析成節點樹，然後傳遞給儲存引擎執行。
Storage Engine Layer：可插拔式的儲存引擎。常用的有InnoDB，MyISAM等
File System : 底層檔案系統。儲存資料，索引，紀錄檔等檔案。

MySQL鎖

鎖的分類

按粒度分

全域性鎖（鎖database，由SQL Layer層實現）
表級鎖（由SQL Layer層實現）
- 表資料鎖
- 後設資料鎖
行級鎖（由儲存引擎實現，如InnoDB）:

可以鎖行，也可以鎖行與行之間的間隙

按功能分

共用鎖（S鎖）（讀鎖）
排他鎖（X鎖）（寫鎖）

鎖的演示

表鎖

表資料鎖

-- 對product表加讀鎖
-- 其他程序仍然可以對product表進行讀取，但不能寫(包括加讀鎖本身這個程序，也無法寫)
lock table product read;
-- 加鎖後，該程序只能存取product表,無法存取其他表。
select * from seller;
ERROR 1100 (HY000): Table 'seller' was not locked with LOCK TABLES
-- 當然可以讓當前程序給seller表加鎖
lock table seller read;
-- 如此以來，便可以存取seller表，但由於一個程序只能持有一個表鎖，故原先的product表鎖被釋放，product表無法存取


-- 其他未持有鎖的session可以存取任意表

-- 釋放鎖
unlock tables;
-- 或者
unlock table; 
-- 上面兩句效果一樣
-- 只會釋放當前連線程序所持有的表鎖，而不是釋放所有鎖，

-- 對product表加寫鎖
lock table product write;
-- 其他執行緒對product表既不能讀，也不能寫


-- 檢視表鎖狀態
show open tables;

-- 注意，一個連線程序，最多隻能持有1個表鎖

後設資料鎖（Meta Data Lock）

MDL無需顯式使用，在存取一個表時，會自動加後設資料鎖。MDL的作用是為了保證讀寫的正確性。

MDL讀鎖：在對某個表進行增刪改查操作時，加MDL讀鎖。

MDL寫鎖：在對某個表的結構進行修改(DDL)時，加MDL寫鎖。

讀鎖之間不互斥，讀寫，寫寫之間互斥。這樣是為了保證對錶結構操作的安全性。

MDL可以認為是表結構鎖。需要改表結構時，自動加MDL寫鎖，其他時候加MDL讀鎖

DML,DQL語句，會自動加MDL的讀鎖

DDL語句，會自動加MDL的寫鎖

-- 執行緒A
begin;
select * from product;
-- 在一個事務內，MDL鎖是一直被持有的

-- 此時另起一個，執行緒B
alter table product add type varchar(10);
-- 執行上面的sql，會發現被阻塞住，因為執行緒B這一句需要MDL寫鎖

-- 再回到執行緒A
commit;
-- 執行緒A提交事務後，釋放MDL讀鎖
-- 此時能看到執行緒B的sql執行成功

注意：如上圖所示，session A 和 session B可以正常執行，session C 就被阻塞了，因為session C需要申請MDL寫鎖，關鍵是，session D也會被阻塞。當session A 提交後，會先執行session D，最後再執行session C。

觀察發現，如果先開啟事務，在事務裡執行DDL，先不提交當前事務。再另起一個執行緒，執行DML，發現DML不會被阻塞。

這是因為DDL在執行完成後，會自動立刻commit（自動commit後會釋放MDL寫鎖）。

申請MDL鎖的操作會形成一個佇列，佇列中寫鎖獲取優先順序高於讀鎖。一旦出現寫鎖等待，不但當前操作會被阻塞，同時還會阻塞後續該表的所有操作。事務一旦申請到MDL鎖後，直到事務執行完才會將鎖釋放。（這裡有種特殊情況如果事務中包含DDL操作，mysql會在DDL操作語句執行前，隱式提交commit，以保證該DDL語句操作作為一個單獨的事務存在，同時也保證後設資料排他鎖的釋放。

行鎖

行鎖是由儲存引擎實現的。InnoDB支援行鎖和事務，MyISAM不支援行鎖和事務。

InnoDB的行鎖是通過給索引項加鎖實現的。所以，若不是通過索引條件檢索的資料，InnoDB會使用表鎖。

InnoDB的行鎖

按照鎖定範圍分3種：

Record Lock：記錄鎖，鎖定索引中的一條記錄
Gap Lock：間隙鎖，鎖定記錄間的間隙
Next-Key Locks：記錄鎖+間隙鎖組合

按功能分為

共用讀鎖
排他寫鎖

DML語句（INSERT/UPDATE/DELETE）會自動加上排他鎖

對於普通SELECT語句，InnoDB不加鎖（是通過MVCC的一致性非鎖定讀的方式完成的，這個後序再做總結），可以通過以下方式，手動新增鎖

-- 共用讀鎖
SELECT * FROM product LOCK IN SHARE MODE;

-- 排他寫鎖
SELECT * FROM product FOR UPDATE;

-- 檢視行鎖情況
show status like '%innodb_row_lock%';

意向鎖

是InnoDB實現的表級鎖，在內部使用，無需使用者干預。

MySQL有多粒度的鎖實現，即行鎖和表鎖。那麼意向鎖存在的意義是為了協調行鎖和表鎖。試想事務A申請了某表某一行的寫鎖X，事務B申請了該表的寫鎖X，那麼事務B按理說也能修改事務A鎖定的某一行，這就產生了衝突。如果沒有意向鎖，某事務申請表鎖時，可能就得一行一行的掃描，看看是不是所有行都沒有鎖，所有行都沒鎖時，才能成功加表鎖。這樣效率就會很低。

所以意向鎖的作用就是表明某個事務有加行鎖的意圖，即，有人鎖住了某一行，或者將要鎖住某一行，這樣在其他人在加表鎖時，就能直接根據意向鎖的情況，判斷是否能夠加表鎖，而不必一行一行掃描了。

意向共用鎖（IS）：加行共用鎖前，必須先取得IS鎖

意向排他鎖（IX）：加行排他鎖前，必須先取得IX鎖

意向鎖的作用主要是為了在針對全表操作時獲得效能提升。

比如：事務A對某一行加了鎖（無論是讀鎖還是寫鎖），事務B嘗試加表鎖，這時如果沒有意向鎖，就需要遍歷檢測每一行是否持有行鎖，這樣效能是極低的。

意向鎖只和表鎖互斥。