usuarios - tipos de bloqueos sql

Estrategias de bloqueo y incremento atómico de SQL: ¿es seguro?

Question

Tengo una pregunta sobre SQL y estrategias de bloqueo. Como ejemplo, supongamos que tengo un contador de vistas para las imágenes en mi sitio web. Si tengo un sproc o similar para realizar las siguientes afirmaciones:

START TRANSACTION;
UPDATE images SET counter=counter+1 WHERE image_id=some_parameter;
COMMIT;

Supongamos que el contador para un image_id específico tiene un valor ''0'' en el tiempo t0. Si dos sesiones que actualizan el mismo contador de imágenes, s1 y s2, comienzan simultáneamente en t0, hay alguna posibilidad de que estas dos sesiones lean el valor ''0'', lo aumenten a ''1'' y ambas intenten actualizar el contador a ''1 '', entonces el contador obtendrá el valor'' 1 ''en lugar de'' 2 ''?

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Resultado final: el valor incorrecto ''1'' para image_id = 15, debería haber sido 2.

Mis preguntas son:

1. ¿Es posible este escenario?
2. Si es así, ¿el nivel de aislamiento de la transacción es importante?
3. ¿Hay un solucionador de conflictos que detectaría tal conflicto como un error?
4. ¿Se puede usar una sintaxis especial para evitar un problema (algo como la comparación e intercambio (CAS) o las técnicas de bloqueo explícito)?

Estoy interesado en una respuesta general, pero si no hay ninguna, estoy interesado en las respuestas específicas de MySql e InnoDB, ya que estoy tratando de usar esta técnica para implementar secuencias en InnoDB.

EDITAR: El siguiente escenario también podría ser posible, dando como resultado el mismo comportamiento. Supongo que estamos en el nivel de aislamiento READ_COMMITED o superior, de modo que s2 obtiene el valor desde el inicio de la transacción, aunque s1 ya escribió ''1'' en el contador.

s1: begin
s1: begin
s1: read counter for image_id=15, get 0, store in temp1
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: read counter for image_id=15, get 0 (since another tx), store in temp2
s2: write counter for image_id=15 to (temp2+1), which is also 1
s1: commit, ok
s2: commit, ok

Answer 1

Si el bloqueo no se realiza correctamente, ciertamente es posible obtener este tipo de condición de carrera, y el modo de bloqueo predeterminado (lectura confirmada) lo permite. En este modo, las lecturas solo colocan un bloqueo compartido en el registro, para que ambos puedan ver 0, incrementarlo y escribir 1 en la base de datos.

Para evitar esta condición de carrera, debe establecer un bloqueo exclusivo en la operación de lectura. Los modos de concurrencia "Serializable" y "Lectura repetible" harán esto, y para una operación en una sola fila son bastante equivalentes.

Para hacerlo completamente atómico tienes que:

• Establezca un nivel de aislamiento de transacción adecuado, como Serializable. Normalmente, puede hacerlo desde una biblioteca cliente o de manera explícita en SQL.
• Comience la transacción
• Lee los datos
• Actualizarlo
• Cometer la transacción.

También puede forzar un bloqueo exclusivo en la lectura con un HOLDLOCK (T-SQL) o una sugerencia equivalente, dependiendo de su dialecto de SQL.

Una sola consulta de actualización lo hará de forma atómica, pero no puede dividir la operación (quizás para leer el valor y devolverlo al cliente) sin asegurarse de que las lecturas eliminen un bloqueo exclusivo. Necesitará obtener el valor de forma atómica para implementar una secuencia , por lo que la actualización en sí misma probablemente no sea todo lo que necesita. Incluso con la actualización atómica, todavía tiene una condición de carrera para leer el valor después de la actualización. La lectura todavía tendrá que realizarse dentro de una transacción (almacenando lo que obtuvo en una variable) y emitirá un bloqueo exclusivo durante la lectura.

Tenga en cuenta que para hacer esto sin crear un punto de acceso, su base de datos debe tener el soporte adecuado para transacciones autónomas (anidadas) dentro de un procedimiento almacenado. Tenga en cuenta que a veces se usa ''anidado'' para referirse a encadenar transacciones o guardar puntos, por lo que el término puede ser un poco confuso. He editado esto para referirse a transacciones autónomas.

Sin transacciones autónomas, sus bloqueos son heredados por la transacción principal, que puede deshacer todo el lote. Esto significa que se mantendrán hasta que se confirme la transacción principal, lo que puede convertir su secuencia en un punto caliente que serializa todas las transacciones que usan esa secuencia. Cualquier otra cosa que intente utilizar la secuencia se bloqueará hasta que se confirme la transacción principal completa.

IIRC Oracle admite transacciones autónomas, pero DB / 2 no lo hizo hasta hace poco y SQL Server no. Desde lo alto de mi cabeza, no sé si InnoDB los respalda, pero Gray y Reuter continúan explicando cuán difíciles son de implementar. En la práctica, creo que es bastante probable que no lo sea. YMMV.

Answer 2

UPDATE consulta UPDATE coloca un bloqueo de actualización en las páginas o registros que lee.

Cuando se toma la decisión de actualizar el registro, el bloqueo se levanta o se pasa al bloqueo exclusivo.

Esto significa que en este escenario:

s1: read counter for image_id=15, get 0, store in temp1
s2: read counter for image_id=15, get 0, store in temp2
s1: write counter for image_id=15 to (temp1+1), which is 1 
s2: write counter for image_id=15 to (temp2+1), which is also 1

s2 esperará hasta que s1 decida si escribir el contador o no, y este escenario es de hecho imposible.

Será esto:

s1: place an update lock on image_id = 15
s2: try to place an update lock on image_id = 15: QUEUED
s1: read counter for image_id=15, get 0, store in temp1
s1: promote the update lock to the exclusive lock
s1: write counter for image_id=15 to (temp1+1), which is 1 
s1: commit: LOCK RELEASED
s2: place an update lock on image_id = 15
s2: read counter for image_id=15, get 1, store in temp2
s2: write counter for image_id=15 to (temp2+1), which is 2

Tenga en cuenta que en InnoDB , las consultas de DML no eliminan los bloqueos de actualización de los registros que leen.

Esto significa que en el caso de una exploración de la tabla completa, los registros que se leyeron pero decidieron no actualizar, permanecerán bloqueados hasta el final de la transacción y no podrán actualizarse desde otra transacción.