Séquence Rollback SQL Server 2012

Question

J'ai un objet Sequence de SQL Server 2012 :

/****** Create Sequence Object ******/
CREATE SEQUENCE TestSeq
START WITH 1
INCREMENT BY 1;

J'ai un SP qui exécute des requêtes dans une transaction :

BEGIN TRAN

SELECT NEXT VALUE FOR dbo.TestSeq

<here all the query update code......>

ROLLBACK TRAN

Si la transaction échoue, toutes les mises à jour sont annulées sans problème, mais la séquence n'est pas annulée, je suppose que c'est parce qu'elle est hors de la portée de la transaction.

Un indice sur la façon de gérer cela ?

Answer 1

Déclarez une table qui stocke tous les numéros de séquence et utilisez ensuite l'instruction comme celle donnée ci-dessous :

déclarer @SequenceNo int

    UPDATE MySequenceNumberTable
    SET @SequenceNo = SequenceNo = SequenceNo + 1
    WHERE SequenceName = 'Your sequence name'

Ici, une seule instruction incrémente la valeur, l'assigne à une variable et met à jour la table de la base de données, de sorte que son action atomique est garantie. Comme il s'agit d'une simple opération de mise à jour de la base de données, elle joue un rôle dans la transaction de la base de données. Si la transaction est annulée, la valeur du champ SequenceNo est rétablie à sa valeur précédente.

Le seul problème est que la transaction suivante qui demande la valeur suivante pour le même nom de séquence doit attendre que la première transaction soit validée ou annulée à cause du verrouillage au niveau de la ligne. Dans le cas d'une séquence DB, il n'y a pas de verrouillage de la séquence et elle ne bloque donc jamais les autres transactions nécessitant la valeur suivante.

Dans notre cas, nous ne pouvons tout simplement pas avoir de trous et nous avons besoin d'une protection contre la concurrence. Nous utilisons donc la méthode ci-dessus et cela fonctionne bien pour nous.

Answer 2

L'instruction ROLLBACK est utilisée de la manière suivante pour annuler une transaction entière :

BEGIN TRANSACTION
...
SQL Statement(s)

...
ROLLBACK TRANSACTION

La commande ROLLBACK peut également être utilisée pour annuler une partie d'une transaction de la manière suivante :

BEGIN TRANSACTION
...
SQL Statement(s)

SAVE TRANSACTION savepoint_name

SQL Statement(s)

ROLLBACK TRANSACTION savepoint_name