SNAPSHOT

Transacties en foutafhandeling in SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Elke wijziging wordt opgeslagen in de tempDB-database
Je ziet alleen gecommitte wijzigingen vóór start van de SNAPSHOT-transactie en je eigen wijzigingen
Je ziet geen wijzigingen van andere transacties na start van de SNAPSHOT-transactie
Lezen blokkeert schrijven niet en schrijven blokkeert lezen niet
Mogelijke updateconflicten

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - vergelijking isolatieniveaus

	Dirty reads	Non-repeatable reads	Phantom reads
READ UNCOMMITTED	ja	ja	ja
READ COMMITTED	nee	ja	ja
REPEATABLE READ	nee	nee	ja
SERIALIZABLE	nee	nee	nee
SNAPSHOT	nee	nee	nee

SNAPSHOT - voorbeeld

Transactie1

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       |

Transactie2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Hij wordt niet geblokkeerd!

SNAPSHOT - voorbeeld

Transactie1

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       |

Transactie2

...

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

    UPDATE accounts 
    SET current_balance = 30000 WHERE account_id = 1;

    SELECT * FROM accounts;
COMMIT TRAN

Hij wordt niet geblokkeerd!

SNAPSHOT - samenvatting

Voordelen:

Goede dataconsistentie: voorkomt dirty, non-repeatable en phantom reads zonder blokkeren

Nadelen:

tempDB groeit

Wanneer gebruiken?:

Als consistentie cruciaal is en je geen blokkades wilt

READ COMMITTED SNAPSHOT

Wijzigt het gedrag van READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

Standaard OFF
Om ON te gebruiken:

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

Met ON ziet elke READ COMMITTED-statement alleen gecommitte wijzigingen vóór start van die statement
Geen updateconflicten

READ COMMITTED SNAPSHOT - voorbeeld

Transactie1

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

COMMIT TRAN

Transactie2

SET TRANSACTION ISOLATION LEVEL READ COMMITTED
BEGIN TRAN

    SELECT current_balance FROM accounts 
    WHERE account_id = 1;

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

READ COMMITTED SNAPSHOT - voorbeeld

Transactie1

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

COMMIT TRAN

Transactie2

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)

Gebruikt om niet-gecommitte data te lezen
READ UNCOMMITTED geldt voor de hele connectie / WITH (NOLOCK) voor een specifieke tabel
Gebruik onder elke isolatieniveau als je alleen on-gecommitte data uit specifieke tabellen wilt lezen

WITH (NOLOCK) - voorbeeld

Oorspronkelijk saldo rekening 5 = $35.000

Transactie1

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

Transactie2

...

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

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

Laten we oefenen!

Transacties en foutafhandeling in SQL Server