快捷導(dǎo)航

MySQL學(xué)習(xí)之事務(wù)詳解

更新時間：2022年12月08日 09:32:07 作者：榮·

在數(shù)據(jù)庫中?事務(wù)(transaction)?可以把多個SQL給打包到一起,?即將多個SQL語句變成一個整體,?也就是說一個事務(wù)中的所有操作要么全部成功執(zhí)行,?要么完全不執(zhí)行.本文主要來和大家聊聊事務(wù)的使用，需要的可以參考一下

一. 事務(wù)的業(yè)務(wù)場景

在數(shù)據(jù)庫中事務(wù)(transaction) 可以把多個SQL給打包到一起, 即將多個SQL語句變成一個整體, 也就是說一個事務(wù)中的所有操作要么全部成功執(zhí)行, 要么完全不執(zhí)行.

通過實(shí)際場景來理解事務(wù):

實(shí)際生活中我們經(jīng)常涉及轉(zhuǎn)帳操作, 張三給李四轉(zhuǎn)賬2000元, 涉及到兩個操作

給張三的賬戶余額減去2000元
給李四的賬戶余額增加2000元

這里就要考慮到這兩個操作的完整性, 也就是不能出現(xiàn)張三的賬戶余額減少了2000元, 但李四的賬戶余額未發(fā)生變化, 這就要求上面的兩個操作要么全部執(zhí)行完成功轉(zhuǎn)賬, 要么一個都不執(zhí)行雙方都沒有損失, 不會出現(xiàn)中途發(fā)生一些問題導(dǎo)致數(shù)據(jù)不一致的情況.

這樣的一次完整操作叫做事務(wù)(transaction), 一個事務(wù)中的所有操作要么全部成功執(zhí)行, 要么完全不執(zhí)行.

二. 事務(wù)的使用

事務(wù)是如何保證操作的完整性的呢?

其實(shí)事務(wù)執(zhí)行中間出錯了, 只需要讓事務(wù)中的這些操作恢復(fù)成之前的樣子即可, 這里涉及到的一個操作, 回滾(rollback).

事務(wù)處理是一種對必須整批執(zhí)行的 MySQL 操作的管理機(jī)制, 在事務(wù)過程中, 除非整批操作全部正確執(zhí)行, 否則中間的任何一個操作出錯, 都會回滾 (rollback)到最初的安全狀態(tài)以確保不會對系統(tǒng)數(shù)據(jù)造成錯誤的改動.

相關(guān)語法:

-- 開啟事務(wù)
start transaction;

-- 若干條執(zhí)行sql

-- 提交/回滾事務(wù)
commit/rollback;

注意:

在開啟事務(wù)之后, 執(zhí)行sql不會立即去執(zhí)行, 只有等到commit操作后才會統(tǒng)一執(zhí)行(保證原子性).

示例:

首先創(chuàng)建一個賬戶表并初始化數(shù)據(jù)

-- 創(chuàng)建一個賬戶表
create table account(
     id int primary key auto_increment,
     name varchar(20),
     money double(10,2)
);
-- 初始化賬戶信息
insert into account(name, money) values ('張三', 10000), ('李四', 10000);

首先看正常情況下的轉(zhuǎn)賬操作

-- 張三賬戶 -2000
mysql> update account set money = money - 2000 where name = '張三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四賬戶 +2000
mysql> update account set money = money + 2000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 轉(zhuǎn)賬成功
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 張三   |  8000.00 |
|  2 | 李四   | 12000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

如果操作中出現(xiàn)異常情況, 比如sql語句中所寫的注釋格式錯誤導(dǎo)致sql執(zhí)行中斷.

-- 先將張三和李四的賬戶余額恢復(fù)為10000元
update account set money = 10000 where name = '張三';
update account set money = 10000 where name = '李四';
-- 張三 -2000
mysql> update account set money = money - 2000 where name = '張三';
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四 +2000
mysql> 沒加--的注釋
    -> update account set money = money + 2000 where name = '李四';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '沒加--的注釋
update account set money = money + 2000 where name = '李四'' at line 1
-- 出現(xiàn)異常
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 張三   |  8000.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

