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規範中定義了四種隔離級別：
越往下，隔離級別越高，問題越少，同時併發度也越低。隔離級別和併發度成反比的。
髒讀：事務A讀取了事務B未提交的資料
不可重複讀：對於一條記錄，事務A兩次讀取的資料變了
幻讀：事務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： 意向共享鎖
IX： 意向排他鎖
事務在請求某一行的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，舉了幾個例子，可能很多都是上線之後才發現的，最好能在開發階段就避免死鎖。