全面解析MySQL中的隔離級別

  數據庫並發的對同一批數據進行增刪改,就可能會出現我們所說的臟寫、臟讀、不可重復讀、幻讀等一系列問題。MySQL提供瞭一系列機制來解決事務並發問題,比如事務隔離、鎖機制、MVCC多版本並發控制機制。今天來探究一下事務隔離機制。

事務是一組SQL組成的邏輯處理單元,先來看下事務的ACID特性:

  • 原子性(Atomicity) :事務是一個原子操作單元,對數據進行修改,要麼全執行要麼全不執行。是從執行層面上來描述的。
  • 一致性(Consistent) :在事務開始和完成時,數據都必須保持一致狀態。是從執行結果層面上來描述的。
  • 隔離性(Isolation) :數據庫系統提供一定的隔離機制,保證事務執行過程中對外部不可見,獨立運行,不受外部影響。
  • 持久性(Durable) :事務完成之後,它對於數據的修改是永久性的,即使出現系統故障也能夠保持。

並發事務的影響:

  • 臟寫(更新丟失:Lost Update):多個事務選擇瞭同一行,彼此不知道對方存在,會覆蓋之前事務的數據操作。
  • 臟讀(Dirty Reads):A事務讀取瞭B事務未提交的數據,B事務回滾,A提交,最終結果不符合一致性原則
  • 不可重讀(Non-Repeatable Reads):同一個事務,相同的查詢語句,執行多次結果不一致,可能是外部事務修改導致的,不符合隔離性。
  • 幻讀(Phantom Reads):事務A讀取到瞭事務B提交的新增數據,不符合隔離性

事務隔離級別:

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

MySQL提供瞭上面四種隔離級別,隔離越嚴格,可能出現的問題就越少,但付出的性能代價就越大,默認的隔離級別是可重復讀。下面使用客戶端進行操作進行驗證。

先加創建一張表和數據

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);

連接客戶端,查看隔離級別,可以看到是可重復讀:

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

讀未提交測試:

AB客戶端都執行set tx_isolation=’read-uncommitted’;設置隔離級別為讀未提交。

A客戶端開啟事務:start transaction;查詢數據:select * from account;

B客戶端開啟事務:start transaction;更新數據:update account set balance = balance – 100 where id = 1;此時事務未提交

A客戶端再次查詢數據:select * from account; 此時看到兩次查詢的數據已經不一樣瞭

在B沒提交前A就讀到瞭B更新的數據,此時如果B回滾,那麼A那邊就是臟數據。這種情況就是讀未提交造成的臟讀。用讀已提交隔離級別可以解決。

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

讀已提交測試:

AB客戶端都執行  set tx_isolation=’read-committed’; 設置隔離級別為讀已提交。

A客戶端開啟事務:start transaction;查詢數據:select * from account;

B客戶端開啟事務:start transaction;更新數據:update account set balance = balance – 100 where id = 1;此時事務未提交

A客戶端再次查詢數據:select * from account; 此時看到A客戶端兩次查詢數據一致,未出現臟讀情況

此時B客戶端事務提交:commit;

A客戶端再次查詢數據:select * from account; 此時看到A客戶端查詢數據已經發生瞭變化,這就是不可重復讀。

可重復讀測試:

AB客戶端都執行  set tx_isolation=’repeatable-read’; 設置隔離級別為可重復讀。

A客戶端開啟事務:start transaction;查詢數據:select * from account;

B客戶端開啟事務:start transaction;更新數據:update account set balance = balance – 100 where id = 1; commit提交事務

A客戶端再次查詢數據:select * from account; 此時看到A客戶端兩次查詢數據一致,重復讀取數據一致。

A客戶端執行更新語句:update account set balance = balance – 50 where id = 1;

A客戶端再次查詢數據:select * from account; 此時看到id=1的這條數據是B客戶端更新之後的數據-50,數據的一致性沒有被破壞

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

A客戶端查詢,和上次結果一致

A客戶端執行:update account set balance = balance – 100 where id = 4; 更新B客戶端新插入的數據,能執行成功,再次查詢所有數據,能插到id=4的數據,出現幻讀。

# A客戶端執行過程:# 設置隔離級別可重復度MySQL [test]> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 開啟事務
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# 查詢所有數據
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   300 |
| 2 |   600 |
| 3 |   200 |
+----+---------+
3 rows in set (0.00 sec)
# 再次查詢驗證兩次結果是否一致
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   300 |
| 2 |   600 |
| 3 |   200 |
+----+---------+
3 rows in set (0.00 sec)
# 在B客戶端插入數據之後,此次A客戶端不能查詢到
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   150 |
| 2 |   600 |
| 3 |   200 |
+----+---------+
3 rows in set (0.00 sec)
# A客戶端更新B客戶端插入的數據,發現可以更新成功
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
# 再次查詢,能查詢到數據,出現幻讀
MySQL [test]> select * from account;
+----+---------+
| id | balance |
+----+---------+
| 1 |   400 |
| 2 |   600 |
| 3 |   200 |
| 4 |  2000 |
+----+---------+
4 rows in set (0.00 sec)
# 提交事務
MySQL [test]> commit;
Query OK, 0 rows affected (0.01 sec)
# B客戶端執行過程:設置隔離級別可重復讀
MySQL [test]> set tx_isolation='repeatable-read';
Query OK, 0 rows affected, 1 warning (0.00 sec)
# 開啟事務
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# 更新數據,直接提交
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)
# 再次開啟事務
MySQL [test]> start transaction;
Query OK, 0 rows affected (0.00 sec)
# 插入一條數據
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’;可自行驗證,能解決上面所有問題,但是一般不會用到的,保證一致性的同時帶來的是性能大幅度下降,並發性極低,默認是可重復讀。

通過隔離級別在一定程度上能處理事務並發的問題,除此之外還有其他的手段,後續會再次探究。

以上就是全面解析MySQL中的隔離級別的詳細內容,更多關於MySQL 隔離級別的資料請關註WalkonNet其它相關文章!

推薦閱讀: