MySQL事務的隔離級別與併發問題

MySQL版本：8.0.27

一、事務併發執行面臨的問題

1. 髒讀（Dirty Read）

如果事務A讀到了未提交的事務B修改過的資料，就意味著發生了髒讀現象。

2. 不可重複讀（Non-Repeatable Read）

如果事務B修改了未提交的事務A讀取到的資料，就意味著發生了不可重複讀現象。

3. 幻讀（Phantom Read）

事務A先根據某個範圍條件查詢出了一些記錄，而事務B寫入了一些符合該條件的新記錄，當事務A再次以相同的條件查詢時，查詢到了新的記錄，就意味著發生了幻讀現象。

二、SQL標準中的四種隔離級別

1. READ UNCOMMITTED（未提交讀）

在 READ UNCOMMITTED 級別，事務中的修改，即使沒有提交，對其他事務也都是可見的。
也就是說該隔離級別會出現髒讀問題。

2. READ COMMITTED（已提交讀）

READ COMMITTED 解決了髒讀問題，它滿足事務隔離性的簡單定義：一個事務從開始直到提交之前，所做的任何修改對其他事務都是不可見的。

3. REPEATABLE READ（可重複讀）

REPEATABLE READ 解決了髒讀和不可重複讀的問題。該級別保證了在同一個事務中多次讀取同樣記錄的結果是一致的。但是理論上，可重複讀隔離級別還是無法解決幻讀（Phantom Read）問題。
InnoDB 儲存引擎通過 MVCC（多版本併發控制）和 Next-Key (臨鍵鎖) 很大程度上避免了幻讀問題。
可重複讀是MySQL的預設事務隔離級別。

4. SERIALIZABLE（序列化）

SERIALIZABLE 通過強制事務序列執行，避免了髒讀、不可重複讀和幻讀的問題。SERIALIZABLE 會在讀取的每一行資料上都加鎖，所以可能導致大量的超時和鎖爭用問題。

三、四種隔離級別對比

四、四種隔離級別事務並行示例

檢視事務的隔離級別

SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

設定事務的隔離級別

SET [GLOBAL|SESSION] TRANSACTION ISOLATION LEVEL <level>;

level 有4個可選值：

• READ UNCOMMITTED
• READ COMMITTED
• REPEATABLE READ
• SERIALIZABLE

修改全域性隔離級別需要退出會話重新連線MySQL生效。

初始資料

CREATE TABLE `user`  (
  `id` int(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  `name` varchar(10) NOT NULL COMMENT '姓名',
  `gender` char(1) NOT NULL COMMENT '性別',
  `age` tinyint(3) UNSIGNED NOT NULL COMMENT '年齡',
  `phone` char(11) NOT NULL COMMENT '電話',
  PRIMARY KEY (`id`) USING BTREE,
  INDEX `idx_username`(`name`) USING BTREE,
  UNIQUE INDEX `unq_phone`(`phone`) USING BTREE
) ENGINE=InnoDB CHARACTER SET=utf8mb4;

INSERT INTO `user` VALUES (10, 'M小明', '男', 16, '11111111111');
INSERT INTO `user` VALUES (20, 'H小紅', '女', 15, '22222222222');
INSERT INTO `user` VALUES (30, 'L小麗', '女', 18, '33333333333');
INSERT INTO `user` VALUES (40, 'M小梅', '女', 21, '44444444444');
INSERT INTO `user` VALUES (50, 'L小亮', '男', 20, '55555555555');

1. READ UNCOMMITTED（未提交讀）

SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-UNCOMMITTED |
+-----------------------+-----------------+

• 髒讀

2. READ COMMITTED（已提交讀）

SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | READ-COMMITTED |
+-----------------------+-----------------+

• 不可重複讀

3. REPEATABLE READ（可重複讀）

SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+

• 可重複讀

• 幻讀

由於 MySQL 的 InnoDB 儲存引擎通過 MVCC（多版本併發控制）和 Next-Key (臨鍵鎖) 很大程度上避免了幻讀問題，所以無法演示大部分幻讀現象，但是InnoDB 儲存引擎並不能完全禁止幻讀。

4. SERIALIZABLE（序列化）

SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
SHOW VARIABLES LIKE 'transaction_isolation';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | SERIALIZABLE |
+-----------------------+-----------------+

SERIALIZABLE 通過強制事務序列執行，避免了髒讀、不可重複讀和幻讀的問題。

• 當事務A讀取一條記錄時，這條記錄會被加上讀鎖（共享鎖），其他事務可以查詢這一條記錄，但是無法修改

• 當事務A修改一條記錄時，這條記錄會被加上寫鎖（排它鎖），其他事務都無法查詢和修改這一條記錄

• 當事務A讀取範圍記錄時，該範圍都會被加上讀鎖（共享鎖），其他事務無法在該範圍內新增、修改記錄，也無法將範圍外的記錄修改為符合範圍條件的記錄。