查询sql server进程死锁方案

 

第一步：在master数据库上执行

SELECT
a.spid ,
a.blocked ,
lastwaittype = RTRIM( a.lastwaittype ),
waitresource = RTRIM( a.waitresource ),
a.waittime ,
a.[dbid] ,
a.cpu ,
a.physical_io ,
a.login_time ,
a.last_batch ,
a.ecid ,
a.open_tran ,
[status] = RTRIM( a.[status] ),
hostname = RTRIM( a.hostname ),
[program_name] = RTRIM( a.[program_name] ),
hostprocess = RTRIM( a.hostprocess ),
cmd = RTRIM( a.cmd ),
loginame = RTRIM( a.loginame ),
b.[text]
FROM sys.sysprocesses a with(nolock)
OUTER APPLY sys.dm_exec_sql_text( a.sql_handle) b
LEFT JOIN sys. dm_exec_requests c with(nolock)
ON a .spid = c.session_id
WHERE a .spid > 50
and a .spid <> @@spid
and a .cmd <> 'AWAITING COMMAND'
order by spid

第二部：查看是哪个数据库上出现了死锁

Select db_name(9)     

第三部长见情况：

insert和update都会产生范围锁。会产生死锁和阻塞

 

SELECT
createtime = GETDATE(),
a.spid ,
a.blocked ,
lastwaittype = RTRIM( a.lastwaittype ),
waitresource = RTRIM( a.waitresource ),
a.waittime ,
a.[dbid] ,
a.cpu ,
a.physical_io ,
a.login_time ,
a.last_batch ,
a.ecid ,
a.open_tran ,
[status] = RTRIM( a.[status] ),
hostname = RTRIM( a.hostname ),
[program_name] = RTRIM( a.[program_name] ),
hostprocess = RTRIM( a.hostprocess ),
cmd = RTRIM( a.cmd ),
loginame = RTRIM( a.loginame ),
b.[text] ,
d.query_plan ,
statement_text = CAST( '<?query --' + CHAR ( 13) + CHAR (10 ) +
SUBSTRING
(
b.[text] ,
(c. statement_start_offset / 2) + 1 ,
((
CASE c .statement_end_offset
WHEN - 1 THEN DATALENGTH(b .[text] )
ELSE c .statement_end_offset
END - c. statement_start_offset
) / 2) + 1
) + CHAR ( 13) + CHAR (10) + '--?>' AS XML)
FROM sys .sysprocesses a
OUTER APPLY sys. dm_exec_sql_text( a . sql_handle) b
LEFT JOIN sys. dm_exec_requests c
ON a .spid = c . session_id
OUTER APPLY sys. dm_exec_query_plan( c . plan_handle) d
WHERE a.spid > 50
and a.spid <> @@spid
and a.blocked >0
and a .cmd <> 'AWAITING COMMAND'