La Violation de la contrainte de CLÉ UNIQUE sur INSÉRER OÙ COUNT(*) = 0 sur SQL Server 2005
Je suis pour l'insertion dans une base de données SQL à partir de plusieurs processus. Il est probable que le processus sera parfois essayer d'insérer des données en double dans la table. J'ai essayé d'écrire la requête d'une manière qui va gérer les doublons mais j'ai toujours:
System.Data.SqlClient.SqlException: Violation of UNIQUE KEY constraint 'UK1_MyTable'. Cannot insert duplicate key in object 'dbo.MyTable'.
The statement has been terminated.
Ma requête ressemble à quelque chose comme:
INSERT INTO MyTable (FieldA, FieldB, FieldC)
SELECT FieldA='AValue', FieldB='BValue', FieldC='CValue'
WHERE (SELECT COUNT(*) FROM MyTable WHERE FieldA='AValue' AND FieldB='BValue' AND FieldC='CValue' ) = 0
La contrainte " UK1_MyConstraint dit que dans MyTable, la combinaison des 3 champs doit être unique.
Mes questions:
- Pourquoi ne pas ce travail?
- Quelle modification dois-je faire si il n'y a pas de chance de faire une exception en raison de la violation de la contrainte?
Remarque que je suis au courant qu'il existe d'autres approches pour résoudre le problème initial de la "INSERTION si n'existe pas" comme (en résumé):
- À l'aide de TRY CATCH
- S'il n'EXISTE PAS INSÉRER (à l'intérieur d'une transaction sérialisable isolement)
Dois-je utiliser l'une des approches?
Edit 1 SQL de Création de Table:
CREATE TABLE [dbo].[MyTable](
[Id] [bigint] IDENTITY(1,1) NOT NULL,
[FieldA] [bigint] NOT NULL,
[FieldB] [int] NOT NULL,
[FieldC] [char](3) NULL,
[FieldD] [float] NULL,
CONSTRAINT [PK_MyTable] PRIMARY KEY NONCLUSTERED
(
[Id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON),
CONSTRAINT [UK1_MyTable] UNIQUE NONCLUSTERED
(
[FieldA] ASC,
[FieldB] ASC,
[FieldC] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON)
)
Edit 2 Décision:
Juste de mettre à jour ce - que j'ai décidé d'utiliser le "JFDI" la mise en œuvre suggérée dans la question (lien). Même si je suis toujours curieux de savoir pourquoi la mise en œuvre d'origine ne fonctionne pas.
- Connexes: stackoverflow.com/questions/3407857/...
- Je n'ai rien d'utile à ajouter, mais votre réputation actuelle (1234) est la même que la combinaison de mes bagages.
- J'espère que vous allez garder vos bagages, la combinaison de la synchronisation que ma réputation changements. Vous pourriez avoir besoin pour obtenir une nouvelle serrure si ma réputation n'est jamais à 5 chiffres 🙂
- Je pense que @Mike Cheel l'a, ou, au moins, a identifié une condition qui peut provoquer cela. Si BValue ou CValue sont nulles, votre SELECT COUNT(*) renvoie 0, à moins que ANSI_NULLS est ÉTEINT.
- Je suis toujours à l'aide de
WHERE NOT EXISTS
, et il fonctionne très bien pour moi. Je pense aussi que le problème pourrait être d'accepter les valeurs null dans les champs. - Juste pour être clair, je ne suis pas d'insérer les valeurs null (mais cela ne veut pas dire que d'autres personnes pourraient avoir ce problème à cause de l'insertion de valeurs null avec ANSI_NULLS être sur).
- Rien dans votre approche actuelle est la prévention de ces erreurs. En vertu de suffisamment de charge élevée, vous verrez.
- Smith: intéressant. Me semble que je n'ai pas pensé à cette même assez bien. Je suppose que je devrais revoir ces lieux et ajouter le TRY-CATCH comme dans le "JFDI" version...
- Connexes: stackoverflow.com/questions/639854/...
Vous devez vous connecter pour publier un commentaire.
Pourquoi ne pas ce travail?
Je crois que le comportement par défaut de SQL Server est la libération de verrous partagés dès qu'ils ne sont plus nécessaires. Votre sous-requête aura pour résultat une courte durée verrou partagé (S) sur la table, qui sera publié dès que la sous-requête terminée.
À ce stade, il n'y a rien pour empêcher un concurrent à l'opération à partir de l'insertion de la très la ligne que vous venez de vérifié n'était pas présent.
Quelle modification dois-je faire si il n'y a pas de chance de faire une exception en raison de la violation de la contrainte?
L'ajout de la
HOLDLOCK
astuce pour vos sous-requête donnera des instructions SQL Server à tenir sur le verrou jusqu'à ce que la transaction est terminée. (Dans votre cas, c'est une transaction implicite.) LeHOLDLOCK
indice est équivalent à laSERIALIZABLE
indice, qui lui-même est l'équivalent de la transaction sérialisable niveau d'isolation que vous évoquez dans votre liste des "autres approches".La
HOLDLOCK
allusion à elle seule serait suffisante pour retenir le verrou S et empêcher un concurrent à l'opération à partir de l'insertion de la ligne que vous marquez contre. Cependant, vous trouverez probablement votre clé unique d'erreur de violation d'remplacé par des blocages, survenant à la même fréquence.Si vous êtes en conservant seulement un verrou S sur la table, pensez à une course entre deux concurrentes tente d'insérer la même ligne, dans la même-à la fois réussir à acquérir un verrou S sur la table, mais elle ne peut réussir dans l'acquisition d'un verrou Exclusif (X) nécessaires à l'exécution de l'insert.
Heureusement, il existe un autre type de verrou pour ce scénario exact, appelé à la mise à Jour (U) de verrouillage. Le U lock est identique à un verrou S avec la différence suivante: alors que plusieurs verrous S peut être effectuée simultanément sur la même ressource, un seul U lock peut avoir lieu à un moment. (Dit d'une autre manière, tout en S serrures sont compatibles les uns avec les autres (c'est à dire peuvent cohabiter sans conflit), U les verrous ne sont pas compatibles les uns avec les autres, mais peuvent coexister aux côtés des verrous S; et plus loin le long du spectre, verrous Exclusifs (X) ne sont pas compatibles avec la S ou des verrous U)
Vous pouvez mettre à niveau l'implicite verrou S sur votre sous-requête pour un U verrouillage à l'aide de la
UPDLOCK
soupçon.Simultanées de deux tentatives d'insertion de la ligne dans la table va maintenant être sérialisé à la première instruction select, depuis cette acquiert (et titulaire d') un U lock, ce qui n'est pas compatible avec un autre U-lock, de la simultanées tentative d'insertion.
Les valeurs NULL
Un autre problème peut venir du fait que FieldC autorise les valeurs NULL.
Si
ANSI_NULLS
est sur (par défaut), alors le contrôle d'égalitéFieldC=NULL
serait de retour faux, même dans le cas où FieldC est NULL (vous devez utiliser leIS NULL
opérateur pour vérifier la valeur null lorsqueANSI_NULLS
est sur). Depuis FieldC est nullable, votre double vérification ne fonctionnera pas lors de l'insertion d'une valeur NULL.De traiter correctement les valeurs null, vous aurez besoin de modifier votre EXISTE des sous-requête à utiliser le
IS NULL
opérateur plutôt que de=
lorsqu'une valeur NULL est inséré. (Ou vous pouvez modifier la table de refuser les valeurs Null dans toutes les colonnes.)Documentation en Ligne SQL Server Références
RE: "je suis toujours curieux de savoir pourquoi la mise en œuvre d'origine ne fonctionne pas."
Pourquoi serait-il travailler?
Ce qui est là pour empêcher les deux transactions simultanées entrelacée comme suit?
La seule fois verrous en conflit seront prises est à la
Insert
scène.De voir dans le générateur de profils SQL
Create Table Script
Puis collez le ci-dessous en deux SSMS windows. Prendre note de l'spid des connexions (x et y) et mettre en place une Trace du générateur de profils de capture de verrouillage des événements et de l'utilisateur messages d'erreur. Appliquer des filtres de spid=x ou y et de gravité = 0 et ensuite d'exécuter des scripts.
Insérer Un Script
Sur le dessus de ma tête, j'ai un sentiment un ou plusieurs de ces colonnes accepte les valeurs null. Je voudrais voir l'instruction de création de la table, y compris la contrainte.
SELECT COUNT
bits en même temps (tous deux seS
verrous qui n'entrent pas en conflit), puis continuer à faire un insert avec les mêmes valeurs, à quel point, la seconde va échouer.