Les conseils de table SQL Server sont un type spécial de commande explicite qui est utilisé pour remplacer le comportement par défaut de L’optimiseur de requête SQL Server pendant L’exécution de la requête T-SQL ceci est accompli en utilisé par l’optimiseur de requête SQL Server pour créer le plan d’exécution de la requête., Les indices de table peuvent être ajoutés à la clause FROM de la requête T-SQL, affectant uniquement la table ou la vue référencée dans la clause FROM.
L’un des indices de table les plus utilisés dans les instructions SELECT T-SQL est L’indice WITH (NOLOCK). Le niveau d’isolation de transaction par défaut dans SQL Server est le niveau D’isolation READ COMMITTED, dans lequel la récupération des données changeantes sera bloquée jusqu’à ce que ces modifications soient validées., L’indice de table WITH (NOLOCK) est utilisé pour remplacer le niveau d’isolation de transaction par défaut de la table ou des tables de la vue dans une requête spécifique, en permettant à l’utilisateur de récupérer les données sans être affecté par les verrous, sur les données demandées, en raison d’un autre processus qui les modifie. De cette façon, la requête consommera moins de mémoire en maintenant des verrous contre ces données. De plus, aucun blocage ne se produira contre les requêtes qui demandent les mêmes données à partir de cette table, ce qui permet un niveau de concurrence plus élevé en raison d’une empreinte plus faible., En d’autres termes, L’indice de table WITH (NOLOCK) récupère les lignes sans attendre que les autres requêtes, qui lisent ou modifient les mêmes données, terminent leur traitement. Ceci est similaire au niveau D’isolation de transaction non validée en lecture, qui permet à la requête de voir les modifications de données avant de valider la transaction qui la modifie. Le niveau d’isolation des transactions peut être défini globalement au niveau de la connexion à l’aide de la commande SET TRANSACTION ISOLATION LEVEL T-SQL, comme nous le verrons plus loin dans cet article.,
bien que L’indice de table NOLOCK, similaire à tous les autres indices de table, puisse être utilisé sans utiliser le mot-clé WITH, Microsoft a annoncé que l’omission du mot-clé WITH est une fonctionnalité obsolète et sera supprimée des futures versions de Microsoft SQL Server. Cela dit, il est préférable d’inclure le mot clé WITH lors de la spécification des conseils de table. L’un des avantages de L’utilisation du mot-clé WITH est que vous pouvez spécifier plusieurs conseils de table en utilisant le mot-clé WITH contre la même table.
en général, l’utilisation fréquente de conseils de table explicites est considérée comme une mauvaise pratique que vous devriez généralement éviter., Pour l’indice de table NOLOCK en particulier, la lecture de données non validées qui pourraient être annulées après l’avoir lue peut entraîner une lecture sale, qui peut se produire lors de la lecture des données modifiées ou supprimées pendant la lecture des données non validées, de sorte que les données que vous lisez pourraient être différentes, ou même n’avoir jamais existé.
L’indice de table WITH (NOLOCK) conduit également à des lectures non répétables; cette lecture se produit lorsqu’il est nécessaire de lire les mêmes données plusieurs fois et que les données changent pendant ces lectures. Dans ce cas, vous lirez plusieurs versions de la même ligne.,
Les lectures fantômes peuvent également être le résultat de l’utilisation de L’indice de table WITH(NOLOCK), dans lequel vous obtiendrez plus d’enregistrements lorsque la transaction qui insère de nouveaux enregistrements est annulée, ou moins d’enregistrements lorsque la transaction qui supprime des données existantes est annulée. Un autre problème peut survenir lorsque d’autres transactions déplacent des données que vous n’avez pas encore lues vers un emplacement que vous avez déjà analysé ou ont ajouté de nouvelles pages à l’emplacement que vous avez déjà analysé. Dans ce cas, vous manquerez ces enregistrements et ne les verrez pas dans le résultat renvoyé., Si une autre transaction déplace les données que vous avez déjà numérisées vers un nouvel emplacement que vous n’avez pas encore lu, vous les lirez deux fois. De plus, comme les données demandées peuvent être déplacées ou supprimées pendant votre processus de lecture, l’erreur ci-dessous peut être rencontrée:
Msg 601, Niveau 12, État 1
impossible de continuer l’analyse avec NOLOCK en raison du mouvement des données.
L’indice de table WITH (NOLOCK) est une bonne idée lorsque le système utilise fortement des transactions explicites, ce qui bloque la lecture des données très fréquemment., L’indice de table WITH (NOLOCK) est utilisé lorsque vous travaillez avec des systèmes qui acceptent des données désynchronisées, telles que les systèmes de rapports.,
pour comprendre l’utilisation de L’indice de table WITH (NOLOCK) pratiquement, créons une nouvelle table en utilisant L’instruction CREATE TABLE T-SQL ci-dessous:
Après avoir créé la table, nous la remplirons avec 100k lignes à des fins de test, en utilisant ApexSQL Generate, générateur de données de test SQL, comme indiqué dans l’instantané, nous simulerons un scénario de blocage, dans lequel une transaction de mise à jour sera exécutée dans une transaction qui commencera et non validée ou annulée.,
avec les données de la table verrouillées par la transaction, nous allons exécuter une autre instruction SELECT, sous le numéro de session SQL 54, qui récupère les données de la table LockTestDemo, en utilisant L’instruction SELECT ci-dessous:
|
1
|
sélectionnez * dans locktestdemo
|
vous verrez que l’instruction SELECT précédente prendra beaucoup de temps sans récupérer aucun enregistrement.,ocking cette requête SELECT en utilisant la commande sp_who2 avec le numéro de session pour les instructions SELECT et UPDATE:
|
1
2
3
|
sp_who2 53
go
sp_who2 54
|
le résultat vous montrera que la transaction précédemment ouverte n’effectue aucune action, car l’instruction UPDATE s’est exécutée avec succès., Mais en raison du fait que la transaction n’est pas encore validée ou annulée, elle bloque toujours les autres requêtes qui tentent d’obtenir des données de cette table. Et L’instruction SELECT qui s’exécute sous la session 54 est bloquée par cette transaction qui s’exécute sous la session 53, Comme indiqué dans le résultat ci-dessous:
l’instruction SELECT précédente attend que la transaction soit supprimée, validée ou annulée afin d’obtenir les lignes demandées de cette table., la commande COMMIT ou ROLLBACK sous la même session de la transaction, le cas échéant, comme indiqué ci-dessous:
Une fois le verrouillage relâché, vous verrez que les lignes demandées seront récupérées directement à partir de L’instruction SELECT comme indiqué dans les résultats ci-dessous:
‘est pas toujours préférable ou applicable, par exemple, lorsque la transaction qui bloque nos requêtes est critique et qu’il n’est pas facile d’être supprimée ou annulée, ou lorsque vous n’avez pas le contrôle sur les transactions des autres dans la base de données., Dans ce cas, L’indice de table WITH (NOLOCK) est utile ici, si vous pouvez tolérer le risque de lectures sales ou d’incohérence des données. Comme mentionné précédemment, L’indice de table WITH (NOLOCK) vous permet de lire les données qui ont été modifiées, mais qui ne sont pas encore validées dans la base de données., Si vous exécutez la même instruction SELECT sans tuer, valider ou annuler la transaction UPDATE, mais cette fois en ajoutant L’indice WITH (NOLOCK) table au nom de la table dans L’instruction SELECT comme indiqué ci-dessous:
|
sélectionnez * dans locktestdemo avec (NOLOCK)
|
puis Vérification de l’état de l’instruction SELECT à l’aide de la commande SP_WHO2., Vous verrez que la requête s’exécute sans attendre que la transaction de mise à jour soit terminée avec succès et relâchez le verrouillage sur la table, comme indiqué dans l’instantané ci-dessous:
L’indice de table WITH (NOLOCK) fonctionne de la même manière que L’indice de table READUNCOMMITTED, ,iv>
|
1
|
SELECT * from LockTestDemo AVEC (READUNCOMMITTED)
|
Récupérer les données demandées directement, sans attendre la mise à JOUR de la déclaration pour la libérer, il a effectué sur la table, ce qui renvoie le même résultat, comme le montre le résultat suivant:
Prendre en considération le fait que, le (NOLOCK) et READUNCOMMITTED les indicateurs de table peut être utilisé uniquement avec les instructions SELECT.,
cette requête récupérera également les mêmes données directement, sans utiliser d’indice de table et sans attendre que L’instruction UPDATE libère le verrou qu’elle a effectué sur la table, comme indiqué dans le jeu de résultats ci-dessous:
d’après les résultats précédents, vous pouvez penser que c’est la solution parfaite pour de tels scénarios, où vous obtiendrez les données demandées plus rapidement, sans attendre que d’autres opérations soient engagées, en prenant le risque d’avoir des données non précises., Mais la requête SELECT qui utilise L’indice de table WITH (NOLOCK) affectera-t-elle négativement les autres processus sur le serveur SQL? Pour obtenir la réponse, vérifions d’abord quel type de verrous L’indice de table WITH (NOLOCK) sera accordé lors de son exécution.,Si vous avez besoin d’une carte de crédit, vous pouvez utiliser la carte de crédit ci-dessous pour obtenir une carte de crédit, puis une carte de crédit, puis une carte de crédit, puis une carte de crédit, puis une carte de crédit, puis une carte de crédit, puis une carte de crédit.div id = »20d90f2e3d »>
vous verrez à partir du résultat que la requête qui utilise l’indice de table with (NOLOCK) se verra accorder les types de verrouillage s et SCH-s, comme indiqué dans le résultat ci-dessous:
à partir du résultat précédent, vous verrez que l’indice de table with (NOLOCK) se verra accorder un verrou d’accès partagé au niveau de la base de données., Le verrou d’accès partagé (s) est utilisé pour l’opération de lecture, permettant aux transactions simultanées de lire les données sous le contrôle de concurrence pessimiste, empêchant d’autres transactions de modifier la ressource verrouillée alors que des verrous partagés existent sur cette ressource, jusqu’à ce que ce verrouillage soit libéré dès que l’opération de lecture est terminée.
le deuxième type de verrouillage accordé à la requête à l’aide de L’indice de table WITH (NOLOCK) est le verrou de stabilité de schéma (SCH-s)., Ce verrou n’empêchera aucune autre transaction d’accéder aux ressources, à l’exception des opérations DDL simultanées et des opérations DML simultanées qui acquièrent des verrous de modification de schéma (SCH-M) sur la même table, qui seront bloqués pendant l’exécution de la requête. Cela a vraiment du sens, car vous n’avez pas besoin de commencer à lire les données de la table, puis une autre transaction modifie la structure de cette table pendant votre processus de récupération de données., Le moteur de base de données SQL Server utilise les verrous de modification de schéma (SCH-M) lors du traitement des commandes DDL (data definition language), telles que l’ajout d’une nouvelle colonne, la suppression d’une colonne existante, la suppression ou la reconstruction d’index, pour empêcher l’accès simultané à la table, jusqu’à ce que le verrou soit libéré.
ma requête NOLOCK bloque!
cela signifie que la dénomination NOLOCK n’est pas toujours précise à 100%. L’utilisation de L’indice de table WITH (NOLOCK), qui contient le verrou sch_s (schema stability), peut bloquer d’autres requêtes qui tentent d’acquérir un verrou de modification de schéma (SCH-m) sur cette table., C’est un problème critique que vous devez prendre en considération s’il y a beaucoup d’utilisateurs qui exécutent leurs requêtes SELECT en utilisant L’indice de table WITH (NOLOCK), vous empêchant d’apporter des modifications au schéma de table ou aux maintenances sur les index de table, étant bloqué par le verrou de stabilité de schéma (Sch_S).,>
en même temps, nous allons exécuter la requête ci-dessous, c’est-à-dire déposer un index sur la même table et le créer à nouveau, sous le numéro de session 58:
en vérifiant ensuite l’état des deux requêtes à l’aide de la commande sp_who2, vous verrez à partir du résultat que,/créer un processus d’index s’exécutant sous le numéro de session 58, Comme indiqué clairement ci-dessous:
Si nous vérifions les verrous effectués par chaque requête, en utilisant le système.,objet système dm_tran_locks comme dans la requête ci-dessous:
|
1
2
3
|
select *
FROM sys.dm_tran_locks
where resource_type = ‘object’
|
vous verrez que le processus drop/CREATE INDEX exécuté sous le numéro de session 58 attend d’acquérir le type de verrouillage de modification de schéma (SCH-m)., Cela est dû au fait que le verrou de modification de schéma (SCH-M) ne peut pas être acquis alors que le verrou de stabilité de schéma (Sch_S) déjà accordé à L’instruction SELECT exécutée sous le numéro de session 53 existe déjà comme indiqué dans l’instantané ci-dessous:
ma requête NOLOCK est bloquée!
inversement, puisque L’indice de table WITH (NOLOCK) acquiert le type de verrouillage schema stability (Sch-s), L’instruction SELECT qui utilise L’indice de table WITH (NOLOCK) sera bloquée si une modification de schéma est effectuée sur cette table.,
|
1
2
3
|
sp_who2 53
ALLER
sp_who2 54
|
Vous allez voir que l’instruction SELECT cours d’exécution en vertu de la session 54 est bloqué par l’instruction ALTER TABLE exécutant le cadre de la session 54, comme indiqué ci-dessous:
Puis de vérifier les verrous qui sont exécutées par chaque requête, en utilisant le sys.,dm_tran_locks system object as in the query below:
|
1
2
3
4
|
SELECT *
FROM sys.,cela, L’instruction SELECT qui utilise L’indice de table WITH (NOLOCK) et s’exécute sous le numéro de session 54, attendra d’acquérir le verrou de stabilité de schéma (Sch_S), car le verrou de stabilité de schéma (SCH-S) ne peut pas être acquis alors que le verrou de modification de schéma (Sch_M), qui est déjà accordé à L’instruction ALTER s’exécutant sous le numéro de session 53, existe déjà comme indiqué dans l’instantané ci-dessous:
Vous pouvez imaginer la situation lorsque vous programmez un grand nombre de rapports la nuit, qui utilisent l’indice de table with (NOLOCK) juste pour être sûr., Dans le même temps, il existe des tâches de maintenance qui sont également programmées pour reconstruire des index fortement fragmentés sur la même table! Il y a un certain nombre de meilleures pratiques et suggestions que vous pouvez suivre, afin d’éviter les problèmes que vous pouvez rencontrer lors de L’utilisation avec (NOLOCK) table hint. Ces suggestions incluent:
Ahmad Yaseen est un ingénieur Big Data Microsoft avec une connaissance approfondie et une expérience dans SQL bi, l’administration de base de données SQL Server et les domaines de développement.,
Il est expert en solutions Microsoft Certified en gestion et analyse de données, Microsoft Certified Solution Associate en administration et développement de bases de données SQL, Azure Developer Associate et Microsoft Certified Trainer. En outre, il contribue avec ses conseils SQL dans de nombreux blogs., Afficher tous les articles par Ahmad Yaseen Derniers messages par Ahmad Yaseen (voir tous)
|
Laisser un commentaire