MySQL高級9-鎖_ZenDei技術網路在線

一、簡介鎖是電腦協調多個進程或線程併發訪問某一資源的機制。在資料庫中，除了傳統的計算資源(CPU、RAM、i/O)的掙用以外，數據也是一種供許多用戶共用的資源。如何保證數據併發訪問的一致性，有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問性能的一個重要因素，從這個角度來說，鎖對 ...

一、簡介

　　鎖是電腦協調多個進程或線程併發訪問某一資源的機制。在資料庫中，除了傳統的計算資源(CPU、RAM、i/O)的掙用以外，數據也是一種供許多用戶共用的資源。如何保證數據併發訪問的一致性，有效性是所有資料庫必須解決的一個問題，鎖衝突也是影響資料庫併發訪問性能的一個重要因素，從這個角度來說，鎖對資料庫而言顯得尤其重要，也更加複雜。

二、分類

　　MySQL中的鎖、按照鎖的粒度分，分為以下三類

全局鎖：鎖定數據量的所有表
表級鎖：每次操作鎖住整張表
行級鎖：每次操作鎖住對應的行數據

三、全局鎖

　　3.1 簡介

　　　　全局鎖就是對整個資料庫實例枷鎖，加鎖後整個實例就處於只讀狀態，後續的DML的寫語句，DDL語句，以及更新操作的事物提交語句都會被阻塞，其典型的使用場景是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。

　　3.2 添加全局鎖語法

flush tables with read lock;

　　3.3 打開全局鎖語法

unlock tables;

　　3.4 案例一

　　　　說明1：客戶端1中設置了全局鎖

　　　　說明2：客戶端2中，執行查詢語句正常的，但是執行DML語句中的更新操作卻是處於阻塞狀態

　　3.5 案例二

　　　　說明1：客戶端1中設置了全局鎖

　　　　說明2：客戶端3中做了資料庫備份的語句，其中mysqldump是和mysql一樣由MySQL伺服器提供的資料庫備份的命令

　　　　說明3：當資料庫設置了全局鎖的時候，不影響資料庫的備份

　　　　說明4：MySQL備份實在終端命令行模式下，不是在資料庫命令模式下，註意！註意！註意！

　　3.6 案例三

　　　　說明1：客戶端1釋放全局鎖

　　　　說明2：客戶端2的更新語句馬上執行成功

　　　　說明3：通過客戶端2的更新語句的執行時間37min,可以說明該語句被客戶端1的全局鎖，阻塞了37分鐘

　　3.7 全局鎖特點

　　　　資料庫中加全局鎖，是一個比較重的操作，存在以下問題

- 如果在主庫上備份，那麼在備份期間都不能執行更新，業務上基本就是停擺狀態
- 如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進位日誌(binlog),會導致主從延遲

　　　　在innoDB引擎中，我們可以在備份時加上 --single-transaction 參數來完成不加鎖的一致性數據備份

四、表級鎖

　　4.1 簡介

　　　　表級鎖，每次操作鎖住整張表，鎖定粒度大，發生鎖衝突的概率最高，併發度最低，應用在MyISAM,InnoDB等存儲引擎中

　　4.2 表級鎖的分類

- 表鎖
- 元數據鎖（meta data lock, MDL）
- 意向鎖

五、表鎖

　　5.1 表鎖的分類

- 表共用讀鎖（read lock）
- 表獨占寫鎖（write lock）

　　5.2 表鎖語法

　　　　加鎖：lock tables 表名... read/write

　　　　釋放鎖：unlock tables / 客戶端斷開連接

　　　　說明1：加鎖的時候，可以多張表同時加鎖

　　　　說明2：客戶端斷開連接也能釋放表鎖

　　5.3 讀鎖案例

　　　說明1：當對一個表添加讀鎖，不會影響其讀取數據，但是會影響其新增，修改，刪除的操作語句

　　　　說明2：當對一個表添加讀鎖，不會影響其他客戶端讀取數據，但是會讓其他客戶端的新增，修改，刪除等操作語句處於阻塞狀態。

　　　　說明3：當把鎖釋放了，自己及其他客戶端的新增，刪除和修改語句才會結束阻塞。

　　5.4 寫鎖案例

　　　　說明1：添加寫鎖的客戶端可以正常對錶進行查詢和增刪改等操作

　　　　說明2：其他客戶端的查詢，修改，新增，刪除都操作在有寫鎖的情況下，都要處於阻塞狀態，直到其添加寫鎖的客戶端釋放寫鎖。

　　　　說明3：上圖中的修改等應該是DML語句（Data Manipulation Language 數據操作語言，用來對資料庫表中的數據進行增刪改的），不是DDL語句（DDL: Data Definition Language 數據定義語言，用來定義資料庫對象，資料庫，表，欄位）

六、元數據鎖

　　6.1 元數據鎖簡介：

　　　　元數據鎖（meta data lock）：簡稱 MDL

　　　　MDL加鎖過程是系統自動控制，無需顯示使用，在訪問一張表的時候會自動加上。MDL鎖主要作用是維護表元數據的數據一致性，在表上有活動事務的時候，不可以對元數據進行寫入操作，為了避免DML和DDL衝突，保證讀寫的正確性

　　6.2 元數據鎖的類型

　　　　在MySQL5.5 中引入了MDL, 當對一張表進行增刪改查的時候，加MDL讀鎖（共用）；當對錶結構進行變更操作的時候，加MDL寫鎖（排他）

　　6.3 案例

