Dans MS SQL Server, est-il un moyen de “atomiquement” incrémenter une colonne qui est utilisé comme un compteur?

En supposant un Read committed d'isolation de transaction, c'est la déclaration suivante "atomique" dans le sens que vous ne jamais "perdre" un concurrent à l'incrément?

update mytable set counter = counter + 1

Je suppose que dans le cas général, où cette instruction de mise à jour fait partie d'une opération plus vaste, qu'il ne serait pas. Par exemple, je pense que ce scénario est possible:

  • mise à jour le compteur de transaction n ° 1
  • faire quelques autres trucs
    dans la transaction #1
  • mise à jour le compteur
    avec l'opération #2
  • commettre
    opération n ° 2
  • commit transaction n ° 1

Dans cette situation, ne serait pas le compteur de fin seulement être incrémenté de 1? Fait-il une différence si c'est la seule instruction dans une transaction?

Comment un site comme stackoverflow en occupera pour sa question, compteur de vue? Ou est la possibilité de "perdre" quelques incréments juste considéré comme acceptable?

InformationsquelleAutor Dan P | 2008-10-10
  1. 13

    Read committed traite uniquement avec les serrures sur la sélection de données à partir de tables.

    En t1 et en t2 cependant, vous êtes UPDATEing les données, ce qui est un scénario différent.

    Lorsque vous mettez à JOUR le compteur de remonter à un verrou d'écriture (sur la ligne), la prévention de la mise à jour de produit. t2 pu lire, mais t2 se bloque sur sa mise à JOUR jusqu'à ce que le t1 est fait, et t2 ne sera pas en mesure de s'engager avant de t1 (ce qui est contraire à votre timeline). Seulement une des transactions mettre à jour le compteur, donc à la fois permettra de mettre à jour le compteur tenu correctement le code présenté. (testé)

    • compteur = 0
    • t1 de mise à jour du compteur (compteur => 1)
    • t2 de mise à jour du compteur (bloqué)
    • t1 commit (compteur = 1)
    • t2 débloqué (pouvez maintenant mettre à jour le compteur) (compteur => 2)
    • t2 commettre

    Lire Commis juste signifie que vous ne pouvez lire commis des valeurs, mais cela ne signifie pas que vous avez des Lectures répétées. Ainsi, si vous utilisez et dépendent de la variable compteur, et ont l'intention de mettre à jour plus tard, vous êtes peut-être en cours d'exécution des opérations au mauvais niveau d'isolation.

    Vous pouvez soit utiliser un repeatable read lock, ou si vous avez seulement parfois, permettra de mettre à jour le compteur, vous pouvez le faire vous-même à l'aide d'un verrouillage optimiste technique. par exemple, une colonne de type timestamp avec le compteur de tableau, ou une condition de mise à jour.

    DECLARE @CounterInitialValue INT
DECLARE @NewCounterValue INT
SELECT @CounterInitialValue = SELECT counter FROM MyTable WHERE MyID = 1234

-- do stuff with the counter value

UPDATE MyTable
   SET counter = counter + 1
WHERE
   MyID = 1234
   AND 
   counter = @CounterInitialValue -- prevents the update if counter changed.

