天天看點
傳回

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

目錄

InnoDB 鎖的基本類型

鎖的基本模式

共享鎖(Shared Locks )

排它鎖(Exclusive Locks)

意向鎖

鎖的原理

鎖的算法

記錄鎖

間隙鎖

臨鍵鎖

總結​

事務隔離級别的選擇

死鎖

檢視鎖日志

死鎖的避免

InnoDB 鎖的基本類型

https://dev.mysql.com/doc/refman/5.7/en/innodb-locking.html

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

官網把InnoDB的鎖分成了8 類。我們把前面的兩個行級别的鎖（Shared and Exclusive Locks），和兩個表級别的鎖（Intention Locks）稱為鎖的基本模式。

後面三個Record Locks、Gap Locks、Next-Key Locks，我們把它們叫做鎖的算法，也就是在什麼情況下會鎖定什麼範圍。

鎖的基本模式

InnoDB 裡面既有行級别的鎖，又有表級别的鎖。

表鎖與行鎖的差別： 

             鎖定粒度             表鎖 > 行鎖 
             加鎖效率             表鎖 > 行鎖 
             沖突機率             表鎖 > 行鎖 
             并發性能             表鎖 < 行鎖

共享鎖(Shared Locks )

共享鎖，又稱為讀鎖，簡稱S鎖，共享鎖就是多個事務對于同一資料可以共享同一把鎖，都能通路到資料，但是隻能讀不能修改和删除。

加鎖釋鎖方式：

select * from user where id=1 LOCK IN SHARE MODE;

commit/rollback;

排它鎖(Exclusive Locks)

排它鎖,又稱為寫鎖，簡稱X鎖，排他鎖不能與其他鎖并存，如一個事務擷取 了一個資料行的排他鎖，其他事務就不能再擷取該行的鎖（包括共享鎖、排他 鎖），隻有該擷取了排他鎖的事務是可以對該資料行進行讀取和修改操作的。

加鎖釋鎖方式：

自動：delete / update / insert  預設加上X鎖；

手動：select * from student where id=1 FOR UPDATE;

釋放：commit/rollback

意向鎖

意向鎖是由資料引擎自己維護的，使用者無法手動操作意向鎖 。

意向共享鎖（Intention Shared Lock，簡稱IS鎖） 　　

     表示事務準備給資料行加入共享鎖，也就是說一個資料行加共享鎖前必須先取得該表的IS鎖。

意向排他鎖（Intention Exclusive Lock，簡稱IX鎖） 　　

    表示事務準備給資料行加入排他鎖，說明事務在一個資料行加排他鎖前 必須先取得該表的IX鎖。

意向鎖的作用:

1.意向鎖是一個表鎖，這樣在InnoDB裡面就可以支援更多粒度的鎖。

2.提高加鎖的效率

如果沒有意向鎖，當我們準備給一張表加上表鎖的時候，我們就需要去判斷有沒其他的事務鎖定了其中了某些行，如果有的話，肯定不能加上表鎖。那麼這個掃描整張表去判斷是否能成功加上表鎖的操作是非常耗時且效率低下的。

有了意向鎖之後就可以解決這個問題了。隻需要判斷這張表上有沒有意向鎖，如果有意向鎖，就直接傳回加表鎖失敗。如果沒有，就代表可以加鎖成功。

鎖的原理

情形1：沒有索引

#沒有索引的表t1
CREATE TABLE `t1` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(255) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
INSERT INTO `t1` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t1` (`id`, `name`) VALUES (2, '2');
INSERT INTO `t1` (`id`, `name`) VALUES (3, '3');
INSERT INTO `t1` (`id`, `name`) VALUES (4, '4');


#事務1中使用獨占鎖查詢id=1
begin;
SELECT * FROM t1 WHERE id=1 for update;

#事務2中使用獨占鎖查詢id=3
select * from t1 where id=3 for update; 

#事務3中插入id=5
INSERT INTO `t1` (`id`, `name`) VALUES(5, '5');
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

說明沒有索引查詢會鎖住整張表

情形2：主鍵索引

#有索引的表t2(主鍵索引id)
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t2` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');
INSERT INTO `t2` (`id`, `name`) VALUES (9, '9');
INSERT INTO `t2` (`id`, `name`) VALUES (14, '14');

#事務1中使用獨占鎖查詢id=1
begin;
SELECT * FROM t2 WHERE id=1 for update;

#事務2中使用獨占鎖查詢id=1
SELECT * FROM t2 WHERE id=1 for update;

#事務3中使用獨占鎖查詢id=5
SELECT * FROM t2 WHERE id=5 for update;
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

