Frage zu Locking (Vergabe von Nummern im Nummernkreis)

[jhoehne]

Ich bin dabei, unsere Software von einer anderen Datenbank (Advantage Database Server) nach Firebird umzustellen. Für eine besondere Artikelnummer muss dabei ein Nummernkreis verwendet werden, d.h. es gibt nur eine begrenzte Zahl von Nummern, und es werden Nummern sowohl entnommen als auch wieder hinzugefügt. Aus dem Grund fällt ein Generator flach.
Damit mehrere User zeitgleich Nummern vergeben können, habe ich bislang die Vergabe mit einer besonderen "Lock"-Tabelle koordiniert: die Tabelle enthält genau einen Datensatz mit einem Wert. Die Vergaberoutine editiert und lockt damit den Datensatz, vergibt die Nummer und hebt danach den Lock wieder auf. Jeder weitere parallele Zugriff bekommt vom Datenbanksystem der Meldung, der Datensatz ist gelockt, wartet dann eine kurze Zeit und versuchts erneut. Das hat schon bei Paradox/BDE super funktioniert und beim ADS ebenfalls. Aber wie mache ich das mit Firebird?

[vr2]

In einer Stored Procedure als erstes einen Generator abfragen, der nur benutzt wird, weil er transaktionsübergreifend ist. Im Grunde eine Semaphore.
Ist der Generator 0, dann als erstes den Generator hochsetzen, dann die Aktion durchführen, dann (auch bei eventueller exception) den Generator zurücksetzen
Ist er > 0, dann raus mit Hinweis

Grüße, Volker

[jhoehne]

Super Idee, danke!

[bfuerchau]

Das mit dem Generator klappt nicht, da zwischen Abfrage und Änderung zu viel Zeit vergeht und somit mehrere parallele Abfragen zum selben Ergebnis, nämlich 0, kommen können.

Die Einzige Funktion, die tatsächlich auch mit LOCK-Timeout (Verbindungseigenschaft) arbeitet, ist ein "Select ... for update".
Der "for update" wartet so lange, bis die Sperre aufgehoben ist.
DIes passiert entweder mit Commit oder mit einem Rollback.

Also ist folgende Reihenfolge möglich:
Tabelle "GLOBALLOCK" mit einem Int-Feld=Key = 1 und einer Zeile erstellen (Global 1x)

- Transaktion starten
- Select Key from Globallock where Key = 1 for update
- Aktion durchführen
- commit

Da i.d.R. diese Transaktion kürzer ist als das Wait-Timeout, bekommst du Zugriffe Datenbankweit sequentialisiert.
Das schöne daran ist, die FB übernimmt auch noch das Synchronisieren, da du vom Client keine Schleife programmieren must.
Wenn die Aktion dann doch mal länger dauern sollte, bekommst du eine Fehlermeldung "Waittimeout" und gehst in die Wiederholung.

Auf diese Weise lassen sich auch verschiedene Aktionen (Key = 1, 2, 3, ...) sequentialisieren.

Auf dieselbe Weise kann man somit auch Beleg-Locks erstellen.
Tabelle mit einem Unique-Key erstellen.
- Transaktion starten
- Insert in die Tabelle
- Bei Duplicate Key => Beleg in Bearbeitung
- Wenn Bearbeitung fertig, dann Delete Key

Dies macht man in einer 2. Verbindung während man in seiner 1. Verbindung arbeitet.
Somit kann ich unterschiedliche "Bearbeitungssperren" setzen, und diese einem User ohne Wartezeiten signalisieren.
Das schöne daran ist, egal wieviele Sperren ich damit setze, bei Verbindungsabbruch wird ein Rollback gemacht und die Sperren sind automatisch aufgehoben.

[jhoehne]

Danke für die ausführliche Antwort.

Reicht "FOR UPDATE" aus, oder muss da noch "WITH LOCK" angehängt werden?

In der Firebird 2.5 Language Reference heißt es etwa "FOR UPDATE does not do what it suggests. Its only effect currently is to disable the pre-fetch buffer."

[vr2]

Das mit dem Generator klappt nicht, da zwischen Abfrage und Änderung zu viel Zeit vergeht und somit mehrere parallele Abfragen zum selben Ergebnis, nämlich 0, kommen können.

Ich hatte es auch nicht ganz korrekt beschrieben. Zuerst muss der Generator hochgesetzt werden, dann verglichen. Wenn man es bspw so macht:

Code: Alles auswählen

if (next value for <generator> = 1) then
begin
  ...
end
-- <generator> wieder auf 0 setzen

kommt nur ein SP-Nutzer in den begin-end-Block. Das Hochsetzen eines Generators ist atomar, egal wie "gleichzeitig" es passiert, einer ist zuerst. Sonst wären Generatoren auch für alle anderen Zwecke unbrauchbar. Es kann höchstens sein, dass der Generator vor dem ersten Vergleich mehrfach hochgesetzt wurde, dann kommt keiner in den Block und es müssen alle warten. Aber zwei kommen definitiv nicht gleichzeitig in den Block.

Die Einzige Funktion, die tatsächlich auch mit LOCK-Timeout (Verbindungseigenschaft) arbeitet, ist ein "Select ... for update".

