Insert Fehler vermeiden durch Transaktionen?

[hawkmaster]

Hallo zusammen,
ich hatte bisher noch nichts mit MySQL und Transaktionen zu tun gehabt.

Bei folgender Situation frage ich mich, ob dies vielleicht sinnvoll wäre:

Ein Anwender gibt über eine Webandendung (PHP mit PDO) Daten in eine MySQL DB ein.

1. Speicherung von Kundenname, Kundennummer etc. in Tabelle "kunde", Rückgabe neue "kundenid"
2. Speicherung von Vertragsdaten wie Vertragsnr. Datum, Beschreibung in Tabelle "vertrag", Rückgabe neue "vertragid".
3. Speicherung von  "kundenid" und "vertragid" in Relationstabelle "kunde_vertrag_rel"

Ich hatte in einem internen Test einen Fall, wo Step 1 und 2 gemacht wurde, aber die Relationstabelle Eintrag fehlte bzw. ich die "vertragid" nicht finden konnte.

Könnte man obiges Szenario mit einer Transaktion absichern?
Momentan sind die Tabellen MyISam. Die müsste man vermutlich auf Innodb umstellen?

Was mir nicht ganz klar ist: Bei der Speicherung der Daten macht das PHP Script vorher diverse Prüfungen, ob z.b. der Kundenname schon da ist oder nicht.

Ein verkürztes Beispiel

  
..  
$check_kunde = get_KundenDaten();  
  
