Shulou(Shulou.com)05/31 Report--

This article will explain in detail how to query the source of deadlock in sqlserver. The content of the article is of high quality. Therefore, Xiaobian shares it with you as a reference. I hope you have a certain understanding of relevant knowledge after reading this article.

Find the source of SQL Server deadlock and blocking--Find the source of SQL Server deadlock and blocking

The code is as follows:

use mastergodeclare @spid int,@bl intDECLARE s_cur CURSOR FORselect 0 ,blockedfrom (select * from sysprocesses where blocked>0 ) awhere not exists (select * from (select * from sysprocesses where blocked>0 ) bwhere a.blocked=spid)union select spid,blocked from sysprocesses where blocked>0OPEN s_curFETCH NEXT FROM s_cur INTO @spid,@blWHILE @@FETCH_STATUS = 0 initif @ pid =0select 'cause database deadlock is:'+ CAST (@bl AS VARCHAR(10)) + 'process number, which executes SQL syntax as follows'elseselect 'process number SPID: '+ CAST (@spid AS VARCHAR(10))+ 'by' + 'process number SPID: '+ CAST(@bl AS VARCHAR(10)) +'blocking, its current process executes SQL syntax as follows'DBCC INPUTBUFFER (@bl )FETCH NEXT FROM s_cur INTO @spid,@blendCLOSE s_curDEALLOCATE s_cur

View current process, or deadlocked process, and automatically kill dead process-View current process, or deadlocked process, and automatically kill dead process

Because it is for dead, if there is a deadlock process, only the deadlock process can be viewed. Of course, you can control by parameters, whether there is deadlock or not, only to see the deadlock process.

The code is as follows:

create proc p_lockinfo@kill_lock_spid bit=1, --Whether to kill deadlocked processes, 1 kill, 0 show only @show_spid_if_nolock bit=1 --If there are no deadlocked processes, whether to show normal process information, 1 show, 0 do not show asdeclare @count int,@s nvarchar(1000),@i intselect id=identity(int,1,1), flags, process ID=spid, thread ID=kpid, block process ID=blocked, database ID=dbid, dbname =db_name(dbid), userid =uid, username =loginame, cumulative CPU time =cpu, login time =login_time, open transactions =open_tran, process status =status, workstation name =hostname, application name =program_name, workstation processid =hostprocess, domain name =nt_domain, NIC address =net_addressinto #t from(select flag ='deadlocked process', pid,kpid,a.blocked,dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name, hostprocess,nt_domain,net_address,s1=a.spid,s2=0from master.. sysprocesses a join (select blocked from master.. sysprocesses group by blocked)b on a.spid=b.blocked where a.blocked=0union allselect '|_品_', spid, kpid, blocked, dbid,uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address,s1 =blocked,s2=1from master.. sysprocesses a where blocked0)a order by s1,s2

select @count=@@rowcount,@i=1

if @count=0 and @show_pid_if_nolock= 1initiinsert #tselect flag ='normal progression', pid,kpid,blocked,dbid,db_name(dbid),uid,loginame,cpu,login_time,open_tran,status,hostname,program_name,hostprocess,nt_domain,net_address from master.. sysprocessesset @count=@@rowcountend

if @count> 0begin increase table #t1(id int identity(1,1),a nvarchar(30),b Int,EventInfo nvarchar(255))if @kill_lock_spid= 1begin declare @spid varchar(10),@ label varchar(10)while @i

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.