SQL Server死锁总结.doc

SQL Server 死锁总结 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) 可以查看哪个资源被锁,用 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 # =# 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 ; inputbuffer( ***@blk ); FETCH NEXT FROM crsr INTO ***@blk ; END ;close crsr; DEALLOCATE c