MySQL 事務和鎖

来源:https://www.cnblogs.com/Yee-Q/archive/2022/04/29/16209147.html
-Advertisement-
Play Games

事務概述 當多個用戶訪問同一份數據時,一個用戶在更改數據的過程中,可能有其他用戶同時發起更改請求,為保證資料庫記錄的更新從一個一致性狀態變為另外一個一致性狀態,使用事務處理是非常必要的,事務具有以下四個特性: 原子性(Atomicity):事務中所有操作視為一個原子單位,即對事務所進行的數據修改等操 ...



事務概述

當多個用戶訪問同一份數據時,一個用戶在更改數據的過程中,可能有其他用戶同時發起更改請求,為保證資料庫記錄的更新從一個一致性狀態變為另外一個一致性狀態,使用事務處理是非常必要的,事務具有以下四個特性:

  1. 原子性(Atomicity):事務中所有操作視為一個原子單位,即對事務所進行的數據修改等操作只能是完全回滾或完全提交
  2. 一致性(Consistency):事務在完成時,必須使用所有的數據從一種一致性變更為另一種一致性狀態,所有的變更都必須應用於事務的修改,以確保數據的完整性。事務的一致性由原子性、持久性和隔離性一起實現
  3. 隔離性(Isolation):一個事務中的操作語句所做的修改必須與其他事務所做的修改相隔離。在進行事務查看數據時,數據所處的狀態,要麼是被另一個併發事務修改之前的狀態,要麼是被另一併發事務修改之後的狀態,即當前事務不會查詢由另一個併發事務正在修改的數據。隔離性由 MySQL 鎖機制實現
  4. 持久性(Durability):事務完成之後,所做的修改對數據的影響是永久的,即使系統重啟或者出現系統故障,數據仍可恢復

MySQL 提供了多種事務型存儲引擎,如 InnoDB 和 BDB 等,而 MyISAM 不支持事務。為了支持事務,InnoDB 存儲引擎引入了與事務處理相關的 REDO 日誌和 UNDO 日誌,同時事務依賴於 MySQL 提供的鎖機制

1. REDO 日誌

事務執行時需要將執行的事務日誌寫入日誌文件,對應的文件為 REDO 日誌。當每條 SQL 進行數據更新操作時,首先將 REDO 日誌寫進日誌緩衝區。當客戶端執行 COMMIT 命令提交時,日誌緩衝區的內容將被刷新到磁碟,日誌緩衝區的刷新方式或者時間間隔可以通過參數 innodb_flush_log_at_trx_commit 控制

REDO 日誌對應磁碟上的 ib_logifleN 文件,該文件預設為 5MB,建議設置為 512MB,以便容納較大的事務。MySQL 崩潰恢復時會重新執行 REDO 日誌的記錄,恢復最新數據,保證已提交事務的持久性

2. UNDO 日誌

與 REDO 日誌相反,UNDO 日誌主要用於事務異常時的數據回滾,具體內容就是記錄數據被修改前的信息到 UNDO 緩衝區,然後在合適的時間將內容刷新到磁碟

假如由於系統錯誤或者 rollback 操作而導致事務回滾,可以根據 undo 日誌回滾到沒修改前的狀態,保證未提交事務的原子性

與 REDO 日誌不同的是,磁碟上不存在單獨的 UNDO 日誌文件,所有的 UNDO 日誌均存在表空間對應的 .ibd 數據文件中,即使 MySQL 服務啟動了獨立表空間


事務控制語句

在 MySQL 中,可以使用 BEGIN 開始事務,使用 COMMIT 結束事務,中間可以使用 ROLLBACK 回滾事務。MySQL 通過 SET AUTOCOMMIT、START TRANSACTION、COMMIT 和 ROLLBACK 等語句支持本地事務

START TRANSACTION | BEGIN [WORK]
COMMIT [WORK]
ROLLBACK [WORK]
SET AUTOCOMMIT = {0 | 1}
  • BEGIN | START TRANSACTION:開始事務
  • COMMIT:結束事務
  • ROLLBACK:回滾事務
  • WORK:SQL 語句
  • SET AUTOCOMMIT:是否自動提交,0 禁止,1 開啟,預設為 1

事務隔離級別

MySQL 定義了四種隔離級別,指定事務中哪些數據改變其他事務可見、哪些數據該表其他事務不可見。低級別的隔離級別可以支持更高的併發處理,同時占用的系統資源更少

InnoDB 系統級事務隔離級別可以使用以下語句設置:

  • 未提交讀:SET GLOBAL TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  • 提交讀:SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 可重覆讀:SET GLOBAL TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • 串列化:SET GLOBAL TRANSACTION ISOLATION LEVEL SERIALIZABLE;

查看系統級事務隔離級別:

SELECT @@global.tx_isolation;

InnoDB 會話級事務隔離級別可以使用以下語句設置:

  • 未提交讀:SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
  • 提交讀:SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
  • 可重覆讀:SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
  • 串列化:SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;

查看會話級事務隔離級別:

