数据库内核月报－ 2017/05 - MSSQL · 应用案例 · 构建死锁自动收集系统 - 《数据库内核月报》

摘要
死锁自动收集系统需求分析
Service Broker和Event Notification简介
- Service Broker简介
- Event Notification简介
死锁收集系统架构图
Service Broker配置
- Deadlock Client Server
- Deadlock Center Server
Event Notification配置
模拟死锁
用户查询死锁信息
踩过的坑
福利发放
最后总结

摘要

这篇文章介绍SQL Server的一个典型的应用案例，即如何利用Event Notification与Service Broker技术相结合来实现死锁信息自动收集系统。通过这个系统，我们可以全面把控SQL Server数据库环境中所有实例上发生的死锁详细信息，供我们后期分析和解决死锁场景。

死锁自动收集系统需求分析

当 SQL Server 中某组资源的两个或多个线程或进程之间存在循环的依赖关系时，但因互相申请被其他进程所占用，而不会释放的资源处于的一种永久等待状态，将会发生死锁。SQL Server服务自动死锁检查进程默认每5分钟跑一次，当死锁发生时，会选择一个代价较小的进程做为死锁牺牲品，以此来避免死锁导致更大范围的影响。被选择做为死锁牺牲品的进程会报告如下错误：


1. Msg 1205, Level 13, State 51, Line 8
2. Transaction (Process ID 54) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

如果进程间发生了死锁，对于用户业务系统，乃至整个SQL Server服务健康状况影响很大，轻者系统反应缓慢，服务假死；重者服务挂起，拒绝请求。那么，我们有没有一种方法可以完全自动、无人工干预的方式异步收集SQL Server系统死锁信息并远程保留死锁相关信息呢？这些信息包括但不仅限于：

死锁发生在哪些进程之间
各个进程执行的语句块是什么？死锁时，各个进程在执行哪条语句？
死锁的资源是什么？死锁发生在哪个数据库？哪张表？哪个数据页？哪个索引上？
死锁发生的具体时间点，包含语句块开始时间、语句执行时间等
用户进程使用的登录用户是什么？客户端驱动是什么？ …… 如此的无人值守的自动死锁收集系统，就是我们今天要介绍的应用案例分享：利用SQL Server的Event Notification与Service Broker建立自动死锁信息收集系统。

Service Broker和Event Notification简介

在死锁自动收集系统介绍开始之前，先简要介绍下SQL Server Service Broker和Event Notification技术。

Service Broker简介

Service Broker是微软至SQL Server 2005开始集成到数据库引擎中的消息通讯组件，为 SQL Server提供队列和可靠的消息传递的能力，可以用来构建基于异步消息通讯为基础的应用程序。Service Broker既可用于单个 SQL Server 实例的应用程序，也可用于在多个实例间进行消息分发工作的应用程序。Service Broker使用TCP/IP端口在实例间交换消息，所包含的功能有助于防止未经授权的网络访问，并可以对通过网络发送的消息进行加密，以此来保证数据安全性。多实例之间使用Service Broker进行异步消息通讯的结构图如下所示（图片来自微软的官方文档）：

Event Notification简介

Event Notification的中文名称叫事件通知，执行事件通知可对各种Transact-SQL数据定义语言(DDL)语句和SQL跟踪事件做出响应，采取的响应方式是将这些事件的相关信息发送到 Service Broker 服务。事件通知可以用来执行以下操作：

记录和检索发生在数据库上的更改或活动。
执行操作以异步方式而不是同步方式响应事件。

可以将事件通知用作替代DDL 触发器和SQL跟踪的编程方法。事件通知的信息媒介是以xml数据类型的信息传递给Service Broker服务，它提供了有关事件的发生时间、受影响的数据库对象、涉及的 Transact-SQL 批处理语句等详细信息。对于SQL Server死锁而言，可以使用Event Notification来跟踪死锁事件，来获取DEADLOCK_GRAPH XML信息，然后通过异步消息组件Service Broker发送到远端的Deadlock Center上的Service Broker队列，完成死锁信息收集到死锁中央服务。

死锁收集系统架构图

在介绍完Service Broker和Event Notification以后，我们来看看死锁手机系统的整体架构图。在这个系统中，存在两种类型角色：我们定义为死锁客户端（Deadlock Client）和死锁中央服务（Deadlock Center）。死锁客户端发生死锁后，首先会将Deadlock Graph XML通过Service Broker发送给死锁中央服务，死锁中央服务获取到Service Broker消息以后，解析这个XML就可以拿到客户端的死锁相关信息，最后存放到本地日志表中，供终端客户查询和分析使用。最终的死锁收集系统架构图如下所示：

详细的死锁信息收集过程介绍如下：死锁客户端通过本地SQL Server的Event Notification捕获发生在该实例上的Deadlock事件，并在死锁发生以后将Deadlock Graph XML数据存放到Event Notification绑定的队列中，然后通过绑定在该队列上的存储过程自动触发将Deadlock Graph XML通过Service Broker异步消息通讯的方式发送到死锁中央服务。中央服务在接收到Service Broker消息以后，首先放入Deadlock Center Service Broker队列中，该队列绑定了消息自动处理存储过程，用来解析Deadlock Graph XML信息，并将死锁相关的详细信息存入到Deadlock Center的Log Table中。最后，终端用户可以直接对Log Table来查询和分析所有Deadlock Client上发生的死锁信息。通过这系列的过程，最终达到了死锁信息的自动远程存储、收集，以提供后期死锁场景还原和复盘，达到死锁信息可追溯，及时监控，及时发现的目的。

Service Broker配置

系统架构设计完毕后，接下来是系统的配置和搭建过程，首先看看Service Broker的配置。这个配置还是相对比较繁琐的，包含了以下步骤：

创建Service Broker数据库（假设数据库名为DDLCenter）并开启Service Broker选项
创建Service Broker队列的激活存储过程和相关表对象
创建Master数据库下的Master Key
创建传输层本地和远程证书
创建基于证书的用户登录
创建Service Broker端口并授权用户连接
创建DDLCenter数据库下的Master Key
创建会话层本地及远程证书
创建Service Broker组件所需要的对象，包括：Message Type、Contact、Queue、Service、Remote Service Binding、Route

Deadlock Client Server

以下的配置请在Deadlock Client SQL Server实例上操作。

创建DDLCenter数据库并开启Service Broker选项


1. -- Run script on client server to gather deadlock graph xml
2. USE master
3. GO
4. -- Create Database
5. IF DB_ID('DDLCenter') IS NULL
6. CREATE DATABASE [DDLCenter];
7. GO
8. -- Change datbase to simple recovery model
9. ALTER DATABASE [DDLCenter] SET RECOVERY SIMPLE WITH NO_WAIT
10. GO
11. -- Enable Service Broker
12. ALTER DATABASE [DDLCenter] SET ENABLE_BROKER,TRUSTWORTHY ON
13. GO
14. -- Change database Owner to sa
15. ALTER AUTHORIZATION ON DATABASE::DDLCenter TO [sa]
16. GO

