:How to check the row Lock wait in MSSQL

Overview

This article describes how to view the row Lock wait in MSSQL.

Detail

Alibaba Cloud reminds you that:

• Before you perform operations that may cause risks, such as modifying instance configurations or data, we recommend that you check the disaster recovery and fault tolerance capabilities of the instances to ensure data security.
• You can modify the configurations and data of instances including but not limited to Elastic Compute Service (ECS) and Relational Database Service (RDS) instances. Before the modification, we recommend that you create snapshots or enable RDS log backup.
• If you have authorized or submitted security information such as the logon account and password in the Alibaba Cloud Management console, we recommend that you modify such information in a timely manner.

You can use the following SQL statement to view the row Lock wait status of the current instance.

use master;
go
WITH CTE_SID ( BSID, SID, sql_handle )        
    AS ( SELECT [Blocking_Session_ID],
                        [Session_ID] ,
                        sql_handle
               FROM     sys.dm_exec_requests
               WHERE    [Blocking_Session_ID] <> 0
               UNION ALL
               SELECT   A.[ Blocking_Session_ID] ,
                        A.[ Session_ID] ,
                        A.sql_handle
               FROM     sys.dm_exec_requests A
                        JOIN CTE_SID B ON A.[ Session_ID] = B.BSID
             )
    SELECT C.BSID,
            C.SID ,
            S.login_name ,
            S.host_name ,
            S.status ,
            S.cpu_time ,
            S.memory_usage ,
            S.last_request_start_time ,
            S.last_request_end_time ,
            S.logical_reads ,
            S.row_count ,
            q.text
    FROM CTE_SID C
            JOIN sys.dm_exec_sessions S ON C.sid = s.[ Session_ID]
            CROSS APPLY sys.dm_exec_sql_text(C.sql_handle) Q
    ORDER BY sid

Note:

• If the row lock is waiting or blocked, the results are listed in the execution result.
• BSID is the session id of the lock.
• SID is the session id of the session waiting for the lock.

Application scope

• ApsaraDB RDS for SQL Server