if(....){//Wenn kunde noch nicht vorhanden  
$neueKundeid = InsertKundenData($Customername,$Customernumber..//Insert Kunden Daten in "kunde" Tabelle  
}  
..  
$newvertragyid = InsertVertrag($_POST['vertragnr'].....//insert in "vertrag" Tabelle  
  
if( (!empty($neueKundeid)) AND (!empty($newvertragyid)) ){//  
...  
InsertRelCustomerOppnr($neueKundeid,$newvertragyid );//Eintrag Relations Tabelle  
  
if( (empty($neueKundeid)) AND (!empty($newvertragyid)) ){//wenn unter gleichem Kunde Name ein neuer Vertrag eingegeben wird  
$neueKundeid= get_KundenID(.....);//vorhandene Kundenid holen  
InsertRelCustomerOppnr($neueKundeid,$newvertragyid );//Eintrag Relations Tabelle  
  

Was ich damit fragen will:
Wie kann man obige Logik mit einer Transaktion verbinden?
In einer Transaktion selbst kann man ja nur SQL Befehle absetzen oder?

vielen Dank und viele Grüße
hawk

[hotti]

hi,

Ich hatte in einem internen Test einen Fall, wo Step 1 und 2 gemacht wurde, aber die Relationstabelle Eintrag fehlte bzw. ich die "vertragid" nicht finden konnte.

Könnte man obiges Szenario mit einer Transaktion absichern?

Auf jeden Fall.

Momentan sind die Tabellen MyISam. Die müsste man vermutlich auf Innodb umstellen?

Yes.

Was mir nicht ganz klar ist: Bei der Speicherung der Daten macht das PHP Script vorher diverse Prüfungen, ob z.b. der Kundenname schon da ist oder nicht.

Einfacher mit Insert on duplicate key update.

[..]

Wie kann man obige Logik mit einer Transaktion verbinden?

Set AutoCommit = 0, set RaiseError = 1, in einem try{} die Statements absetzen, in catch(){} ggf. ein Rollback und wenn der try{} erfolgreich war ein Commit.

RaiseError: Erhebt SQL-Fehler in den status einer Exception. In PHP::PDO mal nachlesen, ob das machbar ist, in Perl geht das auf jeden Fall.

Horst

[T-Rex]

Bei folgender Situation frage ich mich, ob dies vielleicht sinnvoll wäre:

Jaein.

Eine Transaktion stelle ich mir immer wie ein Packet vor. Du startest die Transaktion und öffnest das Packet. Dann packst du Sachen rein die du abschicken willst (später mit commit).
Jetzt ist die Frage welche Daten alleine nicht vorhanden sein sollten.

Bei deinem Beispiel würde ich den Kunden immer abspeichern. Kundendaten sind wertvoll und sollten auch dann abgespeichert werden, wenn mit dem Vertrag etwas nicht stimmt (also technisch betrachtet).
Was den Rest angeht habe ich ein Gefühl als ob das Konzept schlecht gewählt ist. Für mich kann ein Vertrag nur ein Kunde zugeordnet sein. Deshalb würde ich in der Kunden-Vertragsdatenbank ein Feld für den Kunden einbauen. Somit hast du eine 1:n Verbindung. Ein Kunde kann n Verträge haben, aber ein Vertrag kann nur einem Kunden zugeordnet sein.
Ergo würde ich in dem Beispiel komplett auf Transaktion verzichten und das Konzept anpassen.

Ich hoffe ich konnte dich verwirren.

T-wirrung Ver
Gruß
Rex

[hawkmaster]

Hallo Rex,

Für mich kann ein Vertrag nur ein Kunde zugeordnet sein. Deshalb würde ich in der Kunden-Vertragsdatenbank ein Feld für den Kunden einbauen. Somit hast du eine 1:n Verbindung. Ein Kunde kann n Verträge haben, aber ein Vertrag kann nur einem Kunden zugeordnet sein.

Du meint in die Relationstabelle "kunde_vertrag_rel" noch eine zusätzliche Spalte?
Aber da steht doch schon die Kundenid drin?

vielen Dank und viele Grüße
hawk

[Matthias Apsel]

Om nah hoo pez nyeetz, hawkmaster!

Für mich kann ein Vertrag nur ein Kunde zugeordnet sein. Deshalb würde ich in der Kunden-Vertragsdatenbank ein Feld für den Kunden einbauen. Somit hast du eine 1:n Verbindung. Ein Kunde kann n Verträge haben, aber ein Vertrag kann nur einem Kunden zugeordnet sein.

Du meint in die Relationstabelle "kunde_vertrag_rel" noch eine zusätzliche Spalte?
Aber da steht doch schon die Kundenid drin?

Für eine 1:n-Relation, die mir hier auch richtig erscheint brauchst du keine eigene Tabelle. In die Tabelle der n-Seite (Verträge) kommt der Schlüssel der 1-Seite (Kunden).

Matthias

[Sandrine]

1. Speicherung von Kundenname, Kundennummer etc. in Tabelle "kunde", Rückgabe neue "kundenid"
2. Speicherung von Vertragsdaten wie Vertragsnr. Datum, Beschreibung in Tabelle "vertrag", Rückgabe neue "vertragid".
3. Speicherung von  "kundenid" und "vertragid" in Relationstabelle "kunde_vertrag_rel"

Sofern demselben Vertrag nicht mehrere Kunden zugeordnet werden können, ist der dritte Schritt bzw. die Tabelle kunde_vertrag_rel sinnlos; platziere die Kunden-ID im Vertragsdatensatz.

Ich hatte in einem internen Test einen Fall, wo Step 1 und 2 gemacht wurde, aber die Relationstabelle Eintrag fehlte bzw. ich die "vertragid" nicht finden konnte.

Könnte man obiges Szenario mit einer Transaktion absichern?

Die Frage wäre erstmal, warum der banale Eintrag in der Relationstabelle fehlschlug.
Gründe lägen dafür nur im Bereich Platzmangel auf dem Datenspeicher, Verbindungsabbruch zur Datenbank, unerwarteter Abbruch des Skriptes selbst und andere derartige Katastrophenfälle.
Es ist sicher nicht so, dass eine Transaktion als Absicherung gegen unvollständig geschriebene Daten überflüssig wäre, ganz im Gegenteil, aber diese Gründe sind alle so außergewöhnlicher Natur, dass mir da eher noch etwas Größeres zu dräuen scheint, dem unbedingt auf den Grund gegangen werden muss.

Was mir nicht ganz klar ist: Bei der Speicherung der Daten macht das PHP-Script vorher diverse Prüfungen, ob z.b. der Kundenname schon da ist oder nicht.

Ein verkürztes Beispiel

..
$check_kunde = get_KundenDaten();

if(....){//Wenn kunde noch nicht vorhanden
$neueKundeid = InsertKundenData($Customername,$Customernumber..//Insert Kunden Daten in "kunde" Tabelle
}
..
$newvertragyid = InsertVertrag($_POST['vertragnr'].....//insert in "vertrag" Tabelle

if( (!empty($neueKundeid)) AND (!empty($newvertragyid)) ){//
...
InsertRelCustomerOppnr($neueKundeid,$newvertragyid );//Eintrag Relations Tabelle

if( (empty($neueKundeid)) AND (!empty($newvertragyid)) ){//wenn unter gleichem Kunde Name ein neuer Vertrag eingegeben wird
$neueKundeid= get_KundenID(.....);//vorhandene Kundenid holen
InsertRelCustomerOppnr($neueKundeid,$newvertragyid );//Eintrag Relations Tabelle

  

> Wie kann man obige Logik mit einer Transaktion verbinden?  
  
Dein Ablauf ist so schon etwas verquer: Du prüfst, ob ein Kunde schon bekannt ist und legst, falls nicht, einen neuen an. Danach legst du den Vertrag an. Anschließend drehst du dich zweimal im Kreis, indem du prüfst, ob du einen neuen Kunden angelegt hast: Falls ja, wird die Relation angelegt, falls nein, wird zum \_zweiten Mal\_ der bestehende Kunde abgefragt und dann legt ein separater, aber vollkommen identischer Aufruf die Relation an.  
  
Es ist, jedenfalls nach deinem Codeschnippsel, überflüssig, die Relation anzulegen in der Abhängigkeit davon, ob zuvor ein neuer Kunde angelegt wurde oder nicht. Die Relation wird angelegt, fertig. Du musst auch nicht zweimal innerhalb von Mikrosekunden denselben bestehenden Kunden abfragen.  
Last but not least scheint dein Code auch irgendwie nicht so recht auf den Fall einzugehen, dass das Anlegen des Vertrages fehlschlägt. Du fragst nämlich zweimal in Zusammenhang mit der Kunden-ID das Bestehen des Vertrages ab, obwohl das überflüssig wäre, wenn du gleich nach Vertragsanlage diesen Fehler abgefangen hättest (dann kämest du gar nicht mehr bei der Relation an).  
  
Geradlinig wäre dieser Weg:  
  
1\. Bestehende Kundendaten suchen und, so möglich, in kundenid ablegen.  
2\. Falls keine vorhanden, neuen Kunden anlegen und in kundenid ablegen.  
3\. Neuen Vertrag anlegen.  
4\. Relation anlegen.  
  
Die Transaktion sollte Punkt 3 und 4 umschließen, da die Kundenexistenz nicht abhängig vom Vertrag und der Relation ist. Nur die Relation ist abhängig vom Vertrag.  
Hast du die Kunden-ID im Vertrag mit der Kundendatenbank verbunden (Stichwort foreign key), fängt die Transaktion auch den Fall ab, dass zwischen Schritt 2 und 3 der Kunde flötengeht, aus welchem Grund auch immer (Atomkrieg, Weltuntergang, schwarzes Loch im Rechenzentrum, …).  
  

> In einer Transaktion selbst kann man ja nur SQL Befehle absetzen oder?  
  
Ja, aber falls die Ursache für den nicht ausgeführten dritten Schritt datenbankseitig liegt, wird der Fehler von der Transaktion aufgefangen. Aber wie ich oben schon schrieb, solltest du erstmal ergründen, warum Schritt 3 fehlschlägt, und dir anschließend überlegen, wozu du die Relationstabelle brauchst.  

[Encoder]

1. Bestehende Kundendaten suchen und, so möglich, in kundenid ablegen.
2. Falls keine vorhanden, neuen Kunden anlegen und in kundenid ablegen.

Ich würds noch ein bisschen verfeinern. Erst mal Kunde suchen oder neu anlegen. Dann zu dem den Vertrag ablegen.
Der Benutzer muss wissen ob ein Kunde neu angelegt oder ein bestehender verwendet wird. Sonst wird durch einen Vertipper ein neuer Kunde angelegt statt ein bestehender verwendet. Oder es wird ein bestehender verwendet der zufällig gleiche Daten hat, statt ein neuer angelegt.
Da sollte nicht alles auf einmal eingegeben werden und dann mit viel Magie im Hintergrund *irgendwas* passieren.

[hawkmaster]

Hallo
ich danke dir sehr für deine ausführliche Hilfe und Antwort.

Die Frage wäre erstmal, warum der banale Eintrag in der Relationstabelle fehlschlug.
Gründe lägen dafür nur im Bereich Platzmangel auf dem Datenspeicher, Verbindungsabbruch zur Datenbank, unerwarteter Abbruch des Skriptes selbst und andere derartige Katastrophenfälle.
Es ist sicher nicht so, dass eine Transaktion als Absicherung gegen unvollständig geschriebene Daten überflüssig wäre, ganz im Gegenteil, aber diese Gründe sind alle so außergewöhnlicher Natur, dass mir da eher noch etwas Größeres zu dräuen scheint, dem unbedingt auf den Grund gegangen werden muss.

Das habe ich auch schon veruscht und das wäre mir auch wichtig zu wissen warum dieser Eintrag fehlt.
Platzmangel kann man definitiv ausschließen.
Eher war es ein Verbindungsabbruch aber wie will man so etwas im nachhinein prüfen oder nachstellen?

vielen Dank und viele Grüße
hawk

[MudGuard]

Hi,

Was mir nicht ganz klar ist: Bei der Speicherung der Daten macht das PHP Script vorher diverse Prüfungen, ob z.b. der Kundenname schon da ist oder nicht.

Vorsicht - Kundenname allein reicht für die Eindeutigkeitsprüfung nicht aus.
Es gibt z.B. mehrere Peter Müller oder John Smith auf dieser Welt.
Und selbst mit Adresse ist es nicht eindeutig - es kommt öfters mal vor, daß Vater und Sohn gleich heißen und auch noch im gleichen Haus wohnen.
==> Geburtsdatum oder anderes Unterscheidungskriterium muß auch noch mit dazu ...

cu,
Andreas