Einzelne Zeile in Tabelle sperren (lock row)

[Incocnito]

Hallo Zusammen,

ich bräuchte mal einen Mechanismus, wie ich eine einzelne Zeile einer PostgreSQL-Tabelle sperren kann.
1) Ich muss (über einen beliebigen Zeitraum) verhindern, dass die Zeile bearbeitet wird.
2) Ich muss rausfinden können, dass die Zeile gesperrt ist.

Ich habe versucht etwas mit SELECT FOR UPDATE zu bauen:
- Ich öffne pgAdmin
- In einem SQL-Fenster (PC 1) steht (in etwa)

Code:

begin;
select *
from my_super_table
where artikelid = 'AADDEEF' for update;

- Führe ich das aus ist der Datensatz gesperrt und andere pgAdmin-Instanzen (PC 2) können den Datensatz nicht mehr bearbeiten (PC 1 Fenster 2 geht übrigens auch nicht, aber das wäre noch egal)
- Führe ich nun die Freigabe aus (PC 1) (ja das ist tatsächlich im selben SQL-Text-Fenster einfach 2 Minuten später eingegeben)

Code:

select *
from my_super_table
where artikelid = 'AADDEEF';
commit;

so wird der Datensatz wieder freigegeben.

- Versuche ich nun von einer anderen pgAdmin-Instanz auf die Daten zuzugreifen geht wieder alles.
- Das ganze ist auch an die Connection gebunden, so dass bei einem Absturz der Datensatz
automatisch wieder freigegeben ist.

Nun habe ich versucht das Ganze in Delphi mit einer UniDac-Query umzusetzen.
Diese meldet mir aber "cannot insert multiple commands into a prepared statement".
Wieso "auf mal" nur noch 1 Befehl pro Query-ExecSQL möglich ist, ist mir schleierhaft ...
ich hätte schwören können, dass ich schon immer mehr abschicken konnte.

Nun das ganze als Frage:
Gibt es eine Möglichkeit einen einzelnen Datensatz zu sperren und wieder frei zu geben,
so dass ich auf diesem Datensatz (nur an einer Arbeitsstation) arbeiten könnte?
Oder gibt es eine Möglichkeit der Uni-Query zu sagen
"mach trotzdem mehr als einen Befehl pro Query-ExecSQL"?

Vielen Dank schonmal für die Zeit!

Liebe Grüße
Incocnito

[himitsu]

Zitat von Incocnito:

Wieso "auf mal" nur noch 1 Befehl pro Query-ExecSQL möglich ist, ist mir schleierhaft ...

Ich kenn UniDAC jetzt nicht.

In PgDAC gibt es zwei Komponenten.
PgQuery und PgScript (OK, auch PgTable)

PgScript ist böse, denn es teilt an den ; den Text auf und sendet alles als einzelne Abfragen zur DB.
(macht Spaß, wenn das Aufteilen nicht an den richtigen Stellen schneidet)

Beim PgQuery kann man einstellen was es macht, womit sich da auch de-/aktivieren lässt, dass mehrere Befehle gehen (kann auch sein, dass eine Option an der Connection war)
Ich denke mal sowas hast bei dir umgestellt oder vergessen zu machen.


Statt BEGIN und COMMIT kannst du aber bestimmt auch die Transaktion über die Connection-Klasse starten, bzw. mit einem Transactionsmanager an der Connection.

[hoika]

Hallo,
Lock auf DB-Ebene ist auf jeden Fall keine gute Idee.

aber naja, schau mal hier

https://stackoverflow.com/questions/...ow-in-postgres

[jobo]

Ich würde mal in Frage stellen, ob so eine Workflow Aktion (Reservierung) über solche Mechnismen sinnvoll ist.
M.E. ist das ein Bestellstatus ("ImWarenKorb"), der den Artikel aus dem Lager entfernt.

Ansonsten:
Die Query Komponenten, die ich kenne machen automatisches Transaktionshandling und sind nicht fähig, mehrere Befehle zu verarbeiten.
Man kann in PG wie in Deinem Beispiel anonyme Blöcke nutzen, aber (natürlich) auch nicht in 2 separaten Queries.

Wenn es unbedingt sein muss:
Explizite Transaktionsteuerung im Client nutzen, das sperrt aber mehr oder weniger alles, was Du benutzt.
Es gilt eigentlich generell die Regel, Transaktion so kurz wie möglich. Das hat seinen Grund.
Die Probleme, die man sich dadurch einhandelt, kann man u.U. umgehen, indem man eine 2. Connection dafür nutzt.

[Incocnito]

Zitat von himitsu:

...
PgScript ist böse, denn es teilt an den ; den Text auf und sendet alles als einzelne Abfragen zur DB.
(macht Spaß, wenn das Aufteilen nicht an den richtigen Stellen schneidet)
...

