Ich komponiere gerade eine komplexe Update-Abfrage, die mehr oder weniger so aussieht:
%Vor% Dies bedeutet, dass die Variable x basierend auf der Unterabfrage aktualisiert wird, die auch die Variable x verarbeitet - aber konnte diese x nicht bereits durch den laufenden Aktualisierungsbefehl geändert werden? Ist das nicht ein Problem? Ich meine, in der normalen Programmierung müssen Sie normalerweise explizit damit umgehen, dh neuen Wert an einem anderen Ort als den alten Wert speichern und nach dem Job den alten Wert durch new ersetzen ... aber Wie wird die SQL-Datenbank tu das?
Ich bin nicht an einer einzigen Beobachtung oder einem Experiment interessiert. Ich hätte gerne einen Ausschnitt aus der Docs oder Sql-Standard, der sagen wird, was ist das definierte Verhalten in diesem Fall. Ich benutze MySQL, aber die Antworten gelten auch für andere PostgresQL, Oracle, etc. und besonders für SQL-Standard im Allgemeinen werden geschätzt. Danke!
** Bearbeitet **
Auswahl aus der Zieltabelle
Von 13.2.9.8. Unterabfragen in der FROM-Klausel :
Unterabfragen in der FROM-Klausel können einen Skalar, eine Spalte, eine Zeile oder eine Tabelle zurückgeben. Unterabfragen in der FROM-Klausel können keine korrelierten Unterabfragen sein, es sei denn, sie werden innerhalb der ON-Klausel einer JOIN-Operation verwendet.
Also, ja, Sie können die obige Abfrage durchführen.
Das Problem
Hier gibt es wirklich zwei Probleme. Es gibt Nebenläufigkeit, oder sicherzustellen, dass niemand die Daten unter unseren Füßen verändert. Dies wird mit Sperren gehandhabt. Der Umgang mit der tatsächlichen Änderung von neuen gegenüber alten Werten wird mit abgeleiteten Tabellen gehandhabt.
Sperren
Im Falle Ihrer obigen Abfrage führt MySQL mit InnoDB zuerst das SELECT aus und erwirbt einzeln für jede Zeile in der Tabelle eine Lese- (gemeinsam genutzte) Sperre. Wenn Sie in der SELECT-Anweisung eine WHERE-Klausel hätten, wären nur die von Ihnen ausgewählten Datensätze gesperrt, wobei Bereiche auch dazu führen würden, dass Lücken geschlossen werden.
Eine Lesesperre verhindert, dass eine andere Abfrage Schreibsperren erwirbt Datensätze können nicht von woanders aktualisiert werden, während sie schreibgeschützt sind.
Dann erwirbt MySQL eine Schreibsperre (exklusiv) für jeden der Datensätze in der Tabelle einzeln. Wenn Sie in Ihrer UPDATE-Anweisung eine WHERE-Klausel hätten, wären nur die spezifischen Datensätze schreibgeschützt. Wenn die WHERE-Klausel wiederum einen Bereich ausgewählt hätte, wäre der Bereich gesperrt.
Jeder Datensatz, der eine Lesesperre von der vorherigen SELECT hatte, wurde automatisch zu einer Schreibsperre eskaliert.
Eine Schreibsperre verhindert, dass andere Abfragen Lese- oder Schreibzugriff erhalten sperren.
Sie können Innotop um dies zu sehen, indem man es einleitet Sperren Sie den Modus, starten Sie eine Transaktion, führen Sie die Abfrage aus (aber setzen Sie sie nicht fest), und Sie sehen die Sperren in Innotop. Außerdem können Sie die Details ohne Innotop mit SHOW ENGINE INNODB STATUS anzeigen.
Deadlocks
Ihre Abfrage ist anfällig für einen Deadlock, wenn zwei Instanzen gleichzeitig ausgeführt wurden. Wenn Abfrage A Lesesperren erhalten hat, dann hat Abfrage B Lesesperren erhalten, Abfrage A muss auf die Lesesperren von Abfrage B warten, bevor sie die Schreibsperren abrufen kann. Abfrage B wird die Lesesperren jedoch erst nach dem Ende freigeben, und sie wird erst beendet, wenn sie Schreibsperren erhalten kann. Abfrage A und Abfrage B befinden sich in einer Pattsituation und daher ein Deadlock.
Daher möchten Sie möglicherweise eine explizite Tabellensperre ausführen, um die massive Menge an Datensatzsperren zu vermeiden (die Speicher verwendet und die Leistung beeinflusst) und einen Deadlock zu vermeiden.
Ein alternativer Ansatz besteht darin, SELECT ... FOR UPDATE für Ihre innere SELECT-Anweisung zu verwenden. Dies beginnt mit Schreibsperren für alle Zeilen, anstatt mit dem Lesen und der Eskalation zu beginnen.
Abgeleitete Tabellen
Für das innere SELECT erstellt MySQL eine abgeleitete temporäre Tabelle . Eine abgeleitete Tabelle ist eine tatsächliche nicht indizierte Kopie der Daten, die in der temporären Tabelle enthalten sind, die automatisch von MySQL erstellt wird (im Gegensatz zu einer temporären Tabelle, die Sie explizit erstellen und der Sie Indizes hinzufügen können).
Da MySQL eine abgeleitete Tabelle verwendet, ist dies der temporäre alte Wert, auf den Sie in Ihrer Frage verweisen. Mit anderen Worten, hier gibt es keine Magie. MySQL macht es genau so, wie Sie es irgendwo anders machen würden, mit einem temporären Wert.
Sie können die abgeleitete Tabelle sehen, indem Sie eine EXPLAIN-Anweisung für Ihre UPDATE-Anweisung (unterstützt in MySQL 5.6 +) ausführen.
Ein richtiges RDBMS verwendet statement level read consistency , wodurch sichergestellt wird, dass die Anweisung die Daten so sieht (auswählt), wie sie zum Zeitpunkt des Beginns der Anweisung waren. Das Szenario, vor dem Sie Angst haben, wird nicht auftreten.
Grüße, Rob.
Oracle hat dies in der 11.2 Dokumentation
Ein konsistentes Ergebnismenge wird für jede Abfrage bereitgestellt, um die Datenkonsistenz zu gewährleisten, ohne Aktion durch den Benutzer. Eine implizite Abfrage, z. B. eine Abfrage impliziert Durch eine WHERE-Klausel in einer UPDATE-Anweisung wird eine Konsistenz garantiert Reihe von Ergebnissen. Jede Anweisung in einer impliziten Abfrage tut dies jedoch nicht sehen Sie die von der DML-Anweisung selbst vorgenommenen Änderungen, sieht aber die Daten als Es existierte, bevor Änderungen vorgenommen wurden.
Obwohl bemerkt wurde, dass Sie auf Grund ihrer eigenen Daten NICHT in der Lage sein sollten, eine Tabelle zu aktualisieren, sollten Sie in der Lage sein, die MySQL-Syntax über
anzupassen %Vor%Ich weiß nicht, ob die Syntax eine andere Route mit JOIN gegenüber der Kommaliste Version geht, sondern durch die komplette Vorabfrage, die Sie zuerst für sein Ergebnis durchgeführt werden müssen, das EINMAL abgeschlossen wird, und (über das WHERE) verbunden wird Führen Sie das Update tatsächlich durch.
Tags und Links sql mysql database oracle postgresql