{"id":224,"date":"2011-05-02T00:00:40","date_gmt":"2011-05-02T00:00:40","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=224"},"modified":"2026-05-02T14:31:20","modified_gmt":"2026-05-02T12:31:20","slug":"sys-dm_tran_locks-partie-1","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/sys-dm_tran_locks-partie-1\/","title":{"rendered":"Gestion des verrous (Partie 1)"},"content":{"rendered":"

Lorsque l\u2019on rencontre un probl\u00e8me de processus SQL bloqu\u00e9 par un autre, le probl\u00e8me vient des verrous, dont le r\u00f4le est de maintenir l\u2019int\u00e9grit\u00e9 des donn\u00e9es. La vue syst\u00e8me sys.dm_tran_locks permet de lister les diff\u00e9rents verrous g\u00e9r\u00e9s par le moteur (en attente, autoris\u00e9s, \u2026).
\nN\u00e9anmoins, les donn\u00e9es retourn\u00e9es par cette vue peuvent parfois \u00eatre relativement obscures et difficiles \u00e0 d\u00e9chiffrer. Ce premier article de d\u00e9cryptage de ce sujet s\u2019attachera \u00e0 \u00e9claircir un premier niveau de verrou : les verrous de type Page.
\nLes exemples propos\u00e9s dans cet article s\u2019appuient sur la base AdventureWorks, que l\u2019on peut trouver en libre acc\u00e8s ici : http:\/\/msftdbprodsamples.codeplex.com\/<\/a><\/p>\n

Dans un premier temps, positionnons un verrou sur un enregistrement donn\u00e9.<\/p>\n

use AdventureWorks\r\ngo\r\n\r\nbegin transaction\r\nselect top 1 *\r\n\tfrom Sales.SalesOrderDetail with (xlock)\r\n\torder by rowguid<\/pre>\n
Le fait de commencer une transaction et de ne pas la fermer permet de maintenir les verrous en place tant que la connexion n\u2019est pas ferm\u00e9e.
\nMaintenant, cherchons \u00e0 consommer l\u2019ensemble des enregistrements de la table dans une autre connexion :<\/p>\n
use AdventureWorks\r\ngo\r\n\r\nbegin transaction\r\nselect * from Sales.SalesOrderDetail<\/pre>\n
Quelques enregistrements commencent \u00e0 venir, mais la requ\u00eate semble ensuite bloqu\u00e9e, tournant en rond sans ramener plus d\u2019enregistrements.
\nLa commande sp_who2 nous montre que nous sommes bloqu\u00e9s, en attente de la fin d\u2019un autre processus.
\n\"\"<\/a>
\nLe processus 52, le dernier SELECT, est toujours en cours d\u2019ex\u00e9cution, et attend le processus 56 (l\u2019autre transaction lanc\u00e9e pr\u00e9c\u00e9demment et pas termin\u00e9e).
\nMais qu\u2019attend-t-il exactement comme ressource ? La vue sys.dm_tran_locks est l\u00e0 pour nous l\u2019indiquer :
\n\"\"<\/a>
\nNous voyons donc qu\u2019il y a attente sur \u00e0 une demande de verrou de type \u2018S\u2019 (verrou partag\u00e9, demander pour une lecture, par l\u2019instruction SELECT) une ressource de type PAGE.
\nQu\u2019est-ce que cette copie d\u2019\u00e9cran nous indique de plus concernant cette page ?
\nD\u2019une part, la colonne resource_associated_entity_id, en suivant l\u2019aide en ligne (https:\/\/docs.microsoft.com\/fr-fr\/sql\/relational-databases\/system-dynamic-management-views\/sys-dm-tran-locks-transact-sql<\/a>), nous permet de situer l\u2019index auquel appartient cette page :
\n\"\"<\/a>
\nNous voici donc confort\u00e9s dans l\u2019id\u00e9e que le blocage se fait sur la table SalesOrderDetail, et nous savons donc maintenant que c\u2019est la Primary Key que nous requ\u00eate bloqu\u00e9e cherche \u00e0 parcourir.
\nLes propri\u00e9t\u00e9s de cet index nous confirment qu\u2019il peut accepter des verrous au niveau Page :
\n\"\"<\/a>
\nMais quels sont les enregistrements contenus dans cette fameuse page ? D\u00e9j\u00e0, nous avons l\u2019identification de la page (1:21366), c\u2019est-\u00e0-dire la page 21366 du fichier num\u00e9ro 1. Ensuite, pour avoir le contenu de la page, une petite commande peu document\u00e9e permet d\u2019avoir quelques d\u00e9tails : DBCC PAGE.
\nL\u2019usage ci-apr\u00e8s est \u00e0 mes yeux le plus verbeux et le plus explicite :<\/p>\n
dbcc traceon (3604,-1) -- Pour afficher les r\u00e9sultats de DBCC\r\ndbcc page ('Adventureworks',1,21366,3)<\/pre>\n
Le r\u00e9sultat est assez long, je n\u2019en pr\u00e9sente ici qu\u2019un bref aper\u00e7u :<\/p>\n
Ex\u00e9cution de DBCC termin\u00e9e. Si DBCC vous a adress\u00e9 des messages d’erreur, contactez l’administrateur syst\u00e8me.<\/span><\/p>\n
PAGE: (1:21366)<\/span>
\nBUFFER:<\/span>
\nBUF @0x0000000081FCCB80<\/span><\/p>\n
bpage = 0x0000000081774000 bhash = 0x0000000000000000 bpageno = (1:21366)<\/span>
\nbdbid = 12 breferences = 0 bcputicks = 0<\/span>
\nbsampleCount = 0 bUse1 = 3466 bstat = 0xc00009<\/span>
\nblog = 0x159a2159 bnext = 0x0000000000000000<\/span><\/p>\n
PAGE HEADER:<\/span>
\nPage @0x0000000081774000<\/span><\/p>\n
m_pageId = (1:21366) m_headerVersion = 1 m_type = 1<\/span>
\nm_typeFlagBits = 0x0 m_level = 0 m_flagBits = 0x220<\/span>
\nm_objId (AllocUnitId.idObj) = 164 m_indexId (AllocUnitId.idInd) = 256 <\/span>
\nMetadata: AllocUnitId = 72057594048675840 <\/span>
\nMetadata: PartitionId = 72057594046251008 Metadata: IndexId = 1<\/span>
\nMetadata: ObjectId = 642101328 m_prevPage = (1:21365) m_nextPage = (1:21367)<\/span>
\npminlen = 62 m_slotCnt = 116 m_freeCnt = 12<\/span>
\nm_freeData = 7948 m_reservedCnt = 0 m_lsn = (40:5652:145)<\/span>
\nm_xactReserved = 0 m_xdesId = (0:1440) m_ghostRecCnt = 0<\/span>
\nm_tornBits = 169440924<\/span><\/p>\n
Allocation Status<\/span><\/p>\n
GAM (1:2) = ALLOCATED SGAM (1:3) = NOT ALLOCATED <\/span>
\nPFS (1:16176) = 0x40 ALLOCATED 0_PCT_FULL DIFF (1:6) = CHANGED<\/span>
\nML (1:7) = NOT MIN_LOGGED<\/span><\/p>\n
Slot 0 Offset 0x60 Length 66<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP Record Size = 66<\/span><\/p>\n
Memory Dump @0x000000000F81C060<\/span><\/p>\n
0000000000000000: 10003e00 df0e0100 c68e0100 0100c402 \u2020..>.\u00df…\u00c6\u008e….\u00c4. <\/span>
\n0000000000000010: 00000100 0000cc56 05000000 00000000 \u2020……\u00ccV…….. <\/span>
\n0000000000000020: 00000000 00005af8 2af9b080 574c9557 \u2020……Z\u00f8*\u00f9\u00b0.WL\u0095W <\/span>
\n0000000000000030: d76c3691 5f1a0000 0000d994 00000a00 \u2020\u00d7l6\u0091_…..\u00d9\u0094…. <\/span>
\n0000000000000040: 0400\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020\u2020..<\/span><\/p>\n
Slot 0 Column 1 Offset 0x4 Length 4 Length (physical) 4<\/span><\/p>\n
SalesOrderID = 69343<\/span><\/p>\n
Slot 0 Column 2 Offset 0x8 Length 4 Length (physical) 4<\/span><\/p>\n
SalesOrderDetailID = 102086<\/span><\/p>\n
Slot 0 Column 3 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n
CarrierTrackingNumber = [NULL]<\/span><\/p>\n
Slot 0 Column 4 Offset 0xc Length 2 Length (physical) 2<\/span><\/p>\n
OrderQty = 1<\/span><\/p>\n
Slot 0 Column 5 Offset 0xe Length 4 Length (physical) 4<\/span><\/p>\n
ProductID = 708<\/span><\/p>\n
Slot 0 Column 6 Offset 0x12 Length 4 Length (physical) 4<\/span><\/p>\n
SpecialOfferID = 1<\/span><\/p>\n
Slot 0 Column 7 Offset 0x16 Length 8 Length (physical) 8<\/span><\/p>\n
UnitPrice = $34.9900<\/span><\/p>\n
Slot 0 Column 8 Offset 0x1e Length 8 Length (physical) 8<\/span><\/p>\n
UnitPriceDiscount = $0.0000<\/span><\/p>\n
Slot 0 Column 10 Offset 0x26 Length 16 Length (physical) 16<\/span><\/p>\n
rowguid = f92af85a-80b0-4c57-9557-d76c36915f1a<\/span><\/p>\n
Slot 0 Column 11 Offset 0x36 Length 8 Length (physical) 8<\/span><\/p>\n
ModifiedDate = 2004-04-30 00:00:00.000<\/span><\/p>\n
Slot 0 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n
KeyHashValue = (5e7b621eb14c)<\/span><\/p>\n
(\u2026 d\u00e9tails de tous les enregistrements de Slot 1 \u00e0 Slot 114 \u2026)<\/p>\n
Slot 115 Offset 0x1eae Length 94<\/span><\/p>\n
Record Type = PRIMARY_RECORD Record Attributes = NULL_BITMAP VARIABLE_COLUMNS<\/span>
\nRecord Size = 94 <\/span>
\nMemory Dump @0x000000000F81DEAE<\/span><\/p>\n
0000000000000000: 30003e00 0d0f0100 398f0100 03006003 \u20200.>…..9…..`. <\/span>
\n0000000000000010: 00000100 000048d0 05000000 00000000 \u2020……H\u00d0…….. <\/span>
\n0000000000000020: 00000000 00004602 5227be6c 4249ac28 \u2020……F.R’\u00belBI\u00ac( <\/span>
\n0000000000000030: 00009575 40c90000 0000da94 00000a00 \u2020..\u0095u@\u00c9….\u00da\u0094…. <\/span>
\n0000000000000040: 00000100 5e003400 45004500 42002d00 \u2020….^.4.E.E.B.-. <\/span>
\n0000000000000050: 34003400 41003400 2d003900 4300\u2020\u2020\u2020\u2020\u2020\u20204.4.A.4.-.9.C.<\/span><\/p>\n
Slot 115 Column 1 Offset 0x4 Length 4 Length (physical) 4<\/span><\/p>\n
SalesOrderID = 69389<\/span><\/p>\n
Slot 115 Column 2 Offset 0x8 Length 4 Length (physical) 4<\/span><\/p>\n
SalesOrderDetailID = 102201<\/span><\/p>\n
Slot 115 Column 3 Offset 0x46 Length 24 Length (physical) 24<\/span><\/p>\n
CarrierTrackingNumber = 4EEB-44A4-9C<\/span><\/p>\n
Slot 115 Column 4 Offset 0xc Length 2 Length (physical) 2<\/span><\/p>\n
OrderQty = 3<\/span><\/p>\n
Slot 115 Column 5 Offset 0xe Length 4 Length (physical) 4<\/span><\/p>\n
ProductID = 864<\/span><\/p>\n
Slot 115 Column 6 Offset 0x12 Length 4 Length (physical) 4<\/span><\/p>\n
SpecialOfferID = 1<\/span><\/p>\n
Slot 115 Column 7 Offset 0x16 Length 8 Length (physical) 8<\/span><\/p>\n
UnitPrice = $38.1000<\/span><\/p>\n
Slot 115 Column 8 Offset 0x1e Length 8 Length (physical) 8<\/span><\/p>\n
UnitPriceDiscount = $0.0000<\/span><\/p>\n
Slot 115 Column 10 Offset 0x26 Length 16 Length (physical) 16<\/span><\/p>\n
rowguid = 27520246-6cbe-4942-ac28-0000957540c9<\/span><\/p>\n
Slot 115 Column 11 Offset 0x36 Length 8 Length (physical) 8<\/span><\/p>\n
ModifiedDate = 2004-05-01 00:00:00.000<\/span><\/p>\n
Slot 115 Offset 0x0 Length 0 Length (physical) 0<\/span><\/p>\n
KeyHashValue = (7e8207136809)<\/span><\/p>\n
Ex\u00e9cution de DBCC termin\u00e9e. Si DBCC vous a adress\u00e9 des messages d’erreur, contactez l’administrateur syst\u00e8me.<\/span><\/p>\n
Etant donn\u00e9 que l\u2019index s\u2019appuie sur les colonnes SalesOrderId et SalesOrderDetailId, nous avons donc dans cette page les enregistrements allant de (SalesOrderId=69343, SalesOrderDetailId=102086) \u00e0 (SalesOrderId=69389, SalesOrderDetailId=102201).
\nEt, fort heureusement, cette analyse est confirm\u00e9e par l\u2019enregistrement que nous avions volontairement verrouill\u00e9 :<\/p>\n
\"\"<\/a><\/p>\n
Voici pour l\u2019identification des verrous de niveau PAGE.
\nUn prochain article permettra d\u2019identifier les verrous lorsqu\u2019ils sont pos\u00e9s au niveau ligne, car ce premier article sur le sujet est d\u00e9j\u00e0 assez long et car, pour l\u2019identification pr\u00e9cise des lignes, les informations fournies par sys.dm_tran_locks sont d\u2019une nature diff\u00e9rente \u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
Lorsque l\u2019on rencontre un probl\u00e8me de processus SQL bloqu\u00e9 par un autre, le probl\u00e8me vient des verrous, dont le r\u00f4le est de maintenir l\u2019int\u00e9grit\u00e9 des donn\u00e9es. La vue syst\u00e8me sys.dm_tran_locks permet de lister les diff\u00e9rents verrous g\u00e9r\u00e9s par le moteur … Continuer la lecture →<\/span><\/a><\/p>\n","protected":false},"author":7,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[1],"tags":[],"class_list":["post-224","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/224","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/users\/7"}],"replies":[{"embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/comments?post=224"}],"version-history":[{"count":21,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/224\/revisions"}],"predecessor-version":[{"id":1964,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/224\/revisions\/1964"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=224"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=224"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=224"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}