/ * - Handling dead lock
View the current process, or deadlock process, and automatically kill the death process
Because it is against deadlocks, if there is a deadlock process, you can only view the deadlock process. Of course, you can control the parameters, no matter whether there is a dead lock, only the deadlock process is only
Thank: Caiyunxia, JIANGOPEN two provided reference information
- Zou Jian 2004.04 (Please keep this information) - * /
/ * - Call example
EXEC P_LOCKINFO - * / CREATE PROC P_LOCKINFO @ Kill_Lock_SPID bit = 1, - Do you kill deadlocks, 1 kill, 0 only display @show_spid_if_nolock bit = 1 - If there is no deadlock, do you display a normal process? Information, 1 shows, 0 does not display Asset NoCount Ondeclare @count int, @ s nvarchar (1000), @ i Intselect ID = Identity (int, 1, 1), logo, 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 = Open_TRAN, Process Status = STATUS, Workstation Name = hostname, application name = program_name, workstation process id = hostprocess, domain name = NT_DOMAIN, NIC address = net_addressinto #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 = 0 from master..sysprocesses a join (select blocked from master..sysprocesses group by blocked) b on a.spid = b.blocked where a.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 = Block, 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 = 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 @count = @@ rowcountend
