SQL Server---process deadlock avira

SQL Server---process deadlock avira

Recently, in a college evaluation system encountered such a situation, when the number of computer rooms reached the ceiling, the evaluation process encountered a situation can not be submitted. After encountering the problem people first discovered is because of the deadlock caused, so found some information on the Internet to find out in SQL Server on the database process deadlock killing the stored procedures, as follows: (Share to everyone)

CREATE proc [dbo]. [P_lockinfo] @kill_lock_spid bit=1,--whether to kill the deadlock process, 1 kill, 0 show only @show_spid_if_nolock bit=1--if there is no deadlock process, whether to display normal process information, 1 display, 0 does not display a s declare @count int,@s nvarchar (+), @i int Select id=identity (int,1,1), flag, process id=spid, thread id=kpid, block process id=blocked, database id= dbid, database name =db_name (dbid), user id=uid, user name =loginame, Cumulative CPU time =CPU, login time =login_time, open transaction number =open_tran, process status =status, workstation name = Hostname, application name =program_name, workstation process id=hostprocess, domain name =nt_domain, nic address =net_address into #t from (SELECT flag = ' deadlock process ', spid, 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 Frommaster: sysprocesses GROUP by blocked) B on a.spid=b.blocked where A.blocked=0union all select ' |_ victim _> ', spid,kpid,blocked,dbi D,uid,loginame,cpu,login_time,open_tran, Status,hostname,program_name,hostprocess,nt_domain,net_address, s1= Blocked,s2=1from Master. sysprocesses a where blocked<>0) a order by S1,s2 SeleCT @[email protected] @rowcount, @i=1 if @count =0 and @show_spid_if_nolock =1begin insert #t Select flag = ' normal process ', spid, Kpid,blocked,dbid,db_name (dbid), Uid,loginame,cpu,login_time, open_tran,status,hostname,program_name,hostprocess , nt_domain,net_address from Master. sysprocesses set @[email protected] @rowcount End If @count >0 begin CREATE TABLE #t1 (ID int identity (), a nvarch AR (+), b int,eventinfo nvarchar (255)) If @kill_lock_spid =1begin declare @spid varchar (10), @ flag varchar (max) while @i<[ Email protected] BEGIN Select @spid = Process id,@ flag = flag from #t where [email protected] insert #t1 exec (' DBCC INP Utbuffer (' [email protected]+ ') ') if @ flag = ' deadlock process ' exec (' kill ' [email protected]) set @[email protected] +1 end END Else while @i<[email protected] begin select @s= ' DBCC INPUTBUFFER (' +cast (process ID as varchar) + ') ' from # t where [email protected] insert #t1 EXEC (@s) Set @[email protected]+1 End Select A.*, the SQL statement of the process =b.eve Ntinfo From #t a join #t1 B on A.id=b.id end  

The cause of this deadlock is that when using SQL Server, it is found that frequent updates and frequent queries cause deadlocks in high concurrency situations.

The workaround is to add a nonclustered index on the table that is frequently manipulated.

SQL Server---process deadlock avira