查找sqlserver查询死锁源头的方法 sqlserver死锁监控

下面是关于“查找 SQL Server 查询死锁源头的方法 SQL Server 死锁监控”完整攻略，包括以下步骤：

前置条件

在进行 SQL Server 死锁监控之前，需要先了解四个基本概念：

1. 锁（Lock）：在 SQL Server 中，锁是防止并发访问进行数据访问的机制。

2. 事务（Transaction）：事务是执行一系列相关的操作并形成一个逻辑单元，且该逻辑单元要么全部执行，要么全部取消。

3. 互斥性（Mutual Exclusion）：在同一时间只有一个事务可以持有一个锁。

4. 死锁（Deadlock）：死锁是指当两个或多个事务都互相持有彼此所需的资源时，它们将会陷入一种懊恼的状态，不能再向前或向后移动。

步骤

接下来，介绍以下针对 SQL Server 数据库死锁问题进行监控和查找的完整攻略：

1. 启用 SQL Server Profiler 进行监控。

   在 SQL Server Profiler 中，选择 "Tuning" 模板并启动此模板。监控过程中启用 "Deadlock graph" 选项，以显示死锁图。"Deadlock graph" 选项将显示导致锁死的事务，并提供详细信息，例如死锁的事务、锁类型、等待时间等。监控结束后，可以使用 "Show Deadlock XML" 选项查看死锁的 XML 信息。

   以下是一个 SQL Server Profiler 监控死锁的示例：

   sql
USE AdventureWorks2012;
GO
SET NOCOUNT ON;
BEGIN TRAN
UPDATE Production.ProductInventory
SET Quantity = Quantity - 10
WHERE ProductID = 980;
-- Wait for a delay to simulate another query attempting to read
-- uncommitted data.
WAITFOR DELAY '00:00:03';
-- Attempt to read uncommitted data. The query is blocked
-- because of locking conflicts.
SELECT ProductID, Quantity
FROM Production.ProductInventory WITH (NOLOCK)
WHERE ProductID = 980;
ROLLBACK TRAN;

2. 使用 "Activity Monitor" 工具进行查找。

   在 SSMS 中，选择 "Activity Monitor" 工具。选择 "Processes" 选项卡，并按 SQL Server 数据库实例名称。在 "Processes" 选项卡中，将鼠标指针悬停在 "Block By" 列的 "0" 值上。这将显示一个下拉框，其中包含所有正在阻塞的进程以及阻塞它们的进程。可以单击每个进程来查看详细信息。另外，在工具栏中选择 "Deadlock Graph" 选项卡，以查看死锁图。

   以下是 Activity Monitor 工具查找死锁的示例：

   sql
CREATE PROCEDURE DeadlockTrace
AS
BEGIN
SET NOCOUNT ON;
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED;
DECLARE @XMLData XML;
SET @XMLData =(SELECT
-- Filter for date range, optional
CAST(record AS XML)
FROM sys.fn_trace_gettable(CONVERT(VARCHAR(MAX)), 0)
WHERE TextData LIKE '%deadlock-list%'
AND StartTime >= DATEADD(MINUTE,-60, GETDATE()))
SELECT
DL.eventDate, DL.DeadlockGraph
FROM (
SELECT
CONVERT(DATETIME, LEFT(TXT.text, 23), 121) AS eventDate, STUFF(
CAST(
( SELECT CAST(TXT2.text AS VARCHAR(MAX))
FROM sys.dm_exec_connections AS C
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) AS TXT2
WHERE C.session_id = DEADLOCK.session_id
FOR XML PATH('treedata'), TYPE
)AS VARCHAR(MAX)
), 1, 2, '' ) + '' AS DeadlockGraph
FROM sys.dm_exec_connections AS C
CROSS APPLY sys.dm_exec_sql_text(C.most_recent_sql_handle) AS TXT
CROSS APPLY @XMLData.nodes('/Root/Deadlock')AS DEADLOCK(Deadlock)
WHERE TXT.text LIKE '%process id%'
AND C.session_id = DEADLOCK.deadlock.value('@victimProcessID', 'INT')
) AS DL
ORDER BY DL.eventDate DESC
END

总结

在进行 SQL Server 死锁监控时，可以使用 SQL Server Profiler 和 Activity Monitor 工具。使用这些工具可以轻松地查找导致死锁的事务并提供有关死锁的详细信息和死锁图。