数据库内核月报 - 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开始集成到数据库引擎中的消息