Locking: sinnvolle Strategie

[charlytango]

Hi,
ich wollte zum Thema Locking in Mehrbenutzerumgebungen eure Meinung hören um vielleicht eine Strategie zu erarbeiten. Das Thema brennt jetzt nicht unter den Fingernägeln, macht für mich aber Sinn es mal anzugehen.

Nehmen wir mal an es ist eine Mehrbenutzerungebung in einem Unternehmen, alle User müssen sich anmelden. Weiters nehmen wir eine Applikation mit Kontaktdaten (Name, Adresse Telnummer etc etc)
Für jeden Kontaktdatensatz sind mehrere Tabellen betroffen (also zB mehrere Adressen, mehrere Namen, historische Notizen und vieles mehr)
Grafisch wird das in einem Fenster präsentiert in dem mehrere andere Fenster und Frames eingeklebt sind und so logisch zu einem "Kontaktobjekt" zusammengefasst sind.

Nachdem es für den Benutzer aussieht als ob es nur ein Fenster mit einem Kontaktobjekt ist, wären "Speichern"-Buttons auf allen Teilframes nicht sinnvoll. (oder seht ihr das anders?)
Daher gibt es nur einen Knopf "Editieren" der das ganze Konglomerat in den Editiermodus versetzt und eine "Speichern" Knopf der alles speichert. (dass dazwischen einiges an Logik passieren muss sein mal dahingestellt.)

Nun -- es wäre möglich direkt in der Datenbank für die zeit des Editierens Tabellen zu sperren (halte ich für unpraktisch weil Benutzer gerne mal in den Editiermodus gehen und dann ein längeres Telefonat annehmen).

Als Alternative in der DB wäre noch direktes Record-Locking, was aber auch seine Herausforderungen hat wenn mehrere Records in unterschiedlichen Tabellen betroffen sind und dann auch noch mehrere Records je Tabelle (zb mehrere Telefonnummern).
Zudem drückt das auf die Performance, nicht alle Datenbanken bieten so etwas an und jede löst das anders.

Bleibt für mich so etwas wie logisches Locking -- Man sperrt quasi die Benutzung des gesamten Objektes wenn ein Benutzer mit einem Kontaktobjekt in den Editiermodus geht.
Praktisch erfolgt das durch eine Prozedur die zuerst prüft ob das Kontaktobjekt noch nciht gelockt ist, danach selbst einen Lockeintrag setzt und alle zugehörenden Frames in den Editiermodus setzt.
Dabei wäre wichtig eine Art Semaphormodus zu haben dass es nicht möglich ist dass sich zwei Sperranforderungen kreuzen und alles trotzdem performant bleibt.

==> für diese sichere Sperrung fehlt mir irgendwie praktisch der Plan

Nach dem Editieren mit Cashedupdates müsste man transaktionsgesichert alle ApplyUpdates durchführen (reicht das alleine oder braucht es noch etwas anderes?)

Die Diskussion ist eröffnet

[af0815]

Die Diskussion ist eröffnet

Locking Programm und PC-Übergreifend ist eine der schwersten Sachen.

Einen Ansatz kann man sich bei dem DB2/AS400 Programmierern (Großrechnern) ansehen. Wobei es auch auf den Datenbankserver ankommt, welche Spielereien möglich sind. Alleine welche Möglichkeiten es beim Locking und Leseberechtigungen gibt . Infos für den MS-Sql u.a. hier https://learn.microsoft.com/en-us/sql/r ... rver-ver16
Oder auch überlegungen zu Datenbanken von Roadwarriors. Die sind ja auch oft Offline und müssen dann abgeglichen werden.

Möglicher Ansatz Programmtechnisch:
An der Tabelle hängt man einen Tigger an, der bei Änderungen eine neue GUID (oder Timestamp) schreibt. Im Programm holt man sich den Datensatz, speichert den Zwischen, aber nicht über die DB-Komponeneten. Dann lässt man den Benutzer ändern. Beim Zurückschreiben prüft man die GUID ob die gleich ist, wenn ja, so kann man die Änderungen durchführen. Im anderen Fall muss man den Konflikt zusammen mit dem Benutzer auflösen. Daher zum Beispiel den Datensatz nochmals holen und mit den geänderten Daten vergleichen. Dann kann der Benutzer entscheiden ob er drüberbügelt oder die Änderungen zuerst abgeglichen werden oder seine Änderungen verworfen werden.
Ist in jedem Fall ein größerer Aufwand, je nach Strategie. Wenn man auch noch mitprotokolliert wer welche Änderung gemacht hat, so kann man auch die History sichtbar machen (und wer an welchen Daten schuld ist ). Und solche Sachen kann man beim MS-SQL sehr gut auf Trigger legen. Damit wird das vom Server selbst verwaltet und nicht von der Applikation. Ausserdem vermeidet man mit dem Zwischenspeichern die alten (Managment) Spielchen - Ich öffne Daten zum Editieren und machen dann den Rechner zu und gehe in eine wichtige Kuschelrunde (Meeting genannt) und in der DB sind die Daten gesperrt. Mit dem Verfahren, wird einfach gesagt - Hey, da hat wer die Daten geändert, was willst du jetzt machen.

