MySQL 5.x : Update und Insert zusammenbinden?

[Tom]

Hello,

wie kann ich bei einem MySQL-Query ein Update und ein Insert atomar binden?
Gibt es dafür inzwischen eine Lösung, außer die Tabelle zu sperren während der beiden Statements?

Liebe Grüße aus dem Cyberspace

Tom vom Berg

[Christian Seiler]

Hallo Tom,

wie kann ich bei einem MySQL-Query ein Update und ein Insert atomar binden?
Gibt es dafür inzwischen eine Lösung, außer die Tabelle zu sperren während der beiden Statements?

Klar - nutze Transaktionen für's Schreiben (BEGIN, dann Deine INSERT/UPDATE/DELETE, dann COMMIT oder ROLLBACK bei Fehler), MySQL kann das (InnoDB vorausgestzt) schon eine ganze Weile. Wenn das Isolation Level auf REPEATABLE READ oder SERIALIZABLE steht (REPEATABLE READ ist der Defaultwert), dann sind alle COMMITs der Transaktion atomar. Das Isolation Level kannst Du problemlos für Deine Sitzung umstellen, d.h. wenn Du ganz sicher sein willst, nutze am Anfang jeder Sitzung [*] (auch und gerade bei lesenden (!) Sitzungen) folgendes (ganz am Anfang vor jeder gestarteten Transaktion):

TRANSACTION ISOLATION LEVEL REPEATABLE READ

[*] Sitzung im Sinne von MySQL-Verbindung.

Viele Grüße,
Christian

[Tom]

Hello,

wie kann ich bei einem MySQL-Query ein Update und ein Insert atomar binden?
Gibt es dafür inzwischen eine Lösung, außer die Tabelle zu sperren während der beiden Statements?

Klar - nutze Transaktionen für's Schreiben (BEGIN, dann Deine INSERT/UPDATE/DELETE, dann COMMIT oder ROLLBACK bei Fehler), MySQL kann das (InnoDB vorausgestzt) schon eine ganze Weile.

Das hatte ich gelesen. http://dev.mysql.com/doc/refman/5.1/en/commit.html
Meine Datenbanken sind leider vollständig mit MyISAM gebaut. Da muss ich die erstmal umbauen. Aber ich habe noch im Gedächtnis, dass Relations mit MyISAM auch nicht möglich sind, aber mit InnoDB. Dann würde es sich wenigstens lohnen.

Wenn das Isolation Level auf REPEATABLE READ oder SERIALIZABLE steht (REPEATABLE READ ist der Defaultwert), dann sind alle COMMITs der Transaktion atomar. Das Isolation Level kannst Du problemlos für Deine Sitzung umstellen, d.h. wenn Du ganz sicher sein willst, nutze am Anfang jeder Sitzung [*] (auch und gerade bei lesenden (!) Sitzungen) folgendes (ganz am Anfang vor jeder gestarteten Transaktion):

TRANSACTION ISOLATION LEVEL REPEATABLE READ

[*] Sitzung im Sinne von MySQL-Verbindung.

verstehe ich das richtig, dass ich innerhalb einer Transaktion mehrere Commits anwenden kann und das Rollback dann immer nur das letzte wiederherstellen müsste? Oder muss für jedes Commit eine eigene Transaktion aufgemacht werden?

Liebe Grüße aus dem Cyberspace

Tom vom Berg

[Christian Seiler]

Hallo Tom,

Klar - nutze Transaktionen für's Schreiben (BEGIN, dann Deine INSERT/UPDATE/DELETE, dann COMMIT oder ROLLBACK bei Fehler), MySQL kann das (InnoDB vorausgestzt) schon eine ganze Weile.

Das hatte ich gelesen. http://dev.mysql.com/doc/refman/5.1/en/commit.html
Meine Datenbanken sind leider vollständig mit MyISAM gebaut. Da muss ich die erstmal umbauen. Aber ich habe noch im Gedächtnis, dass Relations mit MyISAM auch nicht möglich sind, aber mit InnoDB. Dann würde es sich wenigstens lohnen.

