sql-server - examples - set transaction isolation level read uncommitted vs with nolock

Nivel mínimo de aislamiento de transacciones para evitar "Actualizaciones perdidas"

Question

Con los niveles de aislamiento de transacciones de SQL Server, puede evitar ciertos problemas de concurrencia no deseados, como lecturas sucias, etc.

El que me interesa en este momento es la pérdida de actualizaciones , el hecho de que dos transacciones pueden sobrescribir las actualizaciones de la otra sin que nadie se dé cuenta. Veo y escucho declaraciones contradictorias en cuanto a qué nivel de aislamiento como mínimo debo elegir para evitar esto.

Kalen Delaney en su libro "SQL Server Internals" dice (Capítulo 10 - Transacciones y concurrencia - Página 592):

En el aislamiento Leer sin confirmar, todos los comportamientos descritos anteriormente, excepto las actualizaciones perdidas , son posibles.

Por otro lado, un entrenador de SQL Server independiente que nos dio una clase nos dijo que necesitamos al menos "Lectura repetible" para evitar actualizaciones perdidas.

Entonces, ¿quién tiene razón? ¿¿Y por qué??

Answer 1

El ejemplo en el libro es de Clerk A y Clerk B que reciben envíos de Widgets.

Ambos verifican el inventario actual, ver 25 está en stock. El empleado A tiene 50 widgets y actualizaciones a 75, el empleado B tiene 20 widgets y por lo tanto las actualizaciones a 45 sobrescriben la actualización anterior.

Supongo que ella quiso decir que este fenómeno se puede evitar en todos los niveles de aislamiento si el Secretario A lo hace.

UPDATE Widgets
SET StockLevel = StockLevel + 50
WHERE ...

y Clerk B haciendo

UPDATE Widgets
SET StockLevel = StockLevel + 20
WHERE ...

Ciertamente, si SELECT y UPDATE se realizan como operaciones separadas, necesitaría una repeatable read para evitar esto, por lo que el bloqueo S en la fila se mantiene durante la transacción (lo que llevaría a un interbloqueo en este escenario)

Answer 2

Las actualizaciones perdidas pueden ocurrir incluso si las lecturas y escrituras están en transacciones separadas, como cuando los usuarios leen datos en páginas web y luego actualizan. En tales casos, ningún nivel de aislamiento puede protegerlo, especialmente cuando las conexiones se reutilizan desde un grupo de conexiones. Deberíamos usar otros enfoques, como rowversion. Aquí está mi respuesta enlatada.

Answer 3

Lo siguiente es una cita de 70-762 Developing SQL Databases (p. 212) :

Otro problema potencial puede ocurrir cuando dos procesos leen la misma fila y luego actualizan esos datos con valores diferentes. Esto puede suceder si una transacción lee primero un valor en una variable y luego utiliza la variable en una instrucción de actualización en un paso posterior. Cuando esta actualización se ejecuta, otra transacción actualiza los mismos datos. Cualquiera de estas transacciones se confirma primero se convierte en una actualización perdida porque fue reemplazada por la actualización en la otra transacción. No puede usar niveles de aislamiento para cambiar este comportamiento, pero puede escribir una aplicación que permita específicamente actualizaciones perdidas.

Por lo tanto, parece que ninguno de los niveles de aislamiento puede ayudarlo en tales casos y necesita resolver el problema en el propio código. Por ejemplo:

DROP TABLE IF EXISTS [dbo].[Balance];

CREATE TABLE [dbo].[Balance]
(
    [BalanceID] TINYINT IDENTITY(1,1)
   ,[Balance] MONEY
   ,CONSTRAINT [PK_Balance] PRIMARY KEY
   (
        [BalanceID]
   )
);

INSERT INTO [dbo].[Balance] ([Balance])
VALUES (100);

-- query window 1
BEGIN TRANSACTION;

    DECLARE @CurrentBalance MONEY;

    SELECT @CurrentBalance = [Balance]
    FROM [dbo].[Balance]
    WHERE [BalanceID] = 1;

    WAITFOR DELAY ''00:00:05''

    UPDATE [dbo].[Balance]
    SET [Balance] = @CurrentBalance + 20
    WHERE [BalanceID] = 1;

