5. InnoDB 行鎖

5.1 行鎖介紹

行鎖特點(diǎn) ：偏向InnoDB 存儲(chǔ)引擎，開(kāi)銷大，加鎖慢；會(huì)出現(xiàn)死鎖；鎖定粒度最小，發(fā)生鎖沖突的概率最低,并發(fā)度也最高。
InnoDB 與 MyISAM 的最大不同有兩點(diǎn)：一是支持事務(wù)；二是 采用了行級(jí)鎖。

5.2 背景知識(shí)

事務(wù)及其ACID屬性
事務(wù)是由一組SQL語(yǔ)句組成的邏輯處理單元。
事務(wù)具有以下4個(gè)特性，簡(jiǎn)稱為事務(wù)ACID屬性。

并發(fā)事務(wù)處理帶來(lái)的問(wèn)題

事務(wù)隔離級(jí)別

為了解決上述提到的事務(wù)并發(fā)問(wèn)題，數(shù)據(jù)庫(kù)提供一定的事務(wù)隔離機(jī)制來(lái)解決這個(gè)問(wèn)題。數(shù)據(jù)庫(kù)的事務(wù)隔離越嚴(yán)格，并發(fā)副作用越小，但付出的代價(jià)也就越大，因?yàn)槭聞?wù)隔離實(shí)質(zhì)上就是使用事務(wù)在一定程度上“串行化” 進(jìn)行，這顯然與“并發(fā)” 是矛盾的。

數(shù)據(jù)庫(kù)的隔離級(jí)別有4個(gè)，由低到高依次為Read uncommitted、Read committed、Repeatable read、
Serializable，這四個(gè)級(jí)別可以逐個(gè)解決臟寫(xiě)、臟讀、不可重復(fù)讀、幻讀這幾類問(wèn)題。

備注 ： √ 代表可能出現(xiàn) ， × 代表不會(huì)出現(xiàn) 。：

Mysql 的數(shù)據(jù)庫(kù)的默認(rèn)隔離級(jí)別為 Repeatable read ， 查看方式:

show variables like 'tx_isolation';

image.png

5.3 InnoDB 的行鎖模式

InnoDB 實(shí)現(xiàn)了以下兩種類型的行鎖。

• 共享鎖（S）：又稱為讀鎖，簡(jiǎn)稱S鎖，共享鎖就是多個(gè)事務(wù)對(duì)于同一數(shù)據(jù)可以共享一把鎖，都能訪問(wèn)到數(shù)據(jù)，但是只能讀不能修改。

• 排他鎖（X）：又稱為寫(xiě)鎖，簡(jiǎn)稱X鎖，排他鎖就是不能與其他鎖并存，如一個(gè)事務(wù)獲取了一個(gè)數(shù)據(jù)行的排他鎖，其他事務(wù)就不能再獲取該行的其他鎖，包括共享鎖和排他鎖，但是獲取排他鎖的事務(wù)是可以對(duì)數(shù)據(jù)就行讀取和修改。

對(duì)于UPDATE、DELETE和INSERT語(yǔ)句，InnoDB會(huì)自動(dòng)給涉及數(shù)據(jù)集加排他鎖（X)；

對(duì)于普通SELECT語(yǔ)句，InnoDB不會(huì)加任何鎖；

可以通過(guò)以下語(yǔ)句顯示給記錄集加共享鎖或排他鎖 。

共享鎖（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他鎖（X) ：SELECT * FROM table_name WHERE ... FOR UPDATE

5.4 案例準(zhǔn)備工作

create table test_innodb_lock(
    id int(11),
    name varchar(16),
    sex varchar(1)
)engine = innodb default charset=utf8;
insert into test_innodb_lock values(1,'100','1');
insert into test_innodb_lock values(3,'3','1');
insert into test_innodb_lock values(4,'400','0');
insert into test_innodb_lock values(5,'500','1');
insert into test_innodb_lock values(6,'600','0');
insert into test_innodb_lock values(7,'700','0');
insert into test_innodb_lock values(8,'800','1');
insert into test_innodb_lock values(9,'900','1');
insert into test_innodb_lock values(1,'200','0');
create index idx_test_innodb_lock_id on test_innodb_lock(id);
create index idx_test_innodb_lock_name on test_innodb_lock(name);

5.5 行鎖的基本演示

session-1	session-2
image.png 關(guān)閉自動(dòng)提交功能	image.png 關(guān)閉自動(dòng)提交功能
image.png 可以正常的查詢出全部的數(shù)據(jù)	image.png 可以正常的查詢出全部的數(shù)據(jù)
image.png 查詢id 為3的數(shù)據(jù) ；	image.png 查詢id 為3的數(shù)據(jù) ；
image.png 更新id為3的數(shù)據(jù)，但是不提交；	image.png 更新id為3 的數(shù)據(jù)， 出于等待狀態(tài)
image.png 通過(guò)commit， 提交事務(wù)	image.png 解除阻塞，更新正常進(jìn)行
以上， 操作的都是同一行的數(shù)據(jù)，接下來(lái)，演示不同行的數(shù)據(jù) ：
image.png 更新id為3數(shù)據(jù)，正常的獲取到行鎖 ， 執(zhí)行更新	image.png 由于與Session-1 操作不是同一行，獲取當(dāng)前行鎖，執(zhí)行更新

