Eigenartiger Effekt mit int-Primary Keys ?

[gmc616]

Hallo Delphi-Gemeinde,

ich habe eine Anwendung geschrieben, welche mittler weile seit über 10 Jahren mehr oder weniger erfolgreich läuft. Eher mehr ;-)

Ich nutze zum Erstellen der Primärschlüssel eine eigene Funktion, welche aus einer Primärschlüssel-Tabelle einen Integer-Wert ermittelt, diesen um Eins hoch zählt ... usw.
Die AutoIdent-Funktion des MsSQL-Servers nutze ich nicht, da meine Anwendung teilweise auch auf anderen Datenbanken (z.B. dBase, Clipper ...) läuft, welche AutoIdent-Funktionen nicht kennen.

Zum Löschen von Datensätzen gibt es in meiner Anwendung eine Regel: Der Primärschlüssel wird negativ gesetzt und somit ist der Datensatz als gelöscht markiert. Kunden-Anforderung! (Aufgeräumt wird später).

Code:

UPDATE tabelle SET id=ABS(id)*-1 WHERE id=:ID;

TransactionLook ist aktiviert.

Mit dieser Regel läuft meine Anwendung seit Anfang an eigentlich recht stabil.

Mittlerweile ist meine Anwendung soweit fortgeschritten, dass es in einigen Kundeninstallationen einen Primärschlüssel im 5 Mio-Bereich verwendet. Und natürlich ist die Anwendung mittlerweile so groß, dass es sehr schwer ist, SQL-Fehler zu identifizieren. Voll allem dann, wenn es sich um einen Folgefehler handelt.

Das Problem: Der SQL-Befehl:

Code:

UPDATE tabelle SET id=ABS(id)*-1 WHERE id=:ID;

, wobei ID mit "5366038" belegt ist, schlägt fehl, da ein doppelter Primärschlüssel nicht zugelassen ist.
Das heißt, es soll eine Datensatz gelöscht markiert werden, welcher bereits gelöscht markiert ist. Und tatsächlich existiert zwei Datensätze, jeweils mit einem positiven ID und einem "gleichen" negativen ID. Das das nicht klappt ist klar.

Interessanter Weise tritt dieser Fehler NUR im MSSQL-2008R2 und MSSQL-2012 auf und immer bei den gleichen Schlüsseln 5366038 bzw. 5365874.
Ich habe ca. 20 Installationen am laufen, die diesen Fehler produziert haben. Und es betrifft immer einen dieser beiden Schlüssel.

Ich habe das ganze Programm durchwühlt und nach einer Ursache im Code gesucht, konnte aber nichts finden.
Aber Tatsache, dass dieser Fehler immer bei den gleichen IDs auftritt, bringt mir zu der Überzeugung, dass der Fehler nicht in meinem Programm-Code zu suchen ist, sondern wo anders.

Von Visual Studio 20xy MFC-Klassen kenne ich das Phänomen, dass dort ein Integer-Wert festgelegt wurde, welcher quasi als NULL definiert ist. Das war ein Bug von M$, welcher irgendwie gefixt wurde.
So soll (wenn ich das richtig verstanden haben) der (z.B.) SQL-Befehl:

Code:

UPDATE tabelle SET id=ABS(id)*-1 WHERE id=5365874;

vom MSSQL-Server als

Code:

UPDATE tabelle SET id=ABS(id)*-1 WHERE id =NULL;

übersetzt/interpretiert worden sein.

Kann es sein, dass in den Delphi-ADO-Klassen ein ähnliches Phänomen existiert?

Meine IDE: Delphi-2009 auf MS-Server 2008-R2.
Die Parameter (:ID) werden mit SetParam gesetzt, nicht direkt im SQL-Statement.
Die Verbindung zum SQL-Server wird über einen eigenen ConnectionString per SQLOLEDB aufgebaut.

Habt ihr ähnliche Erfahrungen?

[hoika]

