mysqli query Fehler: update AND select

[Rolf B]

Hallo Max,

Was mache ich falsch?

(1) Du erzählst zu wenig, z.B. hast Du nicht gesagt, dass Du PHP mit MYSQLI nutzt. Das hat sich erst aus den Fehlermeldungen ergeben, die später gepostet wurden.

(2) Du verwendest Phantasiesyntax und nicht die, die dein MYSQL Server kennt.

• Ein SELECT Statement kann nur lesen, nichts ändern, und dort, wo Spaltenangaben oder literale Werte zulässig sind, kann auch ein SubSELECT stehen. Kein Substatement. Dieser Subselect muss genau eine Spalte zurückliefern (wenn man von Tupel-Syntax absieht, mit der ich nicht recht vertraut bin).

• Das Schlüsselwort AND dient zum Verknüpfen zweier logischer Bedingungen und sonst nichts. Es dient nicht zum Verketten von zwei Statements.

Zum Verketten mehrerer Statements verwendet man das Semikolon. Das nennt sich "multiple statement query".

UPDATE table SET score = score + 1 WHERE user = ? AND id = ?;
SELECT score FROM table WHERE user = ? AND id = ?

Ich habe das in PHP noch nicht gemacht und kann nur das Handbuch zitieren.

• Im MYSQL Handbuch über das C API steht, man müsse multiple statements explizit aktivieren. Ich finde aber nichts, wie man das in PHP macht.
• Es gibt aber in PHP mysqli_multi_query, dass dieses Feature implizit zu aktivieren scheint
• Jedoch: Prepare kann das nicht. Das erlaubt immer nur genau ein Statement.

Ein Ausweg wäre eine Transaktion, mit der Du sicherstellen kannst, dass zwischen UPDATE und SELECT kein Update durch einen anderen User erfolgen kann. Ich nehme an, dass das der Grund für dein Ansinnen ist. Das setzt aber voraus, dass Du die InnoDB Engine verwendest. Bei MyISAM gibt's keine Transaktionen, da hilft nur LOCK TABLE - was bei hoher Zugriffsfrequenz natürlich schnell zum Flaschenhals werden kann.

Ohne Transaktion ist - meine ich - auch eine Stored Procedure keine Lösung. Da kannst Du die Zeit zwischen UPDATE und SELECT nur verringern, aber nicht verhindern, dass keiner dazwischengrätscht.

Eine etwas aufwändigere Lösung, die ohne Transaktion und ohne Locks auskommt, besteht darin, SELECT und UPDATE zu tauschen. Hä? Ja, kein Scheiß. Ein Prepare lohnt da übrigens nicht wirklich, es sei denn, du machst diese Operation öfter in einem PHP Request. Ich zeige mal exemplarisch, wie man das ohne prepare machen könnte.

do 
{
  $escUser = mysqli_real_escape_string($user);
  $numId = intval($id);
  $result = $db->query("SELECT score FROM table WHERE user='$escUser' AND id=$numId");

  if ($result === FALSE)
  {
    /* Errorhandling */
  }

  $row = $result->fetch_assoc();
  if (!$row)
  {
    /* Row ist noch nicht da, INSERT statt UPDATE oder Fehler */
  }

  $score = intval($row['score']);
  $newScore = $score+1;
  $db->query("UPDATE table SET score=$newScore WHERE $user='$escUser' AND id=$numId AND score=$score");
}
while ($db->affectedRows == 0)

Der Trick ist hier, den UPDATE nur durchzuführen, wenn der zuvor gelesene Score unverändert ist. Das nennt man optimistisches Sperren - d.h. man sperrt eigentlich gar nicht, sondern liest die Daten, bestimmt die neuen Werte und schreibt sie raus - fragt dabei aber ab, ob der Satz unverändert geblieben ist.

Wenn keiner dazwischengegrätscht hat, braucht es nur diese beiden Queries und Du bist fertig.

Wenn aber doch, sorgt die Schleife dafür, dass der Versuch wiederholt wird. Lesen, Rechnen, Schreiben. Solange, bis keine Störung dazwischen ist.

Das ist effizient, wenn die Kollisionswahrscheinlichkeit auf einem User gering ist, aber im Allgemeinen viele Updates auf der Tabelle stattfinden, so dass Transaktionen oder gar ein LOCK TABLE zu einem Engpass führen.

Rolf