本文將以實(shí)驗(yàn)為主雇寇，演示共享鎖氢拥、排他鎖的特性，演示行鎖升級(jí)為表鎖锨侯。
為進(jìn)行實(shí)驗(yàn)嫩海，先建立account表，含有一個(gè)主鍵id
CREATE TABLE account (
id INT (11) NOT NULL AUTO_INCREMENT,
name VARCHAR(255) DEFAULT NULL,
balance INT DEFAULT 0,
PRIMARY KEY (id)
) ENGINE = INNODB DEFAULT CHARSET = utf8;
INSERT INTO account (name, balance) VALUES ('li', 10);
INSERT INTO account (name, balance) VALUES ('ha', 233);
1囚痴、共享鎖叁怪、也叫讀鎖、S鎖
讀鎖是共享的深滚，或者說(shuō)是互相不阻塞的奕谭，多個(gè)客戶在同一時(shí)刻可以同時(shí)讀取同一資源，而互不干擾成箫。
若 事務(wù)T 對(duì)數(shù)據(jù) 對(duì)象A 加上 S鎖 展箱，其他事務(wù)可以對(duì) A 加 S鎖 ，但不能加 X鎖 蹬昌，直到 T 釋放 A 上的 S鎖混驰。
S鎖保證了其他事務(wù)可以讀A，但在T釋放A上的S鎖之前不能對(duì)A做任何修改。
加讀鎖：普通的select語(yǔ)句不會(huì)加任何鎖栖榨，加讀鎖需要顯示的加上【LOCK IN SHARE MODE】昆汹。
下面通過(guò)例子展示讀鎖的特點(diǎn)。
| 步驟 | 會(huì)話 A命令 | balance值 | 會(huì)話 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | select balance from account where id = 1 LOCK IN SHARE MODE; |
10 | ||
| 3 | select balance from account where id = 1 LOCK IN SHARE MODE; | 10 | ||
| 4 | update account set balance = 666 where id = 1; | 將會(huì)被阻塞 | ||
| 5 | COMMIT; | update執(zhí)行 |
可以發(fā)現(xiàn)會(huì)話A 對(duì)id=1行加讀鎖之后婴栽，會(huì)話B任然可以對(duì)id=1行加讀鎖满粗，但是會(huì)話B繼續(xù)對(duì)id=1行執(zhí)行更新操作將會(huì)被阻塞。
如果在第5步不是提交事務(wù)愚争，而是執(zhí)行一個(gè)更新語(yǔ)句的話映皆，update account set balance = 777 where id = 1，這時(shí)候?qū)?huì)發(fā)生死鎖現(xiàn)象轰枝。
2捅彻、排他鎖、也叫寫鎖鞍陨、X鎖
寫鎖是排他的步淹，也就是說(shuō)一個(gè)寫鎖會(huì)阻塞其他的寫鎖和讀鎖，這是出于安全策略的考慮诚撵。
事務(wù)T對(duì)數(shù)據(jù)對(duì)象A加上X鎖缭裆，事務(wù)T可以讀A也可以修改A，其他事務(wù)不能再對(duì)A加任何鎖寿烟，直到T釋放A上的鎖澈驼。
X鎖保證了其他事務(wù)在T釋放A上的鎖之前不能再讀取和修改A。
1韧衣、自動(dòng)加鎖： delete / update / insert 語(yǔ)句會(huì)自動(dòng)加上排他鎖盅藻；
2、手動(dòng)加排它鎖：select * from student where id=1 FOR UPDATE畅铭；
| 步驟 | 會(huì)話 A命令 | balance值 | 會(huì)話 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | update account set balance = 0 where id = 1; |
|||
| 3 | select balance from account where id = 1; | 10 | ||
| 4 | select balance from accountwhere id = 2 LOCK IN SHARE MODE; |
不阻塞233 |
||
| 4 | select balance from account where id = 1 LOCK IN SHARE MODE; |
阻塞 |
||
| 5 | COMMIT; | 結(jié)果出來(lái)0 |
可以發(fā)現(xiàn)氏淑， update 語(yǔ)句中 where 條件為主鍵時(shí)，是對(duì)該行上鎖硕噩，其他會(huì)話想獲取該行的讀或?qū)戞i都將被阻塞假残，但是其他行不受影響。
3炉擅、為什么推薦 MySQL 的 update 語(yǔ)句中 where 條件要有主鍵索引辉懒、唯一索引？
接下來(lái)演示一下如果update 語(yǔ)句中 where 條件是沒(méi)有索引的name字段會(huì)怎么樣呢谍失。
將會(huì)發(fā)現(xiàn)眶俩，由于name字段沒(méi)有索引，所以對(duì)該行的行鎖升級(jí)為了表鎖快鱼，其他行想獲取讀鎖或?qū)戞i都將被阻塞颠印。
| 步驟 | 會(huì)話 A命令 | balance值 | 會(huì)話 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | update account set balance = 0 where name = 'li'; |
|||
| 3 | select balance from account where id = 1; | 10 | ||
| 4 | select balance from account where id = 2 LOCK IN SHARE MODE; |
阻塞 |
||
| 5 | Ctrl-c | |||
| 6 | select balance from account where id = 1 LOCK IN SHARE MODE; |
阻塞 |
現(xiàn)在對(duì)name 字段新增唯一索引 https://www.cnblogs.com/ShareJia/p/10008110.html
增加唯一索引：CREATE UNIQUE INDEX index_name ON account (NAME);
刪除指定索引：DROP INDEX NAME ON account;
查詢索引：SHOW INDEX FROM account;
| 步驟 | 會(huì)話 A命令 | balance值 | 會(huì)話 B命令 | balance值 |
|---|---|---|---|---|
| 1 | BEGIN; | |||
| 2 | update account set balance = 0 where name = 'li'; |
|||
| 3 | select balance from account where id = 1; | 10 | ||
| 4 | select balance from account where id = 2 LOCK IN SHARE MODE; |
10 |
||
| 6 | select balance from accountwhere id = 1 LOCK IN SHARE MODE; |
阻塞 |
會(huì)發(fā)現(xiàn)纲岭，對(duì)name 字段添加索引后，僅name為'li'的這一行上了鎖线罕。
原理簡(jiǎn)析：
推薦閱讀：
MySQL InnoDB鎖原理剖析
如何書寫 update 避免表鎖
InnoDB行鎖是通過(guò)給索引上的索引項(xiàng)加鎖來(lái)實(shí)現(xiàn)的止潮。所以，只有通過(guò)索引條件檢索數(shù)據(jù)钞楼，InnoDB才使用行級(jí)鎖喇闸，否則，InnoDB將使用表鎖询件。
4燃乍、意向鎖
Inno存儲(chǔ)引擎支持意向共享鎖，意向鎖為表級(jí)別的鎖雳殊。設(shè)計(jì)的主要目的是為了在一個(gè)事務(wù)中揭示下一行將被請(qǐng)求的鎖類型橘沥。其支持兩種意向鎖窗轩。
1夯秃、意向共享鎖 IS
2、意向排他鎖 IX
意向鎖可以優(yōu)化鎖之間的性能痢艺。
B站學(xué)習(xí)視頻
相關(guān)問(wèn)題:
為什么表沒(méi)有索引仓洼，表里所有的記錄都會(huì)被鎖住
update age from user無(wú)索引是行鎖還是表鎖，為什么