Hallo,
dass es "versteckte" Integer-Werte gibt, habe ich noch nie gehört.
Quelle ?

Dein Problem solltest du doch an Deinem Rechner nachstellen können?

Ich denke eher, dass du Ids doppelt vergeben hast.
Genau dafür ist ja das Identity zumindestens beim MS-SQL da.
Andere DBs haben halt andere Mittel, um eindeutige Werte zu erzeugen.

Zumindestens sollte jedes DB einen Unique Index anbieten,
wobei das bei Dir nicht geholfen hätte (wegen dem negativ machen).


Heiko

[Dejan Vu]

Vorschläge habe ich nicht, nur drei Fragen:
1. Wie markierst/löschst Du Fremdschlüssel?
2. Wieso setzt Du die ID auf einen negativen Wert, anstatt einfach ein 'Deleted'-Flag zu pflegen?
3. Welchen transaction Isolation level verwendest Du?

[Perlsau]

Nach der Beschreibung im Eröffnungsposting kann ich mir die Sache nicht anders erklären: Irgendwann wird ein PK negativ zu setzen versucht, wobei eine Kopie des Records mit dem positiven PK entsteht.

Vielleicht kann der DB-Server gar nicht anders handeln, wenn z.B. FKs in anderen Tabellen existieren, die auf diesen "positiven" PK deiner problematischen Tabelle verweisen, so daß dieser gar nicht gelöscht werden kann? Könntest du eventuell mal nachprüfen, ob irgendwelche FKs anderer Tabellen diese beiden problematischen Records über ihre PKs referenzieren?

Zusätzlich würde ich einmal versuchen, einen neuen Datensatz mit diesem problematischen PK anzulegen und danach zu löschen, und zwar debuggenderweise. Damit solltest du eigentlich punktgenau auf die Stelle stoßen, die den Fehler auslöst.

Beobachten oder gar nachvollziehen konnte ich dieses Verhalten bislang jedoch nicht (MsSQL, Firebird, MySQL). Wenn ich mal viel Zeit und Muse habe, probier ich's vielleicht mal aus ...

[himitsu]

Zitat:

Zumindestens sollte jedes DB einen Unique Index anbieten,
wobei das bei Dir nicht geholfen hätte (wegen dem negativ machen).

Er kann sich ja einen eigenen CHECK CONSTRAINT oder einen UNIQUE INDEX mit ABS() auf seine Indexspalten legen,
dann würde das Problem gleich beim zweiten ANLEGEN und nicht erst beim LÖSCHEN auffallen.

Zitat:

Ich nutze zum Erstellen der Primärschlüssel eine eigene Funktion, welche aus einer Primärschlüssel-Tabelle einen Integer-Wert ermittelt, diesen um Eins hoch zählt ... usw.

usw?

[gmc616]

Zitat:

Zitat von hoika (Beitrag 1329175)

Hallo,
dass es "versteckte" Integer-Werte gibt, habe ich noch nie gehört.
Quelle ?

Auch wenns keine Quelle ist, aber ein Beispiel: http://stackoverflow.com/questions/1...field-exchange

Das dieser Effekt/Bug tatsächlich existiert haben ich live gesehen!
Geschrieben ist das Ganze im VS2010 in der afxdb.h

Zitat:

Zitat von Dejan Vu (Beitrag 1329177)

Vorschläge habe ich nicht, nur drei Fragen:
1. Wie markierst/löschst Du Fremdschlüssel?

Was verstehst du unter Fremdschlüssel? Hier gibt es nur Primary Keys und die gehören alle "mir".

Zitat:

Zitat von Dejan Vu (Beitrag 1329177)

2. Wieso setzt Du die ID auf einen negativen Wert, anstatt einfach ein 'Deleted'-Flag zu pflegen?

