05、鎖

5.1、概述

1、介紹

鎖是計(jì)算機(jī)協(xié)調(diào)多個(gè)進(jìn)程或線程并發(fā)訪問(wèn)某一資源的機(jī)制。在數(shù)據(jù)庫(kù)中，除傳統(tǒng)的計(jì)算資源（CPU、RAM、I/O）的爭(zhēng)用以外，數(shù)據(jù)也是一種供許多用戶共享的資源。如何保證數(shù)據(jù)并發(fā)訪問(wèn)的一致性、有效性是所有數(shù)據(jù)庫(kù)必須解決的一個(gè)問(wèn)題，鎖沖突也是影響數(shù)據(jù)庫(kù)并發(fā)訪問(wèn)性能的一個(gè)重要因素。從這個(gè)角度來(lái)說(shuō)，鎖對(duì)數(shù)據(jù)庫(kù)而言顯得尤其重要，也更加復(fù)雜。

2、分類(lèi)

MySQL中的鎖，按照鎖的粒度分，分為以下三類(lèi)：

• 全局鎖：鎖定數(shù)據(jù)庫(kù)中的所有表
• 表級(jí)鎖：每次操作鎖住整張表
• 行級(jí)鎖：每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)

5.2、全局鎖

1、介紹

全局鎖就是對(duì)整個(gè)數(shù)據(jù)庫(kù)實(shí)例加鎖，加鎖后整個(gè)實(shí)例就處于只讀狀態(tài)，后續(xù)的DML的寫(xiě)語(yǔ)句、DDL語(yǔ)句，以及更新操作的事務(wù)提交語(yǔ)句都將被阻塞。

其典型的使用場(chǎng)景是做全庫(kù)的邏輯備份，對(duì)所有的表進(jìn)行鎖定，從而獲取一致性視圖，保證數(shù)據(jù)的完整性。

性能較差，數(shù)據(jù)邏輯備份時(shí)使用。

2、演示：

3、語(yǔ)法：

• 加全局鎖

  flush tables with read lock;
  

• 備份數(shù)據(jù)

  mysqldump -uroot -p密碼 數(shù)據(jù)庫(kù)名稱(chēng) >文件名稱(chēng).sql
  

  如：

  mysqldump -uroot -p1234 db1 >db1.sql
  

• 關(guān)閉全局鎖

  unlock tables;
  

4、一致性數(shù)據(jù)備份

5、特點(diǎn)

數(shù)據(jù)庫(kù)中加全局鎖，是一個(gè)比較重的操作，存在以下問(wèn)題：

• 如果在主庫(kù)上備份，那么在備份期間都不能執(zhí)行更新，業(yè)務(wù)基本上就得停擺。
• 如果在從庫(kù)上備份，那么在備份期間從庫(kù)不能執(zhí)行主庫(kù)同步過(guò)來(lái)的二進(jìn)制日志（binlog），會(huì)導(dǎo)致主從延遲。

在InnoDB引擎中，我們可以在備份時(shí)加上參數(shù)--single-transaction參數(shù)來(lái)完成不加鎖的一致性數(shù)據(jù)備份。

mysqldump --single-transaction -uroot -p1234 db1 >db1.sql

5.3、表級(jí)鎖

5.3.1、介紹

1、表級(jí)鎖，每次操作鎖住整張表。鎖定粒度大，發(fā)生鎖沖突的概率越高，并發(fā)度最低。應(yīng)用在MyISAM、InnoDB、BDB等存儲(chǔ)引擎中。

2、對(duì)于表級(jí)鎖，主要分為以下三類(lèi)：

• 表鎖
• 元數(shù)據(jù)鎖（meta data lock，MDL）
• 意向鎖

3、語(yǔ)法：

• 加鎖

  lock tables 表名... read/write;
  

• 釋放鎖

  unlock tables;
  

  或者客戶端斷開(kāi)連接

5.3.2、表鎖

1、對(duì)于表鎖，分為兩類(lèi)：

• 表共享讀鎖（read lock）
• 表獨(dú)占寫(xiě)鎖（write lock）

2、演示：

• 表共享讀鎖（read lock）

  

  演示：

  

• 表獨(dú)占寫(xiě)鎖（write lock）

  

  演示：

  

3、注意：

• 讀鎖不會(huì)阻塞其他客戶端的讀，但是會(huì)阻塞寫(xiě)。
• 寫(xiě)鎖既會(huì)阻塞其他客戶端的讀，又會(huì)阻塞其他客戶端的寫(xiě)。

5.3.3、元數(shù)據(jù)鎖

1、介紹

元數(shù)據(jù)鎖（meta data lock，MDL）加鎖過(guò)程是系統(tǒng)自動(dòng)控制，無(wú)需顯式使用，在訪問(wèn)一張表的時(shí)候就會(huì)自動(dòng)加上。

MDL鎖主要作用是維護(hù)表元數(shù)據(jù)的數(shù)據(jù)一致性，在表上有活動(dòng)事務(wù)的時(shí)候，不可以對(duì)元數(shù)據(jù)進(jìn)行寫(xiě)入操作。為了避免DML和DDL沖突，保證讀寫(xiě)的正確性。

