MySQL LOCK TABLES und PHP

Kukulkan · Beitrag von **Kukulkan** » 08.07.2009 15:39

Hallo,

Ich habe eine PHP-Routine, die ich immer zum Ausführen eines SQL verwende. Eine ähnliche Routine frägt auch Daten ab. Dabei wird die Verbindung immer mit mysql_connect() aufgebaut.

Jetzt möchte ich verhindern, dass während einer, mehrere Befehle andauernden, Aktion die Tabelle für andere User (und deren Aktionen) zugreifbar ist. Dazu kann ich ja eigentlich LOCK TABLES verwenden. Aber in der Doku steht, dass dann nur der selbe Thread zugreifen kann und andere Threads gesperrt sind. Durch meine Routine ist aber jeder Zugriff ein neuer Thread:

Code: Alles auswählen

// hier würde ich gerne Sperren

ExecuteSQL("UPDATE id FROM tbltest SET test=5 WHERE UserID=1");

echo "ThreadID: " . mysql_thread_id() . "<br>";

// Andere Funktionen in anderen Includes die zwischendurch auch
// mal zB ExecuteSQL aufrufen oder auch andere DB-Funktionen verwenden.
// Genaugenommen fünf mal was anderes machen was innerhalb
// des Locks funktionieren soll. Wenn aber mittendrin ein anderer
// User die selbe Funktionalität benötigt, sollte der bereits laufende
// Prozess erst Fertiggestellt werden bevor das beim anderen User
// anläuft.

ExecuteSQL("UPDATE id FROM tbltest SET test=1 WHERE UserID=1");

echo "ThreadID: " . mysql_thread_id() . "<br>";

// und hier die Sperre aufheben

Ergibt:
ThreadID: 304397
ThreadID: 304402

Wie kann ich MySQL Verbindungen Userbezogen verwalten? Oder ist das gar nicht nötig? Wie würdet Ihr das lösen?

Meine Routine:

Code: Alles auswählen

// Does a SQL-Execute to the database.
// Returns true in case of success, false in case of failure.
function ExecuteSQL($SQL) {
    $DBCONN = mysql_connect (DBHOST, DBUSER, DBPASSWORD);
    if ($DBCONN == FALSE) {
        // can not open database!
        error_log("DATABASE: Can not open database " . DBHOST . " for execute.");
        error_log("DATABASE: error-details: " . mysql_errno() . " / " . mysql_error());
        return FALSE;
    }
    mysql_select_db(DBDATABASE, $DBCONN);

    $DBERG = mysql_query($SQL, $DBCONN);
    if ($DBERG == FALSE) {
        error_log("DATABASE: Error while executing " . $SQL);
        error_log("DATABASE: error-details: " . mysql_errno() . " / " . mysql_error());
        $Ergebnis = FALSE;
    } else {
        $Ergebnis = TRUE;
        @mysql_free_result($DBERG);
    }
    return $Ergebnis;
}

Kann man mein Problem so verstehen?

Das Problem entsteht durch das Kapseln der Funktion in ExecuteSQL(). Aber ich habe enorm viele Vorteile durch diese Kapselung, kann Datenbanksachen besser warten und spare auch Mengen an Code. Aber kann ich so weder Transaktionen noch Locks verwenden?

Volker

DarkDragon · Beitrag von **DarkDragon** » 08.07.2009 16:07

Also jedes mal mysql_connect aufzurufen ist nicht so gut. Den Vorteil von dieser Kapselung kann ich nicht sehen. Es wäre doch genauso einfach immer die $connection mit zu übergeben. Vielleicht könntest du den Vorteil näher erklären.

Kukulkan · Beitrag von **Kukulkan** » 08.07.2009 16:23

Hallo DarkDragon,

Ich verwende alleine die ExecuteSQL() über 60x in meinem Source (grosses Projekt). Das kostet mich so jedesmal nur 1 Zeile und ich habe ein DB-bezogenes Error-Handling mit drinnen. Wenn ich die DB wechseln will (zB PostgreSQL oder Oracle), dann muss ich nur mein Include mit den paar DB-Funktionen anpassen. Der Rest des Source bleibt dann sauber. Wenn das keine Vorteile sind???

Es wäre doch genauso einfach immer die $connection mit zu übergeben.

Naja, wenn ich die $connection global halte, dann hab ich auch nichts davon. Dann hat alles die selbe ThreadID (auch die anderen User). Das bringt mir dann auch nix, weil dann alle User in dem Kontext meines LOCKs sind. Dann kann immer noch jeder lesen/schreiben weil er im selben Thread ist...
Und als Parameter zu übergeben würde bedeuten, den kompletten Source umzuschreiben und auch über Includes hinweg immer die Verbindung durchzureichen...

Ich denke gerade darüber nach, die $connection in der Session zu halten...

Code: Alles auswählen

if (isset($_SESSION['db_connection']) == FALSE) {
    // create new db-connection
    $DBCONN = mysql_connect (DBHOST, DBUSER, DBPASSWORD); 
    if ($DBCONN == FALSE) { 
        // can not open database! 
        error_log("DATABASE: Can not open database " . DBHOST . " for execute."); 
        error_log("DATABASE: error-details: " . mysql_errno() . " / " . mysql_error()); 
    } else {
        $_SESSION['db_connection'] = $DBCONN;
    }
}

So könnte ich später in ExecuteSQL so arbeiten:

Code: Alles auswählen

function ExecuteSQL($SQL) {
    if (isset($_SESSION['db_connection']) == TRUE) {
        $DBCONN = $_SESSION['db_connection']
    } else {
        $DBCONN = mysql_connect (DBHOST, DBUSER, DBPASSWORD);
    }
    // other work
}

