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