SNAPSHOT

Transactions and Error Handling in SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Every modification is stored in the tempDB database
Only see committed changes that occurred before the start of the SNAPSHOT transaction and own changes
Can't see any changes made by other transactions after the start of the SNAPSHOT transaction
Readings don't block writings and writings don't block readings
Can have update conflicts

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - 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
SERIALIZABLE	no	no	no
SNAPSHOT	no	no	no

SNAPSHOT - example

Transaction1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaction2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

It is not blocked!

SNAPSHOT - example

Transaction1

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

BEGIN TRAN
    SELECT * FROM accounts;

    SELECT * FROM accounts;

|account_id|account_number      |...| current_balance|
|----------|--------------------|---|----------------|
|1         |55555555551234567890|...| 25000,00       |
|2         |55555555559876543210|...| 200,00         |
|...       |...                 |...| ...            |
|15        |55555555551234567890|...| 25000,00       |

Transaction2

...

BEGIN TRAN
    INSERT INTO accounts
    VALUES (11111111111111111111, 1, 25000);

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

It is not blocked!

SNAPSHOT - summary

Pros:

Good data consistency: Prevents dirty, non-repeatable and phantom reads without blocking

Cons:

tempDB increases

When to use it?:

When data consistency is a must and don't want blocks

READ COMMITTED SNAPSHOT

Changes the behavior of READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

OFF by default
To use ON:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Set to ON, makes every READ COMMITTED statement can only see committed changes that occurred before the start of that statement
Can't have update conflicts

READ COMMITTED SNAPSHOT - example

Transaction1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 35000,00        |

READ COMMITTED SNAPSHOT - example

Transaction1

 SET TRANSACTION ISOLATION LEVEL READ COMMITTED
 BEGIN TRAN 
   UPDATE accounts
   SET current_balance = 30000
   WHERE account_id = 1;

COMMIT TRAN

Transaction2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

| current_balance |
|-----------------|
| 30000,00        |

WITH (NOLOCK)

Used to read uncommitted data
READ UNCOMMITTED applies to the entire connection / WITH (NOLOCK) applies to a specific table
Use under any isolation level when you just want to read uncommitted data from specific tables

WITH (NOLOCK) - example

Original balance account 5 = $35,000

Transaction1

BEGIN TRAN
  UPDATE accounts
  SET current_balance = 30000
  WHERE account_id = 5;

Transaction2

...

SELECT current_balance
FROM accounts WITH (NOLOCK) 
WHERE account_id = 5;

| current_balance |
|-----------------|
| 30000,00        |

Let's practice!

Transactions and Error Handling in SQL Server