mysql資料庫的隔離性（mysql資料庫隔離機制）

本文目錄一覽：

• 1、mysql默認事務隔離級別
• 2、mysql 核心內容-上
• 3、mysql 隔離級別設置為什麼比較好
• 4、MySQL 事務的默認隔離級別是什麼？可以解決幻讀問題么？
• 5、MySQL的默認事務隔離級別是?

mysql默認事務隔離級別

SQL標準中支持4種事務隔離級別，READ_UNCOMMITTED（讀未提交），READ_COMMITTED（讀已提交），REPEATABLE_READ（可重複讀），SERIALIZABLE（串列讀），MySQL innodb引擎支持全部這4種事務隔離級別。

工具／原料：

聯想Y7000P

Windows10

MySQL6.0

1、啟動命令行窗口，連接MySQL資料庫

圖示，通過MySQL提供的客戶端命令mysql連接MySQL資料庫。

2、通過系統變數查詢資料庫當前事務隔離級別

圖示，通過查詢資料庫提供的系統變數 tx_isolation 或 transaction_isolation 的值即可獲取當前的事務隔離級別。MySQL資料庫默認的事務隔離級別是REPEATABLE_READ (可重複讀）。

3、設置本次會話的事務隔離級別

圖示，通過命令set session transaction isolation level可以設置本次會話的事務隔離級別，該設置不會影響其他會話，並且設置會隨著當前會話的結束而結束。

4、設置全局會話的事務隔離級別

圖示，通過命令set global transaction isolation level可以設置全局會話的事務隔離級別，該設置不會影響當前已經連接的會話，設置完畢後，新打開的會話，將使用新設置的事務隔離級別。

5、設置一次操作的事務隔離級別

圖示，通過命令set transaction isolation level可設置下一次事務操作的隔離級別，該設置會隨著下一次事務的提交而失效。

mysql 核心內容-上

1、SQL語句執行流程

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

Server層：

連接器：TCP握手後伺服器來驗證登陸用戶身份，A用戶創建連接後，管理員對A用戶許可權修改了也不會影響到已經創建的鏈接許可權，必須重新登陸。

查詢緩存：查詢後的結果存儲位置，MySQL8.0版本以後已經取消，因為查詢緩存失效太頻繁，得不償失。

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

優化器：多種執行策略可實現目標，系統自動選擇最優進行執行。

執行器：判斷是否有許可權，將最終任務提交到存儲引擎。

存儲引擎層

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

SQL執行順序

2、BinLog、RedoLog、UndoLog

BinLog

BinLog是記錄所有資料庫表結構變更（例如create、alter table）以及表數據修改(insert、update、delete)的二進位日誌，主從資料庫同步用到的都是BinLog文件。BinLog日誌文件有三種模式。

STATEMENT 模式

內容：binlog 記錄可能引起數據變更的 sql 語句

優勢：該模式下，因為沒有記錄實際的數據，所以日誌量很少 IO 都消耗很低，性能是最優的

劣勢：但有些操作並不是確定的，比如 uuid() 函數會隨機產生唯一標識，當依賴 binlog 回放時，該操作生成的數據與原數據必然是不同的，此時可能造成無法預料的後果。

ROW 模式

內容：在該模式下，binlog 會記錄每次操作的源數據與修改後的目標數據，StreamSets就要求該模式。

優勢：可以絕對精準的還原，從而保證了數據的安全與可靠，並且複製和數據恢復過程可以是並發進行的

劣勢：缺點在於 binlog 體積會非常大，同時，對於修改記錄多、欄位長度大的操作來說，記錄時性能消耗會很嚴重。閱讀的時候也需要特殊指令來進行讀取數據。

MIXED 模式

內容：是對上述STATEMENT 跟 ROW 兩種模式的混合使用。

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

主從同步流程：

1、主節點必須啟用二進位日誌，記錄任何修改了資料庫數據的事件。

2、從節點開啟一個線程（I/O Thread)把自己扮演成 mysql 的客戶端，通過 mysql 協議，請求主節點的二進位日誌文件中的事件 。

3、主節點啟動一個線程（dump Thread），檢查自己二進位日誌中的事件，跟對方請求的位置對比，如果不帶請求位置參數，則主節點就會從第一個日誌文件中的第一個事件一個一個發送給從節點。

4、從節點接收到主節點發送過來的數據把它放置到中繼日誌（Relay log）文件中。並記錄該次請求到主節點的具體哪一個二進位日誌文件內部的哪一個位置（主節點中的二進位文件會有多個）。

