主頁 > 知識(shí)庫 > 全面解析MySQL中的隔離級(jí)別

全面解析MySQL中的隔離級(jí)別

　　數(shù)據(jù)庫并發(fā)的對(duì)同一批數(shù)據(jù)進(jìn)行增刪改，就可能會(huì)出現(xiàn)我們所說的臟寫、臟讀、不可重復(fù)讀、幻讀等一系列問題。MySQL提供了一系列機(jī)制來解決事務(wù)并發(fā)問題，比如事務(wù)隔離、鎖機(jī)制、MVCC多版本并發(fā)控制機(jī)制。今天來探究一下事務(wù)隔離機(jī)制。

事務(wù)是一組SQL組成的邏輯處理單元，先來看下事務(wù)的ACID特性：

原子性(Atomicity) ：事務(wù)是一個(gè)原子操作單元，對(duì)數(shù)據(jù)進(jìn)行修改，要么全執(zhí)行要么全不執(zhí)行。是從執(zhí)行層面上來描述的。
一致性(Consistent) ：在事務(wù)開始和完成時(shí)，數(shù)據(jù)都必須保持一致狀態(tài)。是從執(zhí)行結(jié)果層面上來描述的。
隔離性(Isolation) ：數(shù)據(jù)庫系統(tǒng)提供一定的隔離機(jī)制，保證事務(wù)執(zhí)行過程中對(duì)外部不可見，獨(dú)立運(yùn)行，不受外部影響。
持久性(Durable) ：事務(wù)完成之后，它對(duì)于數(shù)據(jù)的修改是永久性的，即使出現(xiàn)系統(tǒng)故障也能夠保持。

并發(fā)事務(wù)的影響：

臟寫（更新丟失：Lost Update）：多個(gè)事務(wù)選擇了同一行，彼此不知道對(duì)方存在，會(huì)覆蓋之前事務(wù)的數(shù)據(jù)操作。
臟讀（Dirty Reads）：A事務(wù)讀取了B事務(wù)未提交的數(shù)據(jù)，B事務(wù)回滾，A提交，最終結(jié)果不符合一致性原則
不可重讀（Non-Repeatable Reads）：同一個(gè)事務(wù)，相同的查詢語句，執(zhí)行多次結(jié)果不一致，可能是外部事務(wù)修改導(dǎo)致的，不符合隔離性。
幻讀（Phantom Reads）：事務(wù)A讀取到了事務(wù)B提交的新增數(shù)據(jù)，不符合隔離性

事務(wù)隔離級(jí)別：

隔離級(jí)別	臟讀（Dirty Read）	不可重復(fù)讀（NonRepeatable Read）	幻讀（Phantom Read）
讀未提交（Read uncommitted）	可能	可能	可能
讀已提交（Read committed）	不可能	可能	可能
可重復(fù)讀（Repeatable Read）	不可能	不可能	可能
串行化（Serializable）	不可能	不可能	不可能

MySQL提供了上面四種隔離級(jí)別，隔離越嚴(yán)格，可能出現(xiàn)的問題就越少，但付出的性能代價(jià)就越大，默認(rèn)的隔離級(jí)別是可重復(fù)讀。下面使用客戶端進(jìn)行操作進(jìn)行驗(yàn)證。

先加創(chuàng)建一張表和數(shù)據(jù)

