首頁 > 文章 > 資料庫 > 帶你去聊聊MySQL中的事務隔離

帶你去聊聊MySQL中的事務隔離

青灯夜游轉載: 2021-08-30 10:30:401762瀏覽

這篇文章帶大家了解一下MySQL中的事務隔離，介紹一下事務的特性、隔離等級、事務啟動的方式等，希望對大家有幫助！

帶你去聊聊MySQL中的事務隔離

交易就是要確保一組資料庫操作，要嘛全部成功，要嘛全部失敗。在MySQL中，事務支援是在引擎層實現的，但並不是所有的引擎都支援事務。例如MySQL原生的MyISAM引擎就不支援事務。【相關推薦：mysql教學(影片)】

一、交易的特性

原子性：一個事務中的所有操作，要嘛全部完成，要嘛全部不完成，不會結束在中間某個環節。事務在執行過程中發生錯誤，會被回滾到事務開始前的狀態，就像這個事務從來沒有執行過一樣
一致性：在事務開始之前和事務結束以後，資料庫的完整性沒有被破壞
隔離性：資料庫允許多個並發事務同時對資料進行讀寫和修改的能力，隔離性可以防止多個事務並發執行時由於交叉執行而導致資料的不一致
持久性：交易處理結束後，資料的修改就是永久的，即使係統故障也不會遺失

二、隔離等級

# #1.當資料庫上有多個事務同時執行的時候，就可能出現髒讀、不可重複讀、幻讀的問題
髒讀：B事務讀取到了A事務尚未提交的資料

無法重複讀取：一個事務讀取到了另一個事務中提交的update的資料

2.交易的隔離等級包括：讀取未提交、讀取提交、可重複讀取和串行化
讀取未提交：一個交易還沒提交時，它所做的變更就能被別的事務看到
讀取提交：一個事務提交之後，它所做的變更才會被其他事務看到（解決臟讀，Oracle默認的隔離級別）

可重複讀取：一個事務執行過程中看到的數據，總是跟這個事務在啟動時看到的數據是一致的，而且未提交變更對其他事務也是不可見的（解決髒讀與不可重複讀，MySQL預設的隔離等級）

序列化：對於同一行記錄，寫會加寫鎖，讀會加讀鎖，當出現讀寫鎖衝突的時候，後存取的事務必須等前一個事務執行完成，才能繼續執行（解決髒讀、不可重複讀取和幻讀）

安全性依序提交，效能依序降低

帶你去聊聊MySQL中的事務隔離 3 .假設資料表T中只有一列，其中一行的值為1

create table T(c int) engine=InnoDB;
insert into T(c) values(1);

##若隔離等級是讀取未提交，則V1是2。這時候事務B雖然還沒提交，但是結果已經被A看到了。 V2、V3都是2
若隔離等級是讀取提交，則V1是1，V2是2。事務B的更新在提交後才能被A看到。 V3也是2

若隔離等級是可重複讀取，則V1、V2是1，V3是2。之所以V2是1，遵循的是交易在執行期間看到的資料前後必須是一致的

若隔離等級是串列化，V1、V2值是1，V3是2

在實作上，資料庫裡面會建立一個視圖，存取的時候會以視圖的邏輯結果為準。在可重複讀取隔離層級下，這個視圖是在交易啟動時建立的，整個事務存在期間都會使用這個視圖。在讀取提交隔離等級下，這個視圖是在每個SQL語句開始執行的時候建立的。讀取未提交隔離等級下直接傳回記錄上的最新值，沒有視圖概念；而串行化隔離等級下直接用加鎖的方式來避免並行存取

三、交易隔離的實現（以可重複讀取為例）

在MySQL中，每筆記錄在更新的時候都會同時記錄一則回滾操作。記錄上的最新值，透過回滾操作，都可以得到前一個狀態的值帶你去聊聊MySQL中的事務隔離

假設一個值從1被依序改成了2、3、4，在回滾日誌裡面就會有類似下面的記錄

目前值是4，但是在查詢這條記錄的時候，不同時刻啟動的交易會有不同的read-view。如圖看到的，在視圖A、B、C裡面，這一個記錄的值分別是1、2、4，同一記錄在系統中可以存在多個版本，就是資料庫的多版本並發控制（MVCC ）。對於read-viewA，要得到1，就必須將當前值一次執行圖中所有的回滾操作得到#########即使現在有另外一個事務正在將4改成5，這個事務跟read -view A、B、C對應的事務是不會衝突的###

