Lors de transactions concurrentes, des verrous peuvent être posés pour éviter les situations
suivantes :
– Mise à jour perdue : Une mise à jour peut être perdue lorsqu’une transaction écrase les
modifications effectuées par une autre transaction.
– Lecture incorrecte : Se produit lorsqu’une transaction lit des données non validées
provenant d’une autre transaction.
– Lecture non renouvelable : Se produit lorsque, une transaction devant lire la même ligne
plusieurs fois, la ligne est modifiée par une autre transaction et donc produit des valeurs
différentes à chaque lecture.
– Lectures fantômes : Se produit lorsqu’une autre transaction insert une ligne au sein de la
transaction en cours.
Un verrou partagé appliqué à une ressource par une première transaction autorise
l’acquisition par une deuxième transaction d’un verrou partagé sur cette ressource, même si
la première transaction n’est pas terminée. Un verrou partagé sur une ligne est libéré dès la
lecture de la ligne suivante.
Un verrou partagé est alloué pour une instruction SELECT.
SQL Server utilise des verrous exclusifs lorsqu’une instruction INSERT, DELETE ou
UPDATE est exécutée.
Une seule transaction peut acquérir un verrou exclusif sur une ressource ; une transaction ne
peut pas acquérir un verrou exclusif sur une ressource tant qu’il existe des verrous partagés ;
Une transaction ne peut pas acquérir un verrou partagé sur une ressource déjà pourvue d’un
verrou exclusif.
182
SQL Server permet de définir un niveau d’isolement qui protège une transaction vis-à-vis des
autres.
SET TRANSACTION ISOLATION LEVEL {READ COMMITTED | READ UNCOMMITTED |
REPEATABLE READ | SNAPSHOT | SERIALIZABLE}
Read UnCommitted : Indique à SQL Server de ne pas placer de verrous partagés : Une
transaction peut lire des données modifiées non encore validées par une autre transaction.
Des lectures incorrectes peuvent se produire.
Read Committed (Option par défaut) : Indique à SQL Server d’utiliser des verrous
partagés pendant la lecture : une transaction ne peut pas lire les données modifiées, mais
non validées d’une autre transaction. La lecture incorrecte ne peut pas se produire.
Repeatable Read : SQL Server place des verrous partagés sur toutes les données lues par
chaque instruction de la transaction et les maintient jusqu'à la fin de la transaction : Une
transaction ne peut pas lire des données modifiées, mais pas encore validées par une autre
transaction, et ne peut modifier les données lues par la transaction active tant que celle-ci
n'est pas terminée.
La lecture incorrecte et la lecture non renouvelable ne peuvent pas se produire.
Snapshot : Une transaction n’a pas accès aux modifications de données apportées par une
autre transaction : Les données vues par la première transaction sont les données antérieures
au début de la deuxième transaction.
Serializable : Une transaction ne peut pas lire des données modifiées, mais pas encore
validées par une autre transaction, et ne peut modifier les données lues par la transaction
active tant que celle-ci n'est pas terminée.
Une transaction ne peut pas insérer de nouvelles lignes avec des valeurs de clés comprises
dans le groupe de clés lues par des instructions de la transaction active, tant que celle-ci n'est
pas terminée.
La lecture incorrecte la lecture non renouvelable et les lectures fantômes ne peuvent pas se
produire.
Exemple : La transaction de l’exemple suivant n’est pas terminée, mais nous avons utilisé
l’option READ UNCOMMITTED.
USE Papyrus
GO
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
GO
BEGIN TRAN
PRINT 'Valeur de Trancount: ' + CAST(@@TRANCOUNT AS varchar(5))
PRINT 'Avant Maj : '
SELECT NOMFOU FROM vente.FOURNISSEUR WHERE NUMFOU = 120
UPDATE vente.FOURNISSEUR SET NOMFOU = 'GROSBRIBAN' WHERE NUMFOU = 120
PRINT 'Après Maj : '
SELECT NOMFOU FROM vente.FOURNISSEUR WHERE NUMFOU = 120
PRINT 'Valeur de Trancount : ' + CAST(@@TRANCOUNT AS varchar(5))
Nous interrogeons notre table FOURNISSEUR.
SELECT *
FROM vente.FOURNISSEUR
WHERE NUMFOU = 120
183
Cependant, on peut toujours annuler la transaction ou la valider par les instructions COMMIT
TRAN ou ROLLBACK TRAN.
Le niveau d’isolement spécifie le comportement de verrouillage par défaut de toutes les
instructions de la session (connexion).
Plus le niveau d’isolement est élevé, plus les verrous sont maintenus longtemps et plus ils
sont restrictifs.
La commande DBCC USEROPTIONS renvoie entre autres, le niveau d’isolement de la
session.
Il est possible de définir la durée maximale pendant laquelle SQL Server permet à une
transaction d’attendre le déverrouillage d’une ressource bloquée. La variable globale
@@lock_timeout donne le temps d’attente défini.
SET LOCK_TIMEOUT délai_attente positionne le délai, en millisecondes, avant que ne soit
renvoyé un message d’erreur (-1 indique qu’il n’y a pas de délai par défaut).
