Article
03/31/2012

Verrouillage d'étendues de clés

Les verrous d'étendues de clés protègent une série de lignes implicitement incluses dans un jeu d'enregistrements lu par une instruction Transact-SQL lors de l'utilisation d'un niveau d'isolement des transactions sérialisable. Le niveau d'isolement sérialisable exige que toute requête exécutée pendant une transaction obtienne le même jeu de lignes à chaque exécution lors de la transaction. Un verrou d'étendues de clés protège cette exigence en empêchant d'autres transactions d'insérer de nouvelles lignes dont les clés sont comprises dans la plage des clés lues par la transaction sérialisable.

Le verrouillage d'étendues de clés empêche les lectures fantômes. La protection des étendues de clés entre les lignes permet également d'empêcher les insertions fantômes dans un jeu d'enregistrements auquel une transaction accède.

Un verrou d'étendues de clés est placé sur un index, spécifiant une valeur de clé de début et de fin. Ce verrou bloque toute tentative d'insertion, de mise à jour ou de suppression de ligne possédant une valeur de clé comprise dans cette étendue, car ces opérations doivent d'abord acquérir un verrou sur l'index. Par exemple, une transaction sérialisable peut émettre une instruction SELECT qui lit toutes les lignes dont les valeurs de clés sont comprises entre 'AAA' et 'CZZ'. Un verrou d'étendues de clés sur les valeurs de clés comprises entre 'AAA' et 'CZZ' empêche les autres transactions d'insérer des lignes possédant des valeurs de clés comprises dans cette étendue, telles que 'ADG', 'BBD' ou 'CAL'.

Modes de verrouillage d'étendues de clés

Les verrous d'étendues de clés comprennent un composant étendue et un composant ligne, au format étendue-ligne :

L'étendue représente le mode de verrouillage protégeant l'étendue entre deux entrées d'index successives.
La ligne représente le mode de verrouillage protégeant l'entrée de l'index.

Le mode représente la combinaison de modes de verrouillage utilisée. Les modes de verrouillage d'étendues de clés comportent deux parties. La première représente le type de verrouillage utilisé pour verrouiller l'étendue d'index (RangeT) et la deuxième représente le type de verrouillage utilisé pour verrouiller une clé spécifique (K). Les deux parties sont reliées par un tiret (-), par exemple RangeT-K.

Étendue	Ligne	Mode	Description
RangeS	S	RangeS-S	Verrou de ressource partagé, étendue partagée ; analyse d'étendue sérialisable.
RangeS	U	RangeS-U	Verrou de mise à jour de ressource, étendue partagée ; analyse d'étendue sérialisable.
RangeI	Null	RangeI-N	Verrou de ressource NULL, étendue d'insertion ; utilisé pour tester les étendues avant l'insertion d'une nouvelle clé dans un index.
RangeX	X	RangeX-X	Verrou de ressource exclusif, étendue exclusive ; utilisé lors de la mise à jour d'une clé dans une étendue.

Notes

Le mode interne de verrouillage Null est compatible avec tous les autres modes de verrouillage.

Les modes de verrouillage d'étendues de clés possèdent un tableau de compatibilité qui indique quels verrous sont compatibles avec les autres verrous obtenus sur les clés et étendues se chevauchant. Pour connaître tous les renseignements sur la compatibilité de verrouillage, consultez Compatibilité de verrouillage.

	Mode accordé existant
Mode requis	S	U	X	RangeS-S	RangeS-U	RangeI-N	RangeX-X
Partagé (S)	Oui	Oui	Non	Oui	Oui	Oui	Non
Mise à jour (U)	Oui	Non	Non	Oui	Non	Oui	Non
Exclusif (X)	Non	Non	Non	Non	Non	Oui	Non
RangeS-S	Oui	Oui	Non	Oui	Oui	Non	Non
RangeS-U	Oui	Non	Non	Oui	Non	Non	Non
RangeI-N	Oui	Oui	Oui	Non	Non	Oui	Non
RangeX-X	Non	Non	Non	Non	Non	Non	Non

Verrous de conversion

Les verrous de conversion sont créés lorsqu'un verrou d'étendue de clés chevauche un autre verrou.

Verrou 1	Verrou 2	Verrou de conversion
S	RangeI-N	RangeI-S
U	RangeI-N	RangeI-U
X	RangeI-N	RangeI-X
RangeI-N	RangeS-S	RangeX-S
RangeI-N	RangeS-U	RangeX-U

Les verrous de conversion peuvent être observés pendant une courte période dans différentes circonstances complexes, parfois lors de l'exécution de processus concurrents.

Analyse d'étendue sérialisable, extraction singleton, suppression et insertion

Le verrouillage d'étendues de clés permet la sérialisation des opérations suivantes :

Requête d'analyse d'étendue
Extraction singleton de ligne inexistante
Opération de suppression
Opération d'insertion

Les conditions suivantes doivent être satisfaites pour qu'un verrouillage d'étendues de clés puisse se produire :

Le niveau d'isolement de la transaction doit être défini sur SERIALIZABLE.
Le processeur de requêtes doit utiliser un index pour implémenter le prédicat de filtre de l'étendue. Par exemple, la clause WHERE dans une instruction SELECT peut établir une condition d'étendue avec le prédicat suivant : ColumnX BETWEEN N**'AAA'** AND N**'CZZ'**. Un verrou d'étendues de clés ne peut être acquis que si ColumnX est couvert par une clé d'index.

