如何搞定MySQL鎖（全局鎖、表級鎖、行級鎖）？這篇文章告訴你答案！太TMD詳細了！！！

概述

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

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

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

全局鎖

介紹

全局鎖就是對整個數據庫實例加鎖，加鎖後整個實例就處於只讀狀態，後續的DML的寫語句，DDL語句，已經更新操作的事務提交語句都將被阻塞。

其典型的使用場景是做全庫的邏輯備份，對所有的表進行鎖定，從而獲取一致性視圖，保證數據的完整性。

為什麼全庫邏輯備份，就需要加全就鎖呢？

A. 我們一起先來分析一下不加全局鎖，可能存在的問題。

假設在數據庫中存在這樣三張表: tb_stock 庫存表，tb_order 訂單表，tb_orderlog 訂單日誌表。

• 在進行數據備份時，先備份了tb_stock庫存表。
• 然後接下來，在業務系統中，執行了下單操作，扣減庫存，生成訂單（更新tb_stock表，插入tb_order表）。
• 然後再執行備份 tb_order表的邏輯。
• 業務中執行插入訂單日誌操作。
• 最後，又備份了tb_orderlog表。

此時備份出來的數據，是存在問題的。因為備份出來的數據，tb_stock表與tb_order表的數據不一致(有最新操作的訂單信息,但是庫存數沒減)。

那如何來規避這種問題呢? 此時就可以藉助於MySQL的全局鎖來解決。

B. 再來分析一下加了全局鎖後的情況

對數據庫進行進行邏輯備份之前，先對整個數據庫加上全局鎖，一旦加了全局鎖之後，其他的DDL、DML全部都處於阻塞狀態，但是可以執行DQL語句，也就是處於只讀狀態，而數據備份就是查詢操作。那麼數據在進行邏輯備份的過程中，數據庫中的數據就是不會發生變化的，這樣就保證了數據的一致性和完整性。

語法

1. 加全局鎖

flush tables with read lock;

2. 數據備份

mysqldump -uroot –p1234 itcast > itcast.sql

3. 釋放鎖

unlock tables;

特點

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

• 如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本上就得停擺。
• 如果在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進制日誌（binlog），會導致主從延遲。

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

mysqldump --single-transaction -uroot –p123456 itcast > itcast.sql

表級鎖

介紹

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

對於表級鎖，主要分為以下三類：

• 表鎖
• 元數據鎖（meta data lock，MDL）
• 意向鎖

表鎖

對於表鎖，分為兩類：

• 表共享讀鎖（read lock）
• 表獨佔寫鎖（write lock）

語法：

• 加鎖：lock tables 表名... read/write。
• 釋放鎖：unlock tables / 客户端斷開連接 。

特點:

A. 讀鎖

左側為客户端一，對指定表加了讀鎖，不會影響右側客户端二的讀，但是會阻塞右側客户端的寫。

測試:

B.寫鎖

左側為客户端一，對指定表加了寫鎖，會阻塞右側客户端的讀和寫。

測試:

結論

讀鎖不會阻塞其他客户端的讀，但是會阻塞寫。寫鎖既會阻塞其他客户端的讀，又會阻塞其他客户端的寫。

元數據鎖

meta data lock , 元數據鎖，簡寫MDL。

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

這裏的元數據，大家可以簡單理解為就是一張表的表結構。 也就是説，某一張表涉及到未提交的事務時，是不能夠修改這張表的表結構的。

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

常見的SQL操作時，所添加的元數據鎖：

對應SQL	鎖類型	説明
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都互斥

演示：

當執行SELECT、INSERT、UPDATE、DELETE等語句時，添加的是元數據共享鎖（SHARED_READ / SHARED_WRITE），之間是兼容的。

當執行SELECT語句時，添加的是元數據共享鎖（SHARED_READ），會阻塞元數據排他鎖（EXCLUSIVE），之間是互斥的。

我們可以通過下面的SQL，來查看數據庫中的元數據鎖的情況：

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

我們在操作過程中，可以通過上述的SQL語句，來查看元數據鎖的加鎖情況。

mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+----------------+--------------+---------------+
| object_type | object_schema      | object_name    | lock_type    | lock_duration |
+-------------+--------------------+----------------+--------------+---------------+
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_READ  | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_READ  | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user        | SHARED_WRITE | TRANSACTION   |
| TABLE       | MySQL_Advanced     | user_logs      | SHARED_WRITE | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks | SHARED_READ  | TRANSACTION   |
+-------------+--------------------+----------------+--------------+---------------+
5 rows in set (0.00 sec)
mysql> alter table tb_user add column java int;
...阻塞

