MySQL學習之聊聊InnoDB中鎖的情況

本篇文章帶大家聊聊MySQL，介紹一下InnoDB中鎖的情況。有一定的參考價值，有需要的朋友可以參考一下，希望對大家有所幫助。

mysql> select @@version;
+-----------+
| @@version |
+-----------+
| 5.7.21    |
+-----------+
1 row in set (0.01 sec)

一，鎖的基本介紹

相對其他資料庫而言，MySQL的鎖機制比較簡單，其最顯著的特點是不同的儲存引擎支援不同的鎖機制。比如，MyISAM和MEMORY儲存引擎採用的是表級鎖（table-level locking）；InnoDB儲存引擎既支援行級鎖（row-level locking），也支援表級鎖，但預設情況下是採用行級鎖。

表級鎖：開銷小，加鎖快；不會出現死鎖；鎖定粒度大，發生鎖衝突的概率最高，並行度最低。

行級鎖：開銷大，加鎖慢；會出現死鎖；鎖定粒度最小，發生鎖衝突的概率最低，並行度也最高。

行級鎖型別：

Record Lock(記錄鎖)：當個記錄的鎖（鎖住單條記錄）

記錄鎖只會鎖住索引的記錄，如果InnoDB儲存表在建立的時候沒有任何索引，那麼這個鎖會使用隱式的主鍵來進行鎖定，如下圖

Gap Lock(間隙鎖)：鎖定一個範圍，不包括記錄本身(只鎖資料前面的GAP)

如下圖為的鎖就是GAP鎖，就是不允許其他事務在索引列8之前的間隙插入新的記錄，也就是(3 , 8)這個區間。gap鎖的作用僅僅是為了防止插入幻影記錄的而已

Next-Key Lock(臨鍵鎖)：同時鎖住記錄和記錄前面的GAP，也就是Next-Key Lock = Record Lock + Gap Lock。

二，鎖的分類

共用鎖 Share Locks (簡稱S鎖，屬於行鎖)

排它鎖 Exclusive Locks (簡稱X鎖，屬於行鎖)

意向共用鎖 Intention Share Locks (簡稱IS鎖，屬於表鎖)

意向排它鎖 Intention Exclusive Locks (簡稱IX鎖，屬於表鎖)

自增鎖 AUTO-INC Locks(屬於表鎖)

下面具體介紹下每種型別的鎖，我們先建一張innodb的表，sql如下

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

共用鎖

共用鎖就是多個事務對於同一個資料可以共用一把鎖，都能存取資料庫，但是隻能讀不能修改；

事務A：

select * from tab_with_index lock in share mode;

事務B：

select * from tab_with_index where id =1; // 可以查詢資料

update tab_with_index set name = 'aa' where id = 1 ;

注意：這裡的修改語句會堵塞住，直到事務A提交之後才能操作成功。

排它鎖

排它鎖不能與其他鎖並存，如一個事務獲取了一個資料行的排它鎖，其他事務就不能在獲取該行的鎖，只有當前獲取排它鎖的事務可以對資料進行修改。（delete，update，create預設是排它鎖）

事務A：

select * from tab_with_index where id =1 for update;

事務B：

select * from tab_with_index where id =1; //可以獲取結果

select * from tab_with_index where id =1 for update; // 堵塞

select * from tab_with_index where id = 1 lock for share mode; // 堵塞

注意：事務B兩個sql都會堵塞住,也就是獲取不到共用鎖也獲取不到排它鎖，直到事務A提交之後才能操作成功。

意向共用鎖和意向排它鎖

意向共用鎖：表示事務準備給資料行加入共用鎖，也就是說一個資料行在加共用鎖之前必須先獲取該表的IS鎖。

意向排它鎖：表示事務準備給資料行加入排它鎖，也就是說一個資料行加排它鎖之前必須先獲取該表的IX鎖。

IS鎖和IX鎖是表級鎖，他們的提出僅僅為了在之後加表級別的S鎖和X鎖時可以快速判斷表中的記錄是否被上鎖，以避免用遍歷的方式來檢視表中有沒有上鎖的記錄，也就是說其實IS鎖和IX鎖是相容的，IX鎖和IX鎖是相容的。《MySQL是怎樣執行的》

自增鎖

針對自增列自增長的一個特殊的表級別鎖。

show variables like 'innodb_autoinc_lock_mode'; 
-- 預設值1，代表連續，事務未提交則ID永久丟失

三，InnoDB鎖

1、事務及其ACID屬性

事務是由一組SQL語句組成的邏輯處理單元，事務具有4屬性，通常稱為事務的ACID屬性。

原子性（Actomicity）：事務是一個原子操作單元，其對資料的修改，要麼全都執行，要麼全都不執行。一致性（Consistent）：在事務開始和完成時，資料都必須保持一致狀態。隔離性（Isolation）：資料庫系統提供一定的隔離機制，保證事務在不受外部並行操作影響的「獨立」環境執行。永續性（Durable）：事務完成之後，它對於資料的修改是永久性的，即使出現系統故障也能夠保持。

2、並行事務帶來的問題

相對於序列處理來說，並行事務處理能大大增加資料庫資源的利用率，提高資料庫系統的事務吞吐量，從而可以支援更多使用者的並行操作，但與此同時，會帶來一下問題：

髒讀：一個事務正在對一條記錄做修改，在這個事務並提交前，這條記錄的資料就處於不一致狀態；這時，另一個事務也來讀取同一條記錄，如果不加控制，第二個事務讀取了這些「髒」的資料，並據此做進一步的處理，就會產生未提交的資料依賴關係。這種現象被形象地叫做「髒讀」

不可重複讀：一個事務在讀取某些資料已經發生了改變、或某些記錄已經被刪除了！這種現象叫做「不可重複讀」。

