簡述:

我們的MySQL一般會并發(fā)的執(zhí)行多個事務(wù),多個事務(wù)可能會并發(fā)的對同一條或者同一批數(shù)據(jù)進(jìn)行crud操作;可能就會導(dǎo)致我們平常所說的臟讀、不可重復(fù)讀、幻讀這些問題.

這些問題的本質(zhì)都是MySQL多事務(wù)并發(fā)問題,為了解決多事務(wù)并發(fā)問題,MySQL設(shè)計了鎖機(jī)制、MVCC多版本并發(fā)控制隔離機(jī)制、以及事務(wù)隔離機(jī)制,用一整套機(jī)制來解決多事務(wù)并發(fā)所出現(xiàn)的問題.

1. 事務(wù)的四大特性

2.多事務(wù)并發(fā)帶來的問題

3.事務(wù)的隔離級別

臟讀、不可重復(fù)讀和幻讀,其實都是MySQL讀一致性問題,必須由數(shù)據(jù)庫提供一定的事務(wù)隔離機(jī)制來解決.

查看當(dāng)前數(shù)據(jù)庫的事務(wù)隔離級別:show variables like ‘tx_isolation';

設(shè)置事務(wù)隔離級別:set tx_isolation='隔離級別'

4.演示不同隔離級別出現(xiàn)的問題

mysql版本:5.7.34

涉及表:

兩個MySQL客戶端

