数据库死锁的解决办法
一、破坏死锁的四个必要条件
二、如果发生死锁，通过SQL语句关闭当前发生死锁的数据库进程
USE master --不能用 KILL 来取消您自己的进程。
GO
/****** Object: StoredProcedure [dbo].[p_Centaline_Who_Lock] Script Date: 03/23/2016 11:54:38 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[p_killspid]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[p_killspid]
GO
create proc p_killspid
@dbname varchar(200) --要关闭进程的数据库名
as
declare @sql nvarchar(500)
declare @spid nvarchar(20)
declare #tb cursor for
select spid=cast(spid as varchar(20)) from master..sysprocesses where dbid=db_id(@dbname)
open #tb
fetch next from #tb into @spid
while @@fetch_status=0
begin
exec('kill '+@spid)
fetch next from #tb into @spid
end
close #tb
deallocate #tb
go
调用
exec p_killspid '需要关闭进程的数据库名' 当强制关闭进程后，死锁也就没有了
二、查看当前数据库下锁的情况
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='KF-BJAgencyAtt'
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;
--SELECT * FROM #Lock
DROP Table #Who;
DROP Table #Lock;
查看死锁实例
BEGIN tran
UPDATE B SET age=age+1
WaitFor Delay '00:00:05';
SELECT * FROM A --执行完之后有结果
在新建一个查询，执行下面sql语句
BEGIN tran
UPDATE a SET dd=dd+1
WaitFor Delay '00:00:05';
SELECT * FROM B
ROLLBACK tran --当执行的时候会一直执行下去
查看出现死锁语句
