sql - saber - Detectando lecturas sucias de un procedimiento almacenado

permisos para ejecutar procedimientos almacenados sql-server

Question

Tengo 100 hilos que cada uno llama al procedimiento almacenado como se define a continuación.

¿Cómo evito las lecturas sucias?

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity
    (@NextKey int output, @TableID int)
AS
    declare @RowCount int, @Err int

    set nocount on

    select  
        @NextKey = 0

    begin transaction

Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber 
    SET CerealNumber = CerealNumber + 1  
    WHERE CerealNumberID = @TableID

    SELECT 
        @RowCount = @@RowCount, 
        @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0            /* If Error gets here then exit         */
    BEGIN
        RAISERROR (''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 16, 1, @Err, @TableID)
        ROLLBACK TRANSACTION

        set nocount off
        return 1
    END

    IF @RowCount = 0                /* No Record then assume table is not   */
                                /* been initialized for TableID Supplied*/
    BEGIN
        RAISERROR(''No Table Record Exists in CfgCerealNumber for ID:%d   '', 16, 1, @TableID)
        set nocount off
        Rollback Transaction
        return 1
    END

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
    FROM CfgCerealNumber 
    WHERE CerealNumberID = @TableID

    SELECT @Err = @@Error                       /*Obtain updated Cereal number previously incremented*/

    IF @Err <> 0                            /* If Error gets here then exit         */
    BEGIN
        RAISERROR(''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 16, 1, @Err, @TableID)
        Rollback Transaction    
        set nocount off
        return 1
    END

    commit transaction
    set nocount off
    return 0
GO

Parece que esta parte del procedimiento almacenado devuelve el mismo valor alrededor del 0,01% del tiempo cuando se ejecuta en paralelo:

SELECT @NextKey = CerealNumber 
FROM CfgCerealNumber 
WHERE CerealNumberID = @TableID

He estructurado mi código de tal manera que evita lecturas sucias al incluir la actualización en una transacción.

¿Cómo evito las lecturas sucias?

Answer 1

Puede evitar el problema utilizando la @variable = column = expression como se describe en los Libros en @variable = column = expression . Además, dado que la instrucción se ejecuta en una transacción automática de extracto único, puede evitar transacciones explícitas.

SET QUOTED_IDENTIFIER ON;
SET ANSI_NULLS ON;
GO

CREATE PROCEDURE GetNextSerialIdentity
      @NextKey int output
    , @TableID int
AS
SET NOCOUNT ON;

UPDATE dbo.CfgSerialNumber
SET @NextKey = SerialNumber = SerialNumber + 1
WHERE SerialNumberID = @TableID;

IF @@ROWCOUNT = 0
BEGIN
RAISERROR (''No Table Record Exists in CfgCerealNumber for ID:%d   '', 
                  16,1, @TableID);
END
GO

Answer 2

La transacción begin transaction / commit garantizará que no tenga lecturas sucias .

Existe un inconveniente en el rendimiento, si el procedimiento se ejecuta desde dentro de otra transacción, el bloqueo de escritura no se liberará hasta que se haya confirmado la transacción más externa. Esto serializará todos los hilos y bloqueará la concurrencia.

Vea este ejemplo (supongamos que lleva mucho tiempo ejecutarlo):

begin tran
    ...
    exec GetNextCerealIdentity ... ; -- the write lock is established
    ...
commit tran -- the write lock is released

Es posible liberar el bloqueo antes del final de la transacción, pero debe crear un bloqueo de aplicación utilizando los procedimientos sp_getAppLock y sp_releaseAppLock dentro del procedimiento GetNextCerealIdentity .

Esto puede ser bastante complicado, debes prestar atención o puedes tener un punto muerto o algunas lecturas sucias .

Debe ejecutar sp_getAppLock al comienzo de su procedimiento y sp_releaseAppLock al final (antes de la devolución . En su ejemplo, tiene muchas devoluciones, por lo que deberá liberar el bloqueo en muchos puntos)

No olvide liberar el candado también en caso de errores. El bloqueo se liberará al final de la transacción, pero usted desea liberarlo al final del procedimiento. :-)

Debe asegurarse de que el bloqueo de su aplicación sea ​​el único que tenga sobre la mesa los contadores (CfgCerealNumber).

Normalmente SQL Server colocará un bloqueo de escritura en la tabla e interferirá con su bloqueo porque el bloqueo de escritura se liberará al final de la transacción y no al final de su procedimiento.

