Mysql Innodb 中的鎖

本文主要涉及以下三個部分：

1. 為什麼要加鎖

2. 鎖的分類

3. 常見語句的加鎖分析

4. 如何分析死鎖

5. 如何預防死鎖

先列出我本地的運行環境

資料庫版本是5.7，隔離級別是Repeatable-Read(可重複讀)，不同的資料庫版本和隔離級別對語句的執行結果影響很大。討論鎖的時候不指明版本和隔離級別，都是耍流氓。

一、為什麼要加鎖

資料庫是一個多用戶使用的共享資源。當多個用戶並發地存取數據時，在資料庫中就會產生多個事務同時存取同一數據的情況。若對並發操作不加控制就可能會讀取和存儲不正確的數據，破壞資料庫的一致性。

鎖是用於管理對公共資源的並發控制。也就是說並發的情況下，會出現資源競爭，所以需要加鎖。

舉個例子，轉賬操作。簡單來說，張三給李四轉賬x元，可以分為三步：

?1，先查詢張三的賬戶餘額y是否大於x

2，張三的餘額 y = y - x元

3，李四的餘額 x = z + x元

假設張三賬戶餘額有1000元，李四餘額也有1000元，如果不加鎖的話，同時有兩個請求，A要求轉500元，B要求轉600元，第一步查詢餘額都是足夠的，第2步和第3步也能執行成功，但是最終結果卻是錯誤，第二個請求可能會覆蓋掉第一個請求。

這種問題叫做 丟失更新： 多個事務操作同一行，後面的更新覆蓋了前面的更新值。需要在應用級別加鎖來避免。

資料庫有ACID原則，其中I是隔離性，標準SQL規範中定義了四種隔離級別：

越往下，隔離級別越高，問題越少，同時並發度也越低。隔離級別和並發度成反比的。

1. 臟讀：事務A讀取了事務B未提交的數據
2. 不可重複讀：對於一條記錄，事務A兩次讀取的數據變了
3. 幻讀：事務A按照相同的查詢條件，讀取到了新增的數據

MySQL中的隔離級別如下：

和標準SQL規範相比，MySQL中可重複讀解決了幻讀，實現了串列化隔離級別的功能，同時沒有嚴重影響並發。是通過加鎖、阻止插入新數據，來解決幻讀的。

二、鎖的分類

我們聽說過讀鎖、寫鎖、共享鎖、互斥鎖、行鎖等等各種名詞，根據自己的理解，簡單對這些鎖進行了分類。

加鎖機制：

1、樂觀鎖：先修改，保存時判斷是夠被更新過，應用級別

2、悲觀鎖：先獲取鎖，再操作修改，資料庫級別

鎖粒度：

表級鎖：開銷小，加鎖快，粒度大，鎖衝突概率大，並發度低，適用於讀多寫少的情況。

頁級鎖：BDB存儲引擎

行級鎖：Innodb存儲引擎，默認選項

兼容性：

S鎖，也叫做讀鎖、共享鎖，對應於我們常用的 select * from users where id =1 lock in share mode

?X鎖，也叫做寫鎖、排它鎖、獨佔鎖、互斥鎖，對應對於select * from users where id =1 for update

下面這個表格是鎖衝突矩陣，可以看到只有讀鎖和讀鎖之間兼容的，寫鎖和讀鎖、寫鎖都是衝突的。

衝突的時候會阻塞當前會話，直到拿到鎖或者超時

這裡要提到的一點是，S鎖 和 X鎖是可以是表鎖，也可以是行鎖

索引組織表

先理解下索引組織表。

Innodb中的索引數據結構是 B+ 樹，數據是有序排列的，從根節點到葉子節點一層層找到對應的數據。普通索引，也叫做輔助索引，葉子節點存放的是主鍵值。主鍵上的索引叫做聚集索引，表裡的每一條記錄都存放在主鍵的葉子節點上。當通過輔助索引select 查詢數據的時候，會先在輔助索引中找到對應的主鍵值，然後用主鍵值在聚集索引中找到該條記錄。舉個例子，用name=Alice來查詢的時候，會先找到對應的主鍵值是18 ，然後用18在下面的聚集索引中找到name=Alice的記錄內容是 77 和 Alice。

表中每一行的數據，是組織存放在聚集索引中的，所以叫做索引組織表。

了解索引數據結構的目的是為了說明，行鎖是加在索引上的。

1.select * from user where id=10 for update

一條簡單的SQL。在user表中查找id為10的記錄，並用for update加X鎖。

這裡User表中，有3個欄位， 主鍵id 和 另外一個欄位name。下面的表格是B+樹索引的簡化表達。第一行id是索引的節點，第二行和第三行是這行記錄，包含了姓名和性別。

