Transaction isolation levels
Transactions and Error Handling in SQL Server
Miriam Antona
Software Engineer
What is concurrency?
Concurrency: two or more transactions that read/change shared data at the same time.
Isolate our transaction from other transactions
Transaction isolation levels
READ COMMITTED(default)READ UNCOMMITTEDREPEATABLE READSERIALIZABLESNAPSHOT
SET TRANSACTION ISOLATION LEVEL
{READ UNCOMMITTED | READ COMMITTED | REPEATABLE READ | SERIALIZABLE | SNAPSHOT}
Knowing the current isolation level
SELECT CASE transaction_isolation_level
WHEN 0 THEN 'UNSPECIFIED'
WHEN 1 THEN 'READ UNCOMMITTED'
WHEN 2 THEN 'READ COMMITTED'
WHEN 3 THEN 'REPEATABLE READ '
WHEN 4 THEN 'SERIALIZABLE'
WHEN 5 THEN 'SNAPSHOT'
END AS transaction_isolation_level
FROM sys.dm_exec_sessions
WHERE session_id = @@SPID
| transaction_isolation_level |
|-----------------------------|
| READ COMMITTED |
READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- Least restrictive isolation level
- Read rows modified by another transaction which hasn't been committed or rolled back yet
READ UNCOMMITTED
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
- Least restrictive isolation level
- Read rows modified by other transactions without been committed/rolled back.
| Dirty reads | Non-repeatable reads | Phantom reads | |
|---|---|---|---|
| READ UNCOMMITTED | yes | yes | yes |
Dirty reads
Original balance account 5 = $35,000
Transaction1
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000
WHERE account_id = 5;
ROLLBACK TRAN;
Transaction2
...
...
...
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
SELECT current_balance
FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 30000,00 |
Non-repeatable reads
Transaction1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 35000,00 |
Transaction2
...
...
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000 WHERE account_id = 5;
COMMIT TRAN
Non-repeatable reads
Transaction1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 35000,00 |
SELECT * FROM accounts WHERE account_id = 5;
| current_balance |
|-----------------|
| 30000,00 |
Transaction2
...
...
BEGIN TRAN
UPDATE accounts
SET current_balance = 30000 WHERE account_id = 5;
COMMIT TRAN
Phantom reads
Transaction1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts
WHERE current_balance BETWEEN 45000 AND 50000
| account_number | ... | current_balance |
|----------------------|-----|-----------------|
| 55555555552020202020 | ... | 50000,00 |
Transaction2
...
...
BEGIN TRAN
INSERT INTO accounts
VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN
Phantom reads
Transaction1
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
BEGIN TRAN
SELECT * FROM accounts
WHERE current_balance BETWEEN 45000 AND 50000
| account_number | ... | current_balance |
|----------------------|-----|-----------------|
| 55555555552020202020 | ... | 50000,00 |
SELECT * FROM accounts
WHERE current_balance BETWEEN 45000 AND 50000
| account_number |...| current_balance |
|----------------------|---|-----------------|
| 55555555553939393939 |...| 45000,00 | Phantom!
| 55555555552020202020 |...| 50000,00 |
Transaction2
...
...
BEGIN TRAN
INSERT INTO accounts
VALUES ('55555555553939393939', 1, 45000)
COMMIT TRAN
READ UNCOMMITTED - summary
Pros:
- Can be faster, doesn't block other transactions.
Cons:
- Allows dirty reads, non-repeatable reads, and phantom reads.
When to use it?:
- Don't want to be blocked by other transactions but don't mind concurrency phenomena.
- You explicitly want to watch uncommitted data.
Let's practice!
Transactions and Error Handling in SQL Server
