INSERT crasht im Multiuserbetrieb DAO/ODBC

[Milvus]

Hallo zusammen,

ich arbeite an der Lösung einer nicht genauer identifizierbaren Ursache zu einem Access Absturz im Multiuser-Zugriff.

Problembeschreibung:

In 10 Tabellen werden Daten geschrieben. Es gibt quais eine Mastertabelle, in die als erstes geschrieben wird und aus der auch der Key für die nachfolgenden 9 erhalten wird. Anschließend werden die dunklen 9 Geistertabelle mit dem FremdKey gefüllt. Ich nenne sie Geister, denn eine Datenbeziehung gibt es nicht (bitte zunächst hier nicht diskutieren (es sei denn, dies könnte die Ursache sein), ich hätte sie auch gerne, ja!!!).

Das ganze lief bisher wie folgt ab:

War ein Benutzer im Begriff einen Masterdatensatz und die 9 folgenden Dunkelgeister zu befüllen wurde ein Eintrag in einer Tabelle gesetzt, der andere User daran hindern sollte, auf diesen zuzugreifen...

Lassen wirs mal dabei! Das System hat ständig gecrasch und ich habe das Teil erst mal überarbeitet, wodurch es auch besser wurde. vom DoCmd Objekt (welchem man ja ganz offensichtlich sagen kann: Ach komm, ignoriere die Fehle reinfach, was auch intensiv benutzt wurde ) bin ich damit auf CurrentDB wechechselt mit dbFailonerror. Zurzeit ist es so implementiert, dass die Datenbank selbst entscheidet, wo gesperrt wird (Standardeinstellung von DAO dynaset). Es läuft deutlich besser aber immer noch nicht ganz rund und es kommt zu gelegentlichen Abstürzen. Da brauche ich Eure Erfahrung!

Beschreibung:

Ich spanne in DAO zunächst eine 1. Transaktion auf und befülle die Mastertabelle. Nach erhalt des Keys spanne ich eine 2. Transaktion auf und befülle den Rest. Dann werden beide Transaktionen commited (bei Fehler natürlich rollback).

Die erste Aktion (Schreiben des Masterdatensatzes) geschieht über ein DAO-Recordset auf die Mastertabelle mit AddNew, wobei dann auch gleich der Key erhalten wird.
Die 9 Geister werden mit CurrendDB.Excecute("INSERT...") befüllt.

Es gibt eine Backend-Datei mit ca. 20 Frontends, in welche die Zieltabellen per ODBC verknüpft sind.
Die Transaktionen bewirken schon mal, dass keine inskonsistenten Datensätze mehr entstehen und funktionieren.

Ich bin auf der Suche nach der Ursache un die suche ich nicht mehr im Programm selbst. Ich glaube, dass es die Maschine an sich ist, die nicht optimal benutzt wird.

Mein erster Gedanke war: Es laufen nur Inserts. Wie zum Geier sollen sich da die User ins Gehege kommen. Aber irgendwie scheint es damit zusammen zu hängen.

Noch ne Info: Es gibt kein Netzwerk zwischen Client und Server, Zugriff erfolgt Remote.

Kann es sein, dass ODBC, JET und Transaktion in dieser Konstellation nicht optimal laufen?
Könnte eine Datenbeziehung das Problem beheben?
Ist ein ADO-Zugriff per OLEDB zu empfehlen?
Wie ist eigentlich der Curser bei DAO/ODBC eingestellt in dieser Konstellation?
Ist Access als Backend-Datenbank (nennen wirs mal so) vielleicht schon an der Grenze des technisch machbaren?

Ich bedanke mich schon mal für alle hilfreichen Tipps

[PhilS]

Noch ne Info: Es gibt kein Netzwerk zwischen Client und Server, Zugriff erfolgt Remote.

Diese Aussage solltest du genauer ausführen.

Ein INSERT erfolgt in den Bereich (die Datenbankseite) einer Tabelle, die für neue Datensätze vorgesehen ist. Dort gehen i.d.R. alle INSERTs hin. Somit wird durch eine offene Transaktion dieselbe Ressource gesperrt, die auch alle anderen Inserts in dieselbe Tabelle verwenden möchten.

Grundsätzlich halte ich es nicht für eine gute Idee, auf ein Access Backend über ODBC zuzugreifen. - Warum nicht direkt JET/Ace?

[Wurliwurm]

Vorbehaltlich, daß es hier regulars gibt, die in dem Thema tiefer drin sind, meine 10 Cent.

