Werbung einblenden Werbung ausblenden


Home / Tutorials / mysql Handbuch / transaction


commit und rollback
transaction mit einer Programmiersprache verwenden

commit und rollback

Mit Transaktionen kann man erreichen, dass eine Gruppe von SQL Statements entweder komplett ausgeführt wird, oder komplett nicht ausgeführt wird. Dies macht Sinn, wenn das ausführen eines Statements nur dann zu korrekten Ergebnissen führt, wenn das andere auch ausgeführt wird. Wird zum Beispiel Geld von einem Konto auf das andere überwiesen, zerfällt dieser Prozess in zwei Prozesse. Erstens wird von dem einen Konto Geld abgehoben und zweitens wird die gleiche Summe dem anderen Konto gutgeschrieben. Würde jetzt das Geld nur von dem einen Konto abgehoben, auf das andere Konto aber nicht überwiesen, z.B. weil irgend jemand den Stecker aus der Dose zieht, dann wäre es weg. Je länger das Abarbeiten eines SQL Statements dauert, desto größer ist die Gefahr,dass irgend etwas der Art auftritt. Transaktionen bieten aber nicht nur Schutz vor Programmier- oder technischen Fehlern. Sie bieten auch Schutz vor Fehlern, die durch den konkurrierenden Zugriff mehrer User auf die gleichen Datensätze entstehen. Nehmen wir an, wir haben zwei Tabellen, etwa die, die wir im Kapitel update und delete über mehrere Tabellen, verwendet haben. Also eine Tabelle, die die Kunden hält und eine andere, die Bestellungen hält. Nehmen wir an, bei jeder Bestellung wird geprüft, ob der Kunde, der die Bestellung gemacht hat, auch tatsächlich in der Tabelle Kunde existiert, nur dann ist es ja sinnvoll, diese Bestellung in die Tabelle einzufügen, andernfalls müssten wir ja den Kunden hinzufügen. Nehmen wir weiter an, irgendjemand prüft ab und an, ob es einen Kunden gibt, der gar keine Bestellungen hat und beschliesst, diese Karteileiche zu löschen. Es kann nun passieren, dass die eine Abfrage feststellt, dass dieser Kunde keine Bestellungen hat und folglich wird er gelöscht. Denkbar ist nun der Fall, daß beide, der der prüft und der die Bestellungen eintragen will, im gleichen Moment in der Tabelle Kunde nach diesem Kunden suchen. Der eine stellt fest, dass es diesen Kunden gibt, aber er keine Bestellung hat, folglich löscht er ihn. Der andere stellt auch fest, dass es diesen Kunden gibt, und folglich trägt er die Bestellung ein, ohne den Kunden in die Datenbank Kundschaft einzutragen. Das hat dann die Konsequenz, dass diese Bestellung keinem Kunden mehr zugeordnet werden kann. Das Risiko, dass so etwas passiert, steigt natürlich mit der Anzahl konkurrierender Zugriffe. Eine Transaktion bietet Sicherheit bei beide Problemtypen. Die Zusammenfassung einer Gruppe als Transaction bewirkt, dass, in Abhängigkeit vom benutzten Tabellen Typ, entweder die einzelne Tabelle, der entsprechende Datensatz, komplett, oder der entsprechende Datensatz für update,insert und delete gesperrt wird. Vereinfacht kann man also sagen, Transaktions sorgen dafür, dass nur ein einziger Client für die Dauer der Transaktion Zugriff auf die Daten hat. Hier nun ein kurzes Beispiel.

Test eins: Jedes SQL Statement wird sofort ausgeführt

use testlauf;

create table testofix(
Name char(40),
Vorname char(50),
Konto int(20)) type =BDB;

insert into testofix (Name,Vorname,Konto) values ("Ehmann","Andres",7102344);
insert into testofix (Name,Vorname,Konto) values ("Maier",Peter,45333544);

Test zwei: Nach einem fehlerhaften SQL Statement wird commit ausgeführt

mysql>SET AUTOCOMMIT=0;
mysql> create table testofix(
-> Name char(40),
-> Vorname char(50),
-> Konto int(20)) type=BDB;
Query OK, 0 rows affected (0.05 sec)

mysql> insert into testofix(Name,Vorname,Konto) values("Ehmann","Andres",
-> 710235434);
Query OK, 1 row affected (0.00 sec)

mysql> insert into testofix(Name,Vorname,Konto) value("Maier",Peter,
-> 3443543333);
ERROR 1064: You have an error in your SQL syntax. Check the manual that corresp
onds to your MySQL server version for the right syntax to use near 'value("Maier
",Peter,
3443543333)' at line 1
mysql> commit;
Query OK, 0 rows affected (0.00 sec)

