处理死锁

 

sp_who active  --看看哪个引起的阻塞，blk
sp_lock  --看看锁住了那个资源id，objid ，select object_name(objid) 得到
dbcc inputbuffer(@blk) -- 看看是那个语句


/*--处理死锁

 查看当前进程,或死锁进程,并能自动杀掉死进程

 因为是针对死的,所以如果有死锁进程,只能查看死锁进程
 当然,你可以通过参数控制,不管有没有死锁,都只查看死锁进程

--邹建 2004.4--*/

/*--调用示例

 exec p_lockinfo
--*/
create proc p_lockinfo
@kill_lock_spid bit=1,  --是否杀掉死锁的进程,1 杀掉, 0 仅显示
@show_spid_if_nolock bit=1 --如果没有死锁的进程,是否显示正常进程信息,1 显示,0 不显示
as
declare @count int,@s nvarchar(1000),@i int
select id=identity(int,1,1),标志,
 进程ID=spid,线程ID=kpid,块进程ID=blocked,数据库ID=dbid,
 数据库名=db_name(dbid),用户ID=uid,用户名=loginame,累计CPU时间=cpu,
 登陆时间=login_time,打开事务数=open_tran, 进程状态=status,
 工作站名=hostname,应用程序名=program_name,工作站进程ID=hostprocess,
 域名=nt_domain,网卡地址=net_address
into #t from(
 select 标志='死锁的进程',
  spid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=a.spid,s2=0
 from master..sysprocesses a join (
  select blocked from master..sysprocesses group by blocked
  )b on a.spid=b.blocked where a.blocked=0
 union all
 select '|_牺牲品_>',
  spid,kpid,blocked,dbid,uid,loginame,cpu,login_time,open_tran,
  status,hostname,program_name,hostprocess,nt_domain,net_address,
  s1=blocked,s2=1
 from master..sysprocesses a where blocked<>0
)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_spid_if_nolock=1
begin
 insert #t
 select 标志='正常的进程',
  spid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,
  open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address
 from master..sysprocesses
 set @count=@@rowcount
end

if @count>0
begin
 create table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))
 if @kill_lock_spid=1
 begin
  declare @spid varchar(10),@标志 varchar(10)
  while @i<=@count
  begin
   select @spid=进程ID,@标志=标志 from #t where id=@i
   insert #t1 exec('dbcc inputbuffer('+@spid+')')
   if @标志='死锁的进程' exec('kill '+@spid)
   set @i=@i+1
  end
 end
 else
  while @i<=@count
  begin
   select @s='dbcc inputbuffer('+cast(进程ID as varchar)+')' from #t where id=@i
   insert #t1 exec(@s)
   set @i=@i+1
  end
 select a.*,进程的SQL语句=b.EventInfo
 from #t a join #t1 b on a.id=b.id
end
go


在SQL Server 2005中解决死锁问题

作者：佚名  2007-05-29 

    数据库操作的死锁是不可避免的，本文并不打算讨论死锁如何产生，重点在于解决死锁，通过SQL Server 2005, 现在似乎有了一种新的解决办法。 

    将下面的SQL语句放在两个不同的连接里面，并且在5秒内同时执行，将会发生死锁。 

