MySQL的可重複讀級別能解決幻讀嗎

寧願。發表於2019-03-19
MySql

引言

之前在深入瞭解資料庫理論的時候，瞭解到事務的不同隔離級別可能存在的問題。為了更好的理解所以在MySQL資料庫中測試復現這些問題。關於髒讀和不可重複讀在相應的隔離級別下都很容易的復現了。但是對於幻讀，我發現在可重複讀的隔離級別下沒有出現，當時想到難道是MySQL對幻讀做了什麼處理？

測試：

建立一張測試用的表dept：

CREATE TABLE `dept` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=12 DEFAULT CHARSET=utf8

insert into dept(name) values("後勤部")
複製程式碼
事務 1 事務 2
begin begin
select * from dept
- insert into dept(name) values("研發部")
- commit
select * from dept
commit

根據上面的流程執行，預期來說應該是事務1的第一條select查詢出一條資料，第二個select查詢出兩條資料(包含事務2提交的資料)。

但是在實際測試中發現第二條select實際上也只查詢處理一條資料。這是但是根據資料庫理論的可重複讀的實現(排他鎖和共享鎖)這是不應該的情況。

在瞭解實際原因前我們先複習下事務的相關理論。

資料庫原理理論

事務

事務(Transaction)，一般是指要做的或所做的事情。在計算機術語中是指訪問並可能更新資料庫中各種資料項的一個程式執行單元(unit)。事務由事務開始(begin transaction)和事務結束(end transaction)之間執行的全體操作組成。在關聯式資料庫中，一個事務可以是一組SQL語句或整個程式。

為什麼要有事務

一個資料庫事務通常包含對資料庫進行讀或寫的一個操作序列。它的存在包含有以下兩個目的：

  1. 為資料庫操作提供了一個從失敗中恢復到正常狀態的方法，同時提供了資料庫在異常狀態下仍能保持一致性的方法。

  2. 當多個應用程式在併發訪問資料庫時，可以在這些應用程式之間提供一個隔離方法，保證彼此的操作互相干擾。

事務特性

事務具有4個特性：原子性、一致性、隔離性、永續性。這四個屬性通常稱為 ACID 特性。

  • 原子性（atomicity）： 一個事務應該是一個不可分割的工作單位，事務中包括的操作要麼都成功，要麼都不成功。

  • 一致性（consistency）： 事務必須是使資料庫從一個一致性狀態變到另一個一致性狀態。一致性與原子性是密切相關的。

  • 隔離性（isolation）： 一個事務的執行不能被其他事務干擾。即一個事務內部的操作及使用的資料在事務未提交前對併發的其他事務是隔離的，併發執行的各個事務之間不能互相影響。

  • 永續性（durability）： 一個事務一旦成功提交，它對資料庫中資料的改變就應該是永久性的。接下來的其他操作或故障不應該對其有任何影響。

事務之間的幾個特性並不是一組同等的概念：

如果在任何時刻都只有一個事務，那麼其天然是具有隔離性的，這時只要保證原子性就能具有一致性。

如果存在併發的情況下，就需要保證原子性和隔離性才能保證一致性。

資料庫併發事務中存在的問題

如果不考慮事務的隔離性，會發生以下幾種問題：

  • 髒讀：髒讀是指在一個事務處理過程裡讀取了另一個未提交的事務中的資料。當一個事務正在多次修改某個資料，而在這個事務中這多次的修改都還未提交，這時一個併發的事務來訪問該資料，就會造成兩個事務得到的資料不一致。

  • 不可重複讀：不可重複讀是指在對於資料庫中的某條資料，一個事務範圍內多次查詢返回不同的資料值(這裡不同是指某一條或多條資料的內容前後不一致，但資料條數相同)，這是由於在查詢間隔，該事務需要用到的資料被另一個事務修改並提交了。不可重複讀和髒讀的區別是，髒讀是某一事務讀取了另一個事務未提交的髒資料，而不可重複讀則是讀取了其他事務提交的資料。需要注意的是在某些情況下不可重複讀並不是問題。

  • 幻讀：幻讀是事務非獨立執行時發生的一種現象。例如事務T1對一個表中所有的行的某個資料項做了從“1”修改為“2”的操作，這時事務T2又對這個表中插入了一行資料項，而這個資料項的數值還是為“1”並且提交給資料庫。而操作事務T1的使用者如果再檢視剛剛修改的資料，會發現還有一行沒有修改，其實這行是從事務T2中新增的，就好像產生幻覺一樣，這就是發生了幻讀。幻讀和不可重複讀都是讀取了另一條已經提交的事務(這點就髒讀不同)，所不同的是不可重複讀可能發生在update,delete操作中，而幻讀發生在insert操作中。

