[oracle] fehler bei "auto-increment" mit trigger & seque

[s.h.a.r.k]

guten morgen erst mal,

ich bin in letzter zeit auf ein merkwürdiges phenomän in der oracle datenbank gestoßen. kurz zur erläuterung des sachverhalts: und zwar habe ich zwei tabellen, tbl1 und tbl2. bei der ersten tabelle suche ich mir meine id selbst - immer jeweils den nächst höheren wert - , d.h. ich lasse die id nicht automatisch von der datenbank vergeben. bei tbl2 habe ich ein "auto-increment" eingerichtet. ich habe auf die schnelle hier eine beispiel dazu im internet gefunden: link.

hier aber auch noch meni verwendeter code für trigger und sequenz:

SQL-Code:

-- Sequence
CREATE SEQUENCE seqTest MINVALUE 1

-- Trigger
CREATE OR REPLACE TRIGGER trigTest
  BEFORE
    INSERT
  ON tblTest
  FOR EACH ROW
begin
  select seqTest.nextval into :new.id from dual;
end;

nun ist das folgende passiert: ein anwender hat sich über die reihenfolge der datensätze beschwert, da diese nicht in gewöhnlicher reihenfolge angezeigt wurde. dies habe ich nicht wirklich verstanden, da diese ja eigentlich nach der id von tbl2 sortiert angezeigt werden sollten. nach einiger zeit bin ich auf das folgend gestoßen:

SQL-Code:

-- SQL
SELECT vid, id
FROM elz_laufzettel
WHERE vid = 1457
   OR vid = 1458
   OR vid = 1459
ORDER BY vid

-- Ausgabe
VID                    ID
---------------------- ----------------------
1457                   5978
1457                   5979
1458                   5575
1458                   5576
1458                   5577
1458                   5578
1458                   5579
1458                   5580
1458                   5980
1458                   5573
1458                   5574
1459                   5584
1459                   5585
1459                   5586
1459                   5587
1459                   5582
1459                   5581
1459                   5583

die vid ist die id aus tbl1 und die id ist die id aus tbl2. rein theoretisch, und genau das ist es was ich nicht verstehe, müssten die ids ja auch immer größer werden und nicht variieren, wie sie wollen. das problem dabei ist dass das ja nicht immer der fall ist und ich komme absolut nicht drauf warum das so passiert. ich bin da grad leicht überfragt, warum der trigger was falsch machen sollte

ps: ich ändere die id von tbl2 sonst nirgends! sie wird nur beim insert gesetzt und dient sonst nur zur identifikation eines datensatzes.

[mkinzler]

Eigentlich sollte ein PK auch nicht mehrfach vorhanden sein. Wann/wie wird id erhöht/gesetzt?

[marabu]

Moin,

bei einem RDBMS darfst du keine Annahmen über die Ordnung in einer Menge machen, wenn du sie nicht selbst über die ORDER BY Klausel steuern kannst.

Wenn die ID für alle Datensätze mit gleicher VID vom selben Trigger gesetzt wird, d.h. dieser Trigger nicht nur einen einzelnen Datensatz bearbeitet, dann hast du damit schon die Erklärung. Es werden dann zwar fortlaufende ID-Werte eingesetzt, aber die Reihenfolge der übergebenen Datensätze kontrollierst nicht du.

Grüße vom marabu

[ULIK]

Ein Verdacht ins Blaue hinein (aufgrund der Daten): kann es sein, dass die Datensätze mit den 59xx Nummern eigentlich richtig erstellt wurden, dann aber die ID Nummern bei einem Update der VID neu gesetzt wurden (vileicht gibt es irgendwo noch einen Update-Trigger, oder der Datensatz wird gelöscht und mit den neuen Daten neu eingefügt).
Schaut irgendwie so aus wie: Benutzer legt Auftragsdaten an, löscht danach eine Auftragsposition und fügt stattdessen eine neue ein.

HTH,
Ulrich

[s.h.a.r.k]

weiter details zur weiteren erklärung: ich habe eine anwendung, die mir das folgende macht:
- zuerst wird die höchste id in tbl1 gesucht und um 1 erhöht, somit habe ich die nächste id
- dazu gibt es einen datensatz, der in tbl1 landet
- zu diesem einen datensatz gibt es bis zu 15 datensätze, welche in tbl2 landen. bei diesen datensätzen soll die id automatisch gesetzt werden, eben über einen trigger und eine passende sequenz für tbl2 (dazu siehe obigen sql-code). eine spalte, namens vid, referenziert den einen datensatz in tbl1.

die datensätze werden per transaktion in die datenbank geschrieben., d.h. erst der insert in die tbl1 und dann die mehreren inserts in tbl2. später erfolgt keinerlei update von id in tbl1, oder id in tbl2, geschweige denn von vid ind tbl2.

soweit klar, wie das ganze funktioniert? mein problem besteht eben nun darin, dass die automatisch id-vergabe in meinen augen falsch abläuft und ich weiß nicht wieso, da ja der code vom trigger einen fehler verursachen müsste, es aber nicht immer macht! füge ich einen weiteren datensatz ein, auch beim gleichen, bei dem der fehler aufgetreten ist, dann macht der trigger das richtig. ich bin echt am verzweifeln...

