• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 337
  • Last Modified:

adding 'with no lock' to ressolve deadlock issues

Hi

I have two stored procedures that are causing me deadlock issues

rec_cscase

CREATE PROCEDURE Rec_CSCase
@RecordID int,
@HeirachyID int=0,
@UserID int
AS
/*
Author:             N Brooks
Date Created:       Mar 21 2000

*/
Declare @CurrentUserName       varchar(100)
Declare @CurrentUserID       int
Declare @TimeOpened         dateTime
Declare @CaseStatus            tinyInt
if @HeirachyID > 0
  BEGIN
      -- Adding a new record, so get the template Case information.  This includes, the Company Name, Alett, Contract description and Site Name
      SELECT
            @CaseStatus as CaseStatus,
            0 as CSCaseID,
            Site.CTHeirachyID,
            '' as Title,
            '' as FirstName,
            '' as Surname,
            '' as OfficePhone,
            '' as HomePhone,
            '' as MobilePhone,
            '' as Fax,
            '' as Address1,
            '' as Address2,
            '' as Address3,
            '' as Address4,
            '' as Address5,
            '' as Address6,
            '' as EMail,
            1 as FlagSetting,
            0 as FlagSetByID,
            null as FlagSetDate,
            '' as FlagReason,
            NULL AS LKCaseStatusCodeID,
            0 as PreviousUse,
            '' as PreviousCaseNo,
            0 as PreviousUseType,
            '' as CaseInformation,
            ct.FirstName + ' ' + ct.Surname AS ReferrerName,
            ct.Position AS ReferrerJobTitle,
            ct.TelephoneNumber AS ReferrerTelephone,
            ct.Extension AS ReferrerExtension,
            ct.Fax AS ReferrerFaxNo,
            ct.MobilePhone AS ReferrerMobile,
            ct.EMail AS ReferrerEMail,
            ct1.FirstName + ' ' + ct1.Surname AS AlternateName,
            ct1.Position AS  AlternateJobTitle,
            ct1.TelephoneNumber AS AlternateTelephone,
            ct1.Extension AS AlternateExtension,
            ct1.Fax AS AlternateFaxNo,
            ct1.MobilePhone AlternateMobile,
            ct1.EMail AS AlternateEMail,
            frst.BackGroundInfo AS ReferrerBriefing,
            0 as FormalReferralFlag,
            0 as ManCon,
            '' as ManConCaseNo,
            '' as ManConReferral,
            '' as ManConRefCaseNo,
            0 as OHReferral,
            0 as OtherReferral,
            null as LKWorkPerfID,
            null as WorkPerfSetBy,
            null as WorkPerfSetDate,
            null as InitialContactID,
            null as InitialContactDate,
            '00:00' as InitialContactTime,
            null as ContactPassedToID,
            null as OnCallID,
            null as CaseManagerID,
            0 as InterestingCase,
            null as InterestingCaseSetBy,
            null as InterestingDate,
            0 as CaseClosed,
            null as CaseClosedByID,
            null as CaseClosedDate,
            0 as CaseReOpened,
            null as CaseReOpenedByID,
            null as CaseReOpenedDate,
            case
                  when Site.Sitename is null then
                        Cmpy.CustomerName
                  Else
                        Site.SiteName
                  End as SiteName,
            Cmpy.CustomerName,
            n.NotesLong as CompanyAlert,
            n.NotesLong as CompanyAlertNotes,
            Case
                  When Site.AgreementType = 0 then
                         Typ1.Description
                  Else
                        ''
                  End as ContractDescription,
            Site.AgreementType,
            @CurrentUserName as CurrentUser,
            @TimeOpened as TimeOpened,
      --MON 18-01-06
            Null as GPDetails,
            Null as GPNotes,
            Null as PostCode,

            Null as InterestingNote,
            0 as WorkFlag,
            Null as dtDOB
      ---------
            From      CPCompany Cmpy

                  LEFT JOIN CPNotes n ON n.CPCompanyID = Cmpy.CPCompanyID
                  AND n.Alert = 1 AND ((GETDATE() BETWEEN n.StartDate AND n.EndDate) OR (GETDATE() >= n.StartDate AND n.EndDate IS NULL)),
                  CTHeirachy Site
                  --Join to Contracts table if a Contract Type of Case
                  left outer join CTContracts Cont on Site.AgreementID = Cont.CTContractID and Site.AgreementType = 0
                  left outer join LKContractTypes Typ1 on Cont.LKContractTypeID = Typ1.LKContractTypeID
                  --Join to First Call table if a First Call Type of Case
                  left outer join CTFirstCall Frst on Site.AgreementID = Frst.CTFirstCallID and Site.AgreementType = 1
                  --left outer join LKContractTypes Typ2 on frst.LKContractTypeID = Typ2.LKContractTypeID
                  LEFT JOIN CPContacts ct ON ct.CPContactID = frst.ReferrerID
                  LEFT JOIN CPContacts ct1 ON ct1.CPContactID = frst.AlternativeContactID
            Where       Site.CTHeirachyID = @HeirachyID
            and       site.CPCompanyID = Cmpy.CPCompanyID
  END