Klar, hätte man auch machen können. Ich habe mich damals für das Negativ-setzen entschieden. Für das aktuelle Problem wäre ein deleted-Flag evtl. die bessere Lösung gewesen.
Aber die 5 Mio Datensätze davor machen auch keine Probleme. Und in diesen 5 Mio Datensätzen sind ca. 20% mit negativem ID. Ja, gelöscht wird viel! Ein Großteil davon durchaus berechtigt.

Zitat:

Zitat von Dejan Vu (Beitrag 1329177)

3. Welchen transaction Isolation level verwendest Du?

Gute Frage! Was passiert denn, wenn man

Delphi-Quellcode:

TAdoConnection.BeginTrans

ruft?

Zitat:

Zitat von himitsu (Beitrag 1329209)

Zitat:

Ich nutze zum Erstellen der Primärschlüssel eine eigene Funktion, welche aus einer Primärschlüssel-Tabelle einen Integer-Wert ermittelt, diesen um Eins hoch zählt ... usw.

usw?

Code:

// PseudoCode
TAdoCon.BeginTrans;

UPDATE nidents SET id=id+1 WHERE tbname=:TableName AND idname=:PkName;
SELECT id from nidents where tbname=:TableName AND idname=:PkName;

TAdoCon.CommitTrans;

Ein Unique Index über tbbame,idname existiert

[hoika]

Hallo,
nun das Bsp. beschreibt aber, dass der Nutzer die falsche Dummy-Variable benutzt hat.
Murks ist das trotzdem ...

Ich wollte doch aber wissen, ob du das nachstellen kannst.


Heiko

[Perlsau]

Zitat:

Zitat von gmc616 (Beitrag 1329467)

Zitat:

Zitat von Dejan Vu (Beitrag 1329177)

Wie markierst/löschst Du Fremdschlüssel?

Was verstehst du unter Fremdschlüssel? Hier gibt es nur Primary Keys und die gehören alle "mir".

Was ein Primary Key (PK, Primärschlüssel) ist, weißt du ja bereits: eine eindeutige ID pro Datensatz in derselben Tabelle. Ein Fremdschlüssel (Foreign Key, FK) ist ein Key, der meistens auf einen PK einer anderen Tabelle verweist. Wenn du z.B. eine Tabelle ARTIKEL hast, die neben PK, Preis, Artikelnummer usw. auch eine Spalte enthält, in der eine Kategorie vermerkt werden soll, wäre es ja unsinnig, jedesmal den Kategorien-Namen dort reinzuschreiben. Stattdessen erzeugt man einen Tabelle KATEGORIEN, spendiert dieser einen PK und trägt dort die verfügbaren Kategorien ein. In der Artikeltabelle wird dann in der Spalte KATEGORIE nur noch der PK der Kategorientabelle gespeichert. Das ist dann ein Fremdschlüssel, auch wenn er dir gehört.

Zitat:

Zitat von gmc616 (Beitrag 1329467)

Ja, gelöscht wird viel! Ein Großteil davon durchaus berechtigt.

Wird auch unberechtigt gelöscht? Wie darf man das verstehen?

Grundsätzlich halte ich auch weiterhin für problematisch, wenn man ohne ausreichende Grundkenntnisse versucht, funktionierende Anwendungen für ein produktives Umfeld schreiben zu wollen.

[Dejan Vu]

Zitat:

Zitat von gmc616 (Beitrag 1329467)

Das dieser Effekt/Bug tatsächlich existiert haben ich live gesehen!
Geschrieben ist das Ganze im VS2010 in der afxdb.h

Der Bezug zu ADO fehlt mir hier und deine Interpretation im Eingangspost ist eindeutig falsch. Du verwechselst RFX mit SQL-Server. Und selbst wenn -aus welchen Gründen auch immer- ein 'UPDATE tabelle SET id=ABS(id)*-1 WHERE id =NULL;' ausgeführt werden würde, würde rein gar nichts passieren. Daran (also am Schlangenöl) kann es also nicht liegen.

Zitat:

Zitat:

Zitat von Dejan Vu (Beitrag 1329177)