三个表和两个存储过程

表[DDLCollector].[Deadlock_Traced_Records]：从Event Notification队里接收的消息会记录到该表中。表[DDLCollector].[Send_Records]：Deadlock Client成功发送Service Broker消息记录表[DDLCollector].[Error_Records]：记录发生异常情况时的信息。存储过程[DDLCollector].[UP_ProcessDeadlockEventMsg]：Deadlock Client绑定到队里的激活存储过程，一旦队列中有消息进入，这个存储过程会被自动调用。存储过程[DDLCollector].[UP_SendDeadlockMsg]：Deadlock Client发送异步消息给Deadlock Center，这个存储过程会被上面的激活存储过程调用。


1. -- Run on Client Instance
2. USE [DDLCenter]
3. GO
4. -- Create Schema
5. IF NOT EXISTS(
6. SELECT TOP 1 *
7. FROM sys.schemas
8. WHERE name = 'DDLCollector'
9. )
10. BEGIN
11. EXEC('CREATE SCHEMA DDLCollector');
12. END
13. GO

15. -- Create table to log Traced Deadlock Records
16. IF OBJECT_ID('DDLCollector.Deadlock_Traced_Records', 'U') IS NOT NULL
17. DROP TABLE [DDLCollector].[Deadlock_Traced_Records]
18. GO

20. CREATE TABLE [DDLCollector].[Deadlock_Traced_Records](
21. [RowId] [BIGINT] IDENTITY(1,1) NOT NULL,
22. [Processed_Msg] [xml] NULL,
23. [Processed_Msg_CheckSum] INT,
24. [Record_Time] [datetime] NOT NULL
25. CONSTRAINT DF_Deadlock_Traced_Records_Record_Time DEFAULT(GETDATE()),
26. CONSTRAINT PK_Deadlock_Traced_Records_RowId PRIMARY KEY
27. (RowId ASC)
28. ) ON [PRIMARY]
29. GO

31. -- Create table to record deadlock graph xml sent successfully log
32. IF OBJECT_ID('DDLCollector.Send_Records', 'U') IS NOT NULL
33. DROP TABLE [DDLCollector].[Send_Records]
34. GO

36. CREATE TABLE [DDLCollector].[Send_Records](
37. [RowId] [BIGINT] IDENTITY(1,1) NOT NULL,
38. [Send_Msg] [xml] NULL,
39. [Send_Msg_CheckSum] INT,
40. [Record_Time] [datetime] NOT NULL
41. CONSTRAINT DF_Send_Records_Record_Time DEFAULT(GETDATE()),
42. CONSTRAINT PK_Send_Records_RowId PRIMARY KEY
43. (RowId ASC)
44. ) ON [PRIMARY]
45. GO

47. -- Create table to record error info when exception occurs
48. IF OBJECT_ID('DDLCollector.Error_Records', 'U') IS NOT NULL
49. DROP TABLE [DDLCollector].[Error_Records]
50. GO

52. CREATE TABLE [DDLCollector].[Error_Records](
53. [RowId] [int] IDENTITY(1,1) NOT NULL,
54. [Msg_Body] [xml] NULL,
55. [Conversation_handle] [uniqueidentifier] NULL,
56. [Message_Type] SYSNAME NULL,
57. [Service_Name] SYSNAME NULL,
58. [Contact_Name] SYSNAME NULL,
59. [Record_Time] [datetime] NOT NULL
60. CONSTRAINT DF_Error_Records_Record_Time DEFAULT(GETDATE()),
61. [Error_Details] [nvarchar](4000) NULL,
62. CONSTRAINT PK_Error_Records_RowId PRIMARY KEY
63. (RowId ASC)
64. ) ON [PRIMARY]
65. GO

68. USE [DDLCenter]
69. GO

71. -- Create Store Procedure to Send Deadlock Graph xml to Center Server
72. IF OBJECT_ID('DDLCollector.UP_SendDeadlockMsg', 'P') IS NOT NULL
73. DROP PROC [DDLCollector].[UP_SendDeadlockMsg]
74. GO

76. CREATE PROCEDURE [DDLCollector].[UP_SendDeadlockMsg](
77. @DeadlockMsg XML
78. )
79. AS
80. BEGIN
81. SET NOCOUNT ON;

83. DECLARE
84. @handle UNIQUEIDENTIFIER
85. ,@Proc_Name SYSNAME
86. ,@Error_Details VARCHAR(2000)
87. ;

89. -- get the store procedure name
90. SELECT
91. @Proc_Name = ISNULL(QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
92. + '.'
93. + QUOTENAME(OBJECT_NAME(@@PROCID)),'')
94. FROM sys.procedures
95. WHERE OBJECT_ID = @@PROCID
96. ;

98. BEGIN TRY

