Niveau d'isolation SERIALIZABLE

Transactions et gestion des erreurs dans SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Niveau d'isolation le plus strict

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Comparaison des niveaux d'isolation

	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

Verrouillage des enregistrements avec SERIALIZABLE

Requête avec WHERE sur une plage d'index → Verrouille seulement ces enregistrements
Requête sans plage d'index → Verrouille toute la table

SERIALIZABLE - requête basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Enregistrement verrouillé

SERIALIZABLE - requête basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Enregistrement verrouillé

Transaction 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Doît attendre !

SERIALIZABLE - requête basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Transaction 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Doît attendre !

SERIALIZABLE - requête basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

COMMIT TRAN

Transaction 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (2, 'Phantom', 'Ph', '[email protected]', 555666222);

Exécuté enfin !

SERIALIZABLE - requête basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

BEGIN TRAN
    SELECT * FROM customers 
    WHERE customer_id BETWEEN 1 AND 3;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------|-----|-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |

Transaction 2

...

INSERT INTO customers (customer_id, first_name, ...)
VALUES (200, 'Phantom', 'Ph', '[email protected]', 555666222);

Inserté instantanément !

SERIALIZABLE - requête non basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

Verrouille toute la table

Transaction 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Doît attendre !

SERIALIZABLE - requête non basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

| customer_id | first_name | last_name | ... | phone     |
|-------------|------------|-----------| ... |-----------|
| 1           | Dylan      | Smith     | ... | 555888999 |
...
| 10          | Carol      | York      | ... | 555148988 |

Transaction 2

...

INSERT INTO customers
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Doît attendre !

SERIALIZABLE - requête non basée sur une plage d'index

Transaction 1

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRAN
    SELECT * FROM customers;

    SELECT * FROM customers;

COMMIT TRAN

Transaction 2

...

INSERT INTO customers 
VALUES (100, 'Phantom', 'Ph', '[email protected]', 555666222);

Exécuté enfin !

SERIALIZABLE - récapitulatif

Avantages :

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

Inconvénients :

Vous pouvez être bloqué par une transaction SERIALIZABLE

Quand l'utiliser ?

Quand la cohérence des données est indispensable

Passons à la pratique !

Transactions et gestion des erreurs dans SQL Server