Mysql資料庫事務的髒讀幻讀及不可重複讀詳解

2022-05-30 14:02:07

一、什麼是資料庫事務

資料庫事務( transaction)是存取並可能操作各種資料項的一個資料庫操作序列，這些操作要麼全部執行,要麼全部不執行，是一個不可分割的工作單位。事務由事務開始與事務結束之間執行的全部資料庫操作組成。——百度百科

比如，你有2條sql要執行，如果放到一個事務裡，要麼2個sql都執行成功，要麼都失敗。都執行成功了就提交事務，有一個失敗了就回滾，不存在一個成功一個失敗。

二、事務的ACID原則

這是資料庫事務的核心所在。

1. 原子性(Atomicity)

比如現在A有800元，B有200元，A給B轉賬200元。完成此場景有2步，可以當做在一個事務裡：

1- A：800-200=600
2- B：200+200=400

那麼，這2個步驟只能都成功，或者都失敗。如果一個成功一個失敗了，那麼有一個人的錢就不對了。原子性就是表示不能只發生其中一個動作。

2. 一致性(Consistency)

針對一個事務操作前與操作後的狀態一致。

比如現在A有800元，B有200元，2個人總計是1000元。那麼不管這2個人之間怎麼轉來轉去，總和一定還是1000元，錢不會憑空產生或消失。

3. 永續性(Durability)

對於任意已提交事務，系統必須保證該事務對資料庫的改變不被丟失，即使資料庫出現故障。

比如現在A有800元，B有200元，此時A要給B轉賬200，或有2種情況：

1. 事務還沒提交，這時候服務掛了或者斷電，那麼重啟資料庫後，資料狀態應該為：A有800元，B有200元

2. 事務已經提交，這時候服務掛了或者斷電，那麼重啟資料庫後，資料狀態應該為：A有600元，B有400元

可以看到，事務一旦提交，就會持久化到資料庫裡，不會因外界原因導致資料丟失。

4. 隔離性(Isolation)

事務的執行不受其他事務的干擾，事務執行的中間結果對其他事務必須是透明的。

比如現在有2個事務同時進行，A和C同時在給B轉賬：

事務一：A有800元，B有200元，A給B轉賬200元

事務二：C有1000元，B有200元，C給B轉賬100元

這2個事務不會互相影響。隔離性就是針對多使用者同時操作的情況下，排除其他事務對本事務的影響。

三、隔離帶來的問題

資料庫的事務隔離級別有4個，強度從低到高依次為：
Read uncommitted 、Read committed 、Repeatable read、Serializable，而隨著隔離級別的不同，會引發一些其他的問題。

1. 髒讀

一個事務讀取了另外一個事務未提交的資料，就是髒讀。

事務1： A給B轉賬500，但是事務未提交。

事務2： B檢視了賬戶，發現A轉過來500，本來只轉300過來就好，發現多轉了200，心裡美滋滋。。。

事務1： A及時發現多轉了200，修改了轉300，提交事務。

最終，B再次檢視賬戶的時候發現還是隻多了300塊，白高興一場，這種就是髒讀。當隔離級別設定為Read uncommitted時可能會出現該情況。
若避開髒讀，可以設定隔離級別為Read committed。

2. 不可重複讀

一個事務先後讀取同一條記錄，而事務在兩次讀取之間該資料被其它事務所修改，則兩次讀取的資料不同，這種就是不可重複讀。

事務1：B去買東西，卡里有500塊錢，消費100，還沒提交事務。

事務2：B的老婆把B的500塊錢轉出去了，已提交事務。

事務1：B此時提交事務，支付不了。再次讀取發現卡里沒錢支付。

當隔離級別設定為Read committed，可以避免髒讀，但是可能會造成不可重複讀。
若避開不可重複讀，可以設定隔離級別為Repeatable read。

3.幻讀

一個事務按相同的查詢條件重新讀取以前檢索過的資料，卻發現其他事務插入了滿足其查詢條件的新資料，這種現象就稱為幻讀。

事務1：B的老婆檢視B的卡消費記錄，目前共消費了500元。

事務2：B此時剛在外面請朋友吃完飯，付款了100，事務已提交。

B的老婆決定把賬單列印出來，晚上跟B對賬，卻發現列印出來的消費為600元。她剛才明明看到是500，怎麼是600，難道是幻覺？

Mysql的預設隔離級別為Repeatable read，可以避免不可重複讀，但是可能出現幻讀的情況。

如果要繼續解決幻讀，那麼可以將隔離級別設定為最高階的Serializable，這時候事務都是按照順序執行的，髒讀、幻讀、不可重複度都可以避免，但是效能很差。

四、手動測試下事務的過程

可以在mysql裡手動去執行事務提交的過程，輔助理解。現在來模擬一個轉賬的過程，A給B轉賬500。

先創造下測試條件，造庫、表、資料。

-- 建立庫
CREATE DATABASE shop CHARACTER SET utf8 COLLATE utf8_general_ci;
-- 使用庫
USE shop;
-- 建立表
CREATE TABLE `account`(
	`id` INT(3) NOT NULL AUTO_INCREMENT,
	`name` VARCHAR(30) NOT NULL,
	`money` DECIMAL(9,2) NOT NULL,
	PRIMARY KEY (`id`)
)ENGINE=INNODB DEFAULT CHARSET=utf8;
-- 插入資料
INSERT INTO account(`name`,`money`)
VALUES ('A', 2000.00),('B', 10000.00)

上述sql都執行完即可，現在有2條測試資料。

接下來手動執行事務提交的過程。

關閉自動提交

SET autocommit = 0; -- 關閉自動提交，預設是開啟

執行sql。

開啟一個事務

START TRANSACTION -- 開啟一個事務

執行sql。

定義事務裡的sql
開啟事務後的sql，就是定義在一個事務裡了。

UPDATE account SET money=money - 500 WHERE `name` = 'A' -- A減去500
UPDATE account SET money=money - 500 WHERE `name` = 'B' -- B增加500

執行後，資料變更。A加了500，B少了500。

現在我不去提交，進行回滾。

ROLLBACK; -- 回滾

資料變回最開始的樣子。

重新執行2條sql，並且提交事務。

UPDATE `shop`.`account` SET `money`=`money` - 500 WHERE `name` = 'A'; -- A減去500
UPDATE `shop`.`account` SET `money`=`money` + 500 WHERE `name` = 'B'; -- B增加500
COMMIT; -- 提交事務

資料修改成功，此時再次執行回滾，資料已經不可逆了。