Antwort

SEQUENCE (z. B. in PostgreSQL und Oracle) ist ein spezielles Datenbankobjekt, das den aktuellen Wert speichert und es ermöglicht, einzigartige Zahlen für Einfügungen (in der Regel Schlüssel) zu erhalten. IDENTITY ist eine Eigenschaft eines Feldes, die beim Einfügen automatisch den nächsten sequentiellen Wert zuweist (tabellarisches autoincrement in SQL Server, MySQL, PostgreSQL).

Hauptunterschiede:

SEQUENCE ist ein Objekt außerhalb der Tabelle, das von verschiedenen Orten zugänglich ist. Man kann den nächsten Wert ohne Einfügung abrufen (nextval / currval). Es kann mehrere SEQUENCE für eine Tabelle geben.
IDENTITY ist Teil der Spaltendefinition, die sich nur beim Einfügen einer Zeile automatisch erhöht.

Beispiel:

-- PostgreSQL SEQUENCE
CREATE SEQUENCE order_seq;
INSERT INTO orders(id, name) VALUES (nextval('order_seq'), 'First Order');

-- PostgreSQL IDENTITY
CREATE TABLE users (
  id BIGSERIAL PRIMARY KEY,
  username TEXT
);
INSERT INTO users(username) VALUES ('ivan');  -- id wird automatisch zugewiesen

Probleme und Feinheiten:

Wenn man manuell eine id einfügt, können "Lücken" oder Überschneidungen auftreten.
Bei der Löschung von Zeilen werden die Werte nicht wiederverwendet, was zu Lücken im Schlüsselbereich führen kann.
In einigen DBMS (z. B. MySQL) kann autoincrement beim Zurückrollen einer Transaktion "vorüberspringen" und einen unveränderten Wert hinterlassen.
SEQUENCE kann in mehreren Tabellen verwendet werden, was die Gewährleistung der Einzigartigkeit erschwert.

Fangfrage

Was passiert, wenn gleichzeitig zwei Abfragen nextval() aus einer SEQUENCE anfordern? Ist es möglich, nicht einzigartige Werte zu erhalten?

Antwort: Nein, SEQUENCE garantiert die Einzigartigkeit. Jede Abfrage erhält einen einzigartigen Wert, selbst bei gleichzeitigen Zugriffsversuchen. Die Reihenfolge kann jedoch aufgrund von Parallelität von der Reihenfolge der Einfügungen abweichen.

Beispiel:

-- Der erste Thread erhält nextval=100, der zweite kurz darauf nextval=101.
-- Selbst wenn der erste später alles zurücksetzt, wird der Wert 100 bereits verwendet.

Geschichte Nr. 1

Nach der Migration der Datenbank von MySQL zu PostgreSQL wurde das Verständnis der Unterschiede zwischen SEQUENCE und IDENTITY vernachlässigt: Es wurde versucht, manuell ids einzufügen, was zu einem Fehler "duplicate key" bei der automatischen Generierung führte. Es wurde behoben, indem die gesamte Schlüsselverwaltung auf SEQUENCE umgestellt wurde.

Geschichte Nr. 2

Bei der teilweisen Löschung von Zeilen aus einer Tabelle mit autoincrement war es einmal notwendig, den id-Bereich "dicht" zu füllen. Aufgrund der unkontrollierten Neuanlage neuer Zeilen entstanden Mehrdeutigkeiten – die Schlüssel wurden inkonsistent mit den Geschäftslogik-Prozessen, es war nötig, auf eine separate SEQUENCE zur Kontrolle zu wechseln.

Geschichte Nr. 3

Aufgrund fehlender Kontrolle über SEQUENCE in einem großen OLTP-System überschritt die Bestellnummer 2.147.483.647 und verursachte einen kritischen Ausfall im Integrations-API, da es nicht auf bigint-Werte vorbereitet war. Die Anfrage wurde erneut mit Kontrollgrenzwerten für SEQUENCE umgesetzt.

Wie funktionieren die Generierung und Verarbeitung von Sequenzen (SEQUENCE/IDENTITY) bei der automatischen Erstellung einzigartiger Schlüssel in SQL und was sind die wesentlichen Unterschiede? Welche potenziellen Probleme können auftreten?

Antwort

Fangfrage