View Full Version : SQL-Update und Subselect
Hallo Forum.
Eine Datei mit Feld "Satzart" und "Zähler" soll mit SQLRPGLE verarbeitet werden.
Entry-Parameter: Datenstruktur von "Datei"
1. Falls SATZART nicht vorhanden - Insert mit Satzart und Zähler = 1
2. Satzart vorhanden - Update mit folgender Anweisung:
C/EXEC SQL
C+ update Datei set Zaehler = Zaehler + 1 where exists
C+ (select Zaehler from Datei where Satzart = :SATZART)
C/END-EXEC
Die Anweisungen werden korrekt ausgeführt jedoch wird der aktuelle
Zaehler nicht an das rufende Pgm. zurückgeben.
Wie muss ich "Zaehler" definieren?. Als Host-Variable im SubSelect ist das nicht erlaubt.
Wie verhält es sich mit konkurrierenden Zugriffen wenn ich
anschliessend einen select mache um den aktuellen Wert zu holen?
Gruss Joe
Dies kannst du nur mit Satzsperren erreichen:
declare MyCursor for
select Zaehler from Datei where satzart=:satzart for update
Open MyCursor
fetch MyCursor into :Zaehler
set Zaehler = Zaehler + 1
update Datei
set Zaehler = :Zaehler for current of MyCursor
close MyCursor
Damit wird ein konkurrierender Update verhindert.
Die SQL-Syntax erhebt keinen Anspruch auf vollständige Richtigkeit !
Hey Joe,
wie Jimi Hendrix das zu formulieren pflegte, dem Vorschlag von Baldur ist noch hinzuzufügen:
eine SQL Procedure wäre hier eine Alternative, die könnte updaten und den veränderten Wert zurück geben.
Bei dem SQL Vorschlag von baldur muss sicher gestellt sein, dass das isolation level nicht *none ist (kann man beim CRTSQL*** einstellen). Problematisch ist hier auch, dass die Sperrstufe zwischen lesen und schreiben eskaliert wird, was zum Sperrkonflikt führen kann.
Aus Sicht der Satzsperren besser wäre allerdings erst den Update zu machen, dann den Satz neu zu lesen und dann Commit. Unter isolation level > *NONE und *AUTO bleibt der Satz gesperrt bis zum Commit.
mfg
Dieter Bender
Funktioniert auch mit Isolation Level *none !
Der Vorteil bei meiner Methode ist allerdings, dass für Select und Update nicht 2 verschiedene Cursor bzw. ODP's verwendet werden müssen.
Desweiteren ist beim "Update => Select" ohne Commit nicht gewährleistet, dass der Select den um 1 erhöhten Wert liefert.
Auch die SQL-Procedure sollte eigentlich die Reihenfolge "Select for update" => "update current of" einhalten, da hier die Satzsperre unabhängig vom Commit-Level funktioniert.
Hallo nochmal,
die gültige Referenz sagt in der Tat, dass bei einem for update Cursor der Satz ohne Commit gesperrt ist (ich bin mir nicht sicher, ob das immer so war?). Problem ist allerdings immer noch, dass nach dem lesen share read erlaubt wird. Wenn dann ein zweiter Prozess ebenfalls mit gleicher Mimik liest, kriegt keiner von beiden die Eskalation der Sperre zum Update durch und der erste stirbt nach der Satzwartezeit (idiotische 60 sec. im default).
Dies wird vermieden mit dem "Blind update" unter commit Steuerung, der den Satz ändert und sperrt in einem, das anschliessende Lesen bringt dann den Inhalt.
Das Sperrhandling mit SQL unterscheidet sich im Kleingedruckten ein wenig von dem mit Record Level Access aus dem RPG oder COBOL.
mfg
Dieter Bender
Aus diesem Grunde sollte man ja die Sperren eines Zyklus nicht über Bildschirmeingaben hinaus halten, der Benutzer könnte ja gerade in Pause gehen.;)
Wenn also der "Select for Update" die Erhöhung und der "Update for current of" in eine Routine (egal ob SQL-Procedure, Service-Programm, o.ä.) gekapselt sind werden die Wartezeiten nicht auftreten und somit auch ohne Commit funktionieren.
Da hier ja die Erhöhung (z.B. Auftrags-/Rechnungs-Nr. o.ä.) im Vordergrund steht sollte kein anderer Zugriff auf diese Information erfolgen.
Wenn ich mir nun einen Commit-Zyklus vorstelle, dann ist das Problem der Satzsperre auch noch verschärft, da ja dieser Zähler in anderen Dateien sicherlich Verwendung finden wird.
Wenn nun der Zähler von einem anderen Prozess während einer Sperre gelesen und verwendet wird ist das ein schwerer Design-Fehler, da der 1. Prozess, der die Sperre hält, immerhin noch eine Rollback-Operation durchführen kann und der vom 2. Prozess gelesene Zähler dann falsch ist.
Was dann ?:confused:
ps:
Die Reihenfolge für die Erhöhung ist sowieso egal (es ist nur relevant, was performanter ist), da ich sowieso nicht weiß ob ein 2. Prozess den Wert kurz vor oder nach der Erhöhung liest (das ist wie mit der Katze und dem Gas im geschlossenen Würfel, tot oder nicht tot).
Hallo nochmal zum "Privat Thread"
Sperren über eine Benutzertransaktion hinaus:
Völlig einverstanden, eine Datenbanktransaktion darf nie länger als eine Benutzertransaktion sein, d.h. es dürfen keine Sperren gehalten werden beim EXFMT. Aber genau deshalb ist der Verzicht auf Commit riskant. Ist der Cursor updatebar, wird der letzte Satz festgehalten, es sei denn man macht einen Dummy update, oder schließt den Cursor. Unter Commit macht man vor dem EXFMT schnöderweise einen Rollback, (Transaktionsmonitore, wie CICS machen sowqas automatisch.
Das mit dem SELECT for Update stimmt leider nicht, da die Leseoperation sich keine ausreichende Sperre zum update holt, sondern nur soweit sperrt, dass andere nicht dürfen. Das heisst wenn zwei fast zeitgleich lesen, dann hakts beim Versuch des updates. Nun ist es gerade so, dass Sätze, die einen Schlüsselwert zum Hochzählen speichern, konkurrierend benötigt werden und da tritt der Lock Konflikt beim Versuch des Updates auf. Bei der umgekehrten Logik, wie von mir vorgeschlagen, Hat der erste sofort die volle Sperre und der zweite wartet die 3 Millisekunden bis der erste seine erhöhte Nummer gelesen hat und den Satz frei gibt.
Mir ist beim blossen lesen der SQL Reference das auch nicht so klar gewesen, geklingelt hat es dann, als Sperrkonflikte bei einer Keyverwaltung aufgetreten sind.
mfg
Dieter Bender
@Dieter
Hierzu sollten wir vielleicht mal eine Umfrage machen:
Wer entwickelt Anwendungen mit SQL und nutzt auch die Vorteile der Journalisierung ?
Oder wird SQL nur verwendet, weil man dann mit LVLCHK-Fehlern nichts mehr am Hut hat und im wesentlichen eigentlich nur Recordlevel-Access programmiert wird ?
Egal wie, ein "Schmutziger" Select ist allemal möglich !
Hallo "Forum"
Danke für die aufschlussreichen Ausführungen.
Es gibt also keine Möglichkeit, die :Hostvariable bei
der VAriante mit SUbselect zu erhalten...
Gruss
Joe