Odpowiedź

SEQUENCE (na przykład w PostgreSQL i Oracle) — specjalny obiekt bazy danych, który przechowuje bieżącą wartość i pozwala na uzyskiwanie unikalnych liczb do wstawienia (zwykle — kluczy). IDENTITY — właściwość pola, automatycznie przypisująca następne po kolei wartości przy wstawieniu (autoincrement tablo w SQL Server, MySQL, PostgreSQL).

Kluczowe różnice:

SEQUENCE — obiekt niezwiązany z tabelą, dostępny z różnych miejsc, można uzyskać następną wartość bez wstawiania (nextval / currval). Używany dla różnych tabel, może być wiele SEQUENCE dla jednej tabeli.
IDENTITY — część definicji kolumny, automatycznie zwiększa się tylko przy wstawianiu wiersza.

Przykład:

-- 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 zostanie przypisane automatycznie

Problemy i niuanse:

Jeśli ręcznie wstawiać id, mogą wystąpić "dziury" lub kolizje.
Przy usuwaniu wierszy — wartości nie są ponownie używane, możliwe jest przerwanie w zakresie kluczy.
W niektórych DBMS (na przykład MySQL) przy wycofaniu transakcji autoincrement może "skoczyć do przodu", pozostawiając niezmienioną wartość.
SEQUENCE można używać w kilku tabelach, ale to komplikuje utrzymanie unikalności.

Pytanie z haczykiem

Co się stanie, jeśli jednocześnie dwa zapytania poproszą o nextval() z jednego SEQUENCE? Czy możliwe jest uzyskanie nieunikalnych wartości?

Odpowiedź: Nie, SEQUENCE gwarantuje unikalność. Każde zapytanie otrzymuje unikalną wartość, nawet przy równoczesnym dostępie. Jednak kolejność może nie odpowiadać kolejności wstawienia z powodu równoległości.

Przykład:

-- Pierwszy wątek otrzymuje nextval=100, drugi po chwili — nextval=101.
-- Nawet jeśli później pierwszy to wszystko cofnie, wartość 100 jest już używana.

Historia nr 1

Po migracji bazy z MySQL do PostgreSQL zapomniano o różnicach między SEQUENCE a IDENTITY: próbowano ręcznie wstawiać id, co doprowadziło do błędu "duplicate key" przy automatycznej generacji. Poprawiono to, całkowicie przenosząc kontrolę kluczy na SEQUENCE.

Historia nr 2

Przy częściowym usuwaniu wierszy z tabeli z autoincrement pewnego razu potrzebowano "ściśle" wypełnić zakres id. Z powodu niekontrolowanego odbudowywania nowych wierszy powstały niejednoznaczności — klucze stały się niespójne z logiką biznesową, co zmusiło do przejścia na osobną SEQUENCE w celu kontroli.

Historia nr 3

Z powodu braku kontroli nad SEQUENCE w dużym systemie OLTP numer zamówienia przekroczył 2,147,483,647 i spowodował awarię w integracyjnym API, ponieważ nie było ono przygotowane na wartości bigint. Zapytanie zostało ponownie zaimplementowane z kontrolnymi wartościami granicznymi SEQUENCE.

Jak działają i czym się zasadniczo różnią generacja i przetwarzanie sekwencji (SEQUENCE/IDENTITY) przy automatycznym tworzeniu unikalnych kluczy w SQL? Jakie potencjalne problemy mogą wystąpić?

Odpowiedź

Pytanie z haczykiem