Vorschläge habe ich nicht, nur drei Fragen:
1. Wie markierst/löschst Du Fremdschlüssel?

Was verstehst du unter Fremdschlüssel? Hier gibt es nur Primary Keys und die gehören alle "mir".

Du scheinst nur rudimentäres Grundwissen über Datenbanken zu haben. Daher auch dein Glaube an Schlangenöl.

Zitat:

Zitat:

Zitat von Dejan Vu (Beitrag 1329177)

3. Welchen transaction Isolation level verwendest Du?

Gute Frage! Was passiert denn, wenn man

Delphi-Quellcode:

TAdoConnection.BeginTrans

ruft?

Auch hier erkennt man dein fehlendes Wissen.
Um diese Frage zu beantworten, verwende den Profiler, oder schau Dir die Eigenschaften der Connection an. Wenn Du weißt, wie der Isolationlevel ist, dann weißt Du auch, wie sich der Server beim konkurrierenden Lesen verhält.

Natürlich ist das ärgerlich und irgendwo ist der Wurm. Aber ich lege meine Hand ins Feuer, das der SQL-Server mit NULL-Werten richtig umgehen kann.

Zeig doch mal die Definition der Tabelle mit den doppelten Ids. Wichtig ist auch die Definition vom Index sowie die Definition der 'idents-Tabelle'.
Beim SSMS gibt es die Funktion 'Generate Script'. Dort musst Du dann die eine Tabelle auswählen und in den Eigenschaften (ein Button auf der 3. Seite) noch einstellen, das auch der Index geskriptet werden soll. Das stellst Du dann hier ein und dann schauen wir weiter.

Du kannst versuchen, dein Updatestatement sicher zu machen, indem Du die 'OUTPUT' Klausel verwendest (obwohl Du damit nicht mehr kompatibel zu dBase bist).

Code:

UPDATE nidents 
   SET id=id+1 
OUTPUT id
 WHERE tbname=:TableName 
   AND idname=:PkName;

Damit hast Du eine atomare Anweisung, die garantiert transaktionssicher ist. Allerdings sind Probleme aufgrund der Nebenläufigkeit i.a. zufälliger Natur, d.h. Du müsstest eher zufällige doppelte Schlüssel beobachten.

Auch wenn es zum Haareraufen ist: Meist sitzt der Fehler 80cm vom Bildschirm entfernt.

Nebenbei: Wer will heutzutage noch kompatibel zu dbase sein, wo es doch freie Datenbanken gibt?

[p80286]

Zunächst einmal, alle Schlüssel dienen der internen Verwaltung von Daten. Wer von extern darauf zugreift und diese manipuliert gehört geteert und gefedert.
Was deinen Fehler angeht, wenn der/die Schlüssel bereits existieren, dann arbeitet Dein Schlüsselgenerator eben nicht zuverlässig, da das nicht auftreten darf.

Was heißt überhaupt negativ setzen? id:=id*-1 oder id:=id or $F000000 ?
Und wieviele Bits haben Deine Integers eigentlich? Wenn auch DBase versorgt werden muß, konnte das eigentlich schon mit 32Bit-Werten umgehen?

Gruß
K-H

[jobo]

Zitat:

Zitat von baumina (Beitrag 1329478)

[OT]
Ganz ehrlich: Ich ärgere mich über dieses Verhalten und finde es nur zum :kotz:.
[/OT]

:thumb:

@gmc616
Hast Du auch den richtigen Code statt den Pseudocode?
Der Isolationlevel kann ein Problem sein, wenn auch nicht commitete Werte gelesen werden.
Was passiert, wenn Du eine AdoQuery auf ein neues Formular setzt und genau diese Statements ausführst?
Wie alt/neu sind Deine ADO Treiber und die der Kunden?
Was geschieht mit Werten, die über diese problematischen Werte hinausgehen?

[DeddyH]

Dem schließe ich mich an :thumb::kiss: (Letzteres ist rein platonisch gemeint, nicht dass da jemand falsche Schlüsse zieht).

