如何查看SQL表死锁
来源:互联网 发布:网络侦探妖狐兽进化 编辑:程序博客网 时间:2024/05/09 08:00
/***********************************************************
**如何查看死锁的表:方法一**********************************
************************************************************/
SELECT request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id) tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
/***********************************************************
**如何查看死锁的表:方法二**********************************
************************************************************/
IF EXISTS ( SELECT 1
FROM sys.procedures
WHERE name = 'sp_who_lock' )
BEGIN
DROP PROCEDURE sp_who_lock
END
GO
CREATE PROCEDURE sp_who_lock
AS
BEGIN
DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT
SET @lock = 0
CREATE TABLE #temp_who_lock
(
id INT IDENTITY(1, 1) ,
spid INT ,
blk INT
)
IF @@error <> 0
RETURN @@error
INSERT INTO #temp_who_lock
( spid ,
blk
)
SELECT 0 ,
blocked
FROM ( SELECT *
FROM master..sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS ( SELECT *
FROM master..sysprocesses
WHERE a.blocked = spid
AND blocked > 0 )
UNION
SELECT spid ,
blocked
FROM master..sysprocesses
WHERE blocked > 0
IF @@error <> 0
RETURN @@error
SELECT @count = COUNT(*) ,
@index = 1
FROM #temp_who_lock
IF @@error <> 0
RETURN @@error
IF @count = 0
BEGIN
SELECT '没有阻塞和死锁信息'
RETURN 0
END
WHILE @index <= @count
BEGIN
IF EXISTS ( SELECT 1
FROM #temp_who_lock a
WHERE id > @index
AND EXISTS ( SELECT 1
FROM #temp_who_lock
WHERE id <= @index
AND a.blk = spid ) )
BEGIN
SET @lock = 1
SELECT @spid = spid ,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
SELECT @spid ,
@blk
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
END
SET @index = @index + 1
END
IF @lock = 0
BEGIN
SET @index = 1
WHILE @index <= @count
BEGIN
SELECT @spid = spid ,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
IF @spid = 0
SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID:' + CAST(@spid AS VARCHAR(10))
+ '被' + '进程号SPID:'
+ CAST(@blk AS VARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
SET @index = @index + 1
END
END
DROP TABLE #temp_who_lock
RETURN 0
END
GO
*/
/***********************************************************
**如何Kill死锁的表进程**************************************
************************************************************/
**如何查看死锁的表:方法一**********************************
************************************************************/
SELECT request_session_id spid ,
OBJECT_NAME(resource_associated_entity_id) tableName
FROM sys.dm_tran_locks
WHERE resource_type = 'OBJECT'
/***********************************************************
**如何查看死锁的表:方法二**********************************
************************************************************/
IF EXISTS ( SELECT 1
FROM sys.procedures
WHERE name = 'sp_who_lock' )
BEGIN
DROP PROCEDURE sp_who_lock
END
GO
CREATE PROCEDURE sp_who_lock
AS
BEGIN
DECLARE @spid INT
DECLARE @blk INT
DECLARE @count INT
DECLARE @index INT
DECLARE @lock TINYINT
SET @lock = 0
CREATE TABLE #temp_who_lock
(
id INT IDENTITY(1, 1) ,
spid INT ,
blk INT
)
IF @@error <> 0
RETURN @@error
INSERT INTO #temp_who_lock
( spid ,
blk
)
SELECT 0 ,
blocked
FROM ( SELECT *
FROM master..sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS ( SELECT *
FROM master..sysprocesses
WHERE a.blocked = spid
AND blocked > 0 )
UNION
SELECT spid ,
blocked
FROM master..sysprocesses
WHERE blocked > 0
IF @@error <> 0
RETURN @@error
SELECT @count = COUNT(*) ,
@index = 1
FROM #temp_who_lock
IF @@error <> 0
RETURN @@error
IF @count = 0
BEGIN
SELECT '没有阻塞和死锁信息'
RETURN 0
END
WHILE @index <= @count
BEGIN
IF EXISTS ( SELECT 1
FROM #temp_who_lock a
WHERE id > @index
AND EXISTS ( SELECT 1
FROM #temp_who_lock
WHERE id <= @index
AND a.blk = spid ) )
BEGIN
SET @lock = 1
SELECT @spid = spid ,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
SELECT '引起数据库死锁的是: ' + CAST(@spid AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
SELECT @spid ,
@blk
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
END
SET @index = @index + 1
END
IF @lock = 0
BEGIN
SET @index = 1
WHILE @index <= @count
BEGIN
SELECT @spid = spid ,
@blk = blk
FROM #temp_who_lock
WHERE id = @index
IF @spid = 0
SELECT '引起阻塞的是:' + CAST(@blk AS VARCHAR(10))
+ '进程号,其执行的SQL语法如下'
ELSE
SELECT '进程号SPID:' + CAST(@spid AS VARCHAR(10))
+ '被' + '进程号SPID:'
+ CAST(@blk AS VARCHAR(10))
+ '阻塞,其当前进程执行的SQL语法如下'
DBCC inputbuffer(@spid)
DBCC inputbuffer(@blk)
SET @index = @index + 1
END
END
DROP TABLE #temp_who_lock
RETURN 0
END
GO
/*
EXEC sp_who_lock*/
/***********************************************************
**如何Kill死锁的表进程**************************************
************************************************************/
kill spid
0 0
- 如何查看SQL表死锁
- SQL查看死锁+清理死锁
- sql server 查看表的死锁和Kill 死锁进程
- sql server 查看表的死锁和Kill 死锁进程
- sql server 查看表的死锁和Kill 死锁进程
- sql server 查看表的死锁和Kill 死锁进程
- sql server 查看表的死锁和Kill 死锁进程
- sql server 查看表的死锁和Kill 死锁进程
- sql server 查看表的死锁和Kill 死锁进程
- Sql 查看、处理死锁!
- sql server 查看死锁
- SQL Server 查看死锁
- Java如何查看死锁?
- 如何查看死锁时在执行的SQL语句
- 查看数据库死锁,并查看死锁SQL,杀死死锁进程
- 查看死锁的SQL语句
- 查看数据库死锁SQL语句
- 如何查看当前哪个表出现了死锁?
- hdu4503(概率的逆向思维)
- Linux字符串转换函数汇总
- C 语言变参函数解析
- 第十七课 final关键字和abstract关键字
- printf格式符%d、%lld、%llx、%u
- 如何查看SQL表死锁
- edittest按密码输入类型设置
- 在Eclipse中导出jar文件时的配置文件设置
- java 从jar包中读取资源文件
- C语言常见几种排序汇总
- android仿微信向上滑动取消录音解决办法
- socket编程的select模型
- Vim 常用快捷键及键盘图
- Mac系统下Lua开发环境搭建