Personnalisation du verrouillage pour un index

Dans la plupart des cas, le moteur de base de données SQL Server utilise une stratégie de verrouillage dynamique qui choisit automatiquement la granularité de verrouillage la plus appropriée pour les requêtes. Nous vous recommandons de ne pas remplacer les niveaux de verrouillage par défaut, pour lesquels le verrouillage de page et de ligne est activé, sauf si les modèles d'accès à la table ou à l'index sont bien assimilés et cohérents et s'il existe un conflit de ressources à résoudre. Le remplacement d'un niveau de verrouillage peut affecter considérablement l'accès concurrentiel à une table ou un index. Par exemple, la spécification de verrous de niveau table uniquement sur une table de grande taille à laquelle les utilisateurs accèdent fréquemment peut provoquer des goulots d'étranglement, car les utilisateurs doivent attendre que le verrou de niveau table soit libéré avant de pouvoir accéder à la table.

Il existe quelques cas où l'interdiction du verrouillage de page ou de ligne peut être avantageuse, à condition que les modèles d'accès soient bien assimilés et cohérents. Par exemple, une application de base de données utilise une table de recherche mise à jour chaque semaine via un processus par lot. Les lecteurs simultanés accèdent à la table avec un verrou partagé (S) et la mise à jour par lot hebdomadaire accède à la table avec un verrou exclusif (X). La désactivation du verrouillage de page et de ligne sur la table réduit la charge de traitement liée au verrouillage tout au long de la semaine en permettant aux lecteurs d'accéder simultanément à la table via des verrous de table partagés. Lorsque le programme de traitement par lot s'exécute, il peut effectuer la mise à jour efficacement, car il obtient un verrou de table exclusif.

La désactivation du verrouillage de page et de ligne peut parfois être acceptable, car la mise à jour par lot hebdomadaire empêche les lecteurs simultanés d'accéder à la table pendant l'exécution de la mise à jour. Si le programme de traitement par lot modifie seulement quelques lignes ou pages, vous pouvez changer le niveau de verrouillage afin d'autoriser le verrouillage de ligne ou de page ; cela permet à la table d'être lue sans blocage dans d'autres sessions. Si le programme de traitement par lot doit effectuer un grand nombre de mises à jour, l'obtention d'un verrou exclusif sur la table peut représenter la meilleure solution pour garantir l'efficacité et la totalité de son exécution.

Parfois, un blocage se produit dans les circonstances suivantes : deux opérations simultanées acquièrent des verrous de ligne sur la même table, puis se bloquent, car elles doivent toutes les deux verrouiller la page. L'interdiction des verrous de ligne force l'une des opérations à attendre, ce qui évite le blocage.

La granularité de verrouillage utilisée pour un index peut être définie via les instructions CREATE INDEX et ALTER INDEX. Les paramètres de verrouillage s'appliquent à la fois aux pages d'index et aux pages de table. De plus, les instructions CREATE TABLE et ALTER TABLE peuvent être utilisées pour définir la granularité de verrouillage sur les contraintes PRIMARY KEY et UNIQUE. À des fins de compatibilité descendante, la procédure stockée système sp_indexoption peut également définir la granularité. Pour afficher l'option de verrouillage en cours pour un index donné, utilisez la fonction INDEXPROPERTY. Les verrous au niveau des pages, des lignes, ou une combinaison de ces derniers peuvent être refusés pour un index donné.