Resolve blocked requests on SQL Server by terminating blocking sessions - ApsaraDB RDS

Important

This topic describes how to resolve blocked requests on an ApsaraDB RDS for SQL Server instance in emergency situations. To eliminate blocking at its root, identify and analyze the causes of blocking and develop a solution based on the analysis. In most cases, blocking is caused by missing indexes or large transactions. When indexes are missing or transactions are large, transaction execution takes longer and locks are held for extended periods. As a result, a large number of requests are blocked. For a detailed guide to root cause analysis and prevention, see How do I resolve the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance?

Overview

Blocking occurs when one session holds a lock on a resource and another session requests a conflicting lock on the same resource. The second session waits until the first session releases its lock. Unlike a deadlock, where two sessions form a circular dependency that SQL Server detects and resolves automatically, blocking is a one-directional wait. The blocked session waits until the lock holder finishes or is terminated.

Short-lived blocking is normal during concurrent workloads. However, when sessions hold locks for extended periods, a backlog of waiting requests builds up, which degrades application performance or causes timeouts.

Problem description

Requests are blocked on the ApsaraDB RDS for SQL Server instance, and application queries are timing out or running slowly.

Cause

Transactions compete for locked resources. When a session holds a lock and another session needs a conflicting lock on the same resource, the second session is blocked until the lock is released.

Solution

Follow these three steps to identify and terminate the blocking session.

Step 1: Identify the blocking and waiting sessions

Execute the following statement to retrieve information about the current locks and blocked requests. This query joins several SQL Server Dynamic Management Views (DMVs) to show which sessions are blocked, which sessions are causing the blocking, and the SQL statements each session is running.

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_id

The following table describes the key output columns.

Column	Description
`waitSID`	Session ID of the waiting (blocked) session.
`blockSID`	Session ID of the blocking session that holds the lock. Use this value in Step 3.
`wait_type`	Type of wait the blocked session is experiencing (for example, `LCK_M_S` for a shared lock wait, `LCK_M_X` for an exclusive lock wait).
`wait_duration_ms`	How long the blocked session has been waiting, in milliseconds.
`DB`	Name of the database where the blocking occurs.
`waitingAssociatedEntity`	Resource identifier that the waiting session needs. Use this value in Step 2.
`waitResType`	Type of locked resource (for example, `KEY`, `PAGE`, `OBJECT`).
`waitSQL`	SQL statement the blocked session is trying to execute.
`blockingSQL`	SQL statement the blocking session is currently executing. Returns NULL if the blocking session is idle.

Step 2: Identify the locked resource

Execute the following statement to determine which table and index are involved in the blocking. Replace [$Waiting_Associate_Entity] with the waitingAssociatedEntity value from the Step 1 results.

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] specifies the resource that the transactions are waiting for. This value corresponds to the waitingAssociatedEntity column returned by the query in Step 1. It is the hobt_id (heap or B-tree ID) of the table or index where the lock contention is occurring.

This query returns the object name (table) and the index name, which helps you understand what data the competing transactions are trying to access.

Step 3: Terminate the blocking session

After you identify the blocking session from the blockSID column in Step 1, execute the KILL statement to terminate it:

KILL <session_id>

Replace <session_id> with the blockSID value. For example, if blockSID is 58, execute KILL 58.

Important

Before you terminate a session, review the blockingSQL output from Step 1 to understand what the blocking session is doing. Terminating a session rolls back its active transaction, which may affect data integrity or application behavior. Use KILL only when the blocking is causing a significant impact and cannot wait for the transaction to complete naturally.

What to do next

Terminating a blocking session is an emergency measure that resolves the immediate problem but does not prevent recurrence. To address the root cause, consider the following:

Analyze the blocking query: Review the blockingSQL and waitSQL output from Step 1. Look for missing indexes, large table scans, or long-running transactions.
Optimize indexes: Missing indexes are one of the most common causes of prolonged blocking. Ensure that frequently queried columns have appropriate indexes.
Keep transactions short: Design transactions to complete as quickly as possible. Avoid holding transactions open while waiting for user input or external operations.
Review isolation levels: Consider using Read Committed Snapshot Isolation (RCSI) to reduce lock contention by allowing readers to access a snapshot of the data without being blocked by writers.

For a comprehensive guide to diagnosing and preventing blocking, see How do I resolve the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance?