SQL 事務 2

1. 前言

在上一小節中，我們介紹了事務的概念和基本使用，探討了事務的四大特性，本小節我們將更加深入的學習事務。

在實際的生產環境中，偶爾會遇到大量并發訪問的情況；大量的并發會導致數據的競爭，從而引起一系列的并發問題。

本小節，我們將一起學習 SQL 的 4 種事務隔離機制，以及與之對應的 3 種并發異常。

本小節測試數據如下，請先在數據庫中執行，本小節的所有操作若無特殊說明默認在 MySQL 中執行。

DROP TABLE IF EXISTS imooc_user;
CREATE TABLE imooc_user
(
  id int PRIMARY KEY,
  username varchar(20),
  age int
);
INSERT INTO imooc_user(id,username,age)
VALUES (1,'peter',18),(2,'pedro',24),(3,'jerry',22);

2. 并發異常

SQL 標準共定義了 3 種并發異常，這三種異常分別是臟讀（Dirty Read）、不可重復讀（Nnrepeatable Read）和幻讀（Phantom Read）。

這 3 種異常比較抽象，我們直接以一個小例子來講解。

2.1 臟讀

某一天，小王正在訪問數據庫，開啟了一個事務，且向 imooc_user 表中插入名為 tom 的用戶，如下：

BEGIN;
INSERT INTO imooc_user(id,username,age) VALUES (4,'tom',27);

此時，小王還未提交事務，但小李卻在此時訪問了數據庫，并且查詢了 imooc_user 表，如下：

SELECT * FROM imooc_user;

小李看到了如下結果：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 4  | tom      | 27  |
+----+----------+-----+

小王明明還沒有提交事務，但小李卻已經看到了小王操作的結果。試想一下，如果此時小王回滾了事務，那么對于小李來說，他就看到了錯誤的數據，我們稱之為臟讀。

流程圖如下：

2.2 不可重復讀

第二天，小王又在訪問數據庫了，他查看了 imooc_user 表中 id 為 1 的用戶，如下：

SELECT * FROM imooc_user WHERE id = 1;

小王看到的結果是這樣的：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 18  |
+----+----------+-----+

此時，小李也來訪問數據庫，他開啟了一個事務，并且修改了 id 為 1 的用戶，如下：

BEGIN;
UPDATE imooc_user SET age = 100 WHERE id = 1;

這個時候，小王又查看了一次 id 為 1 的用戶。

SELECT * FROM imooc_user WHERE id = 1;

但是他看到的結果卻是這樣的：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 100 |
+----+----------+-----+

小王發現自己兩次查詢的數據不一樣，可是小李的事務還未提交。像小王這樣，兩次查詢結果不同的情況，我們稱之為不可重復讀。

流程圖如下：

2.3 幻讀

第三天，小王開始了一個事務，并向 imooc_user 表中插入了一個名為 mary 的用戶，如下：

BEGIN;
INSERT INTO imooc_user(id,username,age) VALUES (5,'mary',17);

此時，搞事的小李也來訪問數據庫，小李查詢了一下 imooc_user 表，如下：

SELECT * FROM imooc_user;

小李看到了如下結果：

+----+----------+-----+
| id | username | age |
+----+----------+-----+
| 1  | peter    | 100 |
| 2  | pedro    | 24  |
| 3  | jerry    | 22  |
| 4  | tom      | 27  |
| 5  | mary     | 17  |
+----+----------+-----+

小李此時已經看到了新增的用戶 mary 了，但是小王后悔了，他不想創建 mary 了，于是小王回滾了事務：

ROLLBACK;

但小李卻看到了 mary，他一度以為自己出現了幻覺，我們把這種情況稱之為幻讀。

流程圖如下：

我們總結一下這三種異常的特點：

1. 臟讀：讀到了其它事務還未提交的數據；
2. 不可重復讀：兩次讀到了同一數據的不同結果；
3. 幻讀：讀到了其它事務新增但未提交的數據，而且新增數據并未成功。

3. 隔離級別

介紹了常見的 3 種并發異常后，我們再來介紹 4 種隔離機制。

SQL 事務的四種隔離機制主要是為了解決上述的三種并發異常，它們之間的關系如下表所示：

上面的隔離級別由上往下，級別依次會提高，但消耗的性能也會依次提高。我們總結一下四種隔離級別：

1. 讀未提交：允許讀未提交數據，可能會發生臟讀、不可重復讀和幻讀異常；
2. 讀已提交：只能讀已經提交的數據，避免了臟讀，但可能會出現不可重復讀和幻讀；
3. 可重復讀：即能保證在一個事務中多次讀取，數據一致，但可能會出現幻讀；
4. 可串行化：最高的隔離級別，串行的執行事務，可以避免 3 種異常，但性能耗損最高。

提示： SQL Server 和 Oracle 的默認隔離級別是讀已提交，而 MySQL 的默認隔離級別是可重復讀。

魚和熊掌不可而得兼！因此 SQL 提供了 4 種事務隔離級別，在數據吞吐能力和數據安全中，你需要作出相應的選擇。

通過如下語句你可以設置事務隔離級別：

SET SESSION TRANSACTION ISOLATION LEVEL [level];

其中 level 表示隔離級別，如：READ UNCOMMITTED。

4. 小結

• 事務隔離級別以及并發異常是數據庫面試中的重點，請熟練掌握并總結。
• 在實際開發中，事務的使用十分頻繁，當然你不會手寫這些 SQL 語句，會有相應的框架來幫你處理，但你仍然需要弄懂它們的原理，當出現問題時，你才可以迅速解決。