Lorsque l’on rencontre un problème de processus SQL bloqué par un autre, le problème vient des verrous, dont le rôle est de maintenir l’intégrité des données. La vue système sys.dm_tran_locks permet de lister les différents verrous gérés par le moteur (en attente, autorisés, …).
Néanmoins, les données retournées par cette vue peuvent parfois être relativement obscures et difficiles à déchiffrer. Ce premier article de décryptage de ce sujet s’attachera à éclaircir un premier niveau de verrou : les verrous de type Page.
Les exemples proposés dans cet article s’appuient sur la base AdventureWorks, que l’on peut trouver en libre accès ici : http://msftdbprodsamples.codeplex.com/

Dans un premier temps, positionnons un verrou sur un enregistrement donné.

use AdventureWorks
go

begin transaction
select top 1 *
	from Sales.SalesOrderDetail with (xlock)
	order by rowguid

Le fait de commencer une transaction et de ne pas la fermer permet de maintenir les verrous en place tant que la connexion n’est pas fermée.
Maintenant, cherchons à consommer l’ensemble des enregistrements de la table dans une autre connexion :

use AdventureWorks
go

begin transaction
select * from Sales.SalesOrderDetail

Quelques enregistrements commencent à venir, mais la requête semble ensuite bloquée, tournant en rond sans ramener plus d’enregistrements.
La commande sp_who2 nous montre que nous sommes bloqués, en attente de la fin d’un autre processus.

Le processus 52, le dernier SELECT, est toujours en cours d’exécution, et attend le processus 56 (l’autre transaction lancée précédemment et pas terminée).
Mais qu’attend-t-il exactement comme ressource ? La vue sys.dm_tran_locks est là pour nous l’indiquer :