5.6 無(wú)索引 行鎖 升級(jí)為 表鎖

如果不通過(guò)索引條件檢索數(shù)據(jù)，那么InnoDB將對(duì)表中的所有記錄加鎖，實(shí)際效果跟表鎖一樣。

查看當(dāng)前表的索引 ： show index from test_innodb_lock ;

image.png

session-1	session-2
關(guān)閉事務(wù)的自動(dòng)提交 image.png	關(guān)閉事務(wù)的自動(dòng)提交 image.png
執(zhí)行更新語(yǔ)句 ： image.png	執(zhí)行更新語(yǔ)句， 但處于阻塞狀態(tài)： image.png
提交事務(wù)： image.png	解除阻塞，執(zhí)行更新成功 ： image.png
	執(zhí)行提交操作 ： image.png

由于 執(zhí)行更新時(shí) ， name字段本來(lái)為varchar類型， 我們是作為數(shù)組類型使用，存在類型轉(zhuǎn)換，索引失效，最終行鎖變?yōu)楸礞i ；

5.7 間隙鎖 危害

當(dāng)我們用范圍條件，而不是使用相等條件檢索數(shù)據(jù)，并請(qǐng)求共享或排他鎖時(shí)，InnoDB會(huì)給符合條件的已有數(shù)據(jù)進(jìn)行加鎖； 對(duì)于鍵值在條件范圍內(nèi)但并不存在的記錄，叫做 "間隙（GAP）" ， InnoDB也會(huì)對(duì)這個(gè) "間隙" 加鎖，這種鎖機(jī)制就是所謂的 間隙鎖（Next-Key鎖） 。

示例 ：

Session-1	Session-2
關(guān)閉事務(wù)自動(dòng)提交 image.png	關(guān)閉事務(wù)自動(dòng)提交 image.png
根據(jù)id范圍更新數(shù)據(jù) image.png
	插入id為2的記錄， 出于阻塞狀態(tài) image.png
提交事務(wù) ； image.png
	解除阻塞 ， 執(zhí)行插入操作 ： image.png
	提交事務(wù) ：

5.8 InnoDB 行鎖爭(zhēng)用情況

show status like 'innodb_row_lock%';

image.png

Innodb_row_lock_current_waits: 當(dāng)前正在等待鎖定的數(shù)量

Innodb_row_lock_time: 從系統(tǒng)啟動(dòng)到現(xiàn)在鎖定總時(shí)間長(zhǎng)度

Innodb_row_lock_time_avg:每次等待所花平均時(shí)長(zhǎng)

Innodb_row_lock_time_max:從系統(tǒng)啟動(dòng)到現(xiàn)在等待最長(zhǎng)的一次所花的時(shí)間

Innodb_row_lock_waits: 系統(tǒng)啟動(dòng)后到現(xiàn)在總共等待的次數(shù)

當(dāng)?shù)却拇螖?shù)很高，而且每次等待的時(shí)長(zhǎng)也不小的時(shí)候，我們就需要分析系統(tǒng)中為什么會(huì)有如此多的等待，然后根據(jù)分析結(jié)果著手制定優(yōu)化計(jì)劃。

5.9 總結(jié)

InnoDB存儲(chǔ)引擎由于實(shí)現(xiàn)了行級(jí)鎖定，雖然在鎖定機(jī)制的實(shí)現(xiàn)方面帶來(lái)了性能損耗可能比表鎖會(huì)更高一些，但是在整體并發(fā)處理能力方面要遠(yuǎn)遠(yuǎn)由于MyISAM的表鎖的。當(dāng)系統(tǒng)并發(fā)量較高的時(shí)候，InnoDB的整體性能和MyISAM相比就會(huì)有比較明顯的優(yōu)勢(shì)。

但是，InnoDB的行級(jí)鎖同樣也有其脆弱的一面，當(dāng)我們使用不當(dāng)?shù)臅r(shí)候，可能會(huì)讓InnoDB的整體性能表現(xiàn)不僅不能比MyISAM高，甚至可能會(huì)更差。
優(yōu)化建議：

• 盡可能讓所有數(shù)據(jù)檢索都能通過(guò)索引來(lái)完成，避免無(wú)索引行鎖升級(jí)為表鎖。
• 合理設(shè)計(jì)索引，盡量縮小鎖的范圍
• 盡可能減少索引條件，及索引范圍，避免間隙鎖
• 盡量控制事務(wù)大小，減少鎖定資源量和時(shí)間長(zhǎng)度
• 盡可使用低級(jí)別事務(wù)隔離（但是需要業(yè)務(wù)層面滿足需求）