COMMIT TRANSACTION;

-- query window 2
BEGIN TRANSACTION;

    DECLARE @CurrentBalance MONEY;

    SELECT @CurrentBalance = [Balance]
    FROM [dbo].[Balance]
    WHERE [BalanceID] = 1;

    UPDATE [dbo].[Balance]
    SET [Balance] = @CurrentBalance + 50
    WHERE [BalanceID] = 1;

COMMIT TRANSACTION;

Cree la tabla y ejecute cada parte del código en ventanas de consulta separadas. Cambiar el nivel de aislamiento no hace nada. Por ejemplo, la única diferencia entre read committed y repeatable read es que la última, bloquea la segunda transacción mientras la primera finaliza y luego sobrescribe el valor.

Answer 4

Mi experiencia es que con Read Uncommitted ya no obtienes "actualizaciones perdidas", sin embargo, puedes obtener "rollbacks perdidos". El capacitador de SQL probablemente se refería a ese problema de concurrencia, por lo que la respuesta que probablemente esté buscando es Repeatable Read .

Dicho esto, estaría muy interesado si alguien tiene experiencia que vaya en contra de esto.

Answer 5

No sé si es demasiado tarde para responder, pero estoy aprendiendo sobre los niveles de aislamiento de transacciones en la universidad y, como parte de mi investigación, encontré este enlace:

Microsoft Technet

Específicamente el párrafo en cuestión es:

Actualización perdida

Una actualización perdida puede interpretarse de una de dos maneras. En el primer escenario, se considera que una actualización perdida ha tenido lugar cuando los datos que han sido actualizados por una transacción son sobrescritos por otra transacción, antes de que la primera transacción se confirme o se revierta. Este tipo de actualización perdida no puede ocurrir en SQL Server 2005 porque no está permitido bajo ningún nivel de aislamiento de transacción.

La otra interpretación de una actualización perdida es cuando una transacción (Transacción # 1) lee datos en su memoria local, y luego otra transacción (Transacción # 2) cambia estos datos y confirma su cambio. Después de esto, la transacción n. ° 1 actualiza los mismos datos según lo que leyó en la memoria antes de que se ejecutara la transacción n. ° 2. En este caso, la actualización realizada por la Transacción # 2 puede considerarse una actualización perdida.

Así que, en esencia, ambas personas tienen razón.

Personalmente (y estoy abierto a equivocarme, así que corríjame ya que estoy aprendiendo esto). Tomo de esto los siguientes dos puntos:

1. El objetivo de un entorno de transacción es evitar actualizaciones perdidas como se describe en el párrafo superior. Entonces, si incluso el nivel de transacción más básico no puede hacer eso, entonces ¿por qué molestarse en usarlo?

2. Cuando las personas hablan de actualizaciones perdidas, saben que se aplica el primer párrafo, y por lo tanto, en términos generales, significan el segundo tipo de actualización perdida.

Nuevamente, corríjame si algo está mal aquí, ya que me gustaría entender esto también.

Answer 6

Según lo señala Francis Rodgers, lo que puede confiar en la implementación de SQL Server es que una vez que una transacción actualiza algunos datos, cada nivel de aislamiento siempre emite "bloqueos de actualización" sobre los datos y niega las actualizaciones y escrituras de otra transacción, sea cual sea el nivel de aislamiento. es. Puede estar seguro de que este tipo de actualizaciones perdidas están cubiertas.

Sin embargo, si la situación es que una transacción lee algunos datos (con un nivel de aislamiento diferente al de Lectura repetible), entonces otra transacción puede cambiar estos datos y confirma su cambio, y si la primera transacción actualiza los mismos datos, pero esta vez Basado en la copia interna que hizo, el sistema de administración no puede hacer nada para guardarlo.

Su respuesta en ese escenario es usar Lectura repetible en la primera transacción, o tal vez usar un bloqueo de lectura de la primera transacción sobre los datos (no lo sé de manera segura. Solo sé la existencia de este bloqueos y que puede usarlos. Tal vez esto ayude a cualquiera que esté interesado en este enfoque Microsoft (Diseñar transacciones y optimizar el bloqueo ).