Sperrtabelle greift nicht

[Milvus]

Hallo zusammen,

brauche wieder mal Eure Erfahrung.

Vor dem Schreiben einer kompletten Kaskade (in >10 Tabellen) wird eine Sperre geprüft bzw. gesetzt.
Dafür habe ich eine Sperrtabelle, die aus nur einer Zeile besteht.

In diese wird mittels folgendem Konstrukt (Kurzfassung) ein Eintrag gesetzt:

UPDATE TBLSperre SET Locked = true WHERE locked = false

Anschließend wird mittels folgendem Code geprüft, ob das Teil erfolgreich war:

    Set con = CurrentProject.Connection
    con.Execute sSQL, lngRecAffected

    If lngRecAffected = 0 Then
        If sUser = GetUserSemaphor(semaphor) Then
            bSperre = False
        Else
            bSperre = True
        End If
    Else
        bSperre = False
    End If
   
    If bSperre = True Then
        If lngCounter < 15 Then
            Wait 1
            pp semaphor
        Else
            MsgBox "Gesperrt durch User xxx, OK = Neuer Versuch!", vbInformation, "Sperre Aktiv"
            lngCounter = 0
            pp semaphor
        End If
    End If


Zur Erklärung, ist ja nur ein Codeschnipsel: Die GEsamte Przedur wird 15 mal in 1 Sekundenabstand rekursiv aufgerufen und bei keiem Erfolg der User informiert. Das ist erfoglreich getestet worden.

Ich habe das Teil nun aber erwischt, dass die Sperre nicht greift. Kennt Ihr Euch damit aus, ist das überhaupt möglich, gibt es ein infitinisimal kleines Zeitfenster, in dem das passieren kann?


Danke

[PhilS]

Vor dem Schreiben einer kompletten Kaskade (in >10 Tabellen) wird eine Sperre geprüft bzw. gesetzt.
Dafür habe ich eine Sperrtabelle, die aus nur einer Zeile besteht.
[...]

Ich habe das Teil nun aber erwischt, dass die Sperre nicht greift. Kennt Ihr Euch damit aus, ist das überhaupt möglich, gibt es ein infitinisimal kleines Zeitfenster, in dem das passieren kann?

Ich werde aus deinem Code nicht schlau. Dort fehlen elementare Teile, ohne die man das Ganze nicht beurteilen kann.

Mir ist schon mal unklar, was "Schreiben einer kompletten Kaskade (in >10 Tabellen)" genau bedeuten soll. - Die Notwendigkeit für das was ich mir darunter vorstelle, riecht schon nach einem Problem mit deinem DB-Design.

Warum verwendest du keine Transaktion dafür?

Wie weißt du welcher User der Eigentümer der Sperre ist? - Ist das bei der Vereinfachung unter den Tisch gefallen?

[Wurliwurm]

Bei einem professionellen DBMS wie MS SQL darf es kein infinitesimales Zeitfenster geben, wo "Sachen durchrutschen". So ein Setzen von Sperren gehört sicherlich zu den komplizierten Themen und gleichzeitig kann man schnell irgendetwas basteln, wenn man ein paar Brocken Code und SQL kann.

Verwendest Du Transaktionen? Ist Dir klar, daß ein Setzen des Sperrflags für einen Dritten erst bei einer committeten Transaktion sichtbar ist? Die Verwendung von Fachbegriffen wie "Semaphore" und "rekursiv" steht in Kontrast zu der saloppen Darstellung des Problems.

[Milvus]

Vor dem Schreiben einer kompletten Kaskade (in >10 Tabellen) wird eine Sperre geprüft bzw. gesetzt.
Dafür habe ich eine Sperrtabelle, die aus nur einer Zeile besteht.
[...]

Ich habe das Teil nun aber erwischt, dass die Sperre nicht greift. Kennt Ihr Euch damit aus, ist das überhaupt möglich, gibt es ein infitinisimal kleines Zeitfenster, in dem das passieren kann?

Ich werde aus deinem Code nicht schlau. Dort fehlen elementare Teile, ohne die man das Ganze nicht beurteilen kann.

Mir ist schon mal unklar, was "Schreiben einer kompletten Kaskade (in >10 Tabellen)" genau bedeuten soll. - Die Notwendigkeit für das was ich mir darunter vorstelle, riecht schon nach einem Problem mit deinem DB-Design.

