Link to home
Create AccountLog in
Avatar of ac_davis2002
ac_davis2002Flag for United Kingdom of Great Britain and Northern Ireland

asked on

tune stored procedure to reduce deadlocks

Hi can anyone give me some tips on how to tune the following stored procedure as its giving me deadlocks?

CREATE PROCEDURE Check_CaseLockMessages

@UserId as int,
@Type as tinyint
Created By:      Chris Kerrison
Date Created:      30 Apr 2001
Purpose:      determines whether a message should be send to a user relating to a case.  The Store Proc works in two 2 ways:
            1) To identify if any other users are requesting a case opened by the User Id passed in
            2) To identify to a user that a Case they have request has been released by another user

if @type = 2

      Select       csCaseID,
            FirstName + ' ' + Surname as UserName
      From         CSCaseLock a with (nolock), HRHumanResources b
      where       a.LockType = 2
      and      a.UserId = b.HRHumanResourceID
      and       a.LockingUserId = @UserID
      and       a.FlaggedToUser = 0
      order by csCaseID
      -- Update these records so that a message doesn't get sent again
      Set NoCount On
      Update       CSCaseLock
      Set       FlaggedToUser = 1
      Where        LockType = 2
      and      LockingUserId = @UserID
      Set NoCount Off
      Select       csCaseID,
            FirstName + ' ' + Surname as UserName
      From         CSCaseLock a with (nolock), HRHumanResources b
      where       a.UserId = b.HRHumanResourceID
      and      a.LockType = 3
      and       a.UserID = @UserId
      Order by CSCaseID

Avatar of chapmandew
Flag of United States of America image

do these fields in your table have indexes on them?

    Where        LockType = 2
      and      LockingUserId = @UserID
are you sure you get deadlocks on this procedure?
maybe this procedure gets deadlocked with a different procedure?

it does not seem possible that this procedure get deadlocked with itself since you perform the select statement with (nolock) hint so there should not be any contention between the select statement and the update statement
its possible if the update statement takes a long, long time to find the records it is trying to update..

    Update       CSCaseLock
      Set       FlaggedToUser = 1
      Where        LockType = 2
      and      LockingUserId = @UserID
Avatar of ac_davis2002


thats interesting chapmandew, have have created a script for the constrainst on this table

ALTER TABLE [dbo].[CSCaseLock] ADD
      CONSTRAINT [DF_CSCaseLock_Type] DEFAULT (1) FOR [LockType],
      CONSTRAINT [DF_CSCaseLock_FlaggedtoUser] DEFAULT (0) FOR [FlaggedtoUser],
      CONSTRAINT [DF__CSCaseLoc__Locke__339F7146] DEFAULT (null) FOR [LockedForMove],

could that be improved?
I would guess that the PK is going to help you for sure...but you didn't list what field it will be on.

Make sure the LockType and the LockingUserID fields have Nonclustered indexes on them.
ok, i have had a look and there is an PK_CSCaseLock on these 3 fields cscaseid, userid and locktype.

Is there anything i can do to make it quicker?
Avatar of chapmandew
Flag of United States of America image

Link to home
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
ok will do, thanks again!!