Interessanter Ansatz, danke für den Tip!

Wie soll das aber laufen, wenn man die DB-Ebene nicht verlassen will/kann, dh alles innerhalb einer SP oder mehreren verschachtelten SPs geregelt werden soll - und der übrige SP-Code ganz normal ausgeführt werden muss? Auch eine autonome TX ist ja erstmal keine separate Connection. execute statement on external auf die selbe DB? Und wir haben innerhalb einer SP/autonomen TX noch keine präzise/abweichende Transaktionssteuerung (commit/rollback/isolation level).

[bfuerchau]

Da nun mal ein Client nicht ohne Verbindung auskommt, ist auch für eine SP ein LOCK-Timeout definiert (Default glaube ich 60).
Bzgl. des Generators hast du ja Recht, das ist atomar.
Allerdings musst du ja dann trotzdem eine Wait-Prozedur machen. Oder in einer Schleife so lange versuchen, bis es klappt.
Da bich ich auf die Performance gespannt, wenn ggf. mehrere 100 oder 1000 Fehlerversuche stattfinden wenn du das innerhalb der Prozedur löst.

"Select ... for update" lockt automatisch, da braucht man nichts weiter.
Der Lock bleibt bis zum Commit/Rollback erhalten.

[vr2]

Ich brauche das bisher nur in einem speziellen Fall, wo durch den Abruf einen Berichts für mindestens eine seiner Datenquellen eine Art Cache gefült wird, falls nötig. Andere Nutzer dieses Berichts kriegen in einer Webanwendung das Signal, dass Daten gerade geladen werden und dass sie es nach einigen Sekunden nochmal versuchen sollen. Sie füllen aber trotzdem ggf den Cache einer oder mehrerer weiteren Datenquellen des Berichts. Sie können dann entweder einen anderen Bericht starten oder eben kurz drauf wiederholen. Dh, je mehr Nutzer "gleichzeitig" einen bestimmten Bericht aufrufen, desto mehr wird das Datenladen/Wartezeit parallelisiert. Gleichzeitige Requests sind in der Konstellation allerdings gering, so dass man nicht weiter ausholen muss.

Das Datenladen ist nur ein kleiner Abschnitt einer komplexeren Aktion innerhalb einer SP, die aus mehreren anderen besteht, der Rest soll unabhängig weiterlaufen können. Mir ist noch nicht klar, ob man mit select for update tief im Inneren einer SP - die ja bereits einen TX-Kontext mitbringt/vorgibt - gezielt einen kleinen Abschnitt zur parallelen Nutzung klammern kann, abweichend vom TX-Verhalten der übergeordneten SP und ohne aus der übrigen Ausführung komplett rauszuspringen.

[bfuerchau]

Dazu siehe hier:
https://firebirdsql.org/refdocs/langref ... trans.html

Auch dieses mache ich da schon mal, dass ich innerhalb einer separaten SP-Transaktion dann die Sperre verwende.
Z.B. ist ein "Create Table" datanbankweit schädlich, wenn dies parallel versucht wird. Auch hier zieht die Versionierung, so dass alle folgenden DDL-Transaktionen hier Probleme bekommen bis hin zum DB-Crash.
Meine BI-Anwendung erstellt zur Laufzeit viele temporäre Tabellen (und löscht diese auch wieder) was durch "Select for update" dann aber DB-weit sequentialisiert wird. Dies erreiche ich durch autonomous Transaktion:

begin in autonomous transaction
do
select ... for update
create table bla
end
insert into bla select ....

Die autonome transaction macht selber einen Commit/Rollback was durch eine Fehlerbehandlung zusätzlich überwacht werden kann.
Ende ohne Fehler => Commit
Ende mit Fehler => Rollback
Durch Auslösen eines Fehlers kann da also auch ein Rollback gemacht werden.

Übrigens: Create's/Drop's gehen nur per Execute.

[vr2]

Habe den Code mal eingedampft auf folgenden Schnipsel. Das vereinfacht den ursprünglichen Code ganz ordentlich:

Code: Alles auswählen

execute block
returns (status varchar(100))
as
declare id int;
begin
  if (1 = 1) then  -- hier echte bedingung einsetzen
  begin
    in autonomous transaction do
    begin
      select id from seq where id = 1 for update with lock into :id;
      -- nutzlast hier --
      status = 'ok';
    end
    when any do
    begin
      if (gdscode = 335544878) then
        status = 'warten';
      else
        status = 'fehler ' || gdscode;
    end
  end
  suspend;
  -- ...
end

Das funktioniert unter folgenden Bedingungen:
* TX read_committed no_wait
* with lock muss benutzt werden (also explizites locking, implizites reicht nicht) Hier stimmt die Doku nicht https://firebirdsql.org/refdocs/langref ... ithlock-of
* es gibt eine Tabelle seq mit Feld id und einem Satz mit Wert 1.

Testet Du auch auf diesen gdscode (335544878 concurrent_transaction) oder wie unterscheidest Du reguläre Fehler in der autonomen TX von der concurrency exception? Also bspw Laufzeitfehler wie

Code: Alles auswählen

select id from seq where id = 1/0 for update with lock into :id;

Grüße, Volker