Exemples

La table et l'index suivants sont utilisés comme base pour les exemples de verrouillage d'étendues de clés ci-dessous.

Table de base de données avec illustration de l'arbre B (B-tree) de l'index

Requête d'analyse d'étendue

Pour qu'une requête d'analyse d'étendue soit sérialisable, cette requête doit retourner les mêmes résultats chaque fois qu'elle est exécutée dans la même transaction. De nouvelles lignes ne doivent pas être insérées dans la requête d'analyse d'étendue par d'autres transactions, sinon celles-ci deviennent des insertions fantômes. Par exemple, la requête suivante utilise la table et l'index de l'illustration précédente :

SELECT name
    FROM mytable
    WHERE name BETWEEN 'A' AND 'C';

Les verrous d'étendues de clés sont placés sur les entrées d'index correspondant à l'étendue de lignes de données dans laquelle name se trouve entre Adam et Dale, empêchant l'insertion ou la suppression de nouvelles lignes correspondant à la requête précédente. Bien que le premier nom de l'étendue soit Adam, le verrou d'étendues de clés du mode RangeS-S sur cette entrée d'index veille à ce qu'aucun nouveau nom commençant par la lettre A ne soit ajouté avant Adam, comme Abigail. De manière similaire, le verrou d'étendues de clés RangeS-S sur l'entrée d'index pour Dale fait en sorte qu'aucun nom commençant par C ne puisse être ajouté après Carlos, comme Clive.

Notes

Le nombre de verrous RangeS-S maintenus est n+1, où n est le nombre de lignes répondant aux critères de la requête.

Extraction d'un singleton de données non existantes

Si une requête à l'intérieur d'une transaction tente de sélectionner une ligne qui n'existe pas, l'exécution de la requête plus loin dans la même transaction doit retourner le même résultat. Aucune autre transaction ne peut être autorisée à insérer cette ligne inexistante. Supposons par exemple la requête suivante :

SELECT name
    FROM mytable
    WHERE name = 'Bill';

Un verrou d'étendues de clés est placé sur l'entrée d'index correspondant à l'étendue de noms se trouvant entre Ben et Bing, car le nom Bill serait inséré entre ces deux entrées d'index adjacentes. Le verrou d'étendues de clés du mode RangeS-S est placé sur l'entrée d'index Bing. Ceci empêche toute autre transaction d'insérer des valeurs, telles que Bill, entre les entrées d'index Ben et Bing.

Opération de suppression

Lors de la suppression d'une valeur dans une transaction, l'étendue dans laquelle la valeur se trouve ne doit pas nécessairement être verrouillée pendant toute la durée de la transaction effectuant l'opération de suppression. Le verrouillage de la valeur de clé supprimée jusqu'à la fin de la transaction est suffisant pour assurer la sérialisation. Par exemple, pour l'instruction DELETE suivante :

DELETE mytable
    WHERE name = 'Bob';

Un verrou exclusif (X) est placé sur l'entrée d'index correspondant au nom Bob. Les autres transactions peuvent insérer ou supprimer des valeurs avant ou après la valeur effacée Bob. Toutefois, toute transaction tentant de lire, insérer ou supprimer la valeur Bob sera bloquée jusqu'à ce que la transaction effectuant la suppression soit validée ou restaurée.

La suppression d'étendues peut être exécutée à l'aide de trois modes de verrouillage de base : verrouillage de ligne, de page ou de table. La stratégie de verrouillage de ligne, de page ou de table est décidée par l'optimiseur de requête, ou peut être spécifiée par l'utilisateur par l'intermédiaire d'options d'optimiseur telles que ROWLOCK, PAGLOCK ou TABLOCK. Lorsque l'option PAGLOCK ou TABLOCK est utilisée, le Moteur de base de données désalloue immédiatement une page d'index page si toutes les lignes qu'elle contient sont supprimées. En revanche, lorsque l'option ROWLOCK est utilisée, toutes les lignes supprimées sont uniquement marquées en tant que telles ; elles sont effectivement retirées de la page d'index ultérieurement, à l'aide d'une tâche d'arrière-plan.

Opération d'insertion

Lors de l'insertion d'une valeur à l'intérieur d'une transaction, l'étendue dans laquelle la valeur se trouve ne doit pas nécessairement être verrouillée pendant la durée de l'opération effectuant l'opération d'insertion. Le verrouillage de la valeur de clé jusqu'à la fin de la transaction suffit pour assurer la sérialisation. Par exemple, étant donné l'instruction INSERT suivante :

INSERT mytable VALUES ('Dan');

Le verrou d'étendues de clés du mode RangeI-N est placé sur l'entrée d'index correspondant au nom David pour le test de l'étendue. Si le verrou est accordé, la valeur Dan est insérée et un verrou exclusif (X) est placé sur la valeur Dan. Le verrou d'étendues de clés du mode RangeI-N est uniquement nécessaire pour le test de l'étendue et n'est pas maintenu pendant la durée de la transaction effectuant l'opération d'insertion. D'autres transactions peuvent insérer ou supprimer des valeurs avant ou après la valeur Dan insérée. Toutefois, toute transaction essayant de lire, écrire ou supprimer la valeur Dan est verrouillée jusqu'à ce que la transaction d'insertion soit validée ou restaurée.