欧美bbbwbbbw肥妇,免费乱码人妻系列日韩,一级黄片

MySQL的表級鎖,行級鎖,排它鎖和共享鎖

 更新時間:2022年07月14日 15:01:04   作者:菜鳥~~  
這篇文章主要介紹了MySQL的表級鎖,行級鎖,排它鎖和共享鎖,事務(wù)隔離級別越高,為避免沖突所花費(fèi)的性能也就越多,即效率低,更多相關(guān)內(nèi)容需要的小伙伴可以參考與一下

前言

如果我們和面試官聊到事務(wù)的問題,怎么回答呢?

先說下事務(wù)是什么,因為我們業(yè)務(wù)是比較復(fù)雜的,不可能一個sql就能解決的,涉及多個sql就組成一個事務(wù)。事務(wù)就是一組sql共同執(zhí)行,要么完全成功,要么完全失敗,不能出現(xiàn)部分成功或者部分失敗的情況。一個事務(wù)有ACID特性(可以參考:事務(wù)的ACID特性MySQL事務(wù)的隔離級別):

  • 原子性:要么全部成功,要么全部失敗,這樣才能保證事務(wù)的一致性;
  • 一致性:比如銀行的轉(zhuǎn)賬,扣除一個人的錢肯定要給另一個人加錢,不能光扣除不加,這樣業(yè)務(wù)就存在問題,數(shù)據(jù)的一致性就破壞了;
  • 持久性:當(dāng)我們數(shù)據(jù)commit以后,數(shù)據(jù)是先寫到緩存當(dāng)中,緩存中的數(shù)據(jù)還是要慢慢花時間往磁盤上寫,如果此時停電了、宕機(jī)或者重啟了,我們有redo log重做日志來保證數(shù)據(jù)庫的持久性;
  • 隔離性:這塊可以說下事務(wù)為什么要有隔離性,因為事務(wù)要允許并發(fā)執(zhí)行,一個業(yè)務(wù)涉及了很多事務(wù),而我們后臺往往有很多業(yè)務(wù),要能夠讓他們并發(fā)執(zhí)行,如果所有的事務(wù)都是串行執(zhí)行的話,那這樣我們寫多線程程序只有一個線程來做事情,這樣效率很低。所以事務(wù)要并發(fā)執(zhí)行,但是并發(fā)執(zhí)行涉及了一些問題:事務(wù)的安全性&一致性并發(fā)的效率問題,我們以這兩個東西為參考點,才得到了MySQL不同等級的并發(fā)/隔離,如果事務(wù)并發(fā)執(zhí)行時我們完全不隔離的話,就可能會出現(xiàn)臟讀(事務(wù)B讀到了事務(wù)A還未提交的數(shù)據(jù)然后,然后用事務(wù)A未提交的數(shù)據(jù)去做計算,得到了很多其他的結(jié)果,然后事務(wù)A又把那個數(shù)據(jù)rollback掉,那么事務(wù)B計算出來的都是有問題的數(shù)據(jù),臟讀一定會出現(xiàn)問題)、不可重復(fù)讀(以同樣的條件去一個數(shù)據(jù),然后再次去查詢的時候發(fā)現(xiàn)數(shù)據(jù)的值有所改變,當(dāng)然不可重復(fù)讀也不一定會有問題,有些業(yè)務(wù)場景下是允許的,這和業(yè)務(wù)上數(shù)據(jù)的安全性和一致性是否嚴(yán)格有關(guān))和幻讀(在事務(wù)中按照同樣的條件前后兩次查詢的結(jié)果數(shù)據(jù)量不同)這些問題。