-- 另開一個客户端窗口
mysql> select object_type,object_schema,object_name,lock_type,lock_duration from performance_schema.metadata_locks;
+-------------+--------------------+------------------------+---------------------+---------------+
| object_type | object_schema      | object_name            | lock_type           | lock_duration |
+-------------+--------------------+------------------------+---------------------+---------------+
| TABLE       | MySQL_Advanced     | tb_user                | SHARED_READ         | TRANSACTION   |
| GLOBAL      | NULL               | NULL                   | INTENTION_EXCLUSIVE | STATEMENT     |
| BACKUP LOCK | NULL               | NULL                   | INTENTION_EXCLUSIVE | TRANSACTION   |
| SCHEMA      | MySQL_Advanced     | NULL                   | INTENTION_EXCLUSIVE | TRANSACTION   |
| TABLE       | MySQL_Advanced     | tb_user                | SHARED_UPGRADABLE   | TRANSACTION   |
| TABLESPACE  | NULL               | MySQL_Advanced/tb_user | INTENTION_EXCLUSIVE | TRANSACTION   |
| TRIGGER     | MySQL_Advanced     | tb_user_insert_trigger | EXCLUSIVE           | TRANSACTION   |
| TRIGGER     | MySQL_Advanced     | tb_user_update_trigger | EXCLUSIVE           | TRANSACTION   |
| TRIGGER     | MySQL_Advanced     | tb_user_delete_trigger | EXCLUSIVE           | TRANSACTION   |
| TABLE       | MySQL_Advanced     | #sql-261d_18           | EXCLUSIVE           | STATEMENT     |
| TABLE       | MySQL_Advanced     | tb_user                | EXCLUSIVE           | TRANSACTION   |
| TABLE       | performance_schema | metadata_locks         | SHARED_READ         | TRANSACTION   |
+-------------+--------------------+------------------------+---------------------+---------------+
12 rows in set (0.00 sec)

意向鎖

1. 介紹

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

假如沒有意向鎖，客户端一對錶加了行鎖後，客户端二如何給表加表鎖呢，來通過示意圖簡單分析一下：

首先客户端一，開啟一個事務，然後執行DML操作，在執行DML語句時，會對涉及到的行加行鎖。

當客户端二，想對這張表加表鎖時，會檢查當前表是否有對應的行鎖，如果沒有，則添加表鎖，此時就會從第一行數據，檢查到最後一行數據，效率較低。

有了意向鎖之後 :

客户端一，在執行DML操作時，會對涉及的行加行鎖，同時也會對該表加上意向鎖。

而其他客户端，在對這張表加表鎖的時候，會根據該表上所加的意向鎖來判定是否可以成功加表鎖，而不用逐行判斷行鎖情況了。

2. 分類

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

一旦事務提交了，意向共享鎖、意向排他鎖，都會自動釋放。

可以通過以下SQL，查看意向鎖及行鎖的加鎖情況：

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

演示：

A. 意向共享鎖與表讀鎖是兼容的

B. 意向排他鎖與表讀鎖、寫鎖都是互斥的

行級鎖

介紹

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

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

• 行鎖（Record Lock）：鎖定單個行記錄的鎖，防止其他事務對此行進行update和delete。在RC、RR隔離級別下都支持。

• 間隙鎖（Gap Lock）：鎖定索引記錄間隙（不含該記錄），確保索引記錄間隙不變，防止其他事務在這個間隙進行insert，產生幻讀。在RR隔離級別下都支持。

• 臨鍵鎖（Next-Key Lock）：行鎖和間隙鎖組合，同時鎖住數據，並鎖住數據前面的間隙Gap。在RR隔離級別下支持。

行鎖

1. 介紹

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

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

兩種行鎖的兼容情況如下:

常見的SQL語句，在執行時，所加的行鎖如下：

SQL	行鎖類型	説明
INSERT ...	排他鎖	自動加鎖
UPDATE ...	排他鎖	自動加鎖
DELETE ...	排他鎖	自動加鎖
SELECT（正常）	不加任何鎖
SELECT ... LOCK IN SHARE MODE	共享鎖	需要手動在SELECT之後加LOCK IN SHARE MODE
SELECT ... FOR UPDATE	排他鎖	需要手動在SELECT之後加FOR UPDATE

2. 演示

默認情況下，InnoDB在 REPEATABLE READ事務隔離級別運行，InnoDB使用 next-key 鎖進行搜索和索引掃描，以防止幻讀。

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

可以通過以下SQL，查看意向鎖及行鎖的加鎖情況：

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

示例演示

數據準備:

CREATE TABLE `stu` (
	`id` int NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`name` varchar(255) DEFAULT NULL,
	`age` int NOT NULL
) ENGINE = InnoDB CHARACTER SET = utf8mb4;
INSERT INTO `stu` VALUES (1, 'tom', 1);
INSERT INTO `stu` VALUES (3, 'cat', 3);
INSERT INTO `stu` VALUES (8, 'rose', 8);
INSERT INTO `stu` VALUES (11, 'jetty', 11);
INSERT INTO `stu` VALUES (19, 'lily', 19);
INSERT INTO `stu` VALUES (25, 'luci', 25);