5、從節點啟動另外一個線程（sql Thread ），把 Relay log 中的事件讀取出來，並在本地再執行一次。

mysql默認的複製方式是非同步的，並且複製的時候是有並行複製能力的。主庫把日誌發送給從庫後不管了，這樣會產生一個問題就是假設主庫掛了，從庫處理失敗了，這時候從庫升為主庫後，日誌就丟失了。由此產生兩個概念。

全同步複製

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

半同步複製

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

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

RedoLog

可以先通過下面demo理解：

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

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

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

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

同樣在MySQL中如果每一次的更新操作都需要寫進磁碟，然後磁碟也要找到對應的那條記錄，然後再更新，整個過程IO成本、查找成本都很高。而粉板和賬本配合的整個過程就是MySQL用到的是Write-Ahead Logging 技術，它的關鍵點就是先寫日誌，再寫磁碟。此時賬本 = BinLog，粉板 = RedoLog。

1、 記錄更新時，InnoDB引擎就會先把記錄寫到RedoLog（粉板）裡面，並更新內存。同時，InnoDB引擎會在空閑時將這個操作記錄更新到磁碟裡面。

2、 如果更新太多RedoLog處理不了的時候，需先將RedoLog部分數據寫到磁碟，然後擦除RedoLog部分數據。RedoLog類似轉盤。

RedoLog有write pos 跟checkpoint

write pos ：是當前記錄的位置，一邊寫一邊後移，寫到第3號文件末尾後就回到0號文件開頭。

check point：是當前要擦除的位置，也是往後推移並且循環的，擦除記錄前要把記錄更新到數據文件。

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

有了redo log，InnoDB就可以保證即使資料庫發生異常重啟，之前提交的記錄都不會丟失，這個能力稱為crash-safe。 redolog兩階段提交：為了讓binlog跟redolog兩份日誌之間的邏輯一致。提交流程大致如下：

1 prepare階段 — 2 寫binlog — 3 commit

當在2之前崩潰時，重啟恢復後發現沒有commit，回滾。備份恢復：沒有binlog 。一致

當在3之前崩潰時，重啟恢複發現雖沒有commit，但滿足prepare和binlog完整，所以重啟後會自動commit。備份：有binlog. 一致

binlog跟redolog區別：

redo log是InnoDB引擎特有的；binlog是MySQL的Server層實現的，所有引擎都可以使用。

redo log是物理日誌，記錄的是在某個數據頁上做了什麼修改；binlog是邏輯日誌，記錄的是這個語句的原始邏輯，比如給ID=2這一行的c欄位加1。

redo log是循環寫的，空間固定會用完；binlog是可以追加寫入的。追加寫是指binlog文件寫到一定大小後會切換到下一個，並不會覆蓋以前的日誌。

UndoLog

UndoLog 一般是邏輯日誌，主要分為兩種：

insert undo log

代表事務在insert新記錄時產生的undo log, 只在事務回滾時需要，並且在事務提交後可以被立即丟棄

update undo log

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

3、MySQL中的索引

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

哈希表：一種以KV存儲數據的結構，只適合等值查詢，不適合範圍查詢。

有序數組：只適用於靜態存儲引擎，涉及到插入的時候比較麻煩。可以參考Java中的ArrayList。

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

B+樹比B樹優勢在於：

B+ 樹非葉子節點存儲的只是索引，可以存儲的更多。B+樹比B樹更加矮胖，IO次數更少。

B+ 樹葉子節點前後管理，更加方便範圍查詢。同時結果都在葉子節點，查詢效率穩定。

B+樹中更有利於對數據掃描，可以避免B樹的回溯掃描。

索引的優點：

1、唯一索引可以保證每一行數據的唯一性

2、提高查詢速度

3、加速表與表的連接

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

5、通過使用索引，可以在查詢的過程中，使用優化隱藏器，提高系統的性能。

索引的缺點：

1、創建跟維護都需要耗時

2、創建索引時，需要對錶加鎖，在鎖表的同時，可能會影響到其他的數據操作

3、 索引需要磁碟的空間進行存儲，磁碟佔用也很快。

4、當對表中的數據進行CRUD的時，也會觸發索引的維護，而維護索引需要時間，可能會降低數據操作性能

索引設計的原則不應該：

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

2、 頻繁更新的數據，不宜建索引。