那么我們?yōu)榱私鉀Q事務(wù)并發(fā)執(zhí)行遇到的問題就給出了事務(wù)的隔離級別:

  • 串行化,串行化完全用鎖來實現(xiàn),通過鎖給所有事務(wù)排序,按順序執(zhí)行,這樣做數(shù)據(jù)的安全性高但并發(fā)的效率很低,一般我們不會這樣做的。
  • 未提交讀,對于我們寫的多線程程序來說,對于臨界區(qū)代碼段沒有做任何的并發(fā)控制,雖然并發(fā)性高但數(shù)據(jù)安全性很低,未提交讀還允許臟讀的存在,這是有問題的所以絕對不會使用未提交讀。串行化和未提交讀在實際項目中是不會用到的,一般數(shù)據(jù)庫引擎默認(rèn)工作在已提交讀和可重復(fù)讀,這兩個隔離級別就結(jié)合了數(shù)據(jù)的安全性&一致性和數(shù)據(jù)的并發(fā)效率,這兩個是由MVCC多版本并發(fā)控制機(jī)制實現(xiàn)的。
  • 已提交讀,oracle默認(rèn)工作級別。不允許讀取未commit的數(shù)據(jù),這個級別仍然允許不可重復(fù)讀和虛讀產(chǎn)生。
  • 可重復(fù)讀,MySQL默認(rèn)工作級別。保證事務(wù)再次讀取是依然得到相同的數(shù)據(jù),部分解決了虛讀,但虛讀是仍然會出現(xiàn)的

注意:

  • 事務(wù)隔離級別越高,為避免沖突所花費(fèi)的性能也就越多,即效率低。
  • 在“可重復(fù)讀”級別,實際上可以解決部分的虛讀問題,但是不能防止update更新產(chǎn)生的虛讀問題,要禁止虛讀產(chǎn)生,還是需要設(shè)置串行化隔離級別。

事務(wù)隔離級別的實現(xiàn)原理:鎖+MVCC。串行化底層實現(xiàn)原理是鎖,鎖有共享鎖、排它鎖、意向共享鎖、意向排它鎖、間隙鎖和死鎖,InnoDB的已提交讀和可重復(fù)讀的底層實現(xiàn)原理:MVCC(多版本并發(fā)控制),MVCC提供了一種并發(fā)讀取方式,包括快照讀(同一份數(shù)據(jù)會有多個版本)、當(dāng)前讀、undo log和redo log。MVCC是已提交讀和可重復(fù)讀的原理,鎖是串行化的原理

ACD特性用事務(wù)日志實現(xiàn),I 特性用共享鎖、排它鎖、MVCC 實現(xiàn)。事務(wù)日志分為undo log(回滾日志) 和 redo log(重做日志)

一、表級鎖&行級鎖

  • 表級鎖:對整張表加鎖。開銷?。ㄒ驗椴挥萌フ冶淼哪骋恍械挠涗涍M(jìn)行加鎖,要修改這張表,直接申請加這張表的鎖),加鎖快,不會出
  • 現(xiàn)死鎖;鎖粒度大,發(fā)生鎖沖突的概率高,并發(fā)度低
  • 行級鎖:對某行記錄加鎖。開銷大(需要找到表中相應(yīng)的記錄,有搜表搜索引的過程),加鎖慢,會出現(xiàn)死鎖;鎖定粒度最小,發(fā)生鎖沖突的概率最低,并發(fā)度高

MyISAM存儲引擎只支持表級鎖,InnoDB支持事務(wù)處理,支持行級鎖,并發(fā)能力更好

二、排它鎖&共享鎖

  • 排它鎖:又稱為X鎖,寫鎖
  • 共享鎖:又稱為S鎖,讀鎖

讀讀(SS)之間是可以兼容的,但是讀寫(SX、SX)之間,寫寫(XX)之間是互斥的

1. 測試不同事務(wù)之間排它鎖和共享鎖的兼容性

我們先查看表SQL及內(nèi)容

查看隔離級別:

首先開啟一個事務(wù)A,給id=7的數(shù)據(jù)加上排它鎖:

在另一個客戶端開啟事務(wù)B:

給id=7不管加排它鎖和共享鎖都阻塞了并沒有查詢出來,因為A事務(wù)給id=7這一行的數(shù)據(jù)加了排它鎖,就是寫鎖,其他人不能讀也不能寫。

總結(jié):不同事務(wù)之間對于數(shù)據(jù)的鎖,只有SS鎖可以共存,XX、SX、XS都不能共存

2. 測試行鎖加在索引項上

其實行鎖是加在索引樹上的。

每次做完測試都把剛做的rollback。

用表的無索引字段作為過濾條件

那現(xiàn)在事務(wù)2獲取不同行chenwei的記錄

InnoDB是支持行鎖的,剛才以主鍵id為過濾條件時,事務(wù)1和事務(wù)2獲取不同行的鎖是可以成功的。然而現(xiàn)在我們發(fā)現(xiàn)獲取name為chenwei的排它鎖也獲取不到了,這是為什么?我們解釋一下:

