Les méthodes d’isolation des données se décomposent en 2 grandes familles :

Optimiste

Il est probable que le risque qu’une autre transaction aille modifier les données de la transaction en cours et faible. En conséquence la vérification des valeurs se fera au moment de l’écriture. Suivant le mode d’isolation le moteur renverra une erreur ou non si les données ont été modifiée par une autre transaction. Cette méthode est implémentée depuis SQL Server 2005 avec le versionnement des données.

Pessimiste

Le risque de modification des données de la transaction en cours par une autre est élevé. Le moteur va donc interdire l’accès aux données qui sont modifiées par la transaction jusqu’à ce que la transaction soit validée ou annulée. Les autres transactions ayant besoins des données actuellement modifiées se mettront en attente au besoin. Cette méthode est implémentée dans toutes les versions de SQL Server et utilise des verrous pour bloquer les accès.

Voici 2 exemples pour vous permettre de bien appréhender ces concepts. Prenons 2 sites de réservation de voyages en ligne, vendant des billets d’avion ou billets de train. Ces exemples sont tirés de 2 cas réels.

Pour le 1^er  site : vous vous y connecter renseignez votre destination et les dates souhaitées, Il se trouve que vous réservez à la dernière minute et pour une période de pointe, il est assez difficile de trouver le billet de vos rêves. Enfin, vous le trouvez, mais au moment de cliquez sur le bouton de réservation, un message d’erreur vous signale que la place n’est plus disponible. C’est le fondement de l’isolation pessimiste, on laisse toute liberté pour lire les données, mais lors de l’écriture le premier arrivé gagne. Ce mode est généralement bon si le nombre d’écriture est faible, mais se révèle assez vite inopérant lors de périodes comme celle de notre exemple.

Pour le second site : même sélection, mais période plus calme, vous trouve un billet pour votre destination, mais il en reste un seul à un tarif attractif. Heureux de cette trouvaille, vous vous apprêtez à cliquez sur le bouton de réservation. Pas de chance, votre navigateur vous joue un mauvais tour et s’arrête de manière inopinée. Vous rouvrez le navigateur, retournez sur le site en question et refaite la sélection en espérant retrouver ce même billet. Impossible de remettre la main sur le même tarif. Vous réessayez 10 minutes plus tard et le fameux billet est réapparu et le réservez cette fois ci sans problèmes. Cette fois ci, vous avez testé le mode pessimiste, le site à maintenu un verrou sur la place entre la panne de votre navigateur et l’expiration d’un délai interne au site de réservation.

Mais quel mode choisir ?

Le mode optimiste fait apparaitre des places fugitives, rendant le système quasiment inusable en période de pointe. Cependant cette formule est relativement bonne pour site Internet où il est très difficile de déterminer si l’utilisateur se trouve encore sur le site de réservation ou est passé à autre chose.

Le mode pessimiste est bloquant, fait que la vision des disponibilités est faussée, mais n’engendre pas de risque d’apparition de places fugitives. Cette manière de procéder est tout de même indispensable pour des agents de voyages ou personnel de  compagnie de transport, où il serait impensable de laisser un employé face à la même situation que notre premier internaute. C’est d’autant plus facile dans ces types d’applications que l’on peut interagir rapidement avec la personne connecté à ce type d’application métier.

C’est à vous de faire de choisir au sein de l’architecture de votre application quel mode privilégier. Pour vous y aider nous détaillerons les niveaux d’isolation disponibles dans SQL Server.

Voyons tout d’abord les 2 méthodes d’isolations existantes derrières les termes : optimiste ou versionnement, pessimiste ou verrous.

Les verrous

Les verrous sont des éléments résidant en mémoire. D’une taille approximative de 100 octets, ils signalent qu’actuellement une ressource est en cours d’accès par le moteur de base de données.

Les verrous ont un rôle essentiellement applicatif est sont liée obligatoirement à une connexion et à une transaction. Ils sont supprimés à la fin d’une transaction (annulation ou validation).

Le nombre de verrou est limité par différent facteurs. En 32 bits, le nombre de verrou ne peut aller au-delà de 2 147 483 647, de plus la zone mémoire dans laquelle résident les verrous est limité dans la mémoire basse du serveur (Les 2 ou 3 premiers giga-octets de mémoire suivant la configuration système) et ne doit pas représenter plus de 40% de la mémoire disponible. En 64 bits seule le paramètre mémoire rentre en ligne de compte pour la limitation du nombre de verrous du serveur.

Dans tous les cas il est possible de spécifier un nombre maximal de verrous disponible dans une instance de SQL Server en modifiant le paramètre « locks » de l’instance avec la procédure stockée système sp_configure, je ne vous recommande pas la modification de cette option.

Différents types de verrous sont à la disposition du moteur. Ces verrous sont généralement représentés par une lettre, voici les principaux avec une brève description de leurs rôles :