100. -- Begin Dialog
101. BEGIN DIALOG CONVERSATION @handle
102. FROM SERVICE [http://soa/deadlock/service/ClientService]
103. TO Service 'http://soa/deadlock/service/CenterService'
104. ON CONTRACT [http://soa/deadlock/contract/CheckContract]
105. ;

107. -- Send deadlock graph xml as the message to Center Server
108. SEND ON CONVERSATION @handle
109. MESSAGE TYPE [http://soa/deadlock/MsgType/Request] (@DeadlockMsg);

111. -- Log it successfully
112. INSERT INTO [DDLCollector].[Send_Records]([Send_Msg], [Send_Msg_CheckSum])
113. VALUES( @DeadlockMsg, CHECKSUM(CAST(@DeadlockMsg as NVARCHAR(MAX))))
114. END TRY
115. BEGIN CATCH

117. -- Record the error info when exception occurs
118. SET   @Error_Details=
119. ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) +
120. ' Error Message : ' + ERROR_MESSAGE() +
121. ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) +
122. ' Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) +
123. ' Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) +
124. ' Exception Proc: ' + @Proc_Name
125. ;

127. -- record into table
128. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
129. VALUES(@DeadlockMsg, @handle, 'http://soa/deadlock/MsgType/Request', 'http://soa/deadlock/service/ClientService', 'http://soa/deadlock/contract/CheckContract', @Error_Details);

131. END CATCH
132. END
133. GO

135. -- Create Store Procedure for Queue: when extend event notification queue message
136. -- this store procedure will be called.
137. IF OBJECT_ID('DDLCollector.UP_ProcessDeadlockEventMsg', 'P') IS NOT NULL
138. DROP PROC [DDLCollector].[UP_ProcessDeadlockEventMsg]
139. GO

141. CREATE PROCEDURE [DDLCollector].[UP_ProcessDeadlockEventMsg]
142. AS
143. /*

145. SELECT * FROM [DDLCollector].[Deadlock_Traced_Records]
146. SELECT * FROM [DDLCollector].[Send_Records]

148. SELECT * FROM [DDLCollector].[Error_Records]

150. */
151. BEGIN
152. SET NOCOUNT ON;
153. DECLARE
154. @handle UNIQUEIDENTIFIER
155. , @Message_Type SYSNAME
156. , @Service_Name SYSNAME
157. , @Contact_Name SYSNAME
158. , @Error_Details VARCHAR(2000)
159. , @Message_Body XML
160. , @Proc_Name SYSNAME
161. ;

163. -- Store Procedure Name
164. SELECT
165. @Proc_Name = ISNULL(QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
166. + '.'
167. + QUOTENAME(OBJECT_NAME(@@PROCID)),'')
168. FROM sys.procedures
169. WHERE OBJECT_ID = @@PROCID
170. ;

172. BEGIN TRY

174. -- Receive message from queue
175. WAITFOR(RECEIVE TOP(1)
176. @handle = conversation_handle
177. , @Message_Type = message_type_name
178. , @Service_Name = service_name
179. , @Contact_Name = service_contract_name
180. , @Message_Body = message_body
181. FROM dbo.[http://soa/deadlock/queue/ClientQueue]),Timeout 500
182. ;

184. -- just return if there is no message needed to process
185. IF(@@Rowcount=0)
186. BEGIN
187. RETURN
188. END
189. -- Get data from message queue
190. ELSE IF @Message_Type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
191. BEGIN
192. -- Record message log first
193. INSERT INTO  [DDLCollector].[Deadlock_Traced_Records](Processed_Msg, [Processed_Msg_CheckSum])
194. VALUES(@Message_Body, CHECKSUM(CAST(@Message_Body as NVARCHAR(MAX))))

196. -- BE NOTED HERE: PLEASE DO'T END CONVERSATION, OR ELSE EXCEPTION WILL BE THROWN OUTPUT
197. /*
198. Error: 17001, Severity: 16, State: 1.
199. Failure to send an event notification instance of type 'DEADLOCK_GRAPH' on conversation handle '{67419386-7C34-E711-A709-001C42099969}'. Error Code = '8429'.
200. Error: 17005, Severity: 16, State: 1.
201. Event notification 'DeadLockNotificationEvent' in database 'master' dropped due to send time service broker errors. Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active.
202. */
203. --END CONVERSATION @handle

205. --Here call another Store Procedure to send deadlock graph info to center server
206. EXEC [DDLCollector].[UP_SendDeadlockMsg] @Message_Body;
207. END
208. --End Diaglog Message Type, that means we should end this conversation
209. ELSE IF @Message_Type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
210. BEGIN
211. END CONVERSATION @handle;
212. END
213. -- Konwn Service Broker Errors by System.
214. ELSE IF @Message_Type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
215. BEGIN
216. END CONVERSATION @handle

218. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
219. VALUES(@Message_Body, @handle, @Message_Type, @Service_Name, @Contact_Name, ' Exception Store Procedure: ' + @Proc_Name);
220. END
221. ELSE
222. -- unknown Message Types.
223. BEGIN
224. END CONVERSATION @handle

226. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
227. VALUES(@Message_Body, @handle, @Message_Type, @Service_Name, @Contact_Name, ' Received unexpected message type when executing Store Procedure: ' + @Proc_Name);

229. -- unexpected message type
230. RAISERROR (N' Received unknown message type: %s', 16, 1, @Message_Type) WITH LOG;
231. END
232. END TRY
233. BEGIN CATCH
234. BEGIN
235. SET   @Error_Details=
236. ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) +
237. ' Error Details : ' + ERROR_MESSAGE() +
238. ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) +
239. ' Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) +
240. ' Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) +
241. ' Exception Proc: ' + @Proc_Name
242. ;

244. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
245. VALUES(@Message_Body, @handle, @Message_Type, @Service_Name, @Contact_Name, @Error_Details);
246. END
247. END CATCH
248. END
249. GO

创建Master库下Master Key


1. USE master
2. GO
3. -- If the master key is not available, create it.
4. IF NOT EXISTS (SELECT *
5. FROM sys.symmetric_keys
6. WHERE name LIKE '%MS_DatabaseMasterKey%')
7. BEGIN
8. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'ClientMasterKey*';
9. END
10. GO

创建传输层本地证书并备份到本地文件系统

这里请注意证书的开始生效时间要略微早于当前时间，并设置合适的证书过期日期，我这里是设置的过期日期为9999年12月30号。


1. USE master
2. GO
3. -- Crete Transport Layer Certification
4. CREATE CERTIFICATE TrpCert_ClientLocal
5. AUTHORIZATION dbo
6. WITH SUBJECT = 'TrpCert_ClientLocal',
7. START_DATE = '05/07/2017',
8. EXPIRY_DATE = '12/30/9999'
9. GO

11. -- then backup it up to local path
12. -- and after that copy it to Center server
13. BACKUP CERTIFICATE TrpCert_ClientLocal
14. TO FILE = 'C:\Temp\TrpCert_ClientLocal.cer';
15. GO

创建传输层远程证书

这里的证书是通过证书文件来创建的，这个证书文件来自于远程通讯的另一端Deadlock Center SQL Server的证书文件的一份拷贝。


1. USE master
2. GO
3. -- Create certification came from Center Server.
4. CREATE    CERTIFICATE TrpCert_RemoteCenter
5. FROM FILE = 'C:\Temp\TrpCert_RemoteCenter.cer'
6. GO

创建基于证书文件的用户登录

这里也可以创建带密码的常规用户登录，但是为了规避安全风险，这里最好创建基于证书文件的用户登录。


1. USE master
2. GO
3. -- Create user login
4. IF NOT EXISTS(SELECT *
5. FROM sys.syslogins
6. WHERE name='SSBDbo')
7. BEGIN
8. CREATE LOGIN SSBDbo FROM CERTIFICATE TrpCert_ClientLocal;
9. END
10. GO

创建Service Broker TCP/IP通讯端口并授权用户连接权限

这里需要注意的是，端口授权的证书一定本地实例创建的证书，而不是来自于远程服务器的那个证书。比如代码中的AUTHENTICATION = CERTIFICATE TrpCert_ClientLocal部分。


1. USE master
2. GO
3. --Creaet Tcp endpoint for SSB comunication and grant connect to users.
4. CREATE ENDPOINT EP_SSB_ClientLocal
5. STATE = STARTED
6. AS TCP
7. (
8. LISTENER_PORT = 4022
9. )
10. FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TrpCert_ClientLocal,  ENCRYPTION = REQUIRED
11. )
12. GO

