Optimistische Sperren und Abhängigkeiten zwischen Tabellen...

[asc]

Nachdem ich bislang überwiegend an Anwendungen mitgearbeitet habe, die pessimistische Sperrkonzepte verwendet haben, wollte ich mich nun intensiver mit optimistischen Sperrkonzepten vertraut machen (gerade auch weil einige DB-Schnittstellen inzwischen darauf ausgelegt sind).

Wie man eine Konfliktprüfung bei einem einzelnen Datensatz hinbekommt ist mit auch klar (u.a. die Variante mit einer Versionierung, z.B. mittels rowversion in SQL Server 2008 oder über Trigger).

In der Regel sind Daten aber meist über mehrere Tabellen verteilt (zumindest in den Anwendungen in denen ich bislang mitgearbeitet habe, ist das eher der Standardfall für die Bearbeitung, als die Ausnahme), beispielsweise das in einer Bearbeitenmaske auch gleich noch Zuordnungen zu anderen Datensätzen gepflegt werden.


In gewissen Fällen mag es unproblematisch sein, sofern wirklich nur unterschiedliche Datensätze parallel bearbeitet werden, in anderen muss sichergestellt sein, das keine Änderung auch in weiteren Datensätzen erfolgt ist.


1. Beispiel: In einer Maske für die Pflege von Personen werden auch noch seine Funktionen gepflegt. Wie nachfolgend grob beschrieben:

TblPerson (Id, Name, Nachname...)
TblPersonFunktion (IdPerson, Beschreibung...)

In diesem Fall kann man wahrscheinlich damit leben, das gewisse Bearbeitungsüberschneidungen möglich sind (solange nur unterschiedliche Datensätze angepackt werden).


2. Beispiel: Es gibt aber vielleicht auch Abhängigkeiten in denen die Bearbeitung problematischer ist, z.B. Termine und Terminteilnehmer, wo eine terminliche Überlappung wenn nur mit Rückfrage möglich sein sollte:

TblTermin (Id, Beginn, Ende...)
TblTerminRessource (IdTermin, IdRessource...)

Wie setzt man hier am besten eine optimistische Sperre um, da die Abhängigkeiten zwischen Datensätzen hier viel "freier" sind, und Konflikte auch durch neue Datensätze etc. entstehen können.


Meine bisherige Überlegung, wo ich mir aber nicht sicher bin, ob ich hier etwas übersehe, ist folgende:

a) Jeder Transaktion eine eindeutige Id zuzuweisen (Falls die DB dies direkt unterstützt über die DB-Features, sonst über einen neuen Datensatz in einer Tabelle), nennen wir dies im Folgenden TransaktionsId. Sowie einen Zeitstempel zu merken (Beginn der Transaktion).
b) Bei jedem Datensatz die TransaktionsId und einen Zeitstempel zu speichern.
c) Am Schluss ein Select über die Termine und Terminressourcen zu machen, die den Zeitraum des bearbeiteten Termines schneiden, und die seit dem Transaktionsbeginn von einer anderen als der aktuellen Transaktion geändert wurden. Wenn dies ein Treffer ergibt, wird die Transaktion zurückgesetzt.

Wäre dies ausreichend, übersehe ich hier etwas, oder gibt es bessere Vorschläge?

[dimitri]

Datenbanksperren sind dazu da, die sog. Lost Updates zu verhindern. D.h. zwei Sessions ändern die gleichen Datensätze, wobei eine Session schneller ist und die andere Änderung überschreibt, ohne dass der User die Möglichkeit hatte diesen Konflikt manuell aufzulösen. Die optimistische Methode verzichtet dabei darauf die Sätze die ganze Zeit zu sperren.

Du wirst mit solchen Mechanismen alleine keine fachlichen Vorgaben umsetzen können. Wenn eine Person z.B. nicht gleichzeitig die Rolle Admin und User haben kann, die beiden Rollen aber von unterschiedlichen Usern zur (fast) gleichen Zeit zugewiesen werden, muss man sich für diesen Fall etwas spezifisches überlegen.

