atomic compare and swap en una database

Estoy trabajando en una solución de queues de trabajo. Quiero consultar una fila determinada en la database, donde una columna de estado tiene un valor específico, modificar ese valor y devolver la fila, y quiero hacerlo atómicamente, para que ninguna otra consulta lo vea:

begin transaction select * from table where pk = x and status = y update table set status = z where pk = x commit transaction --(the row would be returned) 

debe ser imposible que 2 o más consultas simultáneas devuelvan la fila (una ejecución de consulta vería la fila mientras su estado = y) – algo así como una operación entrelazada CompareAndExchange.

Sé que se ejecuta el código anterior (para el server SQL), pero ¿el intercambio siempre será atómico?

Necesito una solución que funcione para SQL Server y Oracle

¿PK es la key principal? Entonces esto no es un problema, si ya sabes la key principal no hay deporte. Si pk es la key principal, entonces surge la pregunta obvia: ¿ cómo sabes el pk del elemento para dequeue …

El problema es si no conoce la key principal y desea deleccionar la siguiente 'disponible' (es decir, estado = y) y marcarla como eliminada (eliminarla o establecer el estado = z).

La forma correcta de hacerlo es usar una sola statement. Desafortunadamente, la syntax difiere entre Oracle y SQL Server. La syntax de SQL Server es:

 update top (1) [<table>] set status = z output DELETED.* where status = y; 

No estoy lo suficientemente familiarizado con la cláusula RETURNING de Oracle para dar un ejemplo similar al de SQL OUTPUT.

Otras soluciones de SQL Server requieren indicaciones de locking en SELECT (con UPDLOCK) para que sean correctas. En Oracle, la avenida preferida es usar FOR FORDATE, pero eso no funciona en SQL Server, ya que FOR UPDATE debe usarse junto con los cursores en SQL.

En cualquier caso, el comportamiento que tienes en la publicación original es incorrecto. Varias sesiones pueden seleccionar la misma fila (s) e incluso actualizarla, devolviendo el mismo elemento (s) descatalogado (s) a varios lectores.

Como regla general, para realizar una operación como esta atómica, deberá asegurarse de establecer un locking exclusivo (o de actualización) cuando realice la selección para que ninguna otra transacción pueda leer la fila antes de la actualización.

La syntax típica para esto es algo así como:

  select * from table where pk = x and status = y for update 

pero tendrías que searchlo para estar seguro.

Tengo algunas aplicaciones que siguen un patrón similar. Hay una table como la tuya que representa una queue de trabajo. La tabla tiene dos columnas adicionales: thread_id y thread_date. Cuando la aplicación solicita trabajo en la queue, envía una identificación de hilo. Luego, una sola statement de actualización actualiza todas las filas aplicables con la columna de id. De subprocess con la id enviada y la columna de date de subprocess con la hora actual. Después de esa actualización, selecciona todas las filas con esa identificación de hilo. De esta forma, no necesita declarar una transacción explícita. El "locking" ocurre en la actualización inicial.

La columna thread_date se usa para garantizar que no termines con elementos de trabajo huérfanos. ¿Qué sucede si los artículos se extraen de la queue y luego se bloquea la aplicación? Tienes que tener la capacidad de probar esos elementos de trabajo de nuevo. Por lo tanto, puede tomar todos los artículos de la queue que no se hayan marcado como completados pero que hayan sido asignados a un hilo con una date de hilo en el pasado distante. Depende de ti definir "distante".

Prueba esto. La validation está en la statement UPDATE.

Código

 IF EXISTS (SELECT * FROM sys.tables WHERE name = 't1') DROP TABLE dbo.t1 GO CREATE TABLE dbo.t1 ( ColID int IDENTITY, [Status] varchar(20) ) GO DECLARE @id int DECLARE @initialValue varchar(20) DECLARE @newValue varchar(20) SET @initialValue = 'Initial Value' INSERT INTO dbo.t1 (Status) VALUES (@initialValue) SELECT @id = SCOPE_IDENTITY() SET @newValue = 'Updated Value' BEGIN TRAN UPDATE dbo.t1 SET @initialValue = [Status], [Status] = @newValue WHERE ColID = @id AND [Status] = @initialValue SELECT ColID, [Status] FROM dbo.t1 COMMIT TRAN SELECT @initialValue AS '@initialValue', @newValue AS '@newValue' 

Resultados

 ColID Status ----- ------------- 1 Updated Value @initialValue @newValue ------------- ------------- Initial Value Updated Value