Avatar of ac_davis2002
ac_davis2002
Flag 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
AS
/*
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
   BEGIN

      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
      BEGIN TRANSACTION
      Update       CSCaseLock
      Set       FlaggedToUser = 1
      Where        LockType = 2
      and      LockingUserId = @UserID
      COMMIT TRANSACTION
      Set NoCount Off
  END
ELSE
   BEGIN
      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
   END
GO

Cheers
Microsoft SQL Server

Avatar of undefined
Last Comment
ac_davis2002

8/22/2022 - Mon
chapmandew

do these fields in your table have indexes on them?

    Where        LockType = 2
      and      LockingUserId = @UserID
momi_sabag

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
chapmandew

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
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
ac_davis2002

ASKER
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],
      CONSTRAINT [PK_CSCaseLock] PRIMARY KEY  NONCLUSTERED
      (

could that be improved?
chapmandew

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

ASKER
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?
⚡ FREE TRIAL OFFER
Try out a week of full access for free.
Find out why thousands trust the EE community with their toughest problems.
ASKER CERTIFIED SOLUTION
chapmandew

THIS SOLUTION ONLY AVAILABLE TO MEMBERS.
View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
GET A PERSONALIZED SOLUTION
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
ac_davis2002

ASKER
ok will do, thanks again!!
ac_davis2002

ASKER
Excellent