Warum verwendest du keine Transaktion dafür?

Wie weißt du welcher User der Eigentümer der Sperre ist? - Ist das bei der Vereinfachung unter den Tisch gefallen?

Hallo PhilS,

ja der User ist auch relevant, als dass er sich selbst nicht sperren können darf bzw. seine eigene Sperre wieder auflöst. Wollte das hier so einfach wie möglich posten, ist glaube ich auch weniger relevant bezüglich der Frage.

Das DB-Desing sag ich nix zu, stammt nicht von mir. Ob man alle Tabellen befüllen muss, will ich auch nicht angehen, stammt ebenso wenig von meinem Kopf.

Folgende Fakten:

Für den Gesamtprozess waren ursprünglich Sperrtabellen genutzt worden. Die habe ich dann durch eine Transaktion abgelöst. Damit war plötzlich zeitgleiches Schreiben (auch geschachtelt) möglich. Das hat eine Zeit funktioniert, dann gab es aber den Wunsch, dass ID's innerhalb eines Buchungsprozesses durchlaufend sind. Das kann ich mit Transaktionen alleine so aber nicht mehr lösen. Zur Info:

Loop Start
Trans auf: Zunächst wird ein Insert mit Abfangen der ID eines Masterdatensatzes ausgeführt, die ID dann in den Slave-Tabellen weiter verwendet (INSERT und UPDATE)
Trans zu
Loop ende

D.h.: Technisch gesehen hat die Transaktion das tatsälich gelöst, ja! Das hab ich sauber ausprogrammiert, beim Fehler kriegen die Nutzer nach Rollback eine Medlung mit rekursivem Neuversuch. Es gab dabei Feststellungen, das ab und an bei gleichzeitigem Schreiben eine Sperrmeldung vom System generiert wurde. Da hat eine Transaktion eine andere ausgesperrt.

An diesem Punkt gestehe ich (hat allerdings nix mit der Urfrage zu tun), die DB noch nicht ganz verstanden zu haben. Das zeitgleiche Schreiben in die Master-Tabelle scheint möglich zu sein, die Sperrkonflikte scheinen wo anders aufzutreten. Ich vermute die UPDATEs. Meine Erklärung dafür: Sperren beim INSERT kann nicht zum Konflikt führen, dader Datensatz für einen anderen User ja noch gar nicht existiert, warum sollte da also eine Sperre greifen. Bei einem UPDATE ist das natürlich anders, da könnte es sich anders verhalten und somir würde es auch Sinn ergeben, wenn die Maschine da generell anders sperrt.

Wie auch immer, bin zwar höchst interessiert, wie das Teil da sperrt, nehme alle Infos dankbar auf, aber am Problem löst es nix. D.h., zurzeit verwende ich eine Sperrtabelle und dann die Transatkion auf, Doin, Trans zu Sperre raus. Die Sperre setze ich vor den Loop.

Meine ursprüngliche Frage war, ob es ein infinitisimal kleines Zeitfenster geben, kann, in dem das UPDATE für die Sperrtabelle - kombiniert mit der WHERE condintion - so nicht greifen kann. Aus letzter Antwort entnehme ich allerdings, dass dies nicht sein sollte. Das reicht mir schon.

[ebs17]

Schreiben einer kompletten Kaskade (in >10 Tabellen)

Das wäre, wenn man es mit Datenbanklogik ausführt, jeweils eine Anfüge- und eine Aktualisierungsabfrage pro Tabelle, also z.B. hier 20 Stück, die nur das tun sollten, was nötig ist. Und diese kann man wie genannt in einer Transaktion zusammenfassen => einfacher Durchlauf, keine Rekursion.

Die Erzählungen über das Sperren habe ich nicht verstanden Sie erscheinen mir auch eher als Selbstbeschäftigung und Zeitvertreib.

Man kann sich ja gerne über gewisse Techniken unterhalten. Wenn man aber eine Aufgabe zu lösen hat, sollte man sich mit eben dieser eigentlichen Aufgabe beschäftigen (Kaskade = unbekanntes Objekt) und weniger mit einem Lösungsweg, den sich einer ausgedacht hat, vermutlich an der Datenbank vorbei, der nun offensichtlich auch nicht ausreichend funktioniert.

[markusxy]

