SQL Server Deadlocks

Investigate what gets locked in the database during behind-the-scenes update, and try to decrease the scope of the lock. Rewrite the code so that only one order is locked at any given moment, rather than whole table.

I'm surprised though that ERP does not allow custom validations. If you don't mind, what is the ERP? We run Infor Visual, and have ton of validations in "onsave" macros.

We run an ERP specific to our industry - E-Automate.  I know which tables are being locked on a consistent basis.  When you say to rewrite the code so that only one order is locked at a time how do you recommend I do that.  Can you give me an example.

One table that gets locked is our service call table and below is a stored procedure that gets deadlocked from time to time.




      SET NOCOUNT ON;

         IF EXISTS
            (
                  SELECT
                        *
                  FROM
                        tempdb..sysobjects
                  WHERE
                        id=OBJECT_ID('tempdb..#Calls')
            )
            DROP TABLE #Calls



create table #Calls(
ID int identity(1,1),
CallID int,
CallNumber varchar(50),
WorkOrderNumber varchar(50),
TotalAvgCost money,
TotalCost money,
CustomerName varchar(255),
EquipmentNumber varchar(50),
SerialNumber varchar(50),
Item varchar(50),
LocationCustomerName varchar(255),
StatusDesc varchar(255),
CallDate datetime,
Technician varchar(255),
Description varchar(1024),
Notes varchar(1024),
Make varchar(255),
Model varchar(255)
)


Insert into #Calls(CallID,CallNumber,WorkOrderNumber,TotalAvgCost, TotalCost,CustomerName,EquipmentNumber,SerialNumber,Item,LocationCustomerName,StatusDesc,CallDate,Technician,Description,Notes,Make,Model)
(
 SELECT
 scc.CallID,
 scc.CallNumber,
 w.WorkOrderNumber,
 Materials.TotalAvgCost,
 Materials.TotalCost,
 arc.CustomerName,
 EquipmentNumber,
 SerialNumber,
 i.Item,
 lc.CustomerName as LocationCustomerName,
 StatusDesc = scc.v_Status,
 scc.Date as CallDate,
 a.PrefFullName as Technician,
 ct.Description,
 scc.Notes,
-- CASE WHEN LEN(ISNULL(Convert(VARCHAR(101), scc.Notes), '')) > 100 THEN CONVERT(VARCHAR(97), scc.Notes) + '...' ELSE ISNULL(CONVERT(VARCHAR, scc.Notes), '') END [Notes],
 mk.Make,
 md.Model
 FROM v_SCCalls scc
      INNER JOIN SCCalls c on c.CallID = scc.CallID
      INNER JOIN SCWorkOrders w ON w.WorkOrderID = scc.WorkOrderID
      INNER JOIN ARCustomers arc ON scc.CustomerID = arc.CustomerID
      INNER JOIN ARCustomers lc ON scc.LocationID = lc.CustomerID
      INNER JOIN ShAgents a ON a.AgentID = scc.TechnicianID
      INNER JOIN SCCallTypes ct ON ct.CallTypeID = scc.CallTypeID
      LEFT JOIN SCEquipments sce ON sce.EquipmentID = scc.EquipmentID
      LEFT JOIN ICItems i ON i.ItemID = scc.ItemID
      LEFT JOIN ICModels md ON md.ModelID = ISNULL(scc.EquipModelID, i.ModelID)
      LEFT JOIN ICMakes mk ON mk.MakeID = md.MakeID  
       left join
      (
      select rm.CallID,  (sum(i.AvgCost*(rm.Quantity - rm.Canceled))) as TotalAvgCost, (sum(i.Cost*(rm.Quantity - rm.Canceled))) as TotalCost
      from SCQReportMaterials rm
      inner join SCQReportMaterialBins rmb on rmb.CallID = rm.CallID and rm.DetailID = rmb.DetailID
      inner join v_SCCalls scc on scc.CallID = rm.CallID
      inner join ICItems i on i.ItemID = rm.ItemID
      left join ICUsageStatuses us on us.UsageStatusID = rm.UsageStatusID
      where scc.v_OnHoldCodeDesc = 'Waiting for Parts' and us.UsageStatus = 'Needed' and (rm.Quantity - rm.Canceled) > 0
      group by rm.CallID
      ) as Materials on Materials.CallID = scc.CallID
      --Left join call notes
      LEFT OUTER JOIN
                             (SELECT        c.CallID, MAX(cnd.NoteID) AS MaxNoteID, MAX(cnd.NoteDetailID) AS MaxNoteDetailID
                               FROM            dbo.SCCalls AS c INNER JOIN
                                                         dbo.SCCallNoteDetails AS cnd ON cnd.NoteID = c.NoteID INNER JOIN
                                                         dbo.SHNoteTypes AS nt ON nt.NoteTypeID = cnd.TypeID
                               WHERE        (nt.NoteTypeID IN (175))
                               GROUP BY c.CallID) AS CallNotes ON CallNotes.CallID = c.CallID
                  LEFT OUTER JOIN SCCallNoteDetails AS cnd2 ON cnd2.NoteID = CallNotes.MaxNoteID AND cnd2.NoteDetailID = CallNotes.MaxNoteDetailID
                  LEFT OUTER JOIN SHNoteTypes AS nt ON nt.NoteTypeID = cnd2.TypeID

      WHERE (scc.v_OnHoldCodeDesc in ('Waiting for Parts'))  and (Materials.TotalAvgCost > '249.99' or Materials.TotalCost > '249.99') and CallNotes.CallID is null
      --ORDER BY (CallNumber) ASC
)