Debe cambiar el procedimiento a un nivel de transacción READ UNCOMMITED para que la ACTUALIZACIÓN en su código no genere bloqueos de escritura. recuerde volver a COMPROMETIDO en el mismo momento en que suelta el bloqueo de la aplicación.

Si adquiere un bloqueo en modo exclusivo , se asegurará de que solo una conexión podrá ejecutar la actualización / selección en la tabla CfgCerealNumber.

Puedes darle al candado cualquier nombre que desees. Usé el mismo nombre que la tabla (CfgCerealNumber) pero no es importante. Lo más importante es que debe usar el mismo nombre para la obtención inicial y para todas las versiones que ingrese en su código.

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
declare @RowCount int, @Err int
set nocount on
select  @NextKey = 0

-- replace begin tran with:    
EXEC sp_getapplock @Resource = ''CfgCerealNumber'', @LockMode = ''Exclusive'';  
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED

/*Update CfgCerealNumber Table */
UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  
WHERE CerealNumberID = @TableID
select  @RowCount = @@RowCount, @Err = @@Error  /*Obtain updated Cereal number previously incremented*/

if @Err <> 0   /* If Error gets here then exit         */
    begin                        
    raiserror (''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 
           16,1, @Err, @TableID)
    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = ''CfgCerealNumber'';  
    set nocount off
    return 1
    end

if @RowCount = 0 /* No Record then assume table is not   */
                 /* been initialized for TableID Supplied*/
    begin
    raiserror (''No Table Record Exists in CfgCerealNumber for ID:%d   '', 
                  16,1, @TableID)
    set nocount off

    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = ''CfgCerealNumber'';  

    return 1
    end

/*Obtain updated Cereal number previously incremented*/
SELECT @NextKey = CerealNumber 
 From CfgCerealNumber WHERE CerealNumberID = @TableID

select   @Err = @@Error /*Obtain updated Cereal number previously incremented*/

if @Err <> 0  /* If Error gets here then exit         */
    begin                        
    raiserror (''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 
           16,1, @Err, @TableID)
    -- replace Rollback Transaction with:
    SET TRANSACTION ISOLATION LEVEL READ COMMITTED
    EXEC sp_releaseapplock @Resource = ''CfgCerealNumber'';  
    set nocount off
    return 1
    end

-- replace commit transaction with:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
EXEC sp_releaseapplock @Resource = ''CfgCerealNumber'';  

set nocount off
return 0
GO

Si cambia el procedimiento así, mi ejemplo anterior no dará problemas con la concurrencia:

begin tran
    ...
    exec GetNextCerealIdentity ... ; -- the lock is established AND released
    ...
commit tran -- common "write locks" are released