Für Dein beispiel 2 würde man die Termine eintragen lassen und den User über die Anwendung anschließend darauf hinweisen, dass sich zwischenzeitlich auch ein weiterer Termin ergeben hat, welcher von XY um sowunsoviel uhr eingetragen wurde. Je nach fachlicher Anforderung kann dieser Hinweis dann mehr oder weniger penetrant sein.
Über Datenbankmittel hier einzugreifen wäre deutlich komplizierter, da müsste man das Datenbankmodell so entwerfen, dass man einen Unique Constraint auf die vergebene Kombination von Tage, User Uhrzeit legen kann. Damit würde man erreichen, dass nur eine Session für den Zeitraum X bis Y etwas eintragen kann, alle anderen bekommen eine Unique Constraint Verletzung zurück, was die Anwendung dann entsprechend als Konflikt an den User zurückgibt.

Dim

[asc]

Datenbanksperren sind dazu da, die sog. Lost Updates zu verhindern.

Mir ist das bewusst, nur sind manche Frameworks von vorne herein auf optimistische Sperren ausgelegt (Auch wenn in der Regel irgendwie dann doch noch pessimistische Sperren unterstützt werden). Und da wollte ich einfach wissen ob es eine Möglichkeit gibt mein Problem auch mit optimistischen Sperren zu lösen...

Für Dein beispiel 2 würde man die Termine eintragen lassen und den User über die Anwendung anschließend darauf hinweisen, dass sich zwischenzeitlich auch ein weiterer Termin ergeben hat, welcher von XY um sowunsoviel uhr eingetragen wurde. Je nach fachlicher Anforderung kann dieser Hinweis dann mehr oder weniger penetrant sein.

Nach unserer fachlichen Anforderung muss ich dies verhindern, und werde hier wohl nicht um Sperren herum kommen (Wir haben hier "harte" und "weiche" Konflikte, und die sind auch noch von den Benutzerrechten im Programm abhängig, wenn eine Terminüberlappung in diesem Fall ein harter Konflikt ist, darf dieser gar nicht erst physikalisch geschrieben werden).

[dimitri]

(Wir haben hier "harte" und "weiche" Konflikte, und die sind auch noch von den Benutzerrechten im Programm abhängig, wenn eine Terminüberlappung in diesem Fall ein harter Konflikt ist, darf dieser gar nicht erst physikalisch geschrieben werden).

Dann kommst Du um pessimistische Sperren aber nicht herum. Damit wird natürlich auch der Zugriff auf die Ressourcen (=Tabellen) serialisiert. Evtl. könnte man das mit Hilfstabellen besser realisieren, bei dem ein Programm quasi vorher eintragen muss was es tun möchte. Der Zugriff auf diese Tabelle wird serialisiert, aber nur für den Zeitraum des eintragens und dann wieder freigegeben. Andere Programme pürfen das dann und können aber, sofern kein Konflikt vorliegt, auf die Tabellen zugreifen. Ist der Termin erfolgreich eingetragen, wird dieser Merker wieder entfernt.
Ansonsten hast natürlich das berühmte Mittagspausenproblem, bei dem ein User die komplette Arbeit der anderen blockieren kann (und es auch tun wird).

Dim

[asc]

Ansonsten hast natürlich das berühmte Mittagspausenproblem, bei dem ein User die komplette Arbeit der anderen blockieren kann (und es auch tun wird).

Bisher hat die Anwendung wie folgt gearbeitet: Beim Schreiben eine komplette Tabellensperre gesetzt (Holzhammermethode, klappte aber bedingt durch einen bislang eher kleinen Anwenderkreis ganz gut) geprüft ob sich etwas geändert hat, und erst dann geschrieben.

Selbst wenn ich diese Tabellensperre entfernen will, wird der Ablauf ein Ähnlicher sein (Sprich: Trotz Pessimistischer Sperren wird nur während des Schreibvorganges gesperrt). Ich kenne es eigentlich nur so das wenn man mit Sperren arbeitet, diese nur während des Schreibens/Überprüfen gesetzt werden.

[dimitri]

Ich kenne es eigentlich nur so das wenn man mit Sperren arbeitet, diese nur während des Schreibens/Überprüfen gesetzt werden.