mysql>

Test drei: Nach einem fehlerhaften SQL Statement wird commit ausgeführt

mysql> create table testofix(
-> Name char(40),
-> Vorname char(50),
-> Konto int(20)) type=BDB;
Query OK, 0 rows affected (0.06 sec)

mysql> Begin;
Query OK, 0 rows affected (0.00 sec)

mysql> insert into testofix(Name,Vorname,Konto) values("Ehmann","Andres",
-> 7102344);
Query OK, 1 row affected (0.00 sec)

mysql> insert into testofix(Name,Vorname,Konto) values("Maier",Peter,
-> 45333544);
ERROR 1054: Unknown column 'Peter' in 'field list'
mysql> rollback;
Query OK, 0 rows affected (0.00 sec)

mysql>

Test eins macht folgende Einträge in die Tabelle testofix.

mysql> select * from testofix;

Name Vorname Konto
Ehmann Andres 7102344

1 row in set (0.06 sec)
mysql>

Test zwei: Test zwei kann nicht im batch mode durchgeführt werden, da nach dem zweiten insert, das ja Fehlerhaft ist, da der Vorname nicht in Anführungszeichen steht, abgebrochen wird. Das heisst commit wird nicht ausgeführt, weil das Programm gar nicht an die Stelle kommt. Zweitens muss bei diesem Beispiel mysql davon überzeugt werden, dass er einen sql statement nicht sofort ausführt, sondern auf commit warten. Das ereichen wir durch die Zeile set autocommit=0. Das Ergebnis sieht aber so aus.

mysql> select * from testofix;

Name Vorname Konto
Ehmann Andres 710235434

1 row in set (0.00 sec)
mysql>

Das heisst, der korrekte Eintrag wurde nach commit noch ausgeführt. Die Meldung, dass 0 rows affected seien, ist also irreführend.

Test drei: Auch das kann man aus didaktischen Gründen nicht im batch Mode laufen lassen, da sonst rollback und commit zum gleichen Ergebnis führen, da weder das eine noch das andere nach dem falschen Sql Statement abgearbeitet werden. Weder commit noch rollback würden zu einem Eintrag führen. Das Ergebnis wenn man es interaktiv laufen läßt, sieht so aus.

mysql> select * from testofix;
Empty set (0.06 sec)
mysql>

Dies wiederum ist genau das, was wir erwartet hatten. Rollback macht alle SQL Statements bis zu Begin rückgängig.

Bedauerlich ist, dass uns das so gar nichts nützt, da es so erstmal nur interaktiv funktionniert. mysql wird aber meistens über irgendwelche Programmiersprachen angesprochen, Perl, PHP oder Java. Spricht man mysql so an, ist es nicht interaktiv. Wir wollen am Ende wissen, ob eine der beteiligten Sql Statements fehlerhaft war und den ganzen Block nur dann ausführen, wenn keines fehlerhaft war. Genau das funktionniert aber nur interaktiv. Wir müssen es also schaffen, transactions in eine Programmiersprache einzubinden.

transaction mit einer Programmiersprache verwenden

Verdeutlichen wir uns die Vorgehensweise anhand von Perl. Das im Kapitel mysql mit Perl ansteuern behandelte sollte man jetzt parat haben. Wir verdeutlichen uns die Zusammenhänge an diesem kleinen Perl Skript.

use DBI;

$verbinden1="DBI:mysql:testlauf";
$verbinden2="";
$verbinden3="";

my $dbh = DBI->connect( "$verbinden1","$verbinden2","$verbinden3") || die "Database connection not made: $DBI::errstr";

$dbh->{PrintError}=0;
$dbh->{RaiseError}=1;

my $sql = qq{create table testofix (
Name char(40),
Vorname int(50),
Konto int(20)) TYPE=BDB };
my $sth = $dbh->prepare( $sql );
$sth->execute();
$sth->finish();

$dbh->do("SET AUTOCOMMIT=0");

eval
{
$dbh->do("insert into testofix (Name,Vorname,Konto) values ('Ehmann','Andres',710235434)");
$dbh->do("insert into testofix (Name,Vorname,Konto) values ('Maier','Peter',45333544)");
};

if($@)
{
print "Da ist was schief gelaufen. Der Fehler ist \n";
print "$@";
$dbh->{rollback};
}
else
{
print "Alle Sql Statements sind ok.";
$dbh->{commit};
}

if(!$@)
{
my $sql = qq{select * from testofix};
my $sth = $dbh->prepare( $sql ); $sth->execute();
while(@ergebnis=$sth->fetchrow_array)
{
print "\n".$ergebnis[0]." ".$ergebnis[1]." ".$ergebnis[2]." ".$ergebnis[3]." \n";
}
$sth->finish();

}

