Solution to the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance - ApsaraDB RDS

Important

This topic describes how to resolve the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance in emergency situations. To eliminate the issue, we recommend that you identify and analyze the causes of the block and formulate a solution based on the analysis result. In most cases, the issue is caused by missing indexes or large transactions. If indexes are missing or transactions are large, transactions are executed for a long period of time, and lock hold is prolonged. As a result, a large number of requests are blocked. For more information, see How do I resolve the issue that requests are blocked on an ApsaraDB RDS for SQL Server instance?

Problem description

Requests are blocked on the RDS instance.

Cause

Transactions compete for locked resources.

Solution

Execute the following statement to obtain information about the lock and block:

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

Execute the following statement to obtain information about the locked resource:

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]

Note

[$Waiting_Associate_Entity] specifies the resource that the transactions are waiting for.

Run the kill command to terminate the blocking session.