如何搞定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操作時，所添加的元資料鎖：

演示：

當執行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陳述句，在執行時，所加的行鎖如下：

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之前的間隙），正無窮的臨鍵鎖(正無窮及之前的間隙)，

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

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

轉載請注明出處！

標籤：其他

上一篇：Comparable 和 Comparator 的區別

下一篇：hibernate跨資料庫，json欄位處理方案，自定義擴展JsonStringType