MySQL 隔離級別設定與實際演示
設定隔離級別
Session 級別（當前連線）
sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
-- 執行查詢
COMMIT;
全域級別（影響所有新連線）
sql
SET GLOBAL TRANSACTION ISOLATION LEVEL READ COMMITTED;
-- 需要 SUPER 或 SYSTEM_VARIABLES_ADMIN 權限
持久化設定（my.cnf）
ini
[mysqld]
transaction-isolation = READ-COMMITTED
bash
sudo systemctl restart mysql
查看當前隔離級別
sql
SELECT @@session.transaction_isolation; -- Session 級別
SELECT @@global.transaction_isolation; -- 全域級別
Java JDBC 設定
java
Connection conn = dataSource.getConnection();
conn.setTransactionIsolation(Connection.TRANSACTION_SERIALIZABLE);
conn.setAutoCommit(false);
conn.commit();
隔離級別演示
使用
accounts表，示範各隔離級別下不同異常的發生與防止。
準備：建立 accounts 表
sql
DROP TABLE IF EXISTS accounts;
CREATE TABLE accounts (
id INT PRIMARY KEY,
name VARCHAR(50),
balance INT
);
INSERT INTO accounts VALUES (1, 'Alice', 100), (2, 'Bob', 200);
✅ 演示 1：髒讀（READ UNCOMMITTED）
開啟 Session A：
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- Don't commit yet
接著在 Session B：
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;
-- Sees Alice’s balance as 50 (even though it's uncommitted)
🔴 發生髒讀。如果 Session A 之後回滾，Session B 讀到的就是不存在的資料。
✅ 演示 2：防止髒讀（READ COMMITTED）
在 Session A：
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
UPDATE accounts SET balance = balance - 50 WHERE id = 1;
-- Don't commit yet
在 Session B：
sql
SET SESSION TRANSACTION ISOLATION LEVEL READ COMMITTED;
START TRANSACTION;
SELECT * FROM accounts WHERE id = 1;
-- Sees balance still 100 (no dirty read)
✅ 比較安全 —— 只看得到已提交的資料。
✅ 演示 3：不可重複讀（由 REPEATABLE READ 解決）
在 Session A：
sql
SET SESSION TRANSACTION ISOLATION LEVEL REPEATABLE READ;
START TRANSACTION;
SELECT balance FROM accounts WHERE id = 2;
-- returns 200
接著在 Session B：
sql
START TRANSACTION;
UPDATE accounts SET balance = 250 WHERE id = 2;
COMMIT;
回到 Session A：
sql
SELECT balance FROM accounts WHERE id = 2;
-- Still sees 200 (repeatable read)
COMMIT;
✅ 避免不可重複讀 —— 同一個查詢在同一個交易裡回傳同樣的結果。
✅ 演示 4：幻讀（由 SERIALIZABLE 解決）
在 Session A：
sql
SET SESSION TRANSACTION ISOLATION LEVEL SERIALIZABLE;
START TRANSACTION;
SELECT * FROM accounts WHERE balance > 100;
-- Returns Bob only
在 Session B：
sql
START TRANSACTION;
INSERT INTO accounts VALUES (3, 'Charlie', 150);
-- This will block until Session A commits or rolls back
✅ 以範圍鎖（range lock）避免幻讀。
🧠 總結
| 隔離級別 | 能防止什麼 | 適用場景 |
|---|---|---|
| Read Uncommitted | 什麼都不防 | 求快的分析查詢（不安全） |
| Read Committed | 髒讀 | 一般網站應用（PostgreSQL 預設） |
| Repeatable Read | 髒讀 + 不可重複讀 | 庫存、轉帳（MySQL 預設） |
| Serializable | 所有異常 | 訂票、金融等關鍵操作 |