Wie gesagt eine Lösung von vielen. Ging bei Delphi und ADO auch mit einem 'Detached' Dataset mit späteren Einpflegen (und Konfliktlösung).

[Warf]

Mein Vorschlag mit komplettem Plain SQL ohne irgendwelche speziellen Server Features: Erstell eine Tabelle Locked mit 3 spalten, datensatz ID und userID und Timestamp.
Zum alloziieren des Locks mach ein INSERT ... WHERE NOT EXISTS (je nach datenbank system kann die syntax etwas abgewandelt sein) wo du für die DatensatzID für den Nutzer mit der Aktuellen Uhrzeit reinschreibst. Dabei überprüfst du die affected rows (oder wie auch immer das heißt), wenn die = 0 ist wurde kein insert durchgeführt was bedeutet das das lock schon existiert. Dann zeigst du nen Fehler an und der Nutzer kann warten. Ist die Anzahl eingefügter Zeilen gleich 1 hast du das lock und der Nutzer kann feucht fröhlich arbeiten.

Um das Lock freizugeben musst du dann nach dem schreiben des Datums (bzw. in der selben Transaktion) die Lockzeile löschen. Als zusätzlichen Sicherheitsmechanismus testest du beim Delete ob der Nutzer auch das lock hat (die affected rows bei delete sollte 1 sein), wenn nicht machst du ein rollback der aktuellen Transaktion.

Die Timestamp kannst du noch zusätzlich benutzen um ein Timeout zu machen. Sodass niemand das Lock holt und dann einschläft und damit den Datensatz als Geisel hält

Alternativ wenn du kein lock haben willst mach ein UPDATE ... WHERE ... AND Spalte1=AlterWert1 ...
Wenn da die affected rows 0 sind hat jemand anderes in der Zwischenzeit ein update gemacht, du kannst den Nutzer informieren und fragen ob er was anpassen will, überschreiben oder abbrechen will

[Zvoni]

Mein Vorschlag mit komplettem Plain SQL ohne irgendwelche speziellen Server Features: Erstell eine Tabelle Locked mit 3 spalten, datensatz ID und userID und Timestamp.
Zum alloziieren des Locks mach ein INSERT ... WHERE NOT EXISTS (je nach datenbank system kann die syntax etwas abgewandelt sein) wo du für die DatensatzID für den Nutzer mit der Aktuellen Uhrzeit reinschreibst. Dabei überprüfst du die affected rows (oder wie auch immer das heißt), wenn die = 0 ist wurde kein insert durchgeführt was bedeutet das das lock schon existiert. Dann zeigst du nen Fehler an und der Nutzer kann warten. Ist die Anzahl eingefügter Zeilen gleich 1 hast du das lock und der Nutzer kann feucht fröhlich arbeiten.

Interessanter Ansatz.
Jedoch statt WHERE NOT EXISTS hätte ich eher in Richtung Try/Except geschielt.
Einfach das INSERT abfeuern. Wenn der Primärschlüssel bereits existiert biegt dein Code in das "Except" ab "Ähhh.... Satz ist gesperrt von User Fritz Müller".
Setzt natürlich einen "sinnvollen" und logischen Primärschlüssel voraus

Um das Lock freizugeben musst du dann nach dem schreiben des Datums (bzw. in der selben Transaktion) die Lockzeile löschen. Als zusätzlichen Sicherheitsmechanismus testest du beim Delete ob der Nutzer auch das lock hat (die affected rows bei delete sollte 1 sein), wenn nicht machst du ein rollback der aktuellen Transaktion.