排他鎖，共享鎖

排它鎖（Exclusive），又稱為X 鎖，寫鎖。

共享鎖（Shared），又稱為S 鎖，讀鎖。

讀寫鎖之間有以下的關係：

  • 一個事務對資料物件O加了 S 鎖，可以對 O進行讀取操作，但是不能進行更新操作。加鎖期間其它事務能對O 加 S 鎖，但是不能加 X 鎖。
  • 一個事務對資料物件 O 加了 X 鎖，就可以對 O 進行讀取和更新。加鎖期間其它事務不能對 O 加任何鎖。

即讀寫鎖之間的關係可以概括為：多讀單寫

事務的隔離級別

在事務中存在以下幾種隔離級別：

  • 讀未提交(Read Uncommitted)：解決更新丟失問題。如果一個事務已經開始寫操作，那麼其他事務則不允許同時進行寫操作，但允許其他事務讀此行資料。該隔離級別可以通過“排他寫鎖”實現，即事務需要對某些資料進行修改必須對這些資料加 X 鎖，讀資料不需要加 S 鎖。

  • 讀已提交(Read Committed)：解決了髒讀問題。讀取資料的事務允許其他事務繼續訪問該行資料，但是未提交的寫事務將會禁止其他事務訪問該行。這可以通過“瞬間共享讀鎖”和“排他寫鎖”實現， 即事務需要對某些資料進行修改必須對這些資料加 X 鎖，讀資料時需要加上 S 鎖，當資料讀取完成後立刻釋放 S 鎖，不用等到事務結束。

  • 可重複讀取(Repeatable Read)：禁止不可重複讀取和髒讀取，但是有時可能出現幻讀資料。讀取資料的事務將會禁止寫事務(但允許讀事務)，寫事務則禁止任何其他事務。Mysql預設使用該隔離級別。這可以通過“共享讀鎖”和“排他寫鎖”實現，即事務需要對某些資料進行修改必須對這些資料加 X 鎖，讀資料時需要加上 S 鎖，當資料讀取完成並不立刻釋放 S 鎖，而是等到事務結束後再釋放。

  • 序列化(Serializable)：解決了幻讀的問題的。提供嚴格的事務隔離。它要求事務序列化執行，事務只能一個接著一個地執行，不能併發執行。僅僅通過“行級鎖”是無法實現事務序列化的，必須通過其他機制保證新插入的資料不會被剛執行查詢操作的事務訪問到。

MySQL中的隔離級別的實現

上面的內容解釋了一些資料庫理論的概念，但是在MySQL、ORACLE這樣的資料庫中，為了效能的考慮並不是完全按照上面介紹的理論來實現的。

MVCC

多版本併發控制(Multi-Version Concurrency Control, MVCC)是MySQL中基於樂觀鎖理論實現隔離級別的方式，用於實現讀已提交和可重複讀取隔離級別的實現。

實現(隔離級別為可重複讀)

在說到如何實現前先引入兩個概念：

系統版本號：一個遞增的數字，每開始一個新的事務，系統版本號就會自動遞增。

事務版本號：事務開始時的系統版本號。

在MySQL中，會在表中每一條資料後面新增兩個欄位：

建立版本號：建立一行資料時，將當前系統版本號作為建立版本號賦值

刪除版本號：刪除一行資料時，將當前系統版本號作為刪除版本號賦值

SELECT

select時讀取資料的規則為：建立版本號<=當前事務版本號，刪除版本號為空或>當前事務版本號。

建立版本號<=當前事務版本號保證取出的資料不會有後啟動的事務中建立的資料。這也是為什麼在開始的示例中我們不會查出後來新增的資料的原因

刪除版本號為空或>當前事務版本號保證了至少在該事務開啟之前資料沒有被刪除，是應該被查出來的資料。

INSERT

