SQL Server死锁总结

2022-07-27 00:00:00 执行 事务 请求 死锁 资源

 1. 死锁原理

    根据操作系统中的定义:死锁是指在一组进程中的各个进程均占有不会释放的资源,但因互相申请被其他进程所站用不会释放的资源而处于的一种等待状态。

    死锁的四个必要条件:
互斥条件(Mutual exclusion):资源不能被共享,只能由一个进程使用。
请求与保持条件(Hold and wait):已经得到资源的进程可以再次申请新的资源。
非剥夺条件(No pre-emption):已经分配的资源不能从相应的进程中被强制地剥夺。
循环等待条件(Circular wait):系统中若干进程组成环路,该环路中每个进程都在等待相邻进程正占用的资源。

对应到SQL Server中,当在两个或多个任务中,如果每个任务锁定了其他任务试图锁定的资源,此时会造成这些任务阻塞,从而出现死锁;这些资源可能是:单行(RID,堆中的单行)、索引中的键(KEY,行锁)、页(PAG8KB)、区结构(EXT,连续的8)、堆或B(HOBT) 、表(TAB,包括数据和索引)、文件(File,数据库文件)、应用程序专用资源(APP)、元数据(METADATA)、分配单元(Allocation_Unit)、整个数据库(DB)一个死锁示例如下图所示:


    说明:T1T2表示两个任务;R1R2表示两个资源;由资源指向任务的箭头(R1->T1R2->T2)表示该资源被改任务所持有;由任务指向资源的箭头(T1->S2T2->S1)表示该任务正在请求对应目标资源;
    其满足上面死锁的四个必要条件:
(1).互斥:资源S1S2不能被共享,同一时间只能由一个任务使用;
(2).请求与保持条件:T1持有S1的同时,请求S2T2持有S2的同时请求S1
(3).非剥夺条件:T1无法从T2上剥夺S2T2也无法从T1上剥夺S1
(4).循环等待条件:上图中的箭头构成环路,存在循环等待。

 

2. 死锁排查

(1). 使用SQL Server的系统存储过程sp_whosp_lock,可以查看当前数据库中的锁情况;进而根据objectID(@objID)(SQL Server 2005)/ object_name(@objID)(Sql Server 2000)可以查看哪个资源被锁,用dbcc ld(@blk),可以查看后一条发生给SQL ServerSql语句;

CREATE Table #Who(spid int,
    ecid 
int,
    status 
nvarchar(50),
    loginname 
nvarchar(50),
    hostname 
nvarchar(50),
    blk 
int,
    dbname 
nvarchar(50),
    cmd 
nvarchar(50),
    request_ID 
int);

CREATE Table #Lock(spid int,
    dpid 
int,
    objid 
int,
    indld 
int,
    
[Type] nvarchar(20),
    Resource 
nvarchar(50),
    Mode 
nvarchar(10),
    Status 
nvarchar(10)
);

INSERT INTO #Who
    
EXEC sp_who active  --看哪个引起的阻塞,blk 
INSERT INTO #Lock
    
EXEC sp_lock  --看锁住了那个资源id,objid 

DECLARE @DBName nvarchar(20);
SET @DBName='NameOfDataBase'

SELECT #Who.* FROM #Who WHERE dbname=@DBName
SELECT #Lock.* FROM #Lock
    
JOIN #Who
        
ON #Who.spid=#Lock.spid
            
AND dbname=@DBName;

--后发送到SQL Server的语句
DECLARE crsr Cursor FOR
    
SELECT blk FROM #Who WHERE dbname=@DBName AND blk<>;
DECLARE @blk int;
open crsr;
FETCH NEXT FROM crsr INTO @blk;
WHILE (@@FETCH_STATUS = )
BEGIN;
    
dbcc inputbuffer(@blk);
    
FETCH NEXT FROM crsr INTO @blk;
END;
close crsr;
DEALLOCATE crsr;

--锁定的资源
SELECT #Who.spid,hostname,objid,[type],mode,object_name(objid) as objName FROM #Lock
    
JOIN #Who
        
ON #Who.spid=#Lock.spid
            
AND dbname=@DBName
    
WHERE objid<>;

DROP Table #Who;
DROP Table #Lock;

相关文章