$dbh->disconnect();

Zuerst eine Bemerkung vorne weg. In der Literatur finden sich zum Abschalten des Autocommit Modus, folgende Zeile.

$dbh->{AutoCommit};

Da ist dann aber der dbd:mysql Treiber der Meinung, dass mysql keine Transaktionen unterstützt. Das heisst, das funktionniert noch nicht oder nicht unter jedem Betriebssystem oder nicht mit bestimmten Versionen des dbd:mysql Treibers oder was auch immer. Zu beachten sind nun folgende Punkte.
Die Tabelle muss darf nicht vom Typ MyIsam sein, was der Default ist, sondern z.B. vom Typ BDB. Genaueres unten. Der Autocommit Modus ist abzustellen. Das geht nur, wie oben bereits geschildert mit $dbh->do("SET AUTOCOMMIT=0"). Beim Testen des Skriptes oder wenn man damit rumspielen will, dient es der Übersichtlichkeit, wenn die Fehlermeldungen nicht gedruckt werden. Wir schreiben also $dbh->{PrintError}=0. Wir wollen aber, dass er sich die Fehler aus dem eval Block merkt und schreiben folglich $dbh->{RaiseError}=1. Die eigentlichen SQL Statements schreiben wir in den eval Block, wenn dort was schief geht, landet es in der Perl Sondervariablen $@. In Abhängigkeit von $@ führen wir dann rollback oder commit aus. Wenn also ein Fehler aufgetreten ist, hat die Sondervarible $@ einen Wert der ungleich undef ist, was für Perl ja true bedeutet. Folglich ist was schief gelaufen und wir machen Rollback. Zurückgefahren werden alle Sql Statements, die nach der Zeile $dbh->do("SET AUTOCOMMIT=0") kommen. Foglich erhalten wir in Abhängigkeit von den Sql Statements unterschiedliche Ergebnisse.
Wenn alle Sql Statements korrekt sind

C:\test>perl testlauf.pl
Alle Sql Statements sind ok.
Ehmann 0 710235434

Maier 0 45333544

C:\test>

Wenn die Sql Statements fehlerhaft sind

C:\test>perl testlauf.pl
Da ist was schief gelaufen. Der Fehler ist
DBD::mysql::db do failed: Unknown column 'Peter' in 'field list' at testlauf.pl
line 25.

C:\test>

Wie deutlich zu erkennen, werden bei der ersten Variante die Einträge eingetragen und bei der zweiten Variante nicht. Die Tabelle als solche allerdings wird generiert (wenn man nicht dort einen Fehler macht). Der Fehler, der in diesem Beispiel eingebaut wurde ist dieser. Anstatt

$dbh->do("insert into testofix (Name,Vorname,Konto) values ('Maier','Peter',45333544)");

wurde

$dbh->do("insert into testofix (Name,Vorname,Konto) values ('Maier',Peter,45333544)");

geschrieben. Das heisst, die einfachen Anführungsstriche bei Peter wurden entfernt. Damit ist es es nicht mehr vom Typ String und kann in eine Spalte vom Typ char nicht eingetragen werden.

Lösung mit PHP
Es macht Sinn, sich das im Kapitel mysql mit PHP ansteuern beschriebene nochmal zu vergegenwärtigen. Um Transactions mit PHP zu realisieren, muss man etwas in der Art machen.


<?
$a="127.0.0.1";
$b="";
$c="";
$d="testlauf";

mysql_connect($a,$b,$c);
mysql_select_db($d);

mysql_query("
create table testofix (
Name char(40),
Vorname char(50),
Konto int(20)) TYPE=BDB");
$flag="ja";


function teste_alles()
{
mysql_query("Begin");
if(!mysql_query("insert into testofix (Name,Vorname,Konto) values ('Ehmann','Andres',710235434)"))
{$flag="nein";
return $flag; }
if(!mysql_query("insert into testofix (Name,Vorname,Konto) values ('Maier','Peter',45333544)"))
{$flag="nein";
return $flag;}

}


if (teste_alles()=="nein")
{
print "Kein Anschluss unter dieser Nummer";
mysql_query("rollback");
}
else
{
print "hurra";
mysql_query("commit");
}

Das Ergebnis ist dann das gleiche wie in Perl, also entweder sind beide Einträge anschliessend in der Tabelle testofix enthalten, wenn beide richtig waren, oder sie sind eben keine Einträge vorhanden, wenn ein Sql Statement falsch war.

vorhergehendes Kapitel