@marabu: ich ordne die werte ja selbst per ORDER BY, ich überlasse das freilich nicht dem zufall. wie du der oberen result-tabelle entnehmen kannst liegt es ja auch nicht daran, sondern an der evtl. falschen vergabe der id für tbl2.

[s.h.a.r.k]

Zitat von ULIK:

Ein Verdacht ins Blaue hinein (aufgrund der Daten): kann es sein, dass die Datensätze mit den 59xx Nummern eigentlich richtig erstellt wurden, dann aber die ID Nummern bei einem Update der VID neu gesetzt wurden (vileicht gibt es irgendwo noch einen Update-Trigger, oder der Datensatz wird gelöscht und mit den neuen Daten neu eingefügt).
Schaut irgendwie so aus wie: Benutzer legt Auftragsdaten an, löscht danach eine Auftragsposition und fügt stattdessen eine neue ein.

HTH,
Ulrich

nein, das habe ich mir auch schon gedacht, dass ich irgendwo ein update oder dergleichen mache, aber die sql-queries habe ich alle durchgeschaut. daher meine ratlosigkeit.

weitere trigger gibt es nicht, nur den einen für tbl2, natürlich auch weitere für andere tabellen, aber die haben mit diesem nichts zu tun und nutzen auch andere sequenzen.

nur kurz zur klarstellung für mich: wenn ich mir den obigen code anschaue dann wird doch jedes mal beim abruf des aktuellen sequenz-wertes dieser erhöht, beispielsweise von 4 auf 5. der trigger, der den wert 4 geholt hat fügt diesen als id bei einem insert ein. weitere tätigkeiten werden nicht vollzogen?! ich glaub langsam echt ich spinne

[Elvis]

Oracle Sequences besitzen einen Cache (default ist IMO 20).
Wenn eine Session die Sequence zm ersten Mal anfasst, wird diese dann X weitere Werte in den Sessionspeicher laden.
Wenn du danach mit einer 2. Session darauf zugreifst könnte das unterschiedlich sortierte IDs ergeben.
Außerdem unterliegen Sequences niemals Transaktionen. Die Änderungen sind immer sofort für alle sichtbar (muss auch, sonst gäbe es doppelte IDS )

• Session #1 greift auf die Sequence zu
  • Wert: 1
  • Sequence wird um 20 erhöht.
• Session #2 greift auf die Sequence zu
  • Wert: 21
  • Sequence wird um 20 erhöht.
• Session #2 greift wieder auf die Sequence zu
  • Wert: 22
  • Sequence wird nicht um 20 erhöht, hat ja noch weitere 18.
• Session #1 greift wieder auf die Sequence zu
  • Wert: 2
  • Sequence wird nicht um 20 erhöht, hat ja noch weitere 18.
• ...

Wenn mehrere Transaktionen parallel laufen, wird es ständig solche Überschneidungen geben.
Die Alternative wäre ein kleinerer Cache (Minimum ist IMO 2), aber das wäre eher dumm, da das ständige Sperren der Sequence Zeit kosten würde, die je nach Datendurchsatz durchaus bemerkbar wäre.

[s.h.a.r.k]

ich habe aber keine anwendungen die parallel die ids schreiben würden. dies macht nur eine anwendung, eben die server-anwendung meines systems. die client-software arbeitet nur mit den daten, üebrscheibt aber nie irgendwas über fügt neue ids hinzu.

[marabu]

Hallo,

Zitat von s.h.a.r.k:

... ich ordne die werte ja selbst per ORDER BY, ich überlasse das freilich nicht dem zufall. ...

nein - das tust du nicht. Die Reihenfolge der Datensätze in der Pseudo-Tabelle NEW kontrolliert einzig und allein die DB-Engine.

Zitat von s.h.a.r.k:

... wie du der oberen result-tabelle entnehmen kannst liegt es ja auch nicht daran, sondern an der evtl. falschen vergabe der id für tbl2.

die von dir gezeigte Ergebnismenge belegt lediglich meine Vermutung, dass mehrere ID-Werte durch denselben Triggeraufruf besetzt werden, wie es bei transaktionsgestützter Verarbeitung oft vorkommt.

Noch einmal: Der Effekt der nach deiner Wahrnehmung ungeordneten Vergabe von ID-Werten ist auf die Tatsache zurückzuführen, dass kein RDBMS eine Ordnung auf Mengen garantiert. Diese Grundeigenschaft hat bereits Dr. Codd formuliert und sie hat noch eine andere Folge: Du darfst auch nie davon ausgehen, dass eine einmal beobachtete Anordnung von Tupeln bei identischer Widerholung der Anfrage zur exakt gleichen Anordnung führt.

Die Nummern wurden sequentiell aufsteigend vergeben, die Reihenfolge der Sätze kannst du an den Nummern erkennen.

Freundliche Grüße

[ULIK]

Kannst Du in die DB einen neuen Trigger einfügen, der Dir Änderungen an der Tabelle mitprotokolliert? Also einen After Insert/Update/Delete Trigger erstellen, der den jeweiligen Zeitpunkt und die ID VID in eine zweite Tabelle wegschreibt. Oder vieleicht protokollierst Du ja eh schon irgendwo den Zeitpunkt des Inserts mit und kannst schauen, wann die Datensätze denn so eingefügt wurde. Vieleicht sieht man dann irgendwas.

Grüße,
Ulrich