[jobo]

Zitat:

Zitat von DeddyH (Beitrag 1329492)

Dem schließe ich mich an :thumb::kiss: (Letzteres ist rein platonisch gemeint, nicht dass da jemand falsche Schlüsse zieht).

Ich glaube, die "Aktualisierungen" eigenen sich hervorragend, um einen ganz falschen-wenn auch platonischen- Eindruck zu erzeugen. :oops:
Oder wollen wir uns doch mal treffen DeddyH?
:stupid:

[Daniel]

Ups. Mein Fehler - der Beitrag von DeddyH hätte mit abgetrennt werden müssen.
Sorry dafür. :oops:

[jobo]

Kein Problem meinetwegen, ist schließlich Karneval. Ich weiß natürlich nicht wie DeddyH das sieht.
:)
@Abtrennung: Hab ich nun auch endlich geschnallt, dass das Thema "Umgang" verlagert wurde.
http://www.delphipraxis.net/188167-e...rt-gebern.html

[nahpets]

Zitat:

Zitat von gmc616

Interessanter Weise tritt dieser Fehler NUR im MSSQL-2008R2 und MSSQL-2012 auf und immer bei den gleichen Schlüsseln 5366038 bzw. 5365874.
Ich habe ca. 20 Installationen am laufen, die diesen Fehler produziert haben. Und es betrifft immer einen dieser beiden Schlüssel.

Dies erscheint mir sehr befremdlich.

Gibt es Installationen mit diesen Datenbanken, bei denen

1. der Fehler nicht aufgetreten ist, also Sätze mit den IDs -5366038 und -5365874 existieren
2. und keine IDs 5366038 und 5365874 existieren
3. und die weitere ID-Vergabe korrekt verlief?

Wieviele Nutzer können gleichzeitig mit einer Datenbank arbeiten?
Single-User-Betrieb oder Multi-User-Betrieb?

Um besser Hilfestellung geben zu können, wäre es eventuell hilfreich, wenn Du uns eine Liste der genutzten Datenbanken geben könntest.
Für eine Problemlösung für unterschiedliche Datenbanken muss letztlich SQL-technisch der kleinste gemeinsame Nenner genutzt werden, um Dein Programm kompatibel zu allen genutzten Datenbanken zu halten.

***

Gehen wir nochmal von Deinem Code aus:

Aktuell sei ID = 5366037

Code:

// PseudoCode
TAdoCon.BeginTrans;

UPDATE nidents SET id=id+1 WHERE tbname=:TableName AND idname=:PkName;
SELECT id from nidents where tbname=:TableName AND idname=:PkName;

TAdoCon.CommitTrans;

Dann liefert das Select für ID den Wert 5366038.

Der Satz wird nun gelöscht:

Code:

UPDATE tabelle SET id=ABS(id)*-1 WHERE id=5366038;

ID ist nun -5366038.

Würde vom MSSQL-Server ausgeführt

Code:

UPDATE tabelle SET id=ABS(id)*-1 WHERE id =NULL;

so wäre ID weiterhin 5366038, dem Satz fehlt in diesem Fall nur die Löschmarkierung.

Eine ID-Dublette kann hier daher nicht entstanden sein.

Eine ID-Dublette kann nur dann entstehen, wenn obiger "PseudoCode" zweimal das gleiche Ergebnis liefert.

Da nicht bekannt ist, wie der tatsächliche Programmablauf erfolgt, könnte hier aber ein Problem vorliegen.

Wenn mehrere Benutzer (und / oder mehrere Threads) gleichzeitig diesen Code ausführen, kann es zu Dubletten kommen. Beim Lesen der ID per Select ist der mit Update gesetzte Wert noch nicht in der Datenbank festgeschrieben, theoretisch könnte ein zweiter User annähernd zeitgleich daher zum gleichen Ergebnis kommen.
Meiner Meinung nach wäre es sinnvoller, zuerst die ID in einer eigenen Transaktion in der Tabelle nidents zu erhöhen und erst nach dem Commit per SQL auszulesen, aber 100% sicher erscheint mir diese Lösung auch nicht.