Du kannst FOREIGN KEYs auf MyISAM anlegen, aber sie haben keinen Effekt. Daher würde ich immer auf InnoDB setzen wollen, MyISAM hat eigentlich nur Nachteile gegenüber InnoDB - mit der kleinen Ausnahme, dass FULLTEXT-Indizes nur mit MyISAM funktionieren. Allerdings gibt's für Volltextsuchen auch Alternativen außerhalb der Datenbank, die man sich anschauen kann (Lucene et al).

verstehe ich das richtig, dass ich innerhalb einer Transaktion mehrere Commits anwenden kann und das Rollback dann immer nur das letzte wiederherstellen müsste? Oder muss für jedes Commit eine eigene Transaktion aufgemacht werden?

Nein. Es gibt zwar das Konzept von "Nested Transactions", aber MySQL unterstützt das grundsätzlich nicht.

Im Prinzip läuft das bei normalen Transaktionen so ab: Du startest eine Transaktion mit BEGIN oder START TRANSACTION. Dann kannst Du DML-Befehle ausführen (SELECT, INSERT, UPDATE, DELETE) oder Stored Procedures aufrufen die nur DML-Befehle verwenden. Damit verändest Du den Datenbestand. Diese Veränderung ist aber beim richtigen Isolation Level nur für die aktuelle Sitzung (= Verbindung) sichtbar und nicht für andere. Das heißt: Innerhalb der Sitzung liefert ein SELECT auf eine Tabelle auch vor dem COMMIT schon die geänderten Datensätze. Danach hast Du zwei Möglichkeiten:

1. Du rufst COMMIT auf und machst die Änderungen endgültig. Dann ist Deine Transaktion beendet, d.h. Du musst erst wieder eine neue Transaktion mit BEGIN starten.

2. Du rufst ROLLBACK auf und alle Änderungen werden rückgängig gemacht bzw. sehen für die Außenwelt so aus, als ob nie etwas passiert ist. Auch hier ist die Transaktion beendet, auch hier musst du erst wieder eine neue Transaktion mit BEGIN starten.

Ein ROLLBACK direkt nach einem COMMIT hat keinerlei Effekt. Ein DML-Statement direkt nach einem COMMIT oder ROLLBACK wird ohne (!) Transaktion direkt auf der Datenbank ausgeführt, ist also nur bezogen auf sich selbst atomar, nicht auf umliegende Befehle.

Viele Grüße,
Christian

[Tom]

Hello,

ok, dank Dir für die ausführliche Antwort.
Ich kannte das bisher nur von Informix und das habe ich schon Jahre nicht mehr angerührt.

Bleibt jetzt noch eine Frage: Ist es auch erlaubt, _innerhalb_ einer stored routine eine Transaktion zu starten? Nachdem, was Du geschrieben hast, dürfte die dann nicht selber innerhalb einer Transaktion aufgerufen werden.

Ich habe dazu zwar einige Postings in verschiedensten Foren gefunden, die das Gleiche fragten, aber leider keine Antworten.

Oder wäre es von der Sache her sowieso unsinnig, ein Mehrfachstatement so herum aufzubauen?

Es handelt sich allerdings immer um dieselben Abläufe, die ich binden muss. Da habe ich bisher immer die Krücke über einen Table-Lock benutzt. Das funktioniert auch mit MyISAM, hält aber bei stärkerer Nebenläufigkeit enorm den Verkehr auf.

Wie wirkt sich denn ein "Start Transaction - Commit" überhaupt auf die Nebenläufigkeits-Fähigkeit aus? Kann es also sein, dass die Statement-Sequenz im eigenen virtuellen Scope noch sauber durchläuft und dann, aufgrund inzwischen geänderter Verhältnisse, erst beim Commit auf den Fehler (Kollision) läuft?

Liebe Grüße aus dem Cyberspace

Tom vom Berg

[Christian Seiler]

Hallo,

Bleibt jetzt noch eine Frage: Ist es auch erlaubt, _innerhalb_ einer stored routine eine Transaktion zu starten?

Prinzipiell schon.