Danke schonmal für den Hinweis! Das hätte ich tatsächlich als nächstes versucht,
aber dann brauche ich das damit gar nicht erst versuchen.

Zitat von himitsu:

...
Beim PgQuery kann man einstellen was es macht, womit sich da auch de-/aktivieren lässt, dass mehrere Befehle gehen (kann auch sein, dass eine Option an der Connection war)
Ich denke mal sowas hast bei dir umgestellt oder vergessen zu machen.
...

Zumindest finde ich diese Einstellung nicht.

Zitat von himitsu:

...
Statt BEGIN und COMMIT kannst du aber bestimmt auch die Transaktion über die Connection-Klasse starten, bzw. mit einem Transactionsmanager an der Connection.

Ich will keine in sich geschlossene Transaktion starten.
Daher wunderte es mich, dass "BEGIN ... COMMIT;"
sich (im pgAdmin) auftrennen lässt auf zwei nacheinander ausgeführte Querys.
Aber es macht genau das, was ich brauche.

Zitat von hoika:

Hallo,
Lock auf DB-Ebene ist auf jeden Fall keine gute Idee.
...

Kannst du das begründen?

Zitat von hoika:

...
aber naja, schau mal hier

https://stackoverflow.com/questions/...ow-in-postgres

Das dort gezeigte Beispiel verwendet mehrere Befehle (was ja bei mir nicht geht) und außerdem
wird das als Beispiel für einen Vorgang genommen, welcher "in einem Rutsch" durchgeführt wird.

Zitat von jobo:

Ich würde mal in Frage stellen, ob so eine Workflow Aktion (Reservierung) über solche Mechnismen sinnvoll ist.
M.E. ist das ein Bestellstatus ("ImWarenKorb"), der den Artikel aus dem Lager entfernt.
...

Dann habe ich das vielleicht nicht gut genug erklärt.
Stellen wir uns vor, wir hätten einen Kassiervorgang bei dem nun ein Arikel hinzugefügt werden soll.
Hier bietet sich wunderbar eine Transaktion an:
- Position hinzufügen
- Ware aus dem Bestand/Lager entfernen
- meinetwegen noch in den Kopfdaten die Summenwerte anpassen (ist zwar doppelte Datenführung wird aber gerne gemacht, auch als Kontrolle für konsistente Daten)
Das alles passiert in einem Rutsch. Nur in mehreren Tabellen.
Also sehe ich hier eine ideale Verwendung für Transactions.
Zumindest habe ich so Transaktionen bisher mit solchen Aktionen in Verbindung gebracht.
Da wäre ich tatsächlich bei dir:
- Eine seperate (evtl. neue?) Connection
- Transaction Start
- Alle Querys nacheinander so wie man das gerade braucht und/oder möchte
- Transaction Ende
Wunderbar. Dann wäre es auch völlig egal, dass ich pro Query.ExecSQL(); nur einen Befehl abschicken kann.

Jetzt will ich aber sowas gar nicht.
Ich will eher sowas wie das Bearbeiten eines Kunden im Kundenstamm.
Ich habe unzälige Mitarbeiter in der Firma.
Einer öffnet den Kundenstamm und will diesen bearbeiten.
Beim Bearbeiten muss dieser eine Datensatz im Kundenstamm gegen das Bearbeiten
durch andere Mitarbeiter gesperrt sein. Sicher kann man noch einbauen,
dass wenn der Mitarbeiter Kaffee holen geht und die Maus 10 Minuten nicht bewegt wird,
dass das Bearbeiten dann abgebrochen wird oder was auch immer man lustiges machen möchte.
Ich brauche aber dafür halt einen Mechanismus der sicherstellt,
dass der Datensatz für andere zum Bearbeiten gesperrt ist.

Ich hoffe du erkennst an dem Beispiel besser was ich will.
Du hängst mit dem Warenkorb-Beispiel tatsächlich eher bei einer Transaction (1 Event)
und weniger bei einem Lock (2 zeitlich unabhängige Events).
(Auch wenn innerhalb der Transaction ja schon der SQL-Befehl für "Lock" benutzt wird.)

Hier vielleicht noch angemerkt, dass ich keinen Anspruch darauf erhebe, dass ich
die Wörter "Lock" und "Transaction" tatsächlich richtig versetehe, aber so sollen
sie zumindest in meinen Posts gedacht sein und so passt es meines Erachtens nach auch
am Ehesten zu den Beschreibungen, welche ich bisher so gelesen habe.

Zitat von jobo:

...
Ansonsten:
Die Query Komponenten, die ich kenne machen automatisches Transaktionshandling und sind nicht fähig, mehrere Befehle zu verarbeiten.
Man kann in PG wie in Deinem Beispiel anonyme Blöcke nutzen, aber (natürlich) auch nicht in 2 separaten Queries.
...

In pgAdmin kann ich (wie oben schon erwähnt) tatsächlich das "BEGIN" und das "COMMIT" in zwei zeitlich unabhänige Query schreiben. Das funktioniert und macht genau das, was es soll.

---

Unterm Strich: Wie löst ihr das Sperren von Stammdaten zum Bearbeiten?
Oder habt ihr andere Komponenten, bei denen das so funktioniert wie es auch in pgAdmin selbst funktioniert?
Stört ihr euch vielleicht gar nicht daran, wenn Anwender einen Datensatz
von zwei Stationen bearbeiten können?
Bei ADS (Advantage Datebase Server) sperrt die Table-Komponente den Datensatz,
wenn man "TheTable.Edit();" verwendet (bis zum TheTable.Post().
Ich meine bei MySQL/MyDac genau so.
Wenn ich also raten soll, würde die UnitTable das auch machen.
Also werde ich wohl mal darüber versuchen was zu bauen, erscheint mir aber irgendwie falsch/affig.

Liebe Grüße und Danke schonmal für die Zeit und Hilfe bis hierher
Incocnito

[hoika]

Hallo,
du könntest das auf Anwendungsebene implementieren,
z.B. mit einer Sperrtabelle.

Bei Interbase(Firebird hatte ich mal das folgende gepostet

https://www.delphipraxis.net/746237-post.html#854092

[haentschman]

Moin...

Zitat:

Beim Bearbeiten muss dieser eine Datensatz im Kundenstamm gegen das Bearbeiten
durch andere Mitarbeiter gesperrt sein. Sicher kann man noch einbauen,
dass wenn der Mitarbeiter Kaffee holen geht und die Maus 10 Minuten nicht bewegt wird,
dass das Bearbeiten dann abgebrochen wird oder was auch immer man lustiges machen möchte.
Ich brauche aber dafür halt einen Mechanismus der sicherstellt,
dass der Datensatz für andere zum Bearbeiten gesperrt ist.

...eine eigene Lock Tabelle in der DB. Vor dem Bearbeiten wird geguckt ob der Datensatz in Benutzung ist. Nach dem Bearbeiten wieder freigeben.
Die Umsetzung ist natürlich etwas komplizierter:
* in der Lock Tabelle werden gespeichert Computername, User, Wann, Datensatz ID ...etc.
* jeder kann seinen gesperrten Datensatz bearbeiten. (Computerabsturz)
* für den Fall, daß die Putze über das Netzwerkkabel gestolpert ist, kann der Adminstrator des Programmes alle oder bestimmte Sperren zurücksetzen.

Vorteil:
1. kein Locking in der DB!
2. über die Datensatz ID (Kunde etc.) kann man auch die Detaildaten des Kunden sperren. (Zahlungen z.B)
3. automatisches Rücksetzen der Sperre mit Info an den User
...es sind der Kreativität keine Grenzen gesetzt.

[Sinspin]

Zitat von hoika:

Hallo,
du könntest das auf Anwendungsebene implementieren,
z.B. mit einer Sperrtabelle.

Jup. Sowas machen wir auch.
Hat nebenbei noch den Vorteil das man darüber auch Programm Module die nur einmal laufen dürfen verwalten kann.
Dazu gibt es eine Registrierung aller Nutzer zu deren existierenden Connections so das SQL Scripte automatisch wissen unter welchem Nutzer sie laufen und auswerten können welcher Nutzer gerade einen Datensatz besitzt.
Das funktioniert unter ADS Server mit transaktionsfreien Tabellen und unter MS SQL Server via UniDac mit Transaktions ignorierenden Befehlen.

[jfheins]

Zitat von Incocnito:

Unterm Strich: Wie löst ihr das Sperren von Stammdaten zum Bearbeiten?
Oder habt ihr andere Komponenten, bei denen das so funktioniert wie es auch in pgAdmin selbst funktioniert?
Stört ihr euch vielleicht gar nicht daran, wenn Anwender einen Datensatz
von zwei Stationen bearbeiten können?

Als ich sowas mal brauchte, habe ich das über zwei extra (nullable) Spalten gelöst: LockedBy und LockTime. Aber man kann das natürlich auch in eine extra Tabelle auslagern - es ist letztlich eine 0-1:1 Beziehung (0-1 locks pro Datensatz) mit Feldern je nach Bedarf. Bearbeitung nur erlaubt, wenn das Lock dir gehört.

Jede Nacht werden alle locks gelöscht - bei mehr Traffic würde ich aber einen cutoff einbauen und sagen, locks die älter sind als z.B. 1h sind irrelevant.