一、行級鎖介紹

行鎖的是mysql鎖中粒度最小的一種鎖，因為鎖的粒度很小，是以發生資源争搶的機率也最小，并發性能最大，但是也會造成死鎖，每次加鎖和釋放鎖的開銷也會變大。

1、使用MySQL行級鎖的兩個前提

使用 innoDB 引擎
開啟事務 (隔離級别為 Repeatable Read )

2、InnoDB行鎖的類型

共享鎖（S）：當事務對資料加上共享鎖後, 其他使用者可以并發讀取資料，但任何事務都不能對資料進行修改（擷取資料上的排他鎖），直到已釋放所有共享鎖。
排他鎖（X）：如果事務T對資料A加上排他鎖後，則其他事務不能再對資料A加任任何類型的封鎖。獲準排他鎖的事務既能讀資料，又能修改資料。

二、加鎖的方式

InnoDB引擎預設更新語句，update,delete,insert 都會自動給涉及到的資料加上排他鎖，select語句預設不會加任何鎖類型，如果要加可以使用下面的方式:
加共享鎖（S）：select * from table_name where ... lock in share mode;
加排他鎖（x）：select * from table_name where ... for update;

鎖相容

共享鎖隻能相容共享鎖, 不相容排它鎖；
排它鎖互斥共享鎖和其它排它鎖；

三、行鎖測試

1、資料準備

#建立表
CREATE TABLE innodb_lock(
  id INT PRIMARY KEY AUTO_INCREMENT,
  NAME VARCHAR(20),
  age INT,
  INDEX idx_name(NAME)
);

# 插入資料
INSERT INTO innodb_lock VALUES(NULL,'a', 13);
INSERT INTO innodb_lock VALUES(NULL,'b', 23);
INSERT INTO innodb_lock VALUES(NULL,'c', 33);
INSERT INTO innodb_lock VALUES(NULL,'d', 43);
insert into innodb_lock values(null,'b',53); 
insert into innodb_lock values(null,'c',63); 
insert into innodb_lock values(null,'d',73);

2、打開兩個視窗,并開啟手動送出事務(送出或者復原事務就會釋放鎖)

#開啟MySQL資料庫手動送出
SET autocommit=0;

3、視窗1中, 對id為1的資料進行更新操作,但是不commit. 執行之後,在目前視窗檢視表資料,發現被修改了。

mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

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

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | aaa | 13 |
| 2 | b | 23 |
| 3 | c | 33 |
| 4 | d | 43 |
+----+------+------+

4、在視窗2 檢視表資訊, 無法看到更新的内容

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | a | 13 |
| 2 | b | 23 |
| 3 | c | 33 |
| 4 | d | 43 |
+----+------+------+
4 rows in set (0.00 sec)

總結: 在有寫鎖的情況下,一個事務不允許讀取到另一個事務沒有送出的内容。避免了髒讀的發生。

5、視窗1開啟事務, 對innodb_lock表 id=1的這一行進行讀取。

mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | aaa | 13 |
+----+------+------+

6、視窗2開啟事務, 對id=1的資料進行修改,然後送出事務

begin;
update innodb_lock set name = 'a' where id=1;
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | a | 13 |
| 2 | b | 23 |
| 3 | c | 33 |
| 4 | d | 43 |
+----+------+------+
4 rows in set (0.00 sec)

7、視窗2送出事務後,視窗1再次查詢,還是之前的查詢結果

mysql> select * from innodb_lock where id = 1;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 1 | aaa | 13 |
+----+------+------+

總結: 在有寫鎖的情況下，一個事務内多次讀取同一資料的結果始終保持一緻,避免了不可重複讀的問題。

四、InnoDB行級鎖更新為表級鎖

InnoDB中的行級鎖是「對索引加的鎖，在不通過索引查詢資料的時候，InnoDB就會使用表鎖」。

但是通過索引查詢的時候是否使用索引，還要看Mysql的執行計劃，Mysql的優化器會判斷是一條sql執行的最佳政策。

若是Mysql覺得執行索引查詢還不如全表掃描速度快, 那麼Mysql就會使用全表掃描來查詢，這是即使sql語句中使用了索引, 最後還是執行為全表掃描，加的是表鎖。

下面是行級鎖更新為表級鎖的原因:

未使用到索引
索引失效
索引字段重複率過高

接下來對上面的幾種情況進行一下示範:

1、未使用索引導緻行級鎖更新為表級鎖

-- 視窗1中,設定手動送出,更新資料成功,但是不送出
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update innodb_lock set name = 'lisi' where age = 63;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

-- 視窗2中,開啟事務,然後對id為6的資料進行修改,但是發生阻塞
mysql> update innodb_lock set name = 'wangwu' where id = 6;
-- 阻塞......

2、索引失效導緻行級鎖更新為表級鎖

-- 視窗1中,設定手動送出,更新資料成功,但是不送出
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> update innodb_lock set name = 'lisi' where name like '%c';

-- 視窗2中,開啟事務,然後對id為6的資料進行修改,但是發生阻塞
mysql> update innodb_lock set name = 'wangwu' where id = 6;

3、索引字段重複率過高,導緻索引失效

