Frage an django, sqlite, mysql, sql – Das MySQL-Update, das mehrere Spalten ändert, ist nicht atomar.

8

Ich habe das folgende Problem mit Django mit MySQL 5.5.22.

Ausgehend von einer Tabelle mit den Spalten id, level und einer 2x2-Matrix, die als a11, a12, a21, a22 gespeichert ist, habe ich diese Zeile:

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

Bei einem gegebenen Abfragesatz führe ich das folgende Update durch:

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)
    )

Für welche django die folgende Abfrage generiert (habe sie von db.connection.queries erhalten, entferne die where-Klausel aus Gründen der Kürze):

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`) 

Und meine Zeile sieht danach so aus:

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

Für jede Reihea12*a21 - a11*a22 = 1 soll wahr sein, und demnach sollte die Zeile lauten:

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

Dies ist, was ich auf SQLite bekomme, wenn Django die gleiche Abfrage generiert, und ich habe viel Zeit gebraucht, um herauszufinden, dass MySQL etwas anderes macht. Aus der Abfrage geht hervor, dass MySQL beim Aktualisieren mehrerer Zeilen, die in Abhängigkeit voneinander stehen, nicht als einzelne atomare Operation behandelt wird. Wenn Spalten aktualisiert werden, wirken sich diese auf die davon abhängigen Werte aus. Ich habe bestätigt, dass dies anscheinend mit dem folgenden Code in der Python-Eingabeaufforderung geschieht:

>>> 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)

Wenn die Spalten einzeln aktualisiert werden, geschieht dies in der von der Abfrage angegebenen Reihenfolge:

>>> 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)

Dies ist wirklich beängstigend, da es sich um eine Bibliothek handelt, die plattformübergreifend verwendet werden soll. Meine Fragen sind:

Welches tut es falsch, MySQL oder SQLite? Kann dies als Fehler angesehen werden?Was kann ich von anderen wichtigen Datenbanken (Oracle, PostgreSQL und SQLServer) erwarten?Was kann ich mit dem Django ORM tun (keine rohen Abfragen), um dieses Verhalten zu normalisieren?

bearbeiten

Das Problem ist klar, aber ich suche immer noch nach einer Lösung. Das Ziehen und Zurückschieben aller Werte ist für diese spezielle Anwendung keine akzeptable Lösung.

Siehe meine aktualisierte Antwort unten. eggyal
Verwandt / Duplikat:stackoverflow.com/questions/2203202/… pilcrow
Das ist eine interessante Frage. Ich spielte damit weitersqlfiddle und es scheint, dass MySQL das einzige ist, das sich so verhält. Chad

Deine Antwort

2   die antwort
12

Oracle und SQL Server behandeln dies alle als eine atomare Operation.Sehen Sie sich die folgende SQL-Geige an und wechseln Sie den Server, um das Verhalten der folgenden SQL zu sehen:

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 war das einzige RBDMS, das dies mit beiden Spalten implementiert, die nach dem Update denselben Wert enthalten.

Was das Problem angeht, würde ich stattdessen die Werte aus der Datenbank abrufen, die Berechnungen in Ihrer Anwendung ausführen (anstelle Ihrer Aktualisierungsanweisung) und dann die Datenbank mit den berechneten Werten aktualisieren. Auf diese Weise können Sie sicherstellen, dass die Berechnung konsistent durchgeführt wird.

Vielen Dank. SQL Fiddle ist neu für mich und sehr nützlich. Leider hat das Abrufen aller Daten aus der Datenbank und das Zurückschieben der Daten den gesamten Zweck dieser Bibliothek verloren. Es gibt bessere Möglichkeiten, das zu tun, was es tut, wenn ich dazu bereit bin. Wenn es keinen anderen Weg gibt, okay, aber ich hoffe, es gibt eine bessere Lösung, auch wenn sie etwas hackisch ist. Pedro Werneck
10

MySQL-Handbuch:

Die zweite Zuordnung in den folgenden Anweisungen wird festgelegtcol2 zum aktuellen (aktualisiert)col1 Wert, nicht das Originalcol1 Wert. Das Ergebnis ist dascol1 undcol2 haben den gleichen Wert. Dieses Verhalten unterscheidet sich von Standard-SQL.

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

Daher wird in Ihrem Fall der Wert für verwendeta21 bei der Auswertung des Ausdrucks`a11` = (2 * `storage`.`a11`) + (-1 * `storage`.`a21`) ist der neue aktualisierte Wert 4 und nicht der ursprüngliche Wert 5. Wie im Handbuch angegeben,Dieses Verhalten unterscheidet sich von Standard-SQL.

Sie können stattdessen eine Selbstverknüpfung mit der Mehrfachtabelle verwendenUPDATE Syntax, aber ich weiß nicht, ob so etwas mit dem Django ORM implementiert werden kann:

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);

Schau es dir ansqlfiddle.

Mein einziger anderer Gedanke (der definitiv in Django implementiert werden sollte) ist, das Update in separate Teile aufzuteilen und die in späteren Teilen aktualisierten Felder im Verhältnis zu den neuen (und nicht den alten) Werten der zuvor aktualisierten Felder zu definieren Teile:

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));

Um Parallelitätsprobleme zu vermeiden, sollten Sie diese beiden Aktualisierungen innerhalb einer Transaktion ausführen (sofern dies vom RDBMS unterstützt wird).

Gut gesehene Lösung! SQL Server funktioniert zum Beispiel genauso wie derinserted unddeleted Pseudotabellen. Gerardo Lima
Danke für den Hinweis. Das macht es klar. Ich hatte gehofft, es gäbe eine Einstellung, um dieses Verhalten zu ändern. Pedro Werneck
@PedroWerneck: Hilft diese aktualisierte Antwort überhaupt nicht? eggyal

Verwandte Fragen