Mysql各種鎖機制

tomlibao發表於2021-06-09
原文網址 : https://learnku.com/articles/57998
MySql

鎖來源

在存在併發操作的時候，必然需要一種機制來保證資料的完整性與一致性。鎖就是這一技術的實現。

鎖種類

  • 根據概念分：悲觀鎖和樂觀鎖

  • 根據粒度分：表鎖、頁鎖、行鎖，最常見的就是表鎖和行鎖。其中，MyISAM引擎只有表鎖，而InooDB既有表鎖也有行鎖。

  • 根據功能分：共享鎖、排它鎖（獨佔鎖）、意向鎖等。其中，共享鎖被稱為S鎖。排它鎖稱為X鎖。

鎖名稱 特點
表鎖 加鎖快，不會出現死鎖，鎖定粒度大，發生鎖衝突的概率最高，併發度最低。
行鎖 開銷大，發生鎖衝突概率低。併發度高，會發生死鎖。
頁鎖 開銷、加鎖時間、鎖定粒度界於表鎖和行鎖之間，會出現死鎖，併發度一般。

思維導圖一覽

mysql的各種鎖可能會讓人難以理解，理解之前務必心中要有個思維導圖，哪個鎖歸屬哪個引擎，哪個鎖歸屬哪個鎖，心中一定要有個大類和小類的區分，這樣在學起來就不會太難了。

在這裡插入圖片描述

先看一下讀鎖和寫鎖的相容性：

當前鎖模式/是否相容/請求鎖模式 讀鎖 寫鎖
讀鎖 是 否
寫鎖 是 否

博主使用的mysql詳細資訊：
版本引擎等資訊 | 詳細描述
——– | —– | —–
mysql測試版本 | 5.6.49-log
表引擎 | 其中，classromm表為MyISAM，student表為InnoDB。

1、讀鎖

語法：lock table tablename read

會話1：給classroom表加鎖，就不能對student等其它表進行操作了，只能對加鎖的表進行讀操作。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classroom;
+----+---------+
| id | cname   |
+----+---------+
|  1 | php班   |
|  2 | Java班  |
+----+---------+
2 rows in set (0.00 sec)

mysql> select * from student;
ERROR 1100 (HY000): Table 'student' was not locked with LOCK TABLES
mysql> delete from student where id = 6;
ERROR 1100 (HY000): Table 'student' was not locked with LOCK TABLES

會話2：會話1 中給classroom表加鎖，不影響會話2查詢加鎖的表和其它表。

mysql> select * from classroom;
+----+---------+
| id | cname   |
+----+---------+
|  1 | php班   |
|  2 | Java班  |
+----+---------+
2 rows in set (0.00 sec)

會話2：此時會話1雖然對classroom加表鎖了，但是會話2也依然可以對classroom表加表鎖。但是加上表鎖後，會話2也和會話1一樣只能對進行查詢的操作了。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student;
ERROR 1100 (HY000): Table 'student' was not locked with LOCK TABLES
mysql> update classroom set cid = 6 where id = 1;
ERROR 1099 (HY000): Table 'classroom' was locked with a READ lock and can't be updated

結論1：在執行表讀鎖後，當前會話只能訪問加鎖的這個表，不能訪問未加鎖的表，但是非當前會話不受影響。

會話1：對classroom表進行加表鎖，並對id為3的資料進行刪除。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classroom;
+----+---------+
| id | cname   |
+----+---------+
|  1 | php班   |
|  2 | Java班  |
|  3 | 前端    |
+----+---------+
3 rows in set (0.00 sec)

mysql> delete from classroom where id = 3;
ERROR 1099 (HY000): Table 'classroom' was locked with a READ lock and can't be updated

會話2：對classroom表中某行資料進行刪除，但會造成阻塞。

mysql> delete from classroom where id = 3;
……等待

結論2：在執行表讀鎖後，當前會話只能進行查詢操作，不能進行其它操作（update、delete等）。非當前會話可以執行其它操作，但會造成阻塞。

2、寫鎖：

語法：lock table tablename write

會話1：會話1持有classroom表讀鎖，其它會話能持有該表的讀鎖，但不能持有該表的寫鎖。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)

會話2：會話2可在持有classroom的表讀鎖，但不能持有表寫鎖，會造成阻塞。

mysql> lock table classroom read;
Query OK, 0 rows affected (0.00 sec)
mysql> lock table classroom write;
……等待

結論1：當一個會話持有表讀鎖，其它會話可以持有表讀鎖，但不能持有表寫鎖。

會話1：事務1給classroom加表鎖，那只有事務1才能進行增刪改查操作。

mysql> lock table classroom write;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from classroom;
+----+--------+------+
| id | cname  | cid  |
+----+--------+------+
|  1 | php班    |    7 |
|  2 | java班   |    1 |
+----+--------+------+
2 rows in set (0.00 sec)

