READ COMMITTED 與 REPEATABLE READ
SQL Server 的交易與錯誤處理
Miriam Antona
Software Engineer
READ COMMITTED
- 預設隔離層級
- 不能讀取其他交易尚未提交或回滾所修改的資料
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
READ COMMITTED－隔離層級比較
| Dirty reads | Non-repeatable reads | Phantom reads | |
|---|---|---|---|
| READ UNCOMMITTED | yes | yes | yes |
| READ COMMITTED | no | yes | yes |
READ COMMITTED－避免髒讀
原始餘額帳戶 5 = $35,000
Transaction1
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
Transaction2
...
...
...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;
必須等待！
READ COMMITTED－避免髒讀
原始餘額帳戶 5 = $35,000
Transaction1
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
COMMIT TRAN;
Transaction2
...
...
...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 30000,00 |
READ COMMITTED－無需等待的查詢
Transaction1
BEGIN TRAN
SELECT current_balance
FROM accounts WHERE account_id = 5;
Transaction2
...
...
SET TRANSACTION ISOLATION LEVEL READ COMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 35000,00 |
READ COMMITTED－重點整理
優點：
- 可防止髒讀
缺點：
- 允許不可重複讀與幻讀
- 可能被其他交易阻擋
何時使用？
- 你只想讀取已提交資料；可接受不可重複讀與幻讀
REPEATABLE READ
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
- 不能讀取其他交易未提交的資料
- 一旦讀取了資料，在 REPEATABLE READ 交易結束前，其他交易不能修改該資料
REPEATABLE READ－隔離層級比較
| Dirty reads | Non-repeatable reads | Phantom reads | |
|---|---|---|---|
| READ UNCOMMITTED | yes | yes | yes |
| READ COMMITTED | no | yes | yes |
| REPEATABLE READ | no | no | yes |
REPEATABLE READ－避免不可重複讀
Transaction1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT current_balance FROM accounts
WHERE account_id = 5;
| current_balance |
|-----------------|
| 35000,00 |
Transaction2
...
...
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
必須等待！
REPEATABLE READ－避免不可重複讀
Transaction1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT current_balance FROM accounts
WHERE account_id = 5;
SELECT current_balance FROM accounts
WHERE account_id = 5;
| current_balance |
|-----------------|
| 35000,00 |
COMMIT TRAN
Transaction2
...
...
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
必須等待！
REPEATABLE READ－避免不可重複讀
Transaction1
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
BEGIN TRAN
SELECT current_balance FROM accounts
WHERE account_id = 5;
SELECT current_balance FROM accounts
WHERE account_id = 5;
COMMIT TRAN
Transaction2
...
...
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
(1 rows affected)
REPEATABLE READ－重點整理
優點：
- 防止你正在讀取的資料被其他交易修改，避免不可重複讀
- 防止髒讀
缺點：
- 允許幻讀
- 可能被
REPEATABLE READ交易阻擋
何時使用？
- 只想讀取已提交資料，且不希望其他交易修改你正在讀的內容；不在意是否發生幻讀
一起來練習吧！
SQL Server 的交易與錯誤處理
