MySQL中的事務和MVCC

来源:https://www.cnblogs.com/CodeBear/archive/2020/04/16/12710670.html
-Advertisement-
Play Games

本篇博客參考掘金小冊—— "MySQL 是怎樣運行的:從根兒上理解 MySQL" 以及極客時間——MySQL實戰45講。 雖然我們不是DBA,可能對資料庫沒那麼瞭解,但是對於資料庫中的索引、事務、鎖,我們還是必須要有一個較為淺顯的認識,今天我就和大家聊聊事務。 為什麼要有事務 說到事務,不得不提到轉 ...


本篇博客參考掘金小冊——MySQL 是怎樣運行的:從根兒上理解 MySQL
以及極客時間——MySQL實戰45講。

雖然我們不是DBA,可能對資料庫沒那麼瞭解,但是對於資料庫中的索引、事務、鎖,我們還是必須要有一個較為淺顯的認識,今天我就和大家聊聊事務。

為什麼要有事務

說到事務,不得不提到轉賬的事情,幾乎所有的關於事務的文章都會提到這個老掉牙的案例,我也不例外。

轉賬在資料庫層面可以簡單的抽象成兩個部分:

  • 從自己的賬戶中扣除轉賬金額;
  • 往對方賬戶中增加轉賬金額。

如果先從自己的賬戶中扣除轉賬金額,再往對方賬戶中增加轉賬金額,扣除執行成功,增加執行失敗,那自己的賬戶白白少了100塊,欲哭無淚。

如果先往對方賬戶中增加轉賬金額,再從自己的賬戶中扣除轉賬金額,增加執行成功,扣除執行失敗,那對方賬戶白白增加了100塊,自己的賬戶也沒有扣錢,喜大普奔。

不管是讓你欲哭無淚,還是喜大普奔,銀行都不會容忍這樣的事情發生,他們會引入事務來解決這類問題。

事務的特性

  1. 原子性(Atomicity):事務包含的所有操作要麼全部成功(提交),要麼全部失敗(回滾)。
  2. 一致性(Consistency):事務的執行的前後數據的完整性保持一致。
  3. 隔離性(Isolation):一個事務執行的過程中,不應該受到其他事務的干擾。
  4. 持久性(Durability):事務一旦結束,數據就持久到資料庫,即使提交後,資料庫發生崩潰,也不會丟失提交的數據。

四種特性,簡稱ACID,其中最不好理解的就是一致性,有不少人認為原子性、隔離性、持久性就是為了保證一致性,我們也不搞學術研究,一致性到底該怎麼解釋,到底怎麼定義一致性,就看各位看官的了。

事務的隔離級別

從某個角度來說,我們可以控制的、或者說需要研究的只有隔離性這一個特性,而要控制隔離性,幾乎只有調整隔離級別這一個手段,下麵我們就來看看事務的隔離級別。

資料庫是一個客戶端/伺服器架構的軟體,每個客戶端與伺服器連接後,就會產生一個session(會話),客戶端和伺服器的交互就是在session中進行的,理論上來說,如果伺服器同時只能處理一個事務,其他的事務都排隊等待,當該事務提交後,伺服器才處理下一個事務,這樣才真正具有“隔離性”,什麼問題都沒有了,但是如果是這樣,性能就太差了,在性能和隔離性之間,只能做一些平衡,所以資料庫提供了好幾個隔離級別供我們選擇。

在講隔離級別之前,我們先來看看事務併發執行會遇到什麼問題。

為了保證下麵的敘述可以順利進行,我們要先建一張表:

CREATE TABLE `student` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `name` varchar(50) DEFAULT NULL COMMENT '姓名',
  `age` int(11) DEFAULT NULL COMMENT '年齡',
  `grade` int(11) DEFAULT NULL COMMENT '年級',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8mb4;

臟寫

image.png
如圖所示:

  1. sessionA和sessionB開啟了一個事務;
  2. sessionB把id=2的name修改成了“地底王”;
  3. sessionA把id=2的name修改成了“夢境地底王”;
  4. sessionB回滾了事務;
  5. sessionA提交了事務。

如果sessionB在回滾事務的時候把sessionA的修改也給回滾了,導致sessionA的提交丟失了,這種現象就被稱為“臟寫”。sessionA會一臉懵逼,我明明修改了數據,也提交了數據,為什麼數據沒有變化呢。

臟讀

