{"id":191,"date":"2010-05-17T00:00:42","date_gmt":"2010-05-17T00:00:42","guid":{"rendered":"http:\/\/www.sqlserver.fr\/blog\/?p=191"},"modified":"2026-05-02T14:31:25","modified_gmt":"2026-05-02T12:31:25","slug":"verrous","status":"publish","type":"post","link":"https:\/\/www.sqlserver.fr\/blog\/verrous\/","title":{"rendered":"Verrous"},"content":{"rendered":"

Il arrive assez souvent que les utilisateurs (ou d\u00e9veloppeurs) se tournent vers leur administrateur de donn\u00e9es en se plaignant de requ\u00eates bloqu\u00e9es.\u00a0 Voici une premi\u00e8re approche pour savoir qui bloque qui, et quelle ressource est concern\u00e9e.<\/p>\n

Dans un premier temps, il s\u2019agit de comprendre le principe des verrous utilis\u00e9 sous SQL Server. Je vais n\u00e9anmoins me limiter ici aux grandes lignes, les anglophones souhaitant plus de d\u00e9tails pourront \u00e9ventuellement se rendre ici<\/a> pour satisfaire leur curiosit\u00e9.
\nLes diff\u00e9rents concepts sont les suivants :<\/p>\n

\u2022\u00a0La granularit\u00e9 du verrou : \u00e0 la ligne, \u00e0 la page (ensemble de 8Ko), \u00e0 la table ou \u00e0 la base. Suivant les diff\u00e9rents param\u00e8tres de configuration, le syst\u00e8me peut d\u00e9cider d\u2019\u00e9largir la granularit\u00e9 de verrouillage associ\u00e9e \u00e0 une requ\u00eate, par exemple lorsque le co\u00fbt de gestion d\u2019un verrouillage \u00e0 la page devient trop important (car trop de pages sont verrouill\u00e9es), auquel cas le verrouillage bascule sur le niveau table.
\n\u2022\u00a0Le type de verrou : partag\u00e9 ou exclusif. Plusieurs verrous partag\u00e9s peuvent coexister \u00e0 un instant donn\u00e9 sur une m\u00eame ressource, tandis qu\u2019un verrou exclusif emp\u00eache la coexistence simultan\u00e9e sur la m\u00eame ressource d\u2019un autre verrou exclusif ou d\u2019un verrou partag\u00e9. Les verrous partag\u00e9s sont par exemple utilis\u00e9s dans le cadre des instructions SELECT, tandis que les verrous exclusifs sont positionn\u00e9s par les instructions DELETE, INSERT, \u2026
\n\u2022\u00a0La mise en place d\u2019un verrou se fait en deux temps : premi\u00e8rement une demande de verrou (WAIT), puis lorsque le syst\u00e8me a v\u00e9rifi\u00e9 qu\u2019il n\u2019y avait pas d\u2019incompatibilit\u00e9 avec les autres verrous d\u00e9j\u00e0 pr\u00e9sents, l\u2019obtention du verrou (GRANT). Le verrou est automatiquement rel\u00e2ch\u00e9 lorsque la transaction se termine, pour quelque raison que ce soit (fin normale, erreur, coupure r\u00e9seau, \u2026)
\n\u2022\u00a0Certains cas particuliers peuvent mener \u00e0 des situations de blocage, lorsque deux transactions ex\u00e9cut\u00e9es simultan\u00e9ment attendent chacune que l\u2019autre lib\u00e8re un verrou exclusif avant de pouvoir poursuivre. Cette situation a pour nom Deadlock (Interblocage en fran\u00e7ais), et je vous invite \u00e0 consulter le site de Microsoft (http:\/\/msdn.microsoft.com\/fr-fr\/library\/ms177433.aspx<\/a>) si vous souhaitez plus de d\u00e9tails sur ce principe.<\/p>\n

Maintenant que les g\u00e9n\u00e9ralit\u00e9s sont pos\u00e9es, simulons un cas de blocage afin de voir comment obtenir des informations sur les d\u00e9tails du blocage.
\nNous nous appuierons sur des tables pr\u00e9sentes dans la base AdventureWorks (t\u00e9l\u00e9chargeable sur le site Github (ici<\/a>). Toutefois, afin de ne pas polluer cette base, nous ne cr\u00e9erons de nouveaux objets (proc\u00e9dures stock\u00e9es, \u2026) que sur la base tempdb.<\/p>\n

D\u2019abord, cr\u00e9ons la proc\u00e9dure stock\u00e9e qui sera victime du blocage.<\/p>\n

USE [tempdb]\r\nGO\r\nIF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestBlocage]') AND type in (N'P', N'PC'))\r\nDROP PROCEDURE [dbo].[TestBlocage]\r\nGO\r\nSET ANSI_NULLS ON\r\nGO\r\nSET QUOTED_IDENTIFIER ON\r\nGO\r\ncreate Procedure [dbo].[TestBlocage] as\r\nbegin\r\nselect MAX(sod.OrderQty*sod.UnitPrice) from AdventureWorks.Sales.SalesOrderDetail sod;\r\nselect SUM(sod.OrderQty*sod.UnitPrice) from AdventureWorks.Sales.SalesOrderDetail sod;\r\nend\r\nGO<\/pre>\n
Ensuite, pr\u00e9parons le blocage :<\/p>\n
use AdventureWorks\r\ngo\r\n\r\nset transaction isolation level read uncommitted\r\n-- Valeur par d\u00e9faut\r\ngo\r\n\r\nbegin transaction\r\nupdate Sales.SalesOrderDetail\r\nset OrderQty=1\r\n\r\n-- rollback transaction<\/pre>\n
La transaction n\u2019ayant pas \u00e9t\u00e9 ferm\u00e9e (ni valid\u00e9e, ni annul\u00e9e), les verrous sont en place et restent pour l\u2019instant pr\u00e9sents.<\/p>\n
Cherchons maintenant \u00e0 lancer la proc\u00e9dure stock\u00e9e pr\u00e9par\u00e9e pr\u00e9c\u00e9demment.<\/p>\n
use tempdb\r\ngo\r\n\r\nexec dbo.[TestBlocage]<\/pre>\n
On s\u2019aper\u00e7oit alors que l\u2019ex\u00e9cution de la proc\u00e9dure stock\u00e9e semble bloqu\u00e9e, sans r\u00e9ponse.
\nEn fait, la mise \u00e0 jour faite pr\u00e9c\u00e9demment\u00a0 (OrderQty) a positionn\u00e9 un verrou sur les lignes de la table SalesOrderDetail. Etant donn\u00e9 qu\u2019il s\u2019agit d\u2019une instruction Update, les verrous pos\u00e9s sont des verrous exclusifs.
\nLa lecture de valeurs requise par la proc\u00e9dure TestBlocage est donc en attente de verrous partag\u00e9s (de lecture), demande de verrou en stand-by en attendant une d\u00e9cision vis-\u00e0-vis des verrous exclusifs (validation ou annulation de la transaction).<\/p>\n
Pour confirmer ce blocage, la premi\u00e8re commande int\u00e9ressante est tout simplement :<\/p>\n
sp_who<\/pre>\n
La colonne blk du process de la commande SELECT (de la proc\u00e9dure stock\u00e9e) indique que celle-ci est bloqu\u00e9e par le processus correspondant \u00e0 la fen\u00eatre dans laquelle est ex\u00e9cut\u00e9e la requ\u00eate Update.<\/p>\n
La requ\u00eate ci-dessous permet de savoir quelle instruction exactement est bloqu\u00e9e :<\/p>\n
select r.session_id\r\n                ,status\r\n                ,substring(qt.text,r.statement_start_offset\/2,\r\n                                        (case when r.statement_end_offset=-1\r\n                                        then len(convert(nvarchar(max),qt.text))*2\r\n                                        else r.statement_end_offset END - r.statement_start_offset)\/2 +1)\r\n                AS query_text\r\n                ,qt.dbid\r\n                ,qt.objectid\r\n                ,r.cpu_time\r\n                ,r.total_elapsed_time\r\n                ,r.reads\r\n                ,r.writes\r\n                ,r.logical_reads\r\n                ,r.scheduler_id\r\nfrom sys.dm_exec_requests r\r\ncross apply sys.dm_exec_sql_text(sql_handle) as qt\r\nWHERE r.session_id>50\r\norder by r.scheduler_id,r.status, r.session_id;<\/pre>\n
On constate bien que l\u2019instruction bloqu\u00e9e est<\/p>\n
select MAX(sod.OrderQty*sod.UnitPrice) from AdventureWorks.Sales.SalesOrderDetail sod;<\/pre>\n
Les tables et vues syst\u00e8me permettent d\u2019identifier (et confirmer) le point de blocage. La requ\u00eate ci-dessous montre un grand nombre de verrous autoris\u00e9s (GRANT).<\/p>\n
select * from sys.dm_tran_locks<\/pre>\n
En recherchant les verrous en attente, on constate bien une attente de verrou sur un objet en particulier :
\n\"\"<\/a>
\nOn constate bien que l\u2019instruction est bien en attente d\u2019un verrou sur la table SalesOrderDetail.
\nEt les autres verrous list\u00e9s plus haut ? A quoi correspondent-ils ? Int\u00e9ressons-nous \u00e0 certains d\u2019entre eux, ceux correspondant \u00e0 des verrous exclusifs autoris\u00e9s sur des enregistrements particuliers. La donn\u00e9e servant \u00e0 leur identification sera l\u2019identifiant HoBt (Heap or B-Tree).
\n\"\"<\/a><\/p>\n
On constate des verrous sur certaines tables, et m\u00eame pour la table TransactionHistory des verrous au niveau non seulement de la Primary Key mais aussi d\u2019un autre index de la table. Pourquoi des verrous sur ces entit\u00e9s alors que nous n\u2019avons touch\u00e9 qu\u2019\u00e0\u00a0 la table SalesOrderDetail ?<\/p>\n
La r\u00e9ponse s\u2019appelle iduSalesOrderDetail, il s\u2019agit d\u2019un d\u00e9clencheur (Trigger) sur la table SalesOrderDetail, et ces verrous nous rappellent qu\u2019un trigger est ex\u00e9cut\u00e9 de mani\u00e8re implicite, dans la m\u00eame transaction que l\u2019instruction principale\u2026<\/p>\n","protected":false},"excerpt":{"rendered":"
Il arrive assez souvent que les utilisateurs (ou d\u00e9veloppeurs) se tournent vers leur administrateur de donn\u00e9es en se plaignant de requ\u00eates bloqu\u00e9es.\u00a0 Voici une premi\u00e8re approche pour savoir qui bloque qui, et quelle ressource est concern\u00e9e.<\/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-191","post","type-post","status-publish","format-standard","hentry","category-article_sql"],"_links":{"self":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/191","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=191"}],"version-history":[{"count":17,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/191\/revisions"}],"predecessor-version":[{"id":1971,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/posts\/191\/revisions\/1971"}],"wp:attachment":[{"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/media?parent=191"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/categories?post=191"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.sqlserver.fr\/blog\/wp-json\/wp\/v2\/tags?post=191"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}