查询SQL死锁
2021/11/9 2:17:17
本文主要是介绍查询SQL死锁,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
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
print @@ERROR
INSERT INTO #tmp_lock_who
( spid ,
bl
)
SELECT 0 ,
blocked
FROM ( SELECT *
FROM master..sysprocesses
WHERE blocked > 0
) a
WHERE NOT EXISTS ( SELECT *
FROM ( SELECT *
FROM master..sysprocesses
WHERE blocked > 0
) b
WHERE a.blocked = spid )
UNION
SELECT spid ,
blocked
FROM master..sysprocesses
WHERE blocked > 0
IF @@ERROR <> 0
print @@ERROR
-- 找到临时表的记录数
SELECT @intCountProperties = COUNT(*) ,
@intCounter = 1
FROM #tmp_lock_who
IF @@ERROR <> 0
print @@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
print 0
这篇关于查询SQL死锁的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!
- 2024-05-28SpringBoot3.2更新声明!
- 2024-05-28中外程序员到底有啥区别?
- 2024-05-25外企也半夜发布上线吗?
- 2024-05-24鸿蒙原生应用再新丁!芒果TV 入局鸿蒙
- 2024-05-22基本概念
- 2024-05-22检索数据
- 2024-05-22排序数据
- 2024-05-22基础过滤数据
- 2024-05-22通过逻辑操作符过滤数据
- 2024-05-22通过通配符过滤数据