快捷導(dǎo)航

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

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

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

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

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

通過(guò)實(shí)際場(chǎng)景來(lái)理解事務(wù):

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

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

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

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

二. 事務(wù)的使用

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

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

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

相關(guān)語(yǔ)法:

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

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

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

注意:

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

示例:

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

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

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

-- 張三賬戶(hù) -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
-- 李四賬戶(hù) +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語(yǔ)句中所寫(xiě)的注釋格式錯(cuò)誤導(dǎo)致sql執(zhí)行中斷.

-- 先將張三和李四的賬戶(hù)余額恢復(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> 沒(méi)加--的注釋
    -> 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 '沒(méi)加--的注釋
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)了張三的賬戶(hù)少了2000元, 但李四的賬戶(hù)余額并沒(méi)有增加, 在實(shí)際操作中這種涉及錢(qián)的操作發(fā)生這種失誤可能會(huì)造成很大的損失.

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

-- 先將張的賬戶(hù)余額恢復(fù)為10000元
update account set money = 10000 where name = '張三';
-- 開(kāi)啟事務(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> 沒(méi)加--的注釋
    -> 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 '沒(méi)加--的注釋
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ù)庫(kù)用戶(hù)查詢(xún)到的account表中的賬戶(hù)余額發(fā)生了變化，但開(kāi)啟了事務(wù)之后在commit之前只是臨時(shí)的預(yù)操作并不會(huì)真的去修改表中的數(shù)據(jù);

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

發(fā)現(xiàn)操作結(jié)果異常之后, 當(dāng)前用戶(hù)需要恢復(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)

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

-- 開(kāi)啟事務(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ù)也不是萬(wàn)能的, 不能保證你刪表刪庫(kù)之后可以完全恢復(fù), 只是在適量的數(shù)據(jù)和操作下使用事務(wù)可以避免一些問(wèn)題.

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

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

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

1. 原子性(Atomicity)

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

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

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

2. 一致性(Consistency)

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

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

3. 持久性(Durability)

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

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

4. 隔離性(Isolation)

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

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

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

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

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

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

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

1. 臟讀

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

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

用一個(gè)場(chǎng)景例子來(lái)理解, 張三正在寫(xiě)代碼, 李四趴在屏幕前看張三寫(xiě)代碼, 等張三走掉之后, 李四就把他剛剛寫(xiě)的這段代碼刪掉了, 此時(shí)李四看到的這段代碼就可能是一個(gè)錯(cuò)誤的代碼.

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

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

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

2. 不可重復(fù)讀

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

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

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

過(guò)了一會(huì)兒, 張三寫(xiě)完了, 并將代碼提交到了githup上, 李四開(kāi)始讀代碼.

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

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

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

3. 幻讀

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

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

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

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

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

五. MySQL的四個(gè)隔離級(jí)別

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

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