Wie mit INSERT in mehrere Tabellen gleichzeitig umgehen?

[*Markus]

Hallo,

ich habe folgenden Teil eines Datenmodells:

Füge ich nun eine neue Person mit Adresse, Kundenart, Kundennummer und Erreichbarkeit ein, müsste ich so vorgehen:

*) Zuerst die Personendaten einfuegen
*) Dann den Zaehlerwert von Person holen und zwischenspeichern
*) Jetzt die Erreichbarkeitsdaten in der Erreichbarkeitstabelle speichern
*) Dann die ID der Erreichbarkeit auslesen um nachher eine Verknüpfung mit Kunde herstellen zu können
*) Dann die Adressedaten einfügen
*) Dann den Adresszaehler holen und in einer Variable zwischenspeichern
*) Jetzt eine neue Kundennummer erzeugen (In der K-Nummerntabelle einen Wert einfügen, damit die Kundennummer hochzählt)
*) Danach diese Kundennummer auslesne und zwischenspeichern
*) Zu guter letzt alle Nummern und die restl. Daten in "Kunde" einfügen um eine Beziehung zu allen Tabellen herzustellen zu können

Es funktioniert so zwar wunderbar, aber ich kann mir nicht vorstellen, dass man in der Praxis so vorgeht. Vor allem könnte es zwar ziemlich unwahrscheinlich, aber theoretisch doch möglich sein, dass gleichzeitig zwei Clients zur gleichen Zeit auf den Server zugreifen und zwei neue Personen mit gleichem Geburtsdatum und Namen eingefügt werden. Und durch die Tatsache, dass es mehrere INSERT-Anweisungen sind, zwischen denen ja eine gewisse Pause ist, könnten die Personen-IDs durcheinander gebracht werden wodurch die Adresse1, die zu Person Max Mustermann1 gehört, plötzlich dem Max Mustermann2 zugewiesen wird, da in der Zwischenzeit der Zähler wieder um eins weitegezählt hat.

Durch diesen Sachverhalt stellt sich mir die Frage, wie man derartige INSERTs vielleicht anders lösen könnte. Ein mehrfaches INSERT in mehrere Tabellen gibt es ja nicht (ähnlich wie ein SELECT mit JOINs). Wie würde man da also praxisbezogen vorgehen?

Markus

[Chris©]

Hallo,

Es funktioniert so zwar wunderbar, aber ich kann mir nicht vorstellen, dass man in der Praxis so vorgeht. Vor allem könnte es zwar ziemlich unwahrscheinlich, aber theoretisch doch möglich sein, dass gleichzeitig zwei Clients zur gleichen Zeit auf den Server zugreifen und zwei neue Personen mit gleichem Geburtsdatum und Namen eingefügt werden. Und durch die Tatsache, dass es mehrere INSERT-Anweisungen sind, zwischen denen ja eine gewisse Pause ist, könnten die Personen-IDs durcheinander gebracht werden wodurch die Adresse1, die zu Person Max Mustermann1 gehört, plötzlich dem Max Mustermann2 zugewiesen wird, da in der Zwischenzeit der Zähler wieder um eins weitegezählt hat.

Damit hier keine Race-Condition für die Schlüssel entsteht, bieten die Datenbanksysteme normalerweise sogenannte Autowerte an. Das sind numerische Schlüssel, die vom DBMS beim Insert eines Datensatzes automatisch ermittelt und für jede Connection separat geführt werden. Meistens sind dies Auto-Increment-Keys. Sie sind natürlich UNIQUE und daher gleichzeitig Primary Key des Datensatzes.

Dadurch, dass das DBMS sich darum kümmert, sind der Insert und die Schlüsselvergabe nach außen atomar gebunden und es gibt keine lost keys, da das System nur einen Schlüssel generiert, wenn der DS tatsächlich eingefügt wird.

Man muss also nach dem Insert zuerst fragen, ob der Insert überhaupt geklappt hat (also nicht gegen Contraints verstoßen hat) und dann den Schlüssel (z.B. Last_Insert_Id) abholen. Den kann man dann als Foreign Key für die nächste Tabelle verwenden.

Insgesamt sollte man natürlich über den gesamten Vorgang der Insert-Kette eine transaktion legen, wenn das DBMS dies zulässt, damit die Integrität auf jeden Fall wiederhergestellt werden kann, wenn etwas schief gegangen sein sollte.

LG
Chris©

[*Markus]

Hallo,

Man muss also nach dem Insert zuerst fragen, ob der Insert überhaupt geklappt hat (also nicht gegen Contraints verstoßen hat) und dann den Schlüssel (z.B. Last_Insert_Id) abholen. Den kann man dann als Foreign Key für die nächste Tabelle verwenden.

Nun, solange der INSERT keine SQLException geworfen hat, hat er ja auch geklappt.

Insgesamt sollte man natürlich über den gesamten Vorgang der Insert-Kette eine transaktion legen, wenn das DBMS dies zulässt, damit die Integrität auf jeden Fall wiederhergestellt werden kann, wenn etwas schief gegangen sein sollte.

