7 鎖

7.1 概述

介紹

鎖是計算機協調多個程序或者線程并發通路某一資源的機制。
資料也是一種供許多使用者共享的資源。如何保持資料并發通路的一緻性、有效性是所有資料庫必須解決的一個問題，鎖沖突也是影響資料庫并發通路性能的一個重要因素。

分類

全局鎖：鎖定資料庫中所有表
表級鎖：每次操作鎖住整張表
行級鎖：每次操作鎖住對應的行資料

7.2 全局鎖

介紹

全局鎖是對整個資料庫執行個體加鎖，加鎖後整個執行個體就處于隻讀狀态，後續的DML和DDL語句，已經更新操作的事務送出語句将會被阻塞。
其典型使用場景是做全局的邏輯備份，對所有的表進行鎖定，進而獲得一緻性視圖，保證資料的完整性。

一緻性資料備份

進行全局鎖

mysql> flush tables with read lock;

可以查，但是不可以改了

mysql> update weather_service set summary_ts = '2021-12-25 10:23:50' where id = 1;

備份下

[root@hadoop ~]# mysqldump -uroot -pxxxxxxxx weather_service > /home/fenfen/MySQL/weather_service.sql
mysqldump: [Warning] Using a password on the command line interface can be insecure.

釋放鎖

mysql> unlock tables;

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

mysqldump --single-transaction -uroot -pxxxxxxxx itcast > itcast.sql

特點

如果在主庫上備份，那麼在備份期間都不能執行更新，業務基本就得停止。
在從庫上備份，那麼在備份期間從庫不能執行主庫同步過來的二進制日志(binlog)，會導緻主從延遲 -- 這個估計後面主從複制還會講

7.3 表級鎖

介紹

每次操作鎖住整張表。鎖表粒度大，發生鎖沖突的機率最高，并發度最低。

表級鎖分類

表鎖
- 表共享讀鎖
- 表獨占寫鎖
中繼資料鎖
意向鎖

表鎖

文法

lock tables 表名... read/write
unlock tables/直接關閉用戶端

讀鎖案例

-- 用戶端1
-- 設定讀鎖
mysql> lock tables course read;
Query OK, 0 rows affected (0.00 sec)

-- 讀
mysql> select * from course;
+----+-------------+
| id | name        |
+----+-------------+
|  6 | ES          |
|  1 | javaEE      |
|  3 | MySQL       |
|  5 | Oracle      |
|  2 | SpringBoot  |
|  4 | SpringCloud |
+----+-------------+

-- 寫
mysql> update course set name = 'GO' where id = 6;
ERROR 1099 (HY000): Table 'course' was locked with a READ lock and can't be updated


-- 釋放表鎖
unlock tables;

-- 用戶端2 
-- 執行讀語句可以，但是執行寫的語句，發現阻塞了，除非界面1釋放表鎖
mysql> update course set name = 'GO' where id = 6;

寫鎖案例

-- 用戶端1
-- 設定寫鎖
mysql> lock tables course write;
Query OK, 0 rows affected (0.00 sec)

-- 讀寫都可以
mysql> select * from course;
+----+-------------+
| id | name        |
+----+-------------+
|  6 | GO          |
|  1 | javaEE      |
|  3 | MySQL       |
|  5 | Oracle      |
|  2 | SpringBoot  |
|  4 | SpringCloud |
+----+-------------+
6 rows in set (0.00 sec)

mysql> update course set name = 'ES' where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 用戶端2
-- 讀寫都不行，處于阻塞狀态
mysql> select * from course;

mysql> update course set name = 'GO' where id = 6;

中繼資料鎖

介紹
- MDL加鎖過程是系統自己控制的，通路一張表的時候就自動加上了
- 中繼資料了解為表結構，MDL鎖主要的作用就是維護表中繼資料的資料一緻性，在表上有活動事務的時候未送出，不可以對中繼資料進行寫入操作。
- 目的是：為了避免DML和DDL沖突，保證讀寫的正确性。即當對一張表進行增删改查的時候，加MDL讀鎖(共享)；當對表結構進行變更操作的時候，加MDL寫鎖(排他)
- 怎麼了解呢？這麼了解：就是你改表結構的時候，是不能有别的未送出事務存在的，因為MDL寫鎖和其他共享鎖都是互斥的，送出了就不阻塞了。

