論 MySQL 之事務隔離級別 | 資料庫篇

筆記分享 | Note Share

No matter where I am, I will reply you immediately when I see the email.My Email:

echo "YUBzYW1lZ28uY29tCg==" | base64 -d

前言

MySQL事務主要用於處理一個包含操作量比較大、複雜的業務。比如說，刪除一個學生，我們除了要刪除該學生的基本資訊，同時也要刪除考試記錄、違規記錄等。諸多的操作組成一個事務。事務是用來管理insert、update、delete基本指令的。當MySQL使用innodb引擎的前提下才支援事務操作。

事務的基本特點

• 原子性

  一個事務的執行所有的操作，結果只有兩種：要麼全部執行、要麼全部不執行。事務在執行的過程中，當在某一個節點執行發生錯誤的時候，事務會被執行rollback操作，將資料恢復到執行該事務之前的狀態。A去銀行轉賬，要麼轉賬成功、要麼轉賬失敗。

• 一致性

  從事務開始執行到執行完成後，資料庫的完整性約束完全沒有收到破壞。A轉賬給B，不可能發生這種情況：A轉賬成功、B沒有收到款。

• 隔離性

  在同一時間點，資料庫允許多個併發事務同時對其資料進行讀寫和修改的能力，隔離性可以防止多個事務併發執行時由於交叉執行而導致資料的不一致。事務隔離分為不同級別，包括讀未提交( read uncommitted )、讀提交( read committed )、可重複讀( repeatable read|預設方式 )和序列化( serializable )。

• 永續性

  事務成功執行後，事務的所有操作對資料庫的更新是永久的，不能回滾。

隔離性的類別

• read uncommitted | 讀未提交
• read committed | 讀已提交
• repeatable read | 可重複讀
• serializable | 序列化

在MySQL資料庫中，引擎預設使用repeatable read

# SELECT @@tx_isolation 或者 SELECT @@transaction_isolation
# MySQL 8.x 
# transaction_isolation在MySQL 5.7.20中新增了作為別名 tx_isolation，現已棄用，並在MySQL 8.0中刪除。
# 應調整應用程式transaction_isolation以優先使用 tx_isolation。
mysql> SELECT @@transaction_isolation;
+-------------------------+
| @@transaction_isolation |
+-------------------------+
| REPEATABLE-READ         |
+-------------------------+
1 row in set (0.01 sec)

事務的併發問題

• 髒讀

  事務A讀取了事務B更新的資料，然後事務B在某些因素下執行了回滾，那麼事務A讀取的資料就是不合理的，即髒資料。

  ## (1)事務A的操作
  ## 設定為隔離方式為[讀未提交 | read uncommitted]
  ## 開啟事務並查詢id為1的score的值 
  mysql> set session transaction isolation level read uncommitted;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    80 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)
  
  ## (2)事務B的操作
  ## 開啟事務並將id為1的score修改成 75
  mysql> start transaction;
  Query OK, 0 rows affected (0.00 sec)
  
  mysql> update score set score=75 where id=1;
  Query OK, 1 row affected (0.00 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    75 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)
  
  ## (3)事務A的操作
  ## 再次讀取id為1的score值 75
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    75 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)
  
  ## (4) 事務B的操作
  ## 事務回滾
  mysql> rollback;
  Query OK, 0 rows affected (0.00 sec)

  上述四個步驟中，事務A在事務B前讀取的score的值為80，在事務B執行修改後讀取score的值為75，事務B再進行回滾操作，那麼事務A在兩次讀取的score的值是不一致的，那麼就是髒讀。

• 不可重複讀

  事務A需要重複多次讀取某組資料，事務A在事務B對該組資料修改提交前後進行讀取，很顯然、兩次讀取的資料是不一致的，即不可重複讀。側重於後設資料的修改。

  ## 使用[讀已提交]的模式實踐
  mysql> set session transaction isolation level read committed;
  Query OK, 0 rows affected (0.00 sec)
  
  ## (1) 事務A查詢id為1的score 80
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    80 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)
  
  ## (2) 事務B修改id為1的score並提交事務 75
  mysql> update score set score=75 where id=1;
  Query OK, 1 row affected (0.01 sec)
  Rows matched: 1  Changed: 1  Warnings: 0
  
  mysql> commit;
  Query OK, 0 rows affected (0.00 sec)
  
  ## (3) 事務A再次查詢id為1的score的值 75
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    80 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)
  
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    75 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)

  從上述的三個步驟中顯而易見可以看出，事務A在事務B修改並提交的前後讀取同一條資料的值得不一樣的，具有不可重複讀問題。

• 幻讀

  事務A在修改每一條後設資料的時候，事務B在此時新增了一條新記錄，事務A在處理的過程中突然多了一條資料，即幻讀。側重於資料的刪除與修改。

  ## 將事務隔離的模式設定為[可重複讀]
  mysql> set session transaction isolation level repeatable read;
  Query OK, 0 rows affected (0.00 sec)
  
  ## (1)事務A讀取scor資料表
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    75 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)
  
  ## (2)事務B新增刪除一條資料並提交
  mysql> delete from score where id=1;
  Query OK, 1 row affected (0.01 sec)
  
  mysql> commit;
  Query OK, 0 rows affected (0.01 sec)
  
  ## (3)事務A再次讀取score資料表
  mysql> select * from score;
  +----+----------+-------+
  | id | name     | score |
  +----+----------+-------+
  |  1 | alicfeng |    75 |
  |  2 | feng     |   100 |
  |  3 | alic     |    90 |
  +----+----------+-------+
  3 rows in set (0.00 sec)

  可見，事務A在事務B刪除並提交前後讀取的資料一樣，出現了幻讀。

事務隔離級別的影響

事務隔離級別	髒讀	不可重複讀	幻讀
讀未提交 | read uncommitted	會	會	會
讀已提交 | read committed	不會	會	會
可重複讀 | repeatable read	不會	不會	會
序列化 | serializable	不會	不會	不會

事務隔離性說明

• 隔離級別越高，越能保證資料的完整性和一致性，但是對併發效能的影響也越大。
• 事務隔離級別為讀提交時，寫資料只會鎖住相應的行
• 事務隔離級別為序列化時，讀寫資料都會鎖住整張表

本作品採用《CC 協議》，轉載必須註明作者和本文連結