Hier hätte ich eher in Richtung einem AFTER UPDATE-Trigger der "eigentliche(n)" Tabellen geschaut.
1) User erhält den Lock (Eintrag in Lock-Tabelle s.o.)
2) User editiert/updatet die eigentliche Tabelle(n)
3) User clickt auf Speichern, welches den AFTER UPDATE-Trigger zündet. Im Trigger selbst wird der DELETE auf die Lock-Tabelle ausgeführt
Wobei ich mir jetzt im ersten Moment auch nicht sicher bin, wie ich es machen würde, wenn der User in Schritt 3 statt auf Speichern auf Abbrechen geht, und somit den Lock wieder freigeben müsste

Prinzipiell zum "Locking": Würde immer zuerst nachschauen, was mir das DBMS selbst an Mechanismen anbietet, und erst wenn ich absolut nix finde, schaue ich in Richtung eigenem Code (egal ob jetzt per Trigger oder Stored Procedure auf der DB selbst) oder komplett im Frontend.
Aber das hängt natürlich vom verwendeten DBMS ab.
Stichwort: Transaction Isolation Level.
Ich glaube mich daran zu erinnern, dass es einen Level gibt, der Änderungen nicht zulässt, wenn bereits eine Transaction gestartet ist.
Müsste ich aber wieder mal recherchieren.
Also im Sinne (Nur Algoritmus)
1) User geht in "Editier-Modus" --> "BEGIN TRANSACTION" (!!)
2) User editiert
3) User clickt auf Speichern "COMMIT" - User clickt auf Abbrechen "ROLLBACK"
Würde definitiv auf Try/Except hinauslaufen, aber bräuchte keine Lock-Tabelle

[Warf]

Das Problem ist das das DBMS natürlich nicht unbedingt die Semantik deiner Daten kennt. D. H. wenn du ein lockikg übereinzelne Datensätze brauchst kann das DBMS natürlich nicht (ohne code) wissen was dazu gehört und was nicht.
Du willst ja keine ganzen Tabellen sperren

[charlytango]

Das sind schon mal einige interessante Ansätze.

Das Problem ist das das DBMS natürlich nicht unbedingt die Semantik deiner Daten kennt. D. H. wenn du ein lockikg übereinzelne Datensätze brauchst kann das DBMS natürlich nicht (ohne code) wissen was dazu gehört und was nicht.
Du willst ja keine ganzen Tabellen sperren

Ja genau, deswegen tendiere ich eher zu semantisch/logischem Locking als direkt Tabellen oder Datensätze zu sperren. Das hat in der Vergangenheit schon mal ganz gut funktioniert.

TLocking
*************
ID .. irgend eine Art des Prim Keys (vielleicht autoincrement)
UserID
LockingTheme ... ZB "K" für Kontakte, "F" für Faktura
LockingID ... die zum LockingTheme gehörende Prim ID zB einer Person.
LockingTimeStamp .. Wann wurde der Lock eingetragen
LockingType ... experimentel, ich denke an "read" oder "edit"

LockingType: um zb eine Sperre mal vorsichtshalber auf Read zu erstellen und die dann später auf Edit zu stellen.
Sinn dahinter -- wenn Personen - Personen Verbindungen im Spiel sind oder Firmen-Firmen Verbindungen kann der eine Teil der Verbindung gerade gändert werden während der andere gerade von unterschiedlichen Benutzern gelesen (und damit eigentlich updated werden müsste) wird.

Die Herausforderung dabei ist, dass sich INSERT Aktionen zweier unterschiedlicher Benutzer unter keinen Ümständen irgendwie kreuzen dürfen (mit dem Ergebnis dass dann zwei Locks zum gleichen Kontakt und unterschiedlichem User in der Tabelle stehen). Früher hat man das mit einer Semaphorendatei im Netzwerk gelöst, die man zuerst versucht hat zum schreiben zu öffnen, damit war klar dass niemand anderer gerade die Da6tei haben konnte -- dann wirde der Lock geschrieben und anschließend die Datei wieder losgelassen.

Was ich im Moment erreichen möchte, ist dass aus dieser Diskussion eine Strategie entsteht wie man das alleine mit SQL schafft, und das noch dazu so, dass keine besonderen Spezialbefehle benutzt werden die es dann auf anderen SQL Servern nicht gibt.
Letztlich hat dieses Problem jeder der ein Mehrbenutzersystem übergreifend auf mehrere SQL Server bauen will/muss

[charlytango]

