Cette rubrique explique comment débloquer les requêtes sur une instance ApsaraDB RDS for SQL Server dans des situations d'urgence. Pour éliminer le blocage à la source, identifiez et analysez ses causes, puis élaborez une solution adaptée. Dans la plupart des cas, le blocage provient d'index manquants ou de transactions volumineuses. En l'absence d'index appropriés ou lorsque les transactions sont trop lourdes, leur exécution s'allonge et les verrous restent actifs plus longtemps. Par conséquent, un grand nombre de requêtes se retrouvent bloquées. Pour un guide détaillé sur l'analyse des causes profondes et la prévention, consultez Comment résoudre le problème de blocage des requêtes sur une instance ApsaraDB RDS for SQL Server ?
Vue d'ensemble
Un blocage survient lorsqu'une session détient un verrou sur une ressource et qu'une autre session demande un verrou incompatible sur cette même ressource. La seconde session attend que la première libère son verrou. Contrairement à un interblocage (deadlock), où deux sessions forment une dépendance circulaire que SQL Server détecte et résout automatiquement, le blocage est une attente unidirectionnelle. La session bloquée attend que le détenteur du verrou termine son opération ou soit arrêté.
Des blocages brefs sont normaux lors de charges de travail concurrentes. Toutefois, si les sessions conservent les verrous pendant de longues périodes, un arriéré de requêtes en attente s'accumule, ce qui dégrade les performances de l'application ou provoque des expirations de délai (timeouts).
Description du problème
Les requêtes sont bloquées sur l'instance ApsaraDB RDS for SQL Server ; les requêtes de l'application expirent ou s'exécutent lentement.
Cause
Les transactions entrent en concurrence pour l'accès aux ressources verrouillées. Lorsqu'une session détient un verrou et qu'une autre session a besoin d'un verrou incompatible sur la même ressource, la seconde session reste bloquée jusqu'à la libération du verrou.
Solution
Suivez ces trois étapes pour identifier et arrêter la session bloquante.
Étape 1 : Identifier les sessions bloquantes et en attente
Exécutez l'instruction suivante pour récupérer des informations sur les verrous actuels et les requêtes bloquées. Cette requête joint plusieurs vues de gestion dynamique (DMV) de SQL Server afin d'afficher les sessions bloquées, les sessions à l'origine du blocage et les instructions SQL exécutées par chaque session.
SELECT dtl.request_session_id AS waitSID,
der.blocking_session_id AS blockSID,
dowt.resource_description,
der.wait_type,
dowt.wait_duration_ms,
DB_NAME(dtl.resource_database_id) AS DB,
dtl.resource_associated_entity_id AS waitingAssociatedEntity,
dtl.resource_type AS waitResType,
dtl.request_type AS waitReqType,
dest.[text] AS waitSQL,
dtl1.request_type AS blockReqType,
dest1.[text] AS blockingSQL
FROM sys.dm_tran_locks dtl
JOIN sys.dm_os_waiting_tasks dowt ON dowt.resource_address=dtl.lock_owner_address
JOIN sys.dm_exec_requests der ON der.session_id=dtl.request_session_id CROSS apply sys.dm_exec_sql_text(der.sql_handle) dest
LEFT JOIN sys.dm_exec_requests der1 ON der.session_id=dowt.blocking_session_id OUTER apply sys.dm_exec_sql_text(der1.sql_handle) dest1
LEFT JOIN sys.dm_tran_locks dtl1 ON dtl1.request_session_id=der1.session_idLe tableau suivant décrit les principales colonnes de sortie.
| Colonne | Description |
|---|---|
waitSID |
ID de session de la session en attente (bloquée). |
blockSID |
ID de session de la session bloquante qui détient le verrou. Utilisez cette valeur à l'étape 3. |
wait_type |
Type d'attente rencontré par la session bloquée (par exemple, LCK_M_S pour une attente de verrou partagé, LCK_M_X pour une attente de verrou exclusif). |
wait_duration_ms |
Durée d'attente de la session bloquée, en millisecondes. |
DB |
Nom de la base de données où se produit le blocage. |
waitingAssociatedEntity |
Identifiant de la ressource requise par la session en attente. Utilisez cette valeur à l'étape 2. |
waitResType |
Type de ressource verrouillée (par exemple, KEY, PAGE, OBJECT). |
waitSQL |
Instruction SQL que la session bloquée tente d'exécuter. |
blockingSQL |
Instruction SQL actuellement exécutée par la session bloquante. Renvoie NULL si la session bloquante est inactive. |
Étape 2 : Identifier la ressource verrouillée
Exécutez l'instruction suivante pour déterminer quelle table et quel index sont impliqués dans le blocage. Remplacez [$Waiting_Associate_Entity] par la valeur waitingAssociatedEntity obtenue à l'étape 1.
SELECT OBJECT_NAME(i.object_id) obj,
i.name
FROM sys.partitions p
JOIN sys.indexes i ON i.object_id=p.object_id
AND i.index_id=p.index_id
WHERE p.partition_id=[$Waiting_Associate_Entity][$Waiting_Associate_Entity]spécifie la ressource attendue par les transactions. Cette valeur correspond à la colonnewaitingAssociatedEntityrenvoyée par la requête de l'étape 1. Il s'agit de l'identifianthobt_id(ID de tas ou d'arbre B) de la table ou de l'index où se produit la contention de verrou.
Cette requête renvoie le nom de l'objet (table) et le nom de l'index, ce qui vous aide à comprendre à quelles données les transactions concurrentes tentent d'accéder.
Étape 3 : Arrêter la session bloquante
Après avoir identifié la session bloquante à partir de la colonne blockSID de l'étape 1, exécutez l'instruction KILL pour l'arrêter :
KILL <session_id>Remplacez <session_id> par la valeur blockSID. Par exemple, si blockSID est 58, exécutez KILL 58.
Avant d'arrêter une session, examinez la sortie blockingSQL de l'étape 1 pour comprendre l'action de la session bloquante. L'arrêt d'une session annule sa transaction active, ce qui peut affecter l'intégrité des données ou le comportement de l'application. N'utilisez KILL que lorsque le blocage a un impact significatif et qu'il n'est pas possible d'attendre la fin naturelle de la transaction.
Étapes suivantes
L'arrêt d'une session bloquante est une mesure d'urgence qui résout le problème immédiat mais n'empêche pas sa récurrence. Pour traiter la cause profonde, envisagez les actions suivantes :
Analysez la requête bloquante : Examinez les sorties
blockingSQLetwaitSQLde l'étape 1. Recherchez des index manquants, des analyses de tables volumineuses ou des transactions de longue durée.Optimisez les index : Les index manquants constituent l'une des causes les plus fréquentes de blocages prolongés. Assurez-vous que les colonnes fréquemment interrogées disposent d'index appropriés.
Maintenez les transactions courtes : Concevez les transactions de manière à ce qu'elles se terminent aussi rapidement que possible. Évitez de maintenir les transactions ouvertes en attendant une saisie utilisateur ou des opérations externes.
Passez en revue les niveaux d'isolation : Envisagez d'utiliser l'isolation instantanée de lecture validée (RCSI) pour réduire la contention des verrous en permettant aux lecteurs d'accéder à un instantané des données sans être bloqués par les écritures.
Pour un guide complet sur le diagnostic et la prévention des blocages, consultez Comment résoudre le problème de blocage des requêtes sur une instance ApsaraDB RDS for SQL Server ?