| 
commit und rollback
transaction mit einer Programmiersprache verwenden
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.
|
 |