會話2：會話1加了寫鎖後，會話2不能在對此表進行操作，但是可以對其它表進行操作。

mysql> select * from classroom;
Ctrl-C -- sending "KILL QUERY 214" to server ...
Ctrl-C -- query aborted.
ERROR 1317 (70100): Query execution was interrupted
mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
| 21 | 張無忌      |   32 |
| 22 | 周芷若       |   19 |
+----+--------+------+
2 rows in set (0.04 sec)

mysql> update student set age = 33 where id = 21;
Query OK, 1 row affected (0.09 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from student;
+----+--------+------+
| id | name   | age  |
+----+--------+------+
| 21 | 張無忌      |   33 |
| 22 | 周芷若       |   19 |
+----+--------+------+
2 rows in set (0.00 sec)

結論2：當一個會話持有表寫鎖，那麼該會話只能對該表進行增刪改查操作。其它會話則不能對該表進行一切操作。但是不影響其它會話對別的表進行操作。

3、總結

\ 表讀鎖 表寫鎖
當一個事務已持有表讀/寫鎖，其它事務是否可對該表進行curd 可查不可增刪改 可增刪該查
當一個事務已持有表讀鎖，其它事務能否在繼續持有表讀/寫鎖 能在持有表讀鎖 不能持有表寫鎖
當一個事務已持有表寫鎖，其它事務能否在繼續持有表讀/寫鎖 不能持有表讀鎖 不能持有表寫鎖
當一個事務已持有表讀/寫鎖，那這個事務能否在對別的表進行操作 不能 不能
# 二、InnoDB表鎖（意向鎖）：

在這裡插入圖片描述

意向鎖含義（百度百科）：

意向鎖的含義是如果對一個結點加意向鎖，則說明該結點的下層結點正在被加鎖；對任一結點加鎖時，必須先對它的上層結點加意向鎖。

意向鎖是有資料引擎自己維護的，使用者無法手動干預，在加行級排它鎖或共享鎖之前，InooDB先會判斷所在資料行的資料表中是否有對應的意向鎖。

InooDB是持有行鎖的，MyISAM是沒有行鎖的，既然有行鎖，必然就要了解一下InooDB下行鎖和表鎖之間的那相容性。

下面做個實驗：

會話1：給student表中某一行資料加上共享鎖，並未提交

mysql> select * from student;
+----+--------------+-----+
| id | name         | age |
+----+--------------+-----+
|  1 | 光頭強       |  21 |
|  5 | 吉吉國王     |  40 |
| 10 | 白雪公主     |  26 |
+----+--------------+-----+
3 rows in set (0.00 sec)

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

mysql> select * from student where id = 1 lock in share mode;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 光頭強    |  21 |
+----+-----------+-----+
1 row in set (0.00 sec)

會話2：給student表加上表級共享鎖，看是否會阻塞呢？發現是能加上的。

mysql> lock table student read;
Query OK, 0 rows affected (0.00 sec)

會話2：既然表級共享鎖加不上，表級排它鎖是否能加上呢？ 阻塞了……

mysql> lock table student write;
……

結論1：當一個會話持有某個表的行級共享鎖，其它會話可以獲取該表的表級共享鎖，但不能獲取該表的表級排它鎖。

繼續做第二個實驗：

會話1：給student表某一行資料加上排它鎖，並未提交

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

mysql> select * from student where id = 1 for update;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 光頭強    |  23 |
+----+-----------+-----+
1 row in set (0.00 sec)

會話2：此時視窗2去申請student表的表級共享鎖。出現了阻塞。

mysql> lock table student read;
……

會話2：那去申請排它鎖是否會被阻塞呢？發現也被阻塞了。

mysql> lock table student write;
……

結論2：當一個會話持有某個表的行級排它鎖，其它會話不可以獲取到表級的排它鎖和共享鎖。

以上例子，都是講先加行鎖後，在去加表鎖的情況。下面翻過來試一下，在看下先加表鎖在加行鎖的情況。

看下第三個實驗：

會話1：給student表加上表讀鎖。

mysql> select * from student;
+----+--------------+-----+
| id | name         | age |
+----+--------------+-----+
|  1 | 光頭強       |  24 |
|  5 | 吉吉國王     |  40 |
| 10 | 白雪公主     |  26 |
+----+--------------+-----+
3 rows in set (0.00 sec)

mysql> lock table student read;
Query OK, 0 rows affected (0.00 sec)

會話2：在去看看是否能在申請行寫鎖，發現已經被阻塞了。

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

mysql> select * from student where id = 1 for update;
……

會話2：那再看看是否能夠申請行讀鎖呢？發現是可以申請的

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id = 1 lock in share mode;
+----+-----------+-----+
| id | name      | age |
+----+-----------+-----+
|  1 | 光頭強    |  24 |
+----+-----------+-----+
1 row in set (0.00 sec)

結論3：當一個會話持有某個表的表讀鎖，其它會事務可以在獲取到行級讀鎖，但獲取行級寫鎖會阻塞。

再看下第四個試驗：

會話1：給student表加表讀鎖

mysql> select * from student;
+----+--------------+-----+
| id | name         | age |
+----+--------------+-----+
|  1 | 光頭強       |  24 |
|  5 | 吉吉國王     |  40 |
| 10 | 白雪公主     |  26 |
+----+--------------+-----+
3 rows in set (0.00 sec)

mysql> lock table student write;
Query OK, 0 rows affected (0.00 sec)

會話2：給student表加某一行資料申請行寫鎖，發現會被阻塞

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

mysql> select * from student where id = 1 for update;

會話2：給student表某一行資料申請行讀鎖，依然被阻塞。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from student where id = 1 lock in share mode;
……

結論4：當一個會話持有某個表的表寫鎖鎖，其它會事務都不可以在獲取該表的行級讀寫鎖。

總結

那以上栗子就可以說明：

\ 意向共享鎖（IS） 意向排他鎖（IX）
表級共享鎖（S） 相容 互斥
表級排他鎖（X） 互斥 互斥

明白了以上表級與行級的相容後再去了解意向鎖會更好理解。

意向鎖是表鎖！當我們需要給一個表 加表鎖的時候，我們需要根據意向鎖去判斷表中有沒有資料行被鎖定，以確定是否能加成功。如果意向鎖是行鎖，那麼我們就得遍歷表中所有資料行來判斷。如果意向鎖是表鎖，則我們直接判斷一次就知道表中是否有資料行被鎖定了。

注意注意注意！！！這兒有個坑，我上邊的測試都是mysql5.7版本，但是在mysql5.7版本以下，加了行寫鎖後還能再加表讀鎖。版本不一樣導致結果不一樣，這坑研究了好幾天沒從網上找到答案，一度讓我懷疑人生，以此謹記吧

注意，InnoDB中的行鎖需要在事務中執行才生效。

1、共享鎖（S鎖）

語法：lock in share mode

概念：又名讀鎖，對某一資源加共享鎖，自身可以修改或讀取該資源，其它人也能繼續持有該資源的共享鎖，無法持有該資源的排它鎖。並只能讀取，不能進行其它操作。

會話1：給student表id為6的資料加上共享鎖。

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

mysql> select * from student;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 李北京    | 50    | 6,2  |
|  7 | 王上海    | 200   | 2    |
+----+-----------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from student where id = 6 lock in share mode;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 李北京    | 50    | 6,2  |
+----+-----------+-------+------+
1 row in set (0.00 sec)

會話2：在去修改這條資料會一直造成阻塞，知道超時或者鎖釋放。

mysql> update student set name = '周北京' where id = 6;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

結論1：一個會話給一個表中的某一行加共享鎖，其它會話可讀不可進行其它操作，直到鎖釋放。

會話1：給student表id為6的資料加上共享鎖，看看當前會話是否能在操作別的表中的資料呢？

mysql> select * from student;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 趙北京    | 50    | 6,2  |
|  7 | 王上海    | 200   | 2    |
+----+-----------+-------+------+
2 rows in set (0.00 sec)

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

mysql> select * from student where id = 6 lock in share mode;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 趙北京    | 50    | 6,2  |
+----+-----------+-------+------+
1 row in set (0.00 sec)

mysql> select * from classroom;
+----+---------+
| id | cname   |
+----+---------+
|  1 | php班   |
|  2 | Java班  |
+----+---------+
2 rows in set (0.00 sec)

mysql> update classroom set cname = 'php一班' where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from classroom;
+----+-----------+
| id | cname     |
+----+-----------+
|  1 | php一班   |
|  2 | Java班    |
+----+-----------+
2 rows in set (0.00 sec)

結論2：一個會話給一個表中的某一行加共享鎖，不影響該會話操作其它表，以及自身的表，這與表鎖不同（表鎖是當前會話給該表加表鎖後，那當前會話只能操作該表中的資料，不能在進行操作其它表中的資料了）

會話1：開啟事務，給student表中id為6的加上共享鎖。

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

mysql> select * from student;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 孫北京    | 50    | 6,2  |
|  7 | 王上海    | 200   | 2    |
+----+-----------+-------+------+
2 rows in set (0.00 sec)
mysql> select * from student where id = 6 lock in share mode;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 孫北京    | 50    | 6,2  |
+----+-----------+-------+------+
1 row in set (0.00 sec)

會話2：也給student表中id為6的加上共享鎖。

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

mysql> select * from student;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 孫北京    | 50    | 6,2  |
|  7 | 王上海    | 200   | 2    |
+----+-----------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from student where id = 6 lock in share mode;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 孫北京    | 50    | 6,2  |
+----+-----------+-------+------+
1 row in set (0.00 sec)

會話1：去修改這條資料，會造成阻塞。

mysql> update student set name = '李北京' where id = 6;
……等待

會話2：在去修改這條資料，出現死鎖。

mysql> update student set name = '李北京' where id = 6;
ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction

結論3：當一個會話持有某行的共享鎖，其它會話也可在持有某行的共享鎖，但是兩者同時修改這條資料的話會造成死鎖。

2、排它鎖（X鎖）

語法：for update

概念：又名寫鎖，對某一資源加排它鎖，自身可以修改或讀取該資源，其它會話不能繼續持有該資源的共享鎖和排它鎖。並只能對加鎖資料進行讀取，不能進行其它操作。

排他鎖的申請前提 : 沒有執行緒對該結果集中的任何行資料使用排他鎖或共享鎖，否則申請會阻塞
for update及lock in share mode 僅適用於 InnoDB，且必須在事務塊 (BEGIN/COMMIT) 中才能生效，在進行事務操作時，通過 for update 語句，MySQL會對查詢結果集中每行資料都新增排他鎖，其他執行緒對該記錄的更新與刪除操作都會阻塞，排他鎖包含 行鎖、表鎖

行排它鎖可不是加上以後其它事務就不能查詢該行資料，只是其它事務則不能再去給該行加其它的鎖。mysql InnoDB引擎預設的修改資料語句，update,delete,insert都會自動給涉及到的資料加上排他鎖，select語句預設不會加任何鎖型別，不管是行共享鎖還是行排它鎖都能夠進行查詢的，因為普通查詢沒有任何鎖機制。

會話1：給student表id為6的資料加上排它鎖。

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

mysql> select * from student;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 李北京    | 50    | 6,2  |
|  7 | 王上海    | 200   | 2    |
+----+-----------+-------+------+
2 rows in set (0.00 sec)

mysql> select * from student where id = 6 for update;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 李北京    | 50    | 6,2  |
+----+-----------+-------+------+
1 row in set (0.00 sec)

會話2：會話1加上排它鎖後，會話2是否能夠讀取加鎖資料呢？答案是可以的！

mysql> select * from student where id = 6;
+----+-----------+-------+------+
| id | name      | price | cid  |
+----+-----------+-------+------+
|  6 | 李北京    | 50    | 6,2  |
+----+-----------+-------+------+
1 row in set (0.00 sec)

會話2：會話1加上排它鎖後，會話2是否還能在繼續持有該行的排它鎖呢？答案是被阻塞了。

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

mysql> select * from student where id = 6 for update;
……等待

會話2：會話1加上排它鎖後，會話2是否還能在繼續持有該行的共享鎖呢？也會被阻塞。

mysql> select * from student where id = 6 lock in share mode;
……等待

會話2：會話1加上排它鎖後，會話2是否還能夠修改這條資料呢？答案是肯定不行的！會造成鎖等待，直到資源釋放。

mysql> update student set name = '吳北京' where id = 6;
……等待

結論1：當一個會話持有某行的排它鎖，其它會話則不能在修改資料以及持有改行的共享鎖及排它鎖。會造成阻塞。

3、總結

\ 行讀鎖 行寫鎖
加上行讀/寫鎖後，其它事務能刪改這條資料嗎 會阻塞 會阻塞
加上行讀/寫鎖後，其它事務能讀取這條資料嗎 能 能
加上行讀鎖後，其它事務能在去持有行/讀鎖嗎 能 不能
加上行寫鎖後，其它事務能在去持有行/讀鎖嗎 不能 不能

1、記錄鎖：

視窗1：直接鎖住id為1的記錄

mysql> select * from student;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小王   |
|  2 | 李     |
|  3 | 張     |
+----+--------+
3 rows in set (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from student where id = 1 for update;
+----+--------+
| id | name   |
+----+--------+
|  1 | 小王   |
+----+--------+
1 row in set (0.00 sec)

視窗2：

則不能對id為1的記錄進行修加鎖以及增刪改操作，但是可以查出

mysql> select * from student where id = 1 for update;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

2、間隙鎖：

間隙鎖（Gap Lock）是Innodb在可重複讀提交下為了解決幻讀問題時引入的鎖機制。

3、臨鍵鎖

臨鍵鎖，是記錄鎖與間隙鎖的組合，它的封鎖範圍，既包含索引記錄，又包含索引區間，解決幻讀問題。

本作品採用《CC 協議》，轉載必須註明作者和本文連結

相關文章