-- 視窗1,執行查詢 并添加排它鎖
mysql> set autocommit = 0;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where name = 'a' for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 1 | a | 13 |
| 2 | a | 23 |
| 3 | a | 33 |
| 4 | a | 43 |
| 5 | a | 43 |
| 6 | a | 53 |
+----+------+------+
6 rows in set (0.00 sec)

-- 視窗2
mysql> update innodb_lock set name = 'wangwu' where id = 7;
-- 發生阻塞,原因是name字段雖然有索引,但是字段值重複率太高,MySQL放棄了該索引

-- 視窗1 開啟事務,查詢name = b的資料,并加入排它鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where name = 'b' for update;
Empty set (0.00 sec)

-- 視窗1 開啟事務,查詢name = d的資料,并加入排它鎖,查詢到了結果,沒有阻塞的原因是name字段的索引生效了,還是行級的鎖
mysql> begin;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from innodb_lock where name = 'd' for update;
+----+------+------+
| id | name | age |
+----+------+------+
| 8 | d | 73 |
+----+------+------+
1 row in set (0.00 sec)

-- 通過Explain進行分析,可以看到兩條SQL的索引使用情況
mysql> explain select * from innodb_lock where name = 'b';
+----+-------------+-------------+------------+------+---------------+----------
+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key
| key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+----------
+---------+-------+------+----------+-------+
| 1 | SIMPLE | innodb_lock | NULL | ref | idx_name | idx_name
| 63 | const | 1 | 100.00 | NULL |
+----+-------------+-------------+------------+------+---------------+----------
+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from innodb_lock where name = 'a';
+----+-------------+-------------+------------+------+---------------+------+---
------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key |
key_len | ref | rows | filtered | Extra |
+----+-------------+-------------+------------+------+---------------+------+---
------+------+------+----------+-------------+
| 1 | SIMPLE | innodb_lock | NULL | ALL | idx_name | NULL |
NULL | NULL | 8 | 75.00 | Using where |
+----+-------------+-------------+------------+------+---------------+------+---
------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

五、查詢SQL的鎖測試

1、查詢時的排他鎖測試

select語句加排他鎖方式 : select * from table_name where ... for update;

for update 的作用: for update 是在資料庫中上鎖用的，可以為資料庫中的行上一個排他鎖。存在高并發并且對于資料的準确性很有要求的場景，可以選擇使用for update。
for update 的注意點: for update 僅适用于InnoDB，并且必須開啟事務，在begin與commit之間才生效。

2、在視窗1中, 首先開啟事務, 然後對 id為1 的資料進行排他查詢

begin;
select * from innodb_lock where id = 1 for update;

3、在視窗2中，對同一資料分别使用排他查和共享鎖兩種方式查詢

-- 排他鎖查詢
select * from innodb_lock where id = 1 for update;

-- 共享鎖查詢
select * from innodb_lock where id = 1 lock in share mode;

我們看到開了視窗2的排他鎖查詢和共享鎖查詢都會處于阻塞狀态，因為id=1的資料已經被加上了排他鎖，此處阻塞是等待排他鎖釋放。

4、如果隻是使用普通查詢,我們發現是可以的

select * from innodb_lock where id = 1;

5、查詢時的共享鎖測試

添加共享鎖: select * from table_name where ... lock in share mode;
事務擷取了共享鎖，在其他查詢中也隻能加共享鎖,但是不能加排它鎖。

6、視窗1 開啟事務, 使用共享鎖查詢 id = 2 的資料 ,但是不要送出事務

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

mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 2 | b | 23 |
+----+------+------+

7、視窗2 開啟事務, 使用普通查詢和共享鎖查詢 id = 2 的資料 ,是可以的

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

mysql> select * from innodb_lock where id = 2 lock in share mode;
+----+------+------+
| id | NAME | age |
+----+------+------+
| 2 | b | 23 |
+----+------+------+

8、加排他鎖就查不到，因為排他鎖與共享鎖不能存在同一資料上。

select * from innodb_lock where id = 2 for update;
-- 阻塞

InnoDB鎖機制之行級鎖（一）

一、行級鎖介紹

二、加鎖的方式

三、行鎖測試

四、InnoDB行級鎖更新為表級鎖

五、查詢SQL的鎖測試

繼續閱讀

MySQL的varchar存儲原理：InnoDB記錄存儲結構

「資料庫」MySQL 的 varchar 存儲原理：InnoDB 記錄存儲結構

InnoDB存儲引擎的特點：行級鎖

故障分析 | innodb_thread_concurrency 導緻資料庫異常的問題分析

MyISAM 與 InnoDB 索引的差別有哪些？

基于對古羅馬複合柱曆史的研究複合柱是羅馬設計的柱式，結合了古希臘時代愛奧尼亞柱和科林斯柱的特點。複合柱具有高度裝飾的首都

mysql的innodb引擎是如何存儲資料的?

「資料庫」庖丁解InnoDB之REDO LOG

「資料庫」庖丁解InnoDB之UNDO LOG

MySQL InnoDB 行記錄存儲結構分析

「資料庫」庖丁解 InnoDB 之存儲結構

利用Inno Setup在VS編譯時自動建構安裝包

Mysql存儲-InnoDB關鍵特性

InnoDB緩沖池揭秘：MySQL中的資料緩存利器

揭秘InnoDB插入緩沖：提升非唯一輔助索引插入性能的秘密武器

InnoDB中的各種鎖及其應用