Simon Learning SQL Server

Simon Learning SQL Server

Dans un article précédent, j’ai parlé des niveaux de verrouillage, de blocage et d’isolement. Dans cet article, je vais entrer plus en détail sur les verrous de plage de clés utilisés dans le niveau d’isolation SÉRIALISABLE et démontrer également l’utilisation du DMV sys.dm_tran_locks pour afficher les verrous existants et comment déterminer les ressources sur lesquelles les verrous sont placés.

Les verrous de plage de touches ne sont utilisés que dans le niveau d’isolation SÉRIALISABLE et leur but est d’arrêter les lectures fantômes qui sont possibles dans les trois autres niveaux d’isolation de simultanéité pessimistes, à savoir la lecture non validée, la lecture validée et la lecture répétable. Une lecture fantôme est l’endroit où une transaction effectue une lecture sur une plage de données, par exemple en utilisant une requête avec une clause WHERE, et une autre transaction ajoute des données qui satisferont les conditions de la clause WHERE. Si la première transaction effectue la même lecture, elle renvoie maintenant les nouvelles données, ce qui entraîne l’apparition de données fantômes dans la deuxième lecture au sein de la même transaction. Les verrous de plage de clés sont utilisés pour empêcher l’ajout de données fantômes et ils sont placés sur les clés de l’index utilisé pour récupérer les données. L’exemple suivant utilisant la personne.La table des personnes de la base de données AdventureWorks2012 montrera comment ces verrous à plage de clés sont utilisés.

La Personne.La table Person a un index non cluster IX_Person_LastName_FirstName_MiddleName avec la colonne LastName comme première colonne de la clé d’index. Cela signifie que toute requête où nous filtrons sur LastName doit utiliser cet index pour récupérer les données.

Si nous exécutons une requête pour renvoyer quelques lignes de Person.Filtrage de personne sur une plage de noms de famille, nous devrions alors voir cet index utilisé. Par exemple,

SELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'ORDER BY LastName

Renvoie le jeu de résultats suivant.

 Gamme de touches 2

Et en regardant le plan d’exécution, nous pouvons voir que cet index est utilisé.

 Gamme de touches 3

Maintenant, si nous définissons le niveau d’isolation sur SÉRIALISABLE, ouvrons une transaction et exécutons la même instruction select, nous devrions pouvoir voir quels verrous sont détenus.

SET TRANSACTION ISOLATION LEVEL SERIALIZABLEBEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Maintenant, à partir d’une autre connexion, nous pouvons interroger le DMV sys.dm_tran_locks pour voir les verrous actuels maintenus. Nous ne nous intéressons qu’aux verrous sur les ressources CLÉS, nous filtrons donc en conséquence.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

 Gamme de Touches 4

Nous obtenons sept lignes réaccordées avec deux valeurs request_mode différentes. Le mode de requête « S » correspond aux verrous partagés placés sur les trois lignes renvoyées. Parce que nous utilisons le niveau d’isolation SÉRIALISABLE, ces verrous partagés sont maintenus jusqu’à ce que la transaction soit validée ou annulée pour empêcher les lectures non répétables. Le mode de requête « RangeS-S » correspond aux verrous de plage de clés qui ont été accordés. Mais pourquoi y a-t-il quatre rangées? Pour répondre à cela, nous pouvons utiliser les données renvoyées par sys.dm_tran_locks et l’utiliser pour déterminer quelles ressources ont réellement été verrouillées.

Les colonnes qui nous intéressent sont resource_associated_entity_id et resource_description. Les données de ces colonnes peuvent varier en fonction du type de ressources et pour plus de détails à ce sujet, voir TechNet. Pour les ressources CLÉS, le resource_associated_entity_id correspond au partition_id de sys.cloisons

SELECT *FROM sys.partitionsWHERE partition_id in (72057594057523200, 72057594045595648)

 Gamme de Touches 5

L’object_id retourné correspond à la personne.Table de personne

SELECT object_name(1765581328)

 Gamme de Touches 6

Nous pouvons voir que les deux valeurs partition_id différentes correspondent à des index différents sur Person.Personne

SELECT * FROM sys.indexes WHERE object_id = 1765581328

 Gamme de Touches 7

Le système.la vue partitions nous indique que les partitions sur lesquelles nous avons des verrous sont pour les index PK_Person_BusinessEntityID et IX_Person_LastName_FirstName_MiddleName.

Nous pouvons combiner tout cela en une seule requête pour plus de clarté.

 SELECT L.resource_type ,L.resource_description ,L.resource_associated_entity_id ,L.request_mode ,L.request_type ,L.request_status ,OBJECT_NAME(I.object_id) AS TableName ,I.object_id ,I.index_id ,I.name AS IndexName ,I.type_desc FROM sys.dm_tran_locks L INNER JOIN sys.partitions P ON P.partition_id = L.resource_associated_entity_id INNER JOIN sys.indexes I ON I.object_id = P.object_id AND I.index_id = P.index_id WHERE L.resource_type = 'KEY' ORDER BY L.request_mode

 Gamme de Touches 8

