【转】阿里巴巴数据库项目组 -- SQL Server下构建死锁自动收集系统
转自http://mysql.taobao.org/monthly/2017/05/06/
数据库内核月报 － 2017 / 05
MSSQL · 应用案例 · 构建死锁自动收集系统
摘要
这篇文章介绍SQL Server的一个典型的应用案例，即如何利用Event Notification与Service Broker技术相结合来实现死锁信息自动收集系统。通过这个系统，我们可以全面把控SQL Server数据库环境中所有实例上发生的死锁详细信息，供我们后期分析和解决死锁场景。
死锁自动收集系统需求分析
当 SQL Server 中某组资源的两个或多个线程或进程之间存在循环的依赖关系时，但因互相申请被其他进程所占用，而不会释放的资源处于的一种永久等待状态，将会发生死锁。SQL Server服务自动死锁检查进程默认每5分钟跑一次，当死锁发生时，会选择一个代价较小的进程做为死锁牺牲品，以此来避免死锁导致更大范围的影响。被选择做为死锁牺牲品的进程会报告如下错误：
Msg 1205, Level 13, State 51, Line 8
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选项
-- Run script on client server to gather deadlock graph xml
USE master
GO
-- Create Database
IF DB_ID('DDLCenter') IS NULL
CREATE DATABASE [DDLCenter];
GO
-- Change datbase to simple recovery model
ALTER DATABASE [DDLCenter] SET RECOVERY SIMPLE WITH NO_WAIT
GO
-- Enable Service Broker
ALTER DATABASE [DDLCenter] SET ENABLE_BROKER,TRUSTWORTHY ON
GO
-- Change database Owner to sa
ALTER AUTHORIZATION ON DATABASE::DDLCenter TO [sa]
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，这个存储过程会被上面的激活存储过程调用。
-- Run on Client Instance
USE [DDLCenter]
GO
-- Create Schema
IF NOT EXISTS(
SELECT TOP 1 *
FROM sys.schemas
WHERE name = 'DDLCollector'
)
BEGIN
EXEC('CREATE SCHEMA DDLCollector');
END
GO
-- Create table to log Traced Deadlock Records
IF OBJECT_ID('DDLCollector.Deadlock_Traced_Records', 'U') IS NOT NULL
DROP TABLE [DDLCollector].[Deadlock_Traced_Records]
GO
CREATE TABLE [DDLCollector].[Deadlock_Traced_Records](
[RowId] [BIGINT] IDENTITY(1,1) NOT NULL,
[Processed_Msg] [xml] NULL,
[Processed_Msg_CheckSum] INT,
[Record_Time] [datetime] NOT NULL
CONSTRAINT DF_Deadlock_Traced_Records_Record_Time DEFAULT(GETDATE()),
CONSTRAINT PK_Deadlock_Traced_Records_RowId PRIMARY KEY
(RowId ASC)
) ON [PRIMARY]
GO
-- Create table to record deadlock graph xml sent successfully log
IF OBJECT_ID('DDLCollector.Send_Records', 'U') IS NOT NULL
DROP TABLE [DDLCollector].[Send_Records]
GO
CREATE TABLE [DDLCollector].[Send_Records](
[RowId] [BIGINT] IDENTITY(1,1) NOT NULL,
[Send_Msg] [xml] NULL,
[Send_Msg_CheckSum] INT,
[Record_Time] [datetime] NOT NULL
CONSTRAINT DF_Send_Records_Record_Time DEFAULT(GETDATE()),
CONSTRAINT PK_Send_Records_RowId PRIMARY KEY
(RowId ASC)
) ON [PRIMARY]
GO
-- Create table to record error info when exception occurs
IF OBJECT_ID('DDLCollector.Error_Records', 'U') IS NOT NULL
DROP TABLE [DDLCollector].[Error_Records]
GO
CREATE TABLE [DDLCollector].[Error_Records](
[RowId] [int] IDENTITY(1,1) NOT NULL,
[Msg_Body] [xml] NULL,
[Conversation_handle] [uniqueidentifier] NULL,
[Message_Type] SYSNAME NULL,
[Service_Name] SYSNAME NULL,
[Contact_Name] SYSNAME NULL,
[Record_Time] [datetime] NOT NULL
CONSTRAINT DF_Error_Records_Record_Time DEFAULT(GETDATE()),
[Error_Details] [nvarchar](4000) NULL,
CONSTRAINT PK_Error_Records_RowId PRIMARY KEY
(RowId ASC)
) ON [PRIMARY]
GO
USE [DDLCenter]
GO
-- Create Store Procedure to Send Deadlock Graph xml to Center Server
IF OBJECT_ID('DDLCollector.UP_SendDeadlockMsg', 'P') IS NOT NULL
DROP PROC [DDLCollector].[UP_SendDeadlockMsg]
GO
CREATE PROCEDURE [DDLCollector].[UP_SendDeadlockMsg](
@DeadlockMsg XML
)
AS
BEGIN
SET NOCOUNT ON;
DECLARE
@handle UNIQUEIDENTIFIER
,@Proc_Name SYSNAME
,@Error_Details VARCHAR(2000)
;
-- get the store procedure name
SELECT
@Proc_Name = ISNULL(QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
+ '.'
+ QUOTENAME(OBJECT_NAME(@@PROCID)),'')
FROM sys.procedures
WHERE OBJECT_ID = @@PROCID
;
BEGIN TRY
-- Begin Dialog
BEGIN DIALOG CONVERSATION @handle
FROM SERVICE [http://soa/deadlock/service/ClientService]
TO Service 'http://soa/deadlock/service/CenterService'
ON CONTRACT [http://soa/deadlock/contract/CheckContract]
;
-- Send deadlock graph xml as the message to Center Server
SEND ON CONVERSATION @handle
MESSAGE TYPE [http://soa/deadlock/MsgType/Request] (@DeadlockMsg);
-- Log it successfully
INSERT INTO [DDLCollector].[Send_Records]([Send_Msg], [Send_Msg_CheckSum])
VALUES( @DeadlockMsg, CHECKSUM(CAST(@DeadlockMsg as NVARCHAR(MAX))))
END TRY
BEGIN CATCH
-- Record the error info when exception occurs
SET @Error_Details=
' Error Number: ' + CAST(ERROR_NUMBER() AS VARCHAR(10)) +
' Error Message : ' + ERROR_MESSAGE() +
' Error Severity: ' + CAST(ERROR_SEVERITY() AS VARCHAR(10)) +
' Error State: ' + CAST(ERROR_STATE() AS VARCHAR(10)) +
' Error Line: ' + CAST(ERROR_LINE() AS VARCHAR(10)) +
' Exception Proc: ' + @Proc_Name
;
-- record into table
INSERT INTO [DDLCollector].[Error_Records]([Msg_Body], [Conversation_handle], [Message_Type], [Service_Name], [Contact_Name], [Error_Details])
VALUES(@DeadlockMsg, @handle, 'http://soa/deadlock/MsgType/Request', 'http://soa/deadlock/service/ClientService', 'http://soa/deadlock/contract/CheckContract', @Error_Details);
END CATCH
END
GO
-- Create Store Procedure for Queue: when extend event notification queue message
-- this store procedure will be called.
IF OBJECT_ID('DDLCollector.UP_ProcessDeadlockEventMsg', 'P') IS NOT NULL
DROP PROC [DDLCollector].[UP_ProcessDeadlockEventMsg]
GO
CREATE PROCEDURE [DDLCollector].[UP_ProcessDeadlockEventMsg]
AS
/*
SELECT * FROM [DDLCollector].[Deadlock_Traced_Records]
SELECT * FROM [DDLCollector].[Send_Records]
SELECT * FROM [DDLCollector].[Error_Records]
*/
BEGIN
SET NOCOUNT ON;
DECLARE
@handle UNIQUEIDENTIFIER
, @Message_Type SYSNAME
, @Service_Name SYSNAME
, @Contact_Name SYSNAME
, @Error_Details VARCHAR(2000)
, @Message_Body XML
, @Proc_Name SYSNAME
;
-- Store Procedure Name
SELECT
@Proc_Name = ISNULL(QUOTENAME(SCHEMA_NAME(SCHEMA_ID))
+ '.'
+