3、數據量小的表沒必要建立索引。

應該：

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

2、數據具有唯一性，建議生成唯一性索引。在資料庫的層面，保證數據正確性

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

4、經常用於查詢條件的欄位建議生成索引。通過索引查詢，速度更快

索引失效的場景

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

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

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

關於索引的知識點：

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

唯一索引：以唯一列生成的索引，該列不允許有重複值，但允許有空值(NULL)

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

change buffer：普通索引用在更新過程的加速，更新的欄位如果在緩存中，如果是普通索引則直接更新即可。如果是唯一索引需要將所有數據讀入內存來確保不違背唯一性，所以盡量用普通索引。

非主鍵索引：非主鍵索引的葉子節點內容是主鍵的值。在InnoDB里，非主鍵索引也被稱為二級索引（secondary index）

回表：先通過資料庫索引掃描出數據所在的行，再通過行主鍵id取出索引中未提供的數據，即基於非主鍵索引的查詢需要多掃描一棵索引樹。

覆蓋索引：如果一個索引包含（或者說覆蓋）所有需要查詢的欄位的值，我們就稱之為覆蓋索引。

聯合索引：相對單列索引，組合索引是用多個列組合構建的索引，一次性最多聯合16個。

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

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

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

自增主鍵：一般會建立與業務無關的自增主鍵，不會觸發葉子節點分裂。

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

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

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

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

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

4、SQL事務隔離級別

ACID的四個特性

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

一致性（Consistency）：理解成一串對數據進行操作的程序執行下來，不會對數據產生不好的影響，比如憑空產生，或消失

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

持久性（Durability）：當某個操作操作完畢了，那麼結果就是這樣了，並且這個操作會持久化到日誌記錄中

PS：ACID中C與CAP定理中C的區別

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

事務操作可能會出現的數據問題

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

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

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

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

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

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

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

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

串列：我的事務尚未提交，別人就別想改數據。

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

5、MySQL中的鎖

無論是Java的並發編程還是資料庫的並發操作都會涉及到鎖，研發人員引入了悲觀鎖跟樂觀鎖這樣一種鎖的設計思想。

悲觀鎖：

優點：適合在寫多讀少的並發環境中使用，雖然無法維持非常高的性能，但是在樂觀鎖無法提更好的性能前提下，可以做到數據的安全性

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

樂觀鎖：

優點：在讀多寫少的並發場景下，可以避免資料庫加鎖的開銷，提高DAO層的響應性能，很多情況下ORM工具都有帶有樂觀鎖的實現，所以這些方法不一定需要我們人為的去實現。

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

實現：資料庫層面的樂觀鎖其實跟CAS思想類似， 通數據版本號或者時間戳也可以實現。

資料庫並發場景主要有三種：

讀-讀：不存在任何問題，也不需要並發控制

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

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

兩類更新丟失問題：

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

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

鎖分類

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

表級鎖定

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

頁級鎖定

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

行級鎖定

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

MyISAM中的鎖

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

從MyISAM所支持的鎖中也可以看出，MyISAM是一個支持讀讀並發，但不支持通用讀寫並發，寫寫並發的資料庫引擎，所以它更適合用於讀多寫少的應用場合，一般工程中也用的較少。

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 共享讀鎖：

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

for update排它寫鎖:

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

Gap Lock間隙鎖：

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

2、間隙鎖和行鎖合稱NextKeyLock，每個NextKeyLock是前開後閉區間。

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

1、加鎖的基本單位是 NextKeyLock，是前開後閉區間。

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

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

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

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

mysql 隔離級別設置為什麼比較好

術式之後皆為邏輯，一切皆為需求和實現。希望此文能從需求、現狀和解決方式的角度幫大家理解隔離級別。

隔離級別的產生

在串型執行的條件下，數據修改的順序是固定的、可預期的結果，但是並發執行的情況下，數據的修改是不可預期的，也不固定，為了實現數據修改在並發執行的情況下得到一個固定、可預期的結果，由此產生了隔離級別。

所以隔離級別的作用是用來平衡資料庫並發訪問與數據一致性的方法。

事務的4種隔離級別

READ UNCOMMITTED       未提交讀，可以讀取未提交的數據。READ COMMITTED         已提交讀，對於鎖定讀(select with for update 或者 for share)、update 和 delete 語句，                       InnoDB 僅鎖定索引記錄，而不鎖定它們之間的間隙，因此允許在鎖定的記錄旁邊自由插入新記錄。                       Gap locking 僅用於外鍵約束檢查和重複鍵檢查。REPEATABLE READ        可重複讀，事務中的一致性讀取讀取的是事務第一次讀取所建立的快照。SERIALIZABLE           序列化

