SQL Server死锁总结-技术开发it知识库-Ubuntu系统_Ubuntu服务器

1. 死锁原理

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

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

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

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

2. 死锁排查

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

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<>0;

DECLARE @blk int;

open crsr;

FETCH NEXT FROM crsr INTO @blk;

WHILE (@@FETCH_STATUS = 0)

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<>0;

DROP Table #Who;

DROP Table #Lock;

it知识库：SQL Server死锁总结，转载需保留来源！

郑重声明：本文版权归原作者所有，转载文章仅为传播更多信息之目的，如作者信息标记有误，请第一时间联系我们修改或删除，多谢。

相关文章阅读