insert時將當前的系統版本號賦值給建立版本號欄位。

UPDATE

插入一條新紀錄，儲存當前事務版本號為行建立版本號，同時儲存當前事務版本號到原來刪除的行，實際上這裡的更新是通過delete和insert實現的。

DELETE

刪除時將當前的系統版本號賦值給刪除版本號欄位，標識該行資料在那一個事務中會被刪除，即使實際上在位commit時該資料沒有被刪除。根據select的規則後開啟懂資料也不會查詢到該資料。

MVCC真的解決了幻讀？

從最開始我們的測試示例和上面的理論支援來看貌似在MySQL中通過MVCC就解決了幻讀的問題，那既然這樣序列化讀貌似就沒啥意義了，帶著疑問繼續測試。

測試前資料：

測試前資料

事務 1 事務 2
begin begin
select * from dept
- insert into dept(name) values("研發部")
- commit
update dept set name="財務部"(工作中如果不想被辭退一定要寫where條件)
commit

根據上面的結果我們期望的結果是這樣的：

id  name
1   財務部
2   研發部
複製程式碼

但是實際上我們的經過是：

測試後資料

本來我們希望得到的結果只是第一條資料的部門改為財務，但是結果確實兩條資料都被修改了。這種結果告訴我們其實在MySQL可重複讀的隔離級別中並不是完全解決了幻讀的問題，而是解決了讀資料情況下的幻讀問題。而對於修改的操作依舊存在幻讀問題，就是說MVCC對於幻讀的解決時不徹底的。

快照讀和當前讀

出現了上面的情況我們需要知道為什麼會出現這種情況。在查閱了一些資料後發現在RR級別中，通過MVCC機制，雖然讓資料變得可重複讀，但我們讀到的資料可能是歷史資料，不是資料庫最新的資料。這種讀取歷史資料的方式，我們叫它快照讀 (snapshot read)，而讀取資料庫最新版本資料的方式，叫當前讀 (current read)。

select 快照讀

當執行select操作是innodb預設會執行快照讀，會記錄下這次select後的結果，之後select 的時候就會返回這次快照的資料，即使其他事務提交了不會影響當前select的資料，這就實現了可重複讀了。快照的生成當在第一次執行select的時候，也就是說假設當A開啟了事務，然後沒有執行任何操作，這時候B insert了一條資料然後commit,這時候A執行 select，那麼返回的資料中就會有B新增的那條資料。之後無論再有其他事務commit都沒有關係，因為快照已經生成了，後面的select都是根據快照來的。

當前讀

對於會對資料修改的操作(update、insert、delete)都是採用當前讀的模式。在執行這幾個操作時會讀取最新的記錄，即使是別的事務提交的資料也可以查詢到。假設要update一條記錄，但是在另一個事務中已經delete掉這條資料並且commit了，如果update就會產生衝突，所以在update的時候需要知道最新的資料。也正是因為這樣所以才導致上面我們測試的那種情況。

select的當前讀需要手動的加鎖：

select * from table where ? lock in share mode;
select * from table where ? for update;
複製程式碼

有個問題說明下

在測試過程中最開始我以為使用begin語句就是開始一個事務了，所以在上面第二次測試中因為先開始的事務1，結果在事務1中卻查到了事務2新增的資料，當時認為這和前面MVCC中的select的規則不一致了，所以做了如下測試：

image

SELECT * FROM information_schema.INNODB_TRX //用於查詢當前正在執行中的事務
複製程式碼

可以看到如果只是執行begin語句實際上並沒有開啟一個事務。

下面在begin後新增一條select語句：

事務2

所以要明白實際上是對資料進行了增刪改查等操作後才開啟了一個事務。

如何解決幻讀

很明顯可重複讀的隔離級別沒有辦法徹底的解決幻讀的問題，如果我們的專案中需要解決幻讀的話也有兩個辦法：

  • 使用序列化讀的隔離級別
  • MVCC+next-key locks：next-key locks由record locks(索引加鎖) 和 gap locks(間隙鎖，每次鎖住的不光是需要使用的資料，還會鎖住這些資料附近的資料)

實際上很多的專案中是不會使用到上面的兩種方法的，序列化讀的效能太差，而且其實幻讀很多時候是我們完全可以接受的。

相關文章