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.