Prinzipiell zum "Locking": Würde immer zuerst nachschauen, was mir das DBMS selbst an Mechanismen anbietet, und erst wenn ich absolut nix finde, schaue ich in Richtung eigenem Code (egal ob jetzt per Trigger oder Stored Procedure auf der DB selbst) oder komplett im Frontend.

Da hast du sicher recht --wenns ein Job für ein bestimmtes DBMS wäre.
Mein Ansatz ist es, eine möglichst allgemeingültige Lösung für mich zu finden und damit vielleicht auch einen guten Köder zu finden damit auch größere DB Applikationen leichter in Lazarus umgesetzt werden könnten. Die Lösung soll sicher und sauber funktionieren, auch wenn das eine oder andere DBMS bessere Möglichkeiten hätte.

Jemand der erfahren genug ist und speziell für ein DBMS arbeitet wird sicher eine spezifische Lösung vorziehen.

[Zvoni]

Prinzipiell zum "Locking": Würde immer zuerst nachschauen, was mir das DBMS selbst an Mechanismen anbietet, und erst wenn ich absolut nix finde, schaue ich in Richtung eigenem Code (egal ob jetzt per Trigger oder Stored Procedure auf der DB selbst) oder komplett im Frontend.

Da hast du sicher recht --wenns ein Job für ein bestimmtes DBMS wäre.
Mein Ansatz ist es, eine möglichst allgemeingültige Lösung für mich zu finden und damit vielleicht auch einen guten Köder zu finden damit auch größere DB Applikationen leichter in Lazarus umgesetzt werden könnten. Die Lösung soll sicher und sauber funktionieren, auch wenn das eine oder andere DBMS bessere Möglichkeiten hätte.

Jemand der erfahren genug ist und speziell für ein DBMS arbeitet wird sicher eine spezifische Lösung vorziehen.

OK, Einverstanden.

Ja genau, deswegen tendiere ich eher zu semantisch/logischem Locking als direkt Tabellen oder Datensätze zu sperren. Das hat in der Vergangenheit schon mal ganz gut funktioniert.

TLocking
*************
ID .. irgend eine Art des Prim Keys (vielleicht autoincrement)
UserID
LockingTheme ... ZB "K" für Kontakte, "F" für Faktura
LockingID ... die zum LockingTheme gehörende Prim ID zB einer Person.
LockingTimeStamp .. Wann wurde der Lock eingetragen
LockingType ... experimentel, ich denke an "read" oder "edit"

"ID .. irgend eine Art des Prim Keys (vielleicht autoincrement)" --> Definitiv nicht Auto-Increment.
Eher im Sinne von:
Tabellen-Name||ID-Des-Satzes||Was-sonst-Noch

Weil nur OHNE AutoIncrement kannst du in die Situation kommen, dass ein Primärschlüssel-Wert bereits in der Tabelle ist, als solches auch erkannt wird, falls User 2 versucht, denselben Satz zu ändern

[charlytango]

Also im Sinne (Nur Algoritmus)
1) User geht in "Editier-Modus" --> "BEGIN TRANSACTION" (!!)
2) User editiert
3) User clickt auf Speichern "COMMIT" - User clickt auf Abbrechen "ROLLBACK"

Genau so etwas meine ich leider nicht. Wir reden nicht von einer Tabelle die gleichzeitig betroffen ist, sondern sagen wir mal von 10 oder so.
Die Strategie soll so sein dass die Daten geholt werden (meinetwegen mit einem Read-Lock).
Der Benutzer möchte ändern und geht in einen Edit-Modus, den er nur bekommt wenn der EDIT-Lock erfolgreich war.