14. -- Grant Connect on Endpoint to User SSBDbo
15. GRANT CONNECT ON ENDPOINT::EP_SSB_ClientLocal TO SSBDbo
16. GO

创建DDLCenter数据库Master Key


1. -- Now, let's go inside to conversation database
2. USE DDLCenter
3. GO

5. -- Create Master Key
6. IF NOT EXISTS (SELECT *
7. FROM sys.symmetric_keys
8. WHERE name LIKE '%MS_DatabaseMasterKey%')
9. BEGIN
10. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DDLCenterMasterKey*';
11. END
12. GO

创建会话层本地证书


1. USE DDLCenter
2. GO
3. -- Create conversation layer certification
4. CREATE CERTIFICATE DlgCert_ClientLocal
5. AUTHORIZATION dbo
6. WITH SUBJECT = 'DlgCert_ClientLocal',
7. START_DATE = '05/07/2017',
8. EXPIRY_DATE = '12/30/9999'
9. GO

11. -- backup it up to local path
12. -- and then copy it to remote Center server
13. BACKUP CERTIFICATE DlgCert_ClientLocal
14. TO FILE = 'C:\Temp\DlgCert_ClientLocal.cer';
15. GO

创建DDLCenter用户，不需要和任何用户登录匹配


1. USE DDLCenter
2. GO
3. -- Create User for login under conversation database
4. IF NOT EXISTS(
5. SELECT TOP 1 *
6. FROM sys.database_principals
7. WHERE name = 'SSBDbo'
8. )
9. BEGIN
10. CREATE USER SSBDbo WITHOUT LOGIN;
11. END
12. GO

创建会话层远程证书，这个证书文件来自Deadlock Center SQL Server备份


1. USE DDLCenter
2. GO
3. -- Create converstaion layer certification came from remote Center server.
4. CREATE    CERTIFICATE DlgCert_RemoteCenter
5. AUTHORIZATION SSBDbo
6. FROM FILE='C:\Temp\DlgCert_RemoteCenter.cer'
7. GO

9. GRANT CONNECT TO SSBDbo;

创建Service Broker组件对象

Deadlock Client与Deadlock Center在创建Service Broker组件对象时存在差异：第一个差异是创建Service的时候，需要包含Event Notification的Contract，名称为 http://schemas.microsoft.com/SQL/Notifications/PostEventNotification；第二个差异是需要多创建一个指向本地服务的路由http://soa/deadlock/route/LocalRoute。


1. USE DDLCenter
2. GO

