READ COMMITTED & REPEATABLE READ

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

READ COMMITTED

Default isolation level
Can't read data modified by other transaction that hasn't committed or rolled back

SET TRANSACTION ISOLATION LEVEL READ COMMITTED

READ COMMITTED - isolation level comparison

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes

READ COMMITTED - preventing dirty reads

Original balance account 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;

Has to wait!

READ COMMITTED - preventing dirty reads

Original balance account 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 - selecting without waiting

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 - summary

Pros:

Prevents dirty reads

Cons:

Allows non-repeatable and phantom reads
You can be blocked by another transaction

When to use it?:

You want to ensure that you only read committed data, not non-repeatable and phantom reads

REPEATABLE READ

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

Can't read uncommitted data from other transactions
If some data is read, other transactions cannot modify that data until REPEATABLE READ transaction finishes

REPEATABLE READ - isolation level comparison

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	yes	yes	yes
READ COMMITTED	no	yes	yes
REPEATABLE READ	no	no	yes

REPEATABLE READ - preventing non-repeatable reads

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;

Has to wait!

REPEATABLE READ - preventing non-repeatable reads

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;

Has to wait!

REPEATABLE READ - preventing non-repeatable reads

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 - summary

Pros:

Prevents other transactions from modifying the data you are reading, non-repeatable reads
Prevents dirty reads

Cons:

Allows phantom reads
You can be blocked by a REPEATABLE READ transaction.

When to use it?:

Only want to read committed data and don't want other transactions to modify what you are reading. You don't care if phantom reads occur

Let's practice!

Transactions and Error Handling in SQL Server