個人中心
退出
《MySQL 性能優(yōu)化》之數(shù)據(jù)庫事務(wù)與隔離級別
作者: 不剪發(fā)的Tony老師
畢業(yè)于北京航空航天大學，十多年數(shù)據(jù)庫管理與開發(fā)經(jīng)驗，目前在一家全球性的金融公司從事數(shù)據(jù)庫架構(gòu)設(shè)計。CSDN學院簽約講師以及GitChat專欄作者。csdn上的博客收藏于以下地址：https://tonydong.blog.csdn.net
文章目錄
數(shù)據(jù)庫事務(wù)
事務(wù)控制
隔離級別
可重復讀
讀已提交
更新丟失
上一篇我們介紹了 InnoDB 存儲引擎的內(nèi)存和磁盤體系結(jié)構(gòu)。
MySQL 中的 InnoDB 存儲引擎具有事務(wù)安全性，能夠保證多個用戶并發(fā)訪問相同數(shù)據(jù)時的數(shù)據(jù)一致性和完整性；同時也不會由于系統(tǒng)崩潰或硬件故障導致數(shù)據(jù)的破壞。
數(shù)據(jù)庫事務(wù)
在數(shù)據(jù)庫中，事務(wù)（Transaction）是指一組相關(guān)的 SQL 語句操作，它們在業(yè)務(wù)邏輯上是一個原子單元。
??原子在化學反應(yīng)中不可分割，但是在物理狀態(tài)中，原子由原子核和繞核運動的電子組成。不過在數(shù)據(jù)庫領(lǐng)域中，我們?nèi)匀唤柚颖硎疽粋€不可分割的整體操作。
最常見的數(shù)據(jù)庫事務(wù)就是銀行賬戶之間的轉(zhuǎn)賬操作。比如從 A 賬戶轉(zhuǎn)出 200 元到 B 賬戶，其中就包含了多個操作：
查詢 A 賬戶的余額是否足夠；
從 A 賬戶減去 200 元；
往 B 賬戶增加 200 元；
記錄本次轉(zhuǎn)賬流水。
顯然，數(shù)據(jù)庫必須保證所有的操作要么全部成功，要么全部失敗。如果從 A 賬戶減去 1000 元成功執(zhí)行，但是沒有往 B 賬戶增加 1000 元，意味著客戶將會損失 1000 元。用數(shù)據(jù)庫中的術(shù)語來說，這種情況導致了數(shù)據(jù)庫的不一致性。
通過以上案例，我們知道數(shù)據(jù)庫事務(wù)需要滿足一些特性。SQL 標準定義了事務(wù)的四種屬性：ACID。
Atomic，原子性。一個事務(wù)包含的所有 SQL 語句要么全部成功，要么全部失敗。例如，某個事務(wù)需要更新 100 條記錄，但是在更新到一半時系統(tǒng)出現(xiàn)故障；數(shù)據(jù)庫必須保證能夠回滾已經(jīng)修改過的數(shù)據(jù)，就像沒有執(zhí)行過該事務(wù)一樣。
Consistency，一致性。事務(wù)開始之前，數(shù)據(jù)庫位于一致性的狀態(tài)；事務(wù)完成之后，數(shù)據(jù)庫仍然位于一致性的狀態(tài)。例如，銀行轉(zhuǎn)賬事務(wù)中，如果一個賬戶扣款成功但另一個賬戶加錢失敗，那么就會出現(xiàn)數(shù)據(jù)不一致（此時需要回滾已經(jīng)執(zhí)行的扣款操作）。另外，數(shù)據(jù)庫還必須保證滿足完整性約束，比如賬戶扣款之后不能出現(xiàn)余額為負數(shù)（可以在余額字段上添加檢查約束）。
Isolation，隔離性。隔離性與并發(fā)事務(wù)有關(guān)，隔離意味著一個事務(wù)對數(shù)據(jù)的修改在其完成之前對其他事務(wù)是不可見。例如，賬戶 A 向賬戶 B 轉(zhuǎn)賬的過程中，賬戶 B 查詢的余額應(yīng)該是轉(zhuǎn)賬之前的數(shù)目；如果多人同時向賬戶 B 轉(zhuǎn)賬，結(jié)果也應(yīng)該保持一致性，就像依次轉(zhuǎn)賬的結(jié)果一樣。MySQL 支持 SQL 標準中的 4 種事務(wù)隔離級別。
Durability，持久性。已經(jīng)提交的事務(wù)必須永久生效，即使發(fā)生斷電、系統(tǒng)崩潰等故障，數(shù)據(jù)庫都不會丟失數(shù)據(jù)。對于 InnoDB 而言，使用的是重做日志（REDO）實現(xiàn)事務(wù)的持久性。
事務(wù)控制
我們先來了解一下 InnoDB 中的事務(wù)控制。使用以下語句創(chuàng)建一個簡單的示例表：
mysql> CREATE TABLE accounts(id INT AUTO_INCREMENT PRIMARY KEY, user_name varchar(50), balance numeric(10,4));
Query OK, 0 rows affected (0.75 sec)
mysql> ALTER TABLE accounts ADD CONSTRAINT bal_check CHECK(balance >= 0);
Query OK, 0 rows affected (2.95 sec)
Records: 0 Duplicates: 0 Warnings: 0
accounts 用于存儲賬戶信息，檢查約束 bal_check 用于確保余額不會出現(xiàn)負數(shù)。
MySQL 中與事務(wù)管理相關(guān)的語句包括：
autocommit 系統(tǒng)變量，控制是否自動提交，默認為 on；
BEGIN 或者 START TRANSACTION 語句，用于開始一個新的事務(wù)；
COMMIT，提交一個事務(wù)；
ROLLBACK，撤銷一個事務(wù)；
SAVEPOINT，事務(wù)保存點，用于撤銷一部分事務(wù)；
由于 MySQL 默認啟用了自動提交（autocommit），任何數(shù)據(jù)操作都會自動提交：
show variables like 'autocommit';
Variable_name|Value|
-------------|-----|
autocommit |ON |
我們插入一條數(shù)據(jù)到 accounts 表中：
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserA', 100);
Query OK, 1 row affected (0.11 sec)
由于打開了自動提交，MySQL 會自動執(zhí)行一個COMMIT語句。此時表中已經(jīng)存在了一個賬戶 UserA。
我們也可以明確進行事務(wù)的控制，例如：
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserB', 0);
Query OK, 1 row affected (0.01 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.06 sec)
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
| 2 | UserB | 0.0000 |
+----+-----------+----------+
2 rows in set (0.00 sec)
執(zhí)行提交操作之后，accounts 表中存在兩個賬戶。我們也可以使用ROLLBACK撤銷事務(wù)的修改：
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserC', 0);
Query OK, 1 row affected (0.02 sec)
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
| 2 | UserB | 0.0000 |
| 3 | UserC | 0.0000 |
+----+-----------+----------+
3 rows in set (0.00 sec)
mysql> ROLLBACK;
Query OK, 0 rows affected (0.04 sec)
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
| 2 | UserB | 0.0000 |
+----+-----------+----------+
2 rows in set (0.00 sec)
雖然在執(zhí)行插入語句之后，可以查看到賬戶 UserC，但是并沒有持久化；在執(zhí)行ROLLBACK語句之后，accounts 表中仍然只存在兩個賬戶。
最后我們演示一下保存點的使用：
mysql> BEGIN;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserC', 0);
Query OK, 1 row affected (0.07 sec)
mysql> SAVEPOINT sv1;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserD', 0);
Query OK, 1 row affected (0.00 sec)
mysql> ROLLBACK TO sv1;
Query OK, 0 rows affected (0.00 sec)
mysql> COMMIT;
Query OK, 0 rows affected (0.05 sec)
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
| 2 | UserB | 0.0000 |
| 4 | UserC | 0.0000 |
+----+-----------+----------+
3 rows in set (0.00 sec)
在上面的示例中，首先插入了賬戶 UserC，設(shè)置了事務(wù)保存點 sv1；然后插入賬戶 UserD，撤銷保存點 sv1 之后的修改；然后提交 sv1 之前的修改；最終 accounts 表中保存了三個賬戶。
??除了使用以上語句控制事務(wù)的提交之外，MySQL 中還存在許多會引起自動隱式提交的語句，例如 DDL 語句；更多內(nèi)容可以參考官方文檔。
在企業(yè)應(yīng)用中，通常需要支持多用戶并發(fā)訪問；并且保證多個用戶并發(fā)訪問相同的數(shù)據(jù)時，不會造成數(shù)據(jù)的不一致性和不完整性。數(shù)據(jù)庫通常使用事務(wù)的隔離（加鎖）來解決并發(fā)問題。
隔離級別
數(shù)據(jù)庫的并發(fā)意味著多個用戶同時訪問相同的數(shù)據(jù)，例如 A 和 C 同時給 B 轉(zhuǎn)賬。數(shù)據(jù)庫的并發(fā)訪問可能帶來以下問題：
臟讀（Dirty Read）。當一個事務(wù)允許讀取另一個事務(wù)修改但未提交的數(shù)據(jù)時，就可能發(fā)生臟讀。例如，B 的初始余額為 0；A 向 B 轉(zhuǎn)賬 100 元但沒有提交；此時 B 能夠看到 A 轉(zhuǎn)過來的 100 元，并且成功取款 100 元；然后 A 取消了轉(zhuǎn)賬；銀行損失了 100 元。很顯然，銀行不會允許這種事情發(fā)生。
不可重復讀（Nonrepeatable Read）。一個事務(wù)讀取某一記錄后，該數(shù)據(jù)被另一個事務(wù)修改提交，再次讀取該記錄時結(jié)果發(fā)生了改變。例如，B 查詢初始余額為 0；此時 A 向 B 轉(zhuǎn)賬 100 元并且提交；B 再次查詢發(fā)現(xiàn)余額變成了 100 元，以為天上掉餡餅了。
幻讀（Phantom Read）。一個事務(wù)第一次讀取數(shù)據(jù)后，另一個事務(wù)增加或者刪除了某些數(shù)據(jù)，再次讀取時結(jié)果的數(shù)量發(fā)生了變化?；米x和非重復讀有點類似，都是由于其他事務(wù)修改數(shù)據(jù)導致的結(jié)果變化。
更新丟失（Lost Update）。第一類：當兩個事務(wù)更新相同的數(shù)據(jù)時，如果第一個事務(wù)被提交，然后第二個事務(wù)被撤銷；那么第一個事務(wù)的更新也會被撤銷。第二類：當兩個事務(wù)同時讀取某一記錄，然后分別進行修改提交；就會造成先提交的事務(wù)的修改丟失。例如賣票系統(tǒng)，兩個操作人員都查詢到了某張票；然后分別提交更新，結(jié)果一張票賣出去了兩次。
為了解決并發(fā)可能導致的各種問題，SQL 標準定義了 4 種不同的事務(wù)隔離級別（從低到高）：
事務(wù)的隔離級別越高，越能保證數(shù)據(jù)的一致性；但同時會對并發(fā)帶來更大的影響。大多數(shù)數(shù)據(jù)庫系統(tǒng)使用讀已提交（Read Committed）作為默認的隔離級別，MySQL InnoDB 存儲引擎默認使用可重復讀（Repeatable Read）隔離級別。
SELECT @@transaction_isolation;
@@transaction_isolation|
-----------------------|
REPEATABLE-READ |
另外，我們還需要注意的是 MySQL InnoDB 的實現(xiàn)與 SQL 標準的一些差異；它在可重復讀隔離級別解決了幻讀問題，但是存在第二類更新丟失問題 。
??數(shù)據(jù)庫事務(wù)隔離與并發(fā)控制的實現(xiàn)方式通常有兩種：鎖（Lock）與多版本并發(fā)控制（MVCC），具體可以參考這篇文章。
接下來我們演示一下 MySQL 中不同隔離級別的效果。
可重復讀
首先，在會話 1 中開始一個事務(wù)，并查詢 UserA 的余額：
-- 會話 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
賬戶 UserA 的余額為 100 元。然后打開一個新的會話 2，開始一個事務(wù)并修改 UserA 的余額：
-- 會話 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update accounts
-> set balance = balance + 100
-> where user_name = 'UserA';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
會話 2 中顯示 UserA 的余額已經(jīng)被修改為 200。此時再查詢會話 1：
-- 會話 1
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
結(jié)果仍然是 200，沒有出現(xiàn)臟讀。如果我們在會話 1 中并發(fā)修改 UserA 的數(shù)據(jù)：
mysql> update accounts
-> set balance = 99
-> where user_name='UserA';
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction
由于會話 2 已經(jīng)鎖定了該記錄（未提交），會話 1 必須等待，直到等待超時。
??無論哪種隔離級別，如果一個事務(wù)已經(jīng)修改某個數(shù)據(jù)，則另一個事務(wù)不允許同時修改該數(shù)據(jù)，寫操作一定是按照順序執(zhí)行。
我們回到會話 2 中，提交事務(wù)：
-- 會話 2
mysql> commit;
Query OK, 0 rows affected (0.10 sec)
然后再次查詢會話 1：
-- 會話 1
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 100.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
雖然會話 2 已經(jīng)提交了事務(wù)，會話 1 的查詢結(jié)果仍然是 100，意味著可重復讀?。≧epeatable Read）。在會話 1 中提交事務(wù)：
-- 會話 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
此時，賬戶 UserA 的余額為 200，會話 1 讀取到了會話 2 提交的修改。
我們再看一個幻讀的示例，首先在會話 1 中執(zhí)行以下命令：
-- 會話 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserD', 0);
Query OK, 1 row affected (0.03 sec)
會話 1 插入了一條數(shù)據(jù)但未提交。然后在會話 2 中執(zhí)行查詢：
-- 會話 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
| 2 | UserB | 0.0000 |
| 3 | UserC | 0.0000 |
+----+-----------+----------+
3 rows in set (0.00 sec)
查詢結(jié)果只有 3 條記錄。此時回到會話 1 中提交事務(wù)：
-- 會話 1
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
然后再次查詢會話 2：
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
| 2 | UserB | 0.0000 |
| 3 | UserC | 0.0000 |
+----+-----------+----------+
3 rows in set (0.00 sec)
仍然看不到 UserD，也就是說，MySQL 可重復讀（Repeatable Read）隔離級別避免了幻讀。
最后我們在會話 1 中撤銷事務(wù)：
mysql> rollback;
Query OK, 0 rows affected (0.07 sec)
接下來我們看看在其他數(shù)據(jù)庫中默認的讀已提交（Read Committed）隔離級別。
讀已提交
MySQL 提供了以下命令，用于修改當前會話的隔離級別：
SET TRANSACTION ISOLATION LEVEL {
REPEATABLE READ
| READ COMMITTED
| READ UNCOMMITTED
| SERIALIZABLE
};
我們將會話 1 的隔離級別設(shè)置為 READ COMMITTED，然后開始一個事務(wù)查詢 UserA 的余額：
-- 會話 1
mysql> SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
Query OK, 0 rows affected (0.00 sec)
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
賬戶 UserA 的余額為 200 元。然后在會話 2 中修改 UserA 的余額：
-- 會話 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> update accounts
-> set balance = balance + 100
-> where user_name = 'UserA';
Query OK, 1 row affected (0.06 sec)
Rows matched: 1 Changed: 1 Warnings: 0
mysql> select * from accounts where user_name = 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 300.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
會話 2 中顯示 UserA 的余額已經(jīng)被修改為 300。此時再查詢會話 1：
-- 會話 1
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
查詢結(jié)果為 200，沒有出現(xiàn)臟讀。我們回到會話 2 中，提交事務(wù)：
-- 會話 2
mysql> commit;
Query OK, 0 rows affected (0.12 sec)
然后再次查詢會話 1：
-- 會話 1
mysql> select * from accounts where user_name= 'UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 300.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
由于會話 2 已經(jīng)提交了事務(wù)，會話 1 的查詢結(jié)果發(fā)生了變化，意味著不可重復讀?。∟onrepeatable Read）。在會話 1 中提交事務(wù)：
-- 會話 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)
我們同樣看一個幻讀的示例，首先在會話 2 中執(zhí)行以下命令：
-- 會話 2
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> INSERT INTO accounts(user_name, balance) VALUES ('UserD', 0);
Query OK, 1 row affected (0.03 sec)
會話 2 插入了一條數(shù)據(jù)但未提交。然后在會話 1 中執(zhí)行查詢：
-- 會話 1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 200.0000 |
| 2 | UserB | 0.0000 |
| 3 | UserC | 0.0000 |
+----+-----------+----------+
3 rows in set (0.00 sec)
查詢結(jié)果只有 3 條記錄。此時回到會話 2 中提交事務(wù)：
-- 會話 2
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
然后再次查詢會話 1：
mysql> select * from accounts;
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 300.0000 |
| 2 | UserB | 0.0000 |
| 3 | UserC | 0.0000 |
| 6 | UserD | 0.0000 |
+----+-----------+----------+
4 rows in set (0.00 sec)
會話 1 看到了 UserD，出現(xiàn)了幻讀。
更新丟失
現(xiàn)代數(shù)據(jù)庫系統(tǒng)已經(jīng)解決了第一類更新丟失問題，但是可能存在第二類更新丟失。對于 MySQL 而言，除非是設(shè)置可序列化的隔離級別，都可能存在第二類更新丟失問題。
以下試驗仍然以默認的可重復讀隔離級別為例，會話 1 查詢數(shù)據(jù)：
-- 會話1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where user_name='UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 300.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
同時，會話 2 也查詢了該記錄：
-- 會話1
mysql> begin;
Query OK, 0 rows affected (0.00 sec)
mysql> select * from accounts where user_name='UserA';
+----+-----------+----------+
| id | user_name | balance |
+----+-----------+----------+
| 1 | UserA | 300.0000 |
+----+-----------+----------+
1 row in set (0.00 sec)
然后會話 1 將 UserA 的余額增加 100：
mysql> update accounts
-> set balance=balance+100
-> where user_name='UserA';
Query OK, 1 row affected (0.03 sec)
Rows matched: 1 Changed: 1 Warnings: 0
同時會話 2 將 UserA 的余額也增加 100：
-- 會話 2
mysql> update accounts
-> set balance=balance+100
-> where user_name='UserA';
Query OK, 1 row affected (4.04 sec)
Rows matched: 1 Changed: 1 Warnings: 0
此時，會話 2 需要等待會話 1 提交：
-- 會話 1
mysql> commit;
Query OK, 0 rows affected (0.08 sec)
緊接著會話 2 也提交：
-- 會話 2
mysql> commit;
Query OK, 0 rows affected (0.07 sec)
以上場景相當于 UserA 給自己存了 100 元，但發(fā)現(xiàn)賬戶增加了 200 元；雖然這種情況有可能是允許的，UserA 可以查詢交易記錄發(fā)現(xiàn)有人給他轉(zhuǎn)了賬，并不是銀行系統(tǒng)出錯。但是對于賣票等交易系統(tǒng)，如果一張票被賣出兩次就會出現(xiàn)問題了。
解決更新丟失的方法通常有兩種：樂觀鎖（Optimistic Locking，類似于 MVCC）和悲觀鎖（Pessimistic Locking，select for update），具體參考這篇文章。
通常來說，隔離級別越高越能保證數(shù)據(jù)的一致性和完整性，但是支持的并發(fā)也會越低。一般來說，我們可以使用數(shù)據(jù)庫默認的隔離級別；它可以保證不會出現(xiàn)臟讀、不可重復讀以及幻讀問題，并且具有較好的并發(fā)性能。對于特殊的應(yīng)用場景，可以通過應(yīng)用程序主動加鎖的方式進行處理。
歡迎關(guān)注??、評論??、點贊??！
分類導航