Wenn Du sicherstellen willst, dass der oder die Datensätze nicht gleichzeitig von einer anderen Transaktion bearbeitet werden ist das richtig. Wenn ich Dich richtig verstanden habe, geht es aber auch darum überschneidungen mit anderen zu verhindern. Beispiel:
User1 ändert den bestehenden Termin T1 für Person X und legt ihn 2 Stunden nach vorne.
User 2 legt einen weiteren Termin für Person X an, der sich aber mit der noch nicht comitteten (und damit noch nicht sichtbaren) Terminverschiebung übeschneiden würde.

Wenn So etwas auftreten kann und Du das abfangen musst, dann brauchst Du entweder einen kompletten Tabellenlock oder aber eine Art Metalocktabelle, in der sich die Anwendungen eintrage und kundtun was sie ändern möchten. Eine andere AW prüft das vorher und bricht dann ggf. den Vorgang ab.

Dim

[asc]

Wenn So etwas auftreten kann und Du das abfangen musst, dann brauchst Du entweder einen kompletten Tabellenlock oder aber eine Art Metalocktabelle, in der sich die Anwendungen eintrage und kundtun was sie ändern möchten. Eine andere AW prüft das vorher und bricht dann ggf. den Vorgang ab.

Vermutlich wird das auch eine Art von Metalocktabelle, mit einem Eintrag pro Tag. Vor einer Änderung setze ich dann die entsprechenden Tage in den Editiermodus (Was in der Regel maximal 2 sind), prüfe anschließend ob sich etwas in dem Zeitraum geändert hat (um nicht das Problem mit den "Mittagspausen" zu haben) und schreibe anschließend...

So kenne ich das auch bislang von Anwendungen (mit pessimistischen Sperren): Daten werden gelesen, Verbindung "getrennt", beim Speichern wird kurz auf Änderung geprüft und dann geschrieben. Das eine Sperre vom Beginn des Lesens, bis zum Ende des Schreibens gesetzt ist, kenne ich eigentlich nicht (Alleine schon weil es der Regel widerspricht das Sperren so kurz wie möglich gesetzt sein können).

[dimitri]

So kenne ich das auch bislang von Anwendungen (mit pessimistischen Sperren): Daten werden gelesen, Verbindung "getrennt", beim Speichern wird kurz auf Änderung geprüft und dann geschrieben.

Das ist optimistisches Locking. Beim pessimistischen Locking wird beim Lesen gelockt und der Lock erst mit Ende der Transaktion wieder entfernt.

Dim

[asc]

Das ist optimistisches Locking. Beim pessimistischen Locking wird beim Lesen gelockt und der Lock erst mit Ende der Transaktion wieder entfernt.

Das sehe ich etwas anders (Zumindest kann man eine Kombination beider Verfahren nutzen, und nur so kenne ich dies aus der Praxis - Ich kenne keine Anwendung [zumindest keine, an der ich mitgearbeitet habe], die z.B. durch das Öffnen eines Fensters bis zum Speichern einen Datensatz blockiert. Und wir haben bislang nur mit pessimistischen Sperren [Bezogen auf den Schreibvorgang] gearbeitet, im Schreibvorgang wird dann aber dennoch die Version des Datensatzes geprüft).

[dimitri]

Das sehe ich etwas anders

Das mag sein, aber Deine Beschreibung ist genau das, was beim optimistischen Locking gemacht wird:
- Jeder Datensatz hat eine Versionsnummer oder einen Timestamp
- Beim Lesen merkt sich die AW dieses Kennzeichen
- Beim Zurückschreiben wird die Version in der DB mit der gemerkten verglichen

Das ist optimistisches Locking. Das die DB selbst einen Datensatz für Veränderungen aus anderen Transaktionen heraus sperrt wenn dieser per DML geändert wird, hat damit nichts zu tun. Ich kann mir auch denn Aufwand sparen und einfach so drüberschreiben. Auch dann ist der Datensatz von der DB bis zum Ende der Transaktion gelockt ich habe allerdings das (fachliche) Problem der Lost Updates.

Pessimistisches Locking würde so ablaufen:
Ich lese den Datensatz und sperre ihn gleichzeitig (in Oracle z.B. mit der Option SELECT ... FROM ... FOR UPDATE). Damit gehört er mir. Entsperrt wird er erst, wenn die Transaktion mit COMMIT oder ROLLBACK beendet wird.

Dim

[asc]