SELECT @@tx_isolation;

1. 讀未提交

在該隔離級別,所有事務都可以看到其他未提交事務的執行結果。讀取未提交的數據稱為臟讀(Dirty Read),即是:首先開啟 A 和 B 兩個事務,在 B 事務更新但未提交之前,A 事務讀取到了更新後的數據,但由於 B 事務回滾,導致 A 事務出現了臟讀現象

2. 讀已提交

所有事務只能看見已經提交事務所做的改變,此級別可以解決臟讀,但也會導致不可重覆讀(Nonrepeatable Read):首先開啟 A 和 B 兩個事務,A事務讀取了 B 事務的數據,在 B 事務更新並提交後,A 事務又讀取到了更新後的數據,此時就出現了同一 A 事務中的查詢出現了不同的查詢結果

3. 可重覆讀

MySQL 預設的事務隔離級別,能確保同一事務的多個實例在併發讀取數據時看到同樣的數據行,理論上會導致一個問題,幻讀(Phontom Read)。例如,第一個事務對一個表中的數據做了修改,這種修改會涉及表中的全部數據行,同時第二個事務也修改這個表中的數據,這次的修改是向表中插入一行新數據,此時就會發生操作第一個事務的用戶發現表中還有沒有修改的數據行

InnoDB 通過多版本併發控制機制(MVCC)解決了該問題:InnoDB 通過為每個數據行增加兩個隱含值的方式來實現,這兩個隱含值記錄了行的創建時間、過期時間以及每一行存儲時間發生時的系統版本號,每個查詢根據事務的版本號來查詢結果

4. 串列化

通過強制事務排序,使其不可能相互衝突,從而解決幻讀問題。簡而言之,就是在每個讀的數據行上加上共用鎖實現,這個級別會導致大量的超時現象和鎖競爭,一般不推薦使用


InnoDB 鎖機制

為瞭解決資料庫併發控制問題,如走到同一時刻客戶端對同一張表做更新或者查詢操作,需要對併發操作進行控制,因此產生了鎖

1. 鎖的類型

1.1 共用鎖

共用鎖的粒度是行或者元組(多個行),一個事務獲取了共用鎖以後,可以對鎖定範圍內的數據執行讀操作

1.2 排他鎖

排他鎖的粒度與共用鎖相同,一個事務獲取排他鎖以後,可以對鎖定範圍內的數據執行寫操作

有兩個事務 A 和 B,如果事務 A 獲取了一個元組的共用鎖,事務 B 還可以立即獲取這個元組的共用鎖,但不能獲取這個元組的排他鎖,必須等到事務 A 釋放共用鎖之後。如果事務 A 獲取了一個元組的排他鎖,事務 B 不能立即獲取這個元組的共用鎖,也不能立即獲取這個元組的排他鎖,必須等到 A 釋放排他鎖之後

1.3 意向鎖

意向鎖是一種表鎖,鎖定的粒度是整張表,分為意向共用鎖和意向排他鎖。意向共用鎖表示一個事務有意對數據上共用鎖或者排他鎖。有意表示事務想執行操作但還沒真正執行

2. 鎖的粒度

鎖的粒度主要分為表鎖和行鎖

表鎖的開銷最小,同時允許的併發量也是最小。MyISAM 存儲引擎使用該鎖機制。當要寫入數據時,整個表記錄被鎖,此時其他讀/寫動作一律等待。一些特定的動作,如 ALTER TABLE 執行時使用的也是表鎖

行鎖可以支持最大的併發,InnoDB 存儲引擎使用該鎖機制。如果要支持併發讀/寫,建議採用 InnoDB 存儲引擎



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