Was mir nicht gefällt, ist das "Aufspannen" zweier Transaktionen. Grundsätzlich ist es so, daß eine offene Transaktion für den jeweiligen Anwender bereits beim Insert einen neuen Key mitteilt, dieser Key aber noch nicht für andere Anwender sichtbar ist.

Bsp:
BEGIN TRANSACTION
INSERT INTO T01 VALUES ....
SELECT FROM T01 WHERE ... -> hier kann man den vorigen INSERT schon sehen
COMMIT TRANSACTION

Ob der Zugriff über ODBC oder OLEDB passiert, wird hier keinen Unterschied machen. Wirklich stattfinden tut es nur im JET-Modul. Das Cursor-Thema hatten wir schon öfters hier und die Antwort ist, daß bei einem JET-Backend der Cursor immer clientseitig ist, weil es keinen Serverprozess gibt.

JET und Multiuserbetrieb ist immer eine wacklige Angelegenheit. Es gibt wie gesagt keinen Serverprozess, sondern nur eine Sperrtabelle bzw. -datei, an die sich die Clients halten oder auch nicht. Der Unterschied ist wie bei im richtigen Lieben bei einem Selbstbedienungs-Buffet: ob sich die Leute ordentlich anstellen und nicht in die Quere kommen, liegt allein an der Erziehung und Disziplin der Leute. Wenn etwa einer meint, sicherheitshalber der Reihe nach beginnend von links aus das ganze Buffet zu sperren und ein anderer genau das gleiche von rechts tut, kann es durchaus crashen.

Datenbeziehungen haben mit dem Problem nichts zu tun.

Nimm die verschachtelten Transaktionen raus und beschränke Dich auf eine. Wenn das Problem damit nicht mehr auftritt, dann gut.

[ebs17]

crasht im Multiuserbetrieb

Das provoziert den Gedanken, bei SingleUser-Betrieb hättest Du bei ansonsten gleicher Konstellation sicher keine Fehler. Ist das so?

Es gibt quais eine Mastertabelle, in die als erstes geschrieben wird und aus der auch der Key für die nachfolgenden 9 erhalten wird. Anschließend werden die dunklen 9 Geistertabelle mit dem FremdKey gefüllt.

Das wären für mich 10 Anfügeabfragen (bei nur Anfügen) nacheinander im Stück. Das ganze wie genannt in einer Transaktion und selbstredend mit einer einzelnen DB-Referenz statt ...zig-mal CurrentDb.
Auch würde ich direkt in Zieltabellen schreiben, mit Geistertabellen nur mit Foreign Keys kann ich nichts anfangen.
Andere (schnellere) Wege lassen vermuten, dass Sperrungen weniger lang dauern und verschiedene Probleme nicht auftauchen.

Daneben sollte man beachten, dass Access problematisch auf Netzwerkunterbrechungen (Internet!) reagiert.

[Milvus]

Was mir nicht gefällt, ist das "Aufspannen" zweier Transaktionen. Grundsätzlich ist es so, daß eine offene Transaktion für den jeweiligen Anwender bereits beim Insert einen neuen Key mitteilt, dieser Key aber noch nicht für andere Anwender sichtbar ist.

Denke der nicht sichtbare Key ist irrelevant, da die anderen User diesen Datensatz nicht interessiert.

Ja, könnte auch eine Transaktion reichen.

Ob Currentdb oder einen Zeitger darauf sollte keinen Unterschied machen (gleiches Objekt)

Dein Hinweis könnte aber in die richtige Richtung gehen. Habe ein erweitertes Debugging implementiert, welches jeden Schritt dokumentiert. Es bleibt in ca. 5% der Fälle immer an der gleichen Stelle hängen.

In dem besagten Schritt passiert Folgendes:

In eine lokale Temporärtabelle werden Daten geschrieben, u.a. auch der Masterschlüssel. Dann wird (nach erfolgreichem Schreiben) dort wiederum der Schlüssel über SELECT abgefragt und in die nächste Tabelle geschrieben.

Der Schritt ist natürlich unsinnig, da der Schlüssel schon vorher in einer Variablen vorhanden ist und nicht noch mal abgefragt werden muss. Das werde ich angehen und ich glaube, dass das das Problem löst.

Aber Fragezeichen:

Es passiert nur selten und hat auch mit Multiuser nicht viel zu tun (Zugriff zu diesem Zeitpunkt nur eine Person).

Ich schätze doch in Richtung ODBC:

Zuerst schreibender dann lesender Zugriff auf die gleiche Tabelle. Schätze da stolpert was!

[Milvus]

Noch ne Info: Es gibt kein Netzwerk zwischen Client und Server, Zugriff erfolgt Remote.

Diese Aussage solltest du genauer ausführen.

Grundsätzlich halte ich es nicht für eine gute Idee, auf ein Access Backend über ODBC zuzugreifen. - Warum nicht direkt JET/Ace?

Mit der Gefahr, dass ich hier Unwissenheit kund tue:

Es handelt sich um verknüpfte Tabellen der n Frontends in ein Backend. Ist das nicht über ODBC verbunden?

Wenn ich z.B. aus ORACLE verknüpfe, dann läuft das über ODBC.

[Wurliwurm]

Es handelt sich um verknüpfte Tabellen der n Frontends in ein Backend. Ist das nicht über ODBC verbunden?

Ja, aber baue diese verschachtelten Transaktionen aus, das ist doch *****, diese Konstruktion. Immer eine Transaktion auf einmal, und so kurz wie möglich. Es wundert mich sowieso, daß auf einer Connection mehr als eine Transaktion gleichzeitig möglich ist.

[PhilS]

Es handelt sich um verknüpfte Tabellen der n Frontends in ein Backend. Ist das nicht über ODBC verbunden?

Nein, ist es nicht. Der Zugriff auf verknüpfte Access-Tabellen läuft immer direkt über die JET-/ACE-Engine.  - Ich hatte nicht daran gedacht, dass es überhaupt nicht möglich ist, Access-Tabellen per ODBC zu verknüpfen.

Was hat es denn aber nun mit der folgenden Aussage auf sich?

Noch ne Info: Es gibt kein Netzwerk zwischen Client und Server, Zugriff erfolgt Remote.

Dies scheint mir nicht ganz unwesentlich, wenn du mit Abstürzen beim Zugriff auf die Backends zu tun hast.

[Lachtaube]

So ein Szenario würde ich nie durch die Access- bzw. Jet-Engine schleusen (tendiert zu Langsamkeit), sondern direkt vom Server ausführen lassen. Schreibe also eine StoredProcedure und verwende entweder ADO oder eine Pass Through Abfrage, um die Prozedur auszuführen.

Luftcode-Schema für eine temporäre Pass Through Abfrage:

Code (vb) [Auswählen] Erweitern

   Dim db As DAO.Database

   Set db = CurrentDb
   With db.CreateQueryDef(vbNullString)
      'Connect Eigenschaft aus bestehender verknüpfter ODBC-Tabelle holen
      .Connect = db.TableDefs("EineVerknuepfteODBCTabelle").Connect
      .SQL = "EXEC Schema.DieProzedur " & _
             kommaseparierte_und_formatierte_Parameter_Werte_Paare
      .ReturnsRecords = False
      .Execute dbFailOnError
   End With
   
   MsgBox "Ich habe fertig!"

Mit ADO wird der Code länger ausfallen - man muss sich aber nicht um Werteformatierungen kümmern.

PS: verschachtelte Transaktionen sind IMO dann sinnvoll, wenn Alternativen beim Scheitern einer der inneren TA vorliegen.

[PhilS]

So ein Szenario würde ich nie durch die Access- bzw. Jet-Engine schleusen (tendiert zu Langsamkeit), sondern direkt vom Server ausführen lassen.

Ich stimme dir im Kern völlig zu. - Nur, wenn ich die etwas widersprüchlichen Informationen richtig entwirrt habe, gibt es in diesem Fall ja gar keinen Backend-Server, sondern nur eine reine, in FE(s) und BE aufgeteilte, Access-Anwendung.

[Milvus]

So ein Szenario würde ich nie durch die Access- bzw. Jet-Engine schleusen (tendiert zu Langsamkeit), sondern direkt vom Server ausführen lassen.

Ich stimme dir im Kern völlig zu. - Nur, wenn ich die etwas widersprüchlichen Informationen richtig entwirrt habe, gibt es in diesem Fall ja gar keinen Backend-Server, sondern nur eine reine, in FE(s) und BE aufgeteilte, Access-Anwendung.

Jepp, das habt Ihr richtig!

Backend und Frontent befinden sich auf einem Windows Server (lediglich in verschiedenen Verzeichnissen). Die Clients loggen sich remote auf dem Server ein. D.h. keinen Datenverkehr zwischen FE und BE über das Netzwerk, findet direkt auf dem Server statt. BE ist eine Access-Datei (kein SQL-Server oder dergleichen), genauer beide Dateien sind mdb-2003, das FE auf mde kompilliert.