image.png
如圖所示:

  1. sessionA和sessionB開啟了一個事務;
  2. sessionB把id=2的name修改成了“地底王”,此時還未提交;
  3. sessionA查詢了id=2的數據,如果讀出來的數據的name是“地底王”,也就是讀到了sessionB還沒有提交的數據,就被稱為“臟讀”。

不可重覆讀

image.png
如圖所示:

  1. sessionA和sessionB開啟了一個事務;
  2. sessionA查詢id=2的數據,假如name是“地底王”,
  3. sessionB把id=2的name修改成了“夢境地底王”,隨後提交了事務;
  4. sessionA再一次查詢了id=2的數據,如果name是“夢境地底王”,說明在同一個事務中,sessionA前後讀到的數據不一致,就被稱為“不可重覆讀”。

幻讀

image.png
如圖所示:

  1. sessionA和sessionB開啟了一個事務;
  2. sessionA查詢name=“地底王”的數據,假設此時讀到了一條記錄;
  3. sessionB又插入一條name=“地底王”的數據,隨後提交;
  4. seesionA再一次查詢name=“地底王”的數據,如果此時讀到了兩條記錄,第二次查詢讀到了第一次查詢未查詢出來的數據,就被稱為“幻讀”。

四種隔離級別

我們知道了在併發執行事務的時候,會遇到什麼問題,有些問題比較嚴重,有些問題比較輕微,一般來說,我們認為按照嚴重性排序是這樣的:

臟寫>臟讀>不可重覆讀>幻讀

在SQL標准定義中,設定了四種隔離級別,來解決上述的問題:

  • 未提交讀(READ UNCOMMITTED):
    最低的隔離級別,會有“臟讀”、“不可重覆讀”,“幻讀”三個問題。
  • 讀已提交(READ COMMITTED):
    SQLServer預設隔離級別,可以避免“臟讀”,會有“不可重覆讀”,“幻讀”兩個問題。
  • 可重覆讀(REPEATABLE READ):
    可以避免“臟讀”,“不可重覆讀”兩個問題,會有“幻讀”問題。
    MySQL預設隔離級別,但是在MySQL中,此隔離級別解決了“幻讀”問題。
  • 串列化(SERIALIZABLE):
    所有的問題都不會發生。

因為臟寫的問題實在太嚴重了,在任何隔離級別下,都不會有臟寫的問題。

MVCC

前面說的都是開胃菜,相信大部分小伙伴對於上述內容都是手到擒來,所以我連如何修改事務隔離級別都沒有介紹,各種實驗也都沒有做,就是要把大量的時間、文字投入到這一部分內容中來。

MVCC,全稱是Mutil-Version Concurrency Control,翻譯成中文是多版本併發控制,MySQL就利用了MVCC來判斷在一個事務中,哪個數據可以被讀出來,哪個數據不能被讀出來。

多版本

在看MVCC之前,我們有必要知道另外一個知識點,資料庫存儲一行行數據,是分為兩個部分來存儲的,一個是數據行的額外信息(本篇博客不涉及),一個是真實的數據記錄,MySQL會為每一行真實數據記錄添加兩三個隱藏的欄位:

  • row_id
    非必須,如果表中有自定義的主鍵或者有Unique鍵,就不會添加row_id欄位,如果兩者都沒有,MySQL會“自作主張”添加row_id欄位。
  • transaction_id
    必須,事務Id,代表這一行數據是由哪個事務id創建的。
  • roll_pointer
    必須,回滾指針,指向這行數據的上一個版本。

如下圖所示:
image.png

在這裡需要著重說明下事務id,當我們開啟一個事務,並不會馬上獲得事務id,哪怕我們在事務中執行select語句,也是沒有事務id的(事務id為0),只有執行insert/update/delete語句才能獲得事務id,這一點尤為重要。

其中和MVCC緊密相關的是transaction_id和roll_pointer兩個欄位,在開發過程中,我們無需關心,但是要研究MVCC,我們必須關心。

如果有類似這樣的一行數據:
image.png
代表這行數據是由transaction_id為9的事務創建出來的,roll_pointer是空的,因為這是一條新紀錄。

實際上,roll_pointer並不是空的,如果真要解釋,需要繞一大圈,理解成空的,問題也不大。

當我們開啟事務,對這條數據進行修改,會變成這樣:
image.png

有點感覺了吧,這就像一個單向鏈表,稱之為“版本鏈”,最上面的數據是這個數據的最新版本,roll_pointer指向這個數據的舊版本,給人的感覺就是一行數據有多個版本,是不是符合“多版本併發控制”中的“多版本”這個概念,
那麼“併發控制”又是怎麼做到的呢,別急,繼續往下看。