S : Partagé (Shared)

        Verrou habituellement posé lorsqu’une lecture va se produire

X : Exclusif (Exclusive)

Généralement posé lorsqu’une écriture se produit

U : Mise à jour (Update)

Posé avant une mise à jour avec la commande UPDATE

I : Intention (Intent)

Ce verrou est toujours combiné à l’un des précédents. Son but est d’indiquer qu’actuellement est en train d’effectuer une opération du type de verrou indiqué à un niveau inférieur

Sch : Schéma

Utilisé généralement lors d’une mise à jour du schéma ou de l’utilisation de SCHEMABINDING pour une vue

Plusieurs verrous de types différents peuvent être associés aux mêmes ressources (enregistrement, table, etc.), c’est ce qu’on appelle la compatibilité des verrous. Chaque type peut être ou non posé sur une ressource si un verrou d’un autre type y est déjà, SQL Server mettra la demande du second verrou, si celui est incompatible, en attendant de la libération de la ressource, donc de la suppression du verrou existant.

Les verrous exclusifs (X) sont incompatibles avec tous les autres. En conséquence de quoi, quand une écriture a lieue, aucune autre transaction ne peut accéder aux données modifiées car protégées par un verrou de type exclusif. Que ce soit une lecture (qui demandera un verrou partagé (S)) où une écriture (qui demandera aussi un verrou exclusif), elles seront toutes deux mises en attente de la suppression du verrou exclusif.

De même, lors de la lecture de données qui nécessite d’obtenir un verrou partagé, toutes écriture se trouveraient mises en attente, car nécessitant l’obtention d’un verrou de type exclusif qui est incompatible avec le verrou partagé déjà en place ? Par contre les verrous partagés sont compatibles entre eux, ayant comme conséquence permettre plusieurs lectures simultanées des mêmes données. Pour une synthèse complète des compatibilités entre verrous, voyez le tableau 23-1.

23-1

Compatibilité des verrous

Verrou present	Verrou demandé
	S – Partagé	U – Mise à jour	X – Exclusif
S – Partagé	compatible	Compatible	incompatible
U – Mise à jour	compatible	incompatible	incompatible
X - Exclusif	incompatible	incompatible	incompatible

Le verrou de mise à jour (U) est utilisé essentiellement dans des cas de mise à jour de données via la commande UPDATE, il permet d’indiquer au moteur une lecture des données préparatoire à la mise à jour. Son principal intérêt est de ne pas être bloquant pour les autres opérations pendant la période de préparation de la mise à jour, mais d’empêcher le déclenchement de toute autre mise à jour en les mettant en attente.

Les différents types de verrous disponibles dans SQL Server sont associés à des ressources. Celle qui peuvent être verrouillées, cette liste est volontairement non exhaustive, sont :

Base de données

Table ou Objet

Partition

Extension

Page

Enregistrement / Clef

SQL Server décide du type de ressource à verrouiller en fonctions de différents critères tels que l’impact d’une requête sur les données, les ressources mémoire actuellement disponibles, etc. Le choix de la granularité de verrouillage est dépendant du contexte d’exécution et peut alors se trouver être différent entre 2 exécutions de la même requête.

Pour connaitre le type verrous et la granularité du verrouillage utilisés par SQL Server, le moteur fournis une vue dynamique à cet effet : sys.dm_db_tran_locks

Pour bien visualiser l’impact des verrous, regardons une requête plus en détails :

La transaction n’est pas finalisée pour laisser les verrous en place sur les données. Dans une seconde connexion exécutez :

Vous obtiendrez :

On aperçoit ici que le moteur a placé 270 verrous exclusifs au niveau des enregistrements, clefs et enregistrements étant équivalent dans cet exemple. Cela indique qu’une modification de données est actuellement en cours, et interdit la pose de verrous partagés ou exclusifs sur ces données tant que la transaction n’est pas finalisée.

Pour compléter 8 verrous partagés d’intention (IX) ont fait leur apparition. Ces verrous d’intention qui toujours associé à un autre type de verrou, ici exclusif, permettent d’indiquer à des niveaux de verrouillage supérieur (table et page) que des verrous exclusifs sont en activité. La transaction souhaitant par exemple verrouiller la totalité de la table pour réaliser une lecture devra poser un verrou partagé sur celle-ci. Le verrou d’intention exclusif déjà présent au niveau table interdira à ce processus un verrouillage complet de la table et la nouvelle transaction se mettra en attente. Pour s’en persuader, exécutez la requête suivante dans une nouvelle connexion :

Cette requête se met en attente sans rien renvoyer, elle est bloquée par la mise à jour lancée précédemment dont la transaction n’est pas finie.

Dans la première connexion, exécutez pour annuler les modifications que l’on vient de faire :