Ich habe mittlerweile die Stelle des Crashs identifiziert:

1    UPDATE Tabelle1 SET KEY = x
2    INSERT INTO Tabelle1 SELECT KEY FROM Tabelle1

x befindet sich schon vor 1 in einer Variablen, d.h. der Schritt, einen SELECT auf die Tabelle1 zu machen ist überflüssig, das werde ich korrigieren und berichten.

Aber: Wenn das so ist... Hier finden 2 Aktionen auf die gleiche Tabelle1 statt: 1 schreibend, 2 lesend! Nahezu zeitgleich.

Streng genommen würde ich erwarten, dass der VBA-Code erst dann Schritt 2 ausführt, wenn Schritt 1 erledigt ist. Es sieht für mich erst mal danach aus, als ob die Kommunikation da zwischen DB-Engine und VB nicht so richtig sauber läuft.

Ich baue erst mal einen Verzögerer rein, rein aus Interesse.

[ebs17]

Ich habe mittlerweile die Stelle des Crashs identifiziert:

1    UPDATE Tabelle1 SET KEY = x
2    INSERT INTO Tabelle1 SELECT KEY FROM Tabelle1

Ich sinniere über den Inhalt der Anweisungen: Alle Schlüssel werden auf einen Wert festgelegt + vorhandene Schlüssel werden pur entsprechend ihrer vorhandenen Anzahl vervielfältigt. Ergebnislos.

Was fängt man mit nur einem Schlüssel im Datensatz an?
Ein paar Zahlen kann man sich auch aus einer Hilfstabelle holen, ohne alle Verrenkungen.

[Milvus]

richtig, sorry

Menschenskinder, Vertipper:

1    UPDATE Tabelle1 SET KEY = x
2    INSERT INTO Tabelle2 SELECT KEY FROM Tabelle1

[ebs17]

Was fängt man mit nur einem Schlüssel im Datensatz an?

Wenn man das in eine andere Tabelle schreibt statt in die eigene, ändert sich etwas entscheidend an dieser merkwürdigen Konstellation?

[Milvus]

Neben dem Schlüssen werden natürlich auch weitere Felder gesetzt,
hab ich der Einfachheit hier weg gelassen, konzentriere mich auf die Technik.

OK, wie versprochen Rückmeldung:

Ein Verzögerer im Code hat nix gebracht!

Das bedeutet für mich, der Fehler könnte innerhalb eines Executes selbst ausgelöst werden.
Ich kanns mir bis jetzt nicht erklären, habe aber nun Folgendes getan:

Alle db.Execute über SQL abgelöst durch Recordset.Open, dann .AddNew.. Update bzw. .Edit.. Update.
Das Ganze Zeugs in nur eine Transaktion gepackt.

Seit dem habe ich keine Abstürze mehr beobachtet, was ja das Ziel war.

Nun hat sich das Verhalten der Datenbank im Multiuserbetrieb allerdings geändert. Das scheint mit dem Übergang auf die Recordsets zusammen zu hängen.

Beschreibung:

Vorher war es möglich, dass mehrere User gleichzeitig innerhalb ihrer Transaktion in Tabellen schreiben konnten (Ich schreibe die Einzelschritte nebenbei in TextFile mit). Die DB.Executes waren mit dbFailOnError ausgestattet.

Durch die neuen Recordsets ist es aber so, dass immer nur ein User schreiben kann und zwar so lange, bis die Transaktion beendet ist. Erst dann kann ein nächster rein.

Eine komplette Transaktion kann bei mir bis zu 5 Sekunden dauern. So dass es nun öfters zu fehlerbehandelten Stopps kommt. Das würde ich gerne noch verbessern, Ziel: Gleichzeitiges Schreiben. Eine echte Datensatzkonkurrenz gibt es nicht.

Ich arbeite mit DAO und habe schon auf eine optimistische Sperre gesetzt. Bring nix, die Transaktion wirkt wie ein Tor, durch das niemand durch darf, bzw. (vermute ich, das initialisieren eine Recordsets haut ne Sperre rein, das war vorher ja nicht)

Gibts da noch Schrauben?

Eine Alternative wäre evtl. ein Batch-Update mittels ADO?

Noch mal, was gemacht werden soll: INSERT/AddNew in 10 Tabellen, Update in 2 Tabellen (das alles ganz oder gar nicht)