Pregunta sobre mysql, sqlite, sql, django – ¿La actualización de MySQL cambiando varias columnas no es atómica?

8

Tengo el siguiente problema al usar Django con MySQL 5.5.22.

Dada una tabla con columnas id, nivel y una matriz 2x2 almacenada como a11, a12, a21, a22, tengo esta fila:

id   a11   a12   a21   a22   level
324  3     2     5     3     2

Dado un queryset qs, hago la siguiente actualización:

qs.update(
    a11=(b12 * a21 - b11 * a22) * F('a11') + (b11 * a12 - b12 * a11) * F('a21'),
    a12=(b12 * a21 - b11 * a22) * F('a12') + (b11 * a12 - b12 * a11) * F('a22'),
    a21=(b22 * a21 - b21 * a22) * F('a11') + (b21 * a12 - b22 * a11) * F('a21'),
    a22=(b22 * a21 - b21 * a22) * F('a12') + (b21 * a12 - b22 * a11) * F('a22'),
    level=(F('level') - 1)
    )

Para lo que django genera la siguiente consulta (la obtuve de db.connection.queries, elimine la cláusula where por brevedad):

UPDATE `storage` 
SET 
`a21` = (3 * `storage`.`a11`) + (-1 * `storage`.`a21`), 
`a22` = (3 * `storage`.`a12`) + (-1 * `storage`.`a22`), 
`level` = `storage`.`level` - -1, 
`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`), 
`a12` = (2 * `storage`.`a12`) + (-1 * `storage`.`a22`) 

Y mi fila se ve así después de eso:

id   a11   a12   a21   a22   level
324  2     1     4     3     1

Para cualquier fila,a12*a21 - a11*a22 = 1 se supone que es verdadero, y de acuerdo con eso, se suponía que la fila era:

id   a11   a12   a21   a22   level
324  1     1     4     3     1

Esto es lo que obtengo en SQLite, con Django generando la misma consulta, y me tomó mucho tiempo darme cuenta de que MySQL estaba haciendo algo diferente. Desde la consulta, parece que cuando se actualizan varias filas interdependientes, MySQL no lo trata como una sola operación atómica y, al actualizar las columnas, afectan los valores que dependen de ellas. Confirmé que esto parece ser lo que sucede con el siguiente código en el indicador de Python:

>>> a11, a12, a21, a22 = (3, 2, 5, 3)
>>> (2 * a11) + (-1 * a21),\
... (2 * a12) + (-1 * a22),\
... (3 * a11) + (-1 * a21),\
... (3 * a12) + (-1 * a22)
(1, 1, 4, 3)

Si las columnas se actualizan de una en una, en el mismo orden dado por la consulta:

>>> a11, a12, a21, a22 = (3, 2, 5, 3)
>>> a21 = (3*a11) + (-1*a21)
>>> a22 = (3*a12) + (-1*a22)
>>> a11 = (2*a11) + (-1*a21)
>>> a12 = (2*a12) + (-1*a22)
>>> (a11, a12, a21, a22)
(2, 1, 4, 3)

Este es un comportamiento realmente aterrador, ya que se trata de una biblioteca destinada a ser utilizada en múltiples plataformas. Mis preguntas son:

¿Cuál está haciendo mal, MySQL o SQLite? ¿Se puede considerar esto un error?¿Qué puedo esperar de otras bases de datos importantes (Oracle, PostgreSQL y SQLServer)?¿Qué puedo hacer con el ORM de Django (sin consultas sin procesar) para normalizar este comportamiento?

editar

El problema está claro, pero todavía estoy buscando una solución. Extraer todos los valores y empujarlos hacia atrás no es una solución aceptable para esta aplicación en particular.

Ver mi respuesta actualizada a continuación. eggyal
Esta es una pregunta interesante. Jugué con eso ensqlfiddle y parece que MySQL es el único que se comporta de esta manera. Chad
Relacionado / duplicado:stackoverflow.com/questions/2203202/… pilcrow

Tu respuesta

2   la respuesta
10

Como se indica en elManual de MySQL:

La segunda asignación en los siguientes conjuntos de sentencias.col2 al actual (actualizado)col1 valor, no el originalcol1 valor. El resultado es quecol1 ycol2 tienen el mismo valor Este comportamiento difiere del SQL estándar.

UPDATE t1 SET col1 = col1 + 1, col2 = col1;

Por lo tanto, en su caso, el valor que se utiliza paraa21 al evaluar la expresión`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`) es el valor nuevo, actualizado, de 4 en lugar del valor original de 5. Como dice el manual,Este comportamiento difiere del SQL estándar..

En su lugar, podría utilizar una auto-unión con la tabla múltiple.UPDATE sintaxis, sin embargo, no sé si algo como esto se puede implementar utilizando el ORM de Django:

UPDATE storage AS old
  JOIN storage AS new USING (id)
SET
  new.a21   = (3 * old.a11) + (-1 * old.a21),
  new.a22   = (3 * old.a12) + (-1 * old.a22),
  new.level = old.level - -1,
  new.a11   = (2 * old.a11) + (-1 * old.a21),
  new.a12   = (2 * old.a12) + (-1 * old.a22);

Verlo ensqlfiddle.

Mi único otro pensamiento (que definitivamente debería ser implementado dentro de Django) es dividir la actualización en partes separadas, definiendo los campos actualizados en partes posteriores en relación con los valores nuevos (en lugar de los antiguos) de aquellos campos que se actualizaron anteriormente. partes:

UPDATE storage
SET    a21   = (3 * a11) + (-1 * a21),
       a22   = (3 * a12) + (-1 * a22),
       level = level - -1;

UPDATE storage
SET    a11   = (2 * a11) + (-1 * (3*a11 - a21)),
       a12   = (2 * a12) + (-1 * (3*a12 - a22));

Para evitar problemas de concurrencia, debe realizar estas dos actualizaciones dentro de una transacción (si es compatible con RDBMS).

@PedroWerneck: ¿esta respuesta actualizada no ayuda en absoluto? eggyal
¡Solución bien vista! SQL Server, por ejemplo, funciona igual que con elinserted ydeleted pseudo-tablas. Gerardo Lima
Gracias por la referencia. Eso lo deja claro. Esperaba que tal vez hubiera una configuración para cambiar este comportamiento. Pedro Werneck
12

Oracle y SQL Server tratan esto como una operación atómica.Vea el siguiente Fiddle de SQL y cambie el servidor para ver el comportamiento del siguiente SQL:

CREATE TABLE Swap (
  a CHAR(1),
  b CHAR(1)
);

INSERT INTO Swap (a, b) VALUES ('a', 'b');

UPDATE Swap SET a = b, b = a;

SELECT * FROM Swap;

MySQL fue el único RBDMS que implementa esto con ambas columnas que contienen el mismo valor después de la actualización.

En cuanto a cómo resolvería esto, en vez de eso, obtendría los valores de la base de datos, realizaría los cálculos dentro de su aplicación (en lugar de su estado de actualización) y luego actualizaría la base de datos con los valores calculados. De esta manera puede garantizar que el cálculo se realizará de manera consistente.

Gracias. SQL Fiddle es nuevo para mí y muy útil. Desafortunadamente, al extraer todos los datos de la base de datos y al rechazarlos, se pierde todo el propósito de esta biblioteca. Hay mejores maneras de hacer lo que hace si estoy dispuesto a hacerlo. Si no hay otra forma, está bien, pero espero que haya una mejor solución, aunque sea un poco pirata. Pedro Werneck

Preguntas relacionadas