觀察結(jié)果發(fā)現(xiàn)了張三的賬戶少了2000元, 但李四的賬戶余額并沒有增加, 在實(shí)際操作中這種涉及錢的操作發(fā)生這種失誤可能會造成很大的損失.

為了防止這種失誤的出現(xiàn)我們就可以使用事務(wù)來打包這些操作.

-- 先將張的賬戶余額恢復(fù)為10000元
update account set money = 10000 where name = '張三';
-- 開啟事務(wù)
start transaction;
-- 張三 -2000
mysql> update account set money = money - 2000 where name = '張三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四 -2000
mysql> 沒加--的注釋
    -> update account set money = money + 2000 where name = '李四';
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '沒加--的注釋
update account set money = money + 2000 where name = '李四'' at line 1
-- 預(yù)期結(jié)果
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 張三   |  8000.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

觀察這里的結(jié)果發(fā)現(xiàn)在當(dāng)前的數(shù)據(jù)庫用戶查詢到的account表中的賬戶余額發(fā)生了變化，但開啟了事務(wù)之后在commit之前只是臨時的預(yù)操作并不會真的去修改表中的數(shù)據(jù);

可以退出數(shù)據(jù)庫再打開重新查詢表中數(shù)據(jù)或者切換用戶去查詢?nèi)ヲ?yàn)證表中數(shù)據(jù)是否發(fā)生改變, 這里就不作演示了.

發(fā)現(xiàn)操作結(jié)果異常之后, 當(dāng)前用戶需要恢復(fù)到事務(wù)之前的狀態(tài), 即進(jìn)行回滾操作.

-- 回滾事務(wù)
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)
-- 驗(yàn)證回滾后的狀態(tài)
mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 張三   | 10000.00 |
|  2 | 李四   | 10000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

如果開啟事務(wù)之后發(fā)現(xiàn)預(yù)操作的結(jié)果是預(yù)期的效果, 此時我們就可以提交事務(wù), 當(dāng)我們提交完事務(wù)之后, 數(shù)據(jù)就是真的修改了, 也就是硬盤中存儲的數(shù)據(jù)真的改變了.

-- 開啟事務(wù)
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)
-- 張三 -2000
mysql> update account set money = money - 2000 where name = '張三';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 李四 +2000
mysql> update account set money = money + 2000 where name = '李四';
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0
-- 提交事務(wù)
mysql> commit;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from account;
+----+--------+----------+
| id | name   | money    |
+----+--------+----------+
|  1 | 張三   |  8000.00 |
|  2 | 李四   | 12000.00 |
+----+--------+----------+
2 rows in set (0.00 sec)

要注意事務(wù)也不是萬能的, 不能保證你刪表刪庫之后可以完全恢復(fù), 只是在適量的數(shù)據(jù)和操作下使用事務(wù)可以避免一些問題.

回滾(rollback)操作, 實(shí)際上是我們把事務(wù)中的操作再進(jìn)行逆操作, 前面是插入, 回滾就是刪除…

這些操作是有很大開銷的, 可以保存, 但不能夠無限保存, 最多是將正再執(zhí)行的事務(wù)保存下來, 額外的內(nèi)容就不好再保存了; 數(shù)據(jù)庫要是有幾十億條數(shù)據(jù), 占據(jù)了幾百G硬盤空間, 不可能去花費(fèi)幾個T甚至更多的空間用來記錄這些數(shù)據(jù)是如何來的.

三. 事務(wù)的特性(ACID)

1. 原子性(Atomicity)

一個事務(wù)是一個不可分割的最小單位, 事務(wù)中的所有操作要么全部成功, 要么全部失敗, 沒有中間狀態(tài).

原子性主要是通過事務(wù)日志中的回滾日志(undo log)來實(shí)現(xiàn)的, 當(dāng)事務(wù)對數(shù)據(jù)庫進(jìn)行修改時, InnoDB 會根據(jù)操作生成相反操作的 undo log, 比如說對 insert 操作, 會生成 delete 記錄, 如果事務(wù)執(zhí)行失敗或者調(diào)用了 rollback，就會根據(jù) undo log 的內(nèi)容恢復(fù)到執(zhí)行之前的狀態(tài).