演示行鎖的時候，我們就通過上面這張表來演示一下。

A. 普通的select語句，執行時，不會加鎖。

B. select...lock in share mode，加共享鎖，共享鎖與共享鎖之間兼容。

共享鎖與排他鎖之間互斥。

客户端一獲取的是id為1這行的共享鎖，客户端二是可以獲取id為3這行的排它鎖的，因為不是同一行數據。 而如果客户端二想獲取id為1這行的排他鎖，會處於阻塞狀態，以為共享鎖與排他鎖之間互斥。

C. 排它鎖與排他鎖之間互斥

當客户端一，執行update語句，會為id為1的記錄加排他鎖； 客户端二，如果也執行update語句更新id為1的數據，也要為id為1的數據加排他鎖，但是客户端二會處於阻塞狀態，因為排他鎖之間是互斥的。 直到客户端一，把事務提交了，才會把這一行的行鎖釋放，此時客户端二，解除阻塞。

D. 無索引行鎖升級為表鎖

stu表中數據如下:

mysql> select * from stu;
+----+-----+-------+
| id | age | name  |
+----+-----+-------+
|  1 |   1 | Java  |
|  3 |   3 | Java  |
|  8 |   8 | rose  |
| 11 |  11 | jetty |
| 19 |  19 | lily  |
| 25 |  25 | luci  |
+----+-----+-------+
6 rows in set (0.00 sec)

在兩個客户端中執行如下操作:

在客户端一中，開啟事務，並執行update語句，更新name為Lily的數據，也就是id為19的記錄 。然後在客户端二中更新id為3的記錄，卻不能直接執行，會處於阻塞狀態，為什麼呢？

原因就是因為此時，客户端一，根據name字段進行更新時，name字段是沒有索引的，如果沒有索引，此時行鎖會升級為表鎖(因為行鎖是對索引項加的鎖，而name沒有索引)。

接下來，我們再針對name字段建立索引，索引建立之後，再次做一個測試：

此時我們可以看到，客户端一，開啟事務，然後依然是根據name進行更新。而客户端二，在更新id為3的數據時，更新成功，並未進入阻塞狀態。 這樣就説明，我們根據索引字段進行更新操作，就可以避免行鎖升級為表鎖的情況。

間隙鎖&臨鍵鎖

默認情況下，InnoDB在 REPEATABLE READ事務隔離級別運行，InnoDB使用 next-key 鎖進行搜索和索引掃描，以防止幻讀。

• 索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時, 優化為間隙鎖 。
• 索引上的等值查詢(非唯一普通索引)，向右遍歷時最後一個值不滿足查詢需求時，next-key lock 退化為間隙鎖。
• 索引上的範圍查詢(唯一索引)--會訪問到不滿足條件的第一個值為止。

注意:

間隙鎖唯一目的是防止其他事務插入間隙。間隙鎖可以共存，一個事務採用的間隙鎖不會阻止另一個事務在同一間隙上採用間隙鎖。

示例演示

A. 索引上的等值查詢(唯一索引)，給不存在的記錄加鎖時, 優化為間隙鎖 。

B. 索引上的等值查詢(非唯一普通索引)，向右遍歷時最後一個值不滿足查詢需求時，next-key lock 退化為間隙鎖。

介紹分析一下：

我們知道InnoDB的B+樹索引，葉子節點是有序的雙向鏈表。 假如，我們要根據這個二級索引查詢值為18的數據，並加上共享鎖，我們是隻鎖定18這一行就可以了嗎？ 並不是，因為是非唯一索引，這個結構中可能有多個18的存在，所以，在加鎖時會繼續往後找，找到一個不滿足條件的值（當前案例中也就是29）。此時會對18加臨鍵鎖，並對29之前的間隙加鎖。

C. 索引上的範圍查詢(唯一索引)--會訪問到不滿足條件的第一個值為止。

查詢的條件為id>=19，並添加共享鎖。 此時我們可以根據數據庫表中現有的數據，將數據分為三個部分：

[19]

(19,25]

(25,+∞]

所以數據庫數據在加鎖是，就是將19加了行鎖，25的臨鍵鎖（包含25及25之前的間隙），正無窮的臨鍵鎖(正無窮及之前的間隙)。

本文由傳智教育博學谷狂野架構師教研團隊發佈。

如果本文對您有幫助，歡迎關注和點贊；如果您有任何建議也可留言評論或私信，您的支持是我堅持創作的動力。

轉載請註明出處！