use Northwind
begin tran
　　insert into Orders(CustomerId) values(@#ALFKI@#)
　　waitfor delay @#00:00:05@#
　　select * from Orders where CustomerId = @#ALFKI@#
commit
print @#end tran@# 

    SQL Server对付死锁的办法是牺牲掉其中的一个，抛出异常，并且回滚事务。在SQL Server 2000，语句一旦发生异常，T-SQL将不会继续运行，上面被牺牲的连接中， print @#end tran@#语句将不会被运行，所以我们很难在SQL Server 2000的T-SQL中对死锁进行进一步的处理。 

    现在不同了，SQL Server 2005可以在T-SQL中对异常进行捕获，这样就给我们提供了一条处理死锁的途径： 

    下面利用的try  catch来解决死锁。 

SET XACT_ABORT ON
declare @r int
set @r = 1
while @r <= 3
begin
　　begin tran
　　
　　begin try　　　
　　　　insert into Orders(CustomerId) values(@#ALFKI@#)
　　　　waitfor delay @#00:00:05@#
　　　　select * from Orders where CustomerId = @#ALFKI@#
　　　　
　　　　commit
　　　　break
　　end try
　　　　
　　begin catch
　　　　rollback
　　　　waitfor delay @#00:00:03@#
　　　　set @r = @r + 1
　　　　continue
　　end catch
end 

    解决方法当然就是重试，但捕获错误是前提。rollback后面的waitfor不可少，发生冲突后需要等待一段时间，@retry数目可以调整以应付不同的要求。 

    但是现在又面临一个新的问题: 错误被掩盖了，一但问题发生并且超过3次，异常却不会被抛出。SQL Server 2005 有一个RaiseError语句，可以抛出异常，但却不能直接抛出原来的异常，所以需要重新定义发生的错误，现在，解决方案变成了这样: 

declare @r int
set @r = 1
while @r <= 3
begin
　　begin tran
　　
　　begin try　　　
　　　　insert into Orders(CustomerId) values(@#ALFKI@#)
　　　　waitfor delay @#00:00:05@#
　　　　select * from Orders where CustomerId = @#ALFKI@#
　　　　
　　　　commit
　　　　break
　　end try
　　　　
　　begin catch
　　　　rollback
　　　　waitfor delay @#00:00:03@#
　　　　set @r = @r + 1
　　　　continue
　　end catch
end
if ERROR_NUMBER() <> 0
begin
　　declare @ErrorMessage nvarchar(4000);
　　declare @ErrorSeverity int;
　　declare @ErrorState int;
　　select
　　　　@ErrorMessage = ERROR_MESSAGE(),
　　　　@ErrorSeverity = ERROR_SEVERITY(),
　　　　@ErrorState = ERROR_STATE();
　　raiserror (@ErrorMessage,
　　　　　　　 @ErrorSeverity,
　　　　　　　 @ErrorState
　　　　　　　 );
end 

    我希望将来SQL Server 2005能够直接抛出原有异常，比如提供一个无参数的RaiseError。 

    因此方案有点臃肿，但将死锁问题封装到T-SQL中有助于明确职责，提高高层系统的清晰度。现在，对于DataAccess的代码，或许再也不需要考虑死锁问题了。 



-- ===========================================
-- 获取阻塞的 session_id 及阻塞时间
DECLARE @tb_block TABLE(
    top_blocking_session_id smallint,
    session_id smallint,
    blocking_session_id smallint,
    wait_time int,
    Level int,
    blocking_path varchar(8000),
    PRIMARY KEY(
        session_id, blocking_session_id)
)
INSERT @tb_block(
    session_id,
    blocking_session_id,
    wait_time)
SELECT
    session_id,
    blocking_session_id,
    wait_time = MAX(wait_time)
FROM sys.dm_exec_requests
WHERE blocking_session_id > 0
GROUP BY session_id, blocking_session_id

-- ===========================================
-- 处理阻塞的 session_id 之间的关系
DECLARE
    @Level int
SET @Level = 1

INSERT @tb_block(
    session_id, top_blocking_session_id, blocking_session_id,
    Level, blocking_path)
SELECT DISTINCT
    blocking_session_id, blocking_session_id, 0,
    @Level, RIGHT(100000 + blocking_session_id, 5)
FROM @tb_block A
WHERE NOT EXISTS(
        SELECT * FROM @tb_block
        WHERE session_id = A.blocking_session_id)
WHILE @@ROWCOUNT > 0
BEGIN
    SET @Level = @Level + 1
    UPDATE A SET
        top_blocking_session_id = B.top_blocking_session_id,
        Level = @Level,
        blocking_path = B.blocking_path 
            + RIGHT(100000 + A.session_id, 5)
    FROM @tb_block A, @tb_block B
    WHERE A.blocking_session_id = B.session_id
        AND B.Level = @Level - 1
END

-- ===========================================
-- 如果只要显示阻塞时间超过多少毫秒的记录，可以在这里做一个过滤
-- 这里假设阻塞时间必须超过 1 秒钟(1000毫秒)
DELETE A 
FROM @tb_block A
WHERE NOT EXISTS(
        SELECT * FROM @tb_block
        WHERE top_blocking_session_id =A.top_blocking_session_id
            AND wait_time >= 1000)

-- ===========================================
-- 使用 DBCC INPUTBUFFER 获取阻塞进程的 T-SQL 脚本
DECLARE @tb_block_sql TABLE(
    id int IDENTITY,
    EventType nvarchar(30),
    Parameters int,
    EventInfo nvarchar(4000),
    session_id smallint)
DECLARE
    @session_id smallint
DECLARE tb CURSOR LOCAL STATIC FORWARD_ONLY READ_ONLY
FOR
SELECT DISTINCT
    session_id
FROM @tb_block
OPEN tb
FETCH tb INTO @session_id
WHILE @@FETCH_STATUS = 0
BEGIN
    INSERT @tb_block_sql(
        EventType, Parameters, EventInfo)
    EXEC(N'DBCC INPUTBUFFER(' + @session_id + ') WITH NO_INFOMSGS')
    IF @@ROWCOUNT > 0
        UPDATE @tb_block_sql SET
            session_id = @session_id
        WHERE IDENTITYCOL = @@IDENTITY

    FETCH tb INTO @session_id
END
CLOSE tb
DEALLOCATE tb

-- ===========================================
-- 显示阻塞进程信息
;WITH
BLK AS(
    SELECT
        A.top_blocking_session_id,
        A.session_id,
        A.blocking_session_id,
        A.Level,
        A.blocking_path,
        SQL = B.EventInfo
    FROM @tb_block A
        LEFT JOIN @tb_block_sql B
            ON A.session_id = B.session_id
)
SELECT
--    BlockPath = REPLICATE(' ', Level * 2 - 2)
--            + '|-- '
--            + RTRIM(session_id),
    BLK.top_blocking_session_id,
    BLK.session_id,
    BLK.blocking_session_id,
    BLK.Level,
    wait_type = P.waittype,
    wait_time = P.waittime,
    last_wait_type = P.lastwaittype,
    wait_resource = P.waitresource,
    P.login_time,
    P.last_batch,
    P.open_tran,
    P.status,
    host_name = P.hostname,
    P.program_name,
    P.cmd,
    login_name = P.loginame,
    BLK.SQL,
    current_sql = T.text,
    current_run_sql = SUBSTRING(T.text,
            P.stmt_start / 2 + 1,
            CASE
                WHEN P.stmt_end = -1 THEN LEN(T.text)
                ELSE (P.stmt_end - P.stmt_start) / 2+1
            END)
FROM BLK
    -- 简省代码起见，直接引用 sysprocess, 读者可以改为引用前述介绍的“查询进程"的脚本进行替换
    INNER JOIN master.dbo.sysprocesses P
        ON BLK.session_id = P.spid
    OUTER APPLY sys.dm_exec_sql_text(P.sql_handle) T
ORDER BY BLK.top_blocking_session_id, BLK.blocking_path