Выбор запроса пропускает записи во время параллельных обновлений

У меня есть таблица, которая обрабатывается одновременно N потоками.

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

Работа добавляется к таблице с помощью State=0 (New) - ее может потреблять любой рабочий в этом состоянии. Когда рабочий получает этот элемент очереди, State изменен на 50 (Processing), и задание становится недоступным для других потребителей (рабочие звонят [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

После обработки задание State можно снова изменить на 0 (New) или его можно установить как 100 (Completed).

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] с @ExcludedState=100), который возвращает количество незавершенных заданий, когда он возвращает 0, родительское задание State может быть установлено на 100 (Completed).

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% не имеют State=100, когда SP возвращается 0. Кажется, что SP пропускает записи, которые не находятся в состоянии 100 (Completed), но почему это происходит и как мы можем это предотвратить?

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();
}

Ответы

Ответ 1

Было бы очень неприятно, если бы на самом деле ваша процедура Jobs_GetCountWithExcludedState возвращала счет 0 записей, если на самом деле были зафиксированные записи, соответствующие вашим критериям. Это довольно простая процедура. Таким образом, есть две возможности:

Запрос не работает из-за проблемы с SQL Server или данными коррупция.
На самом деле нет зафиксированных записей, соответствующих критериям на момент процедура выполняется.

Коррупция - маловероятная, но возможная причина. Вы можете проверить наличие коррупции с помощью DBCC CHECKDB.

Скорее всего, нет записей зафиксированных, которые имеют Parent_ID, равный параметру @ParentId и не находятся в состоянии 100 в момент его запуска.

Я подчеркиваю зафиксированный, потому что это увидит транзакция.

Вы никогда не объясняете в своем вопросе, как Parent_ID устанавливается на заданиях. Моя первая мысль заключается в том, что, возможно, вы проверяете необработанные дочерние задания и не обнаруживаете ни одного, но затем другой процесс добавляет его как Parent_ID другого неполного задания. Возможно ли это?

Я вижу, что вы добавили обновление, чтобы показать, что при добавлении записи о дочернем задании обновление родительской и дочерней записей завершается транзакцией. Это хорошо, но не вопрос, который я задавал. Это сценарий, который я рассматриваю как возможность:

Запись записи задана для родителя.
Jobs_GetFirstByType захватывает родительское задание.
Рабочий поток обрабатывает его и вызывает Jobs_UpdateStatus и обновляет его статус до 100.
Что-то вызывает Jobs_GetCountWithExcludedState с заданием и возвращает 0.
Создается дочернее задание и прикрепляется к завершенной записи родительского задания... что делает его теперь неполным снова.

Я не говорю, что это то, что происходит... Я просто спрашиваю, возможно ли это и какие шаги вы предпринимаете, чтобы предотвратить это? Например, в приведенном выше коде в обновлении вашего вопроса вы выбираете ParentJob для привязки дочернего элемента к внешней стороне транзакции. Может быть, вы выбираете родительское задание, а затем оно завершается, прежде чем запускать транзакцию, которая добавляет ребенка к родительскому? Или, может быть, последнее дочернее задание родительского задания завершается так, что рабочий поток проверяет и отмечает, что родительский закончен, но какой-то другой рабочий поток уже выбрал задание родителем для нового дочернего задания?

Существует много разных сценариев, которые могут вызвать симптом, который вы описываете. Я считаю, что проблема заключается в том, что в некотором коде, который вы не поделили с нами, особенно о том, как создаются задания и код, вызывающий вызовы, Jobs_GetCountWithExcludedState. Если вы можете дать больше информации, я думаю, что вы с большей вероятностью найдете полезный ответ, иначе лучшее, что мы можем сделать, - это угадать все, что может произойти в коде, который мы не можем видеть.

Ответ 2

Ваша проблема почти наверняка вызвана вашим выбором уровня изоляции "READ COMMITTED". Поведение этого зависит от вашего параметра конфигурации для READ_COMMITTED_SNAPSHOT, но в любом случае он позволяет другому потоку транзакций изменять записи, которые были бы видны вашим SELECT, между вашим SELECT и вашим UPDATE, поэтому у вас есть условие гонки.

Попробуйте еще раз с уровнем изоляции "SERIALIZABLE" и посмотрите, устраняет ли это вашу проблему. Для получения дополнительной информации о уровнях изоляции документация очень полезна:

https://msdn.microsoft.com/en-AU/library/ms173763.aspx

Ответ 3

Ваш код sql выглядит отлично. Поэтому проблема заключается в том, как она используется.

Гипотеза № 0
Процедура "Jobs_GetCountWithExcludedState" вызывается с абсолютно неправильным идентификатором. Потому что да когда-то проблема на самом деле просто небольшая ошибка. Я сомневаюсь, что это ваш случай.

Гипотеза № 1
Код, проверяющий поле "Activity = 30", делает это в уровне изоляции "READ UNCOMMITED". Затем он вызывается "Jobs_GetCountWithExcludedState" с родительским идентификатором, который может быть не совсем готов для него, потому что транзакция ввода может еще не закончиться или была отменена.

Гипотеза № 2
Процедура "Jobs_GetCountWithExcludedState" вызывается с идентификатором, который больше не является дочерним. Могло быть много причин, почему это происходит.
Например,

Транзакция, по которой вставленное дочернее задание завершилось по какой-либо причине, но эта процедура была вызвана в любом случае.
Одно дочернее задание было удалено и должно было быть заменено.
и т.д.

Гипотеза № 3
Процедура "Jobs_GetCountWithExcludedState" вызывается до того, как childJob получит свой родительский указатель.

Заключение
Как вы можете видеть, нам нужна дополнительная информация о двух вещах:
1. Как вызывается "Job_GetCountWithExcludedState".
2. Как вставляются задания. Является ли parentId назначенным во время вставки или обновляется немного позже? Вставляются ли они в пакет? Есть ли приложенный код к нему, который делает другие вещи?

Это также я рекомендую вам взглянуть, чтобы проверить выше гипотезу, потому что проблема наиболее вероятна в программе.

Возможный рефакторинг для аннулирования всех этих гипотез
Попросите базу данных сообщить программе, какие задачи родителей выполняются напрямую.

Точно так же, как "Jobs_GetFirstByType", может быть Job_GetFirstParentJobToComplete ", который мог бы вернуть следующее незавершенное родительское задание с завершенными дочерними элементами, если оно есть. Это также может быть представление, которое возвращает все из них. В любом случае использование" Jobs_GetCountWithExcludedState "тогда больше не используются, тем самым исключая всю мою гипотезу. Новая процедура или представление должны быть ЧИТАЕМЫМИ КОМИТЕТОМ или выше.

Ответ 4

У меня есть рекомендация на стороне клиента и как вы обрабатываете транзакцию и срок службы соединения для каждого потока. Поскольку все команды выполняются на клиентской транзакции.