系统会判断，当没有事务再需要用到这些回滚日志时，回滚日志会被删除

四、事务启动的方式

MySQL的事务启动方式有以下几种：

显示启动事务语句，begin或start transaction。提交语句是commit，回滚语句是rollback
set autocommit=0，这个命令将这个线程的自动提交关掉。意味着如果只执行一个select语句，这个事务就启动了，而且不会自动提交事务。这个事务持续存在直到主动执行commit或rollback语句，或者断开连接

建议使用set autocommit=1，通过显示语句的方式来启动事务

可以在information_schema库中的innodb_trx这个表中查询长事务，如下语句查询持续时间超过60s的事务

select * from information_schema.innodb_trx where TIME_TO_SEC(timediff(now(),trx_started))>60

五、事务隔离还是不隔离

下面是一个只有两行的表的初始化语句：

mysql> CREATE TABLE `t` (
  `id` int(11) NOT NULL,
  `k` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB;
insert into t(id, k) values(1,1),(2,2);

事务A、B、C的执行流程如下，采用可重复读隔离级别
帶你去聊聊MySQL中的事務隔離

begin/start transaction命令：不是一个事务的起点，在执行到它们之后的第一个操作InnoDB表的语句，事务才真正启动，一致性视图是在执行第一个快照读语句时创建的

start transaction with consistent snapshot命令：马上启动一个事务，一致性视图是在执行这条命令时创建的

按照上图的流程执行，事务B查到的k的值是3，而事务A查到的k的值是1

1、快照在MVCC里是怎么工作的？

在可重复读隔离级别下，事务启动的时候拍了个快照。这个快照是基于整个库的，那么这个快照是如何实现的？

InnoDB里面每个事务有一个唯一的事务ID，叫做transaction id。它在事务开始的时候向InnoDB的事务系统申请，是按申请顺序严格递增的

每行数据也都是有多个版本的。每次事务更新数据的时候，都会生成一个新的数据版本，并且把transaction id赋值给这个数据版本的事务ID，记作row trx_id。同时，旧的数据版本要保留，并且在新的数据版本中，能够有信息可以直接拿到它。也就是说，数据表中的一行记录，其实可能有多个版本，每个版本有自己的row trx_id

下图是一个记录被多个事务连续更新后的状态：

帶你去聊聊MySQL中的事務隔離

语句更新生成的undo log（回滚日志）就是上图中的是哪个虚线箭头，而V1、V2、V3并不是物理上真实存在的，而是每次需要的时候根据当前版本和undo log计算出来的。比如，需要V2的时候，就是通过V4依次执行U3、U2算出来的

按照可重复读的定义，一个事务启动的时候，能够看到所以已经提交的事务结果。但是之后，这个事务执行期间，其他事务的更新对它不可见。在实现上，InnoDB为每个事务构造了一个数组，用来保存这个事务启动瞬间，当前在启动了但还没提交的所有事务ID。数组里面事务ID的最小值记为低水位，当前系统里面已经创建过的事务ID的最大值加1记为高水位。这个视图数组和高水位就组成了当前事务的一致性视图。而数据的可见性规则，就是基于数据的row trx_id和这个一致性视图的对比结果得到的

这个视图数组把所有的row trx_id分成了几种不同的情况

帶你去聊聊MySQL中的事務隔離

对于当前事务的启动瞬间来说，一个数据版本的row trx_id，有以下几种可能：

1）如果落在绿色部分，表示这个版本是已提交的事务或者是当前事务自己生成的，这个数据是可见的

2）如果落在红色部分，表示这个版本是由将来启动的事务生成的，肯定不可见

3）如果落在黄色部分，那就包括两种情况

若row trx_id在数组中，表示这个版本是由还没提交的事务生成的，不可见
若row trx_id不在数组中，表示这个版本是已经提交了的事务生成的，可见

InnoDB利用了所有数据都有多个版本的这个特性，实现了秒级创建快照的能力

2、为什么事务A的查询语句返回的结果是k=1？

假设：

1.事务A开始时，系统里面只有一个活跃事务ID是99

2.事务A、B、C的版本号分别是100、101、102

3.三个事务开始前，(1,1)这一行数据的row trx_id是90

這樣，事務A的是數組就是[99,100]，事務B的視圖數組是[99,100,101]，事務C的視圖數組是[99,100,101,102]

帶你去聊聊MySQL中的事務隔離

從上圖可以看到，第一個有效更新是事務C，從資料從(1,1)改成了(1,2)。這時候，這個資料的最新版本的row trx_id是102，而90這個版本已經成為了歷史版本

第二個有效更新是事務B，把資料從(1,2)改成了( 1,3)。這時候，這個資料的最新版本是101，而102又成為了歷史版本

在事務A查詢的時候，其實事務B還沒提交，但是它產生的(1,3)這個版本已經變成當前版本了。但這個版本對事務A必須是看不見的，否則就變成髒讀了

現在事務A要讀資料了，它的視圖數組是[99,100]。讀取資料都是從目前版本讀起的。所以，事務A查詢語句的讀取資料流程是這樣的：

找到(1,3)的時候，判斷出row trx_id=101，比高水位大，處於紅色區域，不可見
接著，找到上一個歷史版本，一看row trx_id=102，比高水位大，處於紅色區域，不可見
再往前找，終於找到了(1,1 )，它的row trx_id=90，比低水位小，處於綠色區域，可見

雖然期間這一行資料被修改過，但是事務A不論在什麼時候查詢，看到這行資料的結果都是一致的，我們稱之為一致性讀

一個資料版本，對於一個交易視圖來說，除了自己的更新總是可見以外，有三種情況：

版本未提交，不可見
版本已提交，但是是在視圖建立後提交的，不可見
版本已提交，而且是在視圖建立前提交的，可見

事務A的查詢語句的視圖陣列是在事務A啟動的時候產生的，這時候：

(1,3)還沒提交，屬於情況1，不可見
(1,2)雖然提交了，但是是在視圖數組創建之後提交的，屬於情況2，不可見
(1,1)是在視圖陣列建立之前提交的，可見

3、為什麼事務B的查詢語句回傳的結果是k=3？

帶你去聊聊MySQL中的事務隔離

事務B要去更新資料的時候，就不能再在歷史版本上更新了，否則交易C的更新就遺失了。因此，事務B此時的set k=k 1是在(1,2)的基礎上進行的操作

#更新資料都是先讀後寫的，而這個讀，只能讀取目前的值，稱為目前讀取。除了update語句外，select語句如果加鎖，也是當前讀

假設事務C不是馬上提交的，而是變成了下面的事務C’，會怎麼樣？
帶你去聊聊MySQL中的事務隔離
上圖中，交易C更新後沒有馬上提交，在它提交前，交易B的更新語句先發起了。雖然事務C還沒提交，但是(1,2)這個版本也已經生成了，並且是當前的最新版本

這時候涉及到了兩階段鎖協議，事務C沒提交，也就是說( 1,2)這個版本上的寫鎖還沒被釋放。而事務B是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務C釋放這個鎖，才能繼續它的當前讀

帶你去聊聊MySQL中的事務隔離

七、事務的可重複讀的能力是怎麼實現的？

可重複讀取的核心就是一致性讀；而交易更新資料的時候，只能用目前讀。如果目前的記錄的行鎖被其他交易佔用的話，就需要進入鎖等待

而讀提交的邏輯和可重複讀取的邏輯類似，它們最主要的區別是：

#在可重複讀取隔離等級下，只需要在交易開始的時候建立一致性視圖，之後交易裡的其他查詢都共用這個一致性視圖
在讀取提交隔離等級下，每一個語句執行前都會重複算出一個新的視圖

更多程式相關知識，請造訪：程式設計影片！！

以上是帶你去聊聊MySQL中的事務隔離的詳細內容。更多資訊請關注PHP中文網其他相關文章！

sql mysql select 线程并发 oracle 数据库

陳述：

本文轉載於：csdn.net。如有侵權，請聯絡admin@php.cn刪除

上一篇：深入了解MySQL中的join語句演算法及最佳化方法下一篇：深入了解MySQL中的join語句演算法及最佳化方法

看更多