Cela nous montre que trois verrous partagés sont placés sur les lignes elles-mêmes (le niveau de feuille de l’index en cluster) et que quatre verrous de plage de clés sont placés sur les clés de l’index non en cluster. Mais cela n’a pas répondu à la question de savoir pourquoi nous avons quatre serrures?

Avant de le faire, je vais démontrer deux façons de prouver que les trois verrous partagés sont placés sur les lignes renvoyées par la requête d’origine, puis l’utiliser pour déterminer les ressources sur lesquelles les quatre verrous « RangeS-S » sont placés.

Tout d’abord, nous allons utiliser les valeurs %%physloc%% et %%lockres%% avec le système.Fonction fn_PhysLocFormatter() pour nous indiquer le fichier, la page et l’emplacement des lignes renvoyées par la requête d’origine. Deuxièmement, nous utilisons ces informations dans la commande de PAGE DBCC.

Il nous suffit de mettre à jour notre requête d’origine comme suit

SELECT sys.fn_PhysLocFormatter(%%physloc%%) AS FilePageSlot, %%lockres%% AS LockResource, *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

 Gamme de Touches 9

Nous pouvons maintenant voir que la ligne de Dennis Ware se trouve physiquement sur le fichier de données 1, à la page 2806 et à l’emplacement 3. Nous pouvons également voir que sa ressource de verrouillage est (05c585e839d4).

Si nous revenons aux données renvoyées par sys.dm_tran_locks, nous pouvons voir que l’un des verrous partagés a une resource_description de (05c585e839d4), ce qui nous indique que ce verrou est placé sur cette ligne dans l’index en cluster.

Maintenant, nous pouvons exécuter la commande de PAGE DBCC suivante pour voir réellement des informations sur ce qui se trouve sur la page, mais pour que cela renvoie des résultats, nous devons activer l’indicateur de trace 3604.

DBCC TRACEON (3604);DBCC PAGE (10, 1, 2806, 3); -- DB_ID, FileId, PageId, Format

De nombreuses informations textuelles sont affichées ici et vous pouvez spécifier un nombre différent dans le paramètre format pour renvoyer différentes quantités de données. Dans ce cas, le premier bit est l’en-tête de la page, puis après cela, nous avons le statut d’allocation. Vous verrez que le premier emplacement utilisé est l’emplacement 0. Il y a une valeur de décalage qui nous indique où commence cet emplacement sur la page et une valeur de longueur qui nous indique la durée de l’enregistrement.

 Gamme de touches 10

Si nous faisons défiler vers le bas jusqu’à ce que nous trouvions l’emplacement 3, puis que nous faisons défiler le bloc de données, nous pouvons voir les données physiques de chaque colonne et confirmer qu’il s’agit de la ligne de Dennis Ware.

 Gamme de touches 11

Maintenant, si nous remontons à la fin de l’emplacement 3, nous pouvons voir

KeyHashValue = (05c585e839d4)

Cela correspond à la description des ressources dans sys.dm_tran_locks et à la valeur renvoyée par %%lockres%%.

Mais comment faire quelque chose de similaire pour les serrures à plage de clés? Comment pouvons-nous savoir quelles ressources ils verrouillent? Une façon serait d’utiliser sys.dm_db_database_page_allocations pour déterminer quelles pages appartiennent à un index non cluster. Nous transmettons le database_id, l’object_id et l’index_id de l’index IX_Person_LastName_FirstName_MiddleName et il renverra une ligne pour chaque page utilisée dans cet index. Nous pouvons ensuite exécuter la PAGE DBCC avec les valeurs allocated_page_page_id fournies et trouver la page qui contient Dennis Ware.

SELECT allocated_page_page_id, *FROM sys.dm_db_database_page_allocations (DB_ID(N'AdventureWorks2012'), object_Id('AdventureWorks2012.Person.Person'), 4, null, 'DETAILED')WHERE is_allocated = 1

Nous pouvons ensuite exécuter la PAGE DBCC avec la valeur fournie et trouver la page qui contient Dennis Ware.

DBCC PAGE (10, 1, 24745, 3); -- DB_ID, FileId, PageId, Format

La chose pratique est que dans ce cas la PAGE DBCC renvoie les données de la page dans un jeu de résultats afin que nous puissions trouver assez rapidement la bonne page, dans ce cas la page 24745.

 Gamme de touches 12

