Unlock a locked table
Use masters - must create Go in the Master database
If EXISTS (Select * from dbo.sysObjects where id = Object_id (n '[dbo]. [p_lockinfo]') And ObjectProperty (ID, n'isprocedure ') = 1) Drop Procedure [dbo]. [p_lockinfo] Go
/ * - Handling dead lock
View the current process, or deadlock process, and automatically kill the death process
Because it is targeted, 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 dead lock process
Thank: Caiyunxia, JIANGOPEN two provided reference information
Zou Jian 2004.4 - * /
/ * - 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 asdeclare @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 number = Open_TRAN, process status = status, work station name = Hostname, App Program Name = Program_Name, Workstation Process ID = HostProcess, Domain Name = NT_DOMAIN, NIC Address = Net_Addressinto #t from (SELECT Sign = '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 = Blocked, S2 = 1 from master..xysprocesses 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 = @@ rowcountendif @count> 0begin create Table # T1 (ID Int Id IDENTITY (1, 1), A NVARCHAR (30), B Int, EventInfo NVARCHAR (255) ) If @ Kill_Lock_SPID = 1 Begin Declare @SPID VARCHAR (10), @ @ 标 v VARCHAR (10) While @i <= @ count begin select @ spid = process id, @ 标 = flag from #t where id = @ i insert # T1 EXEC ('DBCC INPUTBUFFER (' @ spid ')') IF @ 标 = 'deadlock process' exec ('kill' @ spid) set @ i = @ i 1 end 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 a. *, process SQL statement = B. Eventinfo from #ta join # T1 b on a.id = B.IDendgo