案例

檢視中繼資料鎖

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

-- 這個時候就有鎖，有了對兩個事務增删改查的後的鎖
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             | itcast             | course         | SHARED_READ         | TRANSACTION   |
| TABLE             | itcast             | course         | SHARED_READ         | TRANSACTION   |
| TABLE             | performance_schema | metadata_locks | SHARED_READ         | TRANSACTION   
+-------------------+--------------------+----------------+---------------------+---------------+

意向鎖

場景
- 為了避免DML在執行時，加的行鎖與表鎖的沖突，在InnoDB中引入了意向鎖，使得表鎖不用檢查每行資料是否加鎖，使用意向鎖來減少表鎖的檢查。

作用
- 直接去判斷意向鎖和表鎖兼不相容，表鎖不用一行一行看有沒有行鎖了，而意向鎖是自動添加的

分類
- 意向共享鎖IS：與表鎖共享鎖(read)相容，和表鎖排他鎖(write)互斥。
- 意向鎖排他鎖IX：與表鎖共享鎖(read)和表鎖排他鎖(write)都互斥
檢視意向鎖情況

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

增加意向鎖
- 意向共享鎖IS，有語句select ...lock in share mode添加
- 意向排他鎖IX：由insert、update、delete、select ... for update添加
意向共享鎖案例

-- 用戶端1
-- 查詢，加上一個行鎖共享鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course where id = 6 lock in share mode;

-- 此時檢視下意向鎖情況，第一行顯示IS就是意向共享鎖，說明查詢的時候自動加上了

mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
    -> performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | course      | NULL       | TABLE     | IS            | NULL      |
| itcast        | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 6         |
+---------------+-------------+------------+-----------+---------------+-----------+

-- 用戶端2
-- 加表讀鎖成功，是因為意向共享鎖和表鎖共享鎖(read)是相容的
mysql> lock tables course read;
Query OK, 0 rows affected (0.00 sec)

-- 加表寫鎖失敗，原因是意向共享鎖和表鎖排他鎖(write)不相容，即互斥
mysql> lock tables course write;

-- 此時事務用戶端1事務送出就鎖釋放了
mysql> lock tables course write;
Query OK, 0 rows affected (9.06 sec)

意向排他鎖案例

-- 用戶端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
-- 更新
mysql> update course set name = 'PHP' where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 此時檢視下意向鎖情況，第一行顯示IX就是意向共享鎖，說明修改的時候自動加上了
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
    -> performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | course      | NULL       | TABLE     | IX            | NULL      |
| itcast        | course      | PRIMARY    | RECORD    | X,REC_NOT_GAP | 6         |
+---------------+-------------+------------+-----------+---------------+-----------+

-- 用戶端2
-- -- 加表讀鎖失敗，原因是意向排他鎖和表鎖共享鎖(read)不相容，即互斥
mysql> lock tables course read;

-- 加表寫鎖失敗，原因是意向共享鎖和表鎖排他鎖(write)不相容，即互斥
mysql> lock tables course write;

-- 此時事務用戶端1事務送出就鎖釋放了
mysql> lock tables course write;
Query OK, 0 rows affected (3.96 sec)

7.4 行級鎖

介紹

行級鎖，每次操作鎖住的是對應的行資料
鎖定粒度最小，發生鎖沖突的機率最低，并發率最高。
應用在InnoDB存儲引擎中，MyISAM不用

分類

行鎖：鎖住單個記錄的鎖，防止事務對其進行update和delete操作。在RC讀已送出,RR可重複讀隔離級别下都支援
間隙鎖：鎖定索引記錄間隙(但不包含該記錄)，確定索引的間隙不變，防止其他事務在這個間隙中insert，出現幻讀。在RR可重複讀隔離級别下都支援

臨鍵鎖：行鎖和間隙鎖組合，同時鎖住資料，并鎖住資料的前面的間隙GAP。在RR可重複讀的隔離級别下支援。

行鎖

