update - select for share mysql

Bloqueo optimista en MySQL

Question

No puedo encontrar ningún detalle sobre el bloqueo optimista en MySQL. Sin embargo, leí que iniciar una transacción mantiene las actualizaciones de dos entidades sincronizadas, no impide que dos usuarios actualicen los datos al mismo tiempo y causen un conflicto.

¿Aparentemente el bloqueo optimista resolverá este problema? ¿Cómo se aplica esto en MySQL. ¿Hay sintaxis / palabra clave SQL para esto? ¿O MySQL tiene un comportamiento por defecto?

Gracias chicos.

Answer 1

El punto es que el bloqueo optimista no es una función de base de datos, ni para MySQL ni para otros: el bloqueo optimista es una práctica que se aplica utilizando la base de datos con instrucciones estándar.

Pongamos un ejemplo muy simple y digamos que desea hacer esto en un código que varios usuarios / clientes puedan ejecutar simultáneamente:

1. SELECCIONE datos de una fila que tenga un ID archivado (iD) y dos campos de datos (val1, val2)
2. Opcionalmente haz tus cálculos con datos.
3. ACTUALIZAR los datos de esa fila

La forma de NO BLOQUEO es:

NOTA: todo el código {entre curl brakets} está destinado a estar en el código de la aplicación y no (necesariamente) en el lado de SQL

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId;
 - {go on with your other code}

La forma de BLOQUEO OPTIMISTA es:

- SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

Tenga en cuenta que el punto clave está en la estructura de la instrucción ACTUALIZAR y en el número subsiguiente de verificación de filas afectadas. Son estas dos cosas juntas las que le permiten a su código darse cuenta de que alguien ya modificó los datos intermedios cuando ejecutó SELECT y UPDATE. Tenga en cuenta que todo se ha hecho sin transacciones! Esto ha sido posible (ausencia de transacciones) solo porque es un ejemplo muy simple, pero también indica que el punto clave para el bloqueo optimista no está en las transacciones en sí mismas.

¿Qué pasa con las transacciones entonces?

 - SELECT iD, val1, val2
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - BEGIN TRANSACTION;
 - UPDATE anotherTable
       SET col1 = @newCol1,
           col2 = @newCol2
       WHERE iD = @theId;
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2
       WHERE iD = @theId
           AND val1 = @oldVal1
           AND val2 = @oldVal2;
 - {if AffectedRows == 1 }
 -     COMMIT TRANSACTION;
 -     {go on with your other code}
 - {else}
 -     ROLLBACK TRANSACTION;
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

Este último ejemplo muestra que si comprueba si hay colisiones en algún momento y descubre que se ha producido una colisión cuando ya ha modificado otras tablas / filas ... ... luego, con las transacciones, puede revertir TODOS los cambios que ha hecho desde entonces. el principio. Obviamente, depende de usted (que sepa lo que está haciendo su aplicación) decidir qué tan grande es la cantidad de operaciones a revertir para cada posible colisión y en función de esto, decidir dónde colocar los límites de las transacciones y dónde verificar las colisiones con el especial ACTUALIZACIÓN + Verificación de filas afectadas.

En este caso con transacciones, hemos separado el momento en que realizamos la ACTUALIZACIÓN desde el momento en que se confirma. Entonces, ¿qué sucede cuando un "otro proceso" realiza una actualización en este período de tiempo? Para saber qué sucede exactamente, es necesario profundizar en los detalles del nivel de aislamiento (y cómo se gestiona en cada motor). Como ejemplo, en el caso de Micosoft SQL Server con READ_COMMITTED, las filas actualizadas se bloquean hasta que COMMIT no puede hacer nada (se mantiene esperando) en esa fila, ni SELECT (de hecho, solo puede READ_COMMITTED) . Entonces, dado que la actividad de "otro proceso" se aplaza, UPDATE fallará.

La opción VERSIONING OPTIMISTIC LOCKING:

 - SELECT iD, val1, val2, version
       FROM theTable
       WHERE iD = @theId;
 - {code that calculates new values}
 - UPDATE theTable
       SET val1 = @newVal1,
           val2 = @newVal2,
           version = version + 1
       WHERE iD = @theId
           AND version = @oldversion;
 - {if AffectedRows == 1 }
 -     {go on with your other code}
 - {else}
 -     {decide what to do since it has gone bad... in your code}
 - {endif}

Aquí se muestra que, en lugar de verificar si el valor sigue siendo el mismo para todos los campos, podemos usar un campo dedicado (que se modifica cada vez que hacemos una ACTUALIZACIÓN) para ver si alguien fue más rápido que nosotros y cambió la fila entre nuestros SELECCIONAR Y ACTUALIZAR. Aquí, la ausencia de transacciones se debe a la simplicidad del primer ejemplo y no está relacionada con el uso de la columna de versión. Nuevamente, el uso de esta columna depende de la implementación en el código de la aplicación y no de una función del motor de base de datos.

Más que esto, hay otros puntos que creo que harían esta respuesta demasiado larga (ya es demasiado larga), por lo que solo los menciono ahora con algunas referencias:

• nivel de aislamiento de transacción ( aquí para MySQL ) sobre el efecto de transacción en SELECT.
• para el INSERT en tablas con claves primarias no generadas automáticamente (o restricciones únicas) fallará automáticamente sin necesidad de una comprobación particular si dos procesos intentan insertar los mismos valores donde debe ser único.
• Si no tiene una columna de identificación (clave principal o restricciones únicas), un solo SELECT + UPDATE requiere transacciones, ya que podría tener la sorpresa de que después de las modificaciones realizadas por otros, hay más filas de las esperadas que cumplen los criterios de la cláusula WHERE de UPDATE.

Cómo comprobar en la práctica y tener confianza.

Dado que el valor y la implementación del nivel de aislamiento pueden ser diferentes, el mejor consejo (como es habitual en este sitio) es realizar una prueba en la plataforma / entorno utilizado.

Puede parecer difícil, pero en realidad se puede hacer fácilmente desde cualquier entorno de desarrollo de base de datos usando dos ventanas separadas y comenzando en cada una una transacción y luego ejecutando los comandos uno por uno.

En algún momento verá que la ejecución del comando continúa indefinidamente. Luego, cuando en la otra ventana se llama COMMIT o ROLLBACK, completa la ejecución.

Aquí hay algunos comandos muy básicos listos para ser probados como se acaba de describir.

Utilice estos para crear la tabla y una fila útil:

CREATE TABLE theTable(
    iD int NOT NULL,
    val1 int NOT NULL,
    val2 int NOT NULL
)
INSERT INTO theTable (iD, val1, val2) VALUES (1, 2 ,3);

Luego lo siguiente en dos ventanas diferentes y paso a paso:

BEGIN TRAN

SELECT val1, val2 FROM theTable WHERE iD = 1;

UPDATE theTable
  SET val1=11
  WHERE iD = 1 AND val1 = 2 AND val2 = 3;

COMMIT TRAN

luego cambie el orden de los comandos y el orden de ejecución en cualquier orden que pueda pensar.