Das mag sein, aber Deine Beschreibung ist genau das, was beim optimistischen Locking gemacht wird:
- Jeder Datensatz hat eine Versionsnummer oder einen Timestamp
- Beim Lesen merkt sich die AW dieses Kennzeichen
- Beim Zurückschreiben wird die Version in der DB mit der gemerkten verglichen

Nur das es hier noch zusätzliche Abhängigkeiten zur Neuanlage etc. gibt (Neu eingefügte oder zwischenzeitlich veränderte Datensätze können [müssen aber nicht] eine Kollision verursachen). Ich möchte nur wenigstens von der aktuellen Form (komplettes Sperren einer Tabelle) wegkommen.

Das ist optimistisches Locking. Das die DB selbst einen Datensatz für Veränderungen aus anderen Transaktionen heraus sperrt...

Wie gesagt, mit dem Sperren dieses Datensatzes ist es aber nicht getan. Ich werde wohl die schon genannte Tabelle einführen, in der pro Tag ein Datensatz steht, und diesen Sperren, dann prüfen ob zwischenzeitlich etwas neues im Zeitraum eingefügt, oder bestehendes geändert wurde, und dann speichern.

Eine rein optimistische Sperre wäre noch besser, nur weiß zumindest ich nicht, wie ich dann die Abhängigkeiten auch bezogen auf neue Datensätze etc. auflöse (In der Hoffnung da eine Lösung - falls vorhanden - zu bekommen, hatte ich diesen Thread ja aufgemacht).

In jeder vorherigen Firma hatten wir einen (oder mehrere) Datenbankadministratoren, die sich solcher Probleme angenommen hätten, nur ist meine aktuelle so klein, das ich neben der Anwendungsentwicklung auch für die Migration von Access auf SQL Server (usw.) verantwortlich bin, und zumindest einen Teil der Altlasten (gerade auch weil unsere Kunden größer werden, und immer mehr Clients an der Datenbank hängen) entsorgen will/muss...

Und diese Migration muss bald geschehen...

[dimitri]

Eine rein optimistische Sperre wäre noch besser, nur weiß zumindest ich nicht, wie ich dann die Abhängigkeiten auch bezogen auf neue Datensätze etc. auflöse (In der Hoffnung da eine Lösung - falls vorhanden - zu bekommen, hatte ich diesen Thread ja aufgemacht).

Lassen wir mal die Begriffe optimistisch und pessimistisch weg, denn beide Implementierungen sind, wie schon gesagt, nur dazu gedacht Lost Updates zu verhindern. Dir geht es aber nicht nur darum, sondern auch, wenn ich es richtig verstanden habe, eine Überscheidung zwischen einem neu einzufügenden und einem geänderten Termin zu verhindern. Hier haben und werden diese beiden Implementierungen nie helfen.
Du kannst es erreichen über Uniqueconstraints und ein entsprechendes Tabellendesign (haben wir ja schon verworfen) oder, über die angesprochene Metalocktabelle. Die Metalocktabelle ist für Deine Anwendung (und auch nur für die) der Hinweis, dass jemand gerade für den User XY am so und so vielten einen Termin ändert oder neu anlagt. Ob ein Termin sich jetzt aus einer oder aus fünf Tabellen zusammensetzt ist unerheblich, denn die bekommt die Anwendung diesen Hinweis aus der Metalocktabelle wird der User darüber informiert und der Vorgang abgebrochen.

Wenn Du diesen Weg gehen möchtest, dann ist die Frage wie man diese Metalocktabelle designed, dass zum einen ein sicheres Locking stattfindet, und zum anderen auch z.B. beim Absturz des Programmes o.ä. Einträge zurückbleiben, die dann manuell wieder bereinigt werden müssen und somit die Arbeit der anderen User behindern würden.

Man könnte sich das ganz z.B. so vorstellen.

CREATE TABLE TBLTERMINLOCK(USERID VARCHAR(20) NOT NULL, DATUM (DATE) NOT NULL,CHANGENUMBER INTEGER);
CREATE UNIQUE INDEX TBLTERMINLOCK_IX1 ON TBLTERMINLOCK(USERID,DATE);

