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