4. -- Create Message Type
5. CREATE MESSAGE TYPE [http://soa/deadlock/MsgType/Request]
6. VALIDATION = WELL_FORMED_XML;
7. CREATE MESSAGE TYPE [http://soa/deadlock/MsgType/Response]
8. VALIDATION = WELL_FORMED_XML;
9. GO

11. -- Create Contact
12. CREATE CONTRACT [http://soa/deadlock/contract/CheckContract](
13. [http://soa/deadlock/MsgType/Request] SENT BY INITIATOR,
14. [http://soa/deadlock/MsgType/Response] SENT BY TARGET
15. );
16. GO

18. -- Create Queue
19. CREATE QUEUE dbo.[http://soa/deadlock/queue/ClientQueue]
20. WITH STATUS = ON, RETENTION = OFF
21. , ACTIVATION (STATUS = ON ,
22. PROCEDURE_NAME = [DDLCollector].[UP_ProcessDeadlockEventMsg] ,
23. MAX_QUEUE_READERS = 2 ,
24. EXECUTE AS N'dbo')
25. GO

27. -- Create Service
28. -- Here is very import, we have to create service for both contacts
29. -- to get extend event notification and SSB work.
30. CREATE SERVICE [http://soa/deadlock/service/ClientService]
31. ON QUEUE [http://soa/deadlock/queue/ClientQueue]
32. (
33. [http://soa/deadlock/contract/CheckContract],
34. [http://schemas.microsoft.com/SQL/Notifications/PostEventNotification]
35. );
36. GO

38. -- Grant Send on service
39. GRANT SEND ON SERVICE::[http://soa/deadlock/service/ClientService] to SSBDbo;
40. GO

42. -- Create Remote Service Bingding
43. CREATE REMOTE SERVICE BINDING [http://soa/deadlock/RSB/CenterRSB]
44. TO SERVICE 'http://soa/deadlock/service/CenterService'
45. WITH  USER = [SSBDbo],
46. ANONYMOUS=Off
47. GO

49. -- Create Route
50. CREATE ROUTE [http://soa/deadlock/route/CenterRoute]
51. WITH SERVICE_NAME = 'http://soa/deadlock/service/CenterService',
52. ADDRESS = 'TCP://10.211.55.3:4024';
53. GO

55. -- Create route for the DeadlockNotificationSvc
56. CREATE ROUTE [http://soa/deadlock/route/LocalRoute]
57. WITH SERVICE_NAME = 'http://soa/deadlock/service/ClientService',
58. ADDRESS = 'LOCAL';
59. GO

Deadlock Center Server

创建DDLCenter数据库并开启Service Broker选项


1. -- Run script on center server to receive client deadlock xml
2. USE master
3. GO
4. -- Create Database
5. IF DB_ID('DDLCenter') IS NULL
6. CREATE DATABASE [DDLCenter];
7. GO
8. -- Change datbase to simple recovery model
9. ALTER DATABASE [DDLCenter] SET RECOVERY SIMPLE WITH NO_WAIT
10. GO
11. -- Enable Service Broker
12. ALTER DATABASE [DDLCenter] SET ENABLE_BROKER,TRUSTWORTHY ON
13. GO
14. -- Change database Owner to sa
15. ALTER AUTHORIZATION ON DATABASE::DDLCenter TO [sa]
16. GO

三张表和两个存储过程

表[DDLCollector].[Collect_Records]：Deadlock Center成功接收到的Service Broker消息。表[DDLCollector].[Error_Records]：记录发生异常情况的详细信息。表[DDLCollector].[Deadlock_Info]：记录所有Deadlock Client端发生的Deadlock详细信息。存储过程[DDLCollector].[UP_ProcessDeadlockGraphEventMsg]：Deadlock Center上绑定到队列的激活存储过程，一旦队列中有消息进入，这个存储过程会被自动调用。存储过程[DDLCollector].[UP_ParseDeadlockGraphEventMsg]：Deadlock Center上解析Deadlock Graph XML的存储过程对象，这个存储过程会被上面的激活存储过程调用来解析XML，然后放入表[DDLCollector].[Deadlock_Info]中。


1. USE [DDLCenter]
2. GO

4. -- Create Schema
5. IF NOT EXISTS(
6. SELECT TOP 1 *
7. FROM sys.schemas
8. WHERE name = 'DDLCollector'
9. )
10. BEGIN
11. EXEC('CREATE SCHEMA DDLCollector');
12. END
13. GO

15. -- Create table to log the received message
16. IF OBJECT_ID('DDLCollector.Collect_Records', 'U') IS NOT NULL
17. DROP TABLE [DDLCollector].[Collect_Records]
18. GO

20. CREATE TABLE [DDLCollector].[Collect_Records](
21. [RowId] [BIGINT] IDENTITY(1,1) NOT NULL,
22. [Deadlock_Graph_Msg] [xml] NULL,
23. [Deadlock_Graph_Msg_CheckSum] INT,
24. [Record_Time] [datetime] NOT NULL
25. CONSTRAINT DF_Collect_Records_Record_Time DEFAULT(GETDATE()),
26. CONSTRAINT PK_Collect_Records_RowId PRIMARY KEY
27. (RowId ASC)
28. ) ON [PRIMARY]
29. GO

31. -- create table to record the exception when error occurs
32. IF OBJECT_ID('DDLCollector.Error_Records', 'U') IS NOT NULL
33. DROP TABLE [DDLCollector].[Error_Records]
34. GO

36. CREATE TABLE [DDLCollector].[Error_Records](
37. [RowId] [int] IDENTITY(1,1) NOT NULL,
38. [Msg_Body] [xml] NULL,
39. [Conversation_handle] [uniqueidentifier] NULL,
40. [Message_Type] SYSNAME NULL,
41. [Service_Name] SYSNAME NULL,
42. [Contact_Name] SYSNAME NULL,
43. [Record_Time] [datetime] NOT NULL
44. CONSTRAINT DF_Error_Records_Record_Time DEFAULT(GETDATE()),
45. [Error_Details] [nvarchar](4000) NULL,
46. CONSTRAINT PK_Error_Records_RowId PRIMARY KEY
47. (RowId ASC)
48. ) ON [PRIMARY]
49. GO

51. -- create business table to record deadlock analysised info
52. IF OBJECT_ID('DDLCollector.Deadlock_Info', 'U') IS NOT NULL
53. DROP TABLE [DDLCollector].[Deadlock_Info]
54. GO
55. CREATE TABLE [DDLCollector].[Deadlock_Info](
56. RowId INT IDENTITY(1,1) NOT NULL
57. ,SQLInstance sysname NULL
58. ,SPid INT NULL
59. ,is_Vitim BIT NULL
60. ,DeadlockGraph XML NULL
61. ,DeadlockGraphCheckSum INT NULL
62. ,lasttranstarted DATETIME NULL
63. ,lastbatchstarted DATETIME NULL
64. ,lastbatchcompleted DATETIME NULL
65. ,procname SYSNAME NULL
66. ,Code NVARCHAR(max) NULL
67. ,LockMode sysname NULL
68. ,Indexname sysname NULL
69. ,KeylockObject sysname NULL
70. ,IndexLockMode sysname NULL
71. ,Inputbuf NVARCHAR(max) NULL
72. ,LoginName sysname NULL
73. ,Clientapp sysname NULL
74. ,Action varchar(1000) NULL
75. ,status varchar(10) NULL
76. ,[Record_Time] [datetime] NOT NULL
77. CONSTRAINT DF_Deadlock_Info_Record_Time DEFAULT(GETDATE()),
78. CONSTRAINT PK_Deadlock_Info_RowId PRIMARY KEY
79. (RowId ASC)
80. )
81. GO

85. USE [DDLCenter]
86. GO

88. -- Create store procedure to analysis deadlock graph xml
89. -- and log into business table
90. IF OBJECT_ID('DDLCollector.UP_ParseDeadlockGraphEventMsg', 'P') IS NOT NULL
91. DROP PROC [DDLCollector].[UP_ParseDeadlockGraphEventMsg]
92. GO

94. CREATE PROCEDURE [DDLCollector].[UP_ParseDeadlockGraphEventMsg](
95. @DeadlockGraph_Msg XML
96. )
97. AS
98. BEGIN
99. SET NOCOUNT ON;

101. ;WITH deadlock
102. AS
103. (
104. SELECT
105. OwnerID = T.C.value('@id', 'varchar(50)')
106. ,SPid = T.C.value('(./@spid)[1]','int')
107. ,status = T.C.value('(./@status)[1]','varchar(10)')
108. ,Victim = case
109. when T.C.value('@id', 'varchar(50)') = T.C.value('./../../@victim','varchar(50)') then 1
110. else 0 end
111. ,LockMode = T.C.value('@lockMode', 'sysname')
112. ,Inputbuf = T.C.value('(./inputbuf/text())[1]','nvarchar(max)')
113. ,Code = T.C.value('(./executionStack/frame/text())[1]','nvarchar(max)')
114. ,SPName = T.C.value('(./executionStack/frame/@procname)[1]','sysname')
115. ,Hostname = T.C.value('(./@hostname)[1]','sysname')
116. ,Clientapp = T.C.value('(./@clientapp)[1]','varchar(1000)')
117. ,lasttranstarted = T.C.value('(./@lasttranstarted)[1]','datetime')
118. ,lastbatchstarted = T.C.value('(./@lastbatchstarted)[1]','datetime')
119. ,lastbatchcompleted = T.C.value('(./@lastbatchcompleted)[1]','datetime')
120. ,LoginName = T.C.value('@loginname', 'sysname')
121. ,Action = T.C.value('(./@transactionname)[1]','varchar(1000)')
122. FROM @DeadlockGraph_Msg.nodes('EVENT_INSTANCE/TextData/deadlock-list/deadlock/process-list/process') AS T(C)
123. )
124. ,
125. keylock
126. AS
127. (
128. SELECT
129. OwnerID = T.C.value('./owner[1]/@id', 'varchar(50)')
130. ,KeylockObject = T.C.value('./../@objectname', 'sysname')
131. ,Indexname = T.C.value('./../@indexname', 'sysname')
132. ,IndexLockMode = T.C.value('./../@mode', 'sysname')
133. FROM @DeadlockGraph_Msg.nodes('EVENT_INSTANCE/TextData/deadlock-list/deadlock/resource-list/keylock/owner-list') AS T(C)
134. )
135. SELECT
136. SQLInstance = A.Hostname
137. ,A.SPid
138. ,is_Vitim = A.Victim
139. ,DeadlockGraph = @DeadlockGraph_Msg.query('EVENT_INSTANCE/TextData/deadlock-list')
140. ,DeadlockGraphCheckSum = CHECKSUM(CAST(@DeadlockGraph_Msg AS NVARCHAR(MAX)))
141. ,A.lasttranstarted
142. ,A.lastbatchstarted
143. ,A.lastbatchcompleted
144. ,A.SPName
145. ,A.Code
146. ,A.LockMode
147. ,B.Indexname
148. ,B.KeylockObject
149. ,B.IndexLockMode
150. ,A.Inputbuf
151. ,A.LoginName
152. ,A.Clientapp
153. ,A.Action
154. ,status
155. ,[Record_Time] = GETDATE()
156. FROM deadlock AS A
157. LEFT JOIN keylock AS B
158. ON A.OwnerID = B.OwnerID
159. ORDER BY A.SPid, A.Victim
160. ;
161. END
162. GO

164. -- Create store Procedure for Center server service queue to process deadlock xml
165. -- when message sending from client server.
166. IF OBJECT_ID('DDLCollector.UP_ProcessDeadlockGraphEventMsg', 'P') IS NOT NULL
167. DROP PROC [DDLCollector].[UP_ProcessDeadlockGraphEventMsg]
168. GO

170. CREATE PROCEDURE [DDLCollector].[UP_ProcessDeadlockGraphEventMsg]
171. AS
172. /*
173. EXEC [DDLCollector].[UP_ProcessDeadlockGraphEventMsg]

175. SELECT * FROM [DDLCollector].[Collect_Records]

177. SELECT * FROM [DDLCollector].[Error_Records]

179. SELECT * FROM [DDLCollector].[Deadlock_Info]
180. */
181. BEGIN
182. SET NOCOUNT ON;
183. DECLARE
184. @handle UNIQUEIDENTIFIER
185. , @Message_Type SYSNAME
186. , @Service_Name SYSNAME
187. , @Contact_Name SYSNAME
188. , @Error_Details VARCHAR(2000)
189. , @Message_Body XML
190. , @Proc_Name SYSNAME
191. ;

193. -- Store Procedure name
194. SELECT
195. @Proc_Name = ISNULL(QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
196. + '.'
197. + QUOTENAME(OBJECT_NAME(@@PROCID)),'')
198. FROM sys.procedures
199. WHERE OBJECT_ID = @@PROCID
200. ;

202. BEGIN TRY

204. -- Receive deadlock message from service queue
205. WAITFOR(RECEIVE TOP(1)
206. @handle = conversation_handle
207. , @Message_Type = message_type_name
208. , @Service_Name = service_name
209. , @Contact_Name = service_contract_name
210. , @Message_Body = message_body
211. FROM dbo.[http://soa/deadlock/queue/CenterQueue]),Timeout 500
212. ;

214. IF(@@Rowcount=0)
215. BEGIN
216. RETURN
217. END
218. -- Message type is the very correct one
219. ELSE IF @Message_Type = N'http://soa/deadlock/MsgType/Request'
220. BEGIN
221. -- Record message log first
222. INSERT INTO  [DDLCollector].[Collect_Records](Deadlock_Graph_Msg, [Deadlock_Graph_Msg_CheckSum])
223. VALUES(@Message_Body, CHECKSUM(cast(@Message_Body as NVARCHAR(MAX))))

225. END CONVERSATION @handle

227. --Here call another Store Procedure to process our message to record deadlock relation info
228. INSERT INTO [DDLCollector].[Deadlock_Info]
229. EXEC [DDLCollector].[UP_ParseDeadlockGraphEventMsg] @Message_Body;
230. END
231. --End Diaglog Message Type, that means we should end this conversation
232. ELSE IF @Message_Type = N'http://schemas.microsoft.com/SQL/ServiceBroker/EndDialog'
233. BEGIN
234. END CONVERSATION @handle;
235. END
236. -- Konwn Service Broker Errors by System.
237. ELSE IF @Message_Type = N'http://schemas.microsoft.com/SQL/ServiceBroker/Error'
238. BEGIN
239. END CONVERSATION @handle

241. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
242. VALUES(@Message_Body, @handle, @Message_Type, @Service_Name, @Contact_Name, ' Exception Store Procedure: ' + @Proc_Name);
243. END
244. ELSE
245. -- unknown Message Types.
246. BEGIN
247. END CONVERSATION @handle

249. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
250. VALUES(@Message_Body, @handle, @Message_Type, @Service_Name, @Contact_Name, ' Received unexpected message type when executing Store Procedure: ' + @Proc_Name);

252. -- unexpected message type
253. RAISERROR (N' Received unexpected message type: %s', 16, 1, @Message_Type) WITH LOG;
254. END
255. END TRY
256. BEGIN CATCH
257. BEGIN
258. -- record exception record
259. SET   @Error_Details=
260. ' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) +
261. ' Error Message : ' + ERROR_MESSAGE() +
262. ' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) +
263. ' Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) +
264. ' Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) +
265. ' Exception Proc: ' + @Proc_Name
266. ;

268. INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
269. VALUES(@Message_Body, @handle, @Message_Type, @Service_Name, @Contact_Name, @Error_Details);
270. END
271. END CATCH
272. END
273. GO

创建Master库下Master Key


1. USE master
2. GO
3. -- If the master key is not available, create it.
4. IF NOT EXISTS (SELECT *
5. FROM sys.symmetric_keys
6. WHERE name LIKE '%MS_DatabaseMasterKey%')
7. BEGIN
8. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'CenterMasterKey*';
9. END
10. GO

创建传输层本地证书并备份到本地文件系统


1. USE master
2. GO
3. -- Crete Transport Layer Certification
4. CREATE CERTIFICATE TrpCert_RemoteCenter
5. AUTHORIZATION dbo
6. WITH SUBJECT = 'TrpCert_RemoteCenter',
7. START_DATE = '05/07/2017',
8. EXPIRY_DATE = '12/30/9999'
9. GO

11. -- then backup it up to local path
12. -- and after that copy it to Client server
13. BACKUP CERTIFICATE TrpCert_RemoteCenter
14. TO FILE = 'C:\Temp\TrpCert_RemoteCenter.cer';
15. GO

创建传输层远程证书，这个证书文件来至于Deadlock Client SQL Server


1. USE master
2. GO
3. -- Create certification came from client Server.
4. CREATE    CERTIFICATE TrpCert_ClientLocal
5. FROM FILE = 'C:\Temp\TrpCert_ClientLocal.cer'
6. GO

创建基于证书文件的用户登录


1. USE master
2. GO
3. -- Create user login
4. IF NOT EXISTS(SELECT *
5. FROM sys.syslogins
6. WHERE name='SSBDbo')
7. BEGIN
8. CREATE LOGIN SSBDbo FROM CERTIFICATE TrpCert_RemoteCenter;
9. END
10. GO

创建Service Broker TCP/IP通讯端口并授权用户连接权限


1. USE master
2. GO
3. -- Creaet Tcp endpoint for SSB comunication and grant connect to users.
4. CREATE ENDPOINT EP_SSB_RemoteCenter
5. STATE = STARTED
6. AS TCP
7. (
8. LISTENER_PORT = 4024
9. )
10. FOR SERVICE_BROKER (AUTHENTICATION = CERTIFICATE TrpCert_RemoteCenter,  ENCRYPTION = REQUIRED
11. )
12. GO

14. -- Grant Connect on Endpoint to User SSBDbo
15. GRANT CONNECT ON ENDPOINT::EP_SSB_RemoteCenter TO SSBDbo
16. GO

创建DDLCenter数据库Master Key


1. -- Now, let's go inside to conversation database
2. USE DDLCenter
3. GO

5. -- Create Master Key
6. IF NOT EXISTS (SELECT *
7. FROM sys.symmetric_keys
8. WHERE name LIKE '%MS_DatabaseMasterKey%')
9. BEGIN
10. CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'DDLCenterMasterKey*';
11. END
12. GO

创建会话层本地证书


1. USE DDLCenter
2. GO
3. -- Create conversation layer certification
4. CREATE CERTIFICATE DlgCert_RemoteCenter
5. AUTHORIZATION dbo
6. WITH SUBJECT = 'DlgCert_RemoteCenter',
7. START_DATE = '05/07/2017',
8. EXPIRY_DATE = '12/30/9999'
9. GO

11. -- backup it up to local path
12. -- and then copy it to remote client server
13. BACKUP CERTIFICATE DlgCert_RemoteCenter
14. TO FILE = 'C:\Temp\DlgCert_RemoteCenter.cer';
15. GO

创建DDLCenter用户，不需要和任何用户登录匹配


1. USE DDLCenter
2. GO
3. -- Create User for login under conversation database
4. IF NOT EXISTS(
5. SELECT TOP 1 *
6. FROM sys.database_principals
7. WHERE name = 'SSBDbo'
8. )
9. BEGIN
10. --CREATE USER SSBDbo FOR LOGIN SSBDbo;
11. CREATE USER SSBDbo WITHOUT LOGIN;
12. END
13. GO

创建会话层远程证书，这个证书文件来自Deadlock Center SQL Server备份


1. USE DDLCenter
2. GO
3. -- Create converstaion layer certification came from remote client server.
4. CREATE    CERTIFICATE DlgCert_ClientLocal
5. AUTHORIZATION SSBDbo
6. FROM FILE='C:\Temp\DlgCert_ClientLocal.cer'
7. GO

9. GRANT CONNECT TO SSBDbo;

创建Service Broker组件对象


1. USE DDLCenter
2. GO

4. -- Create Message Type
5. CREATE MESSAGE TYPE [http://soa/deadlock/MsgType/Request]
6. VALIDATION = WELL_FORMED_XML;
7. CREATE MESSAGE TYPE [http://soa/deadlock/MsgType/Response]
8. VALIDATION = WELL_FORMED_XML;
9. GO

11. -- Create Contact
12. CREATE CONTRACT [http://soa/deadlock/contract/CheckContract](
13. [http://soa/deadlock/MsgType/Request] SENT BY INITIATOR,
14. [http://soa/deadlock/MsgType/Response] SENT BY TARGET
15. );
16. GO

18. -- Create Queue
19. CREATE QUEUE [dbo].[http://soa/deadlock/queue/CenterQueue]
20. WITH STATUS = ON , RETENTION = OFF
21. , ACTIVATION (STATUS = ON ,
22. PROCEDURE_NAME = [DDLCollector].[UP_ProcessDeadlockGraphEventMsg] ,
23. MAX_QUEUE_READERS = 3 ,
24. EXECUTE AS N'dbo')
25. GO

27. -- Create Service
28. CREATE SERVICE [http://soa/deadlock/service/CenterService]
29. ON QUEUE [http://soa/deadlock/queue/CenterQueue]
30. (
31. [http://soa/deadlock/contract/CheckContract]
32. );
33. GO

35. -- Grant Send on service to User SSBDbo
36. GRANT SEND ON SERVICE::[http://soa/deadlock/service/CenterService] to SSBDbo;
37. GO

39. -- Create Remote Service Bingding
40. CREATE REMOTE SERVICE BINDING [http://soa/deadlock/RSB/ClientRSB]
41. TO SERVICE 'http://soa/deadlock/service/ClientService'
42. WITH  USER = SSBDbo,
43. ANONYMOUS=Off
44. GO

46. -- Create Route
47. CREATE ROUTE [http://soa/deadlock/route/ClientRoute]
48. WITH SERVICE_NAME = 'http://soa/deadlock/service/ClientService',
49. ADDRESS = 'TCP://10.211.55.3:4022';
50. GO

Event Notification配置

Event Notification只需要在Deadlock Client Server创建即可，因为只需要在Deadlock Client上跟踪死锁事件。在为Deadlock Client 配置Service Broker章节，我们已经为Event Notification创建了队列、服务和路由。因此，在这里我们只需要创建Event Notification对象即可。方法参见如下的代码：


1. USE DDLCenter
2. GO

4. -- Create Event Notification for the deadlock_graph event.
5. IF EXISTS(
6. SELECT * FROM sys.server_event_notifications
7. WHERE name = 'DeadLockNotificationEvent'
8. )
9. BEGIN
10. DROP EVENT NOTIFICATION DeadLockNotificationEvent
11. ON SERVER;
12. END
13. GO

16. CREATE EVENT NOTIFICATION DeadLockNotificationEvent
17. ON SERVER
18. WITH FAN_IN
19. FOR DEADLOCK_GRAPH
20. TO SERVICE
21. 'http://soa/deadlock/service/ClientService',
22. 'current database'
23. GO

模拟死锁

至此为止，所有对象和准备工作已经准备完成，万事俱备只欠东风，让我们在Deadlock Client实例上模拟死锁场景。首先，我们在Test数据库下创建两个测试表，表名分别为：dbo.test_deadlock1和dbo.test_deadlock2，代码如下：


1. IF DB_ID('Test') IS NULL
2. CREATE DATABASE Test;
3. GO

5. USE Test
6. GO

8. -- create two test tables
9. IF OBJECT_ID('dbo.test_deadlock1','u') IS NOT NULL
10. DROP TABLE dbo.test_deadlock1
11. GO

13. CREATE TABLE dbo.test_deadlock1(
14. id INT IDENTITY(1,1) not null PRIMARY KEY
15. ,name VARCHAR(20) null
16. );

18. IF OBJECT_ID('dbo.test_deadlock2','u') IS NOT NULL
19. DROP TABLE dbo.test_deadlock2
20. GO

22. CREATE TABLE dbo.test_deadlock2(
23. id INT IDENTITY(1,1) not null PRIMARY KEY
24. ,name VARCHAR(20) null
25. );

27. INSERT INTO dbo.test_deadlock1
28. SELECT 'AA'
29. UNION ALL
30. SELECT 'BB';

33. INSERT INTO dbo.test_deadlock2
34. SELECT 'AA'
35. UNION ALL
36. SELECT 'BB';
37. GO

接下来，我们使用SSMS打开一个新的连接，我们假设叫session 1，执行如下语句：


1. --session 1
2. USE Test
3. GO

5. BEGIN TRAN
6. UPDATE dbo.test_deadlock1
7. SET name = 'CC'
8. WHERE id = 1
9. ;
10. WAITFOR DELAY '00:00:05'

12. UPDATE dbo.test_deadlock2
13. SET name = 'CC'
14. WHERE id = 1
15. ;
16. ROLLBACK

紧接着，我们使用SSMS打开第二个连接，假设叫Session 2，执行下面的语句：


1. --session 2
2. USE Test
3. GO

5. BEGIN TRAN
6. UPDATE dbo.test_deadlock2
7. SET name = 'CC'
8. WHERE id = 1
9. ;

11. UPDATE dbo.test_deadlock1
12. SET name = 'CC'
13. WHERE id = 1
14. ;
15. COMMIT

等待一会儿功夫以后，死锁发生，并且Session 2做为了死锁的牺牲品，我们会在Session 2的SSMS信息窗口中看到如下的死锁信息：


1. Msg 1205, Level 13, State 51, Line 8
2. Transaction (Process ID 60) was deadlocked on lock resources with another process and has been chosen as the deadlock victim. Rerun the transaction.

用户查询死锁信息

根据上面的模拟死锁小节，说明死锁已经真真切切的发生了，那么，死锁信息到底有没有被捕获到呢？如果终端用户想要查看和分析所有客户端的死锁信息，只需要连接Deadlock Center SQL Server，执行下面的语句：


1. -- Run on Deadlock Center Server
2. USE DDLCenter
3. GO

5. SELECT * FROM [DDLCollector].[Deadlock_Info]

由于结果集宽度太宽，人为将查询结果分两段截图，第一段结果集展示如下：

第二段结果集截图如下：

从这个结果集，我们可以清楚的看到Deadlock Client发生死锁的详细信息，包含：

死锁发生的Deadlock Client实例名称：CHERISH-PC
被死锁进程号60，死锁进程57号
死锁相关进程的事务开始时间，最后一个Batch开始执行时间和完成时间
死锁进程执行的代码和Batch语句
死锁发生时锁的类型
表和索引名称
死锁相关进程的登录用户

…… 等等。

踩过的坑

当Deadlock Client 上SQL Server发生两次或者两次以上的Deadlock事件以后，自建的Event Notification对象（名为：DeadLockNotificationEvent）会被SQL Server系统自动删除，从而导致整个死锁收集系统无法工作。

表象

SQL Server在错误日志中会抛出如下4个错误信息：两个错误编号为17004，一个编号为17001的错误，最后是一个编号为17005错误，其中17005明确说明了，Event Notification对象被删除了。如下：


1. Error: 17004, Severity: 16, State: 1.
2. Event notification conversation on dialog handle '{4A6A0FBD-7A34-E711-A709-001C42099969}' closed without an error.
3. Error: 17004, Severity: 16, State: 1.
4. Event notification conversation on dialog handle '{476A0FBD-7A34-E711-A709-001C42099969}' closed without an error.
5. Error: 17001, Severity: 16, State: 1.
6. Failure to send an event notification instance of type 'DEADLOCK_GRAPH' on conversation handle '{F711A404-7934-E711-A709-001C42099969}'. Error Code = '8429'.
7. Error: 17005, Severity: 16, State: 1.
8. Event notification 'DeadLockNotificationEvent' in database 'master' dropped due to send time service broker errors. Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active.

错误日志截图如下：

问题分析

从错误提示信息due to send time service broker errors来看，最开始花了很长时间来排查Service Broker方面的问题，在长达数小时的问题排查无果后，静下心来仔细想想：如果是Service Broker有问题的话，我们不可能完成第一、第二条死锁信息的收集，所以问题应该与Service Broker没有直接关系。于是，注意到了错误提示信息的后半部分Check to ensure the conversation handle, service broker contract, and service specified in the event notification are active，再次以可以成功收集两条deadlock错误信息为由，排除Contact和Service的问题可能性，所以最有可能出问题的地方猜测应该是conversation handle，继续排查与conversation handle相关操作的地方，发现存储过程[DDLCollector].[UP_ProcessDeadlockEventMsg]的中的代码:


1. ...
2. ELSE IF @Message_Type = 'http://schemas.microsoft.com/SQL/Notifications/EventNotification'
3. BEGIN
4. -- Record message log first
5. INSERT INTO  [DDLCollector].[Deadlock_Traced_Records](Processed_Msg, [Processed_Msg_CheckSum])
6. VALUES(@Message_Body, CHECKSUM(CAST(@Message_Body as NVARCHAR(MAX))))

8. END CONVERSATION @handle

10. --Here call another Store Procedure to send deadlock graph info to center server
11. EXEC [DDLCollector].[UP_SendDeadlockMsg] @Message_Body;
12. END
13. ...

这个逻辑分支不应该有End Conversation的操作，因为这里是与Event Notification相关的Message Type操作，而不是Service Broker相关的Message Type操作。

解决问题

问题分析清楚了，解决方法就非常简单了，注释掉这条语句END CONVERSATION @handle后，重新创建存储过程。再多次模拟死锁操作，再也没有出现Event Notification被系统自动删除的情况了，说明这个问题已经被彻底解决，坑已经被填上了。解决问题的代码修改和注释如下截图，以此纪念下踩过的这个坑：

福利发放

以下是关于SQL Server死锁相关的系列文章，可以帮助我们全面了解、分析和解决死锁问题，其中第一个是这篇文章的视频演示。

使用Event Notification和Service Broker自动跟踪SQL Server Deadlock的Youku视频：使用事件通知和Service Broker自动跟踪SQL Server死锁
使用DBCC捕获死锁：RDS SQL Server死锁（Deadlock）系列之一使用DBCC捕获死锁
使用Profiler捕获死锁：RDS SQL Server死锁（Deadlock）系列之二使用Profiler捕获死锁
自动部署Profiler捕获死锁：RDS SQL Server死锁（Deadlock）系列之三自动部署Profiler捕获死锁
利用Service Broker事件通知捕获死锁：RDS SQL Server死锁（Deadlock）系列之四利用Service Broker事件通知捕获死锁
利用Extended Events获取死锁信息：RDS SQL Server死锁（Deadlock）系列之五利用Extended Events获取死锁信息

最后总结

这篇文章是一个完整的SQL Server死锁收集系统典型案例介绍，你甚至可以很轻松简单的将这个方案应用到你的产品环境，来收集产品环境所有SQL Server实例发生死锁的详细信息，并根据该系统收集到的场景来改进和改善死锁发生的概率，从而降低死应用发生异常错误的可能性。因此这篇文章有着非常重要的现实价值和意义。

原文：http://mysql.taobao.org/monthly/2017/05/06/