Meine ursprüngliche Frage war, ob es ein infinitisimal kleines Zeitfenster geben, kann, in dem das UPDATE für die Sperrtabelle - kombiniert mit der WHERE condintion - so nicht greifen kann.

Hast du es denn getestet?
Bei einem Access Backend werden Änderungen gemäß meiner Erfahrung (angeblich aus Performance Gründen) nicht sofort ins Backend geschrieben.
Bei ADO ist der Delay höher wie bei DAO.
Transaktionen erhöhen die Geschwindigkeit, dennoch ist die Verzögerung klar wahrnehmbar.
Du solltest dich aber selbst mit dem Thema beschäftigen, dann bekommst du auch die Gewissheit unter welchen Umständen "sofort" geschrieben wird. 

[Milvus]

Hi Markus,

getestet ja. Das Sperren funktioniert auch, konnte keine Missachtung der Sperre mehr beobachten. Ich schreibe dazu ein Protokoll weg!

Dein Einwurf lässt mich aber schon ein bisschen stutzen. Könnte es sein, dass der Code weiter läuft, ohne dass das SQL ausgeführt wurde? Was wäre dann mit der Long-Variablen RecordsAffected?

Wenn das nicht sicher ist, wäre das natürlich nicht der richtige Ansatz für unsere Fragestellung.

Was ich nun aber beobachtet habe, ist dass das Auflösen der Sperre -zwar sehr selten aber ist so-  nicht funktioniert. Dadurch bleiben alle anderen Nutzer blockiert (was ja gut funktioniert  ).

Noch mal, was passiert:

Über ADO.connection SQL abfeuern

Setzen der Sperre über UDATE mit WHERE Condition
Lösen der Sperre über UPDATE ohne WHERE Condition

Ich habe die Ursache noch nicht gefunden. Ich vermute, dass durch die Setze Sperre SET-Versuche (habe berenzte Loops im Sekundentakt gebebaut) die Zeile (es gibt nur eine) kurzzeitig von der Datenbank her gesperrt ist, so dass das Lösen dann nicht greift. Auch wenn es so ist, komisch!!! Denn durch der WHERE Condition wird ja keine Zeile zurück geliefert.

Ich arbeite eine Protokollierung ein und beobachte das.

Wie sind Eure Erfahrungen mit den Sperrtabellen, wie habt Ihr das gelöst?
Ich schau mir mal den Link genauer an, glaube hier wurde schon was gepostet.

Ich glaube eine gute Variante könnte das sein:
Versetzten einer Sperrtabelle in den Edit Modus und diesen so lange aktiv lassen bis alles durch ist, dann .close oder .update (müsste egal sein). Versucht nun ein anderer in den Edit zu setzen, scheitert er.

Das müsste sicherer sein, oder?

[Wurliwurm]

Ich glaube eine gute Variante könnte das sein:
Versetzten einer Sperrtabelle in den Edit Modus und diesen so lange aktiv lassen bis alles durch ist, dann .close oder .update (müsste egal sein). Versucht nun ein anderer in den Edit zu setzen, scheitert er.

Nein. 

Ich habe weiter oben schon beschrieben, daß das Setzen der Sperre in eine extra Transaktion gehört, sonst sehen die anderen Anwender die Sperre nicht. Die logische Sperrtabelle gibt es ja gerade deswegen, damit man nicht die Datenbanksperren (auf Blockebene) braucht. Jetzt doch die Datenbanksperren zu verwenden, um eine nicht richtig funktionierende logische Sperrung  geradzubiegen, ist Pfusch.

Also
T1 Begin
UPDATE SPERRE SET GESPERRT = TRUE
T1 Commit

T2 Begin
INSERT INTO TABELLE1
INSERT INTO TABELLE2
INSERT INTO TABELLE3
usw.
UPDATE SPERRE SET GESPERRT = FALSE
T2 Commit

Außerdem habe ich kein Vertrauen, daß es einem Access-Backend überhaupt wirklich funktioniert, wegen der Architektur mit den lokalen Jet-Xlient-dlls und der ldb-Sperrdatei Bei einem DBMS hingegen muß das funktionieren. Weiter oben wurde ja angesprochen, daß Updates nicht sofort auf den Sekundärspeicher geschrieben werden. Das ist bei DBMS zwar auch so, hier ist das Rückschreiben auf Festplatte sogar unabhängig von den Transaktionen, und trotzdem sind die Transaktionen serialisierbar. Warum das so ist, bitte bei Interesse googeln.

