FB: Unique Index auf Datum und Uhrzeit

[IBExpert]

was man wissen sollte:

Primärschlüssel und Unique Constraints werden von Firebird auch ohne commit auf Eindeutigkeit geprüft.

Kann man relativ einfach verstehen wenn man mit IBExpert oder isql 2 Instanzen öffnet und auf einer
Tabelle mit PK oder unique constraint in beiden Sitzungen einen identischen Insert macht, ohne den
zu committen.

Ablauf Beispiel auf einer leeren Tabelle mit PK auf der ID Spalte
create table tab(id bigint not null primary key);

1. Client 1: Select * from Tab where id=1;
2. Client 1: Select ausführen starten automatisch eine Transaktion für diese connection
3. Client 1: Weil die Tabelle beim Start leer war kommt kein Ergebnis

4. Client 2: Select * from Tab where id=1
5. Client 2: Select ausführen starten automatisch eine Transaktion für diese connection
6. Client 2: Weil die Tabelle beim Start und immer noch leer ist kommt kein Ergebnis

7. Client 1: insert into tab(id) values (1);
8. Client 1: Befehl wird fehlerfrei ausgeführt, aber Transaktion ist noch aktiv.
9. Client 1: Select * from Tab where id=1;
10.Client 1: In dieser Session sieht Client 1 den selbst erzeugten Datensatz auch wenn noch kein commit erfolgt ist

11. Client 2: Select * from Tab where id=1;
12. Client 2: Obwohl in der Tabelle schon ein Datensatz von Client 1 enthalten ist, sieht client 2 den nicht, weil der noch nicht committed ist (Stichwort MGA Multigenerations Architektur)
13. Client 2: Da kein Datensatz sichtbar ist versucht Client 2 den anzulegen
14. Client 2: insert into tab(id) values (1);
15. Client 2: Befehl wird sofort mit Fehler beendet, weil schon eine PK Verletzung vorliegt, auch wenn der konkurrierende Datensatz noch nicht committed ist.

Technisch wird der PK Wert schon in den Unique Indexbaum eingetragen, auch ohne commit. Wenn da schon einer ist, knallt es bei Unique.

Genau für solche Zwecke wurden die Sequenzen/Generatoren entworfen. Über den serverseitig serialisierten Aufruf der Funktion GEN_ID() wird Erhöhen, zurückschreiben und Ergebnis anzeigen unabhängig von Transaktionen netzwerksicher implementiert.

Zurück zur Ausgangsfrage Timestamp als Unique: CURRENT_TIMESTAMP unterstützt maximal Millisekunden. Also sind maximal 1000 unterschiedliche Werte pro Sekunde möglich. Die Auflösung der Zeitfunktionen unter Windows ist aber wesentlich geringer, man liest im Schnitt von 15ms und das entspricht auch meiner Erfahrung. Wer Spaß dran hat kann ggf eigene Datentypen nutzen und eine nanosekunden genaue Zeit über assembler funktionen abfragen (geht ggf auch als udf), löst aber das Problem nicht, weil auch da kein Unique garantiert werde kann.

Einen Zeitstempel als Unique anzunehmen ist bei langsam eintrudelnden Daten von nur einer Quelle durchaus denkbar, aber im Netzwerkbetrieb und bei schneller Datenerzeugung der falsche Weg (nicht nur unter Firebird)