HOME 首頁
SERVICE 服務(wù)產(chǎn)品
XINMEITI 新媒體代運營
CASE 服務(wù)案例
NEWS 熱點資訊
ABOUT 關(guān)于我們
CONTACT 聯(lián)系我們
創(chuàng)意嶺
讓品牌有溫度、有情感
專注品牌策劃15年

    讀已提交讀未提交可重復(fù)讀(讀已提交,讀未提交)

    發(fā)布時間:2023-04-22 08:40:12     稿源: 創(chuàng)意嶺    閱讀: 78        

    大家好!今天讓創(chuàng)意嶺的小編來大家介紹下關(guān)于讀已提交讀未提交可重復(fù)讀的問題,以下是小編對此問題的歸納整理,讓我們一起來看看吧。

    開始之前先推薦一個非常厲害的Ai人工智能工具,一鍵生成原創(chuàng)文章、方案、文案、工作計劃、工作報告、論文、代碼、作文、做題和對話答疑等等

    只需要輸入關(guān)鍵詞,就能返回你想要的內(nèi)容,越精準,寫出的就越詳細,有微信小程序端、在線網(wǎng)頁版、PC客戶端

    官網(wǎng):https://ai.de1919.com。

    創(chuàng)意嶺作為行業(yè)內(nèi)優(yōu)秀的企業(yè),服務(wù)客戶遍布全球各地,如需了解SEO相關(guān)業(yè)務(wù)請撥打電話175-8598-2043,或添加微信:1454722008

    本文目錄:

    讀已提交讀未提交可重復(fù)讀(讀已提交,讀未提交)

    一、mysql 核心內(nèi)容-上

    1、SQL語句執(zhí)行流程

    MySQL大體上可分為Server層和存儲引擎層兩部分。

    Server層:

    連接器:TCP握手后服務(wù)器來驗證登陸用戶身份,A用戶創(chuàng)建連接后,管理員對A用戶權(quán)限修改了也不會影響到已經(jīng)創(chuàng)建的鏈接權(quán)限,必須重新登陸。

    查詢緩存:查詢后的結(jié)果存儲位置,MySQL8.0版本以后已經(jīng)取消,因為查詢緩存失效太頻繁,得不償失。

    分析器:根據(jù)語法規(guī)則,判斷你輸入的這個SQL語句是否滿足MySQL語法。

    優(yōu)化器:多種執(zhí)行策略可實現(xiàn)目標(biāo),系統(tǒng)自動選擇最優(yōu)進行執(zhí)行。

    執(zhí)行器:判斷是否有權(quán)限,將最終任務(wù)提交到存儲引擎。

    存儲引擎層

    負責(zé)數(shù)據(jù)的存儲和提取。其架構(gòu)模式是插件式的,支持InnoDB、MyISAM、Memory等多個存儲引擎?,F(xiàn)在最常用的存儲引擎是InnoDB,它從MySQL 5.5.5版本開始成為了默認存儲引擎(經(jīng)常用的也是這個)。

    SQL執(zhí)行順序

    2、BinLog、RedoLog、UndoLog

    BinLog

    BinLog是記錄所有數(shù)據(jù)庫表結(jié)構(gòu)變更(例如create、alter table)以及表數(shù)據(jù)修改(insert、update、delete)的二進制日志,主從數(shù)據(jù)庫同步用到的都是BinLog文件。BinLog日志文件有三種模式。

    STATEMENT 模式

    內(nèi)容:binlog 記錄可能引起數(shù)據(jù)變更的 sql 語句

    優(yōu)勢:該模式下,因為沒有記錄實際的數(shù)據(jù),所以日志量很少 IO 都消耗很低,性能是最優(yōu)的

    劣勢:但有些操作并不是確定的,比如 uuid() 函數(shù)會隨機產(chǎn)生唯一標(biāo)識,當(dāng)依賴 binlog 回放時,該操作生成的數(shù)據(jù)與原數(shù)據(jù)必然是不同的,此時可能造成無法預(yù)料的后果。

    ROW 模式

    內(nèi)容:在該模式下,binlog 會記錄每次操作的源數(shù)據(jù)與修改后的目標(biāo)數(shù)據(jù),StreamSets就要求該模式。

    優(yōu)勢:可以絕對精準的還原,從而保證了數(shù)據(jù)的安全與可靠,并且復(fù)制和數(shù)據(jù)恢復(fù)過程可以是并發(fā)進行的

    劣勢:缺點在于 binlog 體積會非常大,同時,對于修改記錄多、字段長度大的操作來說,記錄時性能消耗會很嚴重。閱讀的時候也需要特殊指令來進行讀取數(shù)據(jù)。

    MIXED 模式

    內(nèi)容:是對上述STATEMENT 跟 ROW 兩種模式的混合使用。

    細節(jié):對于絕大部分操作,都是使用 STATEMENT 來進行 binlog 沒有記錄,只有以下操作使用 ROW 來實現(xiàn):表的存儲引擎為 NDB,使用了uuid() 等不確定函數(shù),使用了 insert delay 語句,使用了臨時表

    主從同步流程:

    1、主節(jié)點必須啟用二進制日志,記錄任何修改了數(shù)據(jù)庫數(shù)據(jù)的事件。

    2、從節(jié)點開啟一個線程(I/O Thread)把自己扮演成 mysql 的客戶端,通過 mysql 協(xié)議,請求主節(jié)點的二進制日志文件中的事件 。

    3、主節(jié)點啟動一個線程(dump Thread),檢查自己二進制日志中的事件,跟對方請求的位置對比,如果不帶請求位置參數(shù),則主節(jié)點就會從第一個日志文件中的第一個事件一個一個發(fā)送給從節(jié)點。

    4、從節(jié)點接收到主節(jié)點發(fā)送過來的數(shù)據(jù)把它放置到中繼日志(Relay log)文件中。并記錄該次請求到主節(jié)點的具體哪一個二進制日志文件內(nèi)部的哪一個位置(主節(jié)點中的二進制文件會有多個)。

    5、從節(jié)點啟動另外一個線程(sql Thread ),把 Relay log 中的事件讀取出來,并在本地再執(zhí)行一次。

    mysql默認的復(fù)制方式是異步的,并且復(fù)制的時候是有并行復(fù)制能力的。主庫把日志發(fā)送給從庫后不管了,這樣會產(chǎn)生一個問題就是假設(shè)主庫掛了,從庫處理失敗了,這時候從庫升為主庫后,日志就丟失了。由此產(chǎn)生兩個概念。

    全同步復(fù)制

    主庫寫入binlog后強制同步日志到從庫,所有的從庫都執(zhí)行完成后才返回給客戶端,但是很顯然這個方式的話性能會受到嚴重影響。

    半同步復(fù)制

    半同步復(fù)制的邏輯是這樣,從庫寫入日志成功后返回ACK確認給主庫,主庫收到至少一個從庫的確認就認為寫操作完成。

    還可以延伸到由于主從配置不一樣、主庫大事務(wù)、從庫壓力過大、網(wǎng)絡(luò)震蕩等造成主備延遲,如何避免這個問題?主備切換的時候用可靠性優(yōu)先原則還是可用性優(yōu)先原則?如何判斷主庫Crash了?互為主備的情況下如何避免主備循環(huán)復(fù)制?被刪庫跑路了如何正確恢復(fù)?( o )… 感覺越來越扯到DBA的活兒上去了。

    RedoLog

    可以先通過下面demo理解:

    飯點記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話,一般有兩種做法:

    1、直接把賬本翻出來,把這次賒的賬加上去或者扣除掉。

    2、先在粉板上記下這次的賬,等打烊以后再把賬本翻出來核算。

    生意忙時選后者,因為前者太麻煩了。得在密密麻麻的記錄中找到這個人的賒賬總額信息,找到之后再拿出算盤計算,最后再將結(jié)果寫回到賬本上。

    同樣在MySQL中如果每一次的更新操作都需要寫進磁盤,然后磁盤也要找到對應(yīng)的那條記錄,然后再更新,整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術(shù),它的關(guān)鍵點就是先寫日志,再寫磁盤。此時賬本 = BinLog,粉板 = RedoLog。

    1、 記錄更新時,InnoDB引擎就會先把記錄寫到RedoLog(粉板)里面,并更新內(nèi)存。同時,InnoDB引擎會在空閑時將這個操作記錄更新到磁盤里面。

    2、 如果更新太多RedoLog處理不了的時候,需先將RedoLog部分數(shù)據(jù)寫到磁盤,然后擦除RedoLog部分數(shù)據(jù)。RedoLog類似轉(zhuǎn)盤。

    RedoLog有write pos 跟checkpoint

    write pos :是當(dāng)前記錄的位置,一邊寫一邊后移,寫到第3號文件末尾后就回到0號文件開頭。

    check point:是當(dāng)前要擦除的位置,也是往后推移并且循環(huán)的,擦除記錄前要把記錄更新到數(shù)據(jù)文件。

    write pos和check point之間的是粉板上還空著的部分,可以用來記錄新的操作。如果write pos追上checkpoint,表示粉板滿了,這時候不能再執(zhí)行新的更新,得停下來先擦掉一些記錄,把checkpoint推進一下。

    有了redo log,InnoDB就可以保證即使數(shù)據(jù)庫發(fā)生異常重啟,之前提交的記錄都不會丟失,這個能力稱為crash-safe。 redolog兩階段提交:為了讓binlog跟redolog兩份日志之間的邏輯一致。提交流程大致如下:

    1 prepare階段 --> 2 寫binlog --> 3 commit

    當(dāng)在2之前崩潰時,重啟恢復(fù)后發(fā)現(xiàn)沒有commit,回滾。備份恢復(fù):沒有binlog 。一致

    當(dāng)在3之前崩潰時,重啟恢復(fù)發(fā)現(xiàn)雖沒有commit,但滿足prepare和binlog完整,所以重啟后會自動commit。備份:有binlog. 一致

    binlog跟redolog區(qū)別:

    redo log是InnoDB引擎特有的;binlog是MySQL的Server層實現(xiàn)的,所有引擎都可以使用。

    redo log是物理日志,記錄的是在某個數(shù)據(jù)頁上做了什么修改;binlog是邏輯日志,記錄的是這個語句的原始邏輯,比如給ID=2這一行的c字段加1。

    redo log是循環(huán)寫的,空間固定會用完;binlog是可以追加寫入的。追加寫是指binlog文件寫到一定大小后會切換到下一個,并不會覆蓋以前的日志。

    UndoLog

    UndoLog 一般是邏輯日志,主要分為兩種:

    insert undo log

    代表事務(wù)在insert新記錄時產(chǎn)生的undo log, 只在事務(wù)回滾時需要,并且在事務(wù)提交后可以被立即丟棄

    update undo log

    事務(wù)在進行update或delete時產(chǎn)生的undo log; 不僅在事務(wù)回滾時需要,在快照讀時也需要;所以不能隨便刪除,只有在快速讀或事務(wù)回滾不涉及該日志時,對應(yīng)的日志才會被purge線程統(tǒng)一清除

    3、MySQL中的索引

    索引的常見模型有哈希表、有序數(shù)組和搜索樹。

    哈希表:一種以KV存儲數(shù)據(jù)的結(jié)構(gòu),只適合等值查詢,不適合范圍查詢。

    有序數(shù)組:只適用于靜態(tài)存儲引擎,涉及到插入的時候比較麻煩??梢詤⒖糐ava中的ArrayList。

    搜索樹:按照數(shù)據(jù)結(jié)構(gòu)中的二叉樹來存儲數(shù)據(jù),不過此時是N叉樹(B+樹)。廣泛應(yīng)用在存儲引擎層中。

    B+樹比B樹優(yōu)勢在于:

    B+ 樹非葉子節(jié)點存儲的只是索引,可以存儲的更多。B+樹比B樹更加矮胖,IO次數(shù)更少。

    B+ 樹葉子節(jié)點前后管理,更加方便范圍查詢。同時結(jié)果都在葉子節(jié)點,查詢效率穩(wěn)定。

    B+樹中更有利于對數(shù)據(jù)掃描,可以避免B樹的回溯掃描。

    索引的優(yōu)點:

    1、唯一索引可以保證每一行數(shù)據(jù)的唯一性

    2、提高查詢速度

    3、加速表與表的連接

    4、顯著的減少查詢中分組和排序的時間

    5、通過使用索引,可以在查詢的過程中,使用優(yōu)化隱藏器,提高系統(tǒng)的性能。

    索引的缺點:

    1、創(chuàng)建跟維護都需要耗時

    2、創(chuàng)建索引時,需要對表加鎖,在鎖表的同時,可能會影響到其他的數(shù)據(jù)操作

    3、 索引需要磁盤的空間進行存儲,磁盤占用也很快。

    4、當(dāng)對表中的數(shù)據(jù)進行CRUD的時,也會觸發(fā)索引的維護,而維護索引需要時間,可能會降低數(shù)據(jù)操作性能

    索引設(shè)計的原則不應(yīng)該:

    1、索引不是越多越好。索引太多,維護索引需要時間跟空間。

    2、 頻繁更新的數(shù)據(jù),不宜建索引。

    3、數(shù)據(jù)量小的表沒必要建立索引。

    應(yīng)該:

    1、重復(fù)率小的列建議生成索引。因為重復(fù)數(shù)據(jù)少,索引樹查詢更有效率,等價基數(shù)越大越好。

    2、數(shù)據(jù)具有唯一性,建議生成唯一性索引。在數(shù)據(jù)庫的層面,保證數(shù)據(jù)正確性

    3、頻繁group by、order by的列建議生成索引。可以大幅提高分組和排序效率

    4、經(jīng)常用于查詢條件的字段建議生成索引。通過索引查詢,速度更快

    索引失效的場景

    1、模糊搜索:左模糊或全模糊都會導(dǎo)致索引失效,比如'%a'和'%a%'。但是右模糊是可以利用索引的,比如'a%' 。

    2、隱式類型轉(zhuǎn)換:比如select * from t where name = xxx , name是字符串類型,但是沒有加引號,所以是由MySQL隱式轉(zhuǎn)換的,所以會讓索引失效 3、當(dāng)語句中帶有or的時候:比如select * from t where name=‘sw’ or age=14

    4、不符合聯(lián)合索引的最左前綴匹配:(A,B,C)的聯(lián)合索引,你只where了C或B或只有B,C

    關(guān)于索引的知識點:

    主鍵索引:主鍵索引的葉子節(jié)點存的是整行數(shù)據(jù)信息。在InnoDB里,主鍵索引也被稱為聚簇索引(clustered index)。主鍵自增是無法保證完全自增的哦,遇到唯一鍵沖突、事務(wù)回滾等都可能導(dǎo)致不連續(xù)。

    唯一索引:以唯一列生成的索引,該列不允許有重復(fù)值,但允許有空值(NULL)

    普通索引跟唯一索引查詢性能:InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的,默認每頁16KB,因此這兩種索引查詢數(shù)據(jù)性能差別微乎其微。

    change buffer:普通索引用在更新過程的加速,更新的字段如果在緩存中,如果是普通索引則直接更新即可。如果是唯一索引需要將所有數(shù)據(jù)讀入內(nèi)存來確保不違背唯一性,所以盡量用普通索引。

    非主鍵索引:非主鍵索引的葉子節(jié)點內(nèi)容是主鍵的值。在InnoDB里,非主鍵索引也被稱為二級索引(secondary index)

    回表:先通過數(shù)據(jù)庫索引掃描出數(shù)據(jù)所在的行,再通過行主鍵id取出索引中未提供的數(shù)據(jù),即基于非主鍵索引的查詢需要多掃描一棵索引樹。

    覆蓋索引:如果一個索引包含(或者說覆蓋)所有需要查詢的字段的值,我們就稱之為覆蓋索引。

    聯(lián)合索引:相對單列索引,組合索引是用多個列組合構(gòu)建的索引,一次性最多聯(lián)合16個。

    最左前綴原則:對多個字段同時建立的組合索引(有順序,ABC,ACB是完全不同的兩種聯(lián)合索引) 以聯(lián)合索引(a,b,c)為例,建立這樣的索引相當(dāng)于建立了索引a、ab、abc三個索引。另外組合索引實際還是一個索引,并非真的創(chuàng)建了多個索引,只是產(chǎn)生的效果等價于產(chǎn)生多個索引。

    索引下推:MySQL 5.6引入了索引下推優(yōu)化,可以在索引遍歷過程中,對索引中包含的字段先做判斷,過濾掉不符合條件的記錄,減少回表字數(shù)。

    索引維護:B+樹為了維護索引有序性涉及到頁分裂跟頁合并。增刪數(shù)據(jù)時需考慮頁空間利用率。

    自增主鍵:一般會建立與業(yè)務(wù)無關(guān)的自增主鍵,不會觸發(fā)葉子節(jié)點分裂。

    延遲關(guān)聯(lián):通過使用覆蓋索引查詢返回需要的主鍵,再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。

    InnoDB存儲: * .frm文件是一份定義文件,也就是定義數(shù)據(jù)庫表是一張怎么樣的表。*.ibd文件則是該表的索引,數(shù)據(jù)存儲文件,既該表的所有索引樹,所有行記錄數(shù)據(jù)都存儲在該文件中。

    MyISAM存儲:* .frm文件是一份定義文件,也就是定義數(shù)據(jù)庫表是一張怎么樣的表。* .MYD文件是MyISAM存儲引擎表的所有行數(shù)據(jù)的文件。* .MYI文件存放的是MyISAM存儲引擎表的索引相關(guān)數(shù)據(jù)的文件。MyISAM引擎下,表數(shù)據(jù)和表索引數(shù)據(jù)是分開存儲的。

    MyISAM查詢:在MyISAM下,主鍵索引和輔助鍵索引都屬于非聚簇索引。查詢不管是走主鍵索引,還是非主鍵索引,在葉子結(jié)點得到的都是目的數(shù)據(jù)的地址,還需要通過該地址,才能在數(shù)據(jù)文件中找到目的數(shù)據(jù)。

    PS:InnoDB支持聚簇索引,MyISAM不支持聚簇索引

    4、SQL事務(wù)隔離級別

    ACID的四個特性

    原子性(Atomicity):把多個操作放到一個事務(wù)中,保證這些操作要么都成功,要么都不成功

    一致性(Consistency):理解成一串對數(shù)據(jù)進行操作的程序執(zhí)行下來,不會對數(shù)據(jù)產(chǎn)生不好的影響,比如憑空產(chǎn)生,或消失

    隔離性(Isolation,又稱獨立性):隔離性的意思就是多個事務(wù)之間互相不干擾,即使是并發(fā)事務(wù)的情況下,他們只是兩個并發(fā)執(zhí)行沒有交集,互不影響的東西;當(dāng)然實現(xiàn)中,也不一定需要這么完整隔離性,即不一定需要這么的互不干擾,有時候還是允許有部分干擾的。所以MySQL可以支持4種事務(wù)隔離性

    持久性(Durability):當(dāng)某個操作操作完畢了,那么結(jié)果就是這樣了,并且這個操作會持久化到日志記錄中

    PS:ACID中C與CAP定理中C的區(qū)別

    ACID的C著重強調(diào)單數(shù)據(jù)庫事務(wù)操作時,要保證數(shù)據(jù)的完整和正確性,數(shù)據(jù)不會憑空消失跟增加。CAP 理論中的C指的是對一個數(shù)據(jù)多個備份的讀寫一致性

    事務(wù)操作可能會出現(xiàn)的數(shù)據(jù)問題

    1、臟讀(dirty read):B事務(wù)更改數(shù)據(jù)還未提交,A事務(wù)已經(jīng)看到并且用了。B事務(wù)如果回滾,則A事務(wù)做錯了

    2、 不可重復(fù)讀(non-repeatable read):不可重復(fù)讀的重點是修改: 同樣的條件, 你讀取過的數(shù)據(jù), 再次讀取出來發(fā)現(xiàn)值不一樣了,只需要鎖住滿足條件的記錄

    3、 幻讀(phantom read):事務(wù)A先修改了某個表的所有紀錄的狀態(tài)字段為已處理,未提交;事務(wù)B也在此時新增了一條未處理的記錄,并提交了;事務(wù)A隨后查詢記錄,卻發(fā)現(xiàn)有一條記錄是未處理的造成幻讀現(xiàn)象,幻讀僅專指新插入的行。幻讀會造成語義上的問題跟數(shù)據(jù)一致性問題。

    4、 在可重復(fù)讀RR隔離級別下,普通查詢是快照讀,是不會看到別的事務(wù)插入的數(shù)據(jù)的。因此,幻讀在當(dāng)前讀下才會出現(xiàn)。要用間隙鎖解決此問題。

    在說隔離級別之前,你首先要知道,你隔離得越嚴實,效率就會越低。因此很多時候,我們都要在二者之間尋找一個平衡點。SQL標(biāo)準的事務(wù)隔離級別由低到高如下: 上圖從上到下的模式會導(dǎo)致系統(tǒng)的并行性能依次降低,安全性依次提高。

    讀未提交:別人改數(shù)據(jù)的事務(wù)尚未提交,我在我的事務(wù)中也能讀到。

    讀已提交(Oracle默認):別人改數(shù)據(jù)的事務(wù)已經(jīng)提交,我在我的事務(wù)中才能讀到。

    可重復(fù)讀(MySQL默認):別人改數(shù)據(jù)的事務(wù)已經(jīng)提交,我在我的事務(wù)中也不去讀,以此保證重復(fù)讀一致性。

    串行:我的事務(wù)尚未提交,別人就別想改數(shù)據(jù)。

    標(biāo)準跟實現(xiàn):上面都是關(guān)于事務(wù)的標(biāo)準,但是每一種數(shù)據(jù)庫都有不同的實現(xiàn),比如MySQL InnDB 默認為RR級別,但是不會出現(xiàn)幻讀。因為當(dāng)事務(wù)A更新了所有記錄的某個字段,此時事務(wù)A會獲得對這個表的表鎖,因為事務(wù)A還沒有提交,所以事務(wù)A獲得的鎖沒有釋放,此時事務(wù)B在該表插入新記錄,會因為無法獲得該表的鎖,則導(dǎo)致插入操作被阻塞。只有事務(wù)A提交了事務(wù)后,釋放了鎖,事務(wù)B才能進行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經(jīng)實現(xiàn)解決了臟讀,不可重復(fù)讀和幻讀的。

    5、MySQL中的鎖

    無論是Java的并發(fā)編程還是數(shù)據(jù)庫的并發(fā)操作都會涉及到鎖,研發(fā)人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設(shè)計思想。

    悲觀鎖:

    優(yōu)點:適合在寫多讀少的并發(fā)環(huán)境中使用,雖然無法維持非常高的性能,但是在樂觀鎖無法提更好的性能前提下,可以做到數(shù)據(jù)的安全性

    缺點:加鎖會增加系統(tǒng)開銷,雖然能保證數(shù)據(jù)的安全,但數(shù)據(jù)處理吞吐量低,不適合在讀書寫少的場合下使用

    樂觀鎖:

    優(yōu)點:在讀多寫少的并發(fā)場景下,可以避免數(shù)據(jù)庫加鎖的開銷,提高DAO層的響應(yīng)性能,很多情況下ORM工具都有帶有樂觀鎖的實現(xiàn),所以這些方法不一定需要我們?nèi)藶榈娜崿F(xiàn)。

    缺點:在寫多讀少的并發(fā)場景下,即在寫操作競爭激烈的情況下,會導(dǎo)致CAS多次重試,沖突頻率過高,導(dǎo)致開銷比悲觀鎖更高。

    實現(xiàn):數(shù)據(jù)庫層面的樂觀鎖其實跟CAS思想類似, 通數(shù)據(jù)版本號或者時間戳也可以實現(xiàn)。

    數(shù)據(jù)庫并發(fā)場景主要有三種:

    讀-讀:不存在任何問題,也不需要并發(fā)控制

    讀-寫:有隔離性問題,可能遇到臟讀,幻讀,不可重復(fù)讀

    寫-寫:可能存更新丟失問題,比如第一類更新丟失,第二類更新丟失

    兩類更新丟失問題:

    第一類更新丟失:事務(wù)A的事務(wù)回滾覆蓋了事務(wù)B已提交的結(jié)果 第二類更新丟失:事務(wù)A的提交覆蓋了事務(wù)B已提交的結(jié)果

    為了合理貫徹落實鎖的思想,MySQL中引入了雜七雜八的各種鎖:

    鎖分類

    MySQL支持三種層級的鎖定,分別為

    表級鎖定

    MySQL中鎖定粒度最大的一種鎖,最常使用的MYISAM與INNODB都支持表級鎖定。

    頁級鎖定

    是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖,表級鎖速度快,但沖突多,行級沖突少,但速度慢。所以取了折衷的頁級,一次鎖定相鄰的一組記錄。

    行級鎖定

    Mysql中鎖定粒度最細的一種鎖,表示只針對當(dāng)前操作的行進行加鎖。行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小,但加鎖的開銷也最大行級鎖不一定比表級鎖要好:鎖的粒度越細,代價越高,相比表級鎖在表的頭部直接加鎖,行級鎖還要掃描找到對應(yīng)的行對其上鎖,這樣的代價其實是比較高的,所以表鎖和行鎖各有所長。

    MyISAM中的鎖

    雖然MySQL支持表,頁,行三級鎖定,但MyISAM存儲引擎只支持表鎖。所以MyISAM的加鎖相對比較開銷低,但數(shù)據(jù)操作的并發(fā)性能相對就不高。但如果寫操作都是尾插入,那還是可以支持一定程度的讀寫并發(fā)

    從MyISAM所支持的鎖中也可以看出,MyISAM是一個支持讀讀并發(fā),但不支持通用讀寫并發(fā),寫寫并發(fā)的數(shù)據(jù)庫引擎,所以它更適合用于讀多寫少的應(yīng)用場合,一般工程中也用的較少。

    InnoDB中的鎖

    該模式下支持的鎖實在是太多了,具體如下:

    共享鎖和排他鎖 (Shared and Exclusive Locks)

    意向鎖(Intention Locks)

    記錄鎖(Record Locks)

    間隙鎖(Gap Locks)

    臨鍵鎖 (Next-Key Locks)

    插入意向鎖(Insert Intention Locks)

    主鍵自增鎖 (AUTO-INC Locks)

    空間索引斷言鎖(Predicate Locks for Spatial Indexes)

    舉個栗子,比如行鎖里的共享鎖跟排它鎖:lock in share modle 共享讀鎖:

    為了確保自己查到的數(shù)據(jù)沒有被其他的事務(wù)正在修改,也就是說確保查到的數(shù)據(jù)是最新的數(shù)據(jù),并且不允許其他人來修改數(shù)據(jù)。但是自己不一定能夠修改數(shù)據(jù),因為有可能其他的事務(wù)也對這些數(shù)據(jù)使用了 in share mode 的方式上了S 鎖。如果不及時的commit 或者rollback 也可能會造成大量的事務(wù)等待。

    for update排它寫鎖:

    為了讓自己查到的數(shù)據(jù)確保是最新數(shù)據(jù),并且查到后的數(shù)據(jù)只允許自己來修改的時候,需要用到for update。相當(dāng)于一個 update 語句。在業(yè)務(wù)繁忙的情況下,如果事務(wù)沒有及時的commit或者rollback 可能會造成其他事務(wù)長時間的等待,從而影響數(shù)據(jù)庫的并發(fā)使用效率。

    Gap Lock間隙鎖:

    1、行鎖只能鎖住行,如果在記錄之間的間隙插入數(shù)據(jù)就無法解決了,因此MySQL引入了間隙鎖(Gap Lock)。間隙鎖是左右開區(qū)間。間隙鎖之間不會沖突。

    2、間隙鎖和行鎖合稱NextKeyLock,每個NextKeyLock是前開后閉區(qū)間。

    間隙鎖加鎖原則(學(xué)完忘那種):

    1、加鎖的基本單位是 NextKeyLock,是前開后閉區(qū)間。

    2、查找過程中訪問到的對象才會加鎖。

    3、索引上的等值查詢,給唯一索引加鎖的時候,NextKeyLock退化為行鎖。

    4、索引上的等值查詢,向右遍歷時且最后一個值不滿足等值條件的時候,NextKeyLock退化為間隙鎖。

    5、唯一索引上的范圍查詢會訪問到不滿足條件的第一個值為止。

    二、如何處理數(shù)據(jù)庫并發(fā)問題

    想要知道如何處理數(shù)據(jù)并發(fā),自然需要先了解數(shù)據(jù)并發(fā)。

    什么是數(shù)據(jù)并發(fā)操作呢?

    就是同一時間內(nèi),不同的線程同時對一條數(shù)據(jù)進行讀寫操作。

    在互聯(lián)網(wǎng)時代,一個系統(tǒng)常常有很多人在使用,因此就可能出現(xiàn)高并發(fā)的現(xiàn)象,也就是不同的用戶同時對一條數(shù)據(jù)進行操作,如果沒有有效的處理,自然就會出現(xiàn)數(shù)據(jù)的異常。而最常見的一種數(shù)據(jù)并發(fā)的場景就是電商中的秒殺,成千上萬個用戶對在極端的時間內(nèi),搶購一個商品。針對這種場景,商品的庫存就是一個需要控制的數(shù)據(jù),而多個用戶對在同一時間對庫存進行重寫,一個不小心就可能出現(xiàn)超賣的情況。

    針對這種情況,我們?nèi)绾斡行У奶幚頂?shù)據(jù)并發(fā)呢?

    第一種方案、數(shù)據(jù)庫鎖

    從鎖的基本屬性來說,可以分為兩種:一種是共享鎖(S),一種是排它鎖(X)。在MySQL的數(shù)據(jù)庫中,是有四種隔離級別的,會在讀寫的時候,自動的使用這兩種鎖,防止數(shù)據(jù)出現(xiàn)混亂。

    這四種隔離級別分別是:

    讀未提交(Read Uncommitted)

    讀提交(Read Committed)

    可重復(fù)讀(Repeated Read)

    串行化(Serializable)

    當(dāng)然,不同的隔離級別,效率也是不同的,對于數(shù)據(jù)的一致性保證也就有不同的結(jié)果。而這些可能出現(xiàn)的又有哪些呢?

    臟讀(dirty read)

    當(dāng)事務(wù)與事務(wù)之間沒有任何隔離的時候,就可能會出現(xiàn)臟讀。例如:商家想看看所有的訂單有哪些,這時,用戶A提交了一個訂單,但事務(wù)還沒提交,商家卻看到了這個訂單。而這時就會出現(xiàn)一種問題,當(dāng)商家去操作這個訂單時,可能用戶A的訂單由于部分問題,導(dǎo)致數(shù)據(jù)回滾,事務(wù)沒有提交,這時商家的操作就會失去目標(biāo)。

    不可重復(fù)讀(unrepeatable read)

    一個事務(wù)中,兩次讀操作出來的同一條數(shù)據(jù)值不同,就是不可重復(fù)讀。

    例如:我們有一個事務(wù)A,需要去查詢一下商品庫存,然后做扣減,這時,事務(wù)B操作了這個商品,扣減了一部分庫存,當(dāng)事務(wù)A再次去查詢商品庫存的時候,發(fā)現(xiàn)這一次的結(jié)果和上次不同了,這就是不可重復(fù)讀。

    幻讀(phantom problem)

    一個事務(wù)中,兩次讀操作出來的結(jié)果集不同,就是幻讀。

    例如:一個事務(wù)A,去查詢現(xiàn)在已經(jīng)支付的訂單有哪些,得到了一個結(jié)果集。這時,事務(wù)B新提交了一個訂單,當(dāng)事務(wù)A再次去查詢時,就會出現(xiàn),兩次得到的結(jié)果集不同的情況,也就是幻讀了。

    那針對這些結(jié)果,不同的隔離級別可以干什么呢?

    “讀未提(Read Uncommitted)”能預(yù)防啥?啥都預(yù)防不了。

    “讀提交(Read Committed)”能預(yù)防啥?使用“快照讀(Snapshot Read)”方式,避免“臟讀”,但是可能出現(xiàn)“不可重復(fù)讀”和“幻讀”。

    “可重復(fù)讀(Repeated Red)”能預(yù)防啥?使用“快照讀(Snapshot Read)”方式,鎖住被讀取記錄,避免出現(xiàn)“臟讀”、“不可重復(fù)讀”,但是可能出現(xiàn)“幻讀”。

    “串行化(Serializable)”能預(yù)防啥?有效避免“臟讀”、“不可重復(fù)讀”、“幻讀”,不過運行效率奇差。

    好了,鎖說完了,但是,我們的數(shù)據(jù)庫鎖,并不能有效的解決并發(fā)的問題,只是盡可能保證數(shù)據(jù)的一致性,當(dāng)并發(fā)量特別大時,數(shù)據(jù)庫還是容易扛不住。那解決數(shù)據(jù)并發(fā)的另一個手段就是,盡可能的提高處理的速度。

    因為數(shù)據(jù)的IO要提升難度比較大,那么通過其他的方式,對數(shù)據(jù)進行處理,減少數(shù)據(jù)庫的IO,就是提高并發(fā)能力的有效手段了。

    最有效的一種方式就是:緩存

    想要減少并發(fā)出現(xiàn)的概率,那么讀寫的效率越高,讀寫的執(zhí)行時間越短,自然數(shù)據(jù)并發(fā)的可能性就變小了,并發(fā)性能也有提高了。

    還是用剛才的秒殺舉例,我們?yōu)榈木褪潜WC庫存的數(shù)據(jù)不出錯,賣出一個商品,減一個庫存,那么,我們就可以將庫存放在內(nèi)存中進行處理。這樣,就能夠保證庫存有序的及時扣減,并且不出現(xiàn)問題。這樣,我們的數(shù)據(jù)庫的寫操作也變少了,執(zhí)行效率也就大大提高了。

    當(dāng)然,常用的分布式緩存方式有:Redis和Memcache,Redis可以持久化到硬盤,而Memcache不行,應(yīng)該怎么選擇,就看具體的使用場景了。

    當(dāng)然,緩存畢竟使用的范圍有限,很多的數(shù)據(jù)我們還是必須持久化到硬盤中,那我們就需要提高數(shù)據(jù)庫的IO能力,這樣避免一個線程執(zhí)行時間太長,造成線程的阻塞。

    那么,讀寫分離就是另一種有效的方式了

    當(dāng)我們的寫成為了瓶頸的時候,讀寫分離就是一種可以選擇的方式了。

    我們的讀庫就只需要執(zhí)行讀,寫庫就只需要執(zhí)行寫,把讀的壓力從主庫中分離出去,讓主庫的資源只是用來保證寫的效率,從而提高寫操作的性能。

    三、mysql默認隔離級別怎么加鎖

    修改方法

    有兩種方法可以對配置了 systemd 的程序進行資源隔離:1. 命令行修改:通過執(zhí)行 systemctl set-property 命令實現(xiàn),形式為 systemctl set-property name parameter=value;修改默認即時生效。2. 手工修改文件:直接編輯程序的 systemd unit file 文件,完成之后需手工執(zhí)行 systemctl daemon-reload 更新配置,并重啟服務(wù) systemctl restart name.service。

    systemd unit file 里支持的資源隔離配置項,如常見的:

    • CPUQuota=value

      該參數(shù)表示服務(wù)可以獲取的最大 CPU 時間,value 為百分數(shù)形式,高于 100% 表示可使用 1 核以上的 CPU。與 cgroup cpu 控制器 cpu.cfs_quota_us 配置項對應(yīng)。

    • MemoryLimit=value

      該參數(shù)表示服務(wù)可以使用的最大內(nèi)存量,value 可以使用 K, M, G, T 等后綴表示值的大小。與 cgroup memory 控制器 memory.limit_in_bytes 配置項對應(yīng)。

    • 事務(wù)的4種隔離級別

      READ UNCOMMITTED       未提交讀,可以讀取未提交的數(shù)據(jù)。

      READ COMMITTED         已提交讀,對于鎖定讀(select with for update 或者 for share)、update 和 delete 語句,InnoDB 僅鎖定索引記錄,而不鎖定它們之間的間隙,因此允許在鎖定的記錄旁邊自由插入新記錄。                    

      Gap locking 僅用于外鍵約束檢查和重復(fù)鍵檢查。

      REPEATABLE READ        可重復(fù)讀,事務(wù)中的一致性讀取讀取的是事務(wù)第一次讀取所建立的快照。

      SERIALIZABLE           序列化在了解了 4 種隔離級別的需求后,在采用鎖控制隔離級別的基礎(chǔ)上,我們需要了解加鎖的對象(數(shù)據(jù)本身&間隙),以及了解整個數(shù)據(jù)范圍的全集組成。

      數(shù)據(jù)范圍全集組成

      SQL 語句根據(jù)條件判斷不需要掃描的數(shù)據(jù)范圍(不加鎖);

      SQL 語句根據(jù)條件掃描到的可能需要加鎖的數(shù)據(jù)范圍;

      以單個數(shù)據(jù)范圍為例,數(shù)據(jù)范圍全集包含:(數(shù)據(jù)范圍不一定是連續(xù)的值,也可能是間隔的值組成)

    四、數(shù)據(jù)庫操作的時候,什么情況下需要用到事務(wù)?

    當(dāng)數(shù)據(jù)庫需要處理操作量大、復(fù)雜度高的數(shù)據(jù)的時候需要用到事務(wù)。用事務(wù)是為了保證數(shù)據(jù)庫的完整性,保證成批的 SQL 語句要么全部執(zhí)行,要么全部不執(zhí)行。

    一個數(shù)據(jù)庫事務(wù)通常包含了一個序列的對數(shù)據(jù)庫的讀/寫操作。它的存在包含有以下兩個目的:

    1、為數(shù)據(jù)庫操作序列提供了一個從失敗中恢復(fù)到正常狀態(tài)的方法,同時提供了數(shù)據(jù)庫即使在異常狀態(tài)下仍能保持一致性的方法。

    2、當(dāng)多個應(yīng)用程序在并發(fā)訪問數(shù)據(jù)庫時,可以在這些應(yīng)用程序之間提供一個隔離方法,以防止彼此的操作互相干擾。

    當(dāng)事務(wù)被提交給了數(shù)據(jù)庫管理系統(tǒng),則數(shù)據(jù)庫管理系統(tǒng)需要確保該事務(wù)中的所有操作都成功完成且其結(jié)果被永久保存在數(shù)據(jù)庫中,如果事務(wù)中有的操作沒有成功完成,則事務(wù)中的所有操作都需要被回滾,回到事務(wù)執(zhí)行前的狀態(tài);同時,該事務(wù)對數(shù)據(jù)庫或者其他事務(wù)的執(zhí)行無影響,所有的事務(wù)都好像在獨立的運行。

    讀已提交讀未提交可重復(fù)讀(讀已提交,讀未提交)

    擴展資料:

    數(shù)據(jù)庫事務(wù)ACID性質(zhì):

    1、原子性(Atomicity):事務(wù)作為一個整體被執(zhí)行,包含在其中的對數(shù)據(jù)庫的操作要么全部被執(zhí)行,要么都不執(zhí)行。

    2、一致性(Consistency):事務(wù)應(yīng)確保數(shù)據(jù)庫的狀態(tài)從一個一致狀態(tài)轉(zhuǎn)變?yōu)榱硪粋€一致狀態(tài),一致狀態(tài)的含義是數(shù)據(jù)庫中的數(shù)據(jù)應(yīng)滿足完整性約束。

    3、隔離性(Isolation):多個事務(wù)并發(fā)執(zhí)行時,一個事務(wù)的執(zhí)行不應(yīng)影響其他事務(wù)的執(zhí)行。

    4、持久性(Durability):已被提交的事務(wù)對數(shù)據(jù)庫的修改應(yīng)該永久保存在數(shù)據(jù)庫中。

    參考資料來源:

    百度百科-數(shù)據(jù)庫事務(wù)

    百度百科-數(shù)據(jù)庫

    以上就是關(guān)于讀已提交讀未提交可重復(fù)讀相關(guān)問題的回答。希望能幫到你,如有更多相關(guān)問題,您也可以聯(lián)系我們的客服進行咨詢,客服也會為您講解更多精彩的知識和內(nèi)容。


    推薦閱讀:

    讀已提交怎么實現(xiàn)(讀已提交有什么問題)

    讀已提交(讀已提交解決了什么問題)

    讀已提交讀未提交可重復(fù)讀(讀已提交,讀未提交)

    抖音提現(xiàn)必須本人支付寶嗎(抖音提現(xiàn)必須本人支付寶嗎怎么弄)

    浙江小區(qū)景觀設(shè)計方案(浙江小區(qū)景觀設(shè)計方案圖)