SNAPSHOT

Transactions et gestion des erreurs dans SQL Server

Miriam Antona

Software Engineer

SNAPSHOT

Chaque modification est stockée dans la base de données tempDB
Vous voyez seulement les changements validés avant le début de la transaction SNAPSHOT et vos propres changements
Vous ne voyez aucun changement fait par d'autres transactions après le début de la transaction SNAPSHOT
Les lectures ne bloquent pas les écritures et les écritures ne bloquent pas les lectures
Des conflits de mise à jour peuvent survenir

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

SET TRANSACTION ISOLATION LEVEL SNAPSHOT

SNAPSHOT - comparaison des niveaux d'isolement

	Lectures sales	Lectures non répétables	Lectures fantômes
READ UNCOMMITTED	oui	oui	oui
READ COMMITTED	non	oui	oui
REPEATABLE READ	non	non	oui
SERIALIZABLE	non	non	non
SNAPSHOT	non	non	non

SNAPSHOT - exemple

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

Ce n'est pas bloqué !

SNAPSHOT - exemple

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

Ce n'est pas bloqué !

SNAPSHOT - résumé

Avantages :

Bonne cohérence des données : empêche les lectures sales, non répétables et fantômes sans blocage

Inconvénients :

Croissance de tempDB

Quand l'utiliser ? :

Quand la cohérence est essentielle et que vous ne voulez pas de blocages

READ COMMITTED SNAPSHOT

Modifie le comportement de READ COMMITTED

ALTER DATABASE myDatabaseName SET READ_COMMITTED_SNAPSHOT {ON|OFF};

OFF par défaut
Pour activer ON :

ALTER DATABASE myDatabaseName SET ALLOW_SNAPSHOT_ISOLATION ON;

En ON, chaque instruction READ COMMITTED ne voit que les changements validés avant le début de cette instruction
Aucun conflit de mise à jour

READ COMMITTED SNAPSHOT - exemple

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

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)

Sert à lire des données non validées
READ UNCOMMITTED s'applique à toute la connexion / WITH (NOLOCK) à une table précise
À utiliser sous tout niveau d'isolement quand vous voulez lire des données non validées de tables précises

WITH (NOLOCK) - exemple

Solde initial du compte 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        |

Passons à la pratique !

Transactions et gestion des erreurs dans SQL Server