AI时代，找源码已成为过去式，纪念我过去的十年0(回)
80天前
还有人吗2(回)
98天前会python真的可以为所欲为0(回)
577天前
这里还有人吗2(回)
763天前这里还有人吗0(回)
763天前
每天面对着电脑屏幕，敲打键盘。我所面对的并不只是代码，而是一种生活方式。0(回)
987天前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 |
|
DECLARE @temp_who_lock AS TABLE ( |
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 TOP 1 1 |
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(1), |
@index = 1 |
FROM @temp_who_lock |
|
IF @@error <> 0 |
RETURN @@error |
|
IF @count = 0 |
BEGIN |
SELECT N'没有阻塞和死锁信息' |
|
RETURN 0 |
END |
|
WHILE @index <= @count |
BEGIN |
IF EXISTS ( |
SELECT TOP 1 1 |
FROM @temp_who_lock a |
WHERE id > @index |
AND EXISTS ( |
SELECT TOP 1 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 N'引起数据库死锁的是:' + CAST(@spid AS NVARCHAR(10)) + N'进程号,其执行的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 N'引起阻塞的是:' + CAST(@blk AS NVARCHAR(10)) + N'进程号,其执行的SQL语法如下' |
ELSE |
SELECT N'进程号SPID：' + CAST(@spid AS NVARCHAR(10)) + N'被进程号SPID：' + CAST(@blk AS NVARCHAR(10)) + N'阻塞,其当前进程执行的SQL语法如下' |
|
DBCC INPUTBUFFER (@spid) |
|
DBCC INPUTBUFFER (@blk) |
|
SET @index = @index + 1 |
END |
END |
|
RETURN 0 |
END |
GO |
|
EXEC #sp_who_lock |