ReadView

哎,下麵又要引出一個新的概念:ReadView。

對於READ UNCOMMITTED來說,可以讀取到其他事務還沒有提交的數據,所以直接把這個數據的最新版本讀出來就可以了,對於SERIALIZABLE來說,是用加鎖的方式來訪問記錄。

剩下的就是READ COMMITTED和REPEATABLE READ,這兩個事務隔離級別都要保證讀到的數據是其他事務已經提交的,也就是不能無腦把一行數據的最新版本給讀出來了,但是這兩個還是有一定的區別,最核心的問題就在於“我到底可以讀取這個數據的哪個版本”。

為瞭解決這個問題,ReadView的概念就出現了,ReadView包含四個比較重要的內容:

  • m_ids:表示在生成ReadView時,系統中活躍的事務id集合。
  • min_trx_id:表示在生成ReadView時,系統中活躍的最小事務id,也就是 m_ids中的最小值。
  • max_trx_id:表示在生成ReadView時,系統應該分配給下一個事務的id。
  • creator_trx_id:表示生成該ReadView的事務id。

有了這個ReadView,只要按照下麵的判斷方式就可以解決“我到底可以讀取這個數據的哪個版本”這個千古難題了:

  • 如果被訪問的版本的trx_id和ReadView中的creator_trx_id相同,就意味著當前版本就是由你“造成”的,可以讀出來。
  • 如果被訪問的版本的trx_id小於ReadView中的min_trx_id,表示生成該版本的事務在創建ReadView的時候,已經提交了,所以該版本可以讀出來。
  • 如果被訪問版本的trx_id大於或等於ReadView中的max_trx_id值,說明生成該版本的事務在當前事務生成ReadView後才開啟,所以該版本不可以被讀出來。
  • 如果生成被訪問版本的trx_id在min_trx_id和max_trx_id之間,那就需要判斷下trx_id在不在m_ids中:如果在,說明創建ReadView的時候,生成該版本的事務還是活躍的(沒有被提交),該版本不可以被讀出來;如果不在,說明創建ReadView的時候,生成該版本的事務已經被提交了,該版本可以被讀出來。

如果某個數據的最新版本不可以被讀出來,就順著roll_pointer找到該數據的上一個版本,繼續做如上的判斷,以此類推,如果第一個版本也不可見的話,代表該數據對當前事務完全不可見,查詢結果就不包含這條記錄了。

看完上面的描述,是不是覺得“雲里霧裡”,“不知所云”,甚至“腦闊疼,整個人都不好了”。

我們換個方法來解釋,看會不會更容易理解點:
image.png
在事務啟動的一瞬間(執行CURD操作),會創建出ReadView,對於一個數據版本的trx_id來說,有以下三種情況:

  • 如果落在低水位,表示生成這個版本的事務已經提交了,或者是當前事務自己生成的,這個版本可見。
  • 如果落在高水位,表示生成這個版本的事務是未來才創建的,這個版本不可見。
  • 如果落在中間水位,包含兩種情況:
    a. 如果當前版本的trx_id在活躍事務列表中,代表這個版本是由還沒有提交的事務生成的,這個版本不可見;
    b. 如果當前版本的trx_id不在活躍事務列表中,代表這個版本是由已經提交的事務生成的,這個版本可見。

上面我比較簡單的解釋了下ReadView,用了兩種方式來說明如何判斷當前數據版本是否可見,不知道各位看官是不是有了一個比較模糊的概念,有了ReadView的基本概念,我們就可以具體看下READ COMMITTED、REPEATABLE READ這兩個事務隔離級別為什麼讀到的數據是不同的,以及上述規則是如何應用的。

READ COMMITTED——每次讀取數據都會創建ReadView

假設,現在系統只有一個活躍的事務T,事務id是100,事務中修改了數據,但是還沒有提交,形成的版本鏈是這樣的:
image.png

現在A事務啟動,並且執行了select語句,此時會創建出一個ReadView,m_ids是【100】,min_trx_id是100, max_trx_id是101,creator_trx_id是0。

為什麼m_ids只有一個,為什麼creator_trx_id是0?這裡再次強調下,只有在事務中執行insert/update/delete語句才能獲得事務id。

