一.事物隔離級別

讀未提交(read uncommitted)是指，一個事務還沒提交時，它做的變更就能被別的事務看到.通俗理解,別人改資料的事務尚未提交，我在我的事務中也能讀到，
讀提交(read committed)是指，一個事務提交之后，它做的變更才會被其他事務看到，通俗理解,別人改資料的事務已經提交，我在我的事務中才能讀到，
可重復讀(repeatable read)是指，一個事務執行程序中看到的資料，總是跟這個事務在啟動時看到的資料是一致的，當然在可重復讀隔離級別下，未提交變更對其他事務也是不可見的，通俗理解,別人改資料的事務已經提交，我在我的事務中也不去讀，
串行化(serializable )，顧名思義是對于同一行記錄，“寫”會加“寫鎖”，“讀”會加“讀鎖”，當出現讀寫鎖沖突的時候，后訪問的事務必須等前一個事務執行完成，才能繼續執行，通俗理解,我的事務尚未提交，別人就別想改資料，

圖片示例講解

若隔離級別是“讀未提交”，則 V1 的值就是 2，這時候事務 B 雖然還沒有提交，但是結果已經被 A 看到了，因此，V2、V3 也都是 2，
若隔離級別是“讀提交”，則 V1 是 1，V2 的值是 2，事務 B 的更新在提交后才能被 A 看到，所以， V3 的值也是 2，
若隔離級別是“可重復讀”，則 V1、V2 是 1，V3 是 2，之所以 V2 還是 1，遵循的就是這個要求:事務在執行期間看到的資料前后必須是一致的，
若隔離級別是“串行化”，則在事務 B 執行“將 1 改成 2”的時候，會被鎖住，直到事務 A 提交后，事務 B 才可以繼續執行，所以從 A 的角度看， V1、V2 值是 1，V3 的值是 2，

在實作上，資料庫里面會創建一個視圖，訪問的時候以視圖的邏輯結果為準，在“可重復 讀”隔離級別下，這個視圖是在事務啟動時創建的，整個事務存在期間都用這個視圖， 在“讀提交”隔離級別下，這個視圖是在每個 SQL 陳述句開始執行的時候創建的，這里需要 注意的是，“讀未提交”隔離級別下直接回傳記錄上的最新值，沒有視圖概念;而“串行 化”隔離級別下直接用加鎖的方式來避免并行訪問，

二.查看mysql的隔離級別

mysql> show variables like 'transaction_isolation';
mysql默認級別: repeatable read

三.事務隔離的實作

每條記錄在更新的時候都會同時記錄一潭訓滾操作(也就是說redo log會記錄,undo log也會同時記錄).
記錄上的最新值，通過回滾操作，都可以得到前一個狀態的值，

回滾日志洗掉問題.

在不需要的時候才洗掉，也就是說，系統會判斷，當沒有事務再需要用到這些回滾日志時，回滾日志會被洗掉，
什么時候才不需要了呢?就是當系統里沒有比這個回滾日志更早的 read-view 的時候，

回滾日志存盤位置

在 MySQL 5.5 及以前的版本，回滾日志是跟資料字典一起放在 ibdata 檔案里的(系統表空間)，即使長 事務最終提交，回滾段被清理，檔案也不會變小，我見過資料只有 20GB，而回滾段有 200GB 的庫，最終只好為了清理回滾段，重建整個庫，

回滾流程

四.盡量不要使用長事務

長事務意味著系統里面會存在很老的事務視圖，由于這些事務隨時可能訪問資料庫里面的任何資料，所以這個事務提交之前，資料庫里面它可能用到的回滾記錄都必須保留，這就會導致大量占用存盤空間，
還占用鎖資源，也可能拖垮整個庫

詳解

比如，在某個時刻（今天上午9:00）開啟了一個事務A（對于可重復讀隔離級別，此時一個視圖read-view A也創建了），這是一個很長的事務……

事務A在今天上午9:20的時候，查詢了一個記錄R1的一個欄位f1的值為1……

今天上午9:25的時候，一個事務B（隨之而來的read-view B）也被開啟了，它更新了R1.f1的值為2（同時也創建了一個由2到1的回滾日志），這是一個短事務，事務隨后就被commit了，

