READ COMMITTED 与 REPEATABLE READ
SQL Server 中的事务与错误处理
Miriam Antona
Software Engineer
READ COMMITTED
- 默认隔离级别
- 不能读取其他事务未提交或未回滚的修改
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
READ COMMITTED - 隔离级别对比
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| READ UNCOMMITTED | 是 | 是 | 是 |
| READ COMMITTED | 否 | 是 | 是 |
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 - 隔离级别对比
| 脏读 | 不可重复读 | 幻读 | |
|---|---|---|---|
| READ UNCOMMITTED | 是 | 是 | 是 |
| READ COMMITTED | 否 | 是 | 是 |
| REPEATABLE READ | 否 | 否 | 是 |
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 中的事务与错误处理
