Identité SQL (numéro automatique) est incrémentée même avec une restauration de transaction

J’ai une transaction .net avec une insertion SQL dans une firebase database SQL Server 2005. La table a une clé primaire d’identité.

Lorsqu’une erreur survient dans la transaction, Rollback() est appelée. Les insertions de lignes sont restaurées correctement, mais la prochaine fois que j’insère des données dans la table, l’identité est incrémentée comme si l’annulation n’avait jamais eu lieu. Il y a donc essentiellement des lacunes dans la séquence d’identité. Est-il possible que la méthode Rollback() récupère l’identité manquante?

Est-ce que je ne m’approche pas de la bonne façon?

Si vous y réfléchissez, le nombre à incrémentation automatique ne doit pas être transactionnel. Si d’autres transactions devaient attendre pour voir si le numéro automatique allait être utilisé ou “annulé”, elles seraient bloquées par la transaction existante utilisant le numéro automatique. Par exemple, considérez mon code pseudo ci-dessous avec la table A en utilisant un champ de numéro automatique pour la colonne d’ID:

 User 1 ------------ begin transaction insert into A ... insert into B ... update C ... insert into D ... commit User 2 ----------- begin transaction insert into A ... insert into B ... commit 

Si la transaction de l’utilisateur 2 commence une milliseconde après l’utilisateur 1, son insertion dans la table A doit attendre la fin de la transaction de l’utilisateur 1 pour voir si le numéro automatique de la première insertion dans A a été utilisé.

Ceci est une fonctionnalité, pas un bug. Je recommanderais d’utiliser un autre schéma pour générer des nombres automatiques si vous en avez besoin pour être étroitement séquentiels.

Si vous dépendez de votre identité en tant que personne sans ressources, alors oui – vous le faites mal. L’intérêt d’une clé de substitution est de n’avoir aucune signification commerciale .

Et non, il n’y a aucun moyen de changer ce comportement (à moins de rouler votre propre auto-incrémentation et de subir les conséquences de blocage des autres insertions).

Vous obtenez des lacunes dans votre séquence si vous DELETE une ligne aussi.

Les séquences doivent être uniques, mais elles ne doivent pas nécessairement être séquentielles. Le fait qu’ils augmentent de façon monotone n’est qu’un coup de chance pour la mise en œuvre.

Autant que je sache, les lignes à insérer réclament l’autonumber et à l’annulation ce nombre est définitivement perdu. Si vous dépendez de cette numérotation automatique en cours de séquençage, vous pouvez envisager l’approche que vous utilisez.

Je ne pense pas qu’il soit nécessaire que les clés numérotées soient séquentielles. En fait, je ne pense pas qu’ils peuvent être obligés d’être:

  • transaction a commence et insère
  • la transaction b commence et insère
  • transaction a avorter

    vous obtenez un trou. rien à faire à ce sujet.

Toutes les autres affiches qui disent ne pas s’inquiéter de cela, et que vous devriez avoir des lacunes, ont raison. S’il y a une signification métier au nombre et que cette signification ne tient pas compte des lacunes, n’utilisez pas de colonne d’identité.

FYI, si pour une raison quelconque vous voulez supprimer les lacunes, la plupart des bases de données ont un moyen de ré-indexer la numérotation automatique au numéro de votre choix. C’est une douleur dans le cul, et si vous avez besoin de le faire régulièrement, vous ne devriez certainement pas utiliser un champ Numéro / identité, comme indiqué ci-dessus. Mais voici le code pour le faire dans SQL Server:

DBCC CHECKIDENT (‘Product’, RESEED, 0)

Cela définit la table de produit à démarrer à 1 (bien que si vous avez des enregistrements dans la table, elle sautera évidemment les valeurs d’ID déjà sockets.) Les autres fournisseurs de SGBDR ont leur propre syntaxe, mais l’effet est à peu près le même, alors recherchez “reseed identity” ou “reseed autonumber” dans les fichiers d’aide du système ou les internets.

Encore une fois: c’est pour des occasions spéciales, pas d’usage régulier. Ne le mettez pas dans une procédure stockée et faites-nous tous venir là-bas.

Muhan essaie d’y penser dans le contexte de nombreuses connexions simultanées exécutant cette transaction et non une par une. Certains échoueront et d’autres réussiront. Vous souhaitez que SQL Server se concentre sur l’exécution des nouvelles requêtes dès leur arrivée et non sur la gestion d’une colonne d’identité sans lacunes. IMO it (lacunes dans les valeurs) est certainement quelque chose ne vaut pas la peine de passer du temps.

Les implémentations de séquences utilisent une transaction autonome. Dans Oracle, la transaction autonome était une fois interne au dbms, mais est désormais exposée pour votre propre usage (et est souvent utilisée de manière incorrecte)

 PRAGMA AUTONOMOUS_TRANSACTION;'