How can I avoid or minimize deadlocks in this situation?

Question

I have a relatively small table (for now). It works as a fancy queue. Jobs that execute every /second/, ask this table for more work and whenever work completes, they tell the table that work is completed.

Table has ~1000 entries or so entries and long-term will hopefully have 100k+ rows Each entry signifies a job that needs to be executed once per minute. Table is hosted in SQL Azure (S2 plan)

Job Starter executes a stored proc that requests work from this table. Basically, the proc looks at the table, sees which tasks are not in progress and are overdue, marks them as "in progress" and returns them to job starter.

When task completes, a simple update is executed to tell that task completed and will be available for another cycle of work in a minute (field called Frequency controls this)

PROBLEM: I get deadlocks quiet frequently when asking this table for more work, or trying to mark entries as completed. Looks like ROWLOCK hint is not working. Do I need an indexing structure on this table?

Here is a Stored Procedure that retrieves records (usually up to 20 at a time, governed by @count parameter

CREATE PROCEDURE [dbo].[sp_GetScheduledItems]
@activity NVARCHAR (50), @count INT, @timeout INT=300, @dataCenter NVARCHAR (50)
AS
BEGIN
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 DECLARE @batchId uniqueidentifier
 SELECT @batchId = NEWID()

 DECLARE @result int;
 DECLARE @process nvarchar(255);

   BEGIN TRAN
   -- Update rows
   UPDATE Schedule 
   WITH (ROWLOCK)
   SET 
    LastBatchId = @batchId, 
    LastStartedProcessingId = NEWID(), 
    LastStartedProcessingTime = GETUTCDATE()
   WHERE 
    ActivityType = @activity AND 
    IsEnabled = 1 AND
    ItemId IN (
     SELECT TOP (@count) ItemId 
     FROM Schedule 
     WHERE 
      (LastStartedProcessingId = LastCompletedProcessingId OR LastCompletedProcessingId IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETUTCDATE()) > @timeout) AND 
      IsEnabled = 1 AND ActivityType = @activity AND DataCenter = @dataCenter AND 
      (LastStartedProcessingTime IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETUTCDATE()) > Frequency)
     ORDER BY (DATEDIFF(SECOND, ISNULL(LastStartedProcessingTime, '1/1/2000'), GETUTCDATE()) - Frequency) DESC
    ) 

   COMMIT TRAN

   -- Return the updated rows
   SELECT ItemId, ParentItemId, ItemName, ParentItemName, DataCenter, LastStartedProcessingId, Frequency, LastProcessTime, ActivityType
   FROM Schedule 
   WHERE LastBatchId = @batchId

END
GO

Here is a stored procedure that marks entries as completed (it does so one-at-a-time)

CREATE PROCEDURE [dbo].[sp_CompleteScheduledItem]
@activity NVARCHAR (50), @itemId UNIQUEIDENTIFIER, @processingId UNIQUEIDENTIFIER, @status NVARCHAR (50), @lastProcessTime DATETIME, @dataCenter NVARCHAR (50)
AS
BEGIN
 -- SET NOCOUNT ON added to prevent extra result sets from
 -- interfering with SELECT statements.
 SET NOCOUNT ON;
 SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

 UPDATE Schedule WITH (ROWLOCK)
 SET
  LastCompletedProcessingId = LastStartedProcessingId,
  LastCompletedProcessingTime = GETUTCDATE(),
  LastCompletedStatus = @status,
  LastProcessTime = @lastProcessTime
 WHERE
  ItemId = @itemId AND
  LastStartedProcessingId = @processingId AND
  DataCenter = @dataCenter AND
  ActivityType = @activity
END
GO

Here is the table itself

CREATE TABLE [dbo].[Schedule](
    [ItemId] [uniqueidentifier] NOT NULL,
    [ParentItemId] [uniqueidentifier] NOT NULL,
    [ActivityType] [nvarchar](50) NOT NULL,
    [Frequency] [int] NOT NULL,
    [LastBatchId] [uniqueidentifier] NULL,
    [LastStartedProcessingId] [uniqueidentifier] NULL,
    [LastStartedProcessingTime] [datetime] NULL,
    [LastCompletedProcessingId] [uniqueidentifier] NULL,
    [LastCompletedProcessingTime] [datetime] NULL,
    [LastCompletedStatus] [nvarchar](50) NULL,
    [IsEnabled] [bit] NOT NULL,
    [LastProcessTime] [datetime] NULL,
    [DataCenter] [nvarchar](50) NOT NULL,
    [ItemName] [nvarchar](255) NOT NULL,
    [ParentItemName] [nvarchar](255) NOT NULL,
 CONSTRAINT [PK_Schedule] PRIMARY KEY CLUSTERED 
(
    [DataCenter] ASC,
    [ItemId] ASC,
    [ActivityType] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)

Answer 1

This is a good question :-) As usual you can do many things but in your case I think we can simplify your query quite a bit. Note that the suggestion below doesn't use SERIALIZABLE isolation level which in your case in all likelihood is causing table level locks to prevent phantom read from occurring (and also makes all write access to your table, well, serialized. You also don't actually need to specify BEGIN & COMMIT TRAN as you are only issuing one statement within your transaction (although it doesn't hurt either in your case). In this example we leverage the fact that we can actually issue your update directly against the sub query (in this case in the shape of a CTE) and we can also remove your last SELECT as we can return the result set directly from the UPDATE statement.

HTH,

-Tobias

SQL Server Team

CREATE PROCEDURE [dbo].[sp_GetScheduledItems] 
@activity NVARCHAR (50), @count INT, @timeout INT=300, @dataCenter NVARCHAR (50)
AS
BEGIN
  SET NOCOUNT ON;
  DECLARE @batchId uniqueidentifier

  SELECT @batchId = NEWID()
  DECLARE @result int;
  DECLARE @process nvarchar(255);

  -- Update rows
  WITH a AS (
    SELECT TOP (@count) 
        *
    FROM Schedule 
    WHERE 
    (LastStartedProcessingId = LastCompletedProcessingId OR LastCompletedProcessingId IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETUTCDATE()) > @timeout) AND 
    IsEnabled = 1 AND ActivityType = @activity AND DataCenter = @dataCenter AND 
    (LastStartedProcessingTime IS NULL OR DATEDIFF(SECOND, LastStartedProcessingTime, GETUTCDATE()) > Frequency)
    ORDER BY (DATEDIFF(SECOND, ISNULL(LastStartedProcessingTime, '1/1/2000'), GETUTCDATE()) - Frequency) DESC
  )
  UPDATE a SET 
    LastBatchId = @batchId, 
    LastStartedProcessingId = NEWID(), 
    LastStartedProcessingTime = GETUTCDATE()
  OUTPUT INSERTED.ItemId, INSERTED.ParentItemId, INSERTED.ItemName,   INSERTED.ParentItemName, INSERTED.DataCenter, INSERTED.LastStartedProcessingId,   INSERTED.Frequency, INSERTED.LastProcessTime, INSERTED.ActivityType

END