[markusxy]

@Milvus,
die Frage ist doch warum du deine Zeit mit einem Access Backend vergeuden willst, wo es doch viel besser Möglichkeiten gibt.

[Milvus]

Nein. 

Ich habe weiter oben schon beschrieben, daß das Setzen der Sperre in eine extra Transaktion gehört, sonst sehen die anderen Anwender die Sperre nicht.

Scheint für mich der schnellste Weg zur Abhilfe zu sein.
Was genau verusacht die Transaktion? Vermute, es geht hier um die Rückmeldung des Backends, dass die Sperre tatsächlich gesetzt ist, was ohne Transaktion nicht sicher gestellt ist. Also mit einfachen Worten: Ohne Transaktion rennt der Code weiter, egal was die DB da gemacht hat (Glücksache). Mit Transaktion gibt es entweder einen abfangbaren Fehler oder der Code läuft druch, wenns geklaptt hat!?

Noch mal mit anderen Worten: Die Sperrtabelle funktioniert nur sicher mit einer Transaktion

[Milvus]

@Milvus,
die Frage ist doch warum du deine Zeit mit einem Access Backend vergeuden willst, wo es doch viel besser Möglichkeiten gibt.

Richtige Frage!

Bin hier nicht alleine am Wurschteln und Umstellung => Großes Thema
Würde bedeuten: Mehrere Monate keine anderen Prios. Da wirds wohl erst noch schlimmer werden müssen, was bei Wachstum zu erwarten ist.

[Wurliwurm]

Nein. 

Ich habe weiter oben schon beschrieben, daß das Setzen der Sperre in eine extra Transaktion gehört, sonst sehen die anderen Anwender die Sperre nicht.

Scheint für mich der schnellste Weg zur Abhilfe zu sein.
Was genau verusacht die Transaktion? Vermute, es geht hier um die Rückmeldung des Backends, dass die Sperre tatsächlich gesetzt ist, was ohne Transaktion nicht sicher gestellt ist. Also mit einfachen Worten: Ohne Transaktion rennt der Code weiter, egal was die DB da gemacht hat (Glücksache). Mit Transaktion gibt es entweder einen abfangbaren Fehler oder der Code läuft druch, wenns geklaptt hat!?

Noch mal mit anderen Worten: Die Sperrtabelle funktioniert nur sicher mit einer Transaktion

Ich will es jetzt nicht zu ausführlich machen, weil es schon tausendfach im Netz steht. Transaktionen gehören zum Grundwissen im Datenbankbereich. Google mit "ACID Datenbank" und schaue es Dir genau an, Du wirst es noch oft im Leben brauchen.

Soviel: Es geht um die Isolation. Wenn ein User ein Update eines Datensatzes macht, sieht normalerweise ein anderer User diese Änderung erst, wenn die Transaktion committed, "veröffentlicht", ist. Wenn also ein Update auf die Sperrtabelle gemacht wird, sieht bei einer "echten" Datenbank der andere Lesende vor dem Commit die Änderung noch nicht, jedoch gibt es u.U. eine Lesesperre, eine technische Sperre auf den Datensatz. Bei einer technischen Sperre funktioniert es so, daß der Lesende bei dem Datensatz "ansteht" und wartet.

Bei einem DBMS kann man das sehr anschaulich zeigen: User A macht einen Update auf Tabelle T1 und macht kein Commit. User B schickt daraufhin einen Select auf die Tabelle und nichts passiert. Es sieht für B so aus, als ob die Datenbank hängt. Jetzt schreibt man im anderen Konsolenfenster für User A den Commit, drückt auf Enter und *peng* kommt im zweiten Fenster für User B das Ergebnis der Abfrage.

Für mich stellt sich das so dar, als ob Du eine wirkliche logische Sperre konzipiert hast (1), aber bei der technischen Umsetzung ohne Transaktion das Abfragen des Sperrflags ersetzt durch das "Anstehen" vor dem uncommiteten Datensatz.

Disclaimer: Wie gesagt, bei Access-Backends im Mehruserzugriff gibt es keine Isolation auf dem Level wie bei professionellen Datenbanken.

