MySQL事務(wù)隔離級別學(xué)習(xí)

1. MySQL事務(wù)隔離級別學(xué)習(xí)筆記

1.1. 隔離級別

  1. READ UNCOMMITTED 未提交讀

    在READ UNCOMMITTED級別，事務(wù)中的修改，即使沒有提交，對其他事務(wù)也都是可見的。事務(wù)可以讀取未提交的數(shù)據(jù)，這也被稱作臟讀。這個級別會導(dǎo)致很多問題，從性能上來說，READ UNCOMMITTED不會比其他的級別好太多，但確缺乏其他級別的很多好處，除非真的有必要的理由，在實際應(yīng)用中一般很少使用。

  2. READ COMMITED 提交讀

    大多數(shù)數(shù)據(jù)庫系統(tǒng)的默認隔離級別是 READ COMMITED（但mysql不是）。READ COMMITED滿足前面提到的隔離性簡單定義：一個事務(wù)開始時，只能看見已經(jīng)提交的事務(wù)所做的修改。換句話說，一個事務(wù)從開始直到提交之前，所做的任何修改對其他事務(wù)都是不可見的。這個級別有時候也叫做不可重復(fù)讀，因為兩次執(zhí)行同樣的查詢，可能會得到不一樣的結(jié)果。

    這里的原因是，在兩次執(zhí)行同樣的查詢中間，可能由其他事務(wù)修改了影響查詢結(jié)果的數(shù)據(jù)。在 Read Commited級別下，會發(fā)生這種情況。

  3. REPEATABLE READ 可重復(fù)讀

    MySQL的默認事務(wù)隔離級別。
    REPEATABLE READ解決了臟讀的問題。該級別保證了在同一個事務(wù)中多次讀取同樣的記錄的結(jié)果是一致的。但是理論上，可重復(fù)讀隔離級別還是無法解決另一個幻讀的問題。所謂幻讀，指的是當(dāng)某個事務(wù)在讀取某個范圍內(nèi)的記錄時，另外一個事務(wù)又在該范圍內(nèi)插入了新的記錄，當(dāng)之前的事務(wù)再次讀取該范圍的記錄時，會產(chǎn)生幻行。InnoDB存儲引擎通過多版本并發(fā)控制（MVCC）解決了幻讀的問題。

    在一個事務(wù)未提交前，多次查詢相同的查詢，都會返回相同的結(jié)果。即使在此期間有其他事務(wù)已提交了影響該查詢的數(shù)據(jù)。

  4. SERIALIZABLE 可串行化

    SERIALIZABLE 是最高的隔離級別。它通過強事務(wù)串行執(zhí)行，避免了前面說的幻讀的問題。簡單來說，SERIALIZABLE會在曲度的每一行數(shù)據(jù)都加上鎖，所以可能導(dǎo)致大量的超時和鎖爭用的問題。實際應(yīng)用中也很少用到這個隔離級別，只有在非常需要確保數(shù)據(jù)一致性而且可以接受沒有并發(fā)的情況下，才考慮使用該級別。

1.2. 實驗

上面是摘自《高性能MySQL》一書，光這么看，可能也理解不到多少東西，不如直接做實驗親身體驗一下，加深理解。

我們先簡單創(chuàng)建一個表 users，結(jié)構(gòu)如下：

+------------+------------------+------+-----+-------------------+----------------+
| Field      | Type             | Null | Key | Default           | Extra          |
+------------+------------------+------+-----+-------------------+----------------+
| id         | int(11) unsigned | NO   | PRI | <null>            | auto_increment |
| name       | varchar(50)      | NO   |     |                   |                |
| created_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| updated_at | datetime         | NO   |     | CURRENT_TIMESTAMP |                |
| deleted_at | datetime         | YES  |     | <null>            |                |
+------------+------------------+------+-----+-------------------+----------------+

實現(xiàn)插入三條數(shù)據(jù)：

+----+-------------+---------------------+---------------------+------------+
| id | name        | created_at          | updated_at          | deleted_at |
+----+-------------+---------------------+---------------------+------------+
| 1  | coolcao2018 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom         | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili        | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+-------------+---------------------+---------------------+------------+

然后打開兩個終端，A和B分別表示兩個用戶同時在操作。

1.2.1. READ UNCOMMITTED 未提交讀

對于用戶A，操作：

mysql root@localhost:test> set session transaction isolation level read uncommitted;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+-------------+---------------------+---------------------+------------+
| id | name        | created_at          | updated_at          | deleted_at |
+----+-------------+---------------------+---------------------+------------+
| 1  | coolcao2018 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom         | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili        | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+-------------+---------------------+---------------------+------------+

然后，用戶B操作，

mysql root@localhost:test> set session transaction isolation level read uncommitted;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> update users set name='coolcao' where id=1;

此時，用戶B并未提交事務(wù)，用戶A進行查詢操作看看：

mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+

從整個過程來看，用戶B還并未提交事務(wù)，但是A卻已經(jīng)能夠直接讀到B的更新。

從上面實驗結(jié)果來看，不難理解上面對于 READ UNCOMMITTED級別的描述：在READ UNCOMMITTED級別，事務(wù)中的修改，即使沒有提交，對其他事務(wù)也都是可見的。

1.2.2. READ COMMITTED 提交讀

同時將A，B兩個終端的事務(wù)級別設(shè)置為 read committed:

 // 在A，B兩個終端都執(zhí)行
 set session transaction isolation level read committed;

對于A，我們開啟一個事務(wù)，然后更新一下數(shù)據(jù)，但并不提交事務(wù)：

mysql root@localhost:test>  set session transaction isolation level read committed;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+

mysql root@localhost:test> update users set name='coolcao222' where id=1;
mysql root@localhost:test> select * from users;

+----+------------+---------------------+---------------------+------------+
| id | name       | created_at          | updated_at          | deleted_at |
+----+------------+---------------------+---------------------+------------+
| 1  | coolcao222 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom        | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili       | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+------------+---------------------+---------------------+------------+

然后，在B終端，開啟另外一個事務(wù)，進行數(shù)據(jù)查詢：

mysql root@localhost:test> set session transaction isolation level read committed;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom     | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili    | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+---------+---------------------+---------------------+------------+

然后，將A事務(wù)提交：

commit;

這時，再在B查詢 ：

mysql root@localhost:test> select * from users;

+----+------------+---------------------+---------------------+------------+
| id | name       | created_at          | updated_at          | deleted_at |
+----+------------+---------------------+---------------------+------------+
| 1  | coolcao222 | 2018-05-28 12:48:43 | 2018-05-28 12:48:43 | <null>     |
| 2  | tom        | 2018-05-28 13:24:28 | 2018-05-28 13:24:28 | <null>     |
| 3  | lili       | 2018-05-31 08:54:28 | 2018-05-31 08:54:28 | <null>     |
+----+------------+---------------------+---------------------+------------+

從結(jié)果來看，也不難理解 read committed級別，對于一個事務(wù)，只能讀取到當(dāng)前事務(wù)的數(shù)據(jù)和其他已經(jīng)提交的事務(wù)的數(shù)據(jù)，對于其他未提交事務(wù)的數(shù)據(jù)，讀不到。
而且，從上面的實驗結(jié)果中，我們也看到了，會話B在會話A提交事務(wù)前后查詢的結(jié)果并不一致，這也就是上面所說的，不可重復(fù)讀。

1.2.3. REPEATABLE READ 可重復(fù)讀

我們將會話A設(shè)置為REPEATABLE READ :

mysql root@localhost:test> set session transaction isolation level repeatable read;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
+----+---------+---------------------+---------------------+------------+

此時，我們在B終端插入一條數(shù)據(jù)：

mysql root@localhost:test> insert into users (id,name) values (4,'coco');
mysql root@localhost:test> commit;
mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
| 4  | coco    | 2018-08-10 15:23:50 | 2018-08-10 15:23:50 | <null>     |
+----+---------+---------------------+---------------------+------------+

在終端B中，插入一條記錄，并提交，這時id=4的用戶已經(jīng)被插入到數(shù)據(jù)庫。

此時，再回到終端A，查詢：

mysql root@localhost:test> select * from users;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
+----+---------+---------------------+---------------------+------------+