今天上午9:30的時候，一個事務C（隨之而來的read-view C）也被開啟了，它更新了R1.f1的值為3（同時也創建了一個由3到2的回滾日志），這是一個短事務，事務隨后就被commit了，

……

到了下午3:00了，長事務A還沒有commit，為了保證事務在執行期間看到的資料在前后必須是一致的，那些老的事務視圖、回滾日志就必須存在了(read-view B,read-view C)，這就占用了大量的存盤空間，

源于此，我們應該盡量不要使用長事務，

在information_schema 庫的 innodb_trx 這個表中查詢長事務

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

五.事務的啟動方式

顯式啟動事務陳述句

begin 或 start transaction，
配套的提交陳述句是 commit，
回滾陳述句是 rollback，

set autocommit=0,會將執行緒的自動提交關掉.

意味著如果你只執行一個 select 陳述句，這個事務就啟動了，而且并不會自動提交，這個事務持續存在直到你主 動執行 commit 或 rollback 陳述句，或者斷開連接，

select也是事物

六.可重復讀隔離級詳細分析

可重復讀隔離級別下的更新和讀取

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);

注意的是事務的啟動時機，

在可重復讀RR隔離級別模式下，begin/start transaction 命令并不是一個事務的起點，在執行到它們之后的第一個操作 InnoDB 表的陳述句，事務才真正啟動，如果你想要馬上啟動一個事務，可以使用 start transaction with consistent snapshot 這個命令，

第一種啟動方式，begin/start transaction一致性視圖是在第執行第一個快照讀陳述句時創建的;
第二種啟動方式，start transaction with consistent snapshot一致性視圖是在執行 start transaction with consistent snapshot 時創建的，

上面圖1執行的結果是:

sessionB查詢到的k值為3
sessionA查詢到的k值為1
執行順序是,先執行sessionC更新,在執行sessionB更新和查詢,再執行sessionA的查詢
得到上面的執行結果的原因是什么呢,下面分析.

mysql中兩個視圖概念

view，它是一個用查詢陳述句定義的虛擬表，在呼叫的時候執行查詢陳述句并生成結果，創建視圖的語法是 create view ... ，而它的查詢方法與表一樣，
InnoDB 在實作 MVCC 時用到的一致性讀視圖，即 consistent read view，用于支持 RC(Read Committed，讀提交)和 RR(Repeatable Read，可重復讀)隔離級別的實作，它沒有物理結構，作用是事務執行期間用來定義“我能看到什么資料”，

快照在MVCC里是怎么作業的?

