April 3, 2008 at 11:29 am
Hello All DBA/DB Developer masters!
I have this stored procedure that is causing major blocking probs in our prod db. It's a SQL 2K5 64bit server. Here is the code:
USE [Vimas]
GO
/****** Object: StoredProcedure [dbo].[svg_SetReleaseLock] Script Date: 04/03/2008 13:24:36 ******/
SET ANSI_NULLS OFF
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[svg_SetReleaseLock]
@TableName varchar(50),
@RowID int,
@user-id int,
@ToLock bit
AS
DECLARE @LockStatus int
IF @ToLock=0
BEGIN
DELETE FROM Lock WHERE TableName=@TableName AND RowID=@RowID AND UserID= @user-id
END
ELSE
SELECT @LockStatus=Count(*) FROM Lock (nolock) WHERE TableName=@TableName AND RowID=@RowID AND UserID=@UserID
IF @LockStatus=0
BEGIN
SELECT @LockStatus=Count(*) FROM Lock (nolock) WHERE TableName=@TableName AND RowID=@RowID
IF @LockStatus=0
INSERT INTO Lock (TableName, RowID, UserID) VALUES ( @TableName, @RowID, @user-id)
END
ELSE
UPDATE Lock SET LockDateTime=GetDate() WHERE TableName=@TableName AND RowID=@RowID
April 3, 2008 at 11:55 am
What is calling this, how often is it being called, and what exactly are you trying to do with it?
April 3, 2008 at 12:16 pm
It's being called frequently and I'm not sure what it's purpose. I kinda inherited the object when I took over. I know that doesn't really help but I'm looking for somebody to throw me a bone here. 😀
April 3, 2008 at 12:23 pm
It looks like someone tried to build a row-lock system to bypass what SQL Server does on its own.
Almost certainly, the slows this is causing are because of the "select count(*)" commands. If the proc is actually needed (I'm going to hazard a guess that it's the core of some truly oddball system and can't be eliminated), then it could be rewritten to use "if exists" instead of "select count". That might make it slightly less horrible.
I'd do a select from sys.sql_modules to find out what code references this proc, and see just how much of a mess you've inherited. If you can gut out a "we built our own row-locking because we like to use the nolock hint too much", and let SQL server do the locking on its own, you might be doing a huge favor to everyone who uses that database.
(Of course, I could be reading too much into this thing, but it made me shudder when I read it.)
- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread
"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon
April 3, 2008 at 12:25 pm
davidsalazar01 (4/3/2008)
It's being called frequently and I'm not sure what it's purpose. I kinda inherited the object when I took over. I know that doesn't really help but I'm looking for somebody to throw me a bone here. 😀
Well - interestingly enough - this seems to be in place to cause blocking. Meaning - it's something that looks an awful lot like a locking semaphore, meaning something you'd put in place if you wanted to manually prevent other things from being able to access what your specific thread is doing. It's there to prevent something else (such as another thread of the same process) from getting a lock on the same rowID.
In other words, other stored procedures are calling it and would be using it to prevent concurrency in some way.
----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?
April 3, 2008 at 12:31 pm
That is what I was reading into it, but I did not want to attack with "why are you trying to build your own locking mechanism?".
If anything, replacing this entire thing with sp_getapplock would eliminate a lot of problems that could be caused by dropped connections.
This looks like a mess that was built by some developers that did not understand record locking in SQL server.
April 3, 2008 at 12:39 pm
Try running a profiler on this object and see how often and who is calling this sproc.
that will give u insight into how its being used and why. and you can see who is locking the table as well.
some process is locking up your lock table (see the irony 🙂 )
event to capture:
stored procedures - sp:Completed
stored procedures - sp:recompile (this could also be one of the reasons it takes long to finish)
Locks - lock:acquired
Also, you can see who is locking this table in the activity monitor.
Kindest Regards,
Shivaram Challa
(http://challa.net - Home of the "Excel to CSV converter & Browsepad applications".)
Forum Etiquette: How to post data/code on a forum to get the best help[/url]
April 3, 2008 at 12:43 pm
When I ran select on sys.modules it returned this only. What does this actually mean in relation to the sproc in question?
create procedure dbo.sp_MSrepl_startup as exec sys.sp_MSrepl_startup_internal
create procedure dbo.sp_MScleanupmergepublisher as exec sys.sp_MScleanupmergepublisher_internal
April 3, 2008 at 1:21 pm
I found out that the code being used is actually used to manage when a customer is being worked on in a VB app.
April 3, 2008 at 2:11 pm
I'm not sure if this is designed behavior, but this section:
IF @LOCKSTATUS=0
BEGIN
SELECT
@LOCKSTATUS=COUNT(*)
FROM
LOCK (NOLOCK)
WHERE
TABLENAME=@TABLENAME AND
ROWID=@ROWID
IF @LOCKSTATUS=0
BEGIN
INSERT INTO LOCK
(
TABLENAME,
ROWID,
USERID
)
VALUES
(
@TABLENAME,
@ROWID,
)
END
END
is run every call because @LockStatus is never set in the first IF block when @TOLOCK = 0
You could also replave the @LockStatus with If Exists(Select * From Lock Where TABLENAME=@TABLENAME AND ROWID=@ROWID) which should be faster.
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
April 3, 2008 at 10:58 pm
SELECT @LockStatus=Count(*)
FROM Lock (nolock)
WHERE TableName=@TableName
AND RowID=@RowID
AND UserID=@UserID
?
Classic, LOL! I added the question mark it turned into a Zen koan! Heh, this is going into my SQL scrapbook.
In fact the whole procedure reads like an Escher drawing looks, including the name and the roll-through ELSE clause (great catch, Jack!). :w00t:
[font="Times New Roman"]-- RBarryYoung[/font], [font="Times New Roman"] (302)375-0451[/font] blog: MovingSQL.com, Twitter: @RBarryYoung[font="Arial Black"]
Proactive Performance Solutions, Inc. [/font][font="Verdana"] "Performance is our middle name."[/font]
Viewing 11 posts - 1 through 10 (of 10 total)
You must be logged in to reply to this topic. Login to reply