事務(wù)的原子性, 也是事務(wù)的核心特性, 是事務(wù)的初心.

2. 一致性(Consistency)

事務(wù)執(zhí)行之前和執(zhí)行之后數(shù)據(jù)都是合法的一致性狀態(tài), 即使發(fā)生了異常, 也不會因?yàn)楫惓Ｒ茐臄?shù)據(jù)庫的完整性約束, 比如唯一性約束等.

事務(wù)執(zhí)行前/執(zhí)行后, 都得是數(shù)據(jù)合法的狀態(tài); 比如像上面的轉(zhuǎn)賬, 不能說轉(zhuǎn)的過程出錯了, 導(dǎo)致出現(xiàn)錢轉(zhuǎn)丟了的情況.

3. 持久性(Durability)

事務(wù)提交之后對數(shù)據(jù)的修改是持久性的, 即使數(shù)據(jù)庫宕機(jī)也不會丟失, 通過事務(wù)日志中的重做日志(redo log)來保證; 事務(wù)修改之前, 會先把變更信息預(yù)寫到 redo log 中, 如果數(shù)據(jù)庫宕機(jī), 恢復(fù)后會讀取 redo log 中的記錄來恢復(fù)數(shù)據(jù)(回滾).

事務(wù)產(chǎn)生的修改, 都是會寫入硬盤的, 程序重啟/主機(jī)重啟/掉電, 事務(wù)都可以正常工作, 保證修改是生效的.

4. 隔離性(Isolation)

這里的隔離性是指一個數(shù)據(jù)庫服務(wù)器, 同時執(zhí)行多個事務(wù)的時候, 事務(wù)之間的相互影響程度.

一個服務(wù)器, 可以同時給多個客戶端提供服務(wù), 這多個客戶端是并發(fā)執(zhí)行的關(guān)系, 多個客戶端就會有多個事務(wù), 多個事務(wù)同時去操作一個表的時候, 特別容易出現(xiàn)互相影響的問題.

如果隔離性越高, 就意味著事務(wù)之間的并發(fā)程度越低, 執(zhí)行效率越慢, 但是數(shù)據(jù)準(zhǔn)確性越高.

如果隔離性越低, 就意味著事務(wù)之間的并發(fā)程度越高, 執(zhí)行效率越快, 但是數(shù)據(jù)準(zhǔn)確性越低.

隔離性通過事務(wù)的隔離級別來定義, 并用鎖機(jī)制來保證寫操作的隔離性, 用 MVCC 來保證讀操作的隔離性.

四. 事務(wù)并發(fā)異常

在實(shí)際生產(chǎn)環(huán)境下, 可能會出現(xiàn)大規(guī)模并發(fā)請求的情況, 如果沒有妥善的設(shè)置事務(wù)的隔離級別, 就可能導(dǎo)致一些異常情況的出現(xiàn)，最常見的幾種異常為臟讀(Dirty Read), 幻讀(Phantom Read)和不可重復(fù)讀(Unrepeatable Read).

1. 臟讀

一個事務(wù)讀取到了另外一個事務(wù)沒有提交的數(shù)據(jù)(讀寫的是同一份數(shù)據(jù)).

說詳細(xì)點(diǎn)就是當(dāng)一個事務(wù)正在訪問數(shù)據(jù)并且對數(shù)據(jù)進(jìn)行了修改, 而這種修改還沒有提交到數(shù)據(jù)庫中，與此同時時另外一個事務(wù)也訪問這個數(shù)據(jù), 然后使用了這個數(shù)據(jù); 因?yàn)檫@個數(shù)據(jù)是還沒有提交的數(shù)據(jù), 那么另外一個事務(wù)讀到的這個數(shù)據(jù)就是臟數(shù)據(jù), 依據(jù)臟數(shù)據(jù)所做的操作可能是不正確的.

用一個場景例子來理解, 張三正在寫代碼, 李四趴在屏幕前看張三寫代碼, 等張三走掉之后, 李四就把他剛剛寫的這段代碼刪掉了, 此時李四看到的這段代碼就可能是一個錯誤的代碼.

在這個場景下, 張三和李四就可以理解為兩個事務(wù), 這兩個事務(wù)是完全并發(fā)沒有任何限制的, 此時就會出現(xiàn)臟讀問題.

