sql检查数据库死锁的方法
首先， 执行exec sp_lock找出数据库死锁相关信息。
这个程序会从主数据库中的syslockinfo中返回与锁定相关的大量信息，而主数据库是一个包括了所有允许、转换和等待锁定请求信息的系统工作台。
sp_lock 返回的信息并不是一目了然的，要获得有用的数据，还需要做一些查找。
但是，可以复制该存储过程的文本，然后创建一个新的，从而得到关于系统进程的更好的解释。这里将集中讨论sp_lock返回的数据。
返回结果中可以看到spid、dbid、objid、indid、type、resource、mode和status字段
spid是进程标识号码，用于识别到SQL 服务器的连接。要发现哪些用户和该spid相连，需要执行存储过程sp_who，并将spid作为一个参数传输给该程序。
dbid是锁定发生的数据库，你可以在主数据库中的sysdatabases表格中找到它。
字段objid用来显示在数据库中锁定发生所在的对象。要查看这个对象，可以在主数据库中的sysobjects表格中查询指定的objid。
产生的单一记录并不一定能显示正在你的工作环境中发生的真实情况。
在运行这个程序时，想要找到500到1000个甚至更多结果。
每一次你执行sp_lock，都将有可能得到不同的结果，因为又发生了新的锁定，而部分旧的锁定已经被解除了。
如果发现sp_lock返回的结果中，大量的结果都有着相同的spid，很有可能该进程正在进行大型的处理，同时这些锁定可能开始阻止新事务的发生。
当发现一个spid 获得了大量的数据库锁定时，这将有助于确定什么存储过程或语句正在运行。
运行 DBCC 命令：
这个DBCC命令将返回正在EventInfo字段中运行的语句的相关信息，可以显示正在执行的sql命令。
第二步骤，解除数据库死锁。
--解除死锁
Kill spid
一个可靠的起点
系统运行缓慢可能说明表中有大量的锁定。
造成这些锁定的原因较多，如某个用户正在系统中运行一个相当长的查询，一个进程占用大量资源或者两个关键进程争夺同一资源，经常造成死锁。
一旦发现认为正在减缓你系统速度的进程，应该怎么办？
在大多数情况下，不能采取任何措施，只能监控系统。
结束这个进程并不是明智之举，因为它包括了很多系统锁定，除非你完全肯定不会有其他的负面影响。
否则，就应该想办法自动分析锁定状况。
另外一种可供参考的方法，在一天的特定时间内，当系统锁数量达到极限时，发出通知。
当然，也可以结合dbcc traceon与sql profile生成的日志文件，进行数据分析与跟踪，找出数据库死锁的sql查询，进而解决死锁的问题。
附， sp_lock存储过程代码。
--proc
--sp_lock
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [sys].[sp_lock] --- 1996/04/08 00:00
@spid1 int = NULL, /* server process id to check for locks */
@spid2 int = NULL /* other process id to check for locks */
as
set nocount on
/*
** Show the locks for both parameters.
*/
set transaction isolation level read committed
if @spid1 is not NULL
begin
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 32) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
and req_spid in (@spid1, @spid2)
end
/*
** No parameters, so show all the locks.
*/
else
begin
select convert (smallint, req_spid) As spid,
rsc_dbid As dbid,
rsc_objid As ObjId,
rsc_indid As IndId,
substring (v.name, 1, 4) As Type,
substring (rsc_text, 1, 32) as Resource,
substring (u.name, 1, 8) As Mode,
substring (x.name, 1, 5) As Status
from master.dbo.syslockinfo,
master.dbo.spt_values v,
master.dbo.spt_values x,
master.dbo.spt_values u
where master.dbo.syslockinfo.rsc_type = v.number
and v.type = 'LR'
and master.dbo.syslockinfo.req_status = x.number
and x.type = 'LS'
and master.dbo.syslockinfo.req_mode + 1 = u.number
and u.type = 'L'
order by spid
end
return (0) -- sp_lock
附2，系统存储过程sp_who代码。
---
proc:sp_who
--
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER procedure [sys].[sp_who] --- 1995/11/28 15:48
@loginame sysname = NULL --or 'active'
as
declare @spidlow int,
@spidhigh int,
@spid int,
@sid varbinary(85)
select @spidlow = 0
,@spidhigh = 32767
if ( @loginame is not NULL
AND upper(@loginame collate Latin1_General_CI_AS) = 'ACTIVE'
)
begin
select spid , ecid, status
,loginame=rtrim(loginame)
,hostname ,blk=convert(char(5),blocked)
,dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh AND
upper(cmd) <> 'AWAITING COMMAND'
return (0)
end
if (@loginame is not NULL
AND upper(@loginame collate Latin1_General_CI_AS) <> 'ACTIVE'
)
begin
if (@loginame like '[0-9]%') -- is a spid.
begin
select @spid = convert(int, @loginame)
select spid, ecid, status,
loginame=rtrim(loginame),
hostname,blk = convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from master.dbo.sysprocesses
where spid = @spid
end
else
begin
select @sid = suser_sid(@loginame)
if (@sid is null)
begin
raiserror(15007,-1,-1,@loginame)
return (1)
end
select spid, ecid, status,
loginame=rtrim(loginame),
hostname ,blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from master.dbo.sysprocesses
where sid = @sid
end
return (0)
end
-- loginame arg is null
select spid,
ecid,
status,
loginame=rtrim(loginame),
hostname,
blk=convert(char(5),blocked),
dbname = case
when dbid = 0 then null
when dbid <> 0 then db_name(dbid)
end
,cmd
,request_id
from master.dbo.sysprocesses
where spid >= @spidlow and spid <= @spidhigh
return (0) -- sp_who