SqlServer 1.6

--概要：SqlService常用语句[锁]

--设计者：DuanXuWen

--版本：0.1

--修改者：

--修改时间：

 

--①检测死锁[创建-使用]

-----------------------------------------------------------------------------------------------------------------------

--如果发生死锁了，我们怎么去检测具体发生死锁的是哪条SQL语句或存储过程？

--这时我们可以使用以下存储过程来检测，就可以查出引起死锁的进程和SQL语句。

--SQL Server自带的系统存储过程sp_who和sp_lock也可以用来查找阻塞和死锁, 但没有这里介绍的方法好用。

--[创建]----------------------

USE [master]

GO

CREATE PROCEDURE sp_who_lock

AS

BEGIN

DECLARE @spid INT,@bl INT,@intTransactionCountOnEntry INT,@intRowcount INT,@intCountProperties INT,@intCounter INT

CREATE TABLE #tmp_lock_who

(

id INT IDENTITY(1,1),

spid SMALLINT,

bl SMALLINT

)

IF @@ERROR<>0 RETURN @@ERROR

INSERT INTO #tmp_lock_who(spid,bl)

SELECT 0 ,blocked FROM (SELECT * FROM sysprocesses WHERE  blocked>0 ) a

WHERE NOT EXISTS(SELECT * FROM (SELECT * FROM sysprocesses WHERE  blocked>0 ) b

WHERE a.blocked=spid)

UNION

SELECT spid,blocked FROM sysprocesses WHERE  blocked>0

IF @@ERROR<>0 RETURN @@ERROR

-- 找到临时表的记录数

SELECT @intCountProperties = Count(*),@intCounter = 1 FROM #tmp_lock_who

IF @@ERROR<>0 RETURN @@ERROR

IF @intCountProperties=0

SELECT '现在没有阻塞和死锁信息' AS MESSAGE

-- 循环开始

WHILE @intCounter <= @intCountProperties

BEGIN

-- 取第一条记录

SELECT  @spid = spid,@bl = bl

FROM #tmp_lock_who WHERE Id = @intCounter

BEGIN

IF @spid =0

SELECT '引起数据库死锁的是: '+ CAST(@bl AS VARCHAR(10)) + '进程号,其执行的SQL语法如下'

ELSE

SELECT '进程号SPID：'+ CAST(@spid AS VARCHAR(10))+ '被' + '进程号SPID：'+ CAST(@bl AS VARCHAR(10)) +'阻塞,其当前进程执行的SQL语法如下'

DBCC INPUTBUFFER (@bl)

END

-- 循环指针下移

SET @intCounter = @intCounter + 1

END

DROP TABLE #tmp_lock_who

RETURN 0

END

--[使用]----------------------

EXEC sp_who_lock

 

 

--②杀死锁和进程[创建-使用]

-----------------------------------------------------------------------------------------------------------------------

--如何去手动的杀死进程和锁？最简单的办法，重新启动服务。但是这里要介绍一个存储过程，通过显式的调用，可以杀死进程和锁。

--[创建]----------------------

USE [master]

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  'newdbpy'

 

--③查看锁信息

-----------------------------------------------------------------------------------------------------------------------

--如何查看系统中所有锁的详细信息？在企业管理管理器中，我们可以看到一些进程和锁的信息，这里介绍另外一种方法。

 

CREATE TABLE #t(req_spid INT,obj_name SYSNAME)

DECLARE @s NVARCHAR(4000),@rid INT,@dbname SYSNAME,@id INT,@objname SYSNAME

DECLARE tb CURSOR FOR

SELECT DISTINCT req_spid,dbname=db_name(rsc_dbid),rsc_objid

FROM MASTER..syslockinfo WHERE rsc_type in(4,5)

OPEN tb

FETCH NEXT FROM tb INTO @rid,@dbname,@id

WHILE @@fetch_status=0

BEGIN

SET @s='select @objname=name from ['+@dbname+']..sysobjects where id=@id'

EXEC sp_executesql @s,N'@objname sysname out,@id int',@objname OUT,@id

INSERT INTO #t VALUES(@rid,@objname)

FETCH NEXT FROM tb INTO @rid,@dbname,@id

END

CLOSE tb

DEALLOCATE tb

SELECT 进程id=a.req_spid,数据库=db_name(rsc_dbid),

类型=CASE rsc_type WHEN 1 THEN 'NULL 资源（未使用）'

WHEN 2 THEN '数据库'

WHEN 3 THEN '文件'

WHEN 4 THEN '索引'

WHEN 5 THEN '表'

WHEN 6 THEN '页'

WHEN 7 THEN '键'

WHEN 8 THEN '扩展盘区'

WHEN 9 THEN 'RID（行 ID)'

WHEN 10 THEN '应用程序'

END

,对象id=rsc_objid

,对象名=b.obj_name

,rsc_indid

FROM MASTER..syslockinfo a LEFT JOIN #t b ON a.req_spid=b.req_spid

GO

DROP TABLE #t