2、在MySQL5.5中引入了MDL，當(dāng)對(duì)一張表進(jìn)行增刪改查的時(shí)候，加MDL讀鎖（共享）；當(dāng)對(duì)表結(jié)構(gòu)進(jìn)行變更操作的時(shí)候，加MDL寫(xiě)鎖（排他）。

對(duì)應(yīng)SQL	鎖類(lèi)型	說(shuō)明
lock tables xxx read/write	SHARED_READ_ONLY/SHARED_NO_READ_WRITE
select、select … lock in share mode	SHARED_READ	與SHARED_READ、SHARED_WRITE兼容，與EXCLUSIVE互斥
insert、update、delete、select … for update	SHARED_WRITE	與SHARED_READ、SHARED_WRITE兼容，與EXCLUSIVE互斥
alter table …	EXCLUSIVE	與其他的MDL都互斥

3、演示

4、查看元數(shù)據(jù)鎖

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

5.3.4、意向鎖

1、為了避免DML在執(zhí)行時(shí)，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行數(shù)據(jù)是否加鎖，使用意向鎖來(lái)減少表鎖的檢查。

2、分類(lèi)：

• 意向共享鎖（IS）：由語(yǔ)句select ... lock in share mode添加。
  • 與表鎖共享鎖（read）兼容，與表鎖排他鎖（write）互斥。
• 意向排他鎖（IX）：由insert、update、delete、select ... for update添加。
  • 與表鎖共享鎖（read）及排他鎖（write）都互斥。意向鎖之間不會(huì)互斥。

3、可以通過(guò)以下SQL語(yǔ)句，查看意向鎖及行鎖的加鎖情況：

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

4、演示：

• 意向共享鎖

• 意向排他鎖

5.4、行級(jí)鎖

5.4.1、介紹

1、行級(jí)鎖，每次操作鎖住對(duì)應(yīng)的行數(shù)據(jù)。鎖定粒度最小，發(fā)證鎖沖突的概率最低，并發(fā)度最高。應(yīng)用在InnoDB存儲(chǔ)引擎中。

InnoDB的數(shù)據(jù)是基于索引組織的，行鎖是通過(guò)對(duì)索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的，而不是對(duì)記錄加的鎖。

2、對(duì)于行級(jí)鎖，主要分為以下三類(lèi)：

• 行鎖（Record Lock）：鎖定單個(gè)行記錄的鎖，防止其他事務(wù)對(duì)此進(jìn)行update和delete。在RC（read commit）、RR（repeatable read）隔離級(jí)別下都支持。

  

• 間隙鎖（Gap Lock）：鎖定索引記錄間隙（不包該記錄），確保索引記錄間隙不變，防止其他事務(wù)在這個(gè)間隙進(jìn)行insert，產(chǎn)生幻讀。在RR隔離級(jí)別下都支持。

  

• 臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時(shí)鎖住數(shù)據(jù)，并鎖住數(shù)據(jù)前面的間隙GAP。在RR隔離級(jí)別下支持。

  

5.4.2、行鎖

1、InnoDB實(shí)現(xiàn)了以下兩種類(lèi)型的行鎖：

• 共享鎖（S）：允許一個(gè)事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排它鎖。
• 排他鎖（X）：允許獲取排他鎖的事務(wù)更新數(shù)據(jù)，阻止其他事務(wù)獲得相同數(shù)據(jù)集的共享鎖和排它鎖。

2、行鎖

SQL	行鎖類(lèi)型	說(shuō)明
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

3、演示

默認(rèn)情況下，InnoDB在REPEATABLE READ事務(wù)隔離級(jí)別運(yùn)行，InnoDB使用next-key鎖進(jìn)行搜索和索引掃描，以防止幻讀。

① 針對(duì)唯一索引進(jìn)行檢索時(shí)，對(duì)已存在的記錄進(jìn)行等值匹配時(shí)，將會(huì)自動(dòng)優(yōu)化為行鎖。

② InnoDB的行鎖是針對(duì)于索引加的鎖，不通過(guò)索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中的所有記錄加鎖，此時(shí)就會(huì)升級(jí)為表鎖。

可以通過(guò)以下SQL語(yǔ)句，查看意向鎖及行鎖的加鎖情況：

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

• 演示①

• 演示②

5.4.3、間隙鎖&臨鍵鎖

1、默認(rèn)情況下，InnoDB在REPEATABLE READ事務(wù)隔離級(jí)別運(yùn)行，InnoDB使用next-key鎖進(jìn)行搜索和索引掃描，以防止幻讀。

① 索引上的等值查詢（唯一索引），給不存在的記錄加鎖時(shí)，優(yōu)化為間隙鎖。

② 索引上的等值查詢（普通索引），向右遍歷時(shí)最后一個(gè)值不滿足查詢需求時(shí)，next-key lock退化為間隙鎖。

③ 索引上的范圍查詢（唯一查詢）—— 會(huì)訪問(wèn)到不滿足條件的第一個(gè)值為止。

注意：

間隙鎖唯一目的是防止其他事務(wù)插入間隙。間隙鎖可以共存，一個(gè)事務(wù)采用的間隙鎖不會(huì)阻止另一個(gè)事務(wù)在同一間隙上采用間隙鎖。

2、演示

• 演示①

  

• 演示②

  

• 演示③