認識什麼是鎖，MySQL中鎖如何解決幻讀問題

MySQL教學欄位介紹鎖定如何解決幻讀問題。

前言

今天就來為大家介紹一下MySQL中鎖定相關的知識。

本文在沒有特別宣告的情況下，皆為預設InnoDB引擎，如涉及其他引擎或資料庫則會特別指出。

什麼是鎖定

鎖定是一種用來保證在並發場景下每個事務仍能以一致性的方式讀取和修改資料的方式，當一個事務對某一條資料上鎖之後，其他事務就不能修改或只能阻塞等待鎖的釋放，所以鎖的粒度大小一定程度上可以影響到存取資料庫的效能。

從鎖的粒度上來說，我們可以將鎖分為表鎖和行鎖。

表鎖定

顧名思議，表鎖就是直接鎖定表，在MyISAM引擎中就只有表鎖。

表鎖的加鎖方式為：

LOCK TABLE 表名 READ;--锁定后表只读
UNLOCK TABLE; --解锁复制代码

行鎖

#行鎖，從名字上來看，就是鎖住一行數據，然而，行鎖的實際實現演算法會比較複雜，有時候不只鎖住某一條數據，這個後面再展開。

正常的思路是：鎖住一行數據之後，其他事務就不能來訪問這條數據了，那麼我們想像，假如事務A訪問了一條數據，只是拿出來讀一下，並不想去修改，正好事務B也來存取這條數據，也僅僅只是想拿出來讀一下，並不想去修改，這時候如果因此阻塞了，就有點浪費性能了。所以為了優化這種讀取資料的場景，我們又把行鎖分為了兩大類型：共享鎖定和排他鎖定。

共享鎖

共享鎖，Shared Lock，又稱之為讀鎖，S鎖，就是說一條數據被加了S鎖之後，其他事務也能來讀數據，可以共享一把鎖。
我們可以透過如下語句加上共享鎖定：

select * from test where id=1 LOCK IN SHARE MODE;复制代码

加上鎖定之後，直到加鎖的事務結束(提交或回滾)就會釋放鎖定。

排他鎖

排他鎖，Exclusive Lock，又稱之為寫鎖，X鎖。是說一條資料被加了X鎖之後，其他事務想來存取這條資料只能阻塞等待鎖的釋放，具有排他性。

當我們在修改數據，如：insert,update,delete的時候MySQL就會自動加上排他鎖，同樣的，我們可以透過如下sql語句手動加上排他鎖：

select * from test where id=1 for update;复制代码

在InnoDB引擎中，是允許行鎖和表鎖共存的。

但是這樣就會有一個問題，假如事務A給t表其中一行資料上鎖了，這時候事務B想給t表上一個表鎖，這時候怎麼辦呢？事務B怎麼知道t表有沒有行鎖的存在，如果採用全表遍歷的情況，當表中的資料很大的話，加鎖都要加半天，所以MySQL中就又引入了意向鎖。

意向鎖

意向鎖為表鎖，分為兩種類型，分為：意向共享鎖(Intention Shared Lock)和意向排他鎖(Intention Exclusive Lock)，這兩種鎖又分別可以簡稱為IS鎖和IX鎖。

意向鎖是MySQL自己維護的，使用者無法手動加意向。

意向鎖定有兩大加鎖規則：

• #當需要給一行資料加上S鎖定的時候，MySQL會先給這張表加上IS鎖定。
• 當需要為一行資料加上X鎖定的時候，MySQL會先為這張表加上IX鎖定。

這樣的話上面的問題就迎刃而解了，當需要給一張表上表鎖的時候，只需要看這張表是否有對應的意向鎖就可以了，無需遍歷整張表。

各種鎖定的相容關係

下面這張圖是各種鎖定的相容關係，參考自官網：

##XIXS共享#(

	X	IX	S	IS
	互斥	互斥	#互斥	互斥
		#分享	衝突	共享
	互斥			#互斥
	#共享	IS	互斥	共享

