Niveau d'isolement SERIALIZABLE

Transactions et gestion des erreurs dans SQL Server

Miriam Antona

Software Engineer

SERIALIZABLE

Niveau d'isolement le plus restrictif

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

Comparaison des niveaux d'isolement

	Lectures sales	Lectures non reproductibles	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 clause WHERE sur un intervalle d'index -> Verrouille seulement ces enregistrements
Requête sans intervalle d'index -> Verrouille toute la table

SERIALIZABLE - requête fondée sur un intervalle 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 fondée sur un intervalle 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 fondée sur un intervalle 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 fondée sur un intervalle 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);

Enfin exécutée !

SERIALIZABLE - requête fondée sur un intervalle 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);

Insertion instantanée !

SERIALIZABLE - requête sans intervalle 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 sans intervalle 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 sans intervalle 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);

Enfin exécutée !

SERIALIZABLE - résumé

Avantages :

Bonne cohérence des données : empêche les lectures sales, non reproductibles 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