ApsaraDB RDS:What do I do if a deadlock occurs on an ApsaraDB RDS for SQL Server instance?

Method 1: Query SYS.SYSPROCESSES

Run the following SQL statement to poll the SYS.SYSPROCESSES view at a fixed interval:

WHILE 1 = 1
BEGIN
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
WAITFOR DELAY '[$Time]';
END;

Replace [$Time] with the polling interval. For example, use 00:00:01 to poll every second.

The following figure shows a sample output.

Key columns in the output:

In the example above, session 53 and session 56 are blocking each other, which indicates a deadlock.

Method 2: Join sys.dm_tran_locks and sys.dm_os_waiting_tasks

For more detail — including the exact SQL statements running in each blocked and blocking session — run the following query:

while 1=1
    Begin
    SELECT
    db.name DBName,
    tl.request_session_id,
    wt.blocking_session_id,
    OBJECT_NAME(p.OBJECT_ID) BlockedObjectName,
    tl.resource_type,
    h1.TEXT AS RequestingText,
    h2.TEXT AS BlockingText,
    tl.request_mode
    FROM sys.dm_tran_locks AS tl
    INNER JOIN sys.databases db ON db.database_id = tl.resource_database_id
    INNER JOIN sys.dm_os_waiting_tasks AS wt ON tl.lock_owner_address = wt.resource_address
    INNER JOIN sys.partitions AS p ON p.hobt_id = tl.resource_associated_entity_id
    INNER JOIN sys.dm_exec_connections ec1 ON ec1.session_id = tl.request_session_id
    INNER JOIN sys.dm_exec_connections ec2 ON ec2.session_id = wt.blocking_session_id
    CROSS APPLY sys.dm_exec_sql_text(ec1.most_recent_sql_handle) AS h1
    CROSS APPLY sys.dm_exec_sql_text(ec2.most_recent_sql_handle) AS h2
    waitfor delay '[$Time]'
    End

The following figure shows a sample output.

The output includes the following columns:

Method 3: Use SQL Server Profiler (SQL Server 2012 only)

If your instance runs SQL Server 2012, use SQL Server Profiler to capture deadlock events and generate a deadlock graph.

The following figure shows a deadlock graph.