使用 SQL Server Profiler 分析死锁Analyze Deadlocks with SQL Server Profiler

适用对象:yesSQL ServeryesAzure SQL 数据库noAzure SQL 数据仓库no并行数据仓库APPLIES TO: yesSQL Server noAzure SQL Database noAzure SQL Data Warehouse noParallel Data Warehouse

使用 SQL Server ProfilerSQL Server Profiler 确定死锁的原因。Use SQL Server ProfilerSQL Server Profiler to identify the cause of a deadlock. 当 SQL Server 中某组资源的两个或多个线程或进程之间存在循环的依赖关系时,将会发生死锁。A deadlock occurs when there is a cyclic dependency between two or more threads, or processes, for some set of resources within SQL Server. 使用 SQL Server ProfilerSQL Server Profiler,可以创建记录、重播和显示死锁事件的跟踪以进行分析。Using SQL Server ProfilerSQL Server Profiler, you can create a trace that records, replays, and displays deadlock events for analysis.

若要跟踪死锁事件,请将 Deadlock graph 事件类添加到跟踪。To trace deadlock events, add the Deadlock graph event class to a trace. 此事件类会在跟踪中的 TextData 数据列中填充有关死锁中涉及的进程和对象的 XML 数据。This event class populates the TextData data column in the trace with XML data about the process and objects that are involved in the deadlock. SQL Server ProfilerSQL Server Profiler 可将 XML 文档提取到死锁 XML (.xdl) 文件,你稍后可在 SQL Server Management Studio 中查看该文件。 can extract the XML document to a deadlock XML (.xdl) file which you can view later in SQL Server Management Studio. 您可以配置 SQL Server ProfilerSQL Server Profiler ,将 Deadlock graph 事件提取到一个包含了所有 Deadlock graph 事件的文件中,或提取到多个单独的文件中。You can configure SQL Server ProfilerSQL Server Profiler to extract Deadlock graph events to a single file that contains all Deadlock graph events, or to separate files. 可以通过下列任一方法进行提取:This extraction can be done in any of the following ways:

  • 在配置跟踪时,使用 “事件提取设置” 选项卡。请注意,只有在 “事件选择” 选项卡上选择了 Deadlock graph 事件,才会出现此选项卡。At trace configuration time, using the Events Extraction Settings tab. Note that this tab does not appear until you select the Deadlock graph event on the Events Selection tab.

  • 使用 “文件” 菜单上的 “提取 SQL Server 事件” 选项。Using the Extract SQL Server Events option on the File menu.

  • 通过右键单击特定事件并选择“提取事件数据”,也可以提取并保存各个事件。Individual events can also be extracted and saved by right-clicking a specific event and choosing Extract Event Data.

死锁图形Deadlock Graphs

SQL Server ProfilerSQL Server Profiler SQL Server Management StudioSQL Server Management Studio 使用死锁等待图形描述死锁。 and SQL Server Management StudioSQL Server Management Studio use a deadlock wait-for graph to describe a deadlock. 此死锁等待图形中包含进程节点、资源节点以及表示进程和资源之间关系的边。The deadlock wait-for graph contains process nodes, resource nodes, and edges representing the relationships between the processes and the resources. 等待图形的组件的定义如下表所示:The components of wait-for graphs are defined in the following table:

进程节点Process node
执行任务的线程。例如,INSERT、UPDATE 或 DELETE。A thread that performs a task; for example, INSERT, UPDATE, or DELETE.

资源节点Resource node
数据库对象。例如,表、索引或行。A database object; for example, a table, index, or row.

Edge
进程和资源之间的关系。A relationship between a process and a resource. 当进程等待资源时,将出现 request 边。A request edge occurs when a process waits for a resource. 当资源等待进程时,将出现 owner 边。An owner edge occurs when a resource waits for a process. 边说明中包括了锁模式。The lock mode is included in the edge description. 例如, “模式: X”For example, Mode: X.

死锁进程节点Deadlock Process Node

在等待图形中,进程节点包含有关进程的信息。In a wait-for graph, the process node contains information about the process. 下表介绍了进程的组件。The following table explains the components of a process.

组件Component 定义Definition
服务器进程 IDServer process Id 服务器进程标识符 (SPID),即服务器给拥有锁的进程分配的标识符。Server process identifier (SPID), a server assigned identifier for the process owning the lock.
服务器批 IDServer batch Id 服务器批标识符 (SBID)。Server batch identifier (SBID).
执行上下文 IDExecution context Id 执行上下文标识符 (ECID)。Execution context identifier (ECID). 与指定 SPID 相关联的给定线程的执行上下文 ID。The execution context ID of a given thread associated with a specific SPID.

ECID = {0, 1, 2, 3, ...n},其中 0 始终表示主或父线程,并且 {1, 2, 3, ...n} 表示子线程。ECID = {0,1,2,3, ...n}, where 0 always represents the main or parent thread, and {1,2,3, ...n} represent the subthreads.
死锁优先级Deadlock priority 进程的死锁优先级Deadlock priority for the process. 有关可能值的详细信息,请参阅 SET DEADLOCK_PRIORITY (Transact-SQL)For more information about possible values, see SET DEADLOCK_PRIORITY (Transact-SQL).
已用日志Log Used 进程所使用的日志空间量。Amount of log space used by the process.
所有者 IDOwner Id 正在使用事务并且当前正在等待锁的进程的事务 ID。Transaction ID for the processes which are using transactions and currently waiting on a lock.
事务描述符Transaction descriptor 指向描述事务状态的事务描述符的指针。Pointer to the transaction descriptor that describes the state of the transaction.
输入缓冲区Input buffer 当前进程的输入缓冲区。定义了事件的类型和正在执行的语句。Input buffer of the current process, defines the type of event and the statement being executed. 可能的值包括:Possible values include:

语言Language

RPCRPC

None
Statement 语句类型。Type of statement. 可能的值有:Possible values are:

NOPNOP

SELECTSELECT

UPDATEUPDATE

InsertINSERT

DELETEDELETE

UnknownUnknown

死锁资源节点Deadlock Resource Node

在死锁中,两个进程都在等待对方占用的资源。In a deadlock, two processes are each waiting for a resource held by the other process. 在死锁图形中,资源显示为资源节点。In a deadlock graph, the resources are displayed as resource nodes.