快捷導(dǎo)航

生產(chǎn)環(huán)境的MySQL事務(wù)隔離級(jí)別方式

更新時(shí)間：2025年02月07日 15:49:14 作者：空說

本文探討了MySQL數(shù)據(jù)庫(kù)在RR（可重復(fù)讀）和RC（讀已提交）隔離級(jí)別下的鎖機(jī)制,在RR級(jí)別下,UPDATE語(yǔ)句會(huì)鎖定所有符合條件的行,包括不符合條件的行,以防止幻讀,而在RC級(jí)別下,UPDATE語(yǔ)句僅鎖定符合條件的行,通過半一致性讀優(yōu)化,可以進(jìn)一步提高并發(fā)度

生產(chǎn)環(huán)境的MySQL事務(wù)隔離級(jí)別

MySQL 數(shù)據(jù)庫(kù)的默認(rèn)隔離級(jí)別是 RR( 可重復(fù)讀 )，但是很多大公司把隔離級(jí)別改成了 RC(讀已提交)，主要原因是為了提高并發(fā)和降低死鎖概率

為了解決幻讀的問題 RR 相比 RC 多了間隙鎖( gap lock )和臨鍵鎖( next-keylock )。而 RC 中修改數(shù)據(jù)僅用行鎖，鎖定的范圍更小，因此相比而言 RC的并發(fā)更高。

創(chuàng)建如下的表，并插入一些記錄

CREATE TABLE t (a INT NOT NULL, b INT) ENGINE = InnoDB;
INSERT INTO t VALUES (1,2),(2,3),(3,2),(4,3),(5,2);

此時(shí)執(zhí)行SQL-A ，且未提交事務(wù)

UPDATE t SET b = 5 WHERE b = 3;

在 RR 即可重復(fù)隔離級(jí)別情況下，會(huì)鎖哪幾條數(shù)據(jù)呢?

答案是會(huì)鎖定所有符合條件的行，并且為了防止幻讀，還會(huì)鎖定這些行之間的間隙。

x-lock(2,3) 和 x-lock(4,3)  -- 這兩行會(huì)被更新并保留X鎖。
x-lock(1,2)、x-lock(3,2) 和 x-lock(5,2) -- 這些行雖然不符合更新條件，但由于間隙鎖的存在，它們也會(huì)被鎖定以防止其他事務(wù)插入新的行

可以看到全鎖了，此時(shí)執(zhí)行 SQL-B:

UPDATE t SET b = 4 WHERE b = 2;

就會(huì)被阻塞了。

因此，在RR隔離級(jí)別下，UPDATE t SET b = 5 WHERE b = 3; 會(huì)鎖定所有行，包括那些不符合更新條件的行。

而執(zhí)行 SQL-A 在 RC 即讀已提交隔離級(jí)別下，會(huì)鎖哪幾條數(shù)據(jù)呢?

x-lock(1,2); unlock(1,2)
x-lock(2,3); update(2,3) to (2,5); 保留 x-lock
x-lock(3,2); unlock(3,2)
x-lock(4,3); update(4,3) to (4,5); 保留 x-lock
x-lock(5,2); unlock(5,2)
-- 只會(huì)鎖定符合更新條件的行，即 b = 3 的行，因?yàn)镽C級(jí)別不使用間隙鎖。

可以看到，只鎖了兩條數(shù)據(jù)，此時(shí)執(zhí)行 SQL-B 會(huì)怎樣?

x-lock(1,2); update(1,2) to (1,4); 保留 x-lock
x-lock(2,3); unlock(2,3)
x-lock(3,2); update(3,2) to (3,4); 保留 x-lock
x-lock(4,3); unlock(4,3)
x-lock(5,2); update(5,2) to (5,4); 保留 x-lock

可以看到僅鎖了 b=2 的數(shù)據(jù)，完美避開了 SQL-A加的鎖

此時(shí)可能有同學(xué)會(huì)有疑問: SQL-B 不是應(yīng)該被 (2, 3 )這行的鎖給阻塞嗎?

半一致性讀(“semi-consistent”read)

這其實(shí)也是 InnoDB 做的一個(gè)優(yōu)化。

在執(zhí)行 update 的時(shí)候，掃描發(fā)現(xiàn)當(dāng)前行已經(jīng)被鎖定了，它就會(huì)執(zhí)行半一致性讀的操作，得到當(dāng)前數(shù)據(jù)的最新版本(上述中 SQL-A 鎖定的行最新版本的已提交數(shù)據(jù)，b都為5 )，來(lái)判斷是否和當(dāng)前的(SQL-B)update 的 where 條件匹配
如果匹配則說明當(dāng)前的 update也需要鎖定這行
因此需要等待。如果不匹配說明它們之間沒關(guān)聯(lián)，因此不需要等待鎖，這個(gè)優(yōu)化提升了并發(fā)度。

所以 RC + 半一致性讀 能進(jìn)一步的提升 SQL 執(zhí)行的并發(fā)度。

并且 RC 鎖的粒度更小，意味著死鎖的概率會(huì)更低，但是缺點(diǎn)是可能會(huì)產(chǎn)生幻讀，這個(gè)就需要業(yè)務(wù)自已評(píng)估幻讀的問題(大部分情況下都沒啥影響)。