The solution to the suspended death obstruction of LIS server

Server blocking is a very headache, how many days and nights, just because of the call of the server, I bolted to the hospital to kill the "blocking process" went.

After finishing ISO15189, I want to solve this problem.

One, automatically unlock.

Use the SELECT @ @LOCK_TIMEOUT command to see the time-out period for the current database lock, in milliseconds. If "1", the representative does not have a timeout, the server will always be in the lock state.

So the first way I can think of is "SET lock_timeout 30000″." Now that the server has given such a function, I might as well use it directly. In accordance with the above set, if the lock lasted 30 seconds, then automatically unlock!

The imagination is very good, but the reality is very cruel, the next day, I still ran to kill the process to go! The blocking process will always be there!

Ii. automatic end of blocking process

The following is an SQL stored procedure that ends the blocking process:
--end of SQL blocking process%%%%%%%%%%%%%%%%%%%%%%

The code is as follows

Copy Code

CREATE PROCEDURE Sp_kill_lockprocess As Begin SET NOCOUNT on declare @spid int, @bl int, @intTransactionCountOnEntry int, @intRowcount int, @intCountProperties int, @intCounter int, @sSql nvarchar (200) CREATE TABLE #tmp_lock_who ( ID int identity (1,1), SPID smallint, BL smallint) IF @ @ERROR <>0 return @ @ERROR Insert into #tmp_lock_who (SPID,BL) Select 0, blocked From (SELECT * from sysprocesses where blocked>0) A Where NOT EXISTS ( SELECT * FROM (SELECT * from sysprocesses where blocked>0) b where A.blocked=spid ) Union select spid,blocked from sysprocesses where blocked>0 IF @ @ERROR <>0 return @ @ERROR --Find the number of records in a temporary table Select @intCountProperties = Count (*), @intCounter = 1 From #tmp_lock_who IF @ @ERROR <>0 return @ @ERROR While @intCounter Begin --Take the first www.111cn.net record Select @spid = spid, @bl = bl From #tmp_lock_who where Id = @intCounter Begin If @spid =0 Begin Set @sSql = ' kill ' + CAST (@bl as VARCHAR (10)) EXEC sp_executesql @sSql End End --The loop pointer moves down Set @intCounter = @intCounter + 1 End drop table #tmp_lock_who SET NOCOUNT off return 0 End Go

After the above statement is executed, the stored procedure sp_kill_lockprocess can be found under the master database.

Add job killblocking to SQL Server Agent, set execution time to once every 10 minutes, fix it!

Test, every day can kill three blocking process, our LIS database Ah, really rotten.

If you want to trace the cause of the blockage further, write the output to the log.