Solution to the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance-ApsaraDB RDS(RDS)-阿里云帮助中心

Important

This is an emergency procedure for resolving blocked requests on an ApsaraDB RDS for SQL Server instance. To eliminate blocking at its root, identify and analyze the causes and develop a targeted solution. In most cases, blocking results from missing indexes or large transactions, which increase transaction execution time and lock duration. 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. Unlike a deadlock, where SQL Server detects and resolves a circular dependency 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

Run the following statement to retrieve current lock and blocking information. The query joins several Dynamic Management Views (DMVs) to show which sessions are blocked, which sessions hold the locks, and the SQL statements involved.

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] is the waitingAssociatedEntity value from Step 1. It represents the hobt_id (heap or B-tree ID) of the table or index where lock contention occurs.

The result shows the table name and index name, helping you identify what data the competing transactions are accessing.

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 terminating a session, review the blockingSQL output from Step 1 to understand what the session is doing. Terminating a session rolls back its active transaction, which may affect data integrity or application behavior. Use KILL only when blocking causes significant impact and the transaction cannot complete naturally.

What to do next

Terminating a blocking session resolves the immediate problem but does not prevent recurrence. To address root causes:

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?