哎，查詢的結(jié)果中，沒有B剛插入的id=4的用戶，這也就是說該級別的事務(wù)隔離，保證了在同一個事務(wù)中多次讀取同樣的記錄的結(jié)果是一致的。這時，我們在A中插入一條記錄：

mysql root@localhost:test> insert into users (id,name) values (4,'coco');
(1062, u"Duplicate entry '4' for key 'PRIMARY'")

哎，這個時候，數(shù)據(jù)庫報錯了，提示主鍵重復(fù)。明明我在這個事務(wù)中，查詢的數(shù)據(jù)只有1,2,3，為什么插入4的時候提示主鍵沖突呢？是發(fā)生幻覺了么？是的，發(fā)生“幻讀”了。由于REPEATABLE READ級別的隔離，在一個事務(wù)中，多次讀取同樣記錄的結(jié)果是一致的，在這多次讀取之間，被別的事務(wù)插入了新的數(shù)據(jù)，這時前事務(wù)再插入數(shù)據(jù)，必然會導(dǎo)致錯誤。

在一個事務(wù)未提交前，多次查詢相同的查詢，返回的結(jié)果是相同的，即使在此期間，其他事務(wù)已經(jīng)提交了影響該查詢的數(shù)據(jù)。

1.2.4. SERIALIZABLE 可串行化

我們將A，B同時設(shè)置為SERIALIZABLE, 然后在A開啟是個事務(wù)，做一個簡單查詢：

mysql root@localhost:test> set session transaction isolation level serializable;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> select * from users where id<10;

+----+---------+---------------------+---------------------+------------+
| id | name    | created_at          | updated_at          | deleted_at |
+----+---------+---------------------+---------------------+------------+
| 1  | coolcao | 2018-08-10 15:21:02 | 2018-08-10 15:21:02 | <null>     |
| 2  | tom     | 2018-08-10 15:21:07 | 2018-08-10 15:21:07 | <null>     |
| 3  | lili    | 2018-08-10 15:21:11 | 2018-08-10 15:21:11 | <null>     |
| 4  | coco    | 2018-08-10 15:23:50 | 2018-08-10 15:23:50 | <null>     |
| 5  | juli    | 2018-08-10 15:31:32 | 2018-08-10 15:31:32 | <null>     |
+----+---------+---------------------+---------------------+------------+

此時，A事務(wù)并未提交，然后在B再開啟一個事務(wù)，進行插入操作：

mysql root@localhost:test> set session transaction isolation level serializable;
mysql root@localhost:test> start transaction;
mysql root@localhost:test> insert into users (id,name) values (6,'kate');
(1205, u'Lock wait timeout exceeded; try restarting transaction')

你會發(fā)現(xiàn)，哎我去，B事務(wù)被掛住了，然后過了一段時間，提示了錯誤 (1205, u'Lock wait timeout exceeded; try restarting transaction')，說等待鎖超時。
是的，在串行化級別，會在讀取的每一行數(shù)據(jù)都加上鎖，也就是說，上面A事務(wù)在讀取時，已經(jīng)加了鎖，此時B事務(wù)在插入操作時，得等待鎖的放開，時間一長，A鎖未放開，B就報錯了。
從實驗中可以看出，可串行化級別，由于要保證避免幻讀而加了鎖導(dǎo)致效率以及可能會觸發(fā)的等待鎖超時等錯誤，實際應(yīng)用中，該級別的事務(wù)隔離也很少使用。

對照著實驗結(jié)果，來理解上面四個隔離級別，就容易理解了。

?著作權(quán)歸作者所有,轉(zhuǎn)載或內(nèi)容合作請聯(lián)系作者
【社區(qū)內(nèi)容提示】社區(qū)部分內(nèi)容疑似由AI輔助生成，瀏覽時請結(jié)合常識與多方信息審慎甄別。
平臺聲明：文章內(nèi)容（如有圖片或視頻亦包括在內(nèi)）由作者上傳并發(fā)布，文章內(nèi)容僅代表作者本人觀點，簡書系信息發(fā)布平臺，僅提供信息存儲服務(wù)。

相關(guān)閱讀更多精彩內(nèi)容

友情鏈接更多精彩內(nèi)容