InnoDB的行鎖是通過給索引項加鎖來實現(xiàn)的,而不是給表的行記錄加鎖實現(xiàn)的

而我們用name作為過濾條件沒有用到索引,自然就不會使用行鎖,而是使用表鎖。這就意味著只有通過索引檢索數(shù)據(jù),InnoDB才使用行級鎖,否則InnoDB都將使用表鎖!!!

我們給name字段加上索引:

然后再做剛才的操作:

我們發(fā)現(xiàn),給name加上索引后,兩個事務(wù)可以獲取到不同行的排它鎖(for update),再一次證明了InnoDB的行鎖是加在索引項上的。

因為現(xiàn)在name走的是索引, 通過zhangsan在輔助索引樹上找到它所在行記錄的id是7,然后到主鍵索引樹上,獲取對應(yīng)行記錄的排他鎖(個人猜測應(yīng)該是輔助索引樹和主鍵索引樹相應(yīng)的記錄都加了鎖)

三、串行化隔離級別測試

串行化所有事務(wù)用的都是共享鎖或者排它鎖,不需用手動添加。select獲取的是共享鎖,insert、delete和update獲取的都是排它鎖。

設(shè)置串行化隔離級別:

兩個事務(wù)可以同時獲取共享鎖(SS共存:

現(xiàn)在讓事務(wù)2插入數(shù)據(jù);

由于Insert需要加排它鎖,但是由于事務(wù)1已經(jīng)對整張表加了共享鎖,事務(wù)2無法再對表成功加鎖(sx不共存)

rollback一下,把所有獲取鎖的狀態(tài)都回退掉:

開啟兩個事務(wù):

因為我們給name加上了索引,以上的select相當(dāng)于給name為zhangsan的數(shù)據(jù)加上了行共享鎖

事務(wù)2update;

事務(wù)2不能update,因為此時已經(jīng)被事務(wù)1的共享鎖鎖住了整個表

事務(wù)2在輔助索引樹上找zhangsan,找到對應(yīng)的主鍵值,然后去主鍵索引樹找到相應(yīng)的記錄,但是發(fā)現(xiàn)這行記錄已經(jīng)被共享鎖鎖住了,事務(wù)2可以獲取共享鎖,但是不能獲取排他鎖

我們再用主鍵索引試試id能不能update

依然阻塞住了,雖然我們where后面的字段現(xiàn)在使用的id而不是name,但是name也是通過輔助索引樹找到對應(yīng)的主鍵,再到主鍵索引樹上找相應(yīng)的記錄,而主鍵索引樹上的記錄加了鎖

我們update id=8的數(shù)據(jù),成功了。因為我們select的時候,只是給id=7的數(shù)據(jù)加上了行鎖,我們操作id=8的數(shù)據(jù)當(dāng)然可以成功

有索引,則使用行鎖;沒有索引,則使用表鎖。

表級鎖還是行級鎖說的是鎖的粒度,共享鎖和排他鎖說的是鎖的性質(zhì),不管是表鎖還是行鎖,都有共享鎖和排他鎖的區(qū)分。

串行化玩的就是排它鎖和共享鎖,在可重復(fù)讀級別下,不手動加鎖的話,用的就是MVCC機(jī)制,實際上并沒有用到鎖,我們也可以手動加鎖。InnoDB如果不創(chuàng)建索引的話,用的是表鎖,如果查詢的時候用到了索引項,它用的就是行鎖了,行鎖是給索引加鎖,而不是單純給一行數(shù)據(jù)加鎖。

到此這篇關(guān)于MySQL的表級鎖,行級鎖,排它鎖和共享鎖的文章就介紹到這了,更多相關(guān)MySQL鎖內(nèi)容請搜索腳本之家以前的文章或繼續(xù)瀏覽下面的相關(guān)文章希望大家以后多多支持腳本之家!

相關(guān)文章

  • bitronix 連接 MySQL 出現(xiàn)MySQLSyntaxErrorException 的解決方法

    bitronix 連接 MySQL 出現(xiàn)MySQLSyntaxErrorException 的解決方法

    這篇文章主要介紹了bitronix 連接 MySQL 出現(xiàn)MySQLSyntaxErrorException 的解決方法的相關(guān)資料,需要的朋友可以參考下
    2017-04-04
  • MySQL數(shù)據(jù)庫如何正確設(shè)置主鍵

    MySQL數(shù)據(jù)庫如何正確設(shè)置主鍵

    主鍵是用于唯一標(biāo)識數(shù)據(jù)庫表中每一行數(shù)據(jù)的一列或一組列,主鍵可以確保數(shù)據(jù)的唯一性和完整性,這篇文章主要給大家介紹了關(guān)于MySQL數(shù)據(jù)庫如何正確設(shè)置主鍵的相關(guān)資料,需要的朋友可以參考下
    2024-04-04
  • mysql Access denied for user ‘root’@’localhost’ (using password: YES)解決方法

    mysql Access denied for user ‘root’@’localhost’ (using passw

    這篇文章主要介紹了mysql Access denied for user ‘root’@’localhost’ (using password: YES)解決方法,本文給出詳細(xì)的解決步驟及操作注釋,需要的朋友可以參考下
    2015-07-07
  • mysql字段為null為何不能使用!=

    mysql字段為null為何不能使用!=

    這篇文章主要介紹了mysql字段為null為何不能使用!=問題,具有很好的參考價值,希望對大家有所幫助,如有錯誤或未考慮完全的地方,望不吝賜教
    2024-05-05
  • mysql啟動報錯MySQL server PID file could not be found

    mysql啟動報錯MySQL server PID file could not be found

    這篇文章主要介紹了mysql啟動報錯MySQL server PID file could not be found,具有一定的參考價值,感興趣的小伙伴們可以參考一下
    2016-11-11
  • MySQL分區(qū)表的詳細(xì)介紹

    MySQL分區(qū)表的詳細(xì)介紹

    當(dāng)設(shè)計MySQL分區(qū)表時,需要考慮以下幾個方面,分區(qū)策略、分區(qū)字段、分區(qū)數(shù)量和分區(qū)函數(shù),本文就來介紹一下MySQL分區(qū)表,感興趣的可以了解一下
    2023-10-10
  • 詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化

    詳解MySQL中的數(shù)據(jù)類型和schema優(yōu)化

    這篇文章主要介紹了MySQL中的數(shù)據(jù)類型和schema優(yōu)化的相關(guān)資料,幫助大家更好的理解和學(xué)習(xí)MySQL的知識,感興趣的朋友可以了解下
    2020-10-10
  • Windows 64 位 mysql 5.7以上版本包解壓中沒有data目錄和my-default.ini及服務(wù)無法啟動的快速解決辦法(問題小結(jié))

    Windows 64 位 mysql 5.7以上版本包解壓中沒有data目錄和my-default.ini及服務(wù)無法啟動

    這篇文章主要介紹了Windows 64 位 mysql 5.7以上版本包解壓中沒有data目錄和my-default.ini及服務(wù)無法啟動的快速解決辦法(問題小結(jié)),需要的朋友可以參考下
    2018-03-03
  • MySQL數(shù)據(jù)庫數(shù)據(jù)刪除操作詳解

    MySQL數(shù)據(jù)庫數(shù)據(jù)刪除操作詳解

    本文我們將要學(xué)習(xí)的是作為刪除數(shù)據(jù)使用的?“DELETE”?語句,“DELETE”?語句是用來刪除數(shù)據(jù)的,它不能用來刪除數(shù)據(jù)表本身。刪除數(shù)據(jù)表使用的是?“DROP”?語句,而?“DELETE”?的作用只是用來刪除記錄而已
    2022-08-08
  • MySQL?使用觸發(fā)器記錄用戶的操作日志問題

    MySQL?使用觸發(fā)器記錄用戶的操作日志問題

    使用?MySQL?觸發(fā)器可以記錄哪些用戶、什么時間對數(shù)據(jù)表進(jìn)行了增、刪、改操作。如果執(zhí)行刪除操作,則記錄刪除之前的數(shù)據(jù)記錄;如果執(zhí)行更新操作,記錄更新之前的數(shù)據(jù)記錄,這篇文章主要介紹了MySQL?使用觸發(fā)器記錄用戶的操作日志,需要的朋友可以參考下
    2022-12-12

最新評論