Maintenant, si nous utilisons les valeurs resource_description pour nos quatre verrous de plage de clés dans sys.dm_tran_locks nous pouvons voir que les verrous « RangeS-S » ont en fait été accordés aux lignes contenant des données pour Dennis Ware, Victoria Ware, Clay Warten et Aaron Washington. Mais Aaron Washington n’est pas inclus dans le jeu de résultats, alors pourquoi ce quatrième verrou à clé est-il présent? C’est parce que lorsqu’une plage de clés est prise sur une clé, cela empêchera toute donnée d’être ajoutée immédiatement avant une clé avec un verrou de plage de clés. En d’autres termes, aucune donnée ne peut être ajoutée entre, par exemple, Victoria Ware et Clay Warthen, par exemple Jennifer Warner.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (888888, 'VC', 'Jennifer', 'Warner')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

 Gamme de Touches 13

Ici, nous pouvons voir la serrure à clé en attente.

Cela signifie également qu’aucune donnée ne peut être ajoutée entre Timothy Ward et Aaron Washington et signifie que les verrous de plage de clés verrouillent en fait plus de données que celles qui pourraient satisfaire la requête dans la transaction ouverte. Par exemple, nous n’avons pas pu insérer une ligne pour Simon Wartz qui serait placée entre Clay Warthen et Aaron Washington, même si elle ne serait pas retournée dans la requête d’origine dans la transaction ouverte.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (999999, 'VC', 'Simon', 'Wartz')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'KEY'ORDER BY request_mode

 Gamme de Touches 14

Maintenant que nous savons comment les verrous de plage de clés sont utilisés, nous pouvons déterminer quelles ressources contiennent les verrous de plage de clés sans avoir besoin de rechercher dans les pages de l’index non clusterisé. Nous pouvons simplement inclure un indice de table pour renvoyer une partie des données dont nous avons besoin.

SELECT sys.fn_PhysLocFormatter(%%physloc%%), %%lockres%%, *FROM Person.Person WITH (INDEX = IX_Person_LastName_FirstName_MiddleName)WHERE LastName BETWEEN 'Ware' AND 'Warthen'

 Gamme de Touches 18

Comme vous pouvez le voir, cela nous donne les mêmes données de fichier, de page et d’emplacement et la même resource_description pour les trois lignes renvoyées par la requête. Nous savons maintenant que le quatrième verrou « RangeS-S » est placé sur la clé dans l’index immédiatement après la clé pour Clay Warthen, c’est-à-dire la fente 140. Encore une fois, nous pouvons utiliser la PAGE DBCC pour afficher les données à la page 25065.

Maintenant, dans la démonstration que je viens de parcourir, nous avons un index non clusterisé avec la première colonne de la clé de clustering étant celle que nous utilisons dans notre clause WHERE. Mais que se passe-t-il si nous supprimons l’index non clusterisé ? Ou en d’autres termes, que se passe-t-il si nous exécutons une requête sur une autre table filtrant sur une colonne non incluse dans une clé d’index ? Pour le démontrer facilement, nous pouvons simplement supprimer l’index non clusterisé.

DROP INDEX ON .BEGIN TRANSELECT *FROM Person.PersonWHERE LastName BETWEEN 'Ware' AND 'Warthen'

Nous récupérons les mêmes lignes que prévu, mais cette fois, SQL Server a dû utiliser l’index en cluster pour obtenir les données.

 Gamme de touches 15

Alors, quels verrous avons-nous? Cette fois, il n’y a pas de verrou de plage de touches pouvant être appliqué, un verrou partagé doit donc être placé sur toute la table.

SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_modeSELECT object_name(1765581328)

 Gamme de Touches 16

Étant donné que ce verrou partagé est maintenu jusqu’à la fermeture de la transaction, cela signifie qu’aucune donnée de la table ne peut être mise à jour jusqu’à la fin de la transaction, même si nous n’avons lu que trois lignes.

INSERT INTO Person.Person (BusinessEntityID, PersonType, FirstName, LastName)VALUES (777777, 'VC', 'Simon', 'ZZZZZ')SELECT *FROM sys.dm_tran_locksWHERE resource_type = 'OBJECT'ORDER BY request_mode

 Gamme de Touches 17

C’est quelque chose dont vous devez être conscient si vous utilisez le niveau d’isolation SÉRIALISABLE.

Résumé

Si une colonne de clé est utilisée pour filtrer les données d’une requête, des verrous de plage de clés peuvent être pris contre les clés de cet index. Elles seront prises contre toutes les clés qui satisfont la requête plus la clé suivante séquentiellement en dehors de la requête. Cela empêche alors l’ajout de données qui inséreraient une nouvelle clé immédiatement avant une clé avec un verrou à plage de touches.

Laisser un commentaire

Votre adresse e-mail ne sera pas publiée.