This article describes how to identify an update (Blocking) Lock by scheduling a job to run every few minutes and checks if a blocking lock on an object is held from the previous execution of the job to the current job execution

Sometimes, in order to identify a Long time Lock placed on an object that affects the application behavior we need something more powerful than the simple sp_lock because Sp_lock only lists the current locks placed on objects and does not show the locking time. Here I suggest a mechanism that can be scheduled to run every 5 or 10 minutes in order to check if an update (Exclusive Lock) was held on an object for the last 5 or 10 minutes

The first thing to do is to create a table (called BlockingLocks)  in the format of the result Set of the sp_lock system procedure By executing :

Use master 
Go
Create table BlockingLocks 
( spid smallint,
  dbid int,
  ObjId int ,
  IndId int,
  Type  varchar(4),
  Resource varchar(16),
  Mode varchar(8),
  Status varchar(5))
go
-- Next comes the procedure's source code:
Create procedure sp_Identify_Blocking_Locks
as
begin
declare @cntBlocks int
set noCount on 
-- create a temporary table to hold current locks status reported by sp_lock
Create table #CurrLocks 
( spid smallint,
  dbid int,
  ObjId int ,
  IndId int,
  Type varchar(4),
  Resource varchar(16),
  Mode varchar(8),
  Status varchar(5)
)
-- populate temp table
insert #CurrLocks exec sp_Lock
-- select into counter the number of objects still in lock from prev time
select @cntBlocks = count (distinct b.objId)
 from BlockingLocks a join #CurrLocks b
   on a.ObjId = b.ObjId
 where b.Mode like '%X%'
truncate table BlockingLocks
-- fill Blocking locks with the new lock data 
Insert BlockingLocks 
  select * from #CurrLocks where Mode like '%X%'
Drop table #CurrLocks
return (@cntBlocks)
end
go

If the result returned by procedure is > 0 then locks on objects that exceeded job's time interval exist!

Example of a call to the procedure

declare @cnt int
exec @cnt = sp_Identify_Blocking_Locks
print @cnt 

Conclusion

The process I showed here can be used as a tool for identifying blocking locks. By scheduling a job to run this procedure. If a positive result is returned, the DBA must look at the BlockingLocks table to identify the blocking objects.

Eli Leiba works at Israel Electric Company as a Senior Application DBA in Oracle and MS SQL Server. He also has certifications from Microsoft and BrainBench in Oracle and SQL Server database administration and implementation. Mr. Leiba holds a B.S. in Computer Science since 1991 and has 13 years' experience working in the databases field. Additionally Mr. Leiba teaches SQL Server DBA and Development courses at Microsoft CTEC and also serves as a senior database consultant for several Israeli start-up companies. (e-mail:iecdba@hotmail.com)