J'essaie de mettre en œuvre votre fonctionnalité de base upsert, mais avec une torsion: parfois, je ne veux parfois pas mettre à jour une ligne existante.
essentiellement, j'essaie de synchroniser certaines données entre différents référentiels et un La fonction upsert semblait être la voie à suivre. Tellement basé en grande partie sur Réponse de Sam Saffron à cette question , ainsi que d'autres recherches et de la lecture, j'ai proposé cette procédure stockée: p>
(Remarque: J'utilise MS SQL Server 2005, la déclaration de fusion n'est donc pas une option) p >
UPDATE dbo.Item WITH (SERIALIZABLE)
SET Title = @pTitle,
Teaser = @pTeaser
WHERE ContentID = @pContentID
AND RowLocked = false
5 Réponses :
Vous pouvez changer l'ordre de la mise à jour / insérer. Donc, vous faites l'insert dans un essai / prise et si vous obtenez une violation de contrainte, faites la mise à jour. Il se sent un peu sale.
J'ai toujours pensé que vous n'étiez pas censé compter sur des gestionnaires d'erreur pour un traitement "normal", c'est-à-dire que je sais qu'un cas d'utilisation typique augmentera une exception, je devrais alors vérifier cette condition et le gérer avant de soulager une exception. Donc, je suis d'accord, cela se sente un peu sale;) Si je peux obtenir le niveau d'isolement à droite (je lis toujours), la logique est assez simple - mais je perds l'avantage initial de l'upsert (c.-à-d. Pas de lecture supplémentaire de DB) .
J'ai giflé le script suivant pour prouver que ce truc que j'ai utilisé depuis des années passées. Si vous l'utilisez, vous devrez la modifier pour répondre à vos besoins. Commentaires Suivez: L'astuce Voici que vous pouvez définir des valeurs dans des variables locales dans une déclaration de mise à jour. Ci-dessus, la valeur "drapeau" n'est définie que si la mise à jour fonctionne (c'est-à-dire que les critères de mise à jour sont satisfaits); Sinon, cela ne sera pas changé (ici, laissé à NULL), vous pouvez vérifier cela et traiter en conséquence. comme pour la transaction et la rendant sérialisable, j'aimerais en savoir plus sur Ce qui doit être encapsulé dans la transaction avant de suggérer comment procéder. - Addenda, suivi du deuxième commentaire ci-dessous ----------- M. . Les idées de safran sont une manière complète et solide de mettre en œuvre cette routine car vos touches principales sont définies à l'extérieur et passées dans la base de données (c'est-à-dire que vous n'utilisez pas de colonnes d'identité - bien par moi, elles sont souvent surutilisées). < P> J'ai fait des tests supplémentaires (ajouté une contrainte de clé primaire sur la colonne contestation, enveloppez la mise à jour et insérez dans une transaction, ajoutez l'indice Serializable à la mise à jour) et oui, cela devrait faire tout ce que vous voulez. La mise à jour échouée gifle un verrouillage de plage sur cette partie de l'index et bloquera toutes les tentatives simultanées d'insérer cette nouvelle valeur dans la colonne. Bien entendu, si n demandes sont soumises simultanément, le "premier" créera la ligne, et il sera immédiatement mis à jour par le deuxième, troisième, etc., à moins que vous ne définissiez le "verrou" quelque part sur la ligne. Bon tour! (Notez que sans l'index sur la colonne de la clé, vous verrouillez la table entière. En outre, le verrouillage de la plage peut verrouiller les lignes de «chaque côté» de la nouvelle valeur - ou Peut-être qu'ils ne le feront pas, je n'ai pas testé celui-là. Je n'aurais pas d'importance, puisque la durée de l'opération devrait [?] être en millisecondes à un chiffre.)
Pour mentionner, dans le code d'exemples d'origine, vous mettez à jour l'élément de table, mais inséré dans la table MailItem; Les upsers ne sont-ils pas censés être appliqués contre la même table?
Les noms de table incompatibles sont une faute de frappe (maintenant corrigée). Je savais que vous pouviez définir une variable locale avec une sélection, mais je ne l'ai jamais essayée avec une mise à jour, de sorte que cela pourrait simplement faire l'affaire. En ce qui concerne la transaction sérialisable, ma compréhension (certes imparfaite) est que si vous n'utilisez pas une sorte de verrouillage, vous pouvez obtenir des violations de contraintes clés uniques et que «la mise à jour (sérialisable)» est correctement modifiée. Je travaille à partir de l'exemple de la question liée (ci-dessus) et je lis toujours / essayer de vous assurer que je comprends exactement ce que cela fait.
Mise à jour de ma réponse avec des commentaires sur le commentaire ci-dessus.
@Philip, merci pour la mise à jour et le langage simple sur les indices et les serrures. Cependant, lorsque j'ai testé, j'ai réalisé votre approche en utilisant "si @ @check est NULL" est fonction de fonctionnalité équivalente à l'original "if @@ rowcount = 0" car @check n'aura qu'une valeur si la ligne est mise à jour. Lorsque la ligne existe mais n'est pas mise à jour, @check est NULL et je reçois une violation de contrainte unique de l'insert ultérieur. Donc, votre code fonctionne comme décrit, cela ne résout pas mon problème;) Néanmoins, cela a contribué, donc +1.
Créer la procédure [DBO]. [USP_UPSERTITEM]
- Ajouter les paramètres de la procédure stockée ici
@pcontentid Varchar (30) = NULL,
@ptitle Varchar (255) = null,
@paser Varchar (255) = null
COMME
COMMENCER
- Définissez Nocount ajouté pour éviter des ensembles de résultats supplémentaires de
- Interférer avec des déclarations de sélection.
Définissez Nocount sur; fin
Horrible code horrible! Non seulement utilisez-vous une requête deux fois pour la même opération conditionnelle, mais vous utilisez "sinon sinon pas" quand un simple "autre" ferait. Voir la réponse de Cptskippy pour un meilleur exemple.
Je suis d'accord avec l'autre solution est plus propre, mais horrible, horrible ... je montais dans la bonne direction, non?
BEGIN TRANSACTION
IF EXISTS(SELECT 1 FROM dbo.Item WHERE ContentID = @pContentID)
UPDATE dbo.Item WITH (SERIALIZABLE)
SET Title = @pTitle, Teaser = @pTeaser
WHERE ContentID = @pContentID
AND RowLocked = false
ELSE
INSERT INTO dbo.Item
(ContentID, Title, Teaser)
VALUES
(@pContentID, @pTitle, @pTeaser)
COMMIT TRANSACTION
Qu'est-ce qui est rowlocked in (et wrococked = false)? Est-ce une colonne de votre table?
Je souligne testé ce que j'ai compris comme votre approche, et cela ne tient pas en parfaite simultanément.
Je déposerais la transaction.
plus le @@ RowCount fonctionnerait probablement, mais utiliser des variables globales comme une vérification conditionnelle entraînera des bugs.
juste faire un chèque existant (). Vous devez faire passer une passe à travers la table, de toute façon, la vitesse n'est pas la question.
Pas besoin de la transaction aussi loin que je peux voir.
Le motif de mise à jour / insertion avec RowCount n'est sécurisé que parce qu'il utilise sérialisable pour verrouiller jusqu'à ce que l'insert. Sinon, l'insert pourrait être en conflit avec une tentative simultanée de la mise à jour qui correspond également à aucune ligne de lignes, puis double insertion provoque des lignes en double ou une erreur de clé en double si vous avez des clés uniques.
Qu'est-ce qui est rowlocked in (et wrococked = false)? Est-ce une colonne de votre table?
@ALEXKUZNETSOV - Oui, Wardlocked est censé être une colonne de table; En réalité, il y a quelques colonnes qui dictent si une ligne doit être "verrouillée" (c'est-à-dire non mise à jour par cette procédure) mais j'ai simplifié ma SQL pour essayer de rendre ma question plus claire; Vous avez un peu bâclé avec la syntaxe cependant - il devrait bien sûr être "et wrococked = 0" et j'aurais dû mentionner que c'est une colonne de bit.