avril
2012
Un article de zinzineti
Une transaction SQL est caractérisée par un mode d’accès (READ ONLY ou READ WRITE),un niveau d’isolation (READ UNCOMMITED,READ COMMITED,REPEATABLE READ,SERIALIZABLE) et un niveau de diagnostic (DIAGNOSTIC SIZE).Le mode d’accès permet de préciser explicitement les opérations (lecture seule ou lecture/écriture) à réaliser sur la transaction. Le niveau d’isolation permet d’indiquer avec quel acharnement on souhaite protéger les transactions les unes des autres. Le niveau de diagnostic permet de limiter le nombre d’ereur dans la transaction. Selon la norme SQL une transaction doit être paramétrée de la façon suivante :
[ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED |
REPEATABLE READ | SERIALIZABLE}]
[DIAGNOSTIC SIZE int]
LOCAL : indique que le mode d’accès et le niveau d’isolation indiqué s’applique à la session courante. si le mot clé LOCAL n’est pas indiqué la transaction suivante peut changer même si on est sur la même session
READ ONLY : indique que la transaction est une opération de lecture seule
READ WRITE : indique que la transaction peut lire ou modifier des données dans la base
ISOLATION LEVEL : indique le niveau de protection des transactions
DIAGNOSTIC SIZE int : limite le nombre d’erreur dans la transaction. plus ce nombre est élévé plus il y a consommation de ressources système. la commande GET DIAGNOSTICS permet de recupérer les messages d’erreurs.
SQL SERVER ne respecte pas rigoureusement cette syntaxe. Il est impossible sous SQL SERVER de préciser de façon explicite le :
1) mode d’accès (READ_ONLY ou READ_WRITE)
2) niveau de DIAGNOSTIC
Seul le niveau d’isolation peut être explicitement indiqué sous SQL SERVER. Rappelons que les niveaux d’isolation permettent de resoudre les problèmes de lecture sale (dirty read), lecture qui ne peut être répétée (non-repeatable read) et de lecture fantôme (phantom read). Dans ce billet nous allons examiner ces problèmes et mettre en évidence le mécanisme de verrou (lock) posé par SQL SERVER sur une table.
Dans les exemples qui vont suivre nous allons utiliser deux sessions via SQL Server Management Studio (ssms)
Pour passer d’une session à une autre j’ai préféré la présentation ci-dessous pour plus de confort visuel.
Une fois les deux fenêtre ouvertes, clique droit sur l’onglet
Choisir l’affichage verticale des sessions
Voyons maintenant les différents niveaux d’isolation.
=> READ COMMITED (c’est niveau d’isolation par défaut sous SQL SERVER) : isole la transaction des problèmes de lecture sale
–> Session 1 : dans la session 1, exécuter la requête de création et de remplissage de la table
————–
IF OBJECT_ID('ExpertSGBD') IS NOT NULL DROP TABLE ExpertSGBD
CREATE TABLE ExpertSGBD (id int,prenom varchar(20),nbpoints int,date_inscription datetime)
-- insertion des données dans la table
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (1,'Frédéric',17581,'20020501')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (2,'David',6704,'20020801')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (3,'Nicolas',8488,'20050101')
-- vérification des données insérées
SELECT * FROM ExpertSGBD
--Démarrer dans la session 1 la transaction suivante :
BEGIN TRAN
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (4,'Etienne',2408,'20100301')
-- Vérifier l'insertion de la nouvelle ligne dans la session 1, remarquer que jusque là on n'a pas encore COMMITé ni ROLLBACKé la transaction
SELECT * FROM ExpertSGBD
–> Session 2 : dans la session 2, exécuter la requête suivante :
————-
SELECT * FROM ExpertSGBD
# Résultat session 2 : La requête ne s’exécute pas, elle reste bloquée.
## Explication : La transaction en cours d’exécution dans la session 1 fait que SQL Server a posé un verrou (LOCK) sur la table, il est donc impossible à d’autre session de lire les données de la table
–> Session 1 : Revenons dans la session 1 et validons la transaction en cours avec un COMMIT
————–
COMMIT TRANSACTION
–> Session 2 : Dans la fenêtre de la session 2, exécutons la requête suivante :
————–
SELECT * FROM ExpertSGBD
# Résultat session 2 : La requête s’exécute correctement et la nouvelle ligne apparaît après COMMIT (validation) dans la session 1
## Explication : après validation (COMMIT) de la transaction dans la session 1, le verrou posé par SQL SERVER sur la table est lévé et les données ainsi validées sont accessibles à toutes les sessions
Mais comment éviter le verrou sur la totalité des lignes de la TABLE et permettre l’accessibilité des données initiales validées avant le démarrage de la transaction ?
## Astuce : faire en sorte que SQL Server utilise par exemple un index et pas un Table Scan lors de la lecture des données.
Exemple
——–
–> Session 1 : dans la session 1, exécuter la requête de création et de remplissage de la table
————–
CREATE TABLE ExpertSGBD (id int,prenom varchar(20),nbpoints int,date_inscription datetime)
-- insertion des données dans la table
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (1,'Frédéric',17581,'20020501')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (2,'David',6704,'20020801')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (3,'Nicolas',8488,'20050101')
-- posons un index sur la table
CREATE INDEX IX ON ExpertSGBD (id,prenom,nbpoints,date_inscription)
–> Session 1 : Démarrer depuis la session 1 la transaction suivante :
————–
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (4,'Etienne',2408,'20100301')
-- Vérifier l'insertion de la nouvelle ligne depuis la session 1, remarquer que jusque là on n'a pas encore COMMITé ni ROLLBACKé la transaction
SELECT * FROM ExpertSGBD
–> Session 2 : Dans la fenêtre de la session 2, exécuter la requête suivante
————–
SELECT * FROM ExpertSGBD WHERE ID < 4
# Résultat session 2 : Cette fois-ci la requête s’exécute. Les données présentes dans la table avant le début de transaction sont affichées.
## Explication : SQL Server utilise l’index créé (INDEX SEEK) pour parcourir la table. une analyse du plan d’exécution de la requête le montre. Par contre la nouvelle ligne insérée (non COMMITée) visible depuis la session 1 n’est pas visible par la session 2 car l’exécution de la requête SELECT * FROM ExpertSGBD WHERE ID < 5 bloque.
–> Session 1 : Faire un COMMIT TRANSACTION pour valider la transaction
————–
COMMIT TRANSACTION
Quelles sont les limites du niveau d’isolation READ COMMITED ?
READ COMMITED ne protège pas de la lecture non répétable, c’est quoi la lecture non répétable ? Pour une même requête, les données affichées variées. Prenons un exemple :
Exemple
——–
–> Session 1 : Exécuter la requête de création et de remplissage de la table
————–
— table
CREATE TABLE ExpertSGBD (id int,prenom varchar(20),nbpoints int,date_inscription datetime)
-- insertion des données dans la table
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (1,'Frédéric',17581,'20020501')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (2,'David',6704,'20020801')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (3,'Nicolas',8488,'20050101')
— Exécutons dans la session 1 la requête suivante :
SELECT * FROM ExpertSGBD WHERE nbpoints > 17580 -- on a 1 ligne qui s'affiche
# Résultat session 1 : On a 1 ligne affichée
–> Session 2 : Depuis la session 2 exécuter une requête de mise à jour :
————–
SET nbpoints = 17580
WHERE id = 1
# Résultat session 2 : Update effectué avec succès
–> Session 1 : Rejouons à nouveau la même requête SELECT que précedemment
————–
SELECT * FROM ExpertSGBD WHERE nbpoints > 17580 -- on a 0 ligne qui s'affiche
# Résultat session 1 : Cette fois-ci on a 0 ligne ! la requête SELECT * FROM ExpertSGBD WHERE nbpoints > 17580 n’est donc pas repetable sous la session 1 un coup on a 1 ligne et un autre coup on a 0 ligne ! Voilà un des problèmes du niveau d’isolation READ COMMITED
=> REPEATABLE READ : protège la transaction des problèmes de lecture sale et lecture qui ne peut être répétée
— Session 1 : Exécuter la requête de création et de remplissage de la table
————–
— Création de la table
CREATE TABLE ExpertSGBD (id int,prenom varchar(20),nbpoints int,date_inscription datetime)
-- insertion des données dans la table
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (1,'Frédéric',17581,'20020501')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (2,'David',6704,'20020801')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (3,'Nicolas',8488,'20050101')
–> Session 1 : Démarrer depuis la session 1 la transaction suivante :
————–
BEGIN TRAN
SELECT * FROM ExpertSGBD WHERE nbpoints > 17580 -- on a une ligne qui qui s'affiche
–> Session 2 : Depuis la session 2, mettons à jour une ligne de la table à l’aide de la requête suivante :
————–
SET nbpoints = 17580
WHERE id = 1
# Résultat session 2 : Impossible de mettre à jour la ligne, LOCK de la Table
–> Session 1 : Rejouons à nouveau dans la session 1 la requête suivante :
————–
SELECT * FROM ExpertSGBD WHERE nbpoints > 17580 -- on a une ligne qui s'affiche
# Résultat session 1 : On a toujours une ligne affichée, la requête renvoie toujours les mêmes données.
## Explication : SQL SERVER a posé un verrou sur les lignes en cours de lecture, ainsi aucune modification de ces lignes n’est possible par une autre session. Le problème de lecture qui ne peut être répétée est ainsi résolu avec le niveau d’isolation REPEATABLE READ
–> Session 1 : Dans la session 1 faisons un COMMIT pour valider la transaction
————–
COMMIT TRANSACTION
Quelles sont les limites du niveau d’isolation REPEATABLE READ ? REPEATABLE READ resoud le problème de lecture qui ne peut être répétée, mais pas le problème de lecture fantôme. Prenons un exemple.
Exemple
————–
–> Session 1 : Exécuter la requête de création et de remplissage de la table
————–
— Création de la table
CREATE TABLE ExpertSGBD (id int,prenom varchar(20),nbpoints int,date_inscription datetime)
-- insertion des données dans la table
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (1,'Frédéric',17581,'20020501')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (2,'David',6704,'20020801')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (3,'Nicolas',8488,'20050101')
–> Session 1 : Démarrer depuis la session 1 la transaction suivante :
————–
BEGIN TRAN
SELECT * FROM ExpertSGBD -- on a 3 lignes qui qui s'affiche
–> Session 2 : Depuis la session 2, insérons une nouvelle à l’aide de la requête suivante :
————–
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (4,'Etienne',2408,'20100301')
# Résultat session 2 : insertion effectuée avec succès depuis la session 2
–> Session 1 : Rejouons dans la session 1, la requête SELECT * FROM ExpertSGBD
————–
SELECT * FROM ExpertSGBD -- on a 4 lignes qui s'affiche
# Résultat session 1 : Cette fois-ci on a 4 lignes ! une ligne fantôme apparaît ! le niveau d’isolation REPEATABLE READ ne resoud pas ce problème de ligne fantôme.
–> Session 1 : Validons la transaction (COMMIT)
————–
COMMIT TRANSACTION
=> SERIALIZABLE : protège la transaction des problèmes de lecture sale, de lecture qui ne peut être répétée et de lecture fantôme
–> Session 1 : Exécuter la requête de création et de remplissage de la table
————–
IF OBJECT_ID('ExpertSGBD') IS NOT NULL DROP TABLE ExpertSGBD
CREATE TABLE ExpertSGBD (id int,prenom varchar(20),nbpoints int,date_inscription datetime)
-- insertion des données dans la table
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (1,'Frédéric',17581,'20020501')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (2,'David',6704,'20020801')
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (3,'Nicolas',8488,'20050101')
–> Session 1 : Démarrer depuis la session 1 la transaction suivante :
————–
BEGIN TRAN
SELECT * FROM ExpertSGBD -- on a 3 lignes qui qui s'affiche
–> Session 2 : Depuis la session 2, insérons une nouvelle à l’aide de la requête suivante :
————–
INSERT INTO ExpertSGBD (id,prenom,nbpoints,date_inscription) VALUES (4,'Etienne',2408,'20100301')
# Résultat session 2 : Impossible de faire l’INSERT ! depuis la session 2
–> Session 2 : depuis la session 2, essayer un UDPATE
————–
SET nbpoints = 17580
WHERE id = 1
# Résultat session 2 : Impossible de faire un UPDATE sur la table !
–> Session 1 : Rejouons dans la session 1, la requête SELECT * FROM ExpertSGBD
————–
SELECT * FROM ExpertSGBD
# Résultat session 1 : On a toujours les mêmes données que précédemment : pas de lecture sale, pas de lecture qui ne peut être répétée, pas de lecture fantôme
–> Session 1 : Validons la transaction (COMMIT)
————–
COMMIT TRANSACTION
=> READ UNCOMMITED
Le niveau d’isolation READ UNCOMMITED est le niveau de protection le plus bas. READ UNCOMMITED n’assure aucune protection. Ne résoud donc aucun des problèmes d’intégrité de données : lecture sale, lecture non repetatable,lecture fantôme. Il existe plusieurs possibilités pour positionner une transaction sur ce niveau d’isolation :
Exemple 1 : SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
————–
BEGIN TRAN
SELECT * FROM ExpertSGBD
COMMIT TRANSACTION
Exemple 2 : Utilisation dans la requête du mot clé (HINT) READUNCOMMITTED
————–
SELECT * FROM ExpertSGBD WITH (READUNCOMMITTED)
Exemple 3 : Utilisation dans la requête du mot clé NOCLOCK
————–
SELECT * FROM ExpertSGBD WITH (NOCLOCK)
——————xxx——————
On peut conclure, au travers de ces scénarii que le niveau SERIALIZABLE assure une parfaite protection (isolation) des transactions et donc l’intégrité des données. Les transactions sont dans ce cas traitées en série au sein du SGBD. Ce mécanisme de traitement en série des transactions SQL peut nuire au performance. Et c’est ce niveau SERIALIZABLE que la norme SQL propose comme niveau par défaut pour les SGBDs. À mon humble avis lors du développement des procédures SQL pour les applications il faut essayer de trouver l’équilibre entre performance (niveau d’isolation faible, donc pas de traitement en série) et intégrité (niveau d’isolation élevé) de données.
—————————–
Etienne ZINZINDOHOUE
—————————–