SQL-Problem: SELECT id; if ($id) { UPDATE } else { INSERT }

Dieser Post wurde aus meiner alten WordPress-Installation importiert. Sollte es Darstellungsprobleme, falsche Links oder fehlende Bilder geben, bitte einfach hier einen Kommentar hinterlassen. Danke.

SQL hat viele Nachteile, aber einer der größten ist die Unsinnige Aufteilung zwischen der Erstellung und Veränderung eines Datensatzes. mySQL macht es dem genervten Programmiere mit MyISAM auch nicht leichter, denn Row-Locks oder Transaktionen werden nicht unterstützt. Aber ich schweife ab...

Beim Speichern eines Datensatzes gibt es häufig ein Konstrukt wie dieses hier:

$id = $dbh->selectrow_array('SELECT id FROM tabelle WHERE...');if ($id) {   $dbh->do('UPDATE tabelle SET ... WHERE id=?');} else {   $dbh->do('INSERT INTO tabelle ...');}

Theoretisch perfekt: Wenn der gewünschte Datensatz bereits vorhanden ist, wird er aktualisiert andernfalls ein neuer erstellt.

In der Praxis ergibt sich allerdings ein Problem: Der ganze Ablauf ist Non-Atomic, er wird also nicht als ein Block ausgeführt sondern als einzelne Befehle. Zwischen den einzelnen Queries werden vom Server allerdings auch andere abgearbeitet - und das Chaos ist perfekt.

SELECT wird in einem mySQL-Cluster üblicherweise auf einem Slave ausgeführt, denn für etwas anderes sind diese ohnehin nicht zu gebrauchen. Mittels SHOW SLAVE STATUS, SHOW MASTER STATUS  und MASTER_POS_WAIT() lässt sich der Slave zwar auf ein Alter von "0 Sekunden" oder auf eine bestimmte Master-LogPos bringen, wirklich aktuell ist er allerdings nie. Das folgende UPDATE oder INSERT Query geht zwangsweise auf den Master, denn auf Slaves kann nicht geschrieben werden.

Jetzt entsteht folgende Situation:Der Slave ist (und sei es nur geringfügig) veraltet, liefert allerdings trotzdem die Information zurück "0 rows" - der Datensatz existiert nicht in der Datenbank und es muss ein neuer erstellt werden.Wird auf Grund des SELECT-Ergebnisses (kein id gefunden) jetzt ein INSERT ausgeführt, entstehen doppelte Zeilen für den gleichen Inhalt oder es tritt ein DUPLICATE KEY - Fehler auf (je nachdem wie sauber die Tabelle konfiguriert wurde und ob ein Unique-Index durch die doppelten Werte verletzt würde).

Wird dieser Ablauf jetzt in einer Schleife ausgeführt, muss jeder Schleifendurchlauf so lange warten bis der Slave zumindest anscheinend wieder aktuell ist, bei 3 Sekunden pro Schleifendurchlauf und nur 1.000 Rows in einer Tabelle... viel Spaß.

Es gibt zwei Alternativen, die effektiv das Gleiche Ergebnis erreichen:

1. INSERT INTO ... ON DUPLICATE KEY UPDATE ...Kombiniert alle drei Queries in einem, funktioniert aber nur wenn der INSERT einen duplicate-key-Fehler produzieren würde, die Tabelle also einen passenden unique key hat.

2. UPDATE ... LIMIT 1

$sth = $dbh->prepare_cached('UPDATE ... WHERE ... LIMIT 1');$sth->execute;if (!$sth->rows) {   INSERT ...}

In diesem Fall wird zunächst ein UPDATE versucht, der LIMIT 1 beschränkt diesen auf nur eine Row (auch wenn theoretisch bereits die WHERE-Bedingungen dafür sorgen sollten).Nach dem Update kann mit $sth->rows abgefragt werden, wie viele Rows dem WHERE entsprochen haben. Wird hier eine 0 zurückgeliefert, hat der UPDATE nichts updaten können und der Datensatz muss per INSERT eingefügt werden.UPDATE kennt kein JOIN und auf aufwendige WHERE-Bedingungen sollte verzichtet werden, da das UPDATE-Query primär auf dem Master ausgeführt wird, aber so können die o.g. Probleme behoben werden. Im Gegensatz zum ON DUPLICATE KEY UPDATE handelt es sich immer noch nicht um eine Atomic-Operation, allerdings werden beide Queries auf dem Master ausgeführt, damit entfallen alle Wartezeiten auf aktuelle Daten. Allerdings kann eine komplizierte Bedingung zu einem langen Lauf des UPDATE-Query führen und so den Master lahmlegen.