分類
- 共享鎖S:允許事務讀取一行，但是阻止其他事務獲得相同資料集的排他鎖。共享鎖和共享鎖是相容的，共享鎖和排他鎖是互斥
- 排他鎖X：允許擷取排他鎖的事務更新資料，防止其他事務獲得相同資料集的共享鎖和排他鎖。
- 怎麼記：就是共享和共享可以，其他都不行！和上面表級鎖一個邏輯！有點難！
- InnoDB的行鎖是針對與索引加的鎖，不通過索引條件檢索資料，那麼InnoDB将會對表中的所有記錄加鎖，就會更新成表鎖
  
  春華秋實之MySQL進階-05 鎖7 鎖

檢視意向鎖及行鎖的加鎖情況

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

select案例
- select壓根就沒有加任何的行鎖，是以用戶端2也開啟事務的話，可是能查的！
  
  春華秋實之MySQL進階-05 鎖7 鎖
select ...lock in share mode案例

-- 用戶端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course where id = 6 lock in share mode;
+----+------+
| id | name |
+----+------+
|  6 | PHP  |
+----+------+
1 row in set (0.00 sec)

-- 第二行中的S代表的就是共享鎖
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
    -> performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | course      | NULL       | TABLE     | IS            | NULL      |
| itcast        | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 6         |
+---------------+-------------+------------+-----------+---------------+-----------+
2 rows in set (0.00 sec)

