SQL Server --- process Deadlock Detection and removal, SQL Server ---

SQL Server --- process Deadlock Detection and removal, SQL Server ---
SQLServer --- process Deadlock Detection and Removal

Recently, I encountered such a situation in the evaluation system of a college. When the number of data centers reaches the upper limit, I encountered a situation where I could not submit the evaluation. After encountering the problem, we immediately discovered the deadlock, so we found some information on the Internet and found the stored procedures of the database process Deadlock Detection and removal in SQL Server, as follows: (share with you)

CREATE proc [dbo]. [p_lockinfo] @ kill_lock_spid bit = 1, -- whether to kill the deadlock process, 1 to kill, 0 to 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 as declare @ count int, @ s nvarchar (1000), @ I int select id = identity (int,), 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, accumulated CPU time = cpu, login Time = login_time, number of opened transactions = open_tran, Process status = status, site name = hostname, application name = program_name, workstation process ID = hostprocess, domain name = nt_domain, NIC address = net_address into # t from (select flag = 'deadlocked process', spid, kpid,. blocked, dbid, uid, loginame, cpu, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address, s1 =. spid, s2 = 0 from master .. sysprocesses a join (select blocked frommaster .. sysprocesses group by blocked) B on. spid = B. blocked where. blocked = 0 union all select '| _ victim _>', spid, kpid, blocked, dbid, uid, loginame, cpu, login_time, open_tran, status, hostname, program_name, hostprocess, nt_domain, net_address, s1 = blocked, s2 = 1 from master .. sysprocesses a where blocked <> 0) a order by s1, s2 select @ count =rowcount, @ I = 1 if @ count = 0 and @ show_spid_if_nolock = 1 begin insert # t select Sign = '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 @ count =rowcount end if @ count> 0 begin create table # t1 (id int identity (), a nvarchar (30), B Int, eventInfo nvarchar (255) if @ kill_lock_spid = 1 begin declare @ spid varchar (10), @ sign varchar (10) while @ I <= @ count begin select @ spid = process ID, @ flag = flag from # t where id = @ I insert # t1 exec ('dbcc inputbuffer ('+ @ spid + ')') if @ sign = 'deadlocked process' exec ('Kill '+ @ spid) set @ I = @ I + 1 end else while @ I <= @ count begin select @ s = 'dbcc inputbuffer ('+ cast (process ID as varchar) + ') 'From # t where id = @ I insert # t1 exec (@ s) set @ I = @ I + 1 end select. *, process SQL statement = B. eventInfo from # t a join # t1 B on. id = B. id end

The cause of this deadlock is that frequent updates and frequent queries cause deadlocks when using SqlServer.

Solution: add non-clustered indexes to the table that is frequently operated.