--概要: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