(1) Die logischen Sperren sind mir als SAP-ler bestens vertraut. Im SAP gibt es immer eine zentrale Sperrtabelle, die jedesmal abgefragt wird und wo Sperreinträge vor dem Update geschrieben werden. Eine Buchung im SAP (Stammdaten, Auftrag, Rechnung u.v.m.) führt zu Updates in einer Unzahl von Tabellen und wenn man die logischen Sperren nicht hätte, würde das Chaos bedeuten.

[Milvus]

@WurliWurm

Danke!

Ich kenne zumindest ORACLE als Backend, da ist ja jede Aktion mit einer Transaktion gekoppelt, ist Standard.

Schätze, ich begreife so langsam das Dillemma. In Access muss ich etwas mit der Hand coden, das in anderen DB-Systemen DB-Standard ist. Daher auch die Empfehlung, sich nicht auf die DB-Sperre von Access zu verlassen.

, Schätze, wir kommen mit Access an Grenzen

Ich plane folgende Änderung:

Auflösen der Sperrtabelle in die jeweiligen Bereiche (die sind zurzeit zusammengefasst, z.B. Rechnung, Auftragsanlage, usw.). D.h., jeder Bereich kriegt erst mal seine eigene Tabelle, die exakt einen Datensatz enthält.

Im Code wird dann zunächst mit Transaktion ein Sperrflag gesetzt. Erst nach erfolgreichem Commit wird die eigentliche Aktion gestartet und - ebenfalls mittels Transaktion - das Sperrflag wieder gelöst.

Auflösung auf mehrere Sperrtabellen, weil der Verkehr mittlerweile so groß ist, dass, wenn ein Datensatz gerade gesperrt ist, die Sperrauflösung verhindert wird (weil ja voneinander losgelöste Sachverhalte auf die gleiche Tabelle und den gleichen Datensatz zugreifen, selten aber gibt es).

Nach meiner Vorstellung sollte es da zukünftig keine Konflikte mehr geben und die nächsten Jahre gesichert sein, optimistische Grundhaltung

[Wurliwurm]

Schätze, ich begreife so langsam das Dillemma. In Access muss ich etwas mit der Hand coden, das in anderen DB-Systemen DB-Standard ist.

Eine logische Sperrtabelle gibt es auch im Oracle nicht, das ist etwas, was man immer in der Applikation bauen muß. Und auf der anderen Seite hat man gar keine Chance, beim Access-Backend Isolation und Serialisierbarkeit hinzubekommen, weil das auf einer tieferen Ebene (also unterhalb von DAO/ADO/ODBC etc...) stattfindet.

jeder Bereich kriegt erst mal seine eigene Tabelle, die exakt einen Datensatz enthält.

Das ist schon mal gar keine gute Lösung. Damit blockierst Du doch unnötig. Es reicht eine Sperrtabelle für die ganze Anwendung. Die Felder sind etwa:

SPERROBJEKT *Key*
SCHLUESSEL *Key*
GESPERRTVON
SPERRDATUM

Also etwa
AUFTRAG|0815|SCHULZE|28.04.2019 10:25:15
RECHNUNG|4711|MUELLER|28.04.2019 07:30:57

[Milvus]

Dass das rein von der Auflösung her mit nur einer Tabelle geht, ist mir bewusst. Ich normiere leidenschaftlich gerne.

Ich habe halt nun schon auch Schreibkonflikte in der Sperrtabelle selbst beobachtet. Das wollte ich mit der Aufsplittung verhindern, da - je nach Bereich - keiner mehr in die Tabelle schreibt, bis der zuvorige Nutzer das wieder frei gegeben hat. Bei mehreren Bereichen in einer Tabelle wird immer geschrieben und da müsste ich verstehen, wie die Datenbank sperrt. Das Design von der jetzigen Sperrtabelle ist falsch bzw. die ist nicht normiert. Da wird nicht mit Schlüsseln gearbeitet. Für jeden Bereich gibt es eine Spalte. Das führt dazu, dass in der Tabelle immer nur ein Datensatz ist auf den alle zugreifen.

Ich sag mal so: Wenn ich es gezielt hinbekomme, dass die Datenbank immer nur die Zeile sperrt, die versucht wird zu bearbeiten (wie mache ich das oder ist das Standard?), dann setze ich eine normierte Sperrtabelle auf, in der jeder Bereich seine eigene Zeile hat. Damit wären die Spatzen gefangen.