如圖所示，通過鎖住聚集索引中的節點來鎖住這條記錄。

聚集索引上的鎖，比較好理解，鎖住id=10的索引，即鎖住了這條記錄。

2. select * from user where name=『b』 for update

查詢user表中name為d的記錄，並用for update加X鎖

這裡的name上加了唯一索引，唯一索引本質上是輔助索引，加了唯一約束。所以會先在輔助索引上找到name為d的索引記錄，在輔助索引中加鎖，然後查找聚集索引，鎖住對應索引記錄。

為什麼聚簇索引上的記錄也要加鎖？試想一下，如果有並發的另外一個SQL，是直接通過主鍵索引id=30來更新，會先在聚集索引中請求加鎖。如果只在輔助索引中加鎖的話，兩個並發SQL之間是互相感知不到的。

3. select * from user where name=『b』 for update

查詢user表中name為b的記錄，並用for update加X鎖。這裡name上加了普通的索引，不是唯一索引。普通索引的值是可以重複的。會先在輔助索引中找到name為b的兩條記錄，加X鎖，然後得到主鍵值7和30，到聚集索引中加X鎖。

事情並沒有那麼簡單，如果這時有另一個事務，插入了name=b,id=40的記錄，卻發現是可以插入的。

位置在途中紅色線條標註的間隙內，這樣就會出現幻讀，兩次查詢得到的結果是不一致的，第一次查到兩條數據，插入之後得到三條數據。

為了防止這種情況，出現了另一種鎖，gap lcok 間隙鎖。鎖住的是索引的間隙。

即圖中，紅色線條標識的空隙。因為新插入name=b的記錄，可能出現在這三個間隙內。

這張圖裡出現了三種鎖

記錄鎖：單行記錄上的鎖

間隙鎖：鎖定記錄之間的範圍，但不包含記錄本身。

Next Key Lock: 記錄鎖+ 間隙鎖，鎖定一個範圍，包含記錄本身。

4. 意向鎖( Intention Locks )

InnoDB為了支持多粒度(表鎖與行鎖)的鎖並存，引入意向鎖。意向鎖是表級鎖，

IS: 意向共享鎖

事務在請求某一行的S鎖和X鎖前，需要先獲得對應表的IS、IX鎖。

意向鎖產生的主要目的是為了處理行鎖和表鎖之間的衝突，用於表明「某個事務正在某一行上持有了鎖，或者準備去持有鎖」。比如，表中的某一行上加了X鎖，就不能對這張表加X鎖。

如果不在表上加意向鎖，對錶加鎖的時候，都要去檢查表中的某一行上是否加有行鎖，多麻煩。

5. 插入意向鎖（Insert Intention Lock）

Gap Lock中存在一種插入意向鎖，在insert操作時產生。

有兩個作用：

• 和next-key互斥，阻塞next-key 鎖，防止插入數據，這樣就不會幻讀。
• 插入意向鎖互相是兼容的，允許相同間隙、不同數據的並發插入

三、常見語句的加鎖分析

後面會有多個SQL語句，先說明一下表結構

CREATE TABLE `user` ( `id` int(11) unsigned NOT NULL AUTO_INCREMENT, `id_no` varchar(255) DEFAULT NULL COMMENT 身份證號, `name` varchar(255) DEFAULT NULL COMMENT 姓名, `mobile` varchar(255) DEFAULT NULL COMMENT 手機號, `age` int(11) DEFAULT NULL COMMENT 年齡, `address` varchar(255) DEFAULT NULL COMMENT 地址, PRIMARY KEY (`id`), UNIQUE KEY `uniq_id_no` (`id_no`), KEY `idx_name` (`name`)) ENGINE=InnoDB AUTO_INCREMENT=10002 DEFAULT CHARSET=utf8 COMMENT=用戶表;

這裡有一個user表，5個欄位，其中id是主鍵，id_no是身份證號，加了唯一索引，name是用戶姓名，可以重複的，加了普通索引，手機號、年齡、地址都沒有索引。

1. 普通select

select * from user where id =1;begin;select * from user where id =1;commit:

普通的select 語句是不加鎖的。select包裹在事務中，同樣也是不加鎖的。where後面的條件不管多少，普通的select是不加鎖的。

2. 顯式加鎖

select * from user where id =1 lock in share mode;select * from user where id =1 for update;

顯式指出要加什麼樣的鎖。上面一個加的是共享鎖，下面的是互斥鎖。

這裡需要強調的一點，需要明確在事務中是用這些鎖，不在事務中是沒有意義的。

