**** Stored proc causing major concurrency issues in DB ****

  • 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

  • What is calling this, how often is it being called, and what exactly are you trying to do with it?

  • 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. 😀

  • 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

  • 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?

  • 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.

  • 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]

  • 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

  • I found out that the code being used is actually used to manage when a customer is being worked on in a VB app.

  • 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,

    @userid

    )

    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.

  • 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