ELSE
  BEGIN
     -- Check to see if any user has a lock on this record
     Select @CurrentUserName = FirstName + ' ' + Surname,
         @TimeOpened = TimeOpened,
         @CurrentUserID = a.UserID
     From    CSCaseLock a, HRHumanResources b
     Where a.CSCaseId = @RecordID
     And         a.LockType = 1
     And         a.UserId = b.HRHumanResourceID
     if @CurrentUserName is null or @CurrentUserID = @UserID
        BEGIN
      -- No Locks exist or the user already has the case Held Open
      IF @CurrentUserName IS NULL
         BEGIN
            SET NOCOUNT ON
                  -- Check that the case exists before applying a lock
                  IF (SELECT COUNT(*) FROM CSCase WHERE CSCaseID = @RecordID) > 0  
                  BEGIN
                        INSERT INTO CSCaseLock
                        (CSCaseID, UserID, TimeOpened, LockType, LockingUserID, FlaggedtoUser)
                        VALUES (@RecordID, @UserID, GETDATE(), 1, NULL, 0)
                  END
            SET NOCOUNT OFF
         END
        END
     ELSE
        BEGIN      
      -- Yes it's locked so create a new Lock Request record (but only if a one doesn't already exist for the User)
      Set NoCount ON
      If (select count(*) from CSCaseLock where CSCaseId = @RecordID And UserId = @UserID And LockType = 2) = 0
         BEGIN
                Insert into CSCaseLock
            (CSCaseID, UserID, TimeOpened, LockType, LockingUserID, FlaggedtoUser)
            Values (@RecordID, @UserID, null, 2, @CurrentUserID, 0)
         END      
      Set NoCount OFF
      Select @CaseStatus = 1
        END
     --Get Existing Case record
     SELECT
      @CaseStatus as CaseStatus,
      CSCaseID,
      Cse.CTHeirachyID,
      Cse.Title,
      Cse.FirstName,
      Cse.Surname,
      Cse.OfficePhone,
      Cse.HomePhone,
      Cse.MobilePhone,
      Cse.Fax,
      Cse.Address1,
      Cse.Address2,
      Cse.Address3,
      Cse.Address4,
      Cse.Address5,
      Cse.Address6,
      Cse.EMail,
      Cse.FlagSetting,
      Cse.FlagSetByID,
      Cse.FlagSetDate,
      Cse.FlagReason,
      Cse.LKCaseStatusCodeID,
      Cse.PreviousUse,
      Cse.PreviousCaseNo,
      Cse.PreviousUseType,
      Cse.CaseInformation,
      Cse.ReferrerName,
      Cse.ReferrerJobTitle,
      Cse.ReferrerTelephone,
      Cse.ReferrerExtension,
      Cse.ReferrerFaxNo,
      Cse.ReferrerMobile,
      Cse. ReferrerEMail,
      Cse.AlternateName,
      Cse. AlternateJobTitle,
      Cse.AlternateTelephone,
      Cse. AlternateExtension,
      Cse.AlternateFaxNo,
      Cse.AlternateMobile,
      Cse.AlternateEMail,
      Cse.ReferrerBriefing,
      Cse.FormalReferralFlag,
      Cse.ManCon,
      Cse.ManConCaseNo,
      Cse.ManConReferral,
      Cse.ManConRefCaseNo,
      Cse.OHReferral,
      Cse.OtherReferral,
      Cse.LKWorkPerfID,
      Cse.WorkPerfSetBy,
      Cse.WorkPerfSetDate,
      Cse.InitialContactID,
      --TF160505 - International Dates Fix - Don't convert
      --convert(varchar(12), Cse.InitialContactDate) as InitialContactDate,
      Cse.InitialContactDate as InitialContactDate,
      --TF160505 - International Dates Fix - Use ISO format
      substring(convert(varchar(19), Cse.InitialContactDate, 126), 12, 8) as InitialContactTime,
      Cse.ContactPassedToID,
      Cse.OnCallID,
      Cse.CaseManagerID,
      Cse.InterestingCase,
      Cse.InterestingCaseSetBy,
      Cse.InterestingDate,
      Cse.CaseClosed,
      Cse.CaseClosedByID,
      Cse.CaseClosedDate,

      Cse.CaseReOpened,
      Cse.CaseReOpenedByID,
      Cse.CaseReOpenedDate,
      site.Sitename,
      Cmpy.CustomerName,
      n.NotesLong as CompanyAlert,
      n.NotesLong as CompanyAlertNotes,