Nous voyons donc qu’il y a attente sur à une demande de verrou de type ‘S’ (verrou partagé, demander pour une lecture, par l’instruction SELECT) une ressource de type PAGE.
Qu’est-ce que cette copie d’écran nous indique de plus concernant cette page ?
D’une part, la colonne resource_associated_entity_id, en suivant l’aide en ligne (https://docs.microsoft.com/fr-fr/sql/relational-databases/system-dynamic-management-views/sys-dm-tran-locks-transact-sql), nous permet de situer l’index auquel appartient cette page :

Nous voici donc confortés dans l’idée que le blocage se fait sur la table SalesOrderDetail, et nous savons donc maintenant que c’est la Primary Key que nous requête bloquée cherche à parcourir.
Les propriétés de cet index nous confirment qu’il peut accepter des verrous au niveau Page :

Mais quels sont les enregistrements contenus dans cette fameuse page ? Déjà, nous avons l’identification de la page (1:21366), c’est-à-dire la page 21366 du fichier numéro 1. Ensuite, pour avoir le contenu de la page, une petite commande peu documentée permet d’avoir quelques détails : DBCC PAGE.
L’usage ci-après est à mes yeux le plus verbeux et le plus explicite :

dbcc traceon (3604,-1) -- Pour afficher les résultats de DBCC
dbcc page ('Adventureworks',1,21366,3)

Le résultat est assez long, je n’en présente ici qu’un bref aperçu :

Exécution de DBCC terminée. Si DBCC vous a adressé des messages d’erreur, contactez l’administrateur système.

PAGE: (1:21366)
BUFFER:
BUF @0x0000000081FCCB80

bpage = 0x0000000081774000 bhash = 0x0000000000000000 bpageno = (1:21366)
bdbid = 12 breferences = 0 bcputicks = 0
bsampleCount = 0 bUse1 = 3466 bstat = 0xc00009
blog = 0x159a2159 bnext = 0x0000000000000000

PAGE HEADER:
Page @0x0000000081774000

m_pageId = (1:21366) m_headerVersion = 1 m_type = 1
m_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x220
m_objId (AllocUnitId.idObj) = 164 m_indexId (AllocUnitId.idInd) = 256
Metadata: AllocUnitId = 72057594048675840
Metadata: PartitionId = 72057594046251008 Metadata: IndexId = 1
Metadata: ObjectId = 642101328 m_prevPage = (1:21365) m_nextPage = (1:21367)
pminlen = 62 m_slotCnt = 116 m_freeCnt = 12
m_freeData = 7948 m_reservedCnt = 0 m_lsn = (40:5652:145)
m_xactReserved = 0 m_xdesId = (0:1440) m_ghostRecCnt = 0
m_tornBits = 169440924

Allocation Status

GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED
PFS (1:16176) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED
ML (1:7) = NOT MIN_LOGGED

Slot 0 Offset 0x60 Length 66

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 66

Memory Dump @0x000000000F81C060

0000000000000000: 10003e00 df0e0100 c68e0100 0100c402 †..>.߅Ǝ….Ä.
0000000000000010: 00000100 0000cc56 05000000 00000000 †……ÌV……..
0000000000000020: 00000000 00005af8 2af9b080 574c9557 †……Zø*ù°.WL•W
0000000000000030: d76c3691 5f1a0000 0000d994 00000a00 †×l6‘_…..ٔ….
0000000000000040: 0400†††††††††††††††††††††††††††††††††..

Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4

SalesOrderID = 69343

Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4

SalesOrderDetailID = 102086

Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0

CarrierTrackingNumber = [NULL]

Slot 0 Column 4 Offset 0xc Length 2 Length (physical) 2

OrderQty = 1

Slot 0 Column 5 Offset 0xe Length 4 Length (physical) 4

ProductID = 708

Slot 0 Column 6 Offset 0x12 Length 4 Length (physical) 4

SpecialOfferID = 1

Slot 0 Column 7 Offset 0x16 Length 8 Length (physical) 8

UnitPrice = $34.9900

Slot 0 Column 8 Offset 0x1e Length 8 Length (physical) 8

UnitPriceDiscount = $0.0000

Slot 0 Column 10 Offset 0x26 Length 16 Length (physical) 16

rowguid = f92af85a-80b0-4c57-9557-d76c36915f1a

Slot 0 Column 11 Offset 0x36 Length 8 Length (physical) 8

ModifiedDate = 2004-04-30 00:00:00.000

Slot 0 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (5e7b621eb14c)

(… détails de tous les enregistrements de Slot 1 à Slot 114 …)

Slot 115 Offset 0x1eae Length 94

Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS
Record Size = 94
Memory Dump @0x000000000F81DEAE

0000000000000000: 30003e00 0d0f0100 398f0100 03006003 †0.>…..9…..`.
0000000000000010: 00000100 000048d0 05000000 00000000 †……HÐ……..
0000000000000020: 00000000 00004602 5227be6c 4249ac28 †……F.R’¾lBI¬(
0000000000000030: 00009575 40c90000 0000da94 00000a00 †..•u@É….ڔ….
0000000000000040: 00000100 5e003400 45004500 42002d00 †….^.4.E.E.B.-.
0000000000000050: 34003400 41003400 2d003900 4300††††††4.4.A.4.-.9.C.

Slot 115 Column 1 Offset 0x4 Length 4 Length (physical) 4

SalesOrderID = 69389

Slot 115 Column 2 Offset 0x8 Length 4 Length (physical) 4

SalesOrderDetailID = 102201

Slot 115 Column 3 Offset 0x46 Length 24 Length (physical) 24

CarrierTrackingNumber = 4EEB-44A4-9C

Slot 115 Column 4 Offset 0xc Length 2 Length (physical) 2

OrderQty = 3

Slot 115 Column 5 Offset 0xe Length 4 Length (physical) 4

ProductID = 864

Slot 115 Column 6 Offset 0x12 Length 4 Length (physical) 4

SpecialOfferID = 1

Slot 115 Column 7 Offset 0x16 Length 8 Length (physical) 8

UnitPrice = $38.1000

Slot 115 Column 8 Offset 0x1e Length 8 Length (physical) 8

UnitPriceDiscount = $0.0000

Slot 115 Column 10 Offset 0x26 Length 16 Length (physical) 16

rowguid = 27520246-6cbe-4942-ac28-0000957540c9

Slot 115 Column 11 Offset 0x36 Length 8 Length (physical) 8

ModifiedDate = 2004-05-01 00:00:00.000

Slot 115 Offset 0x0 Length 0 Length (physical) 0

KeyHashValue = (7e8207136809)

Exécution de DBCC terminée. Si DBCC vous a adressé des messages d’erreur, contactez l’administrateur système.

Etant donné que l’index s’appuie sur les colonnes SalesOrderId et SalesOrderDetailId, nous avons donc dans cette page les enregistrements allant de (SalesOrderId=69343, SalesOrderDetailId=102086) à (SalesOrderId=69389, SalesOrderDetailId=102201).
Et, fort heureusement, cette analyse est confirmée par l’enregistrement que nous avions volontairement verrouillé :

Voici pour l’identification des verrous de niveau PAGE.
Un prochain article permettra d’identifier les verrous lorsqu’ils sont posés au niveau ligne, car ce premier article sur le sujet est déjà assez long et car, pour l’identification précise des lignes, les informations fournies par sys.dm_tran_locks sont d’une nature différente …