MS SQL Server - Tabela com lógica de fila - Como posso ter certeza de que tarefas paralelas não recuperarão o mesmo ID?

na fila

Como falo neste post , uma maneira confiável de processar filas é usar uma consulta como esta:

WITH 
    q AS
(
    SELECT TOP (1) 
        x.* 
    FROM sys_QueueJob x WITH (ROWLOCK, UPDLOCK, READPAST)
    WHERE x.ExecutionFinishedOn IS NULL
    AND 
    (
          x.ExecutionStartedOn IS NULL 
       OR x.ExecutionStartedOn < DATEADD(HOUR, -1, GETDATE())
    )
    ORDER BY 
        x.CreatedOn ASC
        /*You may need to also order by Id if CreatedOn isn't unique*/
)
UPDATE 
    q
SET 
    ExecutionStartedOn = GETDATE()
OUTPUT 
    Deleted.Id as Result;

Obviamente, o maior desafio para a maioria das consultas de fila é indexá-las para facilitar a distribuição do trabalho. Normalmente, um índice filtrado que exclui o trabalho concluído (ExecutionFinishedOn) e chaves nos elementos de filtragem/classificação é suficiente (ExecutionStartedOn, CriadoOn).

No seu caso, você está procurando itens que não foram iniciados e itens que foram iniciados há mais de uma hora. Pode fazer mais sentido dividi-los em dois "trabalhadores" que procuram cada disposição de forma independente ou adicionar alguma lógica para procurar linhas iniciadas há mais de uma hora, se nenhuma linha for encontrada que ainda não tenha sido iniciada.

comparando técnicas

A razão pela qual usei uma expressão de tabela comum em vez de uma atualização com uma subconsulta é para evitar um plano de consulta com múltiplas chamadas de bloqueio para a tabela.

Dê uma olhada neste exemplo, que usa uma pequena visualização auxiliar chamada dbo.WhatsUpLocks para resumir os bloqueios mantidos por uma sessão. Também estou usando Eventos Estendidos para observar bloqueios adquiridos e liberados em minha tabela e objetos relacionados (índices, restrições padrão, etc.) quando a consulta de atualização é executada.

primeira técnica

BEGIN TRANSACTION;
    DECLARE
        @id integer, 
        @reputation integer;

    WITH 
        q4 AS
    (
        SELECT TOP (1) 
            fq.*
        FROM dbo.four_queue AS fq WITH(READPAST, ROWLOCK, UPDLOCK)
        WHERE fq.in_process = 0
        ORDER BY 
            fq.id
    )
    UPDATE q4
    SET 
        q4.in_process = 1,
        q4.start_date = SYSDATETIME(),
        @id = q4.id,
        @reputation = q4.reputation
    FROM q4;
    
    SELECT
        wul.*
    FROM dbo.WhatsUpLocks(@@SPID) AS wul;
ROLLBACK;
GO 

Aqui está o plano de consulta, com uma única referência de leitura para a tabela:

Aqui estão os bloqueios que foram obtidos e liberados durante a execução da consulta:

E aqui estão os bloqueios que permanecem antes da transação ser revertida:

segunda técnica

Vamos comparar isso com sua tentativa inicial, aplicada à configuração da tabela na postagem que linkei anteriormente.

BEGIN TRANSACTION;
    DECLARE
        @id integer, 
        @reputation integer;

    UPDATE 
        q4
    SET
        q4.in_process = 1,
        q4.start_date = SYSDATETIME(),
        @id = q4.id,
        @reputation = q4.reputation
    FROM dbo.four_queue AS q4
    WHERE q4.id = 
    (
        SELECT TOP (1) 
            x.id
        FROM dbo.four_queue AS x WITH (ROWLOCK, UPDLOCK, READPAST)
        WHERE x.in_process = 0
        ORDER BY 
            x.id
    );

    SELECT
        wul.*
    FROM dbo.WhatsUpLocks(@@SPID) AS wul;
ROLLBACK;
GO 

Aqui está o plano de consulta, que agora possui duas referências de leitura para a tabela:

Aqui estão os bloqueios que foram obtidos e liberados durante a execução da consulta:

E aqui estão os bloqueios que permanecem antes da transação ser revertida:

diferenças

Os bloqueios adquiridos e liberados durante a execução da consulta são muito diferentes. Há muito menos na expressão de tabela comum do que na atualização com subconsulta, em grande parte devido à referência única de leitura e atualização no plano de consulta.

Há apenas uma pequena diferença nos bloqueios restantes antes da consulta ser revertida. A linha inferior do meu resultado mostra um bloqueio IX nas páginas e a sua mostra bloqueios UIX nas páginas.

A soma dessas diferenças depende da simultaneidade durante o processamento das filas, mas você também pode fazer isso da maneira certa para não precisar se preocupar com o colapso mais tarde.

Como nota final, não quero que você saia desse pensamento de que expressões comuns de mesa são mágicas. Eles não são. Você poderia obter os mesmos resultados com uma tabela derivada ou uma visualização criada que produzisse um único resultado para atualização, sem o uso de uma subconsulta. Nesse caso, acho que a expressão de tabela comum torna a consulta mais compreensível. Isso é tudo.

Acredito que (os testes provarão) usando

CREATE TABLE BLAH (
ID INT IDENTITY(1,1) PRIMARY KEY,
StartTime DATETIME,
EndTime DATETIME,
FinishedTime DATETIME,
TH INT,
CreatedTime DATETIME DEFAULT GETDATE()
)

INSERT INTO Blah (TH)
SELECT TOp 100 (TH)
FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY a.object_id) TH
        FROM sys.objects a
    ) a1

BEGIN TRANSACTION 
UPDATE table1 
  SET StartTime = GETDATE() 
 OUTPUT DELETED.ID  
 FROM BLAH table1
    JOIN (  SELECT TOP 1 ID 
            FROM BLAH table2
            WHERE StartTime IS NULL
            ORDER BY CreatedTime ASC) Selector
 ON Selector.ID = table1.ID
 WHERE StartTime is null 
COMMIT --TRANSACTION

irá LOCK a tabela para UPDATE durante essa transação, para que você capture o ID não processado e possa prosseguir com o processamento dessa linha - enquanto outra chamada captura a próxima.

se você ler https://learn.microsoft.com/en-us/sql/relational-databases/sql-server-transaction-locking-and-row-versioning-guide?view=sql-server-ver16 isso ajudará a ver como as coisas são gerenciadas no SQL.

Parece-me que você está reprocessando registros com StartTime configurado que não terminam há uma hora? pode ser necessário ajustar a cláusula where para incluí-la. Ou faça com que o processo processando o horário de início da atualização do ID para NULL quando ele travar... estará automaticamente disponível para seleção novamente.

Na verdade, executar create, insert e update (sem commit) e executar a atualização novamente (sem commit), gera 1 e depois 2 - não se esqueça de COMMIT duas vezes para fechar transações abertas após o teste.