在了解了 4 種隔離級別的需求後，在採用鎖控制隔離級別的基礎上，我們需要了解加鎖的對象(數據本身間隙)，以及了解整個數據範圍的全集組成。

數據範圍全集組成

SQL 語句根據條件判斷不需要掃描的數據範圍（不加鎖）；

SQL 語句根據條件掃描到的可能需要加鎖的數據範圍；

以單個數據範圍為例，數據範圍全集包含：（數據範圍不一定是連續的值，也可能是間隔的值組成）

1. 數據已經填充了整個數據範圍：（被完全填充的數據範圍，不存在數據間隙）

整形，對值具有唯一約束條件的數據範圍 1～5 ，

已有數據1、2、3、4、5，此時數據範圍已被完全填充；

整形，對值具有唯一約束條件的數據範圍 1 和 5 ，

已有數據1、5，此時數據範圍已被完全填充；

2. 數據填充了部分數據範圍：（未被完全填充的數據範圍，是存在數據間隙）

整形的數據範圍 1～5 ，

已有數據 1、2、3、4、5，但是因為沒有唯一約束，

所以數據範圍可以繼續被 1～5 的數據重複填充；

整形，具有唯一約束條件的數據範圍 1～5 ，

已有數據 2，5，此時數據範圍未被完全填充，還可以填充 1、3、4 ；

3. 數據範圍內沒有任何數據（存在間隙）

如下：

整形的數據範圍 1～5 ，數據範圍內當前沒有任何數據。

在了解了數據全集的組成後，我們再來看看事務並發時，會帶來的問題。

無控制的並發所帶來的問題

並發事務如果不加以控制的話會帶來一些問題，主要包括以下幾種情況。

1. 範圍內已有數據更改導致的：

更新丟失：當多個事務選擇了同一行，然後基於最初選定的值更新該行時，

由於每個事物不知道其他事務的存在，最後的更新就會覆蓋其他事務所做的更新；

臟讀： 一個事務正在對一條記錄做修改，這個事務完成並提交前，這條記錄就處於不一致狀態。

這時，另外一個事務也來讀取同一條記錄，如果不加控制，

第二個事務讀取了這些「臟」數據，並據此做了進一步的處理，就會產生提交的數據依賴關係。

這種現象就叫「臟讀」。

2. 範圍內數據量發生了變化導致：

不可重複讀：一個事務在讀取某些數據後的某個時間，再次讀取以前讀過的數據，

卻發現其讀出的數據已經發生了改變，或者某些記錄已經被刪除了。

這種現象就叫「不可重複讀」。

幻讀：一個事務按相同的查詢條件重新讀取以前檢索過的數據，

卻發現其他事務插入了滿足其查詢條件的新數據，這種現象稱為「幻讀」。

可以簡單的認為滿足條件的數據量變化了。

因為無控制的並發會帶來一系列的問題，這些問題會導致無法滿足我們所需要的結果。因此我們需要控制並發，以實現我們所期望的結果(隔離級別)。

MySQL 隔離級別的實現

InnoDB 通過加鎖的策略來支持這些隔離級別。

行鎖包含：

Record Locks

索引記錄鎖，索引記錄鎖始終鎖定索引記錄，即使表中未定義索引，

這種情況下，InnoDB 創建一個隱藏的聚簇索引，並使用該索引進行記錄鎖定。

Gap Locks

間隙鎖是索引記錄之間的間隙上的鎖，或者對第一條記錄之前或者最後一條記錄之後的鎖。

間隙鎖是性能和並發之間權衡的一部分。

對於無間隙的數據範圍不需要間隙鎖，因為沒有間隙。

Next-Key Locks

索引記錄上的記錄鎖和索引記錄之前的 gap lock 的組合。

假設索引包含 10、11、13 和 20。

可能的next-key locks包括以下間隔，其中圓括弧表示不包含間隔端點，方括弧表示包含端點：

(負無窮大, 10]    (10, 11]    (11, 13]    (13, 20]    (20, 正無窮大)        對於最後一個間隔，next-key將會鎖定索引中最大值的上方，

左右滑動進行查看

“上確界”偽記錄的值高於索引中任何實際值。