Dann wäre das zumindest User-Bezogen, oder? Und ein LOCK darf nur ausgeführt werden, wenn die Verbindung aus einer Session kommt.

Gibt es dagegen Einwände? Sollte die ExecuteSQL ohne gesetzte Session aufgerufen werden, so wird eine neue (temporäre) Verbindung aufgebaut. Ansonsten wird die aus der Session verwendet. Ist noch ungetestet...

Volker

Kukulkan · Beitrag von **Kukulkan** » 08.07.2009 17:15

Hallo,

Die Session-Idee geht nicht, weil eine Session keine Ressourcen halten kann. Und eine Verbindung ist eine Ressource...

Ich könnte noch Zeilenweise sperren. Eine Spalte "Locked_Until" einfügen (Typ DATETIME) und dann eine Sperre vermerken.

(1) Zu Beginn prüfen, ob da ein Datum drinnen steht das noch nicht überschritten ist.
"SELECT IF(Locked_Until IS NULL, 0, Locked_Until - unix_timestamp()) AS LockedSince FROM tbltext WHERE UserID = 1"

(2) Wenn ja, warten und später versuchen. Bei mehr als 5 Sekunden aufgeben (-> Fehler).

(3) Wenn nein, Datum einsetzen
"UPDATE tbltest SET Locked_Until=unix_timestamp() + 5 WHERE UserID = 1"

(4) Meine Operationen durchführen

(5) Lock wegnehmen
"UPDATE tbltest SET Locked_Until=NULL WHERE UserID = 1"

Wie seht Ihr das?

Volker

DarkDragon · Beitrag von **DarkDragon** » 08.07.2009 17:23

Ich verwende alleine die ExecuteSQL() über 60x in meinem Source (grosses Projekt). Das kostet mich so jedesmal nur 1 Zeile und ich habe ein DB-bezogenes Error-Handling mit drinnen. Wenn ich die DB wechseln will (zB PostgreSQL oder Oracle), dann muss ich nur mein Include mit den paar DB-Funktionen anpassen. Der Rest des Source bleibt dann sauber. Wenn das keine Vorteile sind???

Wäre es da nicht besser sowas in deiner Include zu machen:

Code: Alles auswählen

function ConnectSQL() {
    $DBCONN = mysql_connect (DBHOST, DBUSER, DBPASSWORD);
    if ($DBCONN == FALSE) {
        // can not open database!
        error_log("DATABASE: Can not open database " . DBHOST . " for execute.");
        error_log("DATABASE: error-details: " . mysql_errno() . " / " . mysql_error());
        return FALSE;
    }
    mysql_select_db(DBDATABASE, $DBCONN);
    return $DBCONN;
}

// Does a SQL-Execute to the database.
// Returns true in case of success, false in case of failure.
function ExecuteSQL($Connection, $SQL) {
    $DBERG = mysql_query($SQL, $Connection);
    if ($DBERG == FALSE) {
        error_log("DATABASE: Error while executing " . $SQL);
        error_log("DATABASE: error-details: " . mysql_errno() . " / " . mysql_error());
        $Ergebnis = FALSE;
    } else {
        $Ergebnis = TRUE;
        @mysql_free_result($DBERG);
    }
    return $Ergebnis;
}

function CloseSQL($Connection, $SQL) {
    mysql_close($Connection);
}

Desweiteren schließt du ja nirgendwo die Verbindung. D.h. zum Schluss hast du 70 Verbindungen (Natürlich dann pro Verbindung einen Thread) und keine oder nur die letzte (Ich weiß ja nicht was du sonst noch für Methoden hast) wird geschlossen.

Bei psql sieht das fast gleich aus. Oracle kenn ich bisher noch nicht. Man erstellt einfach nicht für jede Query eine neue Connection, das ist unsauber und ressourcenfressend.

Ich bin desweiteren etwas verwirrt über deine "User": Sinds denn nun Nutzer deiner Webanwendung oder Nutzer deiner Datenbank?

Und deine letzte Lockidee mit 5 Sekunden warten ist nicht so sicher.

Kukulkan · Beitrag von **Kukulkan** » 09.07.2009 11:18

Hallo,

Desweiteren schließt du ja nirgendwo die Verbindung. D.h. zum Schluss hast du 70 Verbindungen (Natürlich dann pro Verbindung einen Thread) und keine oder nur die letzte (Ich weiß ja nicht was du sonst noch für Methoden hast) wird geschlossen.

Das ist Absicht! Wenn die Verbindung nicht geschlossen wird, wird diese von PHP wiederverwendet wenn die Connection-Parameter identisch sind (was sie bei mir ja immer sind). So spare ich mir die reconnects und PHP nimmt immer die selbe Verbindung. Das ist das MySQL Verbindungs-Caching von PHP. Sogar mit Close würde PHP das für eine Weile offen halten und dann wiederverwenden. Wird so gelegentlich sogar explizit empfohlen.

Sinds denn nun Nutzer deiner Webanwendung oder Nutzer deiner Datenbank?

Weder noch. Das ist nur ein Beispiel. Genaugenommen sind es Auktionen und desshalb muss es beim bieten durch viele User sauber gelockt sein. Ich sehe irgendwie nicht ein, warum ich mit einem LOCK TABLE die ganze Tabelle sperren soll, obwohl ich nur einen Datensatz bearbeite... Ich denke schon, dass ich am besten einen Lock über eine Spalte mache. Die Kosten sind ein SELECT und zwei UPDATE je Zugriff. Dafür bleibt die Tabelle immer offen für andere Anfragen und Inserts/Updates.

Volker