Pregunta

¿Cuál es el beneficio de aplicar bloqueos a la siguiente declaración?

Del mismo modo, ¿qué problema veríamos si no incluyéramos estas sugerencias? es decir, ¿previenen una condición de carrera, mejoran el rendimiento o tal vez algo más? Pidiendo que tal vez están incluidos para evitar algún problema que no he considerado en lugar de la condición de carrera que asumí.

NB: Este es un desbordamiento de una pregunta que se hace aquí:SQL Threadsafe UPDATE TOP 1 para la cola FIFO

La declaración en cuestión

WITH nextRecordToProcess AS
(
    SELECT TOP(1) Id, StatusId
    FROM    DemoQueue
    WHERE   StatusId = 1 --Ready for processing
    ORDER BY DateSubmitted, Id 
)
UPDATE nextRecordToProcess
SET StatusId = 2 --Processing
OUTPUT Inserted.Id 

RequisitoEl SQL se utiliza para recuperar un registro no procesado de una cola.El registro que se debe obtener debe ser el primer registro en la cola con el estado Listo (StatusId = 1).Puede haber múltiples trabajadores / sesiones procesando mensajes desde esta cola.Queremos asegurarnos de que cada registro en la cola solo sea recogido una vez (es decir, por un solo trabajador), y que cada trabajador procese los mensajes en el orden en que aparecen en la cola.Está bien que un trabajador trabaje más rápido que otro (es decir, si el trabajador A recoge el registro 1, entonces el trabajador B recoge el registro 2, está bien si el trabajador B completa el procesamiento del registro 2 antes de que el trabajador A haya terminado de procesar el registro 1). Solo nos preocupa el contexto de recoger el registro.No hay transacciones en curso; es decir, solo queremos recoger el registro de la cola; no necesitamos mantenerlo bloqueado hasta que regresemos para avanzar el estado deProcessing aProcessed.SQL adicional para el contexto:

CREATE TABLE Statuses
(
    Id SMALLINT NOT NULL PRIMARY KEY CLUSTERED
    , Name NVARCHAR(32) NOT NULL UNIQUE
)
GO
INSERT Statuses (Id, Name)
VALUES (0,'Draft')
, (1,'Ready')
, (2,'Processing')
, (3,'Processed')
, (4,'Error')
GO
CREATE TABLE DemoQueue
(
    Id BIGINT NOT NULL IDENTITY(1,1) PRIMARY KEY CLUSTERED
    , StatusId SMALLINT NOT NULL FOREIGN KEY REFERENCES Statuses(Id)
    , DateSubmitted DATETIME --will be null for all records with status 'Draft'
)
GO

Declaración sugerida

En los diversos blogs que discuten las colas, y en la pregunta que causó esta discusión, se sugiere que la declaración anterior se cambie para incluir sugerencias de bloqueo de la siguiente manera:

WITH nextRecordToProcess AS
(
    SELECT TOP(1) Id, StatusId
    FROM    DemoQueue WITH (UPDLOCK, ROWLOCK, READPAST)
    WHERE   StatusId = 1 --Ready for processing
    ORDER BY DateSubmitted, Id 
)
UPDATE nextRecordToProcess
SET StatusId = 2 --Processing
OUTPUT Inserted.Id 

Mi punto de vista

Entiendo que el bloqueo requerido requiere los beneficios de estos consejos:

UPDLOCK: Debido a que estamos seleccionando el registro para actualizar su estado, debemos asegurarnos de que cualquier otra sesión que lea este registro después de haberlo leído, pero antes de actualizarlo, no podrá leer el registro con la intención de actualizarlo (o más bien, dicha declaración tendría que esperar hasta que hayamos realizado nuestra actualización y liberado el bloqueo antes de que la otra sesión pueda ver nuestro registro con su nuevo valor).ROWLOCK: Mientras bloqueamos el registro, queremos asegurarnos de que nuestro bloqueo solo afecte la fila que estamos bloqueando; es decir, como no necesitamos bloquear muchos recursos / no queremos afectar otros procesos / queremos que otras sesiones puedan leer el siguiente elemento disponible en la cola, incluso si ese elemento está en la misma página que nuestro registro bloqueado .READPAST: si otra sesión ya está leyendo un elemento de la cola, en lugar de esperar a que esa sesión libere su bloqueo, nuestra sesión debería elegir el siguiente registro disponible (no bloqueado) en la cola.

es decir, si estuviéramos ejecutando el siguiente código, creo que esto tendría sentido:

DECLARE @nextRecordToProcess BIGINT

BEGIN TRANSACTION

SELECT TOP (1) @nextRecordToProcess = Id
FROM    DemoQueue WITH (UPDLOCK, ROWLOCK, READPAST)
WHERE   StatusId = 1 --Ready for processing
ORDER BY DateSubmitted, Id 

--and then in a separate statement

UPDATE DemoQueue
SET StatusId = 2 --Processing
WHERE Id = @nextRecordToProcess

COMMIT TRANSACTION

--@nextRecordToProcess is then returned either as an out parameter or by including a `select @nextRecordToProcess Id`

Sin embargo, cuando la selección y la actualización se producen en la misma declaración, habría asumido que ninguna otra sesión podría leer el mismo registro entre la lectura y actualización de nuestra sesión; así que no habría necesidad de pistas explícitas de bloqueo.

¿He entendido mal algo fundamentalmente sobre cómo funcionan las cerraduras? ¿O la sugerencia de estas sugerencias está relacionada con algún otro caso de uso similar pero diferente?