Réponse

SEQUENCE (par exemple, dans PostgreSQL et Oracle) — un objet spécial dans la base de données qui stocke la valeur actuelle et permet d'obtenir des nombres uniques pour l'insertion (généralement des clés). IDENTITY — propriété d'un champ, attribuant automatiquement la valeur suivante lors de l'insertion (autoincrement tabulaire dans SQL Server, MySQL, PostgreSQL).

Différences clés :

SEQUENCE — objet en dehors de la table, accessible depuis différents endroits, il est possible d'obtenir la valeur suivante sans insertion (nextval / currval). Utilisé pour plusieurs tables, plusieurs SEQUENCE peuvent exister pour une table.
IDENTITY — partie de la définition de la colonne, augmente automatiquement uniquement lors de l'insertion d'une ligne.

Exemple :

-- 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 sera attribué automatiquement

Problèmes et subtilités :

Si des id sont insérés manuellement, des "trous" ou des duplications peuvent survenir.
Lors de la suppression de lignes — les valeurs ne sont pas réutilisées, il peut y avoir une rupture dans la plage de clés.
Dans certains SGBD (par exemple, MySQL) lors du rollback de transactions, l'autoincrement peut "sauter en avant", laissant la valeur inchangée.
SEQUENCE peut être utilisée dans plusieurs tables, mais cela complique le maintien de l'unicité.

Question piégeuse

Que se passe-t-il si deux requêtes demandent simultanément nextval() d'une même SEQUENCE ? Est-il possible d'obtenir des valeurs non uniques ?

Réponse : Non, SEQUENCE garantit l'unicité. Chaque requête obtient une valeur unique, même en cas d'accès simultané. Cependant, l'ordre peut ne pas correspondre à l'ordre d'insertion en raison du parallélisme.

Exemple :

-- Le premier thread obtient nextval=100, le second dans un instant — nextval=101.
-- Même si le premier annule ensuite, la valeur 100 est déjà utilisée.

Histoire n°1

Après le transfert de la base de MySQL à PostgreSQL, ils ont oublié les différences entre SEQUENCE et IDENTITY : ils ont essayé d'insérer manuellement des id, ce qui a entraîné une erreur "clé dupliquée" lors de la génération automatique. Ils ont corrigé cela en transférant entièrement la gestion des clés à SEQUENCE.

Histoire n°2

Lors de la suppression partielle de lignes d'une table avec autoincrement, il fut un jour nécessaire de "remplir étroitement" la plage d'id. En raison de la recréation incontrôlée de nouvelles lignes, des ambiguïtés sont apparues — les clés sont devenues incohérentes avec la logique métier, ce qui a conduit à la création d'une SEQUENCE séparée pour le contrôle.

Histoire n°3

En raison du manque de contrôle sur SEQUENCE dans un grand système OLTP, le numéro de commande a dépassé 2,147,483,647 et a provoqué une panne d'urgence dans l'API d'intégration, car elle n'était pas prête à des valeurs bigint. La requête a été réimplémentée avec des valeurs limites de SEQUENCE sous contrôle.

Comment fonctionnent et quelle est la principale différence entre la génération et le traitement des séquences (SEQUENCE/IDENTITY) lors de la création automatique de clés uniques en SQL ? Quels problèmes potentiels peuvent survenir ?

Réponse

Question piégeuse