3. 隱式加鎖

update user set address 北京 where id=1;delete from user where id=1;

update和delete也會對查詢出的記錄加X鎖，隱式加互斥鎖。加鎖類型和for update 類似

後面只按照顯式加鎖的select for update 舉例子，更新和刪除的加鎖方式是一樣的。

4. 按索引類型

elect * from user where id =1 for update;select * from user where id_no =a22 for update;select * from user where name =王二 for update;select * from user where address =杭州 for update;

四條SQL，區別在於where條件的過濾列，分別是主鍵、唯一索引、普通索引、無索引。

主鍵：之前提到過索引組織表，這裡會在聚集索引上對查詢出的記錄，加X鎖

唯一索引：會在輔助索引上，把在對應的id_no=a22的索引加X鎖，因為是唯一的，所以不是next-key鎖。然後在主鍵上，也會在這條記錄上加X鎖。

普通索引：因為不是唯一的，會在輔助索引上，把對應的id_no=a22的索引加next-key鎖。然後在主鍵加X鎖。

無索引：首先，是不推薦這種寫法，沒有索引的話，因為會全表掃描，數據量大的話查詢會很慢。這裡討論的是，這種情況下，會加什麼鎖？ 答案： 首先，聚簇索引上的所有記錄，都被加上了X鎖。其次，聚簇索引每條記錄間的間隙(GAP)，也同時被加上了GAP鎖。在這種情況下，這個表上，除了不加鎖的快照度，其他任何加鎖的並發SQL，均不能執行，不能更新，不能刪除，不能插入，全表被鎖死。這是一個很恐怖的事情，請注意。

5. 記錄不存在的情況

前面幾個例子中，都是可以查到結果的。如果對應記錄不存在會怎樣？答案是鎖住間隙，不允許插入。mysql要保證沒有其他人可以插入，所以鎖住間隙。

6. 普通 insert 語句

在插入之前，會先在插入記錄所在的間隙加上一個插入意向鎖。

insert會對插入成功的行加上排它鎖，這個排它鎖是個記錄鎖，而非next-key鎖（當然更不是gap鎖了），不會阻止其他並發的事務往這條記錄之前插入 。

7. 先查詢後插入

類似於這樣的insert

insert into target_table select * from source_table ...create target_table select * from source_table ...

將select查詢的結果集，插入到另一張表中，或者使用結果集，創建一個新表。

和之前簡單插入的情況類似，已插入成功的數據加X鎖，間隙加上一個插入意向鎖。

對於select的源表中的記錄，會加共享的 next-key 鎖。這是為了防止主從同步出問題。

舉個例子：

session1 先開啟事務，然後查詢user2表的結果集，插入到user表中，session2開啟事務，在插入user2中插入數據，所插入的數據剛好是session1能查詢到的數據，如果不加鎖的話，session2可以插入成功，然後session2提交事務，接著session1提交數據。這樣看起來是沒問題的，但是session2先提交的，所以bin log中會這樣記錄，先在user2表中插入數據，然後在user中插入數據，這樣的bin log在從庫執行的時候，就會出問題。

主庫： user2插入一條數據，user 插入一條數據

從庫： user2插入一條數據，user 插入兩條數據

user表會比主庫多一條數據。所以需要鎖住select查詢表中加next-key鎖，不允許user2表中新增數據。

四、分析當前鎖的情況

先說一下死鎖的定義，死鎖是指兩個或兩個以上的事務在執行過程中，因爭奪資源而造成的一種互相等待的現象。這個定義適用於資料庫，有幾個重點，兩個或兩個以上的事務，一個事務是不會出現死鎖的。爭奪的資源一般都是表或者記錄。

出現死鎖了會怎樣，正常情況下，mysql會檢查出死鎖，並回滾某一個事務，讓另一個事務正常運行。

Mysql 會回滾副作用小的事務，判定的標準是執行的時間以及影響的範圍。

1.如何知道系統有沒有發生過死鎖，如何去查看發生過的鎖

show status like 『innodb_row_lock%; 從系統啟動到現在的數據

Innodb_row_lock_current_waits：當前正在等待鎖的數量；

Innodb_row_lock_time ：鎖定的總時間長度，單位ms；

Innodb_row_lock_time_avg ：每次等待所花平均時間；

Innodb_row_lock_time_max：從系統啟動到現在等待最長的一次所花的時間；

Innodb_row_lock_waits ：從系統啟動到現在總共等待的次數。

平均時間和鎖等待次數比較大的話，說明可能會存在鎖爭用情況

2. show engine innodb status

展示innodb存儲引擎的運行狀態

