Escalade des verrous : identifier la vraie cause

Un traitement de maintenance modifie quelques milliers de lignes, puis des requêtes sans rapport apparent se mettent à attendre. L'escalade des verrous est une explication possible. Voir un verrou de table dans un outil ne suffit cependant pas à la démontrer. Il faut identifier le verrou incompatible et la transaction qui le détient.

Interpréter les observations

Un verrou d'intention exclusive sur un objet est normal lorsqu'une transaction modifie des lignes de cet objet. Il annonce des verrous plus fins et n'exclut pas à lui seul tous les lecteurs. Un verrou partagé ou exclusif sur la table possède d'autres règles de compatibilité. Confondre IX et X conduit facilement à une mauvaise correction.

La requête suivante montre les verrous d'objet accordés et attendus dans la base courante. Son périmètre est volontairement limité : des verrous de clé, page, schéma ou application peuvent encore expliquer le blocage. Les autorisations nécessaires dépendent de la version de SQL Server et doivent passer par le rôle de supervision prévu.

SELECT request_session_id, resource_type, request_mode,
       request_status, resource_associated_entity_id
FROM sys.dm_tran_locks
WHERE resource_database_id = DB_ID()
  AND resource_type = 'OBJECT';

Reliez les sessions à la chaîne de blocage et aux instructions actives. Capturez l'événement étendu lock_escalation si cette cause est suspectée. Un verrou X observé maintenant ne révèle pas s'il provient d'une escalade, d'un hint explicite ou d'une autre opération. L'événement apporte cette dimension historique.

Ne considérez pas un nombre précis de lignes modifiées comme une limite de sécurité garantie. Les verrous ne sont pas des lignes. Les index, le parcours, l'isolation et la pression mémoire influencent leur nombre. ROWLOCK n'interdit pas l'escalade. Désactiver celle-ci peut remplacer un problème de concurrence par un problème de mémoire.

Réduire réellement la transaction

L'objectif est de diminuer les ressources détenues simultanément. Une boucle avec de petites instructions conserve un périmètre important si une transaction extérieure englobe tous les passages. Les validations indépendantes créent les points de libération. Vérifiez aussi si l'appelant, le planificateur ou le client ouvre cette transaction extérieure.

L'exercice crée des données temporaires et les traite par instructions validées indépendamment. Il exige l'absence de transaction ouverte et la désactivation d'IMPLICIT_TRANSACTIONS. La taille choisie illustre le mécanisme ; elle ne constitue pas un réglage universel de production.

IF @@TRANCOUNT <> 0 OR (@@OPTIONS & 2) = 2
    THROW 50000, 'Use autocommit with no open transaction.', 1;
CREATE TABLE #Work (Id int PRIMARY KEY, Done bit NOT NULL);
INSERT #Work
SELECT TOP (10000) ROW_NUMBER() OVER (ORDER BY (SELECT NULL)), 0
FROM sys.all_objects AS a CROSS JOIN sys.all_objects AS b;
DECLARE @changed int = 1;
WHILE @changed > 0
BEGIN
    ;WITH batch AS
    (SELECT TOP (500) Id, Done FROM #Work
     WHERE Done = 0 ORDER BY Id)
    UPDATE batch SET Done = 1;
    SET @changed = @@ROWCOUNT;
END;
SELECT COUNT(*) AS Remaining FROM #Work WHERE Done = 0;

La sélection ordonnée par clé rend la progression compréhensible. Sur une vraie table, le prédicat doit disposer d'un accès efficace. Un lot modifiant 500 lignes peut toujours parcourir des millions de lignes. Examinez donc les lignes lues, les lectures logiques, les attentes et la durée de transaction, pas seulement @@ROWCOUNT.

Tester la concurrence et les interruptions

Exécutez la maintenance avec des lecteurs et écrivains représentatifs. Comparez les requêtes les plus lentes, les événements d'escalade et le volume du journal avant et après. Une maintenance légèrement plus longue peut être préférable si les utilisateurs conservent leur délai de réponse. À l'inverse, des milliers de validations minuscules ajoutent un coût inutile.

Les validations indépendantes changent le comportement en cas d'échec. Une annulation laisse un travail partiellement terminé. Le prédicat d'éligibilité doit permettre une reprise sûre, et le suivi de progression doit être validé avec les modifications correspondantes. Si le métier exige une opération entièrement atomique, le découpage en lots change le contrat et demande une décision explicite.

Examinez enfin les déclencheurs, cascades, mécanismes de réplication et transferts de journal vers les répliques. Ces effets peuvent multiplier le travail d'une petite mise à jour. Une correction convaincante identifie le bloqueur initial, réduit l'empreinte pertinente, conserve la sémantique transactionnelle et démontre une latence concurrente acceptable. Faire disparaître un événement d'escalade ne suffit pas à prouver une amélioration globale.

Références techniques: Microsoft Learn: Lock escalation · Microsoft Learn: Lock diagnostics.