SQL Server: problème de concurrence d'appel en t-sql?

Question

Bonjour à tous

J'ai l'instruction insert suivante:

/* table avec colonne id qui est primary key */
INSERT INTO TABLE(id)
  SELECT @new_id
  WHERE @new_id IS NOT NULL
  AND NOT EXISTS (
    SELECT NULL
    FROM TABLE
    WHERE id = @new_id)

J'ai aléatoirement (et non à chaque appel de l'instruction), des erreurs pour violation de primary key.

Comment est-il possible que la clause "where not exists" soit respectée car @new_id n'est pas trouvé et que l'insert plante parcequ'il y a déjà id=@new_id dans la table!!!??? Je ne comprends pas du tout...
Est-ce un problème de concurrence d'appel? Quelle solution avons nous à notre disposition pour éviter cela?

En vous remerciant, Thomas

Answer 1

Bonjour

Vous êtes sur que l'erreur c'est ici?

Pouvez-vous vérifier le code qui s'exécute en cas que rien n'est insérée dans la table?

Cordialement,

---

Aurel BERA, Microsoft
Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

Answer 2

Pour ce problème un complément d'information est nécessaire :

1. Dans quel niveau d'isolation cette requête s'exécute elle
2. Y a t'il des possibilité d'exécution en parallèle 

Cette erreur est possible dans le niveau d'isolation par défaut de SQL Server (READ COMMITED) car la requête :

INSERT INTO MaTable
SELECT MAX(Id) + 1 FROM MaTable

s'exécute (surement) en deux temps :

1. Récupération du résultat du select
2. Insertion

Donc si entre 1 et 2 une nouvelle transaction exécution de select se fait dans une seconde transaction, le même résultat sera récupéré et donc il y aura un problème au niveau de l'insertion. 

Je n'ai pas réussi à mettre en évidence cela via une trace.

Une solution, un champ IDENTITY dans PK

Answer 3

Bonjour Aurel,

Oui j'ai réalisé ces tests, l'erreur est bien decrite

Answer 4

Bonjour de nouveau

Utiliser un champ Identity(auto-incrément) n'est pas acceptable pour vous?

Merci

Cordialement,

---

Aurel BERA, Microsoft
Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

Answer 5

Bonjour,

Le niveau,d'isolation est celui par défaut, dois-je le changer juste pour cette requête?

J'ai essayé de rajouter with(tablockx):

select 1 from matable with(tablockx), je n'ai pas revu le problème depuis mais j'ai peur que cela ait trop d'impact sur les performances. Qu'en pensez-vous?

Answer 6

Bonjour Aurel,

Un autoincrement n'est pas possible dans le contexte de dev: ce petit bout de code est commun à toutes nos procédures stockées, id étant un user, mais à fins statistiques on veut s'assurer qu'il n'ont qu'une entrée en table quand chacun d'entre eux exécute plusieurs dizaines de nos procédures stockées.

Answer 7

Poser un verrou à un coût léger, mais pas d'erreur d'unicité derrière. Si le système est massivement appelé et que la performance est le mot d'ordre, il est plus intéressant d'avoir un système qui catch ces erreurs d’unicités.

Après comme je disais dans ma réponse précédente, la meilleur solution pour avoir performance et l'absence d'erreur : Ajouter une colonne identity dans la PK qui permettra d'assurer l'unicité.

Answer 8

Pourquoi ne pas recevoir cet ID comme paramètre de la procédure stockée et le mettre comme l'Ud d'un utilisateur (si vous avez un système de login avec utilisateur/mot de passé) ou un GUID gérée par le système extérieur (Application Windows Forms/ASP.NET).

---

Aurel BERA, Microsoft
Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

Answer 9

Bonjour

Merci de nous tenir au courrent.

Cordialement,

---

Aurel BERA, Microsoft
Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.

Answer 10

Bonjour,

Le "with(tablockx)" règle effectivement le problème mais a également une incidence sur les performances. La performance étant la priorité nous allons nous allons utiliser le try catch.

Merci à tous pour l'intérêt porté à mon problème.

Answer 11

Merci de votre retour

Cordialement,

---

Aurel BERA, Microsoft
Microsoft propose ce service gratuitement, dans le but d'aider les utilisateurs et d'élargir les connaissances générales liées aux produits et technologies Microsoft. Ce contenu est fourni "tel quel" et il n'implique aucune responsabilité de la part de Microsoft.