Nachdem, was Du geschrieben hast, dürfte die dann nicht selber innerhalb einer Transaktion aufgerufen werden.

Du kannst das schon tun, allerdings führt das zweite BEGIN innerhalb der Procedure zu einem COMMIT der bisherigen Transaktion und startet dann eine neue Transaktion.

Ich würde das aber bleiben lassen - Transaktionshandling sollte immer innerhalb der Applikation und nie innerhalb von Stored Procedures erfolgen. Ausnahmen bestätigen die Regel.

Oder wäre es von der Sache her sowieso unsinnig, ein Mehrfachstatement so herum aufzubauen?

Multiple Statements zu einer Stored Procedure zusammenzufassen kann durchaus sinnvoll sein. Es hängt von der Aufgabenstellung ab.

Wie wirkt sich denn ein "Start Transaction - Commit" überhaupt auf die Nebenläufigkeits-Fähigkeit aus?

Laut MySQL-Doku werden nur die Zeilen gelockt, die bearbeitet werden - d.h. der Rest der Tabelle bleibt beschreibbar und lesbar ist sowieso immer alles. Ich hab's aber nicht ausprobiert.

Generell gesehen kann man aber sagen, dass eine Transaktion immer günstiger ist, als ein kompletter LOCK auf die Tabelle.

Kann es also sein, dass die Statement-Sequenz im eigenen virtuellen Scope noch sauber durchläuft und dann, aufgrund inzwischen geänderter Verhältnisse, erst beim Commit auf den Fehler (Kollision) läuft?

Prinzipiell gesehen: Ja. So wie ich die MySQL-Doku verstehe sollte das nicht geschehen (d.h. die einzelnen Statements sollten bereits fehlschlagen), aber ich kann mich zum einen auch täuschen und zum anderen ist es immer besser, man fängt so etwas von vorne herein ab.

Du solltest Dir übrigens auch SELECT ... FOR UPDATE ansehen, dann kannst Du, wenn Du bestimmte zusätzliche Daten erst auslesen musst bevor Du etwas modifizierst, der DB auch gleich sagen, dass Du das später noch modifizieren willst. Dann kann die DB nämlich sich bereits intern darauf einstellen, was dann dazu führt, dass Kollisionen vermindert werden (indem das andere SELECT ... FOR UPDATE das den gleichen Datensatz bearbeiten will z.B. auf die erste Transaktion warten muss bevor es ausgeführt wird, was dann zu keinem Fehler führt im Programm).

Viele Grüße,
Christian

[Tom]

Hello,

Du solltest Dir übrigens auch SELECT ... FOR UPDATE ansehen, dann kannst Du, wenn Du bestimmte zusätzliche Daten erst auslesen musst bevor Du etwas modifizierst, der DB auch gleich sagen, dass Du das später noch modifizieren willst. Dann kann die DB nämlich sich bereits intern darauf einstellen, was dann dazu führt, dass Kollisionen vermindert werden (indem das andere SELECT ... FOR UPDATE das den gleichen Datensatz bearbeiten will z.B. auf die erste Transaktion warten muss bevor es ausgeführt wird, was dann zu keinem Fehler führt im Programm).

DER Tipp bringt mich doch gleich nochmal ein Stück weiter!
http://dev.mysql.com/doc/refman/5.1/en/innodb-locking-reads.html

Das ist heute aber binäres Dimino. Eine geklärte Frage löst zwei neue ungeklärte aus.
Ist also eine Art implizites Locking. Ein Page-Locking wäre schon mal besser, als Table-Locking. Aber am besten wäre Row-Locking. Was unterstützt InnoDB? Wenn ich es richtig sehe, dann Zeilensperren.

Das wäre sehr gut!

http://dev.mysql.com/doc/refman/5.1/de/innodb-lock-modes.html

Pause! Die Osterpflichten rufen :-)

Liebe Grüße aus dem Cyberspace

Tom vom Berg

[Sympathisant]

Hai Christian,

kurze Verstaendnisfrage:

Ein ROLLBACK direkt nach einem COMMIT hat keinerlei Effekt.