在可重復讀隔離級別下，事務在啟動的時候就“拍了個快照”，注意，這個快照是基于整庫的，
InnoDB 里面每個事務有一個唯一的事務 ID，叫作 transaction id，它是在事務開始的時候向 InnoDB 的事務系統申請的，是按申請順序嚴格遞增的，
每行資料也都是有多個版本的,涉及到transaction id
- 每次事務更新資料的時候，都會生成一個新的資料版本，并且把 transaction id 賦值給這個資料版本的事務 ID，記為 row trx_id
- 同時，舊的資料版本要保留，并且在新的資料版本中，能夠有資訊可以直接拿到它，
- 也就是說，資料表中的一行記錄，其實可能有多個版本 (row)，每個版本有自己的 row trx_id，
- 圖中虛線框里是同一行資料的 4 個版本，當前最新版本是 V4，k 的值是 22，它是被transaction id 為 25 的事務更新的，因此它的 row trx_id 也是 25，
- 前面的文章不是說，陳述句更新會生成 undo log(回滾日志)嗎?那么，undo log 在哪呢?
- 實際上，圖 2 中的三個虛線箭頭，就是 undo log;而 V1、V2、V3 并不是物理上真實存在的，而是每次需要的時候根據當前版本和 undo log 計算出來的，比如，需要 V2 的時候，就是通過 V4 依次執行 U3、U2 算出來，
按照可重復讀的定義，一個事務啟動的時候，能夠看到所有已經提交的事務結果，但是之后，這個事務執行期間，其他事務的更新對它不可見，
一個事務只需要在啟動的時候宣告說，“以我啟動的時刻為準，如果一個資料版本是在我啟動之前生成的，就認;如果是我啟動以后才生成的，我就不認，我必須要找到它的上一個版本”，
當然，如果“上一個版本”也不可見，那就得繼續往前找，還有，如果是這個事務自己更
新的資料，它自己還是要認的，
在實作上， InnoDB 為每個事務構造了一個陣列，用來保存這個事務啟動瞬間，當前正在“活躍”的所有事務 ID，“活躍”指的就是，啟動了但還沒提交，陣列里面事務 ID 的最小值記為低水位，當前系統里面已經創建過的事務 ID 的最大值加 1 記為高水位，這個視圖陣列和高水位，就組成了當前事務的一致性視圖(read-view)，
當開啟事務時，需要保存活躍事務的陣列（A），然后獲取高水位（B）兩者中間會不會產生新的事務?
- A和B之間在事務系統的鎖保護下做的，可以認為是原子操作，期間不能創建事務，
- 高水位不在視圖陣列里面,高水位應該就是屬于未來未開始事務了
- 事務A啟動時，當前活躍事務陣列包不包括自己的trx_id,因為如果是自己更新的，總是可見的
資料版本的可見性規則，就是基于資料的 row trx_id 和這個一致性視圖的對比結果得到的，這個視圖陣列把所有的 row trx_id 分成了幾種不同的情況，
- 對于當前事務的啟動瞬間來說，假設當前trx id為98 , 在當前事務開始后,計算活躍事務之前又產生了個新事務trx id為99沒有commit，假設活躍事務的id組成的資料為下面的陣列[80,88,99]，此時事務80/88/99為活躍事務，99為當前系統中事務最大ID, 高水位100是當前系統最大事務id99加1計算出來的,則會有以下幾種可能:
  1. 如果落在綠色部分，表示這個版本是已提交的事務或者是當前事務自己生成的，這個資料是可見的; 即80以前的事務都可見
  2. 如果落在紅色部分，表示這個版本是由將來啟動的事務生成的，是肯定不可見的; 100及100以后的事務都不可見
  3. 如果落在黃色部分，那就包括兩種情況
    
    a. 若 row trx_id 在陣列中，表示這個版本是由還沒提交的事務生成的，不可見; 80/88/99為活躍事務,不可見
    
    b. 若 row trx_id 不在陣列中，表示這個版本是已經提交了的事務生成的，可見，80~99中間,去除80/88/99,比如81等其余的是可見的.
InnoDB 利用了“所有資料都有多個版本”的這個特性，利用資料可見性規則實作了“秒級創建快照”的能力，

為什么會出現sessionB查詢到的k值為3，sessionA查詢到的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]，

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

第二個有效更新是事務 B，把資料從 (1,2) 改成了 (1,3)，這時候，這個資料的最新版本 (即 row trx_id)是 101，而 102 又成為了歷史版本，{備注:按理說事務B是[99,100,101],此時找到(1,2)的時候判斷出row trx_id=102,比它自己的高水位大,處于紅色區域,不可見,應該往前找,找(1,1)版本,但是此時它卻是找的(1,2)row trx_id=102的版本,這是什么原因的,是因為更新都是“當前讀”(current read),當前讀這個概念下面解釋}

你可能注意到了，在事務 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 不論在什么時候查詢，看到這 行資料的結果都是一致的，所以我們稱之為一致性讀，

上面的分析判斷規則是從代碼邏輯直接轉譯過來的，一個資料版本，對于一個事務視圖來說，除了自己的更新總是可見以外，有三種情況:
```
1. 版本未提交，不可見;
2. 版本已提交，但是是在視圖創建后提交的，不可見;
3. 版本已提交，而且是在視圖創建前提交的，可見，
```

更新邏輯

事務 B 的 update 陳述句，如果按照一致性讀，好像結果不對哦?事務 B 的視圖陣列是先生成的，之后事務 C 才提交，不是應該看不見 (1,2) 嗎，怎么能算出 (1,3) 來?

