sql sql-server database concurrency

Utilizar SQL Server como cola de espera con múltiples clientes



sql-server database (7)

En lugar de usar un valor booleano para Procesado, puede usar un int para definir el estado del comando:

1 = not processed 2 = in progress 3 = complete

Cada trabajador obtendría la siguiente fila con Procesado = 1, actualizaría Procesado a 2 y luego comenzaría a trabajar. Cuando el trabajo en Completo procesado se actualiza a 3. Este enfoque también permitiría la extensión de otros resultados procesados, por ejemplo, en lugar de simplemente definir que un trabajador está completo, puede agregar nuevos estados para ''Completado con éxito'' y ''Completado con errores''

Dada una tabla que actúa como cola, ¿cómo puedo configurar mejor la tabla / consultas para que múltiples clientes procedan de la cola al mismo tiempo?

Por ejemplo, la tabla a continuación indica un comando que un trabajador debe procesar. Cuando el trabajador termine, establecerá el valor procesado en verdadero.

| ID | COMMAND | PROCESSED | | 1 | ... | true | | 2 | ... | false | | 3 | ... | false |

Los clientes pueden obtener un comando para trabajar así:

select top 1 COMMAND from EXAMPLE_TABLE with (UPDLOCK, ROWLOCK) where PROCESSED=false;

Sin embargo, si hay varios trabajadores, cada uno intenta obtener la fila con ID = 2. Solo el primero obtendrá el bloqueo pesimista, el resto esperará. Entonces uno de ellos obtendrá la fila 3, etc.

¿Qué consulta / configuración permitiría a cada cliente trabajador obtener una fila diferente cada una y trabajar en ellas simultáneamente?

EDITAR:

Varias respuestas sugieren variaciones en el uso de la tabla para registrar un estado en proceso. Pensé que esto no sería posible en una sola transacción. (es decir, ¿cuál es el sentido de actualizar el estado si ningún otro trabajador lo verá hasta que se haya comprometido el txn?) Tal vez la sugerencia es:

# start transaction update to ''processing'' # end transaction # start transaction process the command update to ''processed'' # end transaction

¿Es esta la forma en que las personas generalmente abordan este problema? Me parece que el problema sería manejado mejor por el DB, si es posible.

Me mantendría alejado de jugar con candados en una mesa. Simplemente cree dos columnas adicionales como IsProcessing (bit / boolean) y ProcessingStarted (datetime). Cuando un trabajador se bloquea o no actualiza su fila después de un tiempo de espera, puede hacer que otro trabajador intente procesar los datos.

Probablemente, la mejor opción sea utilizar una columna trisSate procesada junto con una columna de versión / marca de tiempo. Los tres valores en la columna procesada indicarán indica si la fila está en proceso, procesada o no procesada.

Por ejemplo

CREATE TABLE Queue ID INT NOT NULL PRIMARY KEY, Command NVARCHAR(100), Processed INT NOT NULL CHECK (Processed in (0,1,2) ), Version timestamp)

Agarra la fila 1 sin procesar superior, establece el estado en subproceso y establece el estado de nuevo para que se procese cuando haya terminado. Base su estado de actualización en la Versión y las columnas de la clave principal. Si la actualización falla, entonces alguien ya ha estado allí.

También es posible que desee agregar un identificador de cliente, de modo que si el cliente muere mientras lo procesa, puede reiniciarse, ver la última fila y luego comenzar desde donde estaba.

Te recomiendo que vayas a Usar tablas como Colas . Las colas implementadas apropiadamente pueden manejar miles de usuarios concurrentes y un servicio tan alto como 1/2 millón de operaciones de enqueue / dequeue por minuto. Hasta SQL Server 2005, la solución era engorrosa e implicaba mezclar un SELECT y una UPDATE en una sola transacción y proporcionar la combinación correcta de sugerencias de bloqueo, como en el artículo vinculado por gbn. Afortunadamente desde SQL Server 2005 con la llegada de la cláusula OUTPUT, hay disponible una solución mucho más elegante, y ahora MSDN recomienda usar la cláusula OUTPUT :

Puede usar OUTPUT en aplicaciones que usan tablas como colas o para mantener conjuntos de resultados intermedios. Es decir, la aplicación agrega o elimina constantemente filas de la tabla

Básicamente, hay 3 partes del rompecabezas que necesitas corregir para que esto funcione de manera muy simultánea:

1) Debe dequeue atómicamente. Debes encontrar la fila, saltar las filas bloqueadas y marcarla como "dequerada" en una única operación atómica, y aquí es donde entra en juego la cláusula OUTPUT :

with CTE as ( SELECT TOP(1) COMMAND, PROCESSED FROM TABLE WITH (READPAST) WHERE PROCESSED = 0) UPDATE CTE SET PROCESSED = 1 OUTPUT INSERTED.*;

2) Debe estructurar su tabla con la clave de índice agrupada más a la izquierda en la columna PROCESSED . Si el ID se usó una clave principal, muévala como la segunda columna en la clave agrupada. El debate sobre si mantener una clave no agrupada en la columna de ID está abierto, pero estoy muy a favor de no tener ningún índice secundario no agrupado sobre las colas:

CREATE CLUSTERED INDEX cdxTable on TABLE(PROCESSED, ID);

3) No debe consultar esta tabla por ningún otro medio que no sea Dequeue. Intentar hacer operaciones Peek o tratar de usar la tabla tanto como una cola como una tienda muy probablemente conducirá a interbloqueos y ralentizará dramáticamente el rendimiento.

La combinación de dequeue atómico, READPAST insinúa los elementos de búsqueda para dequeuear y la tecla más a la izquierda del índice agrupado basado en el bit de procesamiento asegura un rendimiento muy alto bajo una carga altamente concurrente.

Una forma es marcar la fila con una sola declaración de actualización. Si lee el estado en la cláusula where y lo cambia en la cláusula set , no puede haber otro proceso intermedio, porque la fila se bloqueará. Por ejemplo:

declare @pickup_id int set @pickup_id = 1 set rowcount 1 update YourTable set status = ''picked up'' , @pickup_id = id where status = ''new'' set rowcount 0 return @pickup_id

Esto usa rowcount para actualizar una fila como máximo. Si no se encontró una fila, @pickup_id será -1 .

Si desea serializar sus operaciones para múltiples clientes, puede simplemente usar bloqueos de aplicaciones.

BEGIN TRANSACTION EXEC sp_getapplock @resource = ''app_token'', @lockMode = ''Exclusive'' -- perform operation EXEC sp_releaseapplock @resource = ''app_token'' COMMIT TRANSACTION