--Update Call
update c
set OnHoldCodeID = 129 --Waiting for Parts > $250
from SCCalls c
inner join #Calls on #Calls.CallID = c.CallID

--Update Call Holds
update rh
set OnHoldCodeID = 129 --Waiting for Parts > $250
--select *
from SCQReportHolds rh
inner join #Calls on #Calls.CallID = rh.CallID
where rh.ReleaseTime is null and rh.OnHoldCodeID = 2

--Insert note for history
DECLARE @i int
      DECLARE @max int
      --Declare @callid int
      set @i = (select min(ID) from #Calls)
      set @max = (select max(ID) from #Calls)
      DECLARE @CallID int
      
      WHILE @i <= @max
      BEGIN
            Set @CallID = (select CallID from #Calls where ID = @i)

            DECLARE @RC int
            DECLARE @NoteTypeID int = 178
            DECLARE @UserID varchar(8) = 'tpotte01'
            DECLARE @Note varchar(max) = 'Parts Over $250 - Set On Hold'

            -- TODO: Set parameter values here.

            EXECUTE @RC = [dbo].[cu_Todd_IMS_ServiceCall_Review_Over250_Insert_CallNote]
               @CallID
              ,@NoteTypeID
              ,@UserID
              ,@Note

            --Add ILC Notes in case someone tries to process it
            update rmqb
            set Notes = 'DO NOT PROCESS - Parts Total > $250 ' + rmqb.Notes, UpdatorID = 'tpotte01', LastUpdate = GETDATE() --Waiting for Parts > $250
            from SCQReportMaterialBins rmqb
            where rmqb.CallID = @CallID
            --inner join #Calls on #Calls.CallID = rmqb.CallID

            SET @i = @i + 1

      END

The rewrite has to start with identifying the tables where the deadlock occurs. You can run Profiler and see.

See if you can implement something from this article: https://technet.microsoft.com/en-us/library/ms191242(v=sql.105).aspx

What I mean by rewrite is something like this: let's assume that deadlock occurs during this statement, and that column ID is the primary key of SCQReportHolds.
 
update rh
set OnHoldCodeID = 129 --Waiting for Parts > $250
--select *
from SCQReportHolds rh
inner join #Calls on #Calls.CallID = rh.CallID
where rh.ReleaseTime is null and rh.OnHoldCodeID = 2

so we can try something like this:

declare @id int
declare c cursor local fast_forward for
select SCQReportHolds.id
from SCQReportHolds with (nolock)
inner join #Calls on #Calls.CallID = SCQReportHolds.CallID
where SCQReportHolds.ReleaseTime is null and SCQReportHolds.OnHoldCodeID = 2
open c
fetch c into @id
while @@fetch_status=0 begin
  begin transaction
  update SCQReportHolds set OnHoldCodeID =129   where id=@id --Waiting for Parts > $250
  commit transaction
  fetch c into @id
end
close c; deallocate c

Remove any tables and/or columns you don't need from the query that loads the temp table.  For example, many of the columns aren't used after that in the code you show.  If the columns aren't needed, don't load them into the temp table at all.

Don't use any more views than you really need to in the query.  Views can make it very hard for SQL to properly optimize the query.

After that, deadlocks can often be resolved by adding/changing indexes appropriately.  If that won't work, then you might have to rewrite the code.

How are the major tables in those queries indexed?  That is, what are their clustered and nonclustered index definitions?

@Scott, re. " Views can make it very hard for SQL to properly optimize the query." - aren't views totally transparent, i.e. auto-expanded by sql engine when executing?

@Vadim:

To a point they are.  But when they become too embedded, SQL basically "gives up" and stops expanding them, falling back most often on table scans.  Consider the potential problem SQL faces: a (complicated) view that references a (complicated) view that references another view that has 2 other views, etc., etc..  At some point SQL's optimizer "decides" 'it's just not worth it' and instead of trying to fully optimize access, it just scans the tables.

@Scott, but it's probably not about the views as such, but rather about some threshold in the complexity of the resulting query. I.e. if we expand every complicated view, and arrive at some very large query, the result probably would be the same?

Are those limitations documented somewhere (i.e. "optimizer will fall on table scans if your query is X+ pages long, or involves Y+ tables, or Z+ joins, etc)? or it's just your practical experience?

@Vadim: The optimizer doesn't so much "fail" as it times out, as it sometimes does for queries with no views at all, as you point out.

I don't know that MS has ever put out a specific hard-and-fast rule about optimization time outs, probably because it's a complex topic and because optimizer enhancements make it change over time anyway.

Sorry for the delay.

@Vadim - my issue with using Cursors is that I have a lot of data in these tables and Cursors run much slower.

@Scott - Help me out a little.  By removing unused columns from the temp table does it decrease my chance of deadlocks?  If so, why?

I have attached a file of the indexes for SCCalls and SCQReportHolds.

It appears if I need to redo some of my code...
ExpertsExchange_Indexes.xlsx

I'm going to use a combination of the two suggestions.  Thank you both.