上確界不是一個真正的索引記錄，因此，實際上，這個 next-key 只鎖定最大索引值之後的間隙。

基於此，當獲取的數據範圍中，數據已填充了所有的數據範圍，那麼此時是不存在間隙的，也就不需要 gap lock。

對於數據範圍內存在間隙的，需要根據隔離級別確認是否對間隙加鎖。

默認的 REPEATABLE READ 隔離級別，為了保證可重複讀，除了對數據本身加鎖以外，還需要對數據間隙加鎖。

READ COMMITTED 已提交讀，不匹配行的記錄鎖在 MySQL 評估了 where 條件後釋放。

對於 update 語句，InnoDB 執行 “semi-consistent” 讀取，這樣它會將最新提交的版本返回到 MySQL，

以便 MySQL 可以確定該行是否與 update 的 where 條件相匹配。

總結延展：

唯一索引存在唯一約束，所以變更後的數據若違反了唯一約束的原則，則會失敗。

當 where 條件使用二級索引篩選數據時，會對二級索引命中的條目和對應的聚簇索引都加鎖；所以其他事務變更命中加鎖的聚簇索引時，都會等待鎖。

行鎖的增加是一行一行增加的，所以可能導致並發情況下死鎖的發生。

例如，

在 session A 對符合條件的某聚簇索引加鎖時，可能 session B 已持有該聚簇索引的 Record Locks，而 session B 正在等待 session A 已持有的某聚簇索引的 Record Locks。

session A 和 session B 是通過兩個不相干的二級索引定位到的聚簇索引。

session A 通過索引 idA，session B通過索引 idB 。

當 where 條件獲取的數據無間隙時，無論隔離級別為 rc 或 rr，都不會存在間隙鎖。

比如通過唯一索引獲取到了已完全填充的數據範圍，此時不需要間隙鎖。

間隙鎖的目的在於阻止數據插入間隙，所以無論是通過 insert 或 update 變更導致的間隙內數據的存在，都會被阻止。

rc 隔離級別模式下，查詢和索引掃描將禁用 gap locking，此時 gap locking 僅用於外鍵約束檢查和重複鍵檢查（主要是唯一性檢查）。

rr 模式下，為了防止幻讀，會加上 Gap Locks。

事務中，SQL 開始則加鎖，事務結束才釋放鎖。

就鎖類型而言，應該有優化鎖，鎖升級等，例如rr模式未使用索引查詢的情況下，是否可以直接升級為表鎖。

就鎖的應用場景而言，在回放場景中，如果確定事務可並發，則可以考慮不加鎖，加快回放速度。

鎖只是並發控制的一種粒度，只是一個很小的部分：

從不同場景下是否需要控制並發，(已知無交集且有序的數據的變更，MySQL 的 MTS 相同前置事務的多事務並發回放)

並發控制的粒度，(鎖是一種邏輯粒度，可能還存在物理層和其他邏輯粒度或方式)

相同粒度下的優化，(鎖本身存在優化，如IX、IS類型的優化鎖)

粒度載入的安全性能(如獲取行鎖前，先獲取頁鎖，頁鎖在執行獲取行鎖操作後即釋放，無論是否獲取成功)等多個層次去思考並發這玩意。

MySQL 事務的默認隔離級別是什麼？可以解決幻讀問題么？

我們設想一個場景，這個場景中我們需要插入多條相關聯的數據到資料庫，不幸的是，這個過程可能會遇到下面這些問題：

上面的任何一個問題都可能會導致數據的不一致性。為了保證數據的一致性，系統必須能夠處理這些問題。事務就是我們抽象出來簡化這些問題的首選機制。事務的概念起源於資料庫，目前，已經成為一個比較廣泛的概念。

何為事務？ 一言蔽之， 事務是邏輯上的一組操作，要麼都執行，要麼都不執行。

事務最經典也經常被拿出來說例子就是轉賬了。假如小明要給小紅轉賬 1000 元，這個轉賬會涉及到兩個關鍵操作，這兩個操作必須都成功或者都失敗。

事務會把這兩個操作就可以看成邏輯上的一個整體，這個整體包含的操作要麼都成功，要麼都要失敗。這樣就不會出現小明餘額減少而小紅的餘額卻並沒有增加的情況。

大多數情況下，我們在談論事務的時候，如果沒有特指 分散式事務 ，往往指的就是 資料庫事務 。

