SQL 事務(wù) 2

1. 前言

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

在實(shí)際的生產(chǎn)環(huán)境中，偶爾會(huì)遇到大量并發(fā)訪問(wèn)的情況；大量的并發(fā)會(huì)導(dǎo)致數(shù)據(jù)的競(jìng)爭(zhēng)，從而引起一系列的并發(fā)問(wèn)題。

本小節(jié)，我們將一起學(xué)習(xí) SQL 的 4 種事務(wù)隔離機(jī)制，以及與之對(duì)應(yīng)的 3 種并發(fā)異常。

本小節(jié)測(cè)試數(shù)據(jù)如下，請(qǐng)先在數(shù)據(jù)庫(kù)中執(zhí)行，本小節(jié)的所有操作若無(wú)特殊說(shuō)明默認(rèn)在 MySQL 中執(zhí)行。

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. 并發(fā)異常

SQL 標(biāo)準(zhǔn)共定義了 3 種并發(fā)異常，這三種異常分別是臟讀（Dirty Read）、不可重復(fù)讀（Nnrepeatable Read）和幻讀（Phantom Read）。

這 3 種異常比較抽象，我們直接以一個(gè)小例子來(lái)講解。

2.1 臟讀

某一天，小王正在訪問(wèn)數(shù)據(jù)庫(kù)，開(kāi)啟了一個(gè)事務(wù)，且向 imooc_user 表中插入名為 tom 的用戶，如下：

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

此時(shí)，小王還未提交事務(wù)，但小李卻在此時(shí)訪問(wèn)了數(shù)據(jù)庫(kù)，并且查詢了 imooc_user 表，如下：

SELECT * FROM imooc_user;

小李看到了如下結(jié)果：

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

小王明明還沒(méi)有提交事務(wù)，但小李卻已經(jīng)看到了小王操作的結(jié)果。試想一下，如果此時(shí)小王回滾了事務(wù)，那么對(duì)于小李來(lái)說(shuō)，他就看到了錯(cuò)誤的數(shù)據(jù)，我們稱之為臟讀。

流程圖如下：

2.2 不可重復(fù)讀

第二天，小王又在訪問(wèn)數(shù)據(jù)庫(kù)了，他查看了 imooc_user 表中 id 為 1 的用戶，如下：

SELECT * FROM imooc_user WHERE id = 1;

小王看到的結(jié)果是這樣的：

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

此時(shí)，小李也來(lái)訪問(wèn)數(shù)據(jù)庫(kù)，他開(kāi)啟了一個(gè)事務(wù)，并且修改了 id 為 1 的用戶，如下：

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

這個(gè)時(shí)候，小王又查看了一次 id 為 1 的用戶。

SELECT * FROM imooc_user WHERE id = 1;

但是他看到的結(jié)果卻是這樣的：

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

小王發(fā)現(xiàn)自己兩次查詢的數(shù)據(jù)不一樣，可是小李的事務(wù)還未提交。像小王這樣，兩次查詢結(jié)果不同的情況，我們稱之為不可重復(fù)讀。

流程圖如下：

2.3 幻讀

第三天，小王開(kāi)始了一個(gè)事務(wù)，并向 imooc_user 表中插入了一個(gè)名為 mary 的用戶，如下：

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

此時(shí)，搞事的小李也來(lái)訪問(wèn)數(shù)據(jù)庫(kù)，小李查詢了一下 imooc_user 表，如下：

SELECT * FROM imooc_user;

小李看到了如下結(jié)果：

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

小李此時(shí)已經(jīng)看到了新增的用戶 mary 了，但是小王后悔了，他不想創(chuàng)建 mary 了，于是小王回滾了事務(wù)：

ROLLBACK;

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

流程圖如下：

我們總結(jié)一下這三種異常的特點(diǎn)：

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

3. 隔離級(jí)別

介紹了常見(jiàn)的 3 種并發(fā)異常后，我們?cè)賮?lái)介紹 4 種隔離機(jī)制。

SQL 事務(wù)的四種隔離機(jī)制主要是為了解決上述的三種并發(fā)異常，它們之間的關(guān)系如下表所示：

上面的隔離級(jí)別由上往下，級(jí)別依次會(huì)提高，但消耗的性能也會(huì)依次提高。我們總結(jié)一下四種隔離級(jí)別：

1. 讀未提交：允許讀未提交數(shù)據(jù)，可能會(huì)發(fā)生臟讀、不可重復(fù)讀和幻讀異常；
2. 讀已提交：只能讀已經(jīng)提交的數(shù)據(jù)，避免了臟讀，但可能會(huì)出現(xiàn)不可重復(fù)讀和幻讀；
3. 可重復(fù)讀：即能保證在一個(gè)事務(wù)中多次讀取，數(shù)據(jù)一致，但可能會(huì)出現(xiàn)幻讀；
4. 可串行化：最高的隔離級(jí)別，串行的執(zhí)行事務(wù)，可以避免 3 種異常，但性能耗損最高。

提示： SQL Server 和 Oracle 的默認(rèn)隔離級(jí)別是讀已提交，而 MySQL 的默認(rèn)隔離級(jí)別是可重復(fù)讀。

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

通過(guò)如下語(yǔ)句你可以設(shè)置事務(wù)隔離級(jí)別：

SET SESSION TRANSACTION ISOLATION LEVEL [level];

其中 level 表示隔離級(jí)別，如：READ UNCOMMITTED。

4. 小結(jié)

• 事務(wù)隔離級(jí)別以及并發(fā)異常是數(shù)據(jù)庫(kù)面試中的重點(diǎn)，請(qǐng)熟練掌握并總結(jié)。
• 在實(shí)際開(kāi)發(fā)中，事務(wù)的使用十分頻繁，當(dāng)然你不會(huì)手寫這些 SQL 語(yǔ)句，會(huì)有相應(yīng)的框架來(lái)幫你處理，但你仍然需要弄懂它們的原理，當(dāng)出現(xiàn)問(wèn)題時(shí)，你才可以迅速解決。