SQL Server数据库死锁处理超详细攻略_MsSqlserver

一、引言

在 sql server 数据库的日常使用中，死锁是一个常见且令人头疼的问题。死锁会导致数据库性能下降，甚至影响业务的正常运行。本文将详细介绍如何在 sql server 中查询造成死锁的 spid（会话 id）、获取执行信息、定位造成死锁的语句以及结束死锁进程，并给出相关的应用场景示例。

二、查询 sqlserver 中造成死锁的 spid

原理：在 sql server 中，sys.dm_tran_locks 是一个动态管理视图，它提供了有关当前活动事务持有的锁的信息。我们可以通过查询这个视图，筛选出资源类型为 object的锁信息，从而找出可能造成死锁的会话 id（spid）以及对应的表名。

代码示例：

select request_session_id as spid, object_name(resource_associated_entity_id) as tablename
from sys.dm_tran_locks
where resource_type = 'object';

代码解释：

request_session_id：表示持有锁的会话 id，也就是 spid。
resource_associated_entity_id：表示与锁关联的对象的 id。
object_name(resource_associated_entity_id)：通过这个函数将对象 id 转换为对应的表名。
resource_type = ‘object’`：筛选出资源类型为对象的锁信息。

三、用内置函数查询执行信息

1. sp_who存储过程

原理：sp_who是 sql server 提供的一个系统存储过程，用于显示有关当前 sql server 实例中活动用户和进程的信息。它可以帮助我们了解当前有哪些会话正在运行，以及它们的状态。

代码示例：

execute sp_who;

代码解释：执行该存储过程后，会返回一个结果集，包含以下主要列：

spid`：会话 id。
status`：会话的状态，如 running、sleeping等。
loginame`：登录用户名。
dbname：当前会话使用的数据库名。

2. sp_lock存储过程

** 原理：**
sp_lock是另一个系统存储过程，用于显示有关当前 sql server 实例中锁的信息。它可以帮助我们了解哪些资源正在被锁定，以及是哪些会话持有这些锁。

代码示例：

execute sp_lock;

代码解释：执行该存储过程后，会返回一个结果集，包含以下主要列：

spid：持有锁的会话 id。
dbid：数据库 id。
objid：对象 id。
indid：索引 id。
type：锁的类型，如 ix（意向排它锁）、x（排它锁）等。

四、根据 spid 查询造成死锁的语句

原理：dbcc inputbuffer是一个 sql server 的命令，用于显示指定会话 id（spid）最近执行的语句。通过这个命令，我们可以定位到造成死锁的具体 sql 语句。

代码示例：

dbcc inputbuffer(80);

代码解释：

80：表示要查询的会话 id（spid）。执行该命令后，会返回一个结果集，包含以下主要列：
eventtype：事件类型，如 rpc event、language event等。
parameters：参数信息。
eventinfo：最近执行的 sql 语句。

五、结束死锁进程

原理：kill是 sql server 提供的一个命令，用于终止指定会话 id（spid）的进程。当我们确定某个会话造成了死锁，并且无法通过其他方式解决时，可以使用这个命令结束该会话。

代码示例：

kill 80;

代码解释：

80：表示要终止的会话 id（spid）。执行该命令后，sql server 会立即终止该会话的所有活动，并释放该会话持有的所有资源。

六、相关应用场景

场景一：查询可能造成死锁的会话和表

select request_session_id as spid, object_name(resource_associated_entity_id) as tablename
from sys.dm_tran_locks
where resource_type = 'object';

这个查询可以帮助我们找出当前哪些会话正在对哪些表持有锁，从而判断是否存在死锁的可能性。

场景二：查询不重复的可能造成死锁的会话和表

select distinct request_session_id as spid, object_name(resource_associated_entity_id) as tablename
from sys.dm_tran_locks
where resource_type = 'object';

当我们只需要了解哪些不同的会话和表可能造成死锁时，可以使用这个查询。

场景三：定位具体表的死锁信息

假设我们怀疑以下几个表存在死锁问题：

swmp.dbo.sp_costcollectqueryview_t;1
swmp.dbo.sp_costapplycheckcrm_v3;1
swmp.dbop_repstoc.kanalysis;1

我们可以结合前面的查询方法，进一步定位具体的死锁信息。例如，先通过sys.dm_tran_locks找出涉及这些表的会话 id，然后使用 dbcc inputbuffer查看这些会话最近执行的语句。

-- 假设通过前面的查询得到会话 id 为 90
dbcc inputbuffer(90);

-- 假设通过前面的查询得到需要终止的会话 id 为 81、84、85、119、120、123
kill 81;
kill 84;
kill 85;
kill 119;
kill 120;
kill 123;