資料庫事務在我們日常開發中接觸的最多了。如果你的項目屬於單體架構的話，你接觸到的往往就是資料庫事務了。

那資料庫事務有什麼作用呢？

簡單來說，資料庫事務可以保證多個對資料庫的操作（也就是 SQL 語句）構成一個邏輯上的整體。構成這個邏輯上的整體的這些資料庫操作遵循： 要麼全部執行成功,要麼全部不執行 。

另外，關係型資料庫（例如： MySQL 、 SQL Server 、 Oracle 等）事務都有 ACID 特性：

ACID

這裡要額外補充一點： 只有保證了事務的持久性、原子性、隔離性之後，一致性才能得到保障。也就是說 A、I、D 是手段，C 是目的！

在典型的應用程序中，多個事務並發運行，經常會操作相同的數據來完成各自的任務（多個用戶對同一數據進行操作）。並發雖然是必須的，但可能會導致以下的問題。

不可重複讀和幻讀區別 ：不可重複讀的重點是修改比如多次讀取一條記錄發現其中某些列的值被修改，幻讀的重點在於新增或者刪除比如多次查詢同一條查詢語句（DQL）時，記錄發現記錄增多或減少了。

SQL 標準定義了四個隔離級別：

隔離級別臟讀不可重複讀幻讀 READ-UNCOMMITTED READ-COMMITTED REPEATABLE-READ SERIALIZABLE

MySQL 的隔離級別基於鎖和 MVCC 機制共同實現的。

SERIALIZABLE 隔離級別，是通過鎖來實現的。除了 SERIALIZABLE 隔離級別，其他的隔離級別都是基於 MVCC 實現。

不過， SERIALIZABLE 之外的其他隔離級別可能也需要用到鎖機制，就比如 REPEATABLE-READ 在當前讀情況下需要使用加鎖讀來保證不會出現幻讀。

MySQL InnoDB 存儲引擎的默認支持的隔離級別是 REPEATABLE-READ（可重讀） 。我們可以通過 SELECT @@tx_isolation; 命令來查看，MySQL 8.0 該命令改為 SELECT @@transaction_isolation;

從上面對 SQL 標準定義了四個隔離級別的介紹可以看出，標準的 SQL 隔離級別定義里，REPEATABLE-READ(可重複讀)是不可以防止幻讀的。

但是！InnoDB 實現的 REPEATABLE-READ 隔離級別其實是可以解決幻讀問題發生的，主要有下面兩種情況：

因為隔離級別越低，事務請求的鎖越少，所以大部分資料庫系統的隔離級別都是 READ-COMMITTED ，但是你要知道的是 InnoDB 存儲引擎默認使用 REPEATABLE-READ 並不會有任何性能損失。

InnoDB 存儲引擎在分散式事務的情況下一般會用到 SERIALIZABLE 隔離級別。

MySQL的默認事務隔離級別是?

mysql的4種事務隔離級別，如下所示：

1、未提交讀(Read Uncommitted)：允許臟讀，也就是可能讀取到其他會話中未提交事務修改的數據。

2、提交讀(Read Committed)：只能讀取到已經提交的數據。Oracle等多數資料庫默認都是該級別 (不重複讀)。

3、可重複讀(Repeated Read)：可重複讀。在同一個事務內的查詢都是事務開始時刻一致的，InnoDB默認級別。在SQL標準中，該隔離級別消除了不可重複讀，但是還存在幻象讀，但是innoDB解決了幻讀。

4、串列讀(Serializable)：完全串列化的讀，每次讀都需要獲得表級共享鎖，讀寫相互都會阻塞。

相關簡介

MySQL是一個關係型資料庫管理系統，由瑞典MySQL AB 公司開發，屬於 Oracle 旗下產品。MySQL 是最流行的關係型資料庫管理系統之一，在 WEB 應用方面，MySQL是最好的 RDBMS (Relational Database Management System，關係資料庫管理系統) 應用軟體之一。

MySQL是一種關係型資料庫管理系統，關係資料庫將數據保存在不同的表中，而不是將所有數據放在一個大倉庫內，這樣就增加了速度並提高了靈活性。

MySQL所使用的 SQL 語言是用於訪問資料庫的最常用標準化語言。MySQL 軟體採用了雙授權政策，分為社區版和商業版，由於其體積小、速度快、總體擁有成本低，尤其是開放源碼這一特點，一般中小型網站的開發都選擇 MySQL 作為網站資料庫。