Firebird 3.0 in einer SP (Multiuser-Umfeld) ein Wert ändern

[Kostas]

Hallo Zusammen,

ich habe eine Tabelle mit zwei Felder PK + Chargen-Nummern und mehreren tauend Datensätzen.
Über eine SP wird der PK übergeben. Daraufhin wird die Chargen-Nummern über ein Update inkrementiert.
In sehr seltenen Fällen kommt es zu einem dead lock. Das Problem ist, ich habe keine Möglichkeit die Clients anzupassen um auf die exception zu reagieren. Es muss also innerhalb der SP passieren.

Hat jemand eine Idee?

Gruß Kostas

[Lemmy]

Zitat von Kostas:

Hat jemand eine Idee?

viele, aber mit dem Code der SP müsste man nicht im trüben fischen....

[Kostas]

Sorry,

so sieht meine Methode derzeit aus.

Code:

create or alter procedure GetNextCharge(ChargeID integer)
returns (
    NextCharcheNr integer)
as
declare variable vergleich1 integer;
declare variable vergleich2 integer;
begin

  Vergleich1 = 0;
  Vergleich2 = -1;

  while (Vergleich1 <> Vergleich2) do
  begin

    /* aktuelle CharcheNr abfragen */
    select CharcheNr
    from Chargen
    where ChargeID = :ChargeID
    into :Vergleich1;

    /* CharcheNr inkrementieren */
    Vergleich1 = Vergleich1 + 1;

    /* CharcheNr schreiben */
    update Chargen
       set CharcheNr = :Vergleich1
    where ChargeID = :ChargeID;

    /* zum Vergleich abfragen */
    select CharcheNr
    from Chargen
    where ChargeID = :ChargeID
    into :Vergleich2;

    /* wenn gleich raus ansonsten wiederholen */
    if (vergleich2 = vergleich1) then
    begin
      NextCharcheNr = :vergleich1;
      suspend;
    end
  end
end

[knuut21]

Hallo Kostas,

Du kannst in dem Fall die Exception abfangen und ggf. im Ablauf entsprechend reagieren:

https://firebirdsql.org/file/documen...ef30-psql-when

BG

Knuut21

[IBExpert]

So als Konzept wie man das verhindern kann, wenn man das unter High Load realisieren muss:

1. Erstelle dir eine Tabelle mit chargennummer auf reserve (als zB sind da die nächsten x tausend verfügbaren chargennummer schon drin). Diese hat dann zB ein timestamp, wann die vergeben wurde
2. Wenn du eine Chargenummer brauchst, dann sucht deine prozedur über folgende weg eine heraus
3. übergeordnete sp ohne exception handling when any GetCharge
-diese ruft mit einer for select schleife alle chargen ab, die im vergeben timestamp null hat
4. über eine extra sp mit exception handling when any machst du das den eintrag
create procedure SetCharge(chargenr integer)
returns (res char(1))
as
begin
update charge set charge.vergeben=current_timestamp where charge.chargenr=:chargenr;
res='T';
when any do res='F';
end;
5. mit allen results die du in der GetCharge in der for select into schleife free chargenumer geholt hast machst du ein execute procedure SetCharge RETURNING_VALUES mit der chargenr als input sol lange bis die irgendwann man T zurück gibt, dann reicht ein break um deine for select schleife zu beenden, der timestamp ist für diese bisher frei charge gesetzt und egal ob unter high load jemand anders diese oder andere in lang laufenden transaktion auch setzt und evtl sogar rollback macht ist egal, weil keiner dadurch einen deadlock bekommt.

der code da oben ist einfach so runtergetippt, daher sicherlich hier und da evtl fehlerhaft, sollte aber zu schaffen sein, das umzusetzen

[MyRealName]

Es gibt da noch die globalen Variablen, die zur Connection oder Session gehören. Da kann man über die Tabelle auch die der anderen Connections/sessions sehen und auslesen. Könnte man anlegen, falls sie nicht da ist und löschen wenn man fertig ist. Und darüber dann sagen, welche Chargennummer man da gerade nutzt. wenn das mehrere Sessions sind, die sowas machen, dann einfach nur die größte einer fremdsession (also wo SessionId halt nicht die eigene ist) raussuchen und dann die nächste nehmen und selbst die Variable dann für die eigene Session anlegen. Und löschen, wenn man fertig ist.

[IBExpert]

Zitat von MyRealName:

Es gibt da noch die globalen Variablen, die zur Connection oder Session gehören. Da kann man über die Tabelle auch die der anderen Connections/sessions sehen und auslesen. Könnte man anlegen, falls sie nicht da ist und löschen wenn man fertig ist. Und darüber dann sagen, welche Chargennummer man da gerade nutzt. wenn das mehrere Sessions sind, die sowas machen, dann einfach nur die größte einer fremdsession (also wo SessionId halt nicht die eigene ist) raussuchen und dann die nächste nehmen und selbst die Variable dann für die eigene Session anlegen. Und löschen, wenn man fertig ist.

