接上篇 事務(wù)隔離級別和幻讀 ，留了個坑，沒想到竟然過了10天，時間不注意真的過的好快。順便提下，圖片鏈接是屬于網(wǎng)站的，開發(fā)自己的圖床迫在眉睫，萬一哪天遷移就要做很多額外工作，一些概念或者思路用圖片表達(dá)更直觀清楚。
創(chuàng)新互聯(lián)專注于阿里地區(qū)企業(yè)網(wǎng)站建設(shè),成都響應(yīng)式網(wǎng)站建設(shè)公司,商城網(wǎng)站建設(shè)。阿里地區(qū)網(wǎng)站建設(shè)公司,為阿里地區(qū)等地區(qū)提供建站服務(wù)。全流程定制網(wǎng)站，專業(yè)設(shè)計(jì)，全程項(xiàng)目跟蹤，創(chuàng)新互聯(lián)專業(yè)和態(tài)度為您提供的服務(wù)
回到正題，之前提到一般情況下MySQL的InnoDB引擎在可重復(fù)讀的情況下是沒法保證不出現(xiàn)幻讀的，但實(shí)際情況是MySQL可以通過加鎖來防止幻讀的出現(xiàn)，這種鎖定通過Next-key機(jī)制來實(shí)現(xiàn)，是屬于記錄鎖和間隙鎖(Gap鎖)的結(jié)合。
引申，行級別鎖的三種算法:
舉個存在唯一索引和輔助索引的例子做說明：
執(zhí)行 select * from test where b = 3 for update
存在兩個索引，分別加鎖，唯一主鍵列a加record lock ， 輔助索引列b加next-key lock (1,3) 以及給下一個值的區(qū)間(3,6)加gap鎖；
因此在另一個事務(wù)里執(zhí)行以下語句都會阻塞，具體分析：
第一個阻塞因?yàn)榧恿宋ㄒ凰饕膔ecord lock a = 5；
第二個主鍵插入4，符合條件，但是根據(jù)輔助索引b 的范圍, b = 2 在(1,3)中，同樣阻塞；
第三個a =6 不在主鍵a鎖定范圍，b = 5 也不在輔助索引b 的范圍(1,3)中，但在另一個gap鎖范圍(3,6)中，因此也阻塞；
這種鎖定情形下，可以執(zhí)行的包括類似語句：
insert的特殊情況
對于insert 會檢查下一條記錄是否被鎖定，如上述例子有 select * from test where b = 3 for update 插入 insert into test select 2,2 會檢測到b = 3 已經(jīng)被鎖定，而 insert into test select 2,0 可以執(zhí)行；
[1]:《MySQL技術(shù)內(nèi)幕：InnoDB存儲引擎》-第六章：鎖
1、SQL語句執(zhí)行流程
MySQL大體上可分為Server層和存儲引擎層兩部分。
Server層：
連接器：TCP握手后服務(wù)器來驗(yàn)證登陸用戶身份，A用戶創(chuàng)建連接后，管理員對A用戶權(quán)限修改了也不會影響到已經(jīng)創(chuàng)建的鏈接權(quán)限，必須重新登陸。
查詢緩存：查詢后的結(jié)果存儲位置，MySQL8.0版本以后已經(jīng)取消，因?yàn)椴樵兙彺媸l繁，得不償失。
分析器：根據(jù)語法規(guī)則，判斷你輸入的這個SQL語句是否滿足MySQL語法。
優(yōu)化器：多種執(zhí)行策略可實(shí)現(xiàn)目標(biāo)，系統(tǒng)自動選擇最優(yōu)進(jìn)行執(zhí)行。
執(zhí)行器：判斷是否有權(quán)限，將最終任務(wù)提交到存儲引擎。
存儲引擎層
負(fù)責(zé)數(shù)據(jù)的存儲和提取。其架構(gòu)模式是插件式的，支持InnoDB、MyISAM、Memory等多個存儲引擎?，F(xiàn)在最常用的存儲引擎是InnoDB，它從MySQL 5.5.5版本開始成為了默認(rèn)存儲引擎(經(jīng)常用的也是這個)。
SQL執(zhí)行順序
2、BinLog、RedoLog、UndoLog
BinLog
BinLog是記錄所有數(shù)據(jù)庫表結(jié)構(gòu)變更（例如create、alter table）以及表數(shù)據(jù)修改(insert、update、delete)的二進(jìn)制日志，主從數(shù)據(jù)庫同步用到的都是BinLog文件。BinLog日志文件有三種模式。
STATEMENT 模式
內(nèi)容：binlog 記錄可能引起數(shù)據(jù)變更的 sql 語句
優(yōu)勢：該模式下，因?yàn)闆]有記錄實(shí)際的數(shù)據(jù)，所以日志量很少 IO 都消耗很低，性能是最優(yōu)的
劣勢：但有些操作并不是確定的，比如 uuid() 函數(shù)會隨機(jī)產(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)勢：可以絕對精準(zhǔn)的還原，從而保證了數(shù)據(jù)的安全與可靠，并且復(fù)制和數(shù)據(jù)恢復(fù)過程可以是并發(fā)進(jìn)行的
劣勢：缺點(diǎn)在于 binlog 體積會非常大，同時，對于修改記錄多、字段長度大的操作來說，記錄時性能消耗會很嚴(yán)重。閱讀的時候也需要特殊指令來進(jìn)行讀取數(shù)據(jù)。
MIXED 模式
內(nèi)容：是對上述STATEMENT 跟 ROW 兩種模式的混合使用。
細(xì)節(jié)：對于絕大部分操作，都是使用 STATEMENT 來進(jìn)行 binlog 沒有記錄，只有以下操作使用 ROW 來實(shí)現(xiàn)：表的存儲引擎為 NDB，使用了uuid() 等不確定函數(shù)，使用了 insert delay 語句，使用了臨時表
主從同步流程：
1、主節(jié)點(diǎn)必須啟用二進(jìn)制日志，記錄任何修改了數(shù)據(jù)庫數(shù)據(jù)的事件。
2、從節(jié)點(diǎn)開啟一個線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協(xié)議，請求主節(jié)點(diǎn)的二進(jìn)制日志文件中的事件 。
3、主節(jié)點(diǎn)啟動一個線程（dump Thread），檢查自己二進(jìn)制日志中的事件，跟對方請求的位置對比，如果不帶請求位置參數(shù)，則主節(jié)點(diǎn)就會從第一個日志文件中的第一個事件一個一個發(fā)送給從節(jié)點(diǎn)。
4、從節(jié)點(diǎn)接收到主節(jié)點(diǎn)發(fā)送過來的數(shù)據(jù)把它放置到中繼日志（Relay log）文件中。并記錄該次請求到主節(jié)點(diǎn)的具體哪一個二進(jìn)制日志文件內(nèi)部的哪一個位置（主節(jié)點(diǎn)中的二進(jìn)制文件會有多個）。
5、從節(jié)點(diǎn)啟動另外一個線程（sql Thread ），把 Relay log 中的事件讀取出來，并在本地再執(zhí)行一次。
mysql默認(rèn)的復(fù)制方式是異步的，并且復(fù)制的時候是有并行復(fù)制能力的。主庫把日志發(fā)送給從庫后不管了，這樣會產(chǎn)生一個問題就是假設(shè)主庫掛了，從庫處理失敗了，這時候從庫升為主庫后，日志就丟失了。由此產(chǎn)生兩個概念。
全同步復(fù)制
主庫寫入binlog后強(qiáng)制同步日志到從庫，所有的從庫都執(zhí)行完成后才返回給客戶端，但是很顯然這個方式的話性能會受到嚴(yán)重影響。
半同步復(fù)制
半同步復(fù)制的邏輯是這樣，從庫寫入日志成功后返回ACK確認(rèn)給主庫，主庫收到至少一個從庫的確認(rèn)就認(rèn)為寫操作完成。
還可以延伸到由于主從配置不一樣、主庫大事務(wù)、從庫壓力過大、網(wǎng)絡(luò)震蕩等造成主備延遲，如何避免這個問題？主備切換的時候用可靠性優(yōu)先原則還是可用性優(yōu)先原則？如何判斷主庫Crash了？互為主備的情況下如何避免主備循環(huán)復(fù)制？被刪庫跑路了如何正確恢復(fù)？( o )… 感覺越來越扯到DBA的活兒上去了。
RedoLog
可以先通過下面demo理解：
飯點(diǎn)記賬可以把賬單寫在賬本上也可以寫在粉板上。有人賒賬或者還賬的話，一般有兩種做法：
1、直接把賬本翻出來，把這次賒的賬加上去或者扣除掉。
2、先在粉板上記下這次的賬，等打烊以后再把賬本翻出來核算。
生意忙時選后者，因?yàn)榍罢咛闊┝?。得在密密麻麻的記錄中找到這個人的賒賬總額信息，找到之后再拿出算盤計(jì)算，最后再將結(jié)果寫回到賬本上。
同樣在MySQL中如果每一次的更新操作都需要寫進(jìn)磁盤，然后磁盤也要找到對應(yīng)的那條記錄，然后再更新，整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術(shù)，它的關(guān)鍵點(diǎn)就是先寫日志，再寫磁盤。此時賬本 = BinLog，粉板 = RedoLog。
1、 記錄更新時，InnoDB引擎就會先把記錄寫到RedoLog（粉板）里面，并更新內(nèi)存。同時，InnoDB引擎會在空閑時將這個操作記錄更新到磁盤里面。
2、 如果更新太多RedoLog處理不了的時候，需先將RedoLog部分?jǐn)?shù)據(jù)寫到磁盤，然后擦除RedoLog部分?jǐn)?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推進(jìn)一下。
有了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層實(shí)現(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ù)在進(jìn)行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é)點(diǎn)存儲的只是索引，可以存儲的更多。B+樹比B樹更加矮胖，IO次數(shù)更少。
B+ 樹葉子節(jié)點(diǎn)前后管理，更加方便范圍查詢。同時結(jié)果都在葉子節(jié)點(diǎn)，查詢效率穩(wěn)定。
B+樹中更有利于對數(shù)據(jù)掃描，可以避免B樹的回溯掃描。
索引的優(yōu)點(diǎn)：
1、唯一索引可以保證每一行數(shù)據(jù)的唯一性
2、提高查詢速度
3、加速表與表的連接
4、顯著的減少查詢中分組和排序的時間
5、通過使用索引，可以在查詢的過程中，使用優(yōu)化隱藏器，提高系統(tǒng)的性能。
索引的缺點(diǎn)：
1、創(chuàng)建跟維護(hù)都需要耗時
2、創(chuàng)建索引時，需要對表加鎖，在鎖表的同時，可能會影響到其他的數(shù)據(jù)操作
3、 索引需要磁盤的空間進(jìn)行存儲，磁盤占用也很快。
4、當(dāng)對表中的數(shù)據(jù)進(jìn)行CRUD的時，也會觸發(fā)索引的維護(hù)，而維護(hù)索引需要時間，可能會降低數(shù)據(jù)操作性能
索引設(shè)計(jì)的原則不應(yīng)該：
1、索引不是越多越好。索引太多，維護(hù)索引需要時間跟空間。
2、 頻繁更新的數(shù)據(jù)，不宜建索引。
3、數(shù)據(jù)量小的表沒必要建立索引。
應(yīng)該：
1、重復(fù)率小的列建議生成索引。因?yàn)橹貜?fù)數(shù)據(jù)少，索引樹查詢更有效率，等價基數(shù)越大越好。
2、數(shù)據(jù)具有唯一性，建議生成唯一性索引。在數(shù)據(jù)庫的層面，保證數(shù)據(jù)正確性
3、頻繁group by、order by的列建議生成索引?？梢源蠓岣叻纸M和排序效率
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)于索引的知識點(diǎn)：
主鍵索引：主鍵索引的葉子節(jié)點(diǎn)存的是整行數(shù)據(jù)信息。在InnoDB里，主鍵索引也被稱為聚簇索引（clustered index）。主鍵自增是無法保證完全自增的哦，遇到唯一鍵沖突、事務(wù)回滾等都可能導(dǎo)致不連續(xù)。
唯一索引：以唯一列生成的索引，該列不允許有重復(fù)值，但允許有空值(NULL)
普通索引跟唯一索引查詢性能：InnoDB的數(shù)據(jù)是按數(shù)據(jù)頁為單位來讀寫的，默認(rèn)每頁16KB，因此這兩種索引查詢數(shù)據(jù)性能差別微乎其微。
change buffer：普通索引用在更新過程的加速，更新的字段如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數(shù)據(jù)讀入內(nèi)存來確保不違背唯一性，所以盡量用普通索引。
非主鍵索引：非主鍵索引的葉子節(jié)點(diǎn)內(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三個索引。另外組合索引實(shí)際還是一個索引，并非真的創(chuàng)建了多個索引，只是產(chǎn)生的效果等價于產(chǎn)生多個索引。
索引下推：MySQL 5.6引入了索引下推優(yōu)化，可以在索引遍歷過程中，對索引中包含的字段先做判斷，過濾掉不符合條件的記錄，減少回表字?jǐn)?shù)。
索引維護(hù)：B+樹為了維護(hù)索引有序性涉及到頁分裂跟頁合并。增刪數(shù)據(jù)時需考慮頁空間利用率。
自增主鍵：一般會建立與業(yè)務(wù)無關(guān)的自增主鍵，不會觸發(fā)葉子節(jié)點(diǎn)分裂。
延遲關(guān)聯(lián)：通過使用覆蓋索引查詢返回需要的主鍵，再根據(jù)主鍵關(guān)聯(lián)原表獲得需要的數(shù)據(jù)。
InnoDB存儲: * .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫表是一張?jiān)趺礃拥谋怼?.ibd文件則是該表的索引，數(shù)據(jù)存儲文件，既該表的所有索引樹，所有行記錄數(shù)據(jù)都存儲在該文件中。
MyISAM存儲：* .frm文件是一份定義文件，也就是定義數(shù)據(jù)庫表是一張?jiān)趺礃拥谋怼? .MYD文件是MyISAM存儲引擎表的所有行數(shù)據(jù)的文件。* .MYI文件存放的是MyISAM存儲引擎表的索引相關(guān)數(shù)據(jù)的文件。MyISAM引擎下，表數(shù)據(jù)和表索引數(shù)據(jù)是分開存儲的。
MyISAM查詢：在MyISAM下，主鍵索引和輔助鍵索引都屬于非聚簇索引。查詢不管是走主鍵索引，還是非主鍵索引，在葉子結(jié)點(diǎn)得到的都是目的數(shù)據(jù)的地址，還需要通過該地址，才能在數(shù)據(jù)文件中找到目的數(shù)據(jù)。
PS：InnoDB支持聚簇索引，MyISAM不支持聚簇索引
4、SQL事務(wù)隔離級別
ACID的四個特性
原子性（Atomicity）：把多個操作放到一個事務(wù)中，保證這些操作要么都成功，要么都不成功
一致性（Consistency）：理解成一串對數(shù)據(jù)進(jìn)行操作的程序執(zhí)行下來，不會對數(shù)據(jù)產(chǎn)生不好的影響，比如憑空產(chǎn)生，或消失
隔離性（Isolation，又稱獨(dú)立性）：隔離性的意思就是多個事務(wù)之間互相不干擾，即使是并發(fā)事務(wù)的情況下，他們只是兩個并發(fā)執(zhí)行沒有交集，互不影響的東西；當(dāng)然實(shí)現(xiàn)中，也不一定需要這么完整隔離性，即不一定需要這么的互不干擾，有時候還是允許有部分干擾的。所以MySQL可以支持4種事務(wù)隔離性
持久性（Durability）：當(dāng)某個操作操作完畢了，那么結(jié)果就是這樣了，并且這個操作會持久化到日志記錄中
PS：ACID中C與CAP定理中C的區(qū)別
ACID的C著重強(qiáng)調(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ù)讀的重點(diǎn)是修改: 同樣的條件, 你讀取過的數(shù)據(jù), 再次讀取出來發(fā)現(xiàn)值不一樣了，只需要鎖住滿足條件的記錄
3、 幻讀(phantom read)：事務(wù)A先修改了某個表的所有紀(jì)錄的狀態(tài)字段為已處理，未提交；事務(wù)B也在此時新增了一條未處理的記錄，并提交了；事務(wù)A隨后查詢記錄，卻發(fā)現(xiàn)有一條記錄是未處理的造成幻讀現(xiàn)象，幻讀僅專指新插入的行?；米x會造成語義上的問題跟數(shù)據(jù)一致性問題。
4、 在可重復(fù)讀RR隔離級別下，普通查詢是快照讀，是不會看到別的事務(wù)插入的數(shù)據(jù)的。因此，幻讀在當(dāng)前讀下才會出現(xiàn)。要用間隙鎖解決此問題。
在說隔離級別之前，你首先要知道，你隔離得越嚴(yán)實(shí)，效率就會越低。因此很多時候，我們都要在二者之間尋找一個平衡點(diǎn)。SQL標(biāo)準(zhǔn)的事務(wù)隔離級別由低到高如下： 上圖從上到下的模式會導(dǎo)致系統(tǒng)的并行性能依次降低，安全性依次提高。
讀未提交：別人改數(shù)據(jù)的事務(wù)尚未提交，我在我的事務(wù)中也能讀到。
讀已提交(Oracle默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中才能讀到。
可重復(fù)讀(MySQL默認(rèn))：別人改數(shù)據(jù)的事務(wù)已經(jīng)提交，我在我的事務(wù)中也不去讀，以此保證重復(fù)讀一致性。
串行：我的事務(wù)尚未提交，別人就別想改數(shù)據(jù)。
標(biāo)準(zhǔn)跟實(shí)現(xiàn)：上面都是關(guān)于事務(wù)的標(biāo)準(zhǔn)，但是每一種數(shù)據(jù)庫都有不同的實(shí)現(xiàn)，比如MySQL InnDB 默認(rèn)為RR級別，但是不會出現(xiàn)幻讀。因?yàn)楫?dāng)事務(wù)A更新了所有記錄的某個字段，此時事務(wù)A會獲得對這個表的表鎖，因?yàn)槭聞?wù)A還沒有提交，所以事務(wù)A獲得的鎖沒有釋放，此時事務(wù)B在該表插入新記錄，會因?yàn)闊o法獲得該表的鎖，則導(dǎo)致插入操作被阻塞。只有事務(wù)A提交了事務(wù)后，釋放了鎖，事務(wù)B才能進(jìn)行接下去的操作。所以可以說 MySQL的RR級別的隔離是已經(jīng)實(shí)現(xiàn)解決了臟讀，不可重復(fù)讀和幻讀的。
5、MySQL中的鎖
無論是Java的并發(fā)編程還是數(shù)據(jù)庫的并發(fā)操作都會涉及到鎖，研發(fā)人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設(shè)計(jì)思想。
悲觀鎖：
優(yōu)點(diǎn)：適合在寫多讀少的并發(fā)環(huán)境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數(shù)據(jù)的安全性
缺點(diǎn)：加鎖會增加系統(tǒng)開銷，雖然能保證數(shù)據(jù)的安全，但數(shù)據(jù)處理吞吐量低，不適合在讀書寫少的場合下使用
樂觀鎖：
優(yōu)點(diǎn)：在讀多寫少的并發(fā)場景下，可以避免數(shù)據(jù)庫加鎖的開銷，提高DAO層的響應(yīng)性能，很多情況下ORM工具都有帶有樂觀鎖的實(shí)現(xiàn)，所以這些方法不一定需要我們?nèi)藶榈娜?shí)現(xiàn)。
缺點(diǎn)：在寫多讀少的并發(fā)場景下，即在寫操作競爭激烈的情況下，會導(dǎo)致CAS多次重試，沖突頻率過高，導(dǎo)致開銷比悲觀鎖更高。
實(shí)現(xiàn)：數(shù)據(jù)庫層面的樂觀鎖其實(shí)跟CAS思想類似， 通數(shù)據(jù)版本號或者時間戳也可以實(shí)現(xiàn)。
數(shù)據(jù)庫并發(fā)場景主要有三種：
讀-讀：不存在任何問題，也不需要并發(fā)控制
讀-寫：有隔離性問題，可能遇到臟讀，幻讀，不可重復(fù)讀
寫-寫：可能存更新丟失問題，比如第一類更新丟失，第二類更新丟失
兩類更新丟失問題：
第一類更新丟失：事務(wù)A的事務(wù)回滾覆蓋了事務(wù)B已提交的結(jié)果 第二類更新丟失：事務(wù)A的提交覆蓋了事務(wù)B已提交的結(jié)果
為了合理貫徹落實(shí)鎖的思想，MySQL中引入了雜七雜八的各種鎖：
鎖分類
MySQL支持三種層級的鎖定，分別為
表級鎖定
MySQL中鎖定粒度最大的一種鎖，最常使用的MYISAM與INNODB都支持表級鎖定。
頁級鎖定
是MySQL中鎖定粒度介于行級鎖和表級鎖中間的一種鎖，表級鎖速度快，但沖突多，行級沖突少，但速度慢。所以取了折衷的頁級，一次鎖定相鄰的一組記錄。
行級鎖定
Mysql中鎖定粒度最細(xì)的一種鎖，表示只針對當(dāng)前操作的行進(jìn)行加鎖。行級鎖能大大減少數(shù)據(jù)庫操作的沖突。其加鎖粒度最小，但加鎖的開銷也最大行級鎖不一定比表級鎖要好：鎖的粒度越細(xì)，代價越高，相比表級鎖在表的頭部直接加鎖，行級鎖還要掃描找到對應(yīng)的行對其上鎖，這樣的代價其實(shí)是比較高的，所以表鎖和行鎖各有所長。
MyISAM中的鎖
雖然MySQL支持表，頁，行三級鎖定，但MyISAM存儲引擎只支持表鎖。所以MyISAM的加鎖相對比較開銷低，但數(shù)據(jù)操作的并發(fā)性能相對就不高。但如果寫操作都是尾插入，那還是可以支持一定程度的讀寫并發(fā)
從MyISAM所支持的鎖中也可以看出，MyISAM是一個支持讀讀并發(fā)，但不支持通用讀寫并發(fā)，寫寫并發(fā)的數(shù)據(jù)庫引擎，所以它更適合用于讀多寫少的應(yīng)用場合，一般工程中也用的較少。
InnoDB中的鎖
該模式下支持的鎖實(shí)在是太多了，具體如下：
共享鎖和排他鎖 (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ù)，因?yàn)橛锌赡芷渌氖聞?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ù)式之后皆為邏輯，一切皆為需求和實(shí)現(xiàn)。希望此文能從需求、現(xiàn)狀和解決方式的角度幫大家理解隔離級別。
隔離級別的產(chǎn)生
在串型執(zhí)行的條件下，數(shù)據(jù)修改的順序是固定的、可預(yù)期的結(jié)果，但是并發(fā)執(zhí)行的情況下，數(shù)據(jù)的修改是不可預(yù)期的，也不固定，為了實(shí)現(xiàn)數(shù)據(jù)修改在并發(fā)執(zhí)行的情況下得到一個固定、可預(yù)期的結(jié)果，由此產(chǎn)生了隔離級別。
所以隔離級別的作用是用來平衡數(shù)據(jù)庫并發(fā)訪問與數(shù)據(jù)一致性的方法。
事務(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ù)的值，也可能是間隔的值組成）
1. 數(shù)據(jù)已經(jīng)填充了整個數(shù)據(jù)范圍：（被完全填充的數(shù)據(jù)范圍，不存在數(shù)據(jù)間隙）
整形，對值具有唯一約束條件的數(shù)據(jù)范圍 1～5 ，
已有數(shù)據(jù)1、2、3、4、5，此時數(shù)據(jù)范圍已被完全填充；
整形，對值具有唯一約束條件的數(shù)據(jù)范圍 1 和 5 ，
已有數(shù)據(jù)1、5，此時數(shù)據(jù)范圍已被完全填充；
2. 數(shù)據(jù)填充了部分?jǐn)?shù)據(jù)范圍：（未被完全填充的數(shù)據(jù)范圍，是存在數(shù)據(jù)間隙）
整形的數(shù)據(jù)范圍 1～5 ，
已有數(shù)據(jù) 1、2、3、4、5，但是因?yàn)闆]有唯一約束，
所以數(shù)據(jù)范圍可以繼續(xù)被 1～5 的數(shù)據(jù)重復(fù)填充；
整形，具有唯一約束條件的數(shù)據(jù)范圍 1～5 ，
已有數(shù)據(jù) 2，5，此時數(shù)據(jù)范圍未被完全填充，還可以填充 1、3、4 ；
3. 數(shù)據(jù)范圍內(nèi)沒有任何數(shù)據(jù)（存在間隙）
如下：
整形的數(shù)據(jù)范圍 1～5 ，數(shù)據(jù)范圍內(nèi)當(dāng)前沒有任何數(shù)據(jù)。
在了解了數(shù)據(jù)全集的組成后，我們再來看看事務(wù)并發(fā)時，會帶來的問題。
無控制的并發(fā)所帶來的問題
并發(fā)事務(wù)如果不加以控制的話會帶來一些問題，主要包括以下幾種情況。
1. 范圍內(nèi)已有數(shù)據(jù)更改導(dǎo)致的：
更新丟失：當(dāng)多個事務(wù)選擇了同一行，然后基于最初選定的值更新該行時，
由于每個事物不知道其他事務(wù)的存在，最后的更新就會覆蓋其他事務(wù)所做的更新；
臟讀： 一個事務(wù)正在對一條記錄做修改，這個事務(wù)完成并提交前，這條記錄就處于不一致狀態(tài)。
這時，另外一個事務(wù)也來讀取同一條記錄，如果不加控制，
第二個事務(wù)讀取了這些“臟”數(shù)據(jù)，并據(jù)此做了進(jìn)一步的處理，就會產(chǎn)生提交的數(shù)據(jù)依賴關(guān)系。
這種現(xiàn)象就叫“臟讀”。
2. 范圍內(nèi)數(shù)據(jù)量發(fā)生了變化導(dǎo)致：
不可重復(fù)讀：一個事務(wù)在讀取某些數(shù)據(jù)后的某個時間，再次讀取以前讀過的數(shù)據(jù)，
卻發(fā)現(xiàn)其讀出的數(shù)據(jù)已經(jīng)發(fā)生了改變，或者某些記錄已經(jīng)被刪除了。
這種現(xiàn)象就叫“不可重復(fù)讀”。
幻讀：一個事務(wù)按相同的查詢條件重新讀取以前檢索過的數(shù)據(jù)，
卻發(fā)現(xiàn)其他事務(wù)插入了滿足其查詢條件的新數(shù)據(jù)，這種現(xiàn)象稱為“幻讀”。
可以簡單的認(rèn)為滿足條件的數(shù)據(jù)量變化了。
因?yàn)闊o控制的并發(fā)會帶來一系列的問題，這些問題會導(dǎo)致無法滿足我們所需要的結(jié)果。因此我們需要控制并發(fā)，以實(shí)現(xiàn)我們所期望的結(jié)果(隔離級別)。
MySQL 隔離級別的實(shí)現(xiàn)
InnoDB 通過加鎖的策略來支持這些隔離級別。
行鎖包含：
Record Locks
索引記錄鎖，索引記錄鎖始終鎖定索引記錄，即使表中未定義索引，
這種情況下，InnoDB 創(chuàng)建一個隱藏的聚簇索引，并使用該索引進(jìn)行記錄鎖定。
Gap Locks
間隙鎖是索引記錄之間的間隙上的鎖，或者對第一條記錄之前或者最后一條記錄之后的鎖。
間隙鎖是性能和并發(fā)之間權(quán)衡的一部分。
對于無間隙的數(shù)據(jù)范圍不需要間隙鎖，因?yàn)闆]有間隙。
Next-Key Locks
索引記錄上的記錄鎖和索引記錄之前的 gap lock 的組合。
假設(shè)索引包含 10、11、13 和 20。
可能的next-key locks包括以下間隔，其中圓括號表示不包含間隔端點(diǎn)，方括號表示包含端點(diǎn)：
(負(fù)無窮大, 10] ? ?(10, 11] ? ?(11, 13] ? ?(13, 20] ? ?(20, 正無窮大) ? ? ? ?對于最后一個間隔，next-key將會鎖定索引中最大值的上方，
左右滑動進(jìn)行查看
"上確界"偽記錄的值高于索引中任何實(shí)際值。
上確界不是一個真正的索引記錄，因此，實(shí)際上，這個 next-key 只鎖定最大索引值之后的間隙。
基于此，當(dāng)獲取的數(shù)據(jù)范圍中，數(shù)據(jù)已填充了所有的數(shù)據(jù)范圍，那么此時是不存在間隙的，也就不需要 gap lock。
對于數(shù)據(jù)范圍內(nèi)存在間隙的，需要根據(jù)隔離級別確認(rèn)是否對間隙加鎖。
默認(rèn)的 REPEATABLE READ 隔離級別，為了保證可重復(fù)讀，除了對數(shù)據(jù)本身加鎖以外，還需要對數(shù)據(jù)間隙加鎖。
READ COMMITTED 已提交讀，不匹配行的記錄鎖在 MySQL 評估了 where 條件后釋放。
對于 update 語句，InnoDB 執(zhí)行 "semi-consistent" 讀取，這樣它會將最新提交的版本返回到 MySQL，
以便 MySQL 可以確定該行是否與 update 的 where 條件相匹配。
總結(jié)延展：
唯一索引存在唯一約束，所以變更后的數(shù)據(jù)若違反了唯一約束的原則，則會失敗。
當(dāng) where 條件使用二級索引篩選數(shù)據(jù)時，會對二級索引命中的條目和對應(yīng)的聚簇索引都加鎖；所以其他事務(wù)變更命中加鎖的聚簇索引時，都會等待鎖。
行鎖的增加是一行一行增加的，所以可能導(dǎo)致并發(fā)情況下死鎖的發(fā)生。
例如，
在 session A 對符合條件的某聚簇索引加鎖時，可能 session B 已持有該聚簇索引的 Record Locks，而 session B 正在等待 session A 已持有的某聚簇索引的 Record Locks。
session A 和 session B 是通過兩個不相干的二級索引定位到的聚簇索引。
session A 通過索引 idA，session B通過索引 idB 。
當(dāng) where 條件獲取的數(shù)據(jù)無間隙時，無論隔離級別為 rc 或 rr，都不會存在間隙鎖。
比如通過唯一索引獲取到了已完全填充的數(shù)據(jù)范圍，此時不需要間隙鎖。
間隙鎖的目的在于阻止數(shù)據(jù)插入間隙，所以無論是通過 insert 或 update 變更導(dǎo)致的間隙內(nèi)數(shù)據(jù)的存在，都會被阻止。
rc 隔離級別模式下，查詢和索引掃描將禁用 gap locking，此時 gap locking 僅用于外鍵約束檢查和重復(fù)鍵檢查（主要是唯一性檢查）。
rr 模式下，為了防止幻讀，會加上 Gap Locks。
事務(wù)中，SQL 開始則加鎖，事務(wù)結(jié)束才釋放鎖。
就鎖類型而言，應(yīng)該有優(yōu)化鎖，鎖升級等，例如rr模式未使用索引查詢的情況下，是否可以直接升級為表鎖。
就鎖的應(yīng)用場景而言，在回放場景中，如果確定事務(wù)可并發(fā)，則可以考慮不加鎖，加快回放速度。
鎖只是并發(fā)控制的一種粒度，只是一個很小的部分：
從不同場景下是否需要控制并發(fā)，(已知無交集且有序的數(shù)據(jù)的變更，MySQL 的 MTS 相同前置事務(wù)的多事務(wù)并發(fā)回放)
并發(fā)控制的粒度，(鎖是一種邏輯粒度，可能還存在物理層和其他邏輯粒度或方式)
相同粒度下的優(yōu)化，(鎖本身存在優(yōu)化，如IX、IS類型的優(yōu)化鎖)
粒度加載的安全性能(如獲取行鎖前，先獲取頁鎖，頁鎖在執(zhí)行獲取行鎖操作后即釋放，無論是否獲取成功)等多個層次去思考并發(fā)這玩意。
mysql 為并發(fā)事務(wù)同時對一條記錄進(jìn)行讀寫時，提出了兩種解決方案：
1）使用 mvcc 的方法，實(shí)現(xiàn)多事務(wù)的并發(fā)讀寫，但是這種讀只是“快照讀”，一般讀的是歷史版本數(shù)據(jù)，還有一種是“當(dāng)前讀”，一般加鎖實(shí)現(xiàn)“當(dāng)前讀”，或者 insert、update、delete 也是當(dāng)前讀。
2）使用加鎖的方法，鎖分為共享鎖（讀鎖），排他鎖（寫鎖）
快照讀：就是select
當(dāng)前讀：特殊的讀操作，插入/更新/刪除操作，屬于當(dāng)前讀，處理的都是當(dāng)前的數(shù)據(jù)，需要加鎖。
mysql 在 RR 級別怎么處理幻讀的呢？一般來說，RR 級別通過 mvcc 機(jī)制，保證讀到低于后面事務(wù)的數(shù)據(jù)。但是 select for update 不會觸發(fā) mvcc，它是當(dāng)前讀。如果后面事務(wù)插入數(shù)據(jù)并提交，那么在 RR 級別就會讀到插入的數(shù)據(jù)。所以，mysql 使用 行鎖 + gap 鎖（簡稱 next-key 鎖）來防止當(dāng)前讀的時候插入。
Gap Lock在InnoDB的唯一作用就是防止其他事務(wù)的插入操作，以此防止幻讀的發(fā)生。
Innodb自動使用間隙鎖的條件：
加鎖情況與死鎖原因分析
為方便大家復(fù)現(xiàn)，完整表結(jié)構(gòu)和數(shù)據(jù)如下：
CREATE TABLE `t3` (
`c1` int(11) NOT NULL AUTO_INCREMENT,
`c2` int(11) DEFAULT NULL,
PRIMARY KEY (`c1`),
UNIQUE KEY `c2` (`c2`)
) ENGINE=InnoDB
insert into t3 values(1,1),(15,15),(20,20);
在 session1 執(zhí)行 commit 的瞬間，我們會看到 session2、session3 的其中一個報死鎖。這個死鎖是這樣產(chǎn)生的：
1.?session1 執(zhí)行 delete ?會在唯一索引 c2 的 c2 = 15 這一記錄上加 X lock（也就是在MySQL 內(nèi)部觀測到的：X Lock but not gap）；
2.?session2 和 session3 在執(zhí)行 insert 的時候，由于唯一約束檢測發(fā)生唯一沖突，會加 S Next-Key Lock，即對 (1,15] 這個區(qū)間加鎖包括間隙，并且被 seesion1 的 X Lock 阻塞，進(jìn)入等待；
3.?session1 在執(zhí)行 commit 后，會釋放 X Lock，session2 和 session3 都獲得 S Next-Key Lock；
4.?session2 和 session3 繼續(xù)執(zhí)行插入操作，這個時候 INSERT INTENTION LOCK（插入意向鎖）出現(xiàn)了，并且由于插入意向鎖會被 gap 鎖阻塞，所以 session2 和 session3 互相等待，造成死鎖。
死鎖日志如下：
請點(diǎn)擊輸入圖片描述
INSERT INTENTION LOCK
在之前的死鎖分析第四點(diǎn)，如果不分析插入意向鎖，也是會造成死鎖的，因?yàn)椴迦胱罱K還是要對記錄加 X Lock 的，session2 和 session3 還是會互相阻塞互相等待。
但是插入意向鎖是客觀存在的，我們可以在官方手冊中查到，不可忽略：
Prior to inserting the row, a type of gap lock called an insert intention gap lock is set. This lock signals the intent to insert in such a way that multiple transactions inserting into the same index gap need not wait for each other if they are not inserting at the same position within the gap.
插入意向鎖其實(shí)是一種特殊的 gap lock，但是它不會阻塞其他鎖。假設(shè)存在值為 4 和 7 的索引記錄，嘗試插入值 5 和 6 的兩個事務(wù)在獲取插入行上的排它鎖之前使用插入意向鎖鎖定間隙，即在（4，7）上加 gap lock，但是這兩個事務(wù)不會互相沖突等待。
當(dāng)插入一條記錄時，會去檢查當(dāng)前插入位置的下一條記錄上是否存在鎖對象，如果下一條記錄上存在鎖對象，就需要判斷該鎖對象是否鎖住了 gap。如果 gap 被鎖住了，則插入意向鎖與之沖突，進(jìn)入等待狀態(tài)（插入意向鎖之間并不互斥）?？偨Y(jié)一下這把鎖的屬性：
1. 它不會阻塞其他任何鎖；
2. 它本身僅會被 gap lock 阻塞。
在學(xué)習(xí) MySQL 過程中，一般只有在它被阻塞的時候才能觀察到，所以這也是它常常被忽略的原因吧...
GAP LOCK
在此例中，另外一個重要的點(diǎn)就是 gap lock，通常情況下我們說到 gap lock 都只會聯(lián)想到 REPEATABLE-READ 隔離級別利用其解決幻讀。但實(shí)際上在 READ-COMMITTED 隔離級別，也會存在 gap lock ，只發(fā)生在：唯一約束檢查到有唯一沖突的時候，會加 S Next-key Lock，即對記錄以及與和上一條記錄之間的間隙加共享鎖。
通過下面這個例子就能驗(yàn)證：
請點(diǎn)擊輸入圖片描述
這里 session1 插入數(shù)據(jù)遇到唯一沖突，雖然報錯，但是對 (15,20] 加的 S Next-Key Lock 并不會馬上釋放，所以 session2 被阻塞。另外一種情況就是本文開始的例子，當(dāng) session2 插入遇到唯一沖突但是因?yàn)楸?X Lock 阻塞，并不會立刻報錯 “Duplicate key”，但是依然要等待獲取 S Next-Key Lock 。
有個困惑很久的疑問：出現(xiàn)唯一沖突需要加 S Next-Key Lock 是事實(shí)，但是加鎖的意義是什么？還是說是通過 S Next-Key Lock 來實(shí)現(xiàn)的唯一約束檢查，但是這樣意味著在插入沒有遇到唯一沖突的時候，這個鎖會立刻釋放，這不符合二階段鎖原則。這點(diǎn)希望能與大家一起討論得到好的解釋。
如果是在 REPEATABLE-READ，除以上所說的唯一約束沖突外，gap lock 的存在是這樣的：
普通索引（非唯一索引）的S/X Lock，都帶 gap 屬性，會鎖住記錄以及前1條記錄到后1條記錄的左閉右開區(qū)間，比如有[4,6,8]記錄，delete 6，則會鎖住[4,8）整個區(qū)間。
對于 gap lock，相信 DBA 們的心情是一樣一樣的，所以我的建議是：
1. 在絕大部分的業(yè)務(wù)場景下，都可以把 MySQL 的隔離界別設(shè)置為 READ-COMMITTED；
2. 在業(yè)務(wù)方便控制字段值唯一的情況下，盡量減少表中唯一索引的數(shù)量。
鎖沖突矩陣
前面我們說的 GAP LOCK 其實(shí)是鎖的屬性，另外我們知道 InnoDB 常規(guī)鎖模式有：S 和 X，即共享鎖和排他鎖。鎖模式和鎖屬性是可以隨意組合的，組合之后的沖突矩陣如下，這對我們分析死鎖很有幫助：
請點(diǎn)擊輸入圖片描述
重慶分公司
重慶分公司