　　　　說明1：select 和 update 分別會添加SHARED_READ 和 SHARED_WRITE, 但是SHARED_READ 和 SHARED_WRITE是相容的，所以其他客戶端是可以做更刪改查的

　　　　說明2：alter table 會產生EXCLUSIVE 鎖。該鎖與其他MDL都互斥。ß

　　6.4 查看元數據鎖

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

七、意向鎖

　　7.1 意向鎖介紹

　　　　為了避免DML在執行時，加的行鎖與表鎖的衝突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每一行數據是否加鎖，使用意向鎖來減少表鎖的檢查。

　　　　情況1：不加意向鎖

　　　　說明1：線程A 開啟一個事務，並且使用DML語句更新數據，此時會對更新的數據添加行鎖，

　　　　說明2：此時線程A 的事務並沒有提交事務，線程B又對該表添加一個表鎖，此時添加表鎖的時候，就會從第一條數據，依次檢查到最後一條數據，看是否有其他的鎖，如果有其他鎖，需要等到其他鎖釋放了，才能執行添加表鎖

　　　　說明3：此時添加表鎖的效率就非常低　　

　　　　情況2：加意向鎖

　　　　說明4：當線程A 開啟了一個事務，並且執行了DML的更新數據的語句，此時除了會給該行數據添加行鎖之外，還會添加一個基於整表的意向鎖。

　　　　說明5：當線程B 再次添加表鎖的時候，就不用逐行排查是否有行鎖了，而是直接檢查整表是否有意向鎖，如果意向鎖和表鎖相容則直接加表鎖

　　　　說明6：如果表鎖和意向鎖不相容，則仍然會等到意向鎖釋放了，表鎖才能添加成功。

　　7.2 意向鎖的類型

- 意向共用鎖(IS): 由語句 select ... lock in share mode 添加。與表鎖共用鎖(read)相容，與表鎖排它鎖(write)互斥。
- 意向排它鎖(IX): 由 insert, update, delete, select ... for update 添加。與表鎖共用鎖(read)及排它鎖(write)都互斥，意向鎖之間不會互斥

　　7.3 意向鎖查詢

select object_schema, object_name, index_name, lock_type, lock_mode, lock_data from performance_schema.data_locks;

　　7.4 意向鎖案例

　　　　說明1：客戶端1開啟事務，並且查詢語句時設置一個意向共用鎖(IS)

　　　　說明2：客戶端2在客戶端1的事務未提交的時候，創建read鎖成功

　　　　說明3：意向共用鎖(IS)和read共用鎖相容

　　　　說明4：執行update等DML語句時，MySQL會自動添加行鎖和排他意向鎖。

　　　　說明5：排他意向鎖與共用read鎖和排他write鎖都互斥

　　　　說明6：意向鎖的作用主要是解決行鎖和表鎖之間的矛盾。

八、行級鎖

　　8.1、簡介

　　　　行級鎖，每次操作鎖住對應的行數據，所得粒度最小，發生鎖衝突的概率最低，併發度最高，應用在InnoDB存儲引擎中

　　　　InnoDB的數據是基於索引組織的，行鎖是通過對索引上的索引項加鎖來實現的，而不是對記錄加的鎖，對於行級鎖，主要分為一下三類

　　　　1、行鎖(record lock): 鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete，在read committed和repeatable read隔離級別下都支持。

　　　　2、間隙鎖(Gap lock): 鎖定索引記錄間隙（不包含記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行insert,產生幻讀，在repeatable read隔離級別下支持

　　　　3、臨建鎖(Next-Key lock): 行鎖和間隙鎖組合，同時鎖住數據，並鎖住數據前面的間隙，在repeatable read隔離級別下支持

　　8.2 行鎖

　　　　InnoDB 實現了一下兩種類型的行鎖

　　　　1、共用鎖(S): 允許一個事物去讀一行，阻止其他事務獲得相同數據集的排他鎖。

　　　　2、排他鎖(X): 允許獲取排他鎖的事務更新數據，阻止其他事物獲得相同數據集的共用鎖和排他鎖

　　　　3、不同語句的枷鎖情況

　　　　4、預設情況下，InnoDB在 repeatable read隔離級別下運行，InnoDB使用next-key鎖進行行搜索和索引掃描，以防止幻讀

- 針對唯一索引進行檢索時，對已經存在的記錄進行等值匹配時，將會自動優化為行鎖
- InnoDB的行鎖是針對於索引加的鎖，不通過索引檢索數據，那麼InnoDB將對錶中的所有記錄加鎖，此時就會升級為表鎖

　　 8.3 行鎖案例

　　　　說明1：客戶端1開始事務，並執行查詢語句，客戶端2鎖的情況，顯示為空表

　　　　說明2：客戶端執行查詢語句，並手動添加共用，在客戶端2中，查詢lock_mode欄位中有一個S，即共用鎖:

　　　　說明3：客戶端2也手動開啟了一個共用鎖，並且成功，說明共用鎖與共用鎖之間是相容的。

　　　　說明5：在客戶端2上執行更新id=2的數據成功，並且查詢可以看出，自動增加了一個(X)排它鎖。

　　　　說明6: 因為更新id=2的數據時，id=2的這一行上沒有其他的鎖，所以可以執行成功

　　　　說明7：因為在客戶點1上已經對id=1的行上添加了一個共用鎖(S), 這是客戶端2對id=1的數據做update操作時會自動在id=1的數據上在添加一個排它鎖(X),這是id=1的數據上就會有共用鎖(S)和排它鎖(X),又因為共用鎖和排它鎖不相容，所以在update id = 1 的時候就會阻塞，需要等到共用鎖（S）釋放了，才能執行成功。