数据库死锁查询及处理

创建存储过程sp_who_lock，查询死锁的进程

create procedure sp_who_lock
WITH ENCRYPTION
as
begin
    declare @spid int,@bl int,
    @intTransactionCountOnEntry     int,
    @intRowcount             int,
    @intCountProperties         int,
    @intCounter             int
    create table #tmp_lock_who (
        id int identity(1,1),
        spid smallint,
        bl smallint)
    
    IF @@ERROR <> 0 RETURN @@ERROR

    insert into #tmp_lock_who(spid,bl) select spid,blocked from sysprocesses where  blocked <> 0

    IF @@ERROR <> 0 RETURN @@ERROR
    -- 找到临时表的记录数
    select @intCountProperties = Count(1),@intCounter = 1 from #tmp_lock_who
    IF @@ERROR <> 0 RETURN @@ERROR
    if @intCountProperties=0
        select '现在没有阻塞和死锁信息' as 'message'

    -- 循环开始
    while @intCounter = @intCountProperties
    begin
    -- 取第一条记录
        select     @spid = spid,@bl = bl
        from #tmp_lock_who where Id = @intCounter
        begin
            select '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被'
            + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'
            DBCC INPUTBUFFER (@bl )
        end
        -- 循环指针下移
        set @intCounter = @intCounter + 1
    end
    drop table #tmp_lock_who
    return 0
end

下面我们自己构建一个死锁进程：

BEGIN TRANSACTION--开始事务

update   T_Users  set UserName='00000'  where UserId='123'

WAITFOR DELAY '01:00'; --指定1点执行 

执行查询语句：

select * from T_Users where UserId='123'

这时会发现一直在执行查询。得不到查询结果，我们执行第一步创建的存储过程sp_who_lock.得到结果如下：

此时我们只需执行

kill 53

然后再执行查询语句就可以得到结果了。