Dernier verrou présent, de type partagé au niveau de la base de données, n’est là que pour signaler qu’une connexion est actuellement active sur la base de données et empêcher ainsi la suppression de la base de données.

La promotion de verrous

SQL Server décide du niveau optimal de verrouillage des données pour éviter de consommer trop de ressources à la fois processeur et mémoire et d’autre part pour éviter d’être trop bloquant vis-à-vis des autres transactions.

Le niveau initial de verrouillage est déterminé par l’impact de la requête sur les données. En exécutant un SELECT sans clause WHERE il est très probable que SQL Server décide de verrouiller la totalité de la table, mais est aussi dépendant de paramètres autres qui amèneront peut être à une autre décision. Si le niveau initial demandé ne peut être obtenu, le moteur commencera par un verrouillage d’enregistrements.

Un autre mécanisme entre alors en jeu, il s’agit de La promotion de verrous. Ce système permet de limiter la consommation de ressource en limitant le nombre de verrous posés. Pour ce faire, SQL Server valide tous les 1 250 verrous obtenus sur une table s’il n’en aurait pas accumulé plus de 5 000 sur cette ressource. Si c’est le cas il tente une escalade de verrous au niveau supérieur : s’il verrouillait les enregistrements, il tente une escalade vers le niveau page, s’il verrouillait des pages il tente une escalade vers la table ou la partition.

Il est possible d’observer le phénomène dans l’outil Générateur de Profils et via certaines vues dynamique dans SQL Server.

Dans le Générateur de Profils, l’évènement « Lock :Escalation » est à suivre pour détecter une promotion de verrous. En complément, les évènements « Lock : Acquired » et « Lock : Released » qui accompagnent la pose et la suppression d’un verrou peuvent s révéler intéressantes, même si la quantité d’évènements listés se révèlera très volumineuse.

Parmi les fonctions dynamiques systèmes que SQL Server met à notre disposition, sys.dm_db_index_operational_stats est celle contenant le plus de détails intéressant sur les verrous et particulièrement la promotion de verrous via 2 de ses champs : index_lock_promotion_attempt_count et index_lock_promotion_count.

En pratique, le lot suivant va provoque une promotion de verrous sur la table « Person.Address ».

Si vous avez exécuté le Générateur de Profils de SQL Server, vous aurez enregistré l’évènement « Lock : Escalation » une fois.

Au niveau des verrous posés, la vue sys.dm_tran_locks signale que la table est verrouillée dans sa totalité avec un verrou partagé. On observe aussi dans le champ index_lock_promotion_count qu’une promotion de verrou s’est bien produite.

Ce comportement est normal, mais dans certaines situations, il est souhaitable de pouvoir contrôler le comportement de ce système pour limiter les blocages potentiels.

La promotion de verrous peut se désactiver au niveau de l’instance, via un Traceflag dans les précédentes versions, cette possibilité interdit au moteur de procéder à cette promotion de verrous sur la totalité des tables présentes sur le serveur de base de données. Seule subsiste la possibilité de procéder à cette promotion de verrous en cas de manque de mémoire. Les Traceflags 1211 et 1224 permettent cela. Le premier désactive la promotion de verrou de manière inconditionnelle, tandis que le second l'autorise en cas de manque de mémoire. Pour les paramétrer se référer à l’aide en ligne de SQL Server.

SQL Server 2008 offre maintenant la possibilité de contrôler le système de promotion de verrous au niveau de chaque table, via la commande :

Cette commande peut prendre 3 valeurs :

Disabled : La promotion de verrous est interdite sur cette table, sauf obligation de l’utiliser

Auto : La promotion de verrous est autorisée et se fait jusqu’au niveau partition si la table est partitionné sinon jusqu’au niveau table dans les autres cas

Table : Valeur par défaut et comportement identique aux versions précédentes. La promotion de verrous est autorisée et se fait jusqu’au niveau table

N’utilisez ces paramétrages que si vous avez la certitude que la promotion de verrous vous occasionne des blocages important sur votre serveur. La fonction vue plus haut sys.dm_db_index_operational_stats pourra vous y aider.

Les latchs

La langue française nous réserve parfois des surprises, c’est le cas avec la traduction des termes locks et latchs, tous 2 traduits par verrous en Français. Leur rôle est très différent au sein du moteur de base de données.

Un verrou (lock) a un rôle applicatif, il est associé à une connexion et sert généralement à l’isolation des données d’une transaction. Sa durée de vie est de l’ordre de la centaine de milliseconde. Il est possible de visualiser la liste des verrous actuellement posé ou en attente.

Le latch a un rôle beaucoup plus orienté système. Il sert à la synchronisation de certaines opérations internes à SQL Server, comme par exemple la lecture de données depuis le système disque vers le cache de données ou la mise à jour des métadonnées d’une page. Sa durée de vie est de l’ordre de quelques millisecondes.