Atomique UPSERT dans SQL Server 2005

Question

Est-ce le bon modèle pour faire atomique "UPSERT" (mise à JOUR où existe, INSÉRER autrement) dans SQL Server 2005?

Je vois beaucoup de code (par exemple, voir http://stackoverflow.com/questions/639854/tsql-check-if-a-row-exists-otherwise-insert) avec les deux parties ci-modèle:

UPDATE ...
FROM ...
WHERE <condition>
-- race condition risk here
IF @@ROWCOUNT = 0
  INSERT ...

ou

IF (SELECT COUNT(*) FROM ... WHERE <condition>) = 0
  -- race condition risk here
  INSERT ...
ELSE
  UPDATE ...

où < condition > fera l'objet d'une évaluation des clés naturelles. Aucune des méthodes ci-dessus semblent faire face à la concurrence. Si je ne peut pas avoir deux lignes avec la même clé naturelle, il semble que tous les ci-dessus risque de l'insertion de lignes avec le même naturel touches en condition de course des scénarios.

J'ai été en utilisant l'approche suivante, mais je suis surpris de ne pas voir partout dans les réactions des gens alors je me demandais quel est le problème avec elle:

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
   -- race condition risk here?
   ( SELECT 1 FROM <table> WHERE <natural keys> )

UPDATE ...
WHERE <natural keys>

Notez que la condition de la course mentionné ici est différente de celles utilisées dans le code antérieur. Dans le code précédent, la question était de lectures fantômes (les lignes étant inséré entre la mise à JOUR/SI ou entre le SELECT/INSERT par une autre session). Dans le code ci-dessus, la condition de la course a à voir avec la Supprime. Est-il possible pour une ligne correspondante à être supprimée par une autre session APRÈS l' (OÙ n'EXISTE PAS) s'exécute mais avant de l'INSÉRER exécute? Il n'est pas clair où la OÙ n'EXISTE PAS pose un verrou sur quoi que ce soit en conjonction avec la mise à JOUR.

Est-ce atomique? Je n'arrive pas à trouver où ce serait documentée dans la documentation de SQL Server.

EDIT: je me rends compte de ce qui pourrait être fait avec les transactions, mais je pense que j'aurais besoin de définir le niveau des transactions SERIALIZABLE pour éviter les fantômes problème? Sûrement, c'est trop pour un tel problème commun?

Answer 1

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
FROM <table>
WHERE NOT EXISTS
   -- race condition risk here?
   ( SELECT 1 FROM <table> WHERE <natural keys> )

UPDATE ...
WHERE <natural keys>

• il est une condition de concurrence dans la première INSERTION. La clé n'existe pas au cours de la intérieure requête SELECT, mais n'existe pas à l'heure d'INSERTION résultant de violation de clé.
• il est une condition de concurrence entre l'INSERT et UPDATE. La clé peut exister lorsque la case est cochée dans la requête interne de l'INSERT, mais est passé par la mise à JOUR en temps s'exécute.

Pour la deuxième condition de course on pourrait dire que la clé aurait été supprimé de toute façon par les threads, donc ce n'est pas vraiment une perte de mise à jour.

La solution optimale est généralement d'essayer la plupart des cas probable, et de gérer l'erreur si elle échoue (à l'intérieur d'une transaction, bien sûr):

• si la clé est probablement manquant, insérez toujours en premier. Gérer la violation de contrainte d'unicité, de secours à mettre à jour.
• si la clé est probablement présent, toujours mettre à jour en premier. Insérer si aucune ligne n'a été trouvé. Poignée de la possible violation de contrainte d'unicité, de secours à mettre à jour.

En plus de l'exactitude, ce modèle est également optimale pour la vitesse: est plus efficace pour essayer d'insérer et manipuler l'exception que pour faire de fausses blocages. Le blocage moyenne de page logique lit (ce qui peut signifier physique lectures de page), IO (même logique) est plus cher que le SEH.

Mise À Jour @Peter

Pourquoi n'est-il pas d'une simple déclaration "atomique"? Disons que nous avons un banal tableau:

create table Test (id int primary key);

