深入理解MySQL數(shù)據(jù)庫各種鎖（總結(jié)）

MyISAM和InnoDB存儲引擎使用的鎖：

創(chuàng)新互聯(lián)公司主要從事成都網(wǎng)站制作、網(wǎng)站設計、網(wǎng)頁設計、企業(yè)做網(wǎng)站、公司建網(wǎng)站等業(yè)務。立足成都服務敦化,十余年網(wǎng)站建設經(jīng)驗,價格優(yōu)惠、服務專業(yè),歡迎來電咨詢建站服務:18982081108

封鎖粒度小：

由于InnoDB存儲引擎支持的是行級別的鎖，因此意向鎖（因為意向鎖是表鎖）其實不會阻塞除全表掃以外的任何請求。故表級意向鎖與行級鎖的兼容性如下所示

參考

參考

行鎖的三種算法：

這條語句阻止其他事務插入10和20之間的數(shù)字，無論這個數(shù)字是否存在。 間隙可以跨越0個，單個或多個索引值。

共享鎖：

排他鎖：

樂觀鎖：總是假設最好的情況，每次去拿數(shù)據(jù)的時候都認為別人不會修改（天真）， 操作數(shù)據(jù)時不會上鎖 ，但是 更新時會判斷在此期間有沒有別的事務更新這個數(shù)據(jù)，若被更新過，則失敗重試 ；適用于讀多寫少的場景。

樂觀鎖的實現(xiàn)方式 有：

關閉自動提交后，我們需要手動開啟事務。

上述就實現(xiàn)了悲觀鎖，悲觀鎖就是悲觀主義者，它會認為我們在事務A中操作數(shù)據(jù)1的時候，一定會有事務B來修改數(shù)據(jù)1,所以，在第2步我們將數(shù)據(jù)查詢出來后直接加上排它鎖（X）鎖，防止別的事務來修改事務1，直到我們commit后，才釋放了排它鎖。

Mysql中鎖的類型有哪些呢？

mysql鎖分為共享鎖和排他鎖，也叫做讀鎖和寫鎖。

讀鎖是共享的，可以通過lock in share mode實現(xiàn)，這時候只能讀不能寫。

寫鎖是排他的，它會阻塞其他的寫鎖和讀鎖。從顆粒度來區(qū)分，可以分為表鎖和?鎖兩種。

表鎖會鎖定整張表并且阻塞其他?戶對該表的所有讀寫操作，?如alter修改表結(jié)構的時候會鎖表。

?鎖?可以分為樂觀鎖和悲觀鎖，悲觀鎖可以通過for update實現(xiàn)，樂觀鎖則通過版本號實現(xiàn)。

mysql中的樂觀鎖和悲觀鎖怎么用

關于mysql中的樂觀鎖和悲觀鎖面試的時候被問到的概率還是比較大的。

mysql的悲觀鎖：

其實理解起來非常簡單，當數(shù)據(jù)被外界修改持保守態(tài)度，包括自身系統(tǒng)當前的其他事務，以及來自外部系統(tǒng)的事務處理，因此，在整個數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機制，但是也只有數(shù)據(jù)庫層提供的鎖機制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在自身系統(tǒng)中實現(xiàn)了加鎖機制，也無法保證外部系統(tǒng)不會修改數(shù)據(jù)。

來點實際的，當我們使用悲觀鎖的時候我們首先必須關閉mysql數(shù)據(jù)庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當你執(zhí)行一個更新操作后，MySQL會立刻將結(jié)果進行提交。

關閉命令為：set autocommit=0;

悲觀鎖可以使用select…for update實現(xiàn)，在執(zhí)行的時候會鎖定數(shù)據(jù)，雖然會鎖定數(shù)據(jù)，但是不影響其他事務的普通查詢使用。此處說普通查詢就是平時我們用的：select * from table 語句。在我們使用悲觀鎖的時候事務中的語句例如：

