sql server - tablas - Transacción eficiente, bloqueo de registros

sql server bloqueo de tablas

Question

Tengo un procedimiento almacenado, que selecciona 1 registro de nuevo. el procedimiento almacenado podría llamarse desde varias aplicaciones diferentes en diferentes PC. La idea es que el procedimiento almacenado recupere el siguiente registro que debe procesarse, y si dos aplicaciones llaman al proceso almacenado al mismo tiempo, no se debe recuperar el mismo registro. Mi consulta está debajo, intento escribir la consulta de la manera más eficiente posible (sql 2008). ¿Se puede hacer de manera más eficiente que esto?

CREATE PROCEDURE GetNextUnprocessedRecord
AS
BEGIN
    SET NOCOUNT ON;

    --ID of record we want to select back
    DECLARE @iID BIGINT     

    -- Find the next processable record, and mark it as dispatched
    -- Must be done in a transaction to ensure no other query can get
    -- this record between the read and update
    BEGIN TRAN

        SELECT TOP 1
            @iID = [ID]
        FROM
            --Don''t read locked records, only lock the specific record
            [MyRecords] WITH (READPAST, ROWLOCK)
        WHERE
            [Dispatched] is null
        ORDER BY
            [Received]

        --Mark record as picked up for processing   
        UPDATE 
            [MyRecords]
        SET
            [Dispatched] = GETDATE()
        WHERE
            [ID] = @iID     

    COMMIT TRAN

    --Select back the specific record
    SELECT 
        [ID],
        [Data]
    FROM    
        [MyRecords] WITH (NOLOCK, READPAST)
    WHERE
        [ID] = @iID

END

Answer 1

El uso de la sugerencia de bloqueo de READAPTO es correcto y su SQL se ve bien.

Yo agregaría usar XLOCK aunque también es HOLDLOCK / SERIALIZABLE

...
[MyRecords] WITH (READPAST, ROWLOCK, XLOCK)
...

Esto significa que obtienes la ID y que bloqueas esa fila exclusivamente mientras la llevas a cabo y la actualizas.

Editar: agregue un índice en las columnas Enviado y Recibido para hacerlo más rápido. Si [ID] (supongo que es el PK) no está agrupado, INCLUDE [ID]. Y filtra el índice también porque es SQL 2008

También puedes usar esta construcción que lo hace todo de una vez sin XLOCK o HOLDLOCK

UPDATE
    MyRecords
SET
    --record the row ID
    @id = [ID],
    --flag doing stuff
    [Dispatched] = GETDATE()
WHERE
    [ID] = (SELECT TOP 1 [ID] FROM MyRecords WITH (ROWLOCK, READPAST) WHERE Dispatched IS NULL ORDER BY Received)

ACTUALIZAR, asignar, establecer en uno

Answer 2

Puede asignar a cada proceso de selector una identificación única y agregar columnas pickerproc y pickstate a sus registros. Entonces

ACTUALIZAR MyRecords
SET pickerproc = myproc,
pickstate = ''I'' - para ''I''n process
DONDE Id = (SELECCIONE MAX (Id) FROM MyRecords WHERE pickstate = ''A'') - ''A''vailable

Eso le proporciona su registro en un paso atómico, y puede hacer el resto de su procesamiento en su tiempo libre. Luego puede configurar pickstate en ''C''omplete'', ''E''rror o lo que sea cuando se resuelva.

Creo que Mitch se está refiriendo a otra buena técnica en la que creas una tabla de cola de mensajes e insertas los Id. Allí. Hay varios hilos SO: busque ''tabla de cola de mensajes''.

Answer 3

Puede mantener MyRecords en una tabla "MEMORY" para un procesamiento más rápido.