-Advertisement-
Play Games
更多相關文章
  • linux網路編程示例 簡單TCP、UDP編程示例。 TCP TCP客戶端與伺服器通信模型 伺服器代碼 #include <sys/socket.h> #include <netinet/in.h> #include <arpa/inet.h> #include <errno.h> #include ...
  • Linux下實現I/O復用的系統調用方式主要:select、poll、epoll。 ...
  • 從零部署.NetCore到Linux 購買Ubuntu伺服器 購買伺服器 創建root用戶,設置密碼 # 創建root用戶 sudo passwd root # 輸入兩次密碼 啟動ssh 系統預設是關閉的,需要打開下。 安裝Nginx 如何在 Ubuntu 20.04 上安裝 Nginx sudo命 ...
  • 前言 用騰訊雲伺服器Ubuntu搭建完SSH服務後,隔了一段時間重裝了伺服器系統,搭建的時候想著直接按照原先的博客梭哈就完事了Windows10公鑰遠程連接Linux伺服器結果就是各種失敗,耗費不少時間去解決,其中馬虎問題最嚴重。在此記錄分享,望能對大家有所幫助。Windows10公鑰遠程連接Lin ...
  • 鏡像下載、功能變數名稱解析、時間同步請點擊 阿裡雲開源鏡像站 一、在兩台機器上安裝docker // 1.安裝Docker源 yum install -y wget && wget https://mirrors.aliyun.com/docker-ce/linux/centos/docker-ce.rep ...
  • 前言 由於CentOS 8的支持已經到期了,.NET 6也不支持了,然後也無法升級,導致使用起來已經非常不便,無奈只有遷移伺服器這個選項了。 選擇發行版本一直是一個比較頭疼的問題,首先我不是專門運維的,最好使用的人比較多的版本,然後.NET要官方支持的版本才行,最好是具有中國區的鏡像服務。雖然Fed ...
  • ** linux 下需要知道你系統的Linux內核版本,然後選擇相應的版本下載,版本查詢命令:** uname -a 在高版本20.04和18.04等版本,不需要執行下麵這條命令來編譯makefile文件,因為系統自身存在串口驅動cp210x.o make 於是只需要在[/lib/modules/5 ...
  • 最近需要使用mysql8.0版本,但是原本的mysql5.7版本已經被多個服務依賴,於是想想能不能同一臺伺服器裝多個版本的mysql,一查確實可行,這裡做一個記錄方便自己後期回憶 閱讀本文前請註意!!! 本文是幫助您建立在mysql5.7版本已經安裝完成併在運行中,另外安裝配置mysql8.0版本 ...
一周排行
    -Advertisement-
    Play Games
  • Timer是什麼 Timer 是一種用於創建定期粒度行為的機制。 與標準的 .NET System.Threading.Timer 類相似,Orleans 的 Timer 允許在一段時間後執行特定的操作,或者在特定的時間間隔內重覆執行操作。 它在分散式系統中具有重要作用,特別是在處理需要周期性執行的 ...
  • 前言 相信很多做WPF開發的小伙伴都遇到過表格類的需求,雖然現有的Grid控制項也能實現,但是使用起來的體驗感並不好,比如要實現一個Excel中的表格效果,估計你能想到的第一個方法就是套Border控制項,用這種方法你需要控制每個Border的邊框,並且在一堆Bordr中找到Grid.Row,Grid. ...
  • .NET C#程式啟動閃退,目錄導致的問題 這是第2次踩這個坑了,很小的編程細節,容易忽略,所以寫個博客,分享給大家。 1.第一次坑:是windows 系統把程式運行成服務,找不到配置文件,原因是以服務運行它的工作目錄是在C:\Windows\System32 2.本次坑:WPF桌面程式通過註冊表設 ...
  • 在分散式系統中,數據的持久化是至關重要的一環。 Orleans 7 引入了強大的持久化功能,使得在分散式環境下管理數據變得更加輕鬆和可靠。 本文將介紹什麼是 Orleans 7 的持久化,如何設置它以及相應的代碼示例。 什麼是 Orleans 7 的持久化? Orleans 7 的持久化是指將 Or ...
  • 前言 .NET Feature Management 是一個用於管理應用程式功能的庫,它可以幫助開發人員在應用程式中輕鬆地添加、移除和管理功能。使用 Feature Management,開發人員可以根據不同用戶、環境或其他條件來動態地控制應用程式中的功能。這使得開發人員可以更靈活地管理應用程式的功 ...
  • 在 WPF 應用程式中,拖放操作是實現用戶交互的重要組成部分。通過拖放操作,用戶可以輕鬆地將數據從一個位置移動到另一個位置,或者將控制項從一個容器移動到另一個容器。然而,WPF 中預設的拖放操作可能並不是那麼好用。為瞭解決這個問題,我們可以自定義一個 Panel 來實現更簡單的拖拽操作。 自定義 Pa ...
  • 在實際使用中,由於涉及到不同編程語言之間互相調用,導致C++ 中的OpenCV與C#中的OpenCvSharp 圖像數據在不同編程語言之間難以有效傳遞。在本文中我們將結合OpenCvSharp源碼實現原理,探究兩種數據之間的通信方式。 ...
  • 一、前言 這是一篇搭建許可權管理系統的系列文章。 隨著網路的發展,信息安全對應任何企業來說都越發的重要,而本系列文章將和大家一起一步一步搭建一個全新的許可權管理系統。 說明:由於搭建一個全新的項目過於繁瑣,所有作者將挑選核心代碼和核心思路進行分享。 二、技術選擇 三、開始設計 1、自主搭建vue前端和. ...
  • Csharper中的表達式樹 這節課來瞭解一下表示式樹是什麼? 在C#中,表達式樹是一種數據結構,它可以表示一些代碼塊,如Lambda表達式或查詢表達式。表達式樹使你能夠查看和操作數據,就像你可以查看和操作代碼一樣。它們通常用於創建動態查詢和解析表達式。 一、認識表達式樹 為什麼要這樣說?它和委托有 ...
  • 在使用Django等框架來操作MySQL時,實際上底層還是通過Python來操作的,首先需要安裝一個驅動程式,在Python3中,驅動程式有多種選擇,比如有pymysql以及mysqlclient等。使用pip命令安裝mysqlclient失敗應如何解決? 安裝的python版本說明 機器同時安裝了 ...