//開始事務

begin;/begin work;/start transaction; (三選一)

//查詢信息

select * from order where id=1 for update;

//修改信息

update order set name='names';

//提交事務

commit;/commit work;(二選一)

此處的查詢語句for update關鍵字，在事務中只有SELECT ... FOR UPDATE 或LOCK IN SHARE MODE 同一條數(shù)據(jù)時會等待其它事務結(jié)束后才執(zhí)行，一般的SELECT查詢則不受影響。

執(zhí)行事務時關鍵字select…for update會鎖定數(shù)據(jù)，防止其他事務更改數(shù)據(jù)。但是鎖定數(shù)據(jù)也是有規(guī)則的。

查詢條件與鎖定范圍：

1、具體的主鍵值為查詢條件

比如查詢條件為主鍵ID=1等等，如果此條數(shù)據(jù)存在，則鎖定當前行數(shù)據(jù)，如果不存在，則不鎖定。

2、不具體的主鍵值為查詢條件

比如查詢條件為主鍵ID1等等，此時會鎖定整張數(shù)據(jù)表。

3、查詢條件中無主鍵

會鎖定整張數(shù)據(jù)表。

4、如果查詢條件中使用了索引為查詢條件

明確指定索引并且查到，則鎖定整條數(shù)據(jù)。如果找不到指定索引數(shù)據(jù)，則不加鎖。

悲觀鎖的確保了數(shù)據(jù)的安全性，在數(shù)據(jù)被操作的時候鎖定數(shù)據(jù)不被訪問，但是這樣會帶來很大的性能問題。因此悲觀鎖在實際開發(fā)中使用是相對比較少的。

mysql的樂觀鎖：

相對悲觀鎖而言，樂觀鎖假設數(shù)據(jù)一般情況下不會造成沖突，所以在數(shù)據(jù)進行提交更新的時候，才會對數(shù)據(jù)的沖突與否進行檢測，如果發(fā)現(xiàn)沖突，則讓返回用戶錯誤的信息，讓用戶決定如何去做。

一般來說，實現(xiàn)樂觀鎖的方法是在數(shù)據(jù)表中增加一個version字段，每當數(shù)據(jù)更新的時候這個字段執(zhí)行加1操作。這樣當數(shù)據(jù)更改的時候，另外一個事務訪問此條數(shù)據(jù)進行更改的話就會操作失敗，從而避免了并發(fā)操作錯誤。當然，還可以將version字段改為時間戳，不過原理都是一樣的。

例如有表student，字段：

id,name,version

1 a 1

當事務一進行更新操作：update student set name='ygz' where id = #{id} and version = #{version};

此時操作完后數(shù)據(jù)會變?yōu)閕d = 1,name = ygz,version = 2，當另外一個事務二同樣執(zhí)行更新操作的時候，卻發(fā)現(xiàn)version != 1，此時事務二就會操作失敗，從而保證了數(shù)據(jù)的正確性。

悲觀鎖和樂觀鎖都是要根據(jù)具體業(yè)務來選擇使用，本文僅作簡單介紹。

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

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

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

Server層：

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

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

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

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

執(zhí)行器：判斷是否有權限，將最終任務提交到存儲引擎。

存儲引擎層

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

SQL執(zhí)行順序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是記錄所有數(shù)據(jù)庫表結(jié)構變更（例如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)生唯一標識，當依賴 binlog 回放時，該操作生成的數(shù)據(jù)與原數(shù)據(jù)必然是不同的，此時可能造成無法預料的后果。

ROW 模式

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

優(yōu)勢：可以絕對精準的還原，從而保證了數(shù)據(jù)的安全與可靠，并且復制和數(shù)據(jù)恢復過程可以是并發(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ā)送給從庫后不管了，這樣會產(chǎn)生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫后，日志就丟失了。由此產(chǎn)生兩個概念。

全同步復制

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

半同步復制

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

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

