上一篇文說明了transaction是如何確保資料在多筆對DB操作後仍保持正確,但隨著業務量增加,越來越多transaction實際應用在DB上,一定會遇到不同比transaction操作同一筆資料的狀況,進而衍伸出三個併發問題,就讓我來一一梳理這三個不同層級的問題,以及說明MySQL又提供哪些解法來應對
在說明情況前,先準備資料
use test;
CREATE TABLE account (
`id` INT(11) NOT NULL AUTO_INCREMENT,
`name` VARCHAR(50) NOT NULL,
money DOUBLE(10,2),
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
INSERT account (name, money) VALUES ('martin', 100), ('ted', 100);
併發問題
髒讀 dirty read
一個 transaction讀取到另一 transaction未 commit前的資料
在上圖中呈現髒讀的狀況,左邊的transaction在尚未commit的情況下,被右邊的transaction讀取到修改過的資料。
明明是還沒commit的結果,卻被讀取到,難怪叫髒讀也不是沒道理的
不可重複讀 non-repeatable read
一個transaction先後讀取同一筆資料,但兩次讀取的結果不相同
上圖呈現不可重複讀的情況,左邊的transaction修改資料並commit後,影響右邊的transaction兩次讀取同一筆資料,卻得到不同的結果。
讀取同一筆資料得到不同結果,難怪叫不可重複讀。
從上圖來看,不可重複讀與髒讀好像是一樣的,都是兩次讀取相同資料,但得到不同結果。然而細節上兩者是有差別的,髒讀的概念是讀取尚未commit的資料,而造成的資料不一致;不可重複讀是先後分別讀取commit前後的資料,而造成的資料不一致
幻讀 phantom read
一個transaction按照條件查詢資料時,沒找到對應的資料。但卻在插入資料時,發現資料已經存在,再次查詢時又無法查詢出來
乍看幻獨是三個狀況中最抽象的,直接上例子比較好理解
左邊transaction在右邊的transaction開始後插入一條,導致右邊的transaction第一次搜尋只得到2筆資料,確認id=3目前為空要插入資料時,卻又遇到系統拋出主鍵重複的錯誤,但第二次查詢依然只得到2筆資料
兩次的查詢與插入資料的行為彷彿遇到幻影資料,因此這種併發行為稱為幻讀
隔離層級
MySQL為了解決transaction併發衍伸的一致性問題,在資料庫中使用不同的transaction隔離級別,背後實現用到的是lock(鎖)的概念,細節就等下一篇文章再來介紹(無限的挖坑填坑orz)。系統預設的隔離層級為Repeatable Read
用select @@tx_isolation
或是select @@transaction_isolation
查看預設層級
總共有四個層級,一致性最高但效能最差為Serializable依序為Repeatable Read(默認)、Read committed、Read uncommitted
Read uncommitted
最低層級的Read uncommitted會遇到上述所講的三個併發問題:
- 讀取到另一個transaction尚未commit的資料--髒讀
- 第一次讀取到另一個transaction尚未commit的資料,第二次讀取相同資料,讀取到另一個transaction已commit的資料,造成兩次讀取相同資料,取得不同結果--不可重複讀
- 查詢時僅查到2筆資料,插入第三筆資料卻遇到主鍵重覆的錯誤--幻讀
Read committed
Read committed解決了髒讀的問題
但卻依然會遇到不可重複讀以及幻讀的狀況
- 左邊的transaction commit後,右邊的transaction依然遇到不可重複讀的問題
Repeatable Read(默認)
MySQL 默認的Repeatable Read級別則解決了髒讀和不可重複讀的狀況,但依然會遇到幻讀的問題
Serializable
要解決最難搞的幻讀,在隔離級別上只能出動最高層級Serializable
當使用最高的隔離層級啟動transaction時,該transaction所用到的資料表都會被鎖上,唯有該transaction成功commit或是rollback後,才會釋放掉該表的鎖,因此可以避免幻讀的情況