IDENTITY-Poblem (SQL Server 2016)
-
Für meine umfangreiche Dokumentenverwaltung verwende ich die kostenlose Developer Edition des Microsoft SQL Server 2016. Die Dokumente sollen fortlaufend nummeriert werden. Dafür hat die Tabelle ein Integer Feld ID als Primärschlüssel mit IDENTITY-Eigenschaft.
So weit so gut.
Ab und zu kommt es aber vor, daß nicht fortlaufend nummeriert wird, sondern das Lücken entstehen. Heute vergab die DB mit 4464 Einträgen einem Neueintrag nicht wie geplant/gewünscht 4465 sondern 5321!
Kann mir jemand dieses Verhalten erklären? Und wie kann ich dieses Verhalten abstellen?
PS: Archiv- und Websuche brachten keine Ergebnisse.
-
Die Zähler von IDENTITY Spalten werden bei abgebrochenen Transaktionen nicht zurückgesetzt.
Hast du vielleicht mal erfolglos versucht etliche hundert Zeilen in die Tabelle einzufügen?
-
Nein, täglich werden niemals mehr als ca. 20 Neueinträge vorgenommen. Und die jeweils erfolgreich, also ohne Fehler.
hustbaer schrieb:
Die Zähler von IDENTITY Spalten werden bei abgebrochenen Transaktionen nicht zurückgesetzt.
Hast du vielleicht mal erfolglos versucht etliche hundert Zeilen in die Tabelle einzufügen?
-
Hallo
Solange keiner explizit den Identity verändert hat, ist die einzige Möglichkeit wie hustbaer schon geschrieben hat, das Zeilen erzeugt wurden, dabei ner die Transaktion abgebrochen wurde. Dies könnte auch durch Dreadlocks passieren. Ich würde, wenn vorhanden, die Logs der Software nochmal durchforsten.
Ansonsten könnte es wenn dann nur noch jemand explizit gesetzt haben, das geht über den Befehl checkident, siehe hier.
Was noch gehen würde ist ein seit identity_insert, wobei ich hier nicht sicher bin, ob der Identity mit verändert wird.
Grus Marco
-
Benni07 schrieb:
Nein, täglich werden niemals mehr als ca. 20 Neueinträge vorgenommen. Und die jeweils erfolgreich, also ohne Fehler.
Dann kann ich erstmal keine Erklärung anbieten.
-
Falls eine eindeutige, lückenfreie Durchnummerierung wichtig ist, ist IDENTITY nicht das richtige. Das garantiert keine direkt aufeinander folgende Nummerierung (nur das die neue Id größer als die alte ist). Eine solche Garantie ergibt auch keinen Sinn (Transaktionen etc. wurden schon genannt).
Übrigens ist das Verhalten auch bei anderen Serverdatenbanken so (z.B. Oracle).
Falls man eine solche Durchnummerierung braucht, muss man diese selbst vornehmen und sicherstellen das die Vergabe nicht durch Transaktionen beeinflusst werden kann.
Marc-O schrieb:
Was noch gehen würde ist ein seit identity_insert, wobei ich hier nicht sicher bin, ob der Identity mit verändert wird.
Nein, wird es nach meiner Erfahrung nach nicht. Nach einem händischen Setzen der Id musste ich immer anschließend "DBCC CHECKIDENT" aufrufen.
-
Für einmalige Probleme kann man den Identity-Seed auch mit Hand ändern.
Löst aber das grundlegende "Problem" nicht, dass man sich nicht auf die "Dichtheit" von IDENTITY Werten verlassen kann.
-
asc schrieb:
Falls man eine solche Durchnummerierung braucht, muss man diese selbst vornehmen und sicherstellen das die Vergabe nicht durch Transaktionen beeinflusst werden kann.
Hast du für diese Vorgehensweise vielleicht weiteres Infomaterial?
Mein Projekt habe ich in C# mit ADO.NET umgesetzt.
-
Benni07 schrieb:
Hast du für diese Vorgehensweise vielleicht weiteres Infomaterial?
Nein, nur Projekterfahrungen. Und mit etwas wie deinen Fall bin ich bei meinem ersten Projekt konfrontiert wurden. Unser damaliger Projektleiter wollte die Id ebenso für eine direkt fortlaufende Nummerierung verwenden, die aber nicht einmal durchgehend fortlaufend war sondern auch noch Jahres- und Datenbezogen in unterschiedlichen Nummernkreisen vergeben werden musste.
In dem Fall konnten wir den PL davon abbringen, auch weil Teile des Nummernkreises abhängig vom Status des Datensatzes waren und bei der Erzeugung ggf. noch nicht bekannt waren (sofern der Anwender nicht die Rechte hatte alle nötigen Daten gleich mitzuliefern). Da wurde es eine eigene Spalte, die erst im Nachgang gefüllt wurde (Und wenn ich mich recht erinnere auch mit Hilfe einer Tabellensperre zum Zeitpunkt des Bestimmens und Setzens der Nummer - Ist aber nun auch gute 15 Jahre her).
-
Das Problem sollte doch aber häufiger auftreten. In der Buchführung zum Beispiel müssen Belege zwingend fortlaufend lückenlos nummeriert werden. Merkwürdig, dass es für diese Problemstellung keine sinnvolle Lösung gibt.
-
Benni07 schrieb:
Das Problem sollte doch aber häufiger auftreten. In der Buchführung zum Beispiel müssen Belege zwingend fortlaufend lückenlos nummeriert werden. Merkwürdig, dass es für diese Problemstellung keine sinnvolle Lösung gibt.
Und wo steht, das diese Nummerierung zwangsläufig in der Id-Spalte geschehen muss? Oder das diese Nummerierung von der Datenbank durchgeführt wird?
Eine Variante die vermutlich funktionieren würde:
1. Tabellensperre setzen
2. Datensatz speichern, Nummer mit MAX(<Spalte>) + 1 setzen. (Bei mehr als einen Datensatz, z.B. zusammenhängende Tabellen, dies zwangsweise innerhalb einer Transaktion ausführen).
3. Tabellensperre entfernenNachteil: Nicht geeignet für eine Massendatenbearbeitung.
-
Marc-O schrieb:
Dreadlocks
-
Mal ganz etwas anderes: Ist die lückenlose Nummerierung bei Belegen wirklich verpflichtend? Bei Rechnungen scheint dies nämlich nicht der Fall zu sein.
-
Benni07 schrieb:
Das Problem sollte doch aber häufiger auftreten. In der Buchführung zum Beispiel müssen Belege zwingend fortlaufend lückenlos nummeriert werden. Merkwürdig, dass es für diese Problemstellung keine sinnvolle Lösung gibt.
Ja, merkwürdig
Ist dir klar warum es dabei konzeptuell ein Problem gibt?
Klar kann man es lösen, aber jede Lösung hat Nachteile die man sich nicht eintreten will wenn es nicht nötig ist. Und da man es eben doch nicht so häufig wie von dir angenommen braucht...
-
asc schrieb:
Mal ganz etwas anderes: Ist die lückenlose Nummerierung bei Belegen wirklich verpflichtend? Bei Rechnungen scheint dies nämlich nicht der Fall zu sein.
In der Buchhaltung ist lückenlose Nummerierung bei Belegen wirklich verpflichtend.
-
hustbaer schrieb:
Benni07 schrieb:
Das Problem sollte doch aber häufiger auftreten. In der Buchführung zum Beispiel müssen Belege zwingend fortlaufend lückenlos nummeriert werden. Merkwürdig, dass es für diese Problemstellung keine sinnvolle Lösung gibt.
Ja, merkwürdig
Ist dir klar warum es dabei konzeptuell ein Problem gibt?
Klar kann man es lösen, aber jede Lösung hat Nachteile die man sich nicht eintreten will wenn es nicht nötig ist. Und da man es eben doch nicht so häufig wie von dir angenommen braucht...Welche konzeptuellen Probleme meinst du?
-
Muss die fortlaufende Nummerierung sofort gesetzt werden, oder reicht es wenn es zu einem Zeitpunkt X durchgeführt wird?
Dann würde ich eine weitere nummerische Spalte die Null enthalten kann anlegen, und einen SELECT oder eine STP schreiben die alle Datensätze bei denen die Spalte NULL ist mit dem jeweiligen Maximum + 1 füllt.
-
@Benni07
Naja...
Sagen wir so ... es kommt drauf an wie man es betrachtet.Dinge wie Belege sind komplexe Objekte die aus mehreren Einzelteilen zusammengesetzt sind. Beim Anlegen kann daher an verschiedenen Stellen 'was schief gehen. (Du kannst nen Beleg nicht mit einem einzigen INSERT anlegen, der hat ja vermutlich noch Belegzeilen usw.)
Dann muss man zurückrollen. Um sicherzustellen dass dabei keine Löcher durch das Freiwerden der bereits "vergebenen" Nummer entstehen, muss man die ganze Transaktion in der so ein Beleg angelegt wird (und die Nummer vergeben) mit Isolation Level "serializable" machen. Weil ja sonst eine zweite Transaktion gleichzeitig laufen könnte, in der dann die nächste Nummer vergeben wird. Und wenn das erlaubt ist, und die 1. Transaktion abbricht, dann entsteht eben das verbotene Loch. *Das, also die Transaktion "serializable" machen, will man meistens nicht. Weil es praktisch verhindert dass Transaktionen gleichzeitig laufen können. Bzw. gibt es sogar DBs die es nichmal können.
Also muss man irgendwie rumtricksen. z.B. so wie es asc vorgeschlagen hat. Nur damit erlaubt man dann Belege die erstmal gar keine Nummer haben. Wobei man damit vermutlich noch leben kann. Man müsste halt definieren dass ein Beleg der keine Nummer hat einfach nicht existiert.
-
asc schrieb:
Muss die fortlaufende Nummerierung sofort gesetzt werden, oder reicht es wenn es zu einem Zeitpunkt X durchgeführt wird?
Dann würde ich eine weitere nummerische Spalte die Null enthalten kann anlegen, und einen SELECT oder eine STP schreiben die alle Datensätze bei denen die Spalte NULL ist mit dem jeweiligen Maximum + 1 füllt.
Die fortlaufende Nummerierung soll unmittelbar bei der Erfassung eines neuen Dokuments erfolgen. Pro Werktag sind das zurzeit zwischen 2 bis max. 25 Einträge.
-
Benni07 schrieb:
Pro Werktag sind das zurzeit zwischen 2 bis max. 25 Einträge.
Das ist super überschaubar wenig. Dafür reicht es wenn du das Gesamte Anlegen des Belegs/... in eine Transaktion packst, die Nummer per "MAX(Spalte) + 1" vergibst und einen Unique-Index auf die Spalte setzt. Wenn du dabei als Isolation-Level zumindest "READ COMMITTED" verwendest (und mindestens das ist glaube ich bei jedem RDBMS Standard) können so nie Löcher entstehen.
Was maximal passieren kann, ist dass das Anlegen eines Belegs schief geht. Wenn zwei solche Vorgänge gleichzeitig ausgeführt werden. Bei "READ COMMITTED" würden dann beide Versuchen die selbe Nummer für den neuen Beleg zu vergeben, aber dank des Unique Index auf die Spalte lässt das DBMS das nicht zu, und wirft dir eine der beiden Transaktionen mit nem Fehler zurück. Und dann machst du halt nen Retry.
-
Endlich mal was Brauchbares. Das probiere ich am Wochenende mal aus.
Danke schön!
hustbaer schrieb:
Benni07 schrieb:
Pro Werktag sind das zurzeit zwischen 2 bis max. 25 Einträge.
Das ist super überschaubar wenig. Dafür reicht es wenn du das Gesamte Anlegen des Belegs/... in eine Transaktion packst, die Nummer per "MAX(Spalte) + 1" vergibst und einen Unique-Index auf die Spalte setzt. Wenn du dabei als Isolation-Level zumindest "READ COMMITTED" verwendest (und mindestens das ist glaube ich bei jedem RDBMS Standard) können so nie Löcher entstehen.
Was maximal passieren kann, ist dass das Anlegen eines Belegs schief geht. Wenn zwei solche Vorgänge gleichzeitig ausgeführt werden. Bei "READ COMMITTED" würden dann beide Versuchen die selbe Nummer für den neuen Beleg zu vergeben, aber dank des Unique Index auf die Spalte lässt das DBMS das nicht zu, und wirft dir eine der beiden Transaktionen mit nem Fehler zurück. Und dann machst du halt nen Retry.
