CREATE TABLE [dbo].[Jobs]
(
    [Id]                    BIGINT          NOT NULL    CONSTRAINT [PK_Jobs] PRIMARY KEY IDENTITY,[Data]                  VARBINARY(MAX)  NOT NULL,[CreationTimestamp]     DATETIME2(7)    NOT NULL,[Type]                  INT             NOT NULL,[ModificationTimestamp] DATETIME2(7)    NOT NULL,[State]                 INT             NOT NULL,[RowVersion]            ROWVERSION      NOT NULL,[Activity]              INT                 NULL,[Parent_Id]             BIGINT              NULL
)
GO

CREATE NONCLUSTERED INDEX [IX_Jobs_Type_State_RowVersion] ON [dbo].[Jobs]([Type],[State],[RowVersion] ASC) WHERE ([State] <> 100)
GO

CREATE NONCLUSTERED INDEX [IX_Jobs_Parent_Id_State] ON [dbo].[Jobs]([Parent_Id],[State] ASC)
GO

作业正在添加到状态= 0(新)的表 – 它可以被这个状态的任何工作人员使用.当工作者获得此队列项时,状态更改为50(处理),作业变为不可用于其他用户(工作人员调用[dbo].[Jobs_GetFirstByType]与参数：Type = any,@ CurrentState = 0,@ NewState = 50).

CREATE PROCEDURE [dbo].[Jobs_GetFirstByType]
    @Type           INT,@CurrentState   INT,@NewState       INT
AS
BEGIN

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    DECLARE @JobId BIGINT;

    BEGIN TRAN

        SELECT      TOP(1)
                    @JobId = Id
        FROM        [dbo].[Jobs] WITH (UPDLOCK,READPAST)
        WHERE       [Type] = @Type AND [State] = @CurrentState
        ORDER BY    [RowVersion];


        UPDATE  [dbo].[Jobs]

        SET     [State] = @NewState,[ModificationTimestamp] = SYSUTCDATETIME()

        OUTPUT  INSERTED.[Id],INSERTED.[RowVersion],INSERTED.[Data],INSERTED.[Type],INSERTED.[State],INSERTED.[Activity]

        WHERE   [Id] = @JobId;

    COMMIT TRAN

END

处理完成后,作业状态可以再次更改为0(新建),也可以将其设置为100(已完成).

CREATE PROCEDURE [dbo].[Jobs_UpdateStatus]
    @Id         BIGINT,@State      INT,@Activity   INT
AS
BEGIN

    UPDATE  j

    SET     j.[State] = @State,j.[Activity] = @Activity,j.[ModificationTimestamp] = SYSUTCDATETIME()

    OUTPUT  INSERTED.[Id],INSERTED.[RowVersion]

    FROM    [dbo].[Jobs] j

    WHERE   j.[Id] = @Id;

END

作业具有层次结构,只有当所有子项完成时,父作业才会获得State = 100(Completed).
一些工作人员调用存储过程([dbo].[Jobs_GetCountWithExcludedState] with @ ExcludedState = 100)返回未完成作业的数量,当它返回0时,父作业状态可以设置为100(已完成).

CREATE PROCEDURE [dbo].[Jobs_GetCountWithExcludedState]
    @ParentId       INT,@ExcludedState  INT
AS
BEGIN

    SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

    SELECT  COUNT(1)

    FROM    [dbo].[Jobs]

    WHERE   [Parent_Id] = @ParentId
    AND     [State] <> @ExcludedState

END

这个存储过程的主要问题是奇怪的行为.有时候,为父工作返回0,但它完全没有完成工作.我尝试打开更改数据跟踪和一些调试信息(包括分析) – 子作业100％没有状态= 100,当SP返回0.
看来,SP跳过不在100(已完成)状态的记录,但是为什么会发生,如何防止这种情况呢？

UPD：
调用[dbo].[Jobs_GetCountWithExcludedState]在父作业有子代时启动.当工作人员开始检查未存在的子作业时,由于创建子项并将其设置为包含在事务中的父作业检查活动,那么就不会有这种情况：

using (var ts = new TransactionScope())
{
    _jobManager.AddChilds(parentJob);

    parentJob.State = 0;
    parentJob.Activity = 30; // in this activity worker starts checking child jobs

    ts.Complete();
}