Il arrive assez souvent que les utilisateurs (ou développeurs) se tournent vers leur administrateur de données en se plaignant de requêtes bloquées.  Voici une première approche pour savoir qui bloque qui, et quelle ressource est concernée.

Dans un premier temps, il s’agit de comprendre le principe des verrous utilisé sous SQL Server. Je vais néanmoins me limiter ici aux grandes lignes, les anglophones souhaitant plus de détails pourront éventuellement se rendre ici pour satisfaire leur curiosité.
Les différents concepts sont les suivants :

• La granularité du verrou : à la ligne, à la page (ensemble de 8Ko), à la table ou à la base. Suivant les différents paramètres de configuration, le système peut décider d’élargir la granularité de verrouillage associée à une requête, par exemple lorsque le coût de gestion d’un verrouillage à la page devient trop important (car trop de pages sont verrouillées), auquel cas le verrouillage bascule sur le niveau table.
• Le type de verrou : partagé ou exclusif. Plusieurs verrous partagés peuvent coexister à un instant donné sur une même ressource, tandis qu’un verrou exclusif empêche la coexistence simultanée sur la même ressource d’un autre verrou exclusif ou d’un verrou partagé. Les verrous partagés sont par exemple utilisés dans le cadre des instructions SELECT, tandis que les verrous exclusifs sont positionnés par les instructions DELETE, INSERT, …
• La mise en place d’un verrou se fait en deux temps : premièrement une demande de verrou (WAIT), puis lorsque le système a vérifié qu’il n’y avait pas d’incompatibilité avec les autres verrous déjà présents, l’obtention du verrou (GRANT). Le verrou est automatiquement relâché lorsque la transaction se termine, pour quelque raison que ce soit (fin normale, erreur, coupure réseau, …)
• Certains cas particuliers peuvent mener à des situations de blocage, lorsque deux transactions exécutées simultanément attendent chacune que l’autre libère un verrou exclusif avant de pouvoir poursuivre. Cette situation a pour nom Deadlock (Interblocage en français), et je vous invite à consulter le site de Microsoft (http://msdn.microsoft.com/fr-fr/library/ms177433.aspx) si vous souhaitez plus de détails sur ce principe.

Maintenant que les généralités sont posées, simulons un cas de blocage afin de voir comment obtenir des informations sur les détails du blocage.
Nous nous appuierons sur des tables présentes dans la base AdventureWorks (téléchargeable sur le site Github (ici). Toutefois, afin de ne pas polluer cette base, nous ne créerons de nouveaux objets (procédures stockées, …) que sur la base tempdb.

D’abord, créons la procédure stockée qui sera victime du blocage.

USE [tempdb]
GO
IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[TestBlocage]') AND type in (N'P', N'PC'))
DROP PROCEDURE [dbo].[TestBlocage]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
create Procedure [dbo].[TestBlocage] as
begin
select MAX(sod.OrderQty*sod.UnitPrice) from AdventureWorks.Sales.SalesOrderDetail sod;
select SUM(sod.OrderQty*sod.UnitPrice) from AdventureWorks.Sales.SalesOrderDetail sod;
end
GO

Ensuite, préparons le blocage :

use AdventureWorks
go

set transaction isolation level read uncommitted
-- Valeur par défaut
go

begin transaction
update Sales.SalesOrderDetail
set OrderQty=1

-- rollback transaction

La transaction n’ayant pas été fermée (ni validée, ni annulée), les verrous sont en place et restent pour l’instant présents.

Cherchons maintenant à lancer la procédure stockée préparée précédemment.

use tempdb
go

exec dbo.[TestBlocage]

On s’aperçoit alors que l’exécution de la procédure stockée semble bloquée, sans réponse.
En fait, la mise à jour faite précédemment  (OrderQty) a positionné un verrou sur les lignes de la table SalesOrderDetail. Etant donné qu’il s’agit d’une instruction Update, les verrous posés sont des verrous exclusifs.
La lecture de valeurs requise par la procédure TestBlocage est donc en attente de verrous partagés (de lecture), demande de verrou en stand-by en attendant une décision vis-à-vis des verrous exclusifs (validation ou annulation de la transaction).

Pour confirmer ce blocage, la première commande intéressante est tout simplement :

sp_who

La colonne blk du process de la commande SELECT (de la procédure stockée) indique que celle-ci est bloquée par le processus correspondant à la fenêtre dans laquelle est exécutée la requête Update.

La requête ci-dessous permet de savoir quelle instruction exactement est bloquée :

select r.session_id
                ,status
                ,substring(qt.text,r.statement_start_offset/2,
                                        (case when r.statement_end_offset=-1
                                        then len(convert(nvarchar(max),qt.text))*2
                                        else r.statement_end_offset END - r.statement_start_offset)/2 +1)
                AS query_text
                ,qt.dbid
                ,qt.objectid
                ,r.cpu_time
                ,r.total_elapsed_time
                ,r.reads
                ,r.writes
                ,r.logical_reads
                ,r.scheduler_id
from sys.dm_exec_requests r
cross apply sys.dm_exec_sql_text(sql_handle) as qt
WHERE r.session_id>50
order by r.scheduler_id,r.status, r.session_id;

On constate bien que l’instruction bloquée est

select MAX(sod.OrderQty*sod.UnitPrice) from AdventureWorks.Sales.SalesOrderDetail sod;

Les tables et vues système permettent d’identifier (et confirmer) le point de blocage. La requête ci-dessous montre un grand nombre de verrous autorisés (GRANT).

select * from sys.dm_tran_locks

En recherchant les verrous en attente, on constate bien une attente de verrou sur un objet en particulier :

On constate bien que l’instruction est bien en attente d’un verrou sur la table SalesOrderDetail.
Et les autres verrous listés plus haut ? A quoi correspondent-ils ? Intéressons-nous à certains d’entre eux, ceux correspondant à des verrous exclusifs autorisés sur des enregistrements particuliers. La donnée servant à leur identification sera l’identifiant HoBt (Heap or B-Tree).

On constate des verrous sur certaines tables, et même pour la table TransactionHistory des verrous au niveau non seulement de la Primary Key mais aussi d’un autre index de la table. Pourquoi des verrous sur ces entités alors que nous n’avons touché qu’à  la table SalesOrderDetail ?

La réponse s’appelle iduSalesOrderDetail, il s’agit d’un déclencheur (Trigger) sur la table SalesOrderDetail, et ces verrous nous rappellent qu’un trigger est exécuté de manière implicite, dans la même transaction que l’instruction principale…