幻讀：一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為「幻讀」

上述出現的問題都是資料庫讀一致性的問題，可以通過事務的隔離機制來進行保證。

資料庫的事務隔離越嚴格，並行副作用就越小，但付出的代價也就越大，因為事務隔離本質上就是使事務在一定程度上序列化，需要根據具體的業務需求來決定使用哪種隔離級別

	髒讀	不可重複讀	幻讀
read uncommitted	√	√	√
read committed		√	√
repeatable read			√
serializable

可以通過檢查InnoDB_row_lock狀態變數來分析系統上的行鎖的爭奪情況：

mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name                 | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0     |
| Innodb_row_lock_time          | 18702 |
| Innodb_row_lock_time_avg      | 18702 |
| Innodb_row_lock_time_max      | 18702 |
| Innodb_row_lock_waits         | 1     |
+-------------------------------+-------+
--如果發現鎖爭用比較嚴重，如InnoDB_row_lock_waits和InnoDB_row_lock_time_avg的值比較高

3、InnoDB的行鎖模式及加鎖方法

共用鎖（S） ：又稱讀鎖(lock in share mode)。允許一個事務去讀一行，阻止其他事務獲得相同資料集的排他鎖。若事務T對資料物件A加上S鎖，則事務T可以讀A但不能修改A，其他事務只能再對A加S鎖，而不能加X鎖，直到T釋放A上的S鎖。這保證了其他事務可以讀A，但在T釋放A上的S鎖之前不能對A做任何修改。 排他鎖（X） ：又稱寫鎖(for update)。允許獲取排他鎖的事務更新資料，阻止其他事務取得相同的資料集共用讀鎖和排他寫鎖。若事務T對資料物件A加上X鎖，事務T可以讀A也可以修改A，其他事務不能再對A加任何鎖，直到T釋放A上的鎖。

mysql InnoDB引擎預設的修改資料語句：update,delete,insert都會自動給涉及到的資料加上排他鎖，select語句預設不會加任何鎖型別，如果加排他鎖可以使用select …for update語句，加共用鎖可以使用select … lock in share mode語句。所以加過排他鎖的資料行在其他事務中是不能修改資料的，也不能通過for update和lock in share mode鎖的方式查詢資料，但可以直接通過select …from…查詢資料，因為普通查詢沒有任何鎖機制。

4、InnoDB行鎖實現方式

InnoDB行鎖是通過給索引上的索引項加鎖來實現的，這一點MySQL與Oracle不同，後者是通過在資料塊中對相應資料行加鎖來實現的。InnoDB這種行鎖實現特點意味著：只有通過索引條件檢索資料，InnoDB才使用行級鎖，否則，InnoDB將使用表鎖！

1、在不通過索引條件查詢的時候，innodb使用的是表鎖而不是行鎖

create table tab_no_index(id int,name varchar(10)) engine=innodb;
insert into tab_no_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_no_index where id = 1;	set autocommit=0 select * from tab_no_index where id =2
select * from tab_no_index where id = 1 for update
	select * from tab_no_index where id = 2 for update;

session1只給一行加了排他鎖，但是session2在請求其他行的排他鎖的時候，會出現鎖等待。原因是在沒有索引的情況下，innodb只能使用表鎖。

2、建立帶索引的表進行條件查詢，innodb使用的是行鎖

create table tab_with_index(id int,name varchar(10)) engine=innodb;
alter table tab_with_index add index id(id);
insert into tab_with_index values(1,'1'),(2,'2'),(3,'3'),(4,'4');

session1	session2
set autocommit=0 select * from tab_with_indexwhere id = 1;	set autocommit=0 select * from tab_with_indexwhere id =2
select * from tab_with_indexwhere id = 1 for update
	select * from tab_with_indexwhere id = 2 for update;

3、由於mysql的行鎖是針對索引加的鎖，不是針對記錄加的鎖，所以雖然是存取不同行的記錄，但是依然無法存取到具體的資料（這裡是表鎖）

alter table tab_with_index drop index id;
insert into tab_with_index  values(1,'4');

session1	session2
set autocommit=0	set autocommit=0
select * from tab_with_index where id = 1 and name='1' for update
	select * from tab_with_index where id = 1 and name='4' for update 雖然session2存取的是和session1不同的記錄，但是鎖的是具體表，所以需要等待鎖

總結

對於InnoDB表，本文主要討論了以下幾項內容： （1）InnoDB的行鎖是基於索引實現的，如果不通過索引存取資料，InnoDB會使用表鎖。（2）在不同的隔離級別下，InnoDB的鎖機制和一致性讀策略不同。

在瞭解InnoDB鎖特性後，使用者可以通過設計和SQL調整等措施減少鎖衝突和死鎖，包括：

儘量使用較低的隔離級別；精心設計索引，並儘量使用索引存取資料，使加鎖更精確，從而減少鎖衝突的機會；
選擇合理的事務大小，小事務發生鎖衝突的機率也更小；
給記錄集顯式加鎖時，最好一次性請求足夠級別的鎖。比如要修改資料的話，最好直接申請排他鎖，而不是先申請共用鎖，修改時再請求排他鎖，這樣容易產生死鎖；
不同的程式存取一組表時，應儘量約定以相同的順序存取各表，對一個表而言，儘可能以固定的順序存取表中的行。這樣可以大大減少死鎖的機會；
儘量用相等條件存取資料，這樣可以避免間隙鎖對並行插入的影響；不要申請超過實際需要的鎖級別；除非必須，查詢時不要顯示加鎖；
對於一些特定的事務，可以使用表鎖來提高處理速度或減少死鎖的可能。

【相關推薦：】

以上就是MySQL學習之聊聊InnoDB中鎖的情況的詳細內容，更多請關注TW511.COM其它相關文章！