解決臟讀問題的辦法, 就是降低并發(fā)性, 提高隔離性, 具體來說就是給這里的 “寫操作” 加鎖, 張三在寫代碼的時候, 李四不能看, 張三和李四約定張三代碼寫完后會提交到githup上, 李四去githup上去看.

當(dāng)進(jìn)行了寫加鎖的時候, 張三寫的時候, 李四就不能同時去讀了; 相當(dāng)于降低了并發(fā)程度, 提高了隔離性. 降低了一定的效率, 但是提高了準(zhǔn)確性.

2. 不可重復(fù)讀

在同一事務(wù)中, 連續(xù)兩次讀取同一數(shù)據(jù), 得到的結(jié)果不一致.

還是基于上面的場景進(jìn)行理解, 上面已經(jīng)約定了寫加鎖(張三寫代碼過程中, 李四不要讀, 等到張三提交之后, 李四再去讀).

此時張三在寫代碼, 張三和李四有約定, 所以此時李四在等張三把代碼提交到githup上再去看代碼.

過了一會兒, 張三寫完了, 并將代碼提交到了githup上, 李四開始讀代碼.

當(dāng)李四正在讀這個代碼的時候, 張三覺得自己的代碼還有不足, 于是張三動手修改, 重新提交了個版本; 導(dǎo)致李四讀代碼讀了一半, 突然代碼自動就變了.

這種情況就是不可重復(fù)讀問題了, 解決辦法是給讀操作也加鎖, 張三在讀代碼的時候, 李四不能修改.

此時這兩個事務(wù)之間的并發(fā)程度進(jìn)一步降低了, 隔離性又進(jìn)一步提高了, 運(yùn)行速度又進(jìn)一步變慢了, 數(shù)據(jù)的準(zhǔn)確性又進(jìn)—步提高了.

3. 幻讀

同一事務(wù)中, 用同樣的操作讀取兩次, 得到的記錄數(shù)不相同.

幻讀是指當(dāng)事務(wù)不是獨(dú)立執(zhí)行時發(fā)生的一種現(xiàn)象, 例如第一個事務(wù)對一個表中的數(shù)據(jù)進(jìn)行了修改, 這種修改涉及到表中的全部數(shù)據(jù)行; 同時, 第二個事務(wù)也修改這個表中的數(shù)據(jù), 這種修改是向表中插入一行新數(shù)據(jù); 那么, 以后就會發(fā)生操作第一個事務(wù)的用戶發(fā)現(xiàn)表中還有沒有修改的數(shù)據(jù)行, 就好象發(fā)生了幻覺一樣.

再基于2中的場景進(jìn)行理解, 當(dāng)前已經(jīng)約定了寫加鎖和讀加鎖, 即張三寫代碼的時候, 李四不能讀; 李四讀代碼的時候, 張三也不能寫.

此時李四在讀代碼, 張三雖然不能去修改李四現(xiàn)在正在讀的這個文件, 但是李四又去新增/刪除一個其他的文件, 此時, 雖然李四讀的代碼內(nèi)容沒變, 但他發(fā)現(xiàn), 文件的數(shù)量變了; 這就是幻讀問題了.

解決幻讀問題的辦法是串行化, 也就是徹底的舍棄并發(fā), 此時只要李四在讀代碼, 張三就不能進(jìn)行任何操作.

五. MySQL的四個隔離級別

MySQL中有 4 種事務(wù)隔離級別, 由低到高依次為讀未提交 Read Uncommitted, 讀已提交 Read Committed , 可重復(fù)讀 Repeatable Read , 串行化 Serializable.

串行化的事務(wù)處理方式是最安全的, 但不能說用這個就一定好, 應(yīng)該是根據(jù)實(shí)際需求去選擇合適的隔離級別, 比如銀行等涉及錢的場景, 就需要確保準(zhǔn)確性, 速度慢一點(diǎn)也沒什么; 而比如抖音,B站,快手等上面的點(diǎn)贊數(shù), 收藏?cái)?shù)就沒必要那么精確了, 這個場景下速度提高一點(diǎn)體驗(yàn)會更好一些.