那麼A事務執行的select語句會讀到什麼數據呢?

  1. 判斷最新的數據版本,name是“夢境地底王”,對應的trx_id是100,trx_id在m_ids裡面,說明當前事務是活躍事務,這個數據版本是由還沒有提交的事務創建的,所以這個版本不可見。
  2. 順著roll_pointer找到這個數據的上一個版本,name是“地底王”,對應的trx_id是99,而ReadView中的min_trx_id是100,trx_id<min_trx_id,代表當前數據版本是由已經提交的事務創建的,該版本可見。

所以讀到的數據的name是“地底王”。

我們把事務T提交了,事務A再次執行select語句,此時,事務A再次創建出ReadView,m_ids是【】,min_trx_id是0, max_trx_id是101,creator_trx_id是0。

因為事務T已經提交了,所以沒有活躍的事務。

那麼事務A第二次執行select語句又會讀到什麼數據呢?

  1. 判斷最新的數據版本,name是“夢境地底王”,對應的trx_id是100,不在m_ids裡面,說明這個數據版本是由已經提交的事務創建的,該版本可見。

所以讀到的數據的name是“夢境地底王”。

REPEATABLE READ ——首次讀取數據會創建ReadView

假設,現在系統只有一個活躍的事務T,事務id是100,事務中修改了數據,但是還沒有提交,形成的版本鏈是這樣的:
image.png

現在A事務啟動,並且執行了select語句,此時會創建出一個ReadView,m_ids是【100】,min_trx_id是100, max_trx_id是101,creator_trx_id是0。

那麼A事務執行的select語句會讀到什麼數據呢?

  1. 判斷最新的數據版本,name是“夢境地底王”,對應的trx_id是100,trx_id在m_ids裡面,說明當前事務是活躍事務,這個數據版本是由還沒有提交的事務創建的,所以這個版本不可見。
  2. 順著roll_ponit找到這個數據的上一個版本,name是“地底王”,對應的trx_id是99,而ReadView中的min_trx_id是100,trx_id<min_trx_id,代表當前數據版本是由已經提交的事務創建的,該版本可見。

所以讀到的數據的name是“地底王”。

細心的你,一定發現了,這裡我就是複製粘貼,因為在REPEATABLE READ事務隔離級別下,事務A首次執行select語句創建出來的ReadView和在READ COMMITTED事務隔離級別下,事務A首次執行select語句創建出來的ReadView是一樣的,所以判斷流程也是一樣的,所以我就偷懶了,copy走起。

隨後,事務T提交了事務,由於REPEATABLE READ是首次讀取數據才會創建ReadView,所以事務A再次執行select語句,不會再創建ReadView,用的還是上一次的ReadView,所以判斷流程和上面也是一樣的,所以讀到的name還是“地底王”。

本篇博客到這裡就結束了。


您的分享是我們最大的動力!