-- the value of counter must not change in this scenario.
-- so we rollback if the update affected no rows
IF( @@ROWCOUNT = 0 )
    ROLLBACK

    Ce devx article est instructif, bien qu'il parle de fonctions alors qu'ils étaient encore en version bêta, donc il ne peut pas être tout à fait précis.

    mise à jour: Comme la Justice indique, si t2 est une transaction imbriquée dans t1, la sémantique est différente. De nouveau, à la fois permettrait de mettre à jour le compteur correctement (+2) en raison de t2 du point de vue à l'intérieur de t1, le compteur a déjà été mis à jour à la fois. Le imbriquée t2 n'a pas accès à ce que le compteur a été avant de t1 à jour.

    • compteur = 0
    • t1 de mise à jour du compteur (compteur => 1)
    • t2 de mettre à jour le compteur de transaction imbriquée) (compteur => 2)
    • t2 commettre
    • t1 commit (compteur = 2)

    À une transaction imbriquée, si t1 questions de RESTAURATION après t1 COMMETTRE, compteur revient à sa valeur d'origine car il annule t2 de s'engager.

    • Dans votre premier exemple, vous mentionner que T2 pu lire, mais pourrait bloquer sur une écriture (logique). Cependant, une augmentation est une lecture et une écriture vous ne voulez pas lire alors sur le bloc de l'écriture sinon, vous devez lire les données validées (0), incrément (1), puis sur le bloc de l'écriture d'un 1 jusqu'à T1 avait écrit un 1. Je suppose que la base de données permet de bloquer la lecture ici puisque l'intention est de mettre à jour. Est-ce vrai?
    • Dans le premier exemple, il y a 2 requêtes dans les différentes opérations de mise à jour de la valeur (+1) en une seule instruction de mise à jour (par exemple, sans le lire avant la mise à jour). N requêtes d'effectuer la même counter=counter+1 mise à jour de bloc et les mises à jour se produisent de manière séquentielle comme la mise à jour de la déclaration escalade de la serrure déjà. Il y a des indicateurs de verrouillage et d'autres mécanismes disponibles si vous en avez besoin. par exemple, T1 d'émission SELECT counter FROM MyTable WITH(updlock) WHERE MyId = 1234 escalade de la serrure et permettrait d'éviter T2 à partir de la lecture jusqu'à ce que le T1 est commis / annulée.
    • est droit. Alors que les utilisateur n'est-ce pas le lire avant la mise à jour, la base de données est lecture avant la mise à jour. Une partie du plan d'exécution va lire la valeur courante, puis on incrémente la valeur en mémoire, puis il écrit la nouvelle valeur. Même en ajoutant des WITH(HOLDLOCK) à la mise à jour juste qu'il détient que l'initiale lire serrure - pour permettre à un autre processus de lire la valeur actuelle. Vous un test de l'absence de l'atomicité en ayant deux lots dans une boucle while constamment en cours d'exécution UPDATE MyTAble SET Counter=Counter+1
    • Je pense que vous pourriez être trompé. Je ne pense pas que j'ai jamais vu échouer lors de l'utilisation d'une transaction. Pour ce que ça vaut, je l'ai testé avec fortement controversé code de production et je suis convaincu que cela fonctionne pour moi. Je suis sûr que d'autres apprécieront que vous en ajoutant une réponse avec vos propres conclusions à l'aide de différents niveaux de transaction, etc. Merci
    InformationsquelleAutor Robert Paulson
  2. 26

    Selon le MSSQL Aider, vous pouvez faire comme ceci:

    UPDATE tablename SET counterfield = counterfield + 1 OUTPUT INSERTED.counterfield

    Cela permettra de mettre à jour le champ par un, et le retour de la valeur de mise à jour comme un jeu d'enregistrements SQL.

    InformationsquelleAutor scott-pascoe
  3. 3

    Non, il n'est pas. La valeur est lue en mode partagé, puis mises à jour en mode exclusif, donc plusieurs lectures peuvent se produire.

    Utiliser soit Sérialisable niveau ou utiliser quelque chose comme

    update t
set counter = counter+1
from t with(updlock, <some other hints maybe>)
where foo = bar
    InformationsquelleAutor GSerg
  4. 1

    Il est au cœur d'une seule transaction, de l'extérieur. Les transactions internes sont plus comme des points de contrôle au sein d'une transaction. Les niveaux d'Isolation affecter la seule de la fratrie ultrapériphériques de transactions, pas de parent/enfant transactions connexes.

    Le compteur sera incrémenté par deux. La suite de rendements d'une ligne avec une valeur de (Num = 3). (J'ai ouvert le SMS et le pointa d'un local SQL Server 2008 Express instance. J'ai une base de données nommée aire de Jeux pour tester des trucs.)

    use Playground

drop table C
create table C (
    Num int not null)

insert into C (Num) values (1)

begin tran X
    update C set Num = Num + 1
    begin tran Y
        update C set Num = Num + 1
    commit tran Y
commit tran X

select * from C
    • Je ne crois pas que la question était en train de parler sur les transactions imbriquées qui, comme vous l'indiquez, sont traitées différemment.
    InformationsquelleAutor yfeldblum