一错沽、屬性鎖：Shared and Exclusive Locks
1.1 簡介
shared locks 是共享鎖楼肪，簡稱 S 鎖寿桨，exclusive locks 是排它鎖，簡稱 X 鎖琅坡，它們既可以是表級鎖悉患，也可以是行級鎖，在 MySQL 的 InnoDB 引擎中是行級鎖榆俺，可以加在一行或者多行上售躁，那么何時在一行上加鎖坞淮，何時在多行上加鎖，這需要根據(jù)索引情況而定
shared locks 允許持有某行 S 鎖的事務(wù)讀扰憬荨（select）該行碾盐，exclusive locks 允許持有某行 X 鎖的事務(wù)更新（update）和刪除（delete）該行
InnoDB 支持通過特定的語句進行顯式加鎖：
- 顯式加 X 鎖：
select ... for update - 顯式加 S 鎖：
select ... lock in share mode
1.2 S 鎖和 X 鎖的兼容性
共享鎖和排它鎖的兼容性列表如下表，該表表示：
- 如果事務(wù) T1 獲得了行 r 的 S 鎖揩局，另一個事務(wù) T2 可以獲取行 r 的 S 鎖毫玖，但是不能獲取行 r 的 X 鎖。即 S 鎖可以被多個事務(wù)共享凌盯，所以稱為共享鎖
- 如果事務(wù) T1 獲得了行 r 的 X 鎖付枫，另一個事務(wù) T2 既不能獲取行 r 的 S 鎖，也不能獲取行 r 的 X 鎖驰怎，必須等待 T1 釋放 X 鎖阐滩，故稱為排他鎖
X |
S |
|
|---|---|---|
X |
不兼容 | 不兼容 |
S |
不兼容 | 兼容 |
1.3 S 鎖和 X 鎖的兼容性示例
1.3.1 創(chuàng)建測試表
-- ----------------------------
-- Table structure for test
-- ----------------------------
DROP TABLE IF EXISTS `test`;
CREATE TABLE `test` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`xid` int(11) NULL DEFAULT NULL,
`name` varchar(255) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (`id`) USING BTREE,
INDEX `idx_xid`(`xid`) USING BTREE
) ENGINE = InnoDB AUTO_INCREMENT = 6 CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;
-- ----------------------------
-- Records of test
-- ----------------------------
INSERT INTO `test`(xid, name) VALUES (1, '1');
INSERT INTO `test`(xid, name) VALUES (5, '5');
INSERT INTO `test`(xid, name) VALUES (9, '9');
1.3.2 S 鎖兼容性測試
分別打開兩個會話（會話 A 和會話 B），在會話 A 中開啟一個事務(wù)并執(zhí)行：
select * from test where xid = 1 lock in share mode;
在會話 B 中開啟一個事務(wù)并執(zhí)行：
select * from test where xid = 1 lock in share mode;
select * from test where xid = 1 for update;
效果如下：
1.3.3 X 鎖兼容性測試
分別打開兩個會話（會話 A 和會話 B）县忌，在會話 A 中開啟一個事務(wù)并執(zhí)行：
select * from test where xid = 1 for update;
在會話 B 中開啟一個事務(wù)并執(zhí)行：
select * from test where xid = 1 lock in share mode;
select * from test where xid = 1 for update;
效果如下：
二掂榔、狀態(tài)鎖：Intention Locks
Intention Locks 稱為意向鎖，它是表級鎖症杏，顧名思義装获，它是用來鎖定表的，與行級鎖相對應(yīng)
如果事務(wù) T1 獲取了一個表的 intention exclusive 鎖（簡稱 IX 鎖）厉颤，相當(dāng)于表級別的排它鎖穴豫，那么事務(wù) T2 就不能再獲取表上的 S 和 X 鎖了；
如果事務(wù) T1 獲取了一個表的 intention shared 鎖（簡稱 IS 鎖）逼友，那么事務(wù) T2 可以獲取表的 S 鎖精肃，但不能獲取表的 X 鎖，它與共享鎖和排它鎖的關(guān)系如下：
（1）一個事務(wù)獲取一張表中某行的 S 鎖之前帜乞，必須獲取表的 IS 鎖或者更強的鎖（比如 IX）司抱；
（2）一個事務(wù)獲取一張表中某行的 X 鎖之前，必須獲取表的 IX 鎖黎烈；
表級鎖的兼容性如下：
X |
IX |
S |
IS |
|
|---|---|---|---|---|
X |
Conflict | Conflict | Conflict | Conflict |
IX |
Conflict | Compatible | Conflict | Compatible |
S |
Conflict | Conflict | Compatible | Compatible |
IS |
Conflict | Compatible | Compatible | Compatible |
當(dāng)前事務(wù) T1 想要修改某張表的一些行习柠，那么首先要獲取該表的 IX 鎖，然后在要修改的行上加上 X 鎖怨喘，另一個事務(wù) T2 也準(zhǔn)備要修改該表的一些行津畸，因為表中除了被當(dāng)前事務(wù) T1 加鎖的行，其他行是可以修改的必怜，所以 T2 可以獲取該表的 IX 鎖，然后在其他行添加 X 鎖后频，但是如果要修改當(dāng)前事務(wù) T1 加鎖的行就需要等待了
三梳庆、算法鎖
InnoDB 有三種鎖行算法：
- Record Lock：單個行記錄上的鎖
- Gap Lock：間隙鎖暖途，鎖定一個范圍，但不包括記錄本身膏执。GAP 鎖的目的驻售，是為了防止同一事務(wù)的兩次當(dāng)前讀，出現(xiàn)幻讀的情況
- Next-Key Lock：Record Lock 和 Gap Lock 的結(jié)合更米。鎖定一個范圍欺栗，并且鎖定記錄本身。對于行的查詢征峦，都是采用該方法迟几，主要目的是解決幻讀的問題
3.1 for update 簡介
for update 可以顯式地為表中滿足條件的行加 X 鎖。當(dāng)一個事務(wù)的操作未完成時候栏笆，其他事務(wù)可以讀取該行类腮，但不能更新或刪除該行
使用場景：高并發(fā)并且對于數(shù)據(jù)的準(zhǔn)確性很有要求的場景。例如涉及到金錢蛉加、庫存等蚜枢。一般這些操作都是很長一串并且是開啟事務(wù)的。如果庫存剛開始讀的時候是 1针饥，而立馬另一個進程進行了 update 將庫存更新為 0 了厂抽，而事務(wù)還沒有結(jié)束，會將錯的數(shù)據(jù)一直執(zhí)行下去丁眼，就會有問題修肠。所以需要 for upate 進行數(shù)據(jù)加鎖防止高并發(fā)時候數(shù)據(jù)出錯
InnoDB 行鎖是通過給索引項加鎖來實現(xiàn)的，如果沒有索引户盯，InnoDB 將通過隱藏的聚簇索引來對記錄加鎖
3.2 for update 中算法鎖的使用
場景一：明確指定索引嵌施，并且有此記錄，加 Next-Key Lock
select * from test where xid = 5 for update;
插入操作
此時鎖住的范圍除了 ② 之外莽鸭，還會鎖住下一個范圍吗伤，即 ③，這就是所謂的 Next-Key Lock硫眨。因此不能在另一個會話中插入 xid 在 1~9 范圍內(nèi)的數(shù)據(jù)足淆。xid = 1 的數(shù)據(jù)不能插入，但 xid = 9 的數(shù)據(jù)可以插入礁阁，這可能與 B-Tree 索引的順序插入有關(guān)巧号。我們可以看下圖描述的聚簇索引（自增 id）和 xid 的索引結(jié)構(gòu)圖：
個人理解（僅供參考）：xid = 1 的數(shù)據(jù)不能插入，因為主鍵索引 id 是自增的姥闭，因此在 id=2 這條記錄之前丹鸿，是不允許插入一條 xid=5 的記錄，這樣就破壞了主鍵索引 id 的有序性棚品；xid = 9 的數(shù)據(jù)可以插入可能是因為 MySQL允許在 (id = 3, xid = 9) 的后面插入一條 (id = 4, xid = 9) 的數(shù)據(jù)靠欢，并不破壞主鍵索引的有序性
INSERT INTO `test`(xid, name) VALUES (1, '1');
INSERT INTO `test`(xid, name) VALUES (4, '4');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (9, '9');
INSERT INTO `test`(xid, name) VALUES (15, '15');
讀取廊敌、更新和刪除操作
事務(wù) A 執(zhí)行 select * from test where xid = 5 for update; 將 xid = 5 這一行鎖定后，事務(wù) B 可以執(zhí)行 select门怪，但不能執(zhí)行 update 和 delete
select * from test where xid = 5;
update test set name = '5-1' where xid = 5;
delete from test where xid = 5;
事務(wù) B 對其他行的操作（select骡澈、update 和 delete）則不受影響
場景二：使用范圍條件而不是相等條件檢索數(shù)據(jù)時，InnoDB 會給滿足條件的索引行加鎖掷空，對于索引值在條件范圍但不存在的行記錄加 Gap Lock
select * from test where xid > 7 for update;
插入操作
此時針對索引 xid 使用的范圍查找肋殴，會鎖住 xid = 9 的行以及 ③、 ④ 坦弟。因此除了可以插入 xid = 3 的數(shù)據(jù)外护锤，其余插入語句均不能執(zhí)行
INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (8, '8');
INSERT INTO `test`(xid, name) VALUES (15, '15');
讀取、更新和刪除操作
事務(wù) A 執(zhí)行 select * from test where xid > 7 for update; 將 xid = 9 這一行鎖定后减拭，事務(wù) B 可以執(zhí)行 select蔽豺，但不能執(zhí)行 update 和 delete
select * from test where xid = 9;
update test set name = '9-1' where xid = 9;
delete from test where xid = 9;
事務(wù) B 對其他不滿足 xid > 7 的行的操作（select、update 和 delete）則不受影響
場景三：明確指定索引拧粪，若查無此記錄修陡，加 Gap Lock
select * from test where xid = 7 for update;
此時加鎖區(qū)間是 ③ ，因此除了 xid = 6 和 xid = 8 無法插入外可霎，其余均可執(zhí)行
INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (6, '6');
INSERT INTO `test`(xid, name) VALUES (8, '8');
INSERT INTO `test`(xid, name) VALUES (15, '15');
讀取魄鸦、更新和刪除操作
Gap Lock 主要是為了防止其他事務(wù)在鎖定范圍內(nèi)插入數(shù)據(jù)，不影響其他事務(wù)操作其他行數(shù)據(jù)
場景四：當(dāng)查詢的索引含有唯一屬性（主鍵或唯一索引）的時候癣朗，Next-Key Lock 會進行優(yōu)化拾因，將其降級為 Record Lock，即僅鎖住索引本身旷余，不是范圍
將 xid 改為 unique index：
ALTER TABLE `db_zll`.`test`
DROP INDEX `idx_xid`,
ADD UNIQUE INDEX `idx_xid`(`xid`) USING BTREE;
select * from test where xid = 5 for update;
事務(wù) A 執(zhí)行 select * from test where xid = 5 for update; 只鎖住 xid = 5 這一行绢记，因此事務(wù) B 對 xid = 5 的行只能 select，事務(wù) B 對其他行的操作不受影響
INSERT INTO `test`(xid, name) VALUES (3, '3');
INSERT INTO `test`(xid, name) VALUES (5, '5');
INSERT INTO `test`(xid, name) VALUES (7, '7');
行鎖失效場景：
- 未指定主鍵/索引正卧，并且有此記錄蠢熄，表級鎖
- 無主鍵/索引，表級鎖
- 主鍵/索引不明確炉旷，表級鎖签孔，例如
where xid like、where xid <>等操作
3.3 for update 超時回滾
超時時間的參數(shù)：innodb_lock_wait_timeout 窘行，默認是50秒
超時是否回滾參數(shù)：innodb_rollback_on_timeout 默認是OFF
默認情況下饥追，InnoDB 存儲引擎不會回滾超時引發(fā)的異常，除死鎖外罐盔。當(dāng)參數(shù) innodb_rollback_on_timeout 設(shè)置成 ON 時但绕，則可以回滾
3.4 for update 注意點
for update 僅適用于 InnoDB，并且必須開啟事務(wù)翘骂，在 begin 與 commit 之間才生效
-
當(dāng)開啟一個事務(wù)進行 for update 的時候壁熄，另一個事務(wù)也有 for update 的時候會一直等待帚豪，直到第一個事務(wù)結(jié)束嗎碳竟？
答：會的草丧。除非第一個事務(wù) commit 或者 rollback 或者斷開連接，第二個事務(wù)會立馬拿到鎖進行后面操作莹桅。不過也可以設(shè)置鎖等待超時參數(shù)innodb_lock_wait_timeout 來解決
-
如果沒查到記錄會加鎖嗎昌执？
答：會的。有主鍵/索引產(chǎn)生間隙鎖诈泼，無主鍵/索引產(chǎn)生表鎖表級鎖
-
for update 和 for update nowait 區(qū)別（前者阻塞其他事務(wù)，后者拒絕其他事務(wù)）
for update 鎖住表或者鎖住行，只允許當(dāng)前事務(wù)進行操作（讀寫）键兜，其他事務(wù)被阻塞巧还，直到當(dāng)前事務(wù)提交或者回滾，被阻塞的事務(wù)自動執(zhí)行 for update nowait 鎖住表或者鎖住行瓮孙，只允許當(dāng)前事務(wù)進行操作（讀寫）唐断，其他事務(wù)被拒絕，事務(wù)占據(jù)的 statement 連接也會被斷開
行鎖分析
show status like 'innodb_row_lock%';
mysql> show status like 'innodb_row_lock%';
+-------------------------------+-------+
| Variable_name | Value |
+-------------------------------+-------+
| Innodb_row_lock_current_waits | 0 |
| Innodb_row_lock_time | 0 |
| Innodb_row_lock_time_avg | 0 |
| Innodb_row_lock_time_max | 0 |
| Innodb_row_lock_waits | 0 |
+-------------------------------+-------+
5 rows in set (0.06 sec)
- 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ù)