Das klingt interessant. Wie wird so etwas normalerweise bewerkstelligt? Ich bin mir jetzt nicht sicher, ob das bei mir sogar funktionieren würde, da der ganze Vorgang während einer offenen Pool Connection passiert:

  
con = DatabaseSingleton.getPoolConnection();  
try {  
    stmt = con.createStatement();  
  
//  Hier die INSERT-Befehle.....  
  
} catch (SQLException e) {  
    throw new DaoException(e);  
}  
DatabaseSingleton.closePoolConnection(con);  

Da ich leider noch zu wenig über die internen Abläufe der DB-Interaktion mit Java weiß, bin ich nicht sicher, ob hier die Integrität bei auftretenden Fehlern, wie bspw ein Verbindungsausfall, auf jeden Fall gewährleistet bleibt.

Markus

[Frank (no reg)]

Hallo,

Da ich leider noch zu wenig über die internen Abläufe der DB-Interaktion mit Java weiß ...

Na dann kümmer dich doch mal um dieses Problem!

Eine simple Googlesuche hat mich gerade auf conn.setAutoCommit(true|false), conn.Commit() stossen lassen.

<http://java.sun.com/docs/books/tutorial/jdbc/basics/examples/TransactionPairs.java@mal ein Beispiel>

Man muss also nach dem Insert zuerst fragen, ob der Insert überhaupt geklappt hat (also nicht gegen Contraints verstoßen hat) und dann den Schlüssel (z.B. Last_Insert_Id) abholen. Den kann man dann als Foreign Key für die nächste Tabelle verwenden.

Wenn der Befehl gegen Constraints verstossen hätte, dann hätte es dir eine SqlException geworfen. (Sollte es in 95% der Fälle)

Wenn du Sequenzen benutzen kannst, dann kannst du dir diese Tabellen für Mitarbeiternummer und Kundennummer stecken. Aber benutze die Sequenzen innerhalb von Transaktionen, wenn das unterstützt wird.

Gruss, Frank

[Vinzenz Mai]

Hallo

Es funktioniert so zwar wunderbar, aber ich kann mir nicht vorstellen, dass man in der Praxis so vorgeht.

Verdammt, es gibt Sequenzen, Autoincrement-Werte und was ich was. Wie Dir bereits mehrfach gesagt wurde, bieten die DBMS auch die Funktionalität, genau diesen in dieser DB-Connection vergebenen Wert zu ermitteln.

Nutze das! Und nicht Dein kaputtes Konstrukt.

Durch diesen Sachverhalt stellt sich mir die Frage, wie man derartige INSERTs vielleicht anders lösen könnte. Ein mehrfaches INSERT in mehrere Tabellen gibt es ja nicht (ähnlich wie ein SELECT mit JOINs).

Das ist in dieser Absolutheit falsch. Es gibt DBMS, die über Views ein INSERT in mehrere Tabellen ermöglichen. Dabei werden (mal wieder) Autoincrement-Werte bzw. Sequenzen benutzt.

Wie würde man da also praxisbezogen vorgehen?

Wie Dir in diversen Threads bereits gesagt wurde, nutze die entsprechenden und vorhandenen Mechanismen Deines DBMS, in Deinem Fall Sequenzen. (Ja, ich weiß zufällig noch, dass Du PostgreSQL nutzt. Sag's doch!)

Freundliche Grüße

Vinzenz

[*Markus]

Hallo,

Wie Dir in diversen Threads bereits gesagt wurde, nutze die entsprechenden und vorhandenen Mechanismen Deines DBMS, in Deinem Fall Sequenzen. (Ja, ich weiß zufällig noch, dass Du PostgreSQL nutzt. Sag's doch!)

Ich benutze ja auch Sequenzen. Wie denn sonst könnte ich immer eine neue Kundennummer udn Mitarbeiternummer abfragen. Außerdem sind die Primary Keys in div. Tabellen ebenfalls Sequenzen. Auf die Idee wäre ich gar nicht gekommen, mit einer Programmlogik hochzuzählen.

Markus

[Vinzenz Mai]

Hallo Markus,

Ich benutze ja auch Sequenzen. Wie denn sonst könnte ich immer eine neue Kundennummer udn Mitarbeiternummer abfragen.

und wo ist das Problem, die entsprechende Funktion des DBMS zu nutzen?
Im übrigen würde es sich anbieten, das ganze in einer Stored Procedure zu kapseln.

Freundliche Grüße

Vinzenz

[Rouven]

Hello,

Ich benutze ja auch Sequenzen. Wie denn sonst könnte ich immer eine neue Kundennummer udn Mitarbeiternummer abfragen. Außerdem sind die Primary Keys in div. Tabellen ebenfalls Sequenzen. Auf die Idee wäre ich gar nicht gekommen, mit einer Programmlogik hochzuzählen.

okay, dann kombiniere das ganze noch mit einer Transaktion, damit nicht bei einem Fehlschlag während des letzten INSERTs verwaiste Adressen o.ä. im System rumfliegen. Dann wäre das meiner Meinung nach durchaus der Weg, wie eine Applikation da dran gehen sollte.

MfG
Rouven

[*Markus]

Hallo,

okay, dann kombiniere das ganze noch mit einer Transaktion, damit nicht bei einem Fehlschlag während des letzten INSERTs verwaiste Adressen o.ä. im System rumfliegen. Dann wäre das meiner Meinung nach durchaus der Weg, wie eine Applikation da dran gehen sollte.

Gut. Wie man Transaktionen verwendet, muss ich noch ergründen, aber danke einstweil.

Markus