RedoLog

可以先通過下面demo理解：

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

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

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

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

同樣在MySQL中如果每一次的更新操作都需要寫進磁盤，然后磁盤也要找到對應的那條記錄，然后再更新，整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術，它的關鍵點就是先寫日志，再寫磁盤。此時賬本 = 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 ：是當前記錄的位置，一邊寫一邊后移，寫到第3號文件末尾后就回到0號文件開頭。

check point：是當前要擦除的位置，也是往后推移并且循環(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

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

當在3之前崩潰時，重啟恢復發(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

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

update undo log

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

3、MySQL中的索引

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

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

有序數(shù)組：只適用于靜態(tài)存儲引擎，涉及到插入的時候比較麻煩。可以參考Java中的ArrayList。

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

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、當對表中的數(shù)據(jù)進行CRUD的時，也會觸發(fā)索引的維護，而維護索引需要時間，可能會降低數(shù)據(jù)操作性能

索引設計的原則不應該：

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

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

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

應該：

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

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

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

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

索引失效的場景

1、模糊搜索：左模糊或全模糊都會導致索引失效，比如'%a'和'%a%'。但是右模糊是可以利用索引的，比如'a%' 。

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

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

關于索引的知識點：

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

唯一索引：以唯一列生成的索引，該列不允許有重復值，但允許有空值(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)合索引：相對單列索引，組合索引是用多個列組合構建的索引，一次性最多聯(lián)合16個。

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

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

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

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

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

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

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

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

PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引

4、SQL事務隔離級別

ACID的四個特性

原子性（Atomicity）：把多個操作放到一個事務中，保證這些操作要么都成功，要么都不成功

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

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

持久性（Durability）：當某個操作操作完畢了，那么結(jié)果就是這樣了，并且這個操作會持久化到日志記錄中

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

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

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

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

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

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

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

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

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

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

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

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

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

5、MySQL中的鎖

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

悲觀鎖：

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

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

樂觀鎖：

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

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

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

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

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

讀-寫：有隔離性問題，可能遇到臟讀，幻讀，不可重復讀

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

兩類更新丟失問題：

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

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

鎖分類

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

表級鎖定

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

頁級鎖定

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

行級鎖定

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

MyISAM中的鎖

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

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

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

for update排它寫鎖:

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

Gap Lock間隙鎖：

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

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

間隙鎖加鎖原則(學完忘那種)：

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

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

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

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

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

mysql的事務四個特性以及事務的四個隔離級別

分別是原子性、一致性、隔離性、持久性。

原子性是指事務包含的所有操作要么全部成功，要么全部失敗回滾，因此事務的操作如果成功就必須要完全應用到數(shù)據(jù)庫，如果操作失敗則不能對數(shù)據(jù)庫有任何影響。

一致性是指事務必須使數(shù)據(jù)庫從一個一致性狀態(tài)變換到另一個一致性狀態(tài)，也就是說一個事務執(zhí)行之前和執(zhí)行之后都必須處于一致性狀態(tài)。舉例來說，假設用戶A和用戶B兩者的錢加起來一共是1000，那么不管A和B之間如何轉(zhuǎn)賬、轉(zhuǎn)幾次賬，事務結(jié)束后兩個用戶的錢相加起來應該還得是1000，這就是事務的一致性。

隔離性是當多個用戶并發(fā)訪問數(shù)據(jù)庫時，比如同時操作同一張表時，數(shù)據(jù)庫為每一個用戶開啟的事務，不能被其他事務的操作所干擾，多個并發(fā)事務之間要相互隔離。關于事務的隔離性數(shù)據(jù)庫提供了多種隔離級別，稍后會介紹到。

持久性是指一個事務一旦被提交了，那么對數(shù)據(jù)庫中的數(shù)據(jù)的改變就是永久性的，即便是在數(shù)據(jù)庫系統(tǒng)遇到故障的情況下也不會丟失提交事務的操作。例如我們在使用JDBC操作數(shù)據(jù)庫時，在提交事務方法后，提示用戶事務操作完成，當我們程序執(zhí)行完成直到看到提示后，就可以認定事務已經(jīng)正確提交，即使這時候數(shù)據(jù)庫出現(xiàn)了問題，也必須要將我們的事務完全執(zhí)行完成。否則的話就會造成我們雖然看到提示事務處理完畢，但是數(shù)據(jù)庫因為故障而沒有執(zhí)行事務的重大錯誤。這是不允許的。

在數(shù)據(jù)庫操作中，在并發(fā)的情況下可能出現(xiàn)如下問題：

正是為了解決以上情況，數(shù)據(jù)庫提供了幾種隔離級別。

數(shù)據(jù)庫事務的隔離級別有4個，由低到高依次為Read uncommitted(未授權讀取、讀未提交)、Read committed（授權讀取、讀提交）、Repeatable read（可重復讀取）、Serializable（序列化），這四個級別可以逐個解決臟讀、不可重復讀、幻象讀這幾類問題。

雖然數(shù)據(jù)庫的隔離級別可以解決大多數(shù)問題，但是靈活度較差，為此又提出了悲觀鎖和樂觀鎖的概念。

悲觀鎖，它指的是對數(shù)據(jù)被外界（包括本系統(tǒng)當前的其他事務，以及來自外部系統(tǒng)的事務處理）修改持保守態(tài)度。因此，在整個數(shù)據(jù)處理過程中，將數(shù)據(jù)處于鎖定狀態(tài)。悲觀鎖的實現(xiàn)，往往依靠數(shù)據(jù)庫提供的鎖機制。也只有數(shù)據(jù)庫層提供的鎖機制才能真正保證數(shù)據(jù)訪問的排他性，否則，即使在本系統(tǒng)的數(shù)據(jù)訪問層中實現(xiàn)了加鎖機制，也無法保證外部系統(tǒng)不會修改數(shù)據(jù)。

商品t_items表中有一個字段status，status為1代表商品未被下單，status為2代表商品已經(jīng)被下單（此時該商品無法再次下單），那么我們對某個商品下單時必須確保該商品status為1。假設商品的id為1。

如果不采用鎖，那么操作方法如下：

但是上面這種場景在高并發(fā)訪問的情況下很可能會出現(xiàn)問題。例如當?shù)谝徊讲僮髦校樵兂鰜淼纳唐穝tatus為1。但是當我們執(zhí)行第三步Update操作的時候，有可能出現(xiàn)其他人先一步對商品下單把t_items中的status修改為2了，但是我們并不知道數(shù)據(jù)已經(jīng)被修改了，這樣就可能造成同一個商品被下單2次，使得數(shù)據(jù)不一致。所以說這種方式是不安全的。

在上面的場景中，商品信息從查詢出來到修改，中間有一個處理訂單的過程，使用悲觀鎖的原理就是，當我們在查詢出t_items信息后就把當前的數(shù)據(jù)鎖定，直到我們修改完畢后再解鎖。那么在這個過程中，因為t_items被鎖定了，就不會出現(xiàn)有第三者來對其進行修改了。需要注意的是，要使用悲觀鎖，我們必須關閉mysql數(shù)據(jù)庫的自動提交屬性，因為MySQL默認使用autocommit模式，也就是說，當你執(zhí)行一個更新操作后，MySQL會立刻將結(jié)果進行提交。我們可以使用命令設置MySQL為非autocommit模式： set autocommit=0;

設置完autocommit后，我們就可以執(zhí)行我們的正常業(yè)務了。具體如下：

上面的begin/commit為事務的開始和結(jié)束，因為在前一步我們關閉了mysql的autocommit，所以需要手動控制事務的提交。

上面的第一步我們執(zhí)行了一次查詢操作： select status from t_items where id=1 for update; 與普通查詢不一樣的是，我們使用了 select…for update 的方式，這樣就通過數(shù)據(jù)庫實現(xiàn)了悲觀鎖。此時在t_items表中，id為1的那條數(shù)據(jù)就被我們鎖定了，其它的事務必須等本次事務提交之后才能執(zhí)行。這樣我們可以保證當前的數(shù)據(jù)不會被其它事務修改。需要注意的是，在事務中，只有 SELECT ... FOR UPDATE 或 LOCK IN SHARE MODE 操作同一個數(shù)據(jù)時才會等待其它事務結(jié)束后才執(zhí)行，一般 SELECT ... 則不受此影響。拿上面的實例來說，當我執(zhí)行 select status from t_items where id=1 for update; 后。我在另外的事務中如果再次執(zhí)行 select status from t_items where id=1 for update; 則第二個事務會一直等待第一個事務的提交，此時第二個查詢處于阻塞的狀態(tài)，但是如果我是在第二個事務中執(zhí)行 select status from t_items where id=1; 則能正常查詢出數(shù)據(jù)，不會受第一個事務的影響。

使用 select…for update 會把數(shù)據(jù)給鎖住，不過我們需要注意一些鎖的級別，MySQL InnoDB默認Row-Level Lock，所以只有「明確」地指定主鍵或者索引，MySQL 才會執(zhí)行Row lock (只鎖住被選取的數(shù)據(jù)) ，否則MySQL 將會執(zhí)行Table Lock (將整個數(shù)據(jù)表單給鎖住)。舉例如下：

1、 select * from t_items where id=1 for update;

這條語句明確指定主鍵（id=1），并且有此數(shù)據(jù)（id=1的數(shù)據(jù)存在），則采用row lock。只鎖定當前這條數(shù)據(jù)。

2、 select * from t_items where id=3 for update;

這條語句明確指定主鍵，但是卻查無此數(shù)據(jù)，此時不會產(chǎn)生lock（沒有元數(shù)據(jù)，又去lock誰呢？）。

3、 select * from t_items where name='手機' for update;

這條語句沒有指定數(shù)據(jù)的主鍵，那么此時產(chǎn)生table lock，即在當前事務提交前整張數(shù)據(jù)表的所有字段將無法被查詢。

4、 select * from t_items where id0 for update; 或者 select * from t_items where id1 for update; （注：在SQL中表示不等于）

上述兩條語句的主鍵都不明確，也會產(chǎn)生table lock。

5、 select * from t_items where status=1 for update; （假設為status字段添加了索引）

這條語句明確指定了索引，并且有此數(shù)據(jù)，則產(chǎn)生row lock。

6、 select * from t_items where status=3 for update; （假設為status字段添加了索引）

這條語句明確指定索引，但是根據(jù)索引查無此數(shù)據(jù)，也就不會產(chǎn)生lock。

樂觀鎖（ Optimistic Locking ） 相對悲觀鎖而言，樂觀鎖假設認為數(shù)據(jù)一般情況下不會造成沖突，所以只會在數(shù)據(jù)進行提交更新的時候，才會正式對數(shù)據(jù)的沖突與否進行檢測，如果發(fā)現(xiàn)沖突了，則返回用戶錯誤的信息，讓用戶決定如何去做。實現(xiàn)樂觀鎖一般來說有以下2種方式：

名稱欄目：mysql樂觀鎖怎么實現(xiàn),mysql如何實現(xiàn)悲觀鎖
網(wǎng)站路徑：http://chinadenli.net/article21/dsecjcd.html

成都網(wǎng)站建設公司_創(chuàng)新互聯(lián)，為您提供App開發(fā)、企業(yè)網(wǎng)站制作、電子商務、網(wǎng)頁設計公司、營銷型網(wǎng)站建設、網(wǎng)站排名

廣告

聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源： 創(chuàng)新互聯(lián)