【Mysql】資料庫事務，髒讀、幻讀、不可重複讀

把蘋果v咬哭發表於2021-05-24
MySql資料庫

一、什麼是資料庫事務

資料庫事務( 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條測試資料。

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

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

執行sql。

  1. 開啟一個事務
START TRANSACTION -- 開啟一個事務

執行sql。

  1. 定義事務裡的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; -- 提交事務

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

相關文章