RDS SQL Server出現死結時的處理方法 - ApsaraDB RDS

問題描述

當應用程式頻繁讀寫某個表或者資源時，容易出現死結現象。出現死結時，SQL Server會選擇終止其中一個事務，並且向發起該事務的用戶端發送類似如下的錯誤資訊：

Error Message：Msg 1205, Level 13, State 47, Line 1Transaction (Process ID 53) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

解決方案

使用用戶端串連執行個體。具體操作，請參見串連SQL Server執行個體。

監控相關視圖。

執行如下SQL語句，迴圈監控SYS.SYSPROCESSES。
```
WHILE 1 = 1
BEGIN
SELECT * FROM SYS.SYSPROCESSES WHERE BLOCKED <> 0;
WAITFOR DELAY '[$Time]';
END;
```
說明
您可以自訂[$Time]迴圈間隔時間，此處以00:00:01為例。

多次迭代結果中，SPID 53 等待鎖類型為 LCK_M_X，SPID 56 等待鎖類型為 LCK_M_S，SPID 53 的 blocked 為 56、SPID 56 的 blocked 為 53（互相阻塞），兩者的 waittime 隨每次迴圈持續遞增。

說明
監控結果中blocked列的值為阻塞該會話的阻塞源會話ID，waitresource為被阻塞的會話等待的資源。從上述結果可以看到，spid 53和spid 56相互阻塞，形成了死結。

執行如下SQL語句，迴圈監控sys.dm_tran_locks和sys.dm_os_waiting_tasks等視圖。

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

查詢結果顯示兩個會話互相阻塞形成死結：request_session_id 56 被 blocking_session_id 62 阻塞，操作對象為 Tbl2，request_mode 為 X；同時會話 62 被會話 56 阻塞，操作對象為 Tbl1，request_mode 為 S。兩個會話的 RequestingText 均為 INSERT 語句，形成雙向阻塞的死結關係。

返回參數說明如下：

參數	說明
`DBName`	request_session_id操作的資料庫。
`request_session_id`	當前請求的會話ID，即被阻塞的會話。
`blocking_session_id`	阻塞源會話ID。
`BlockedObjectName`	被阻塞的會話操作的對象。
`resource_type`	等待的資源類型。
`RequestingText`	當前會話執行的語句，即被阻塞的語句。
`BlockingText`	阻塞源會話執行的語句。
`request_mode`	當前會話請求的鎖模式。

如果您的執行個體版本是RDS SQL Server 2012，還可以使用SQL Server Profiler來監控和抓取死結圖譜：

在 Trace Properties 對話方塊的 Events Selection 標籤頁中，展開 Locks 分類，勾選 Deadlock graph 和 Lock:Deadlock 事件。

抓取的死結圖譜：

SQL Server Profiler 跟蹤結果顯示兩條 Lock:Deadlock Chain 事件：SPID 52 在資料庫 test_shrink 上持有 5-X（排他）鎖（事務 1374753），SPID 55 持有 3-S（共用）鎖（事務 1374762），隨後觸發 Lock:Deadlock 事件。死結圖譜顯示 SPID 52 與 SPID 55 分別鎖定 test_shrink.dbo.Th12 和 test_shrink.dbo.Th11 上索引 i 的 Key Lock 資源，兩個進程互相請求對方持有的鎖（Request Mode 為 X/S），形成環形等待，最終觸發死結。

調優建議

可以參考以下步驟，進行調優。

關閉阻塞源會話，可以協助快速解除阻塞。
查看是否有長時間未提交的事務，及時提交事務。
如果有S鎖導致的死結，並且您的應用允許髒讀，可以使用WITH (NOLOCK)查詢提示。例如在查詢中寫入SELECT * FROM table WITH (NOLOCK);使查詢在執行時避免申請鎖，從而避免死結。
檢查應用程式邏輯，按順序訪問某個資源。

ApsaraDB RDS：RDS SQL Server出現死結時的處理方法

問題描述

解決方案

調優建議

相關操作