-Advertisement-
Play Games
更多相關文章
  • 前言: 工欲善其事必先利其器,今天給大家介紹一下HBase Shell十大花式利器,在日常運維工作中,可以試著用起來。 1. 交互模式 也就是我們最常用到的Shell命令行的方式。 2. 非交互模式 與交互模式比較 如果我們想要知道HBase Shell命令執行之後是否成功,那一定要使用非交互模式。 ...
  • 一、Redis 1、簡介 【官方簡介地址:】 https://redis.io/topics/introduction 看不懂不要緊,先混個眼熟,慢慢來...。 【初步認識 Redis:】 Redis is an open source (BSD licensed), in-memory data ...
  • 一、集群伺服器配置說明(整個過程中我會提前把一些小坑填上,有的坑後面沒有提到) IP 節點名 OS Cores Memory Disk Remark 172.25.16.1 cdh1 CentOS7.5 40 128 4T cloudera Server、cloudera agent 172.25. ...
  • 數據預處理背景 大數據項目開發流程 數據質量 準確性:數據是正確的,數據存儲在資料庫中的值對應於真實世界的值。 數據不准確的原因 1. 數據收集設備故障。 2. 數據輸入錯誤。 3. 數據傳輸過程出錯。 4. 命名約定、數據輸入、輸入欄位格式不一致。 相關性:指數據與特定的應用和領域有關。 相關性應 ...
  • 拼團活動該如何設計 後臺創建拼團活動 一個成熟的拼團活動包含的四個要素: 1 拼團成團商品 必須要帶上或者關聯商品,設置拼團時商品的價格,與原價格肯定要低,這樣才能吸引更多的人拼團。 2 拼團人數 既然是拼著購買,這裡設置的人數肯定是不能低於2人的。要不然就不成團了。 3 拼團活動有效時間 一個拼團 ...
  • 1.聚集索引和輔助索引 在資料庫中,B+樹的高度一般都在24層,這也就是說查找某一個鍵值的行記錄時最多只需要2到4次IO,這倒不錯。因為當前一般的機械硬碟每秒至少可以做100次IO,24次的IO意味著查詢時間只需要0.02~0.04秒。 資料庫中的B+樹索引可以分為聚集索引(clustered in ...
  • 新建表T: mysql> create table T ( ID int primary key, k int NOT NULL DEFAULT 0, s varchar(16) NOT NULL DEFAULT '', index k(k)) engine=InnoDB; insert into ...
  • 恢復內容開始 [TOC] MySQL相關知識 Mysql鏈接 mysql u用戶名 p密碼 創建資料庫 create databse 資料庫名; 刪除資料庫 drop database 資料庫名; 選擇資料庫 use 資料庫名 數據類型 1. 數值型 整型 INTEGER、SMALLINT、NUME ...
一周排行
    -Advertisement-
    Play Games
  • 移動開發(一):使用.NET MAUI開發第一個安卓APP 對於工作多年的C#程式員來說,近來想嘗試開發一款安卓APP,考慮了很久最終選擇使用.NET MAUI這個微軟官方的框架來嘗試體驗開發安卓APP,畢竟是使用Visual Studio開發工具,使用起來也比較的順手,結合微軟官方的教程進行了安卓 ...
  • 前言 QuestPDF 是一個開源 .NET 庫,用於生成 PDF 文檔。使用了C# Fluent API方式可簡化開發、減少錯誤並提高工作效率。利用它可以輕鬆生成 PDF 報告、發票、導出文件等。 項目介紹 QuestPDF 是一個革命性的開源 .NET 庫,它徹底改變了我們生成 PDF 文檔的方 ...
  • 項目地址 項目後端地址: https://github.com/ZyPLJ/ZYTteeHole 項目前端頁面地址: ZyPLJ/TreeHoleVue (github.com) https://github.com/ZyPLJ/TreeHoleVue 目前項目測試訪問地址: http://tree ...
  • 話不多說,直接開乾 一.下載 1.官方鏈接下載: https://www.microsoft.com/zh-cn/sql-server/sql-server-downloads 2.在下載目錄中找到下麵這個小的安裝包 SQL2022-SSEI-Dev.exe,運行開始下載SQL server; 二. ...
  • 前言 隨著物聯網(IoT)技術的迅猛發展,MQTT(消息隊列遙測傳輸)協議憑藉其輕量級和高效性,已成為眾多物聯網應用的首選通信標準。 MQTTnet 作為一個高性能的 .NET 開源庫,為 .NET 平臺上的 MQTT 客戶端與伺服器開發提供了強大的支持。 本文將全面介紹 MQTTnet 的核心功能 ...
  • Serilog支持多種接收器用於日誌存儲,增強器用於添加屬性,LogContext管理動態屬性,支持多種輸出格式包括純文本、JSON及ExpressionTemplate。還提供了自定義格式化選項,適用於不同需求。 ...
  • 目錄簡介獲取 HTML 文檔解析 HTML 文檔測試參考文章 簡介 動態內容網站使用 JavaScript 腳本動態檢索和渲染數據,爬取信息時需要模擬瀏覽器行為,否則獲取到的源碼基本是空的。 本文使用的爬取步驟如下: 使用 Selenium 獲取渲染後的 HTML 文檔 使用 HtmlAgility ...
  • 1.前言 什麼是熱更新 游戲或者軟體更新時,無需重新下載客戶端進行安裝,而是在應用程式啟動的情況下,在內部進行資源或者代碼更新 Unity目前常用熱更新解決方案 HybridCLR,Xlua,ILRuntime等 Unity目前常用資源管理解決方案 AssetBundles,Addressable, ...
  • 本文章主要是在C# ASP.NET Core Web API框架實現向手機發送驗證碼簡訊功能。這裡我選擇是一個互億無線簡訊驗證碼平臺,其實像阿裡雲,騰訊雲上面也可以。 首先我們先去 互億無線 https://www.ihuyi.com/api/sms.html 去註冊一個賬號 註冊完成賬號後,它會送 ...
  • 通過以下方式可以高效,並保證數據同步的可靠性 1.API設計 使用RESTful設計,確保API端點明確,並使用適當的HTTP方法(如POST用於創建,PUT用於更新)。 設計清晰的請求和響應模型,以確保客戶端能夠理解預期格式。 2.數據驗證 在伺服器端進行嚴格的數據驗證,確保接收到的數據符合預期格 ...