sql server死锁_了解SQL Server死锁图的图形表示

sql server死锁

介绍 (Introduction)

If you are reading this I am sure you already know what a deadlock is, but just in case you are new to SQL, a deadlock is when 2 queries are blocking each other in such a way that neither of the two can be completed.

如果您正在阅读本文，我确定您已经知道什么是死锁，但是以防万一，您不熟悉SQL时，死锁就是两个查询相互阻塞而无法完成两个查询的情况。

For example, 2 queries are holding shared locks on a table, but both queries need to escalate their locks to exclusive locks to perform an update. Since neither query can proceed, intervention is required. SQL Server routinely performs a check for deadlocks and will choose to kill one of the processes to allow the other to proceed. This killed process is known as the deadlock victim.

例如，两个查询在一个表上持有共享锁，但是两个查询都需要将其锁升级为互斥锁才能执行更新。 由于无法进行任何查询，因此需要干预。 SQL Server常规执行死锁检查，并将选择终止其中一个进程以允许其他进程继续进行。 这个被杀死的过程称为死锁受害者。

There are multiple ways to go about troubleshooting deadlocks which include:

解决死锁的方法有多种，包括：

• Trace flags 1222, 1204

  跟踪标志1222、1204

• Profiler (trace events)

  探查器（跟踪事件）

• Extended events

  扩展事件

In this particular article I will only focus on the Deadlock Graph or the file in .rdl format (graphical representation) , what you can know by looking at the graph image, this is handy if you just want to get the basic information quickly. Generally, however looking at the XML version of the file, reveals more information which can make troubleshooting deadlocks a lot easier. I will cover that in my next article: Understanding the deadlock graph part 2: The XML description.

在这篇特别的文章中，我将只关注死锁图或.rdl格式的文件（图形表示），通过查看图图像可以知道什么，如果您只是想快速获取基本信息，这将非常方便。 通常，但是查看文件的XML版本会显示更多信息，这些信息可以使对死锁的疑难解答变得更加容易。 我将在下一篇文章中介绍：理解死锁图第2部分：XML描述。

造成僵局 (Creating a deadlock)

For the purpose of this demonstration I will create a very simple deadlock in the AdvetureWorks2012 database to allow us to see what we can learn from looking at the deadlock graph.

出于此演示的目的，我将在AdvetureWorks2012数据库中创建一个非常简单的死锁，使我们能够看到从死锁图中学到的知识。

I will execute 2 queries simultaneously:

我将同时执行2个查询：

Query 1

查询1

 
 BEGIN TRAN
 
UPDATE Person.Address set StateProvinceID = 78 where AddressID = 1
WAITFOR DELAY '00:00:05'
UPDATE Person.Person  set FirstName = 'John' where BusinessEntityID = 1
 
COMMIT TRAN
 

Query 2

查询2

 
 BEGIN TRAN
 
UPDATE Person.Person  SET FirstName = 'John' WHERE BusinessEntityID = 1
WAITFOR DELAY '00:00:05'
UPDATE Person.Address SET StateProvinceID = 78 WHERE AddressID = 1
 
COMMIT TRAN
 

These queries are fairly simple, as you can see query 1 tries to update Person.Address and then waits for 5 seconds, and then tries to update Person.Person. Query 2 attempts to do the same but in reverse order.

这些查询非常简单，如您所见，查询1尝试更新Person.Address，然后等待5秒钟，然后尝试更新Person.Person。 查询2尝试执行相同的操作，但顺序相反。

Since neither query can proceed, this results in a deadlock and the deadlock message will be displayed for one of them, which means that that query was terminated and rolled back (deadlock victim) and the other query will complete successfully.

由于无法进行任何查询，因此将导致死锁，并且其中一个将显示死锁消息，这意味着该查询已终止并回滚（死锁受害者），而另一个查询将成功完成。

Query 1 Results

查询1个结果

(1 row(s) affected)

（影响1行）

Msg 1205, Level 13, S