#####共享# ##############

锁到底锁的是什么

建立以下两张表，并初始化5条数据，注意test表有2个索引而test2没有索引：

CREATE TABLE `test` (
  `id` int(11) NOT NULL,
  `name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `NAME_INDEX` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test VALUE(1,'张1');
INSERT INTO test VALUE(5,'张5');
INSERT INTO test VALUE(8,'张8');
INSERT INTO test VALUE(10,'张10');
INSERT INTO test VALUE(20,'张20');

CREATE TABLE `test2` (
  `id` varchar(32) NOT NULL,
  `name` varchar(32) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

INSERT INTO test2 VALUE(1,'张1');
INSERT INTO test2 VALUE(5,'张5');
INSERT INTO test2 VALUE(8,'张8');
INSERT INTO test2 VALUE(10,'张10');
INSERT INTO test2 VALUE(20,'张20');复制代码

举例猜测

在行锁中，假如我们对一行记录加锁，那么到底是把什么东西锁住了，我们来看下面两个例子：
举例1（操作test表）：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=1 FOR UPDATE;
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=5 FOR UPDATE; 加锁成功
COMMIT; （释放锁）
	SELECT * FROM test WHERE id=1 FOR UPDATE; 加锁成功

举例2（操作test2表）：

事务A	事务B
BEGIN;
SELECT * FROM test2 WHERE id=1 FOR UPDATE;
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 阻塞
	SELECT * FROM test2 WHERE id=5 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT * FROM test2 WHERE id=1 FOR UPDATE; 加锁成功

从上面两个例子我们可以发现，test表好像确实是锁住了id=1这一行的记录，而test2表好像不仅仅是锁住了id=1这一行记录，实际上经过尝试我们就知道，test2表是被锁表了，所以其实MySQL中InnoDB锁住的是索引，当没有索引的时候就会锁表。

接下来再看一个场景：

事务A	事务B
BEGIN;
SELECT * FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

这个例子中我们是把name索引锁住了，然后我们在事务B中通过主键索引只查id，这样就用到name索引了，但是最后发现也被阻塞了。所以我们又可以得出下面的结论，MySQL索引不但锁住了辅助索引，还会把辅助索引对应的主键索引一起锁住。

到这里，可能有人会有怀疑，那就是我把辅助索引锁住了，但是假如加锁的时候，只用到了覆盖索引，然后我再去查主键会怎么样呢？

接下来让我们再验证一下：

事务A	事务B
BEGIN;
SELECT name FROM test WHERE name=‘张1’ FOR UPDATE;
	SELECT name FROM test WHERE name=‘张1’ FOR UPDATE; 阻塞
	SELECT * FROM test WHERE id=1 FOR UPDATE; 阻塞
	SELECT id FROM test WHERE id=1 FOR UPDATE; 阻塞
COMMIT; （释放锁）
	SELECT id FROM test WHERE id=1 FOR UPDATE; 加锁成功

我們可以看到，就算只是用到了輔助索引加鎖，MySQL還是會把主鍵索引鎖住，而主鍵索引的B 樹葉子節點中，又存儲了整條數據，所以查詢任何字段都會被鎖定。

到這裡，我們可以明確的給鎖到底鎖住了什麼下結論了：

結論

InnoDB引擎中，鎖定的是索引：

• 假如一張表沒有索引，MySQL會進行鎖定表(其實鎖住的是隱藏列ROWID的主鍵索引)
• 假如我們對輔助索引加鎖，那麼輔助索引所對應的主鍵索引也會被鎖住
• 主鍵索引被鎖住，實際上就等於是整筆記錄都被鎖住了(主鍵索引葉子節點儲存了整個資料)

行鎖的演算法

上一篇介紹事務的時候我們提到了，MySQL透過加鎖來防止了幻讀，但是如果行鎖只是鎖住一行記錄，好像並不能防止幻讀，所以行鎖鎖住一條記錄的話只是其中一種情況，實際上行鎖有三種演算法：記錄鎖(Record Lock)，間隙鎖(Gap Lock)和臨鍵鎖(Next-Key Lock)，而之所以能做到防止幻讀，正是臨鍵鎖扮演的角色。

記錄鎖定(Record Lock)

記錄鎖定就是上面介紹的，當我們的查詢能命中一筆記錄的時候，InnoDB就會使用記錄鎖定，鎖定住所命中的這一行記錄。

間隙鎖定(Gap Lock)

當我們的查詢沒有命中記錄的時候，這時候InnoDB就會加上一個間隙鎖定。

SELECT * FROM test WHERE id=1 FOR UPDATE;#SELECT * FROM test WHERE id=2 FOR UPDATE;

交易A	交易B
BEGIN;
	##BEGIN;
	INSERT INTO test VALUE (2,'張2'); 阻塞
	INSERT INTO test VALUE (3,'張3'); 阻塞
	加鎖成功

COMMIT;

（釋放鎖定）

• ##從上面的例子中，我們可以得出結論：

間隙鎖與間隙鎖之間不衝突，也就是事務A加了間隙鎖，事務B可以在同一個間隙中加間隙鎖。

(之所以會用到間隙鎖就是沒有命中資料的時候，所以並沒有必要去阻塞讀，也沒有必要阻塞其他事務對同一個間隙加鎖)

##間隙鎖主要是會阻塞插入操作

間隙是如何決定的

test表中有5筆記錄，主鍵值分別為:1,5,8,10 ,20。則會有以下六個間隙：

(-∞,1),(1,5),(5,8),(8,10),(10,20),(20, ∞)

而假如主鍵不是int型，那麼就會轉換為ASCII碼之後再確定間隙。

臨鍵鎖定(Next-Key Lock)

臨鍵鎖定就是記錄鎖定和間隙鎖定的結合。當我們進行一個範圍查詢，不但命中了一條或多筆記錄，且同時包含了間隙，這時候就會使用臨鍵鎖，臨鍵鎖是InnoDB中行鎖的預設演算法。

• 注意了，這裡僅針對RR隔離級別，對於RC隔離級除了外鍵約束和唯一性約束會加間隙鎖，沒有間隙鎖，自然也就沒有了臨鍵鎖，所以RC級別下加的行鎖都是記錄鎖，沒有命中記錄則不加鎖，所以RC級別是沒有解決幻讀問題的
• 。

臨鍵鎖在以下兩個條件時會降級成為間隙鎖定或記錄鎖定：#當查詢未命中任務記錄時，會降級為間隙鎖定。 當使用主鍵或唯一索引命中了一筆記錄時，會降級為記錄鎖定。 交易A交易B阻塞阻斷阻斷阻斷## INSERT INTO test VALUE (9,'張9');

BEGIN;
SELECT * FROM test WHERE id>=2 AND id
##INSERT INTO test VALUE (2,'張2') ;
INSERT INTO test VALUE (6,'張6');
INSERT INTO test VALUE (8,'張8');
SELECT * FROM test WHERE id=8 FOR UPDATE;
	插入成功

COMMIT;###（釋放鎖定）######### #################

上面这个例子，事务A加的锁跨越了(1,5)和(5,8)两个间隙，且同时命中了5，然后我们发现我们对id=8这条数据进行操作也阻塞了，但是9这条记录插入成功了。

临键锁加锁规则

临键锁的划分是按照左开右闭的区间来划分的，也就是我们可以把test表中的记录划分出如下区间：(-∞,1],(1,5],(5,8],(8,10],(10,20],(20,+∞)。

那么临键锁到底锁住了哪些范围呢？

**临键锁中锁住的是最后一个命中记录的 key 和其下一个左开右闭的区间**

那么上面的例子中其实锁住了(1,5]和(5,8]这两个区间。

临键锁为何能解决幻读问题

临键锁为什么要锁住命中记录的下一个左开右闭的区间？答案就是为了解决幻读。

我们想一想上面的查询范围id>=2且id

当然，其实如果我们执行的查询刚好是id>=2且id

在我们使用锁的时候，有一个问题是需要注意和避免的，我们知道，排它锁有互斥的特性。一个事务持有锁的时候，会阻止其他的事务获取锁，这个时候会造成阻塞等待，那么假如事务一直等待下去，就会一直占用CPU资源，所以，锁等待会有一个超时时间，在InnoDB引擎中，可以通过参数：innodb_lock_wait_timeout查询：

SHOW VARIABLES LIKE 'innodb_lock_wait_timeout';复制代码

默认超时时间是50s，超时后会自动释放锁回滚事务。但是我们想一下，假如事务A在等待事务B释放锁，而事务B又在等待事务A释放锁，这时候就会产生一个等待环路了，而这种情况是无论等待多久都不可能会获取锁成功的，所以是没有必要去等50s的，这种形成等待环路的现象又叫做死锁。

死锁(Dead Lock)

什么是死锁

死锁是指的两个或者两个以上的事务在执行过程中，因为争夺锁资源而造成的一种互相等待的现象。

事务A	事务B
BEGIN;
SELECT * FROM test WHERE id=10 FOR UPDATE;
	BEGIN;
	SELECT * FROM test WHERE id=20 FOR UPDATE;
SELECT * FROM test WHERE id=20 FOR UPDATE;
	SELECT * FROM test WHERE id=10 FOR UPDATE;
	ERROR 1213 (40001): Deadlock found when trying to get lock; try restarting transaction
查询出结果

我們可以看到，發生死鎖之後就會立刻回滾，而不會漫無目的的去等待50s之後超時再回滾事務，那麼MySQL是如何知道產生了死鎖的，是如何偵測死鎖的發生呢？

死鎖的偵測

目前資料庫大部分都採用wait-for graph（等待圖）的方式來進行死鎖偵測，InnoDB引擎也是採用這種方式來偵測死鎖。資料庫中會記錄兩種資訊：

• 鎖定的資訊鍊錶
• 交易的等待鍊錶
  wait-for graph 演算法會根據這兩個資訊建立一張圖，當圖中存在迴路，則證明存在死鎖：
  如下圖中，t1和t2之間存在迴路，這就證明t1和t2事務之間存在死鎖
  

死鎖的避免

• 盡量將長事務拆分成多個小事務
• #查詢時避免沒有where條件語句查詢，並儘可能使用索引查詢
• 可以的話盡量使用等值查詢

鎖定資訊查詢

InnoDB在information_schema庫下提供了3張表供我們查詢並排查事務和鎖定相關問題。

INNODB_TRX

記錄了目前在InnoDB中執行的每個事務的信息，包括事務是否在等待鎖定、事務何時啟動以及事務正在執行的SQL語句(如果有的話)。

trx_unique_checks是否為目前事務開啟或關閉唯一約束:0-否1-是trx_foreign_key_checks是否為目前事務開啟或關閉外鍵約束:0-否1-是trx_last_foreign_key_error最後一個外鍵錯誤訊息，沒有則為空trx_adaptive_hash_latched自適應雜湊索引是否被目前交易鎖定。在分區自適應哈希索引搜尋系統時，單一事務不會鎖定整個自適應哈希索引。自適應雜湊索引分區由innodb_adaptive_hash_index_parts控制，預設為8。 trx_adaptive_hash_timeout是立即放棄自適應雜湊索引的搜尋latch，還是在來自MySQL的呼叫中保留它。當沒有自適應雜湊索引爭用時，這個值將保持為零，並且語句會保留latch直到它們完成。在爭用期間，它的計數減少到零，並且語句在每一行查找之後立即釋放鎖存。當自適應雜湊索引搜尋系統被分區時(由innodb_adaptive_hash_index_parts控制)，該值保持為0。 trx_is_read_only目前交易是否只讀:0-否1-是trx_autocommit_non_locking#值為1表示這是一條不包含for update和lock in share model的語句，而且是在開啟autocommit情況下執行的有且僅有這一語句，當這列和TRX_IS_READ_ONLY都為1時，InnoDB會優化事務以減少與更改表資料事務的相關開銷。

列名	意義
#trx_id	InnoDD引擎中的事務的唯一ID
trx_state	事務狀態:RUNNING, LOCK WAIT, ROLLING BACK,COMMITTING
#trx_started	事務的開始時間
trx_requested_lock_id	等待會務的鎖定ID,如果trx_state不為LOCK WAIT時，為null
#trx_wait_started	事務等待開始的時間
#trx_weight	交易的權重，反映了一個交易修改和鎖定的行數，當發生死鎖時候，InnoDB會選擇該值最小的交易進行回滾
#trx_mysql_thread_id	MySQL中的執行緒ID，可以透過SHOW PROCESSLISTIST查詢
trx_query	交易運行的sql語句
trx_operation_state	交易的目前操作狀態，如果沒有則為NULL
trx_tables_in_use	#目前交易中執行的sql語句用到的表格數量
trx_tables_locked	已經被鎖定表的數量(因為用的是行鎖，所以雖然顯示一張表被鎖了，但是可能只是鎖定的其中一行或幾行，所以其他行還是可以被其他事務訪問)
trx_lock_structs	目前交易保留的鎖定數量
trx_lock_memory_bytes	#目前交易的索結構在記憶體中的大小
trx_rows_locked	目前交易中鎖住的大致行數，包括已經被打上刪除標記等物理存在的但是對當前事務不可見的資料
trx_rows_modified	目前交易修改或插入的行數
trx_concurrency_tickets	並發數，指的是目前交易未結束前仍可執行的並發數,可以透過系統變數innodb_concurrency_tickets設定
trx_isolation_level	#目前交易隔離等級

INNODB_LOCKS記錄了交易請求鎖定但未獲得的每個鎖的資訊和一個事務持有鎖但正在阻塞另一個事務的每個鎖的資訊。 列名意思lock_id鎖的id(雖然LOCK_ID目前包含TRX_ID，但LOCK_ID中的資料格式隨時可能更改，不要編寫解析LOCK_ID值的應用程式)lock_trx_id上一張表格的事務IDlock_mode鎖定的模式: S, X, IS, IX, GAP, AUTO_INC,UNKNOWN#lock_type鎖定的型別是表格鎖定還是行鎖定#lock_table已鎖定的表格被鎖定的索引，表鎖定則為NULL鎖定記錄的空間id，表鎖定則為NULL交易鎖定頁的數量，表鎖定則為NULL

	#lock_index
	#lock_space
	lock_page

######lock_rec###### #交易鎖定行的數量，表鎖定則為NULL############lock_data#######交易鎖定的主鍵值，表鎖定則為NULL####### #####

INNODB_LOCK_WAITS

記錄了鎖定等待的資訊。每個被阻塞的InnoDB事務包含一個或多個行，表示它所要求的鎖以及正在阻塞該請求的任何鎖。

列名	意思
lock_id	鎖的id(雖然LOCK_ID目前包含TRX_ID，但LOCK_ID中的資料格式隨時可能更改，不要編寫解析LOCK_ID值的應用程式)
requesting_trx_id	#申請鎖定資源的事務ID
requested_lock_id	申請的鎖定的ID
blocking_trx_id	#阻塞的交易ID
blocking_lock_id	阻塞的鎖的ID

更多相關免費學習推薦：mysql教學(影片)

#

以上是認識什麼是鎖，MySQL中鎖如何解決幻讀問題的詳細內容。更多資訊請關注PHP中文網其他相關文章！