是的，如果事務 B 在更新之前查詢一次資料，這個查詢回傳的 k 的值確實是 1，
但是，當它要去更新資料的時候，就不能再在歷史版本上更新了，否則事務 C 的更新就丟失了，因此，事務 B 此時的 set k=k+1 是在(1,2)的基礎上進行的操作，
這里就用到了這樣一條規則: 更新資料都是先讀后寫的，而這個讀，只能讀當前的值，稱為“當前讀”(current read)，
因此，在更新的時候，當前讀拿到的資料是 (1,2)，更新后生成了新版本的資料 (1,3)，這個新版本的 row trx_id 是 101，
所以，在執行事務 B 查詢陳述句的時候，一看自己的版本號是 101，最新資料的版本號也是 101，是自己的更新，可以直接使用，所以查詢得到的 k 的值是 3，
除了 update 陳述句外，select 陳述句如果加鎖，也是當前讀，
- mysql> select k from t where id=1 lock in share mode; 讀鎖(S 鎖，共享鎖)
- mysql> select k from t where id=1 for update; 寫鎖(X 鎖，排他鎖)

假設事務 C 不是馬上提交的，而是變成了下面的事務 C’

事務 C’的不同是，更新后并沒有馬上提交，在它提交前，事務 B 的更新陳述句先發起了，前面說過了，雖然事務 C’還沒提交，但是 (1,2) 這個版本也已經生成了，并且是當前的最新版本，那么，事務 B 的更新陳述句會怎么處理呢?

上一篇文章中提到的“兩階段鎖協議”就要上場了
事務 C’ 沒提交，也就是說 (1,2) 這個版本上的寫鎖還沒釋放，而事務 B 是當前讀，必須要讀最新版本，而且必須加鎖，因此就被鎖住了，必須等到事務 C’釋放這個鎖，才能繼續它的當前讀，
這樣一致性讀、當前讀和行鎖就串起來了.在一致性讀的環境下,事務C' 執行更新,此時C'沒有commit，事務B就開啟了,因為事務B要進行當前讀，獲取最新的資訊，讀的時候要加鎖(讀完立馬更新)，但是此時事務C'還沒有commit，鎖(行鎖)還沒釋放，所以事務B需要等待事務C'釋放鎖之后才能獲取鎖，然后才能執行當前讀，讀到事務B更新了的(1,2)，既而更新為(1,3)，同時因為(1,3)是事務B自身更新的，所以事務B在查詢id=1的值時，自然而然的就查到了k為3. 但是對于事務A來說，查詢的時候，因為事務C'和事務B的更新都是在事務A開始之后，所以對于事務A都不可見，所以事務A讀取到的值為1. 上面的分析同樣適用于事務A/B/C

可重復讀隔離級別RR核心

核心就是一致性讀(consistent read)，正式因為一致性讀的原因，所以本事務開始之后，就算其他事務更新了相關的值，此時本事務還是能查到本事務開始之前的值，而不是其他事務更新后的值.
讀提交的邏輯和可重復讀的邏輯類似，它們最主要的區別是
- 在可重復讀隔離級別下，只需要在事務開始的時候創建一致性視圖，之后事務里的其他查詢都共用這個一致性視圖;
- 在讀提交隔離級別下，每一個陳述句執行前都會重新算出一個新的視圖，

讀提交RC隔離級別

start transaction with consistent snapshot; 在都提交下與start transaction等效.

事務 A 的查詢陳述句的視圖陣列是在執行這個陳述句的時候創建的，時序上 (1,2)、(1,3) 的生成時間都在創建這個視圖陣列的時刻之前，
但是(1,3) 還沒提交，屬于情況 1，不可見; (1,2) 提交了，屬于情況 3，可見，
所以，這時候事務 A 查詢陳述句回傳的是 k=2，
顯然地，事務 B 查詢結果 k=3，

站在巨人的肩膀上摘蘋果:

https://time.geekbang.org/column/intro/100020801

轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/74696.html

標籤：MySQL

上一篇：mysql兩個重要的日志redolog和binlog

下一篇：MySQL中的函式索引（Generated Column）及一次SQL優化

mysql中的事務隔離級別及可重復讀讀提交詳細分析(mvcc多版本控制/undo log)