mysql查询数据库阻塞_查看数据库阻塞 | 学步园

--创建递归查询数据库的阻塞

WITH CTE_TABLE(session_id,wait_type,blocking_session_id,resource_type,DATABASE_NAME,resource_lock_partition,request_mode,request_type,request_status,LV)

AS

(

SELECT

A.session_id,

A.wait_type,

A.blocking_session_id,

B.resource_type,

DB_NAME(B.resource_database_id) AS DATABASE_NAME,

B.resource_lock_partition,

B.request_mode,

request_type,

B.request_status,

1

FROM SYS.dm_os_waiting_tasks A

JOIN SYS.dm_tran_locks B ON A.resource_address=B.lock_owner_address

WHERE session_id>50

UNION ALL

SELECT

C.session_id,

C.wait_type,

C.blocking_session_id,

C.resource_type,

C.DATABASE_NAME,

C.resource_lock_partition,

C.request_mode,

C.request_type,

C.request_status,

CTE_TABLE.LV+1

FROM

(

SELECT

A.session_id,

A.wait_type,

A.blocking_session_id,

B.resource_type,

DB_NAME(B.resource_database_id) AS DATABASE_NAME,

B.resource_lock_partition,

B.request_mode,

request_type,

B.request_status

FROM SYS.dm_os_waiting_tasks A

JOIN SYS.dm_tran_locks B ON A.resource_address=B.lock_owner_address

WHERE session_id>50

) AS C JOIN CTE_TABLE ON CTE_TABLE.session_id=C.blocking_session_id

)

SELECT * INTO #TEMPTABLE_1 FROM CTE_TABLE

CREATE TABLE #TEMPTABLE

(

session_id INT,

wait_type NVARCHAR(MAX),

blocking_session_id INT,

resource_type NVARCHAR(MAX),

DATABASE_NAME NVARCHAR(MAX),

resource_lock_partition INT,

request_mode NVARCHAR(MAX),

request_type NVARCHAR(MAX),

request_status NVARCHAR(MAX),

LV INT,

BLOCKED_SQL NVARCHAR(MAX),

BLOCKING_SQL NVARCHAR(MAX)

)

INSERT INTO #TEMPTABLE(session_id,wait_type,blocking_session_id,resource_type,DATABASE_NAME,resource_lock_partition,request_mode,request_type,request_status,LV)

SELECT * FROM #TEMPTABLE_1

DROP TABLE #TEMPTABLE_1

CREATE TABLE #TEMO

(

EVENTTYPE NVARCHAR(100) ,

PARAMETER NVARCHAR(MAX) ,

EVENTINFO NVARCHAR(MAX)

)

--通过游标把查询结果用session_id关联执行的sql语句

DECLARE @SEID INT

DECLARE @SEID_2 INT

DECLARE @SQL NVARCHAR(MAX)

DECLARE @SEIDTEXT NVARCHAR(MAX)

DECLARE @SEID_2TEXT NVARCHAR(MAX)

DECLARE COSU CURSOR FOR SELECT session_id,blocking_session_id FROM #TEMPTABLE

OPEN COSU

FETCH NEXT FROM COSU INTO @SEID,@SEID_2

WHILE @@FETCH_STATUS<>-1

BEGIN

SET @SQL=N'DBCC INPUTBUFFER('+STR(@SEID)+')'

INSERT INTO #TEMO

EXEC SP_SQLEXEC @SQL

SELECT @SEIDTEXT=EVENTINFO FROM #TEMO

DELETE FROM #TEMO

SET @SQL=N'DBCC INPUTBUFFER('+STR(@SEID_2)+')'

INSERT INTO #TEMO

EXEC SP_SQLEXEC @SQL

SELECT @SEID_2TEXT=EVENTINFO FROM #TEMO

UPDATE #TEMPTABLE SET BLOCKED_SQL=@SEIDTEXT,BLOCKING_SQL=@SEID_2TEXT

WHERE session_id=@SEID AND blocking_session_id=@SEID_2

FETCH NEXT FROM COSU INTO @SEID,@SEID_2

END

CLOSE COSU

DEALLOCATE COSU

SELECT * FROM #TEMPTABLE

DROP TABLE #TEMPTABLE

DROP TABLE #TEMO

GO