Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 

Identifying Blocking Locks

By Eli Leiba,

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)

Total article views: 12320 | Views in the last 30 days: 8
 
Related Articles
FORUM

blocking

blocking

FORUM

How to get blocked objects?

How to get blocked objects?

BLOG

Identify the cause of SQL Server blocking

In my previous article (Different techniques to identify blocking in SQL Server) on MSSQLTips.com, I...

ARTICLE

Identifying Unused Objects in a Database

Longtime author Leo Peysakhovich answers one of the most common questions seen in discussion areas a...

FORUM

Blocked and Blocking process Help

Blocked and Blocking process Help

Tags
 
Contribute

Join the most active online SQL Server Community

SQL knowledge, delivered daily, free:

Email address:  

You make SSC a better place

As a member of SQLServerCentral, you get free access to loads of fresh content: thousands of articles and SQL scripts, a library of free eBooks, a weekly database news roundup, a great Q & A platform… And it’s our huge, buzzing community of SQL Server Professionals that makes it such a success.

Join us!

Steve Jones
Editor, SQLServerCentral.com

Already a member? Jump in:

Email address:   Password:   Remember me: Forgotten your password?
Steve Jones