-- 用戶端2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 同樣用用戶端1的查詢share mode查詢也是可以的，因為共享和共享式相容的！
mysql>  select * from course where id = 6 lock in share mode;
+----+------+
| id | name |
+----+------+
|  6 | PHP  |
+----+------+
1 row in set (0.00 sec
              
-- 再查下行鎖情況，發現id為6的加了兩個共享鎖！
mysql>  select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
    -> performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | course      | NULL       | TABLE     | IS            | NULL      |
| itcast        | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 6         |
| itcast        | course      | NULL       | TABLE     | IS            | NULL      |
| itcast        | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 6         |
+---------------+-------------+------------+-----------+---------------+-----------+
4 rows in set (0.00 sec)     
              
 -- 後面記得送出事務，送出後發現s共享鎖沒了

update行鎖排他鎖案例

-- 用戶端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 修改
mysql> update course set name = 'Go' where id = 6;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings:

-- 用戶端2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 讀可以，發現沒變，用戶端1 中更改的沒變，也驗證了事務的RR隔離性，出現了幻讀。
mysql> select * from course;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | javaEE      |
|  3 | MySQL       |
|  5 | Oracle      |
|  6 | PHP         |
|  2 | SpringBoot  |
|  4 | SpringCloud |
+----+-------------+
6 rows in set (0.00 sec)

-- 寫就不可以，因為排他鎖和排他鎖互斥
mysql> update course set name = 'Go' where id = 6;

-- 此時用戶端1的事務送出後就解除阻塞了
mysql> update course set name = 'Go' where id = 6;
Query OK, 0 rows affected (8.00 sec)
Rows matched: 1  Changed: 0  Warnings: 0

行鎖更新成表鎖的案例

-- 用戶端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> update course set name = 'Redis' where name = 'Go';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- 客戶段2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 即使是不同的資料行，客戶段1中update的name字段由于沒有索引，将行鎖更新成表鎖，因為你更新id字段也是不行
mysql> update course set name = 'Hive' where id = 5;

-- 當用戶端1中的事務送出，就接觸阻塞了
mysql> update course set name = 'Hive' where id = 5;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

間隙鎖

- 索引上的等值查詢(唯一索引)，給不存在的記錄加鎖，優化為間隙鎖
- 索引上的等值查詢(普通索引)，向右周遊時最後一個值不滿足查詢條件時，next_key lock 退化為間隙鎖
- 索引上的範圍查詢(唯一索引)-- 會通路到不滿足條件的第一個值為止

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

查詢不存在記錄(唯一索引)案例

-- 用戶端1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course;
+----+-------------+
| id | name        |
+----+-------------+
|  1 | javaEE      |
|  3 | MySQL       |
|  5 | Oracle      |
|  6 | Redis       |
|  9 | Spark       |
|  2 | SpringBoot  |
|  4 | SpringCloud |
+----+-------------+
7 rows in set (0.00 sec)

mysql> update course set name = 'JS' where id = 7;
Query OK, 0 rows affected (0.00 sec)
Rows matched: 0  Changed: 0  Warnings: 0

-- 第二行中lock_type時record時行鎖的意思，lock_mode是X表示是排他鎖，GAP指的是間隙鎖，鎖的是9之前的間隙，即6-9的間隙
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from
    -> performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+-----------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data |
+---------------+-------------+------------+-----------+---------------+-----------+
| itcast        | course      | NULL       | TABLE     | IX            | NULL      |
| itcast        | course      | PRIMARY    | RECORD    | X,GAP         | 9         |
+---------------+-------------+------------+-----------+---------------+-----------+

-- 客戶段2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

-- 此時插入6-9的中插入值，就會阻塞
mysql> insert into course values (8,'RabbitMQ');

-- 此時釋放掉用戶端1的事務就會接觸阻塞
mysql> insert into course values (8,'RabbitMQ');
Query OK, 1 row affected (4.65 sec)

等值查詢(普通索引)案例
- 第一行S，是臨鍵鎖，鎖住了1-3的間隙和資料
- 第二行S，REC_NOT_GAP，是行鎖，鎖住了第三行的資料
- 第三行S，GAP，是間隙鎖，鎖住了3-7的間隙，鎖上的原因是age是非唯一索引，為了防止其他事務往這邊加入相同的資料，出現幻讀現象

範圍查詢(唯一索引)

mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from course where id>= 6 lock in share mode;
+----+----------+
| id | name     |
+----+----------+
|  6 | Hive     |
|  8 | RabbitMQ |
|  9 | Spark    |
+----+----------+
3 rows in set (0.00 sec)

-- 第二行S,REC_NOT_GAP行鎖鎖住id為6的行，第五行S是臨鍵鎖表示鎖住6-8的資料和間隙，第四行臨鍵鎖表示鎖住8-9行的資料和間隙，第三行是正無窮，表示鎖住9後面的資料和間隙
mysql> select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;
+---------------+-------------+------------+-----------+---------------+------------------------+
| object_schema | object_name | index_name | lock_type | lock_mode     | lock_data              |
+---------------+-------------+------------+-----------+---------------+------------------------+
| itcast        | course      | NULL       | TABLE     | IS            | NULL                   |
| itcast        | course      | PRIMARY    | RECORD    | S,REC_NOT_GAP | 6                      |
| itcast        | course      | PRIMARY    | RECORD    | S             | supremum pseudo-record |
| itcast        | course      | PRIMARY    | RECORD    | S             | 9                      |
| itcast        | course      | PRIMARY    | RECORD    | S             | 8                      |
+---------------+-------------+------------+-----------+---------------+------------------------+
5 rows in set (0.00 sec)

春華秋實之MySQL進階-05 鎖7 鎖

7 鎖

7.1 概述

7.2 全局鎖

7.3 表級鎖

7.4 行級鎖

繼續閱讀

2022秋招面試總結（cpp+java+測開）百度測開一面位元組後端一面蝦皮後端一面蝦皮後端二面

資料庫之DDL操作資料庫DDL操作資料庫DDL操作資料表

資料庫之DQL操作資料庫

mysql優化（sql優化）

資料遷移方法資料遷移原則資料遷移之雙寫方案資料遷移之級聯同步方案

redis叢集資料一緻性_RedisRaft為Redis叢集帶來強大的資料一緻性

寶塔面闆mysql恢複2018.1.8更新

Centos7 MySQL 5.7 安裝MySQL 5.7 安裝

查找入職員工時間排名倒數第三的員工所有資訊

Hibernate使用Hibernate的“3個準備，7個步驟”Hibernate API簡介操作實體對象對象識别

雲計算面試題——mysql/存儲引擎/備份

SQL語言基礎：常用的資料查詢語句

Ubuntu16.04安裝Apache+MySQL+PHP1. 安裝Apache2. 安裝MySQL3. 安裝PHP4. 安裝phpMyAdmin

MySQL的4種隔離級别？出現問題

neo4j之cypher使用文檔

mysql使用source指令導入.sql檔案