Code:

// PseudoCode
TAdoCon.BeginTrans;
UPDATE nidents SET id=id+1 WHERE tbname=:TableName AND idname=:PkName;
TAdoCon.CommitTrans;
SELECT id from nidents where tbname=:TableName AND idname=:PkName;

Irritierend finde ich allerdings schon, dass der Fehler nur bei zwei Datenbanktypen mit zwei bestimmten IDs aufgetreten ist.

[p80286]

Zitat:

Zitat von nahpets (Beitrag 1329514)

Irritierend finde ich allerdings schon, dass der Fehler nur bei zwei Datenbanktypen mit zwei bestimmten IDs aufgetreten ist.

Das hake ich unter Zufall ab. Weit wichtiger ist, wie wird eine ID "negativiert", da gibt es ja mehrere Möglichkeiten, und auf welcher Basis wird eine neue ID berechnet.

(vor vielen Jahren war es Zeichen besonders cleveren Programmierens 16Bit-Words zu nutzen, die gehen ja bis 65.000 und soooviele Datensätze haben wir nieeee. Abgesehen von der etwas kurzsichtigen Sichtweise was die Datenmenge angeht, was passiert wenn auf diesen 16Bit-Wert mal als Word und mal als Integer zugegriffen wird??)


Gruß
K-H

[Dejan Vu]

Bei 20 Installationen ist das kein Zufall mehr. Es scheint doch so zu sein, das es bei allen Installationen auffällt. Ich tippe ja auch auf die Software selbst, aber das muss schon ein merkwürdiger Zufall sein.

Code:

Update Tabelle set Id=5366038 where ....

Gemein ist das in jedem Fall.

[p80286]

Zitat:

Zitat von Dejan Vu (Beitrag 1329528)

Bei 20 Installationen ist das kein Zufall mehr. Es scheint doch so zu sein, das es bei allen Installationen auffällt. Ich tippe ja auch auf die Software selbst, aber das muss schon ein merkwürdiger Zufall sein.

Code:

Update Tabelle set Id=5366038 where ....

Gemein ist das in jedem Fall.

DAS ist doch wohl nicht Dein Ernst!:shock:
ein

Code:

Insert into Tabelle (id) values(12345)

ginge ja zur not noch aber ein Update auf einen Datensatz der keinen Schlüssel hat??


Gruß
K-H

[Dejan Vu]

Nein, ich meine 'irgendetwas Gemeines'. Also etwas, das richtig fies ist. Gemein. Unerwartet. Zum-Hand-Gegen-die-Stirn-klatschen. Oder-Kopf-gegen-die-Mauer.

PS: Die Tabelle hat ja einen Schlüssel. Das 'Statement' (das es so ja bestimmt nicht gibt) kann ja anders aussehen, z.B.

Delphi-Quellcode:

//Anstatt 
Datensatz.Zahlenfeld := 5366038;
// Steht da eben
Datensatz.Id := BloedeFunktionDie5366038Ergibt();
Datensatz.Post;

Anders kann ich mir das nicht erklären. Oder er überschreibt Field.SetText. Oder. Oder. Oder.

Ärgerlich, sowas.

[hoika]

Hallo,
da fällt mir ein Fehler aus meiner Jugendzeit ein (Paradox-"Datenbank" mit 20 Nutzern im Netz).
Ab und zu waren die Indizes zerschossen und ich habe 3 Rechner parallel ständig Daten einfügen/löschen lassen,
konnte das aber nicht reproduzieren.

Der Isolation-Level spielt beim SQL-Server ja wirklich eine Rolle,
dass ist bei "meinem" FireBird viel einfacher.

Noch mal die Frage an den TE:
Das trat jetzt bei wie vielen Kunden/Installationen auf?


Heiko