Wie habe ich das zu verstehen? Nur bei erfolgreichem Commit?

Folgendes Beispiel anhand PDO:

<?  
public function Insert($bo)  
{  
	try {  
		$this->GetPDO()->beginTransaction();	  
		  
		// [....]  
		  
		if( !$this->GetPDO()->commit() )  
			throw new PDOException("Failed to insert Contact: ".$this->GetLastError());  
		  
		$bo->SetId( $this->GetPDO()->lastInsertId() );  
				  
		// [....]  
		return $bo;  
	}  
	catch(PDOException $e)  
	{  
		Logger::Error('ContactHandler->Insert(): '.$e->getMessage()).' Starting Rollback!';  
		if( !$this->GetPDO()->rollback() )  
			Logger::Error('Rollback Failed!' );  
		Logger::Info('Rollback Successfully!' );  
		return false;  
	}  
}  
?>

Wenn mit dem Statement (commit) etwas nicht stimmt, dann wird das Rollback erfolgreich aufgerufen.

MfG,
Sympatisant

[Christian Seiler]

Hallo,

kurze Verstaendnisfrage:

Ein ROLLBACK direkt nach einem COMMIT hat keinerlei Effekt.
Wie habe ich das zu verstehen? Nur bei erfolgreichem Commit?

Naja, wenn ich das richtig im Kopf habe, wird bei einem fehlgeschlagenen Commit automatisch ein Rollback gemacht (was anderes ist ja auch nicht sinnvoll, die Transaktion kann ja gar nicht mehr korrekt beendet werden). Bin mir aber nicht absolut sicher.

[PHP-Logik]
Wenn mit dem Statement (commit) etwas nicht stimmt, dann wird das Rollback erfolgreich aufgerufen.

Naja, schädlich ist Dein Rollback da absolut nicht und Du kannst es aus Sicherheitsgründen gerne drin lassen. Allerdings meine ich mich wie gesagt zu erinnern, dass Du den Rollback-Aufruf auch weglassen könntest, weil die aktuelle Transaktion bereits zurückgerollt wurde.

Viele Grüße,
Christian

[dedlfix]

echo $begrüßung;

Meine Datenbanken sind leider vollständig mit MyISAM gebaut. Da muss ich die erstmal umbauen. Aber ich habe noch im Gedächtnis, dass Relations mit MyISAM auch nicht möglich sind, aber mit InnoDB. Dann würde es sich wenigstens lohnen.

Vergiss nicht, einen Performance-Test durchzuführen. All die netten InnoDB-Features gibt es nicht zum Nulltarif. http://dev.mysql.com/doc/refman/5.1/en/storage-engine-compare-transactions.html

echo "$verabschiedung $name";

[Tom]

Hello Dedlfix,

Meine Datenbanken sind leider vollständig mit MyISAM gebaut. Da muss ich die erstmal umbauen. Aber ich habe noch im Gedächtnis, dass Relations mit MyISAM auch nicht möglich sind, aber mit InnoDB. Dann würde es sich wenigstens lohnen.

Vergiss nicht, einen Performance-Test durchzuführen. All die netten InnoDB-Features gibt es nicht zum Nulltarif. http://dev.mysql.com/doc/refman/5.1/en/storage-engine-compare-transactions.html

Die erste Aufgabe, die ich dann mal damit angehen würde, wäre das beliebte "Insert Before" oder auch "Insert Behind". Dazu mussten bisher (symbolisch) die Statements

before:

lock table tabelname;
  select @nummer := lfdnr from tablename where id = $id;
  update tablename set lfdnr = lfdnr+1 where lfdnr >= @nummer;
  insert into tabelename (lfdnr,  ... columnlist) values (@nummer, ... valuelist);
  unlock table tablename;

ausgeführt werden.

Vielleicht kann man das ja mit einem "select .. for update" besser machen, dass wenigstens nur noch die Datensätze gesperrt werden, die auch manipuliert werden.

da bringe ich jetzt nur das Insert noch nicht unter.

Liebe Grüße aus dem Cyberspace

Tom vom Berg