Unter High Load ist das kein echter Lösungsansatz, weil rollbacks oder exceptions die sessionvariablen dann nie wieder freigeben würden, daher würde ich das so nicht machen

[Kostas]

Vielen Dank für die Antworten,

das klingt alles richtig kompliziert.
Eigentlich habe ich nach einer Konstruktion gehofft, die vor dem Update prüfen kann ob der Record, beschreibbar ist. Wenn nicht, selbständig warten bis er beschreibbar ist und fertig. In diesem speziellen Fall ist der einzige Initiator der die ChargenNr schreibt diese eine einzige SP. Das Problem ist nur, diese SP kann von zwei Client gleichzeitig aufgerufen werden und so den deadlock produzieren. In meiner SP mit dem auslesen, upadten, wieder auslesen und vergleichen ist eigentlich alles Mist. Ist nur aus Verzweiflung entstanden.

@Holger: Es sind mehrere tausend Artikel die jeweils eine eigene ChargenNr die NUR fortlaufend ist, haben. Die Tabelle Chargen ist absichtlich 1:1 über den PK und ohne FK des Artikels und beinhaltet NUR die ChargenNr sonst nichts, damit der Record mit der ChargenNr beschrieben werden kann unabhängig vom Artikel möglichst ohne deadlock. Sobald ein Artikel angelegt wird, wird der Record in Chargen angelegt mir ChargenNr = null.
Dein Vorschlag mit der zusätzlichen Tabelle die für jeden Artikel mehrere Chargennummer vorhalten soll habe ich nicht verstanden. Vermutlich passt das auch nicht zu meinem Fall. Leider habe ich das nicht gleich erwähnt und habe dich möglicherweise in die Irre geführt, sorry dafür. Ich dachte so eine "Warten" Konstruktion gibt es, nur ich kenne sie nicht.

Wichtig ist, ich habe keine Möglichkeit im Client irgend etwas zu verändern da es fremde Maschinensteuerungen sind. Ich kann also keine Exception im Client abfangen. Es muss alles in der SP passieren und die SP muss durchlaufen und die nächste gespeicherte ChargenNr ausgeben. Das beste währe gewesen, ich hätte für jeden Artikel einen Generator angelegt. Da es viele tausend Artikel sind wollte ich dafür die Generatoren nicht missbrauchen.

Gruß Kostas

[IBExpert]

du kannst mit einer wait transaction auch warten, hängt von den DB Komponenten ab, wo du das wie einstellst.
das blockt aber nur so lange bis klar ist ob die konkurrierende deadlock transaction committed ist, weil du in dem
fall erst dann die exception bekommst, die du auch bei nowait direkt bekommen hättest. nur bei rollback der konkurrierenden
deadlock transaction ginge es bei dir ohne exception weiter. Wenn die andere Transaktion aber warum auch immer stundenlang
dauert, dann wird es bei wait auch stundelang nicht weitergehen, daher eignet sich der ansatz nur selten.

Das Verfahren das ich dir geschildert hat ist 100% deadlock sicher und sorgt für eindeutigkeit schon in der db, du kannst das
aus einem trigger heraus machen oder vom client oder wo aus immer.

Im Rahmen unserer Hotlinepaket kann man so was auch mit meiner Hilfe per remote Session direkt in die db einbauen lassen
und sich dabei erklären lassen, das das überhaupt nicht kompliziert ist und das nachher auch selber so sieht

[MyRealName]

Zitat von IBExpert:

Zitat von MyRealName:

Es gibt da noch die globalen Variablen, die zur Connection oder Session gehören. Da kann man über die Tabelle auch die der anderen Connections/sessions sehen und auslesen. Könnte man anlegen, falls sie nicht da ist und löschen wenn man fertig ist. Und darüber dann sagen, welche Chargennummer man da gerade nutzt. wenn das mehrere Sessions sind, die sowas machen, dann einfach nur die größte einer fremdsession (also wo SessionId halt nicht die eigene ist) raussuchen und dann die nächste nehmen und selbst die Variable dann für die eigene Session anlegen. Und löschen, wenn man fertig ist.

Unter High Load ist das kein echter Lösungsansatz, weil rollbacks oder exceptions die sessionvariablen dann nie wieder freigeben würden, daher würde ich das so nicht machen

Wenn die Session ungültig wird, werden dann nicht automatisch die Variablen gelöscht ? Oder könnte man einen Inner-Join zur Sessiontabelle machen und so sicher stellen, das ungültige Einträge ignoriert werden ?