這說明對于有索引的表使用相同的id值去加鎖，會沖突；使用不同的id加鎖，可以加鎖成功(這跟我們平時關于鎖的認知也是一緻的)

情形3:輔助索引

#主鍵索引id,唯一索引name
CREATE TABLE `t3` (
  `id` int(11) ,
  `name` varchar(255) ,
  PRIMARY KEY (`id`),
  UNIQUE KEY `uk_name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t3` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t3` (`id`, `name`) VALUES (5, '5');
INSERT INTO `t3` (`id`, `name`) VALUES (9, '9');
INSERT INTO `t3` (`id`, `name`) VALUES (14, '14');


#事務1中使用獨占鎖查詢name=5
begin;
SELECT * FROM t3 WHERE name=5 for update;

#事務2中使用獨占鎖查詢name=5
SELECT * FROM t3 WHERE name=5 for update;

#事務3中使用獨占鎖查詢id=5
SELECT * FROM t3 WHERE id=5 for update;
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

InnoDB的行鎖，是通過鎖住索引來實作的

 如果鎖住的是索引，一張表沒有索引怎麼辦？

前面關于索引的部落格裡說到過:

1）如果我們定義了主鍵(PRIMARYKEY)，那麼 InnoDB 會選擇主鍵作為聚集索引。

2）如果沒有顯式定義主鍵，則 InnoDB 會選擇第一個不包含有 NULL 值的唯一索引作為主鍵索引。

3）如果也沒有這樣的唯一索引，InnoDB 會選擇内置 6 位元組長的 ROWID 作為隐藏的聚集索引，它會随着行記錄的寫入而遞增。

是以如果查詢沒有使用到索引，會進行全表掃描，然後把每一個隐藏的聚集索引都鎖住了，也就是鎖表。這也是為什麼要求我們建立索引，一個是提高查詢效率，一個是提高并發性。

對于輔助索引，在InnoDB中輔助索引樹裡會存儲二級索引和主鍵的值。是以我們通過輔助索引鎖定一行資料的時候，其鎖定的步驟跟我們檢索資料的步驟是一樣的，會通過輔助索引找到主鍵索引，然後也鎖定 (這跟我們通常了解的鎖定資料的概念也是一樣的)是以在InnoDB中，一旦鎖定了某個索引，可以了解為該記錄行都已經被鎖住

鎖的算法

InnoDB裡有三種鎖的算法:Record Lock,Gap Lock,Next-Key Lock

#有索引的表t2(主鍵索引id)
CREATE TABLE `t2` (
  `id` int(11) NOT NULL,
  `name` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO `t2` (`id`, `name`) VALUES (1, '1');
INSERT INTO `t2` (`id`, `name`) VALUES (5, '5');
INSERT INTO `t2` (`id`, `name`) VALUES (9, '9');
INSERT INTO `t2` (`id`, `name`) VALUES (14, '14');

先說明下三種範圍的概念:因為t2隻有主鍵索引，是以這裡的劃分标準就是目前主鍵索引的值

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

我們把它資料庫裡面存在的主鍵值，叫做Record---記錄，這裡就有4個Record。

根據存在的Record，将整個區域隔開的一個個區間，對于資料不存在的區間，稱之為 Gap--間隙，是一個左開右開的區間。

将間隙（Gap）連同它左邊的記錄（Record），一起稱之為Next-Key,即臨鍵的區間，這是一個左開右閉的區間。

記錄鎖

對于唯一性的索引（包括唯一索引和主鍵索引）使用等值查詢，如果能夠精确比對到一條記錄，這個時候使用的就是記錄鎖

前面我們使用不同的id去查詢加鎖的時候就驗證過了，此時查詢不同的id并給其加排他鎖不會發生沖突，說明隻鎖住這個record

間隙鎖

如果我們查詢的記錄不存在，沒有命中任何一個record，無論是用等值查詢還是範圍查詢的時候，它使用的都是間隙鎖，會鎖住一個間隙區間。間隙鎖定是對索引記錄之間的間隙的鎖定，或者是對第一個或最後一個索引記錄之間的間隙的鎖定

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

InnoDB會将索引id的區間進行劃分，分為：（-infinite，1），（1，5），（5，9），（9，14），（14，+infinite）五個區間；這裡鎖住的是(5,9)區間

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

如果其他事務想在（5,9）區間内插入新的資料記錄，将會阻塞，如上圖

間隙鎖主要是阻塞插入insert。相同的間隙鎖之間不沖突，如下圖:

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

猜測是因為Gap鎖出現的條件是沒有命中任何一個record,由于已經将整個間隙鎖住，那麼相同的間隙鎖的查詢結果都會和之前的一緻，也就沒必要産生沖突

Gap間隙鎖鎖定範圍

假設SQL裡where中範圍查找中的最小值、最大值分别為sMin和sMax，則鎖定區間(lMin,lMax)滿足如下條件:

  • lMin的值為表索引值中小于or等于sMin中的最大值，對于sMin=6的情況，lMin=5。
  • lMax的值為表索引值中大于or等于sMax中的最小值，對于sMax=6的情況，lMax=9.

Gap Lock 隻在 RR 中存在。如果要關閉間隙鎖，就需要把事務隔離級别設定成RC或者把innodb_locks_unsafe_for_binlog設定為ON(現已棄用)。這種情況下除了外鍵限制和唯一性檢查會加間隙鎖，其他情況都不會使用間隙鎖。

臨鍵鎖

如果我們使用了範圍查詢，不僅僅命中了Record記錄，還包含了 Gap間隙，在這種情況下我們使用的就是臨鍵鎖，它是MySQL裡面預設的行鎖算法，相當于記錄鎖加上間隙鎖,。

其他兩種退化的情況：

如果使用唯一性索引等值查詢比對到一條記錄的時候，會退化成記錄鎖。

如果沒有比對到任何記錄的時候，會退化成間隙鎖。

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

會鎖住命中的目前Next-Key區間和下一個Next-Key區間,這裡是(5，9]，（9，14]

試驗不難發現，在其他事務下嘗試在Next-Key Lock鎖住的區間内插入資料會被阻塞，如下圖

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

mysql> insert into t2 values (8,'8');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
mysql> insert into t2 values (12,'12');
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction

但是在其他事務查詢Next-Key Lock鎖住區間内不存在的記錄不會被阻塞，但是查詢存在的記錄會被阻塞

我的了解:對于區間内不存在的記錄，由于插入會被阻塞，是以其他事務不可能插入，也就是說查詢的時候一定不會查詢到資料，也就不用擔心會出現幻讀的問題 ;沒必要去阻塞; 但是對于存在的記錄，在事務1内可能發生變化，是以不能被其他事務鎖定，這跟間隙鎖阻塞插入insert,但是相同的間隙鎖之間不沖突的道理是一樣的，都是為了提高SQL的并發性。執行結果如下圖:

mysql> select * from t2 where id = 8 for update;
Empty set (0.00 sec)

mysql> select * from t2 where id = 13 for update;
Empty set (0.00 sec)

mysql> select * from t2 where id = 15 for update;
Empty set (0.00 sec)

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

注意區間範圍 : 這裡Next-Key Lock鎖住的區間會包含最大命中的記錄的下一個區間

看下官網的描述

A next-key lock is an index-record lock plus a gap lock on the gap preceding the index record. If one session has a shared or exclusive lock on record R in an index, another session cannot insert a new index record in the gap immediately before R in the index order.

這裡說臨鍵鎖會鎖住對于的記錄和記錄前面的gap,防止其他事務在此區間内進行插入

When InnoDB scans an index, it can also lock the gap after the last record in the index

select * from t2 where id >6 and id<=9 for update ;

//鎖住的是(5，9]，（9，14]  這裡命中的最大記錄是9,是以鎖定區間還會包含(9,14]

select * from t2 where id >6 and id<=14 for update;

//鎖住的是(5，9]，（9，14] ,(14,正無窮) 這裡命中的最大記錄是14,是以鎖定區間還會包含(14,正無窮)

總結 下臨鍵鎖: Next-Key Lock鎖定範圍會包含sql命中的最大記錄的下一個區間(這是這裡的Next-Key的含義);當在事務1使用臨鍵鎖鎖定範圍時，在區間範圍内其他事務都不可能插入記錄，但是可以使用select ... for update查詢區間範圍内不存在的記錄且不會阻塞

總結
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

Read Uncommited

RU隔離級别：不加鎖。

Serializable

Serializable 所有的 select 語句都會被隐式的轉化為select ... in share mode，會和update、delete互斥。

Repeatable Read

RR隔離級别下，普通的select使用快照讀(snapshotread)，底層使用MVCC來實作。

加鎖的 select(select ... in share mode / select ... for update)以及更新操作update, delete 等語句使用目前讀（current read），底層使用記錄鎖、或者間隙鎖、臨鍵鎖。

Read Commited

RC隔離級别下，普通的select都是快照讀，使用MVCC實作。加鎖的select都使用記錄鎖，因為沒有Gap Lock。

除了兩種特殊情況——外鍵限制檢查(foreign-key constraint checking)以及重複鍵檢查(duplicate-key checking)時會使用間隙鎖封鎖區間

事務隔離級别的選擇

RU 和 Serializable 肯定不能用。我們來看看RR和RC的差別

RC和RR主要有幾個差別：

1、 RR的間隙鎖會導緻鎖定範圍擴大。

2、 條件列如果未使用到索引，RR會鎖表，RC隻會鎖行(RC中沒有Gap鎖，隻有Record鎖)。

3、在一緻性讀方面的差別：

RC隔離級别時，事務中的每一條select語句會讀取到該SQL執行時已經送出了的記錄，也就是每一條select都有自己的一緻性讀ReadView ； 

而RR隔離級别時，事務中的一緻性讀的ReadView是以第一條select語句的運作時間，作為本事務的一緻性讀snapshot的建立時間點的。隻能讀取該時間點之前已經送出的資料。

4. RC 隔離級别，通過 where 條件過濾之後，不符合條件的記錄上的行鎖，會釋放掉；但是RR隔離級别，即使不符合where條件的記錄，也不會釋放行鎖和gap lock

5、 RC的“半一緻性”（semi-consistent）讀可以增加update操作的并發性。

A type of read operation used for UPDATE statements, that is a combination of read committed and consistent read. When an UPDATE statement examines a row that is already locked, InnoDB returns the latest committed version to MySQL so that MySQL can determine whether the row matches the WHERE condition of the UPDATE. If the row matches (must be updated), MySQL reads the row again, and this time InnoDB either locks it or waits for a lock on it. This type of read operation can only happen when the transaction has the read committed isolation level, or when the innodb_locks_unsafe_for_binlog option is enabled.

簡單來說，semi-consistent read是read committed與consistent read兩者的結合。一個update語句，如果讀到一行已經加鎖的記錄，此時InnoDB傳回記錄最近送出的版本，由MySQL上層判斷此版本的資料是否滿足 update的where條件。若滿足(也就是需要更新已加鎖的記錄)，則MySQL會重新發起一次讀操作，此時會讀取行資料的最新版本并加鎖。

semi-consistent read隻會發生在read committed隔離級别下，或者是參數innodb_locks_unsafe_for_binlog被設定為true(該參數即将被廢棄)。

對比RR隔離級别，update語句會使用目前讀，如果其中的一行被鎖定了，那麼就會被阻塞，等待其他事務釋放鎖;而不會讀取最新的送出版本，然後來判斷是否符合where條件。

死鎖

MySQL中使用鎖來實作事務隔離級别，那麼不可避免的會出現死鎖問題

#事務1 按照step的順序執行SQL
begin; 
select *from t2 where id = 1 for update; -- step1
delete  from t2 where id = 5 ; -- step4

#事務2
begin; 
delete from t2 where id  = 5 ; -- step2
delete  from t2 where id = 1 ; -- step3
MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

發現在執行SQL的時候，系統會檢測到死鎖然後主動釋放，必不會等待擷取鎖逾時

因為死鎖的發生需要滿足一定的條件，是以在發生死鎖時，InnoDB一般都能通過算法（wait-for graph）自動檢測到，然後立即釋放鎖，而不需要多餘的等待

死鎖的産生條件：

  1. 互斥條件
  2. 同一時刻隻能有一個事務持有這把鎖
  3. 其他的事務需要在這個事務釋放鎖之後才能擷取鎖，而不可以強行剝奪
  4. 當多個事務形成等待環路的時候，即發生死鎖。

檢視鎖日志

show status like 'innodb_row_lock_%';

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

SHOW指令可以查詢鎖的概要資訊。InnoDB還提供了三張表來分析事務與鎖的情況：

select * from information_schema.INNODB_TRX ; -- 目前運作的所有事務 ，還有具體的語句

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

select * from information_schema.INNODB_LOCKS; -- 目前出現的鎖

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

select * from information_schema.INNODB_LOCK_WAITS; -- 鎖等待的對應關系

MySQL(六)InnoDB鎖詳解InnoDB 鎖的基本類型

如果一個事務長時間持有鎖不釋放，我們就可以可以 kill 事務對應的線程 ID(INNODB_TRX表中的trx_mysql_thread_id)來強行釋放鎖

死鎖的避免

  • 在程式中，操作多張表時，盡量以相同的順序來通路（避免形成等待環路）；
  • 批量操作單張表資料的時候，先對資料進行排序（避免形成等待環路）；
  • 申請足夠級别的鎖，如果要操作資料，就直接申請排它鎖；
  • 盡量使用索引通路資料，避免沒有where條件的操作，防止鎖表；
  • 盡量使用等值查詢而不是範圍查詢查詢資料，避免間隙鎖對并發的影響。
mysql 鎖算法 next-key GAP record
上一篇: 軍營觀察：一名軍士教員帶出32名冠軍學員
下一篇: “過期廢棄”條款有效嗎？“概不退換”誰說了算？法官建議您這樣維權

繼續閱讀