Das wäre die Metalocktabelle in die sich eine Anwendung einträgt.
Möchte ein Anwender einen Termin eintragen oder ändern, dann wird im Ersten Schritt per MERGE Befehl versucht einen neuen Eintrag hinzuzufügen bzw. einen bestehenden zu aktualisieren (mit den gleichen Werten und Changenumber +1) und damit per Datenbank Lock für andere zu sperren. MERGE deshalb, damit ein bereits vorhandener Eintrag z.B. wegen eines Absturzes nicht verdoppelt wird.
Wann diese Tabelle das erste mal gelesen wird, hängt davon ab, wie Deine Anwendung funktioniert. Gibt es einen Anzeigemodus, in dem die Termine angezeigt werden und man nuss für einen einzelnen Termin eine Änderungmaske öffnen, kann die Changenumber (die wird für das optimistische Locking verwendet) erst gelesen werden, wenn der betreffende Termin in der Maske geöfnet wird. Kann man z.B. wie in Outlook beliebige Termine einfach per Mausklick ändern, dann müssen alle Einträge und deren Changenumber schon beim Aufbau der Anzeige gelesen und gespeichert werden.

Aber zurück zum Eintragen. Hat eine Anwnedung erfolgreich einen Eintrag eingefügt bzw. aktualisiert hat diese ab diesem Zeitpunkt einen logischen Lock auf alle Termintabellen, denn andere Anwendungen können kein weiteren Eintrag mehr für den entsprechenden User am gleichen Tag hinzufügen und brechen den Vorgang ab.
Nachdem die berechtigte Anwendung ihre Datensätze geschrieben hat wird die Änderung committet.

Versucht eine weitere Anwendung jetzt 2 Sekunden später den gleichen Termijn zu ändern, wird über die Changenumber festgestellt, das dieser sich zwischenzeitlich geändert hat (es wird eine Uniqueconstraintverletzung geworfen) und der User darüber informiert.

Vom Ablauf her würde das so aussehen:
1. Fall: Änderung geht durch
User1 selektiert Locktabelle für UserX a, 1.1.2012. Changenumber hat den Wert 10.
User1 ändert den Termin oder fügt einen neuen hinzu und klickt auf speichern
Der Eintrag in der Locktabelle wird per MERGE geändert und Changenumer auf 11 gesetzt.:

MERGE INTO TBLTERMINLOCK a (SELECT userid,datum,changenumber FROM tblterminlock WHERE userid='UserX' AND datum='1.1.2012' AND changenumber=10) b
ON(a.userid=b.userid and a.datum=b.datum and a.changenumber=b.changenumber)
WHEN MATCHED THEN UPDATE SET changenumber=changenumber+1
WHEN NOT MATCHED THEN INSERT(userid,datum,changenumber) VALUES('UserX','1.1.2012',1);

Der Termin wird eingetragen und die Änderung comittet. Alles ok.

2. Fall: Termin ist gerade gelockt
User1 ändert den Termin und klickt auf speichern.
Das MERGE wartet, bis der Datensatz wieder freigegeben wird.
2.1 Die Änderung der anderen Anwendung wurde per Rollback zurückgerollt
Der MERGE findet seinen Datensatz, updatet die Changenumber, schreibt seine Änderung und fertig.
2.1 Die Änderung der anderen Anwendung wird per COMMIT festgeschrieben.
Der MERGE versucht jetzt einen neuen Datensatz anzulegen, da sich die Changenumber zwischenzeitlich ja geändert hat. Da auf USER und Datum aber ein Unqueconstraint liegt, wird der MERGE mit einer Uniqueconstraint Violation abbrechen und die Anwnedung muss entsprechend darauf reagieren.

Da ich aus der Oracleecke komme, kann es sein, dass man bei MSSQL noh etwas mit den verschiedenen Lockoptionen arbeiten muss. Oracle lockt immer zeilenweise, während MSSQL auch Page und Tabellocks verwendet. Hier musst Du dich auf jeden Fall einlesen.

Das ganze ist sicherlich etwas umständlich, aber damit verhindert man zum einen Lost Updates und auch, dass sich neue Termine mit gerade geänderten Überschneiden (darum treiben wir ja den ganzen Aufwand).

Alle Codebeisupiele sind übrigends ungetestet, kann also durchaus ein Tippfehler drinnen sein

Dim