:Blocking occurs in RDS SQL Server

Problem description

A block occurs in the apsaradb for RDS SQL Server.

Cause

Inter-transaction lock resource contention leads to blocking.

Solution

1. When a lock occurs in a RDS SQL Server, run the following script to obtain the lock information:

   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

2. Run the following command to obtain the lock resource information:

   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

   Note:[$Waiting_Associate_Entity] is the value of the pending resource parameter.

3. Run the kill command to terminate the session from which the source instance is blocked.

   Note

   Note: This step is a quick solution. If you need to solve the problem fundamentally, you need to analyze the blocked information, confirm the cause of the blocking, and formulate a solution to the problem based on the reason. A common cause is the lack of indexes or large transactions, which leads to long transaction execution time and increase of lock holding time, resulting in a large blocking situation.

Scope

• ApsaraDB RDS for SQL Server