通過這個命令顯示的內容比較多，其中有一項lasted detected deadlock 顯示最近發生的死鎖。

圖中紅色線條標註的是執行的SQL，以及加了什麼鎖，可以看出是在這行記錄上加了X鎖，沒有gap鎖。

3. 錯誤日誌中查看歷史發生過的死鎖

set global innodb_print_all_deadlocks=1;

上一個命令，只能看到最近發生的鎖，如果我想看歷史發生的鎖怎麼辦？ 執行這一句，更改innodb 的一個配置，innodb_print_all_deadlocks，列印所有的死鎖。會將死鎖的信息輸出到mysql的錯誤日誌中，默認是不輸出，格式和show engine innodb status 是差不多的。

4. information_schema.innodb_locks

information_schema 資料庫是mysql自帶的，保存著關於MySQL伺服器所維護的所有其他資料庫的信息。其中innodb_locks表，記錄了事務請求但是還沒獲得的鎖，即等待獲得的鎖。

lock_id：鎖的id，由鎖住的空間id編號、頁編號、行編號組成

lock_trx_id：鎖的事務id。

lock_mode：鎖的模式。S[,GAP], X[,GAP], IS[,GAP], IX[,GAP]

lock_type：鎖的類型，表鎖還是行鎖

lock_table：要加鎖的表。

lock_index：鎖住的索引。

lock_space：innodb存儲引擎表空間的id號碼

lock_page：被鎖住的頁的數量，如果是表鎖，則為null值。

lock_rec：被鎖住的行的數量，如果表鎖，則為null值。

lock_data：被鎖住的行的主鍵值，如果表鎖，則為null值。

5. information_schema.innodb_lock_waits

查看等待中的鎖

requesting_trx_id：申請鎖資源的事務id。

requested_lock_id：申請的鎖的id。

blocking_trx_id：阻塞的事務id，當前擁有鎖的事務ID。

blocking_lock_id：阻塞的鎖的id，當前擁有鎖的鎖ID

6. information_schema.innodb_trx

查看已開啟的事務

trx_id：innodb存儲引擎內部事務唯一的事務id。

trx_state：當前事務的狀態。

trx_started：事務開始的時間。

trx_requested_lock_id：等待事務的鎖id，如trx_state的狀態為LOCK WAIT，那麼該值代表當前事務之前佔用鎖資源的id，如果trx_state不是LOCK WAIT的話，這個值為null。

trx_wait_started：事務等待開始的時間。

trx_weight：事務的權重，反映了一個事務修改和鎖住的行數。在innodb的存儲引擎中，當發生死鎖需要回滾時，innodb存儲引擎會選擇該值最小的事務進行回滾。

trx_mysql_thread_id：正在運行的mysql中的線程id，show full processlist顯示的記錄中的thread_id。

trx_query：事務運行的sql語句

五、預防死鎖

1. 以相同的順序更新不同的表

這樣執行的話，會出現鎖等待，但不容易出現死鎖。

假設有這麼兩個介面，增加老師和學生的幸運值、減少老師和學生的幸運值，這個需求是我造出來的，先別管需求是不是合理。

現在有兩個請求，一個增加幸運值，一個降低幸運值，如果更新順序不同的話，就是這樣，第一個事務先給老師加幸運值，第二個介面給學生減幸運值，然後第一個事務給學生加幸運值，因為鎖已經被第二個事務持有了，所以第一個事務等待。然後第二個事務給老師幸運值，這時就互相等待鎖，出現了死鎖。

2. 預先對數據進行排序

比如一個介面批量操作數據，如果亂序的話，並發的情況下，也是有可能出現死鎖的。給學生批量加分的介面，按照表格中的執行順序的話，第一個事務，持有A的鎖，請求B的鎖，第二個事務持有B的鎖，請求A的鎖，出現死鎖。

3. 直接申請足夠級別的鎖，而非先共享鎖，再申請排他鎖。

比如這種情況，兩個事務，先申請共享鎖，共享鎖是兼容的，然後申請互斥鎖的時候，需要互相等待，就出現了死鎖。

4. 事務的粒度及時間盡量保持小，這樣鎖衝突的概率就小了，也就不容易出現死鎖。不建議在資料庫的事務中執行API調用。

5. 正確加索引。沒有索引會引起全表掃描，類似於鎖表。

六：總結：

1，正確的加索引，盡量先查詢，然後使用主鍵去加鎖，等於操作來加鎖，而盡量避免輔助索引，或者不是範圍比較來加鎖。

2，出現了鎖的問題，根據資料庫已有的信息，分析死鎖。

3，舉了幾個例子，可能很多都是上線之後才發現的，最好能在開發階段就避免死鎖。