Der Benutzer editiert munter rum. Falls er abbricht (oder ein Timeout für nicht geschlossene Formulare greift) wird der Edit-Lock gelöscht (oder ggfs auf Read zurückgesetzt) und das Formular geschlossen (oder eine andere Strategie verfolgt, wie zb ein Refresh der Daten.

Drückt der Benutzer Speichern, werden transaktionsgesichert (ich weiß noch nciht wie ich das mit CashedUpdates hin bekomme, hatte da früher einen anderen Cache-Mechanismus) in die DB geschrieben und als letztes der Lock rausgelöscht.

Die Zeit für eine transaktionsgesicherte DB-Aktion soll gering gehalten werden, damit da nicht reihenweise offene Transaktionen rumschwirren.

[af0815]

Drückt der Benutzer Speichern, werden transaktionsgesichert (ich weiß noch nciht wie ich das mit CashedUpdates hin bekomme, hatte da früher einen anderen Cache-Mechanismus) in die DB geschrieben und als letztes der Lock rausgelöscht.

Wenn du das atomic über Transaktionen machst, dann musst du zuerst einmal die Änderungen herausfinden und dann das ganze als Transaktion durchführen. Damit hast du dann wirklich entweder alles oder nichts.

Was hast du für einen SQL-Server im Auge ? Weil da sind öfters verschiedene Mechanismen implementiert, abgeshen von den Unterschieden von SQLdb und ZEOS (und dort 7.x oder 8.x)

[charlytango]

Was hast du für einen SQL-Server im Auge ? Weil da sind öfters verschiedene Mechanismen implementiert, abgeshen von den Unterschieden von SQLdb und ZEOS (und dort 7.x oder 8.x)

Blöderweise keinen. Und ich weiß zwar grundsätzlich dass SQLDB und ZEOS unterschiedlich funktionieren aber weiter geht mein Wissen nicht.

dann musst du zuerst einmal die Änderungen herausfinden und dann das ganze als Transaktion durchführen. Damit hast du dann wirklich entweder alles oder nichts.

Ja. So etwas hat der alte Cache Mechanismus gemacht. Keine Ahnung ob ich den noch finde oder ihn zum Leben erwecken kann.

[Zvoni]

Wenn du das atomic über Transaktionen machst, dann musst du zuerst einmal die Änderungen herausfinden und dann das ganze als Transaktion durchführen. Damit hast du dann wirklich entweder alles oder nichts.

Das ist aber genau das was ich meinte z.B. mit Stored Procedure: Du übergibst alle Änderungen (bzw. sogar alle Daten) an eine SP, und die SP selbst startet eine locking Transaction, welche dann alle betroffenen Datensätze (Nicht Tabellen) in ihren entsprechenden Tabellen sperrt.

Zumindest verstehe ich den Mechanismus so

[af0815]

Das ist aber genau das was ich meinte z.B. mit Stored Procedure: Du übergibst alle Änderungen (bzw. sogar alle Daten) an eine SP, und die SP selbst startet eine locking Transaction, welche dann alle betroffenen Datensätze (Nicht Tabellen) in ihren entsprechenden Tabellen sperrt.

Vorsicht mit Transaktionen bei SQLdb (und MS-SQL). Da habe ich schon eine Diskussion mit MvC gehabt, bezüglich automatischen Transaktionen.

Konklusio: Das das mit den automatischen Transaktionen bei SP nicht so funktioniert ist per Design (Zumindest wurde das mir so verkauft). Das geht also nur bei normalen Queries. Wie merkt man es: Es wird lt. Servertrace immer wieder ein Rollback gemacht, weil die Transaktion bei SP in der SQLdb nicht richtig abgeschlossen wird und die Daten sind nicht so geändert wie man es sich gedacht hat. Fehlermeldung - habe keine Bemerkt, nur lange Fehler gesucht, bis ich es mit einer simplen Sequenz in einer SP nachstellen konnte.

[Zvoni]

Das ist aber genau das was ich meinte z.B. mit Stored Procedure: Du übergibst alle Änderungen (bzw. sogar alle Daten) an eine SP, und die SP selbst startet eine locking Transaction, welche dann alle betroffenen Datensätze (Nicht Tabellen) in ihren entsprechenden Tabellen sperrt.

Vorsicht mit Transaktionen bei SQLdb (und MS-SQL). Da habe ich schon eine Diskussion mit MvC gehabt, bezüglich automatischen Transaktionen.

Konklusio: Das das mit den automatischen Transaktionen bei SP nicht so funktioniert ist per Design (Zumindest wurde das mir so verkauft). Das geht also nur bei normalen Queries. Wie merkt man es: Es wird lt. Servertrace immer wieder ein Rollback gemacht, weil die Transaktion bei SP in der SQLdb nicht richtig abgeschlossen wird und die Daten sind nicht so geändert wie man es sich gedacht hat. Fehlermeldung - habe keine Bemerkt, nur lange Fehler gesucht, bis ich es mit einer simplen Sequenz in einer SP nachstellen konnte.

AUTSCH!!
OK, bin ich noch nicht mit konfrontiert worden.

[charlytango]

AUTSCH!!
OK, bin ich noch nicht mit konfrontiert worden.

That's why I'm asking here ggg