Case
          When Typ1.Description is null then
            ''
          Else
            Typ1.Description
          End as ContractDescription,
      Site.AgreementType,
      @CurrentUserName as CurrentUser,
      @TimeOpened as TimeOpened,
      Case
                      --TF160505 - International Dates Fix - Use ISO format
                      When Typ1.Description is null then
                        --convert(varchar(12), frst.fromDate)
                        convert(varchar(10), frst.fromDate, 126)
                       Else
                         --typ1.Description + ' (' + convert(varchar(12), cont.fromDate) + ' - ' + convert(varchar(12), cont.EndDate) + ')'
                         typ1.Description + ' (' + convert(varchar(10), cont.fromDate, 126) + ' - ' + convert(varchar(10), cont.EndDate, 126) + ')'
                      End as AgreementDetails,
      Case
                      When Typ1.Description is null then
                        1
                       Else
                        0
                      End as AgreementType,
      Case
                      When Typ1.Description is null then
                        frst.CTFirstCallID
                       Else
                        cont.CTContractID
                      End as AgreementID,
      --MON 18-01-06
            Cse.GPDetails,
            Cse.GPNotes,
            Cse.PostCode,
            Cse.InterestingNote,
            Cse.WorkFlag,
            Cse.dtDOB
      ---------
      From      CSCase Cse, CPCompany Cmpy
            LEFT JOIN CPNotes n ON n.CPCompanyID = Cmpy.CPCompanyID
            AND n.Alert = 1 AND ((GETDATE() BETWEEN n.StartDate AND n.EndDate) OR (GETDATE() >= n.StartDate AND n.EndDate IS NULL)),
            CTHeirachy Site
            --Join to Contracts table if a Contract Type of Case
            left outer join CTContracts Cont on Site.AgreementID = Cont.CTContractID and Site.AgreementType = 0
            left outer join LKContractTypes Typ1 on Cont.LKContractTypeID = Typ1.LKContractTypeID
            --Join to First Call table if a First Call Type of Case
            left outer join CTFirstCall Frst on Site.AgreementID = Frst.CTFirstCallID and Site.AgreementType = 1
            --left outer join LKContractTypes Typ2 on frst.LKContractTypeID = Typ2.LKContractTypeID
      Where       Cse.CSCaseID = @RecordID
      and      Cse.CTHeirachyID = Site.CTHeirachyID
      and       site.CPCompanyID = Cmpy.CPCompanyID
  END
GO

AND check_caseLockMessages

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, 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
   END
ELSE
   BEGIN
      Select       csCaseID,
            FirstName + ' ' + Surname as UserName
      From         CSCaseLock a, HRHumanResources b
      where       a.UserId = b.HRHumanResourceID
      and      a.LockType = 3
      and       a.UserID = @UserId
      Order by CSCaseID
   END

GO

Can anyone please show me the best place to put deadlock statements that may help to reduce the deadlocks? By the way I'm using sql2000


Thanks

0
ac_davis2002
Asked:
ac_davis2002
  • 3
  • 3
  • 2
1 Solution
 
James MurrellProduct SpecialistCommented:
NOLOCK requires careful understanding of what your application is
doing. You may increase reponse time, and you may resolve deadlocks.
But what if the price is incorrect results leading to incorrect decisions?

The procedure you post looks innocent (save that it starts with the
letters sp_, which is a prefix reserved for system procedure; SQL
Server first looks in master for these), but there may be more issues
in involved.

Have you enabled deadlock logging? You do this, by specifying trace
flag 1204 as a startup parameter to SQL Server (use Enterprise Manger).
The output is not that terribly easy to interpret, but if you don't
understand why your deadlocks arise, you cannot address them.


taken from http://bytes.com/forum/thread78778.html
0
 
James MurrellProduct SpecialistCommented:
0
 
lundnakCommented:
I'd start by setting up a trace on the DB to determine which statements are causing the deadlocks.

http://msdn.microsoft.com/en-us/library/aa937573(SQL.80).aspx
or
http://www.mssqltips.com/tip.asp?tip=1036
or
http://support.microsoft.com/kb/832524
0
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
ac_davis2002Author Commented:
hi chaps yep

I have run a trace using DBCC TRACEON (3605,1205,-1), thats how identified the deadlocks.

The deadlogmessage was present within the sqlserver logs and gives the line number of the problem line in each procedure

rec_cscase line 170 and Check_CaseLockMessages line 36.

What do you think?? would 'with no lock' be advisable?

0
 
lundnakCommented:
>>What do you think?? would 'with no lock' be advisable
Is it all right if the Check_CaseLockMessages procedure returns "dirty" information?  If it is ok, then I'd put a nolock on that procedure?

cs97jjm3 is correct about inconsistent data.

I have used nolock before, when I didn't care about data consistency.
0
 
ac_davis2002Author Commented:
probably a silly question but could you confirm where i should put the 'with no lock statement'?
0
 
lundnakCommented:
I'd consider putting it after the cscaselock table.
   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

Open in new window

0
 
ac_davis2002Author Commented:
Exacellent

Thanks for all your help!!
0

Featured Post

How to Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

  • 3
  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now