Maintenant, si je veux faire fonctionner cette déclaration unique à partir de deux fils à la fois, dans une boucle, il serait "atomique", comme vous dites, un pas de condition de course peuvent exister:

  insert into Test (id)
    select top (1) id
    from Numbers n
    where not exists (select id from Test where id = n.id); 

Pourtant, en seulement quelques secondes, une violation de clé primaire se produit:

Msg 2627, Niveau 14, État 1, Ligne 4
La Violation de la contrainte de CLÉ PRIMAIRE "PK_Test_24927208'. Impossible d'insérer une clé en double dans l'objet 'dbo.Test".

Pourquoi est-ce? Vous avez raison de requête SQL, le plan permettra de faire la "bonne chose" sur DELETE ... FROM ... JOIN, WITH cte AS (SELECT...FROM ) DELETE FROM cte et dans de nombreux autres cas. Mais il y a une différence cruciale dans ces cas: le " sous-requête se réfère à l' objectif d'une mise à jour ou supprimer l'opération. Pour de tels cas, le plan de requête va en effet utiliser un verrou, en fait j'ai ce problème est critique dans certains cas, comme lors de la mise en œuvre de files d'attente à l'Aide de tableaux comme des Files d'attente.

Mais dans la question d'origine, ainsi que dans mon exemple, la sous-requête est considérée par l'optimiseur de requête comme une sous-requête dans une requête, et non pas comme une 'analyse pour la mise à jour" type de requête que les besoins spéciaux de protection de verrouillage. Le résultat est que l'exécution de la sous-requête de recherche peut être observé comme une opération distincte par un concurent observerver, brisant ainsi le 'atomique' comportement de la déclaration. À moins que des précautions particulières sont prises, plusieurs threads peuvent tenter d'insérer la même valeur, à la fois convaincus qu'ils avaient vérifié et la valeur n'est pas déjà existant. Un seul peut réussir, l'autre va frapper le PK violation. CQFD.

Answer 2

Passer updlock, verrou, holdlock conseils lors de la vérification de l'existence de la ligne. Holdlock s'assure que toutes les insertions sont sérialisés; rowlock permis de mises à jour simultanées aux lignes existantes.

Les mises à jour peuvent encore bloquer si votre PK est un bigint, comme l'intérieur de hachage est dégénérée pour les valeurs de 64 bits.

begin tran -- default read committed isolation level is fine

if not exists (select * from <table> with (updlock, rowlock, holdlock) where <PK = ...>
    -- insert
else
    -- update

commit

Answer 3

EDIT: Remus est correct, le conditionnel insérer w/ clause where ne garantit pas un état cohérent entre la sous-requête en corrélation et la table d'insérer.

Peut-être le droit indicateurs de table pourrait forcer un état cohérent. INSERT <table> WITH (TABLOCKX, HOLDLOCK) semble fonctionner, mais je n'ai aucune idée si c'est le niveau optimal de verrouillage pour une condition de l'insertion.

Dans un banal test comme celui de Remus décrit, TABLOCKX, HOLDLOCK a montré ~5x insérer le volume de aucun des indicateurs de table, et sans le PK des erreurs ou des cours.

RÉPONSE ORIGINALE À CETTE QUESTION, INCORRECT:

Est-ce atomique?

Oui, le conditionnel insérer w/ clause where est atomique, et votre INSERT ... WHERE NOT EXISTS() ... UPDATE forme est la bonne façon d'effectuer une UPSERT.

J'ajouterais IF @@ROWCOUNT = 0 entre l'INSERT et la mise à JOUR:

INSERT INTO <table>
SELECT <natural keys>, <other stuff...>
WHERE NOT EXISTS
   -- no race condition here
   ( SELECT 1 FROM <table> WHERE <natural keys> )

IF @@ROWCOUNT = 0 BEGIN
  UPDATE ...
  WHERE <natural keys>
END

Seul consolidés toujours exécuter au sein d'une transaction, soit de leur propre (validation automatique et implicite) ou avec d'autres états (explicite).

Answer 4

Un truc que j'ai vu, c'est d'essayer de l'INSÉRER et, si elle échoue, effectuez la mise à JOUR.

Answer 5

Vous pouvez utiliser les verrous d'application: (sp_getapplock) http://msdn.microsoft.com/en-us/library/ms189823.aspx