Una posible adición es utilizar la construcción BEGIN / END TRY .. BEGIN / END CATCH , para que también libere el bloqueo en caso de excepciones inesperadas (esto le dará otro profesional: tendrá un único punto de salida del procedimiento, por lo que tendrá un único punto donde debe colocar las instrucciones para liberar el bloqueo y volver a colocar el nivel de aislamiento de transacción anterior.

Vea los siguientes enlaces: (sp_getAppLock) https://msdn.microsoft.com/en-us/library/ms189823.aspx y (sp_releaseAppLock) https://technet.microsoft.com/en-us/library/ms178602.aspx

Answer 3

sp_getapplock se asegurará de que la transacción tenga un bloqueo exclusivo. Las actualizaciones y lecturas se confirmarán antes de que el siguiente subproceso pueda usarlo, por lo que no puede haber lecturas sucias.

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

    ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
    AS
    declare @RowCount int, @Err int
    set nocount on
    select  @NextKey = 0
    begin transaction
   --ADDED CODE
    EXEC sp_getapplock @Resource=''MyLock'', @LockMode=''Exclusive''
                , @LockOwner=''Transaction'', @LockTimeout = 15000
    Again:
    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  WHERE CerealNumberID = @TableID
    select  @RowCount = @@RowCount, @Err = @@Error      /*Obtain updated Cereal number previously incremented*/

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
        raiserror (''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 
               16,1, @Err, @TableID)
                Rollback Transaction    
        set nocount off
        return 1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
        raiserror (''No Table Record Exists in CfgCerealNumber for ID:%d   '', 
                      16,1, @TableID)
        set nocount off
                Rollback Transaction
        return 1
        end

    /*Obtain updated Cereal number previously incremented*/
    SELECT @NextKey = CerealNumber 
     From CfgCerealNumber WHERE CerealNumberID = @TableID

    select   @Err = @@Error                     /*Obtain updated Cereal number previously incremented*/

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
        raiserror (''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 
               16,1, @Err, @TableID)
                Rollback Transaction    
        set nocount off
        return 1
        end

    commit transaction
    set nocount off
    return 0

Answer 4

Bacon Bits me ganó, pero usar la cláusula OUTPUT será la forma más fácil de solucionar tu problema de carrera. El bloqueo fuera del curso también es una opción, aunque creo que tendrá una sobrecarga ligeramente mayor. Dicho esto, usar una columna de IDENTITY o una SEQUENCE es mucho más fácil que tratar de implementar esta funcionalidad manualmente.

Me tomé la libertad de poner la respuesta en tu código y agregar algunas observaciones:

SET QUOTED_IDENTIFIER OFF
SET ANSI_NULLS OFF
GO

ALTER procedure GetNextCerealIdentity(@NextKey int output,@TableID int)
AS
set nocount on

DECLARE @RowCount int, @Err int
DECLARE @output TABLE (NextKey int)

begin transaction

    /*Update CfgCerealNumber Table */
    UPDATE CfgCerealNumber WITH (UPDLOCK) 
       Set CerealNumber = CerealNumber + 1
    OUTPUT inserted.CerealNumber INTO @output (NextKey)
     WHERE CerealNumberID = @TableID

    select @RowCount = @@RowCount, /*Obtain updated Cereal number previously incremented*/ 
           @Err = @@Error      

    if @Err <> 0                            /* If Error gets here then exit         */
        begin                        
            Rollback Transaction    
            raiserror (''GetNextCerealIDSeries Failed with Error: %d TableID: %d '', 16,1, @Err, @TableID)
            return -1
        end

    if @RowCount = 0                        /* No Record then assume table is not   */
                                    /* been initialized for TableID Supplied*/
        begin
            Rollback Transaction
            raiserror (''No Table Record Exists in CfgCerealNumber for ID:%d   '', 16,1, @TableID)
            return -1
        end

COMMIT TRANSACTION


/*Obtain updated Cereal number previously incremented*/
SELECT @NextKey = NextKey 
 From @output

return 0
GO

Observaciones:

• No es necesario hacer SET NOCOUNT OFF nuevamente antes de salir del procedimiento almacenado. A medida que salga del alcance, esta configuración volverá a ser lo que era antes de ingresar el procedimiento almacenado.
• No estoy seguro de que necesite el WITH (UPDLOCK) , pero ciertamente no le hará daño.
• Mantuve la transacción abierta lo más breve posible, no hay razón para recuperar el valor de la variable de tabla dentro de la transacción.
• Creo que es más seguro hacer primero un ROLLBACK y luego hacer un RaisError() simplemente porque este último puede hacer que la conexión sea eliminada por algún software cliente y / o puede que esté dentro de TRY...CATCH . Ambos romperán el flujo de los comandos y terminarás con el desajuste del recuento de transacciones.
• YMMV pero siempre me han dicho que use códigos de retorno negativos en caso de error. Los códigos de retorno positivos pueden usarse para indicar el número de filas ... aunque nunca he visto que se use en la práctica.

Answer 5

Una opción sería utilizar el procedimiento almacenado del sistema sp_getapplock y usar el bloqueo integrado del servidor sql para garantizar el acceso serializado a un recurso.

CREATE PROC MyCriticalWork(@MyParam INT)      
AS
    DECLARE @LockRequestResult INT
    SET @LockRequestResult=0

    DECLARE @MyTimeoutMiliseconds INT
    SET @MyTimeoutMiliseconds=5000--Wait only five seconds max then timeouit

    BEGIN TRAN

    EXEC @LockRequestResult=SP_GETAPPLOCK ''MyCriticalWork'',''Exclusive'',''Transaction'',@MyTimeoutMiliseconds
    IF(@LockRequestResult>=0)BEGIN

        /*
        DO YOUR CRITICAL READS AND WRITES HERE
        */

        --Release the lock
        COMMIT TRAN
    END ELSE
        ROLLBACK TRAN   

Answer 6

Necesita reemplazar esta declaración

UPDATE CfgCerealNumber Set CerealNumber = CerealNumber + 1  
WHERE CerealNumberID = @TableID

por esto:

declare @CerealNumber int

SELECT @CerealNumber = CerealNumber  + 1
FROM CfgCerealNumber WITH (READCOMMITTED, READPAST, ROWLOCK) 
WHERE CerealNumberID = @TableID

if @CerealNumber is not null
    UPDATE CfgCerealNumber Set CerealNumber = @CerealNumber
    WHERE CerealNumberID = @TableID
else
    raiserror (''Row was locked by another update (no dirty read and no deadlock happen) or no Table Record Exists in CfgCerealNumber for ID:%d   '', 
              16,1, @TableID)

estas sugerencias de tabla READCOMMITTED, READPAST, ROWLOCK se asegurarán de que no haya lectura sucia ni punto muerto

también le permitirá decidir si aún desea hacer una actualización

READCOMMITTED
Especifica que las operaciones de lectura cumplen con las reglas para el nivel de aislamiento READ COMMITTED mediante el bloqueo o el control de versiones de filas. Si la opción de base de datos READ_COMMITTED_SNAPSHOT está desactivada, el motor de base de datos adquiere bloqueos compartidos a medida que se leen los datos y libera esos bloqueos cuando se completa la operación de lectura. Si la opción de base de datos READ_COMMITTED_SNAPSHOT está activada, el motor de base de datos no adquiere bloqueos y utiliza el control de versiones de filas.

LEJOS
Especifica que el Motor de base de datos no lee las filas que están bloqueadas por otras transacciones. Cuando se especifica READPAST, se omiten los bloqueos a nivel de fila. Es decir, el Motor de base de datos omite las filas en lugar de bloquear la transacción actual hasta que se liberen los bloqueos. Por ejemplo, supongamos que la tabla T1 contiene una sola columna entera con los valores de 1, 2, 3, 4, 5. Si la transacción A cambia el valor de 3 a 8 pero aún no se ha confirmado, se obtiene un SELECT * FROM T1 (READPAST) valores 1, 2, 4, 5. READPAST se usa principalmente para reducir la contención de bloqueo cuando se implementa una cola de trabajo que usa una tabla de SQL Server. Un lector de colas que usa READPAST omite las entradas de cola bloqueadas por otras transacciones en la siguiente entrada de la cola disponible, sin tener que esperar hasta que las otras transacciones liberen sus bloqueos.

CHUMACERA
Especifica que los bloqueos de fila se toman cuando se utilizan ordinariamente bloqueos de página o de tabla. Cuando se especifica en transacciones que operan en el nivel de aislamiento SNAPSHOT, no se toman bloqueos de fila a menos que ROWLOCK se combine con otras sugerencias de tabla que requieren bloqueos, como UPDLOCK y HOLDLOCK.

Sugerencias de tabla de MSDN de origen (Transact-SQL)

Es posible que también necesite usar UPDLOCK y / o HOLDLOCK

Answer 7

Como ya se mencionó, puede usar la función incorporada de incremento automático, como las columnas de identidad o la secuencia.

Si no desea esto, debe hacer el acceso a la tabla en forma de serie: usando bloqueo de aplicaciones u otras habilidades.

Por ejemplo, puede agregar las sugerencias al PRIMER acceso a la tabla (en la transacción) como se muestra a continuación:

UPDATE CfgCerealNumber
Set CerealNumber = CerealNumber + 1
FROM CfgCerealNumber with (tablockx, holdlock)
WHERE CerealNumberID = @TableID

Esto garantizará el acceso secuencial a la tabla en todos los hilos paralelos.

Answer 8

Si necesita actualizar y devolver lo que actualizó, entonces simplemente usaría la cláusula OUTPUT :

UPDATE CfgCerealNumber 
SET CerealNumber = CerealNumber + 1 
OUTPUT INSERTED.CerealNumber
WHERE CerealNumberID = @TableID;

Si necesita una verificación adicional, puede RESULTAR en una variable de tabla declarada antes de devolver el conjunto de resultados del procedimiento almacenado.

Otra alternativa sería crear primero un bloqueo de bloqueo en la tabla y luego actualizar:

SELECT @CerealNumber = CerealNumber + 1 
FROM CfgCerealNumber WITH (HOLDLOCK, UPDLOCK) 
WHERE CerealNumberID = @TableID;

UPDATE CfgCerealNumber
SET CerealNumber = @CerealNumber
WHERE CerealNumberID = @TableID;

Pero pondría dinero porque he visto que esto todavía causa problemas. Confío mucho menos.