客戶端A <===================> 客戶端B(下面每張圖片兩個客戶端皆以第一張圖命名為準(zhǔn)

讀未提交

1.1 設(shè)置事務(wù)隔離級別set tx_isolation=‘read-uncommitted';

1.2 客戶端A和客戶端B各開啟一個事務(wù),

1.3 客戶端A只做查詢,客戶端B對id = 1的記錄做修改;

1.4 再兩個事務(wù)都未提交的情況下,事務(wù)A讀到了事務(wù)B修改后的數(shù)據(jù)

1.5 一旦客戶端B的事務(wù)因為某種原因rollback,那么客戶端A查詢到的數(shù)據(jù)其實就是臟數(shù)據(jù),不符合一致性的要求

讀已提交

2.1 設(shè)置隔離級別讀已提交:set tx_isolation=‘read-committed';

2.2 客戶端A和客戶端B各開啟一個事務(wù),

2.3 客戶端A只做查詢,客戶端B對id = 1的記錄做修改;

2.4 客戶端B未提交事務(wù)時,客戶端A不能查詢客戶端B未提交的數(shù)據(jù),解決了臟讀的問題

2.5 當(dāng)客戶端B提交事務(wù)后,客戶端A再次對表進(jìn)行查詢,結(jié)果與上一步不一致,即產(chǎn)生了不可重復(fù)讀的問題,不符合隔離性

可重復(fù)讀

3.1 設(shè)置隔離級別可重復(fù)讀:set tx_isolation=‘repeatable-read';

3.2 客戶端A和客戶端B各開啟一個事務(wù),

3.3 客戶端B修改表中數(shù)據(jù)然后提交;

3.4 客戶端A查詢表中數(shù)據(jù),并未出現(xiàn)與上一步不一致的問題,解決了不可重復(fù)讀的問題

3.5 在客戶端A中執(zhí)行update account set balance = balance - 100 where id = 1;blance并未有變成800-100=700;而是使用客戶端B提交后的數(shù)據(jù)來算的,所以是600;數(shù)據(jù)的一致性并沒有被破壞;可重復(fù)讀的隔離級別下使用的是MVCC機(jī)制,select操作不會更新版本號,是快照讀(歷史版本),保證同一事務(wù)下的可重復(fù)讀;insert/update/delete會更新版本號,是當(dāng)前讀(當(dāng)前版本)保證數(shù)據(jù)的一致性

3.6 客戶端B重新開啟一個事務(wù)插入一條數(shù)據(jù)后提交

3.7 在客戶端A中重新查詢表數(shù)據(jù),并沒有出現(xiàn)客戶端B剛才新增的數(shù)據(jù),沒有出現(xiàn)幻讀

3.8 驗證幻讀:在客戶端A中,對id = 4 的數(shù)據(jù)做修改;可以更新成功;再次進(jìn)行查詢就能查詢出客戶端B新增的數(shù)據(jù),出現(xiàn)幻讀問題,不符合隔離性

串行化

4.1 設(shè)置隔離級別串行化:set tx_isolation=‘serializable';

4.2 客戶端A和客戶端B各開啟一個事務(wù),

4.3 客戶端A先查詢表中id = 1的數(shù)據(jù)

4.4 在客戶端A事務(wù)未提交時,客戶端B對表中id = 1 的數(shù)據(jù)做更新;由于客戶端A的事務(wù)并沒有提交,客戶端B的更新動作將會阻塞至到客戶端A提交事務(wù)或者超時,超時SQL報錯:Lock wait timeout exceeded; try restarting transaction

4.5 在客戶端B中更新id = 2 的數(shù)據(jù)卻可以成功,說明在串行化的隔離級別下,innodb的查詢也會被加上行鎖;

4.6 如果客戶端A執(zhí)行的是一個范圍查詢,那么該范圍內(nèi)的所有行包括每行記錄所在的間隙區(qū)間范圍(就算該行未被插入也會加鎖,這種是間隙鎖)都會被加鎖,此時如果客戶端B對該范圍內(nèi)的數(shù)據(jù)做任何操作都會被阻塞;所以就避免了幻讀;

4.7 串行化這種隔離級別并發(fā)性極低,所以再真實的開發(fā)很少會遇到,這也是MySQL為什么使用可重復(fù)讀作為默認(rèn)的隔離級別的重要原因

5.鎖機(jī)制

MySQL默認(rèn)的隔離級別是可重復(fù)讀,可是還是會出現(xiàn)幻讀問題;間隙鎖再某種情況下可以解決幻讀問題;

間隙鎖

概述:間隙鎖,鎖的就是兩個值之間的空隙.

假設(shè)表中數(shù)據(jù)如下:

那么間隙就有(4,10)、(10,15)和(15,正無窮)三個間隙;

1.1 設(shè)置隔離級別可重復(fù)讀:set tx_isolation=‘repeatable-read';

1.2 客戶端A和客戶端B各開啟一個事務(wù),

1.3 在客戶端A執(zhí)行update account set balance = 1000 where id > 5 and id < 13 ;

1.4 在客戶端A未提交的時候,客戶端B是沒有辦法對這個范圍包含的所有行記錄(包括間隙行記錄)以及行記錄所在間隙里執(zhí)行insert/update操作,即4<id<=15這個區(qū)間內(nèi)都無法修改數(shù)據(jù),id = 15 同樣不能修改;

1.5 間隙鎖只有在可重復(fù)讀的隔離級別下才會生效

臨建鎖

概述:臨建鎖是行鎖和間隙鎖的結(jié)合,想上面那個4<id<=15就屬于臨建鎖;

無索引行鎖會升級成為表鎖

3.1 客戶端A和客戶端B各開啟一個事務(wù),

3.2 在客戶端A執(zhí)行update account set balance = 1000 where name = ‘李四';

3.3 在客戶端A未提交的時候,客戶端B執(zhí)行update account set balance = 800 where id = 15 ;同樣會被阻塞至客戶端A提交或者超時;

3.4 MySQL中的鎖主要是加載索引字段上,如果使用再非索引字段上,行鎖會升級成表鎖;

排他鎖

4.1 客戶端A和客戶端B各開啟一個事務(wù),

4.2 在客戶端A執(zhí)行select * from account where id = 1 for update ;

4.3 在客戶端A未提交的時候,客戶端B執(zhí)行update account set balance = 800 where id = 1 ;會被阻塞至客戶端A提交或者超時;

結(jié)論:Innodb引擎實現(xiàn)了行鎖,雖然行鎖機(jī)制實現(xiàn)方面所帶來的性能損耗可能比表級鎖定會更高,但是再整體并發(fā)處理能力肯定要強(qiáng)于表級鎖;當(dāng)系統(tǒng)并發(fā)量高的時候,行級鎖和表級鎖相比就會有比較明顯的優(yōu)勢;但是行級鎖使用起來也比表級鎖復(fù)雜,當(dāng)我們使用不當(dāng)?shù)臅r候,可能會使行鎖的性能不僅不比表級鎖的性能高,甚至可能會更差.

為什么行鎖鎖定的粒度小,開銷反而會比表級鎖的開銷大?

因為表級鎖只需要找到當(dāng)前表就可以進(jìn)行加鎖,行鎖的話需要對表中記錄進(jìn)行掃描,直至掃描到需要加鎖的行才可以進(jìn)行加鎖,所以行鎖的開銷是比表級鎖的開銷要來得大的.

真實開發(fā)情況下對鎖優(yōu)化的一些建議:

• 合理使用索引字段加鎖,縮小鎖的范圍
• 盡可能讓所有鎖都加到索引字段上,避免無索引行鎖升級成表鎖
• 盡可能減少查詢范圍,避免間隙過大的間隙鎖
• 盡可能低級別事務(wù)隔離
• 盡可能控制事務(wù)大小,減少鎖定資源量,涉及事務(wù)加鎖的sql盡量放在事務(wù)最后執(zhí)行,減少加鎖的時間

總結(jié)

到此這篇關(guān)于MySQL隔離級別和鎖機(jī)制的文章就介紹到這了,更多相關(guān)MySQL隔離級別和鎖機(jī)制內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家！

最新評論