萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 事務隔離級別實測:READ UNCOMMITTED 到 SERIALIZABLE 的資料可見性差異演示

MySQL 四大隔離級別實測:親眼看見髒讀、不可重複讀、幻讀

事務隔離級別決定了「一個事務能看到另一個事務的哪些中間狀態」。本文在 MySQL 5.6.10 (RedHat 5.8) 上,雙會話並發實測四個級別下的資料可見性差異,附完整操作指令與現象解析。

ACID 與隔離級別速查

隔離級別髒讀不可重複讀幻讀鎖/機制
READ UNCOMMITTED✅ 可能✅ 可能✅ 可能無鎖,直接讀最新版本
READ COMMITTED❌ 不可能✅ 可能✅ 可能讀取已提交版本(每次 SELECT 重新建立 ReadView)
REPEATABLE READ (InnoDB 預設)❌ 不可能❌ 不可能⚠️ 可能 (MVCC 快照讀不鎖間隙)事務啟動時建立 ReadView,快照讀不加鎖
SERIALIZABLE❌ 不可能❌ 不可能❌ 不可能讀加共享鎖,寫加排他鎖,完全串行化

實驗環境

  • OS:RedHat 5.8 (2.6.18-308.el5)
  • MySQL:5.6.10 (源碼編譯,啟用 innodb_file_per_table)
  • 測試庫:jiaowu(教務系統模擬資料)
  • 工具:Xmanager 開啟兩個終端會話 (Session A / Session B)

前置準備:關閉自動提交、設定隔離級別

-- 兩個會話皆執行
SET SESSION autocommit = 0;
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;  -- 依實驗逐級調整
-- 驗證
SELECT @@autocommit, @@tx_isolation;

實驗場景:Session A 修改資料,Session B 觀察可見性

-- 初始資料
SELECT * FROM student WHERE id = 1;
-- id=1, name='Tom', score=80

級別 1:READ UNCOMMITTED(髒讀)

-- Session A
BEGIN;
UPDATE student SET score = 90 WHERE id = 1;
-- 未 COMMIT

-- Session B
SELECT score FROM student WHERE id = 1;
-- 結果:90 (讀到未提交資料 = 髒讀)

-- Session A
ROLLBACK;

-- Session B 再查
SELECT score FROM student WHERE id = 1;
-- 結果:80 (髒資料消失,業務邏輯可能已基於 90 做決策)

級別 2:READ COMMITTED(不可重複讀)

-- Session A
BEGIN;
UPDATE student SET score = 90 WHERE id = 1;
COMMIT;

-- Session B (同一事務內兩次 SELECT)
BEGIN;
SELECT score FROM student WHERE id = 1;  -- 80
-- Session A COMMIT 後
SELECT score FROM student WHERE id = 1;  -- 90 (前後不一致 = 不可重複讀)
COMMIT;

級別 3:REPEATABLE READ(可重複讀,但可能幻讀)

-- Session B 開啟事務
BEGIN;
SELECT * FROM student WHERE score > 85;  -- 假設回傳 0 列

-- Session A 插入新資料並提交
BEGIN;
INSERT INTO student (name, score) VALUES ('Jerry', 95);
COMMIT;

-- Session B 再次同條件查詢
SELECT * FROM student WHERE score > 85;  -- 仍回傳 0 列(快照讀,無幻讀)
-- 但若 Session B 執行當前讀 (鎖定讀)
SELECT * FROM student WHERE score > 85 FOR UPDATE;  -- 會看到 Jerry (幻讀出現)
COMMIT;

關鍵:RR 下快照讀 (普通 SELECT) 不會幻讀,當前讀 (LOCK IN SHARE MODE / FOR UPDATE) 會幻讀。InnoDB 靠 Next-Key Lock (記錄鎖 + 間隙鎖) 解決當前讀幻讀。

級別 4:SERIALIZABLE(完全串行化)

-- Session B
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN;
SELECT * FROM student WHERE score > 85;  -- 隱式加共享鎖

-- Session A 嘗試插入
BEGIN;
INSERT INTO student (name, score) VALUES ('Jerry', 95);
-- 阻塞,等待 Session B COMMIT/ROLLBACK
-- Session B COMMIT 後,Session A 才能插入成功

效能極差,極少生產使用。

MySQL 實際預設與建議

  • MySQL 5.7/8.0 預設 REPEATABLE READ。
  • Oracle/PostgreSQL/SQL Server 預設 READ COMMITTED。
  • 建議:大多數 OLTP 業務用 READ COMMITTED + 行鎖**(SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED),效能與一致性平衡最佳;需嚴格一致性(如財務對賬)再升級 RR 或應用層邏輯補強。

查看當前隔離級別與鎖資訊

SELECT @@tx_isolation;
SHOW ENGINE INNODB STATUS\G  -- 觀察 TRANSACTIONS 區塊鎖等待
SELECT * FROM performance_schema.data_locks;  -- 8.0+ 鎖監控
copyright © 萬盛學電腦網 all rights reserved