InnoDB的行鎖按鎖的互斥程度來劃分
Shared Lock 共享鎖（s）：又稱讀鎖。
- 允許一個事務(wù)去讀一行，阻止其他事務(wù)獲得相同數(shù)據(jù)集的排他鎖。–讀鎖
- 顯式加鎖
select * from test lock in share mode;
Exclusive Lock 排他鎖（Ｘ）：又稱寫鎖。
允許獲取排他鎖的事務(wù)更新數(shù)據(jù)既穆，阻止其他事務(wù)取得相同的數(shù)據(jù)集共享讀鎖和排他寫鎖赎懦。–寫鎖
對于UPDATE、DELETE和INSERT語句幻工，InnoDB會自動給涉及及數(shù)據(jù)集加排他鎖（Ｘ）励两；對于普通SELECT語句，InnoDB不會加任務(wù)鎖囊颅；
- 顯式加鎖
select * from test for update;
無鎖当悔、S鎖、X鎖的互斥情況試驗
| 鎖類型 | lock in share mode | for update |
|---|---|---|
| select | 兼容 | 兼容 |
| lock in share mode | 兼容 | 互斥 |
| for update | 互斥 | 互斥 |
為了節(jié)省時間踢代，x鎖我統(tǒng)一使用select … for update的方式
S鎖的互斥情況
X鎖的互斥情況
S鎖和X鎖的注意事項
如果事務(wù)A使用select ... lock in share mode 的話盲憎，之后事務(wù)B也使用select ... lock in share mode。這樣事務(wù)B持鎖胳挎，然后在事務(wù)A里執(zhí)行update語句會造成死鎖饼疙；對于鎖定記錄后需要進(jìn)行更新操作的應(yīng)用，應(yīng)該使用select ... for update 的方式獲得排它鎖慕爬。
InnoDB行鎖實現(xiàn)方式
-
在不通過索引條件查詢時宏多，InnoDB會鎖定表中的所有記錄！
image.png -
mysql的行鎖是針對索引加鎖澡罚，不是針對記錄加鎖，所以雖然是訪問到不同的記錄肾请，但是使用相同的索引鍵（使用普通索引而非唯一索引）留搔，是會出現(xiàn)鎖沖突的
在這里id是普通的索引
image.png -
當(dāng)表有多個索引的時候，不同的事務(wù)可以使用的索引鎖定不同的行铛铁，不論是使用主鍵索引隔显、唯一索引或普通索引，InnoDB都會使用行鎖來對數(shù)據(jù)加鎖饵逐。
image.png -
在查詢中索引不一定會被用到括眠，這個時候就會進(jìn)行全表掃描，形成表鎖
image.png
InnoDB行鎖按實現(xiàn)方案可以分3種：
默認(rèn)情況下倍权，InnoDB工作在RR隔離級別下掷豺，并且以Next-Key Lock的方式對數(shù)據(jù)行進(jìn)行加鎖。
Next-Key Lock是行鎖與間隙鎖的組合薄声，這樣当船，當(dāng)InnoDB掃描索引記錄的時候，會首先對選中的索引記錄加上行鎖（Record Lock）默辨，再對索引記錄兩邊的間隙（向左掃描掃到第一個比給定參數(shù)小的值德频， 向右掃描掃描到第一個比給定參數(shù)大的值， 然后以此為界缩幸，構(gòu)建一個區(qū)間）加上間隙鎖（Gap Lock）壹置。
如果一個間隙被事務(wù)A加了鎖竞思，其它事務(wù)是不能在這個間隙插入記錄的。
Record lock:
-
記錄鎖钞护，對索引項加鎖
Gap lock:
-
間隙鎖盖喷，鎖加在不存在的空閑空間，可以是兩個索引記錄之間患亿，也可能是第一個索引記錄之前或最后一個索引之后的空間传蹈。 - 關(guān)閉間隙鎖：可以通過修改隔離級別為 READ COMMITTED 或者配置 innodb_locks_unsafe_for_binlog 參數(shù)為 ON。
mysql5.6中不能通過命令直接設(shè)置步藕，表示這個參數(shù)是只讀的惦界。只能通過修改my.cnf文件后重啟
set @@innodb_locks_unsafe_for_binlog = ON
- 間隙鎖在RR級別下開啟
- 間隙鎖會對 在
不存在的記錄上做insert操作 加鎖 -
間隙鎖驗證
image.png
image.png - 當(dāng)操作作用于
不存在的一條記錄時也會出現(xiàn)間隙鎖，驗證如下：
以update為主
image.png
以select .. for update為主
以insert為主
以delete為主
id=15的記錄并不存在
| 鎖類型 | select * from test where id = 15 for update; | delete from test where id = 15; | update test set a='fff' where id =15; | INSERT INTO test(id, a, b, c) VALUES (15, 'new !!!!', 'bbb', 'ccc'); |
|---|---|---|---|---|
| select * from test where id = 15 for update; | 兼容 | 兼容 | 兼容 | 互斥 |
| delete from test where id = 15; | 兼容 | 兼容 | 兼容 | 互斥 |
| update test set a='fff' where id =15; | 兼容 | 兼容 | 兼容 | 互斥 |
INSERT INTO test(id, a, b, c) VALUES (15, 'new !!!!', 'bbb', 'ccc'); |
互斥 | 互斥 | 互斥 | 互斥 |
- 在RC下驗證間隙鎖關(guān)閉情況
image.png
RC下間隙鎖的確是關(guān)閉的咙冗。如果事務(wù)B提交后沾歪。事務(wù)A繼續(xù)執(zhí)行一個查詢操作∥硐可以發(fā)現(xiàn)多處了一條記錄灾搏。出現(xiàn)了幻讀！
image.png - 我在這里有兩個問題很不解：
1立润、《高性能mysql》說MVCC機(jī)制在RC和RR下生效狂窑，那為什么不能杜絕RC下的幻讀？
答：因為RC下和RR下MVCC機(jī)制不同桑腮，RC下每次select都會生成readview泉哈，而RR下只在第一次select生成readview
2、RC下因為沒有間隙鎖機(jī)制而出現(xiàn)了幻讀破讨。那MVCC機(jī)制解決幻讀和間隙鎖解決的幻讀是不是一個概念丛晦？
答：RR下的MVCC能解決幻讀是：普通select快照讀；RR下間隙鎖解決的幻讀是：select ... lock in share mode提陶、select .. for update的加鎖讀 -
間隙鎖的危害
image.png
Next-key lock:
-
臨鍵鎖烫沙，該鎖就是 Record Locks 和 Gap Locks 的組合，即鎖定一個范圍并且鎖定該記錄本身 - 舉個例子隙笆，如果一個索引有 1, 3, 5 三個值锌蓄，則該索引鎖定的區(qū)間為 (-∞,1], (1,3], (3,5], (5,+ ∞)
前開后閉區(qū)間
查看行鎖情況
show status like 'InnoDB_row_lock%';
對于各個字段說明如下：
- Innodb_row_lock_current_waits:當(dāng)前正在等待鎖的數(shù)量；
- Innodb_row_lock_time:從系統(tǒng)啟動到現(xiàn)在鎖定總時間長度撑柔；
- Innodb_row_lock_time_avg：每次等待所花平均時間煤率；
- Innodb_row_lock_time_max:從系統(tǒng)啟動到現(xiàn)在等待最長的一次所花的時間長度；
- Innodb_row_lock_waits:系統(tǒng)啟動到現(xiàn)在總共等待的次數(shù)乏冀；
如果發(fā)現(xiàn)鎖爭用比較嚴(yán)重蝶糯，還可以通過設(shè)置InnoDB Monitors 來進(jìn)一步觀察發(fā)生鎖沖突的表、數(shù)據(jù)行等辆沦，并分析鎖爭用的原因昼捍。
注意识虚，這條sql無法查詢到持鎖數(shù)量和鎖類型。只有在引發(fā)阻塞后才會記錄鎖的信息妒茬。在mysql5.7中請使用show engine innodb status;來獲得這兩個信息担锤！
關(guān)于行鎖的優(yōu)化建議
- 盡可能讓所有數(shù)據(jù)檢索都通過索引來完成，避免無索引行鎖升級為表鎖
- 合理設(shè)計索引乍钻，盡量縮小鎖的范圍
- 盡可能較少檢索條件肛循，避免間隙鎖
- 盡量控制事務(wù)大小，減少鎖定資源量和時間長度
- 盡可能低級別事務(wù)隔離