CREATE TABLE `account` (
 `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
 `balance` int(11) DEFAULT NULL,
 PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

INSERT INTO `account` (`id`, `balance`)
VALUES
  (1, 500),
  (2, 600),
  (3, 200);

連接客戶端，查看隔離級(jí)別，可以看到是可重復(fù)讀：

MySQL [test]> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value      |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+

讀未提交測(cè)試：

AB客戶端都執(zhí)行set tx_isolation='read-uncommitted';設(shè)置隔離級(jí)別為讀未提交。

A客戶端開啟事務(wù)：start transaction;查詢數(shù)據(jù)：select * from account;

B客戶端開啟事務(wù)：start transaction;更新數(shù)據(jù)：update account set balance = balance - 100 where id = 1;此時(shí)事務(wù)未提交

A客戶端再次查詢數(shù)據(jù)：select * from account; 此時(shí)看到兩次查詢的數(shù)據(jù)已經(jīng)不一樣了

在B沒提交前A就讀到了B更新的數(shù)據(jù)，此時(shí)如果B回滾，那么A那邊就是臟數(shù)據(jù)。這種情況就是讀未提交造成的臟讀。用讀已提交隔離級(jí)別可以解決。

使用commit命令把AB客戶端的事務(wù)提交。

讀已提交測(cè)試：

AB客戶端都執(zhí)行 set tx_isolation='read-committed'; 設(shè)置隔離級(jí)別為讀已提交。

A客戶端開啟事務(wù)：start transaction;查詢數(shù)據(jù)：select * from account;

B客戶端開啟事務(wù)：start transaction;更新數(shù)據(jù)：update account set balance = balance - 100 where id = 1;此時(shí)事務(wù)未提交

A客戶端再次查詢數(shù)據(jù)：select * from account; 此時(shí)看到A客戶端兩次查詢數(shù)據(jù)一致，未出現(xiàn)臟讀情況

此時(shí)B客戶端事務(wù)提交：commit;

A客戶端再次查詢數(shù)據(jù)：select * from account; 此時(shí)看到A客戶端查詢數(shù)據(jù)已經(jīng)發(fā)生了變化，這就是不可重復(fù)讀。

可重復(fù)讀測(cè)試：

AB客戶端都執(zhí)行 set tx_isolation='repeatable-read'; 設(shè)置隔離級(jí)別為可重復(fù)讀。

A客戶端開啟事務(wù)：start transaction;查詢數(shù)據(jù)：select * from account;

B客戶端開啟事務(wù)：start transaction;更新數(shù)據(jù)：update account set balance = balance - 100 where id = 1; commit提交事務(wù)

A客戶端再次查詢數(shù)據(jù)：select * from account; 此時(shí)看到A客戶端兩次查詢數(shù)據(jù)一致，重復(fù)讀取數(shù)據(jù)一致。

A客戶端執(zhí)行更新語句：update account set balance = balance - 50 where id = 1;

A客戶端再次查詢數(shù)據(jù)：select * from account; 此時(shí)看到id=1的這條數(shù)據(jù)是B客戶端更新之后的數(shù)據(jù)-50，數(shù)據(jù)的一致性沒有被破壞

B客戶端重新開啟事務(wù)，插入一條數(shù)據(jù):insert into account(id,balance) values (4,1000); commit提交事務(wù)；

A客戶端查詢，和上次結(jié)果一致

A客戶端執(zhí)行：update account set balance = balance - 100 where id = 4; 更新B客戶端新插入的數(shù)據(jù)，能執(zhí)行成功，再次查詢所有數(shù)據(jù)，能插到id=4的數(shù)據(jù)，出現(xiàn)幻讀。

# A客戶端執(zhí)行過程：# 設(shè)置隔離級(jí)別可重復(fù)度MySQL [test]> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 開啟事務(wù)
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# 查詢所有數(shù)據(jù)
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   300 |
| 2 |   600 |
| 3 |   200 |
+----+---------+
3 rows in set (0.00 sec)
# 再次查詢驗(yàn)證兩次結(jié)果是否一致
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   300 |
| 2 |   600 |
| 3 |   200 |
+----+---------+
3 rows in set (0.00 sec)
# 在B客戶端插入數(shù)據(jù)之后，此次A客戶端不能查詢到
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   150 |
| 2 |   600 |
| 3 |   200 |
+----+---------+
3 rows in set (0.00 sec)
# A客戶端更新B客戶端插入的數(shù)據(jù)，發(fā)現(xiàn)可以更新成功
MySQL [test]> update account set balance = balance + 1000 where id = 4;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0
# 再次查詢，能查詢到數(shù)據(jù)，出現(xiàn)幻讀
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   400 |
| 2 |   600 |
| 3 |   200 |
| 4 |  2000 |
+----+---------+
4 rows in set (0.00 sec)
# 提交事務(wù)
MySQL [test]> commit;
Query OK, 0 rows affected (0.01 sec)

# B客戶端執(zhí)行過程：設(shè)置隔離級(jí)別可重復(fù)讀
MySQL [test]> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 開啟事務(wù)
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# 更新數(shù)據(jù)，直接提交
MySQL [test]> update account set balance = balance - 100 where id = 1;
Query OK, 1 row affected (0.00 sec)
Rows matched: 1 Changed: 1 Warnings: 0

MySQL [test]> commit;
Query OK, 0 rows affected (0.01 sec)
# 再次開啟事務(wù)
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# 插入一條數(shù)據(jù)
MySQL [test]> insert into account(id,balance) values (4,1000);
Query OK, 1 row affected (0.01 sec)
MySQL [test]> commit;
Query OK, 0 rows affected (0.00 sec)

最后一種串行化：set tx_isolation='serializable';可自行驗(yàn)證，能解決上面所有問題，但是一般不會(huì)用到的，保證一致性的同時(shí)帶來的是性能大幅度下降，并發(fā)性極低，默認(rèn)是可重復(fù)讀。

通過隔離級(jí)別在一定程度上能處理事務(wù)并發(fā)的問題，除此之外還有其他的手段，后續(xù)會(huì)再次探究。

以上就是全面解析MySQL中的隔離級(jí)別的詳細(xì)內(nèi)容，更多關(guān)于MySQL 隔離級(jí)別的資料請(qǐng)關(guān)注腳本之家其它相關(guān)文章！

您可能感興趣的文章:

詳解MySQL 數(shù)據(jù)庫隔離級(jí)別與MVCC
詳解MySQL中事務(wù)隔離級(jí)別的實(shí)現(xiàn)原理
mysql、oracle默認(rèn)事務(wù)隔離級(jí)別的說明
聊聊MySQL事務(wù)的特性和隔離級(jí)別
簡(jiǎn)述MySql四種事務(wù)隔離級(jí)別
Mysql隔離性之Read View的用法說明

標(biāo)簽：銅川崇左湘潭蘭州仙桃黃山湖南衡水

巨人網(wǎng)絡(luò)通訊聲明：本文標(biāo)題《全面解析MySQL中的隔離級(jí)別》，本文關(guān)鍵詞；如發(fā)現(xiàn)本文內(nèi)容存在版權(quán)問題，煩請(qǐng)?zhí)峁┫嚓P(guān)信息告之我們，我們將及時(shí)溝通與處理。本站內(nèi)容系統(tǒng)采集于網(wǎng)絡(luò)，涉及言論、版權(quán)與本站無關(guān)。