Gestion des problèmes de concurrence du serveur SQL

Question

J'ai parfois besoin d'acquérir un identifiant unique et de le stocker avec un enregistrement, mais je ne parviens pas à utiliser une colonne d'identité. J'ai donc une table qui fournit des identifiants uniques à l'aide d'un champ d'étiquette et d'un nombre entier. Lorsque j'ai besoin d'un identifiant unique, j'appelle une procédure stockée et je lui transmets l'étiquette, qui produit l'identifiant suivant qui lui est associé. Bien sûr, il est important que cette procédure soit fiable dans un environnement avec des transactions simultanées. En d'autres termes, la procédure stockée ne doit jamais renvoyer deux fois la même valeur pour une étiquette donnée. Ma compréhension limitée de l'isolation des transactions m'a conduit à faire ce qui suit :

1) Définir le niveau d'isolation des transactions sur sérialisable

2) SELECT id FROM UniqueIdTable WHERE label = @inputLabel

3) UPDATETEIdTable SET id = id + 1 WHERE label = @inputLabel

4) Renvoyer l'identifiant récupéré en 2)

Mais est-ce vraiment sûr ? N'est-il pas toujours possible pour deux threads d'exécuter simultanément jusqu'à l'étape 2), même avec une isolation sérialisable ? J'ai cru comprendre que le niveau d'isolation le plus élevé garantit seulement qu'une transaction unique s'exécutera sans avoir à subir des lignes fantômes ou à modifier des données provenant d'autres threads. Si c'est le cas, deux appels simultanés à la fonction GetID pourraient renvoyer la même valeur.

Ai-je mal compris quelque chose à propos des niveaux d'isolation ? Comment puis-je garantir que cela ne se produira pas ?

---

J'ai un autre problème à régler. Supposons que j'ai une table avec un champ qui contient des clés étrangères pour une deuxième table. Initialement, les enregistrements de la première table n'ont pas d'enregistrement correspondant dans la seconde, je stocke donc NULL dans ce champ. A un moment donné, un utilisateur exécute une opération qui génère un enregistrement dans la deuxième table et qui a un lien avec la première table. Il s'agit toujours d'une relation univoque, de sorte que si deux utilisateurs tentent simultanément de générer l'enregistrement, un seul enregistrement est créé et lié, et l'autre utilisateur reçoit un message indiquant que l'enregistrement existe déjà. Comment puis-je m'assurer que des doublons ne sont pas créés dans un environnement concurrent ?

Answer 1

Vous pourriez incrémenter y récupérer l'ID dans l'instruction de mise à jour à l'aide de sortie .

update UniqueIdTable
set ID = ID + 1
output deleted.ID
where label = @inputLabel

Answer 2

Je pense que vous avez raison de dire que deux threads peuvent lire la même valeur à l'étape 2. Je pense à deux alternatives :

1. Ajouter un prédicat pour id dans l'instruction de mise à jour afin de mettre à jour que si la valeur n'a pas changé. Si la mise à jour ne met à jour aucun (je ne sais pas comment vérifier dans SQL Server mais cela doit être possible) réessayer l'opération.

2. Exécutez d'abord l'instruction de mise à jour. Un seul thread pourra l'exécuter. Sélectionnez ensuite la valeur mise à jour.

J'ai deux autres suggestions

1. Effectuez cette opération dans une transaction séparée afin qu'une transaction en cours depuis longtemps ne bloque pas une autre transaction.

2. Réserver un bloc local au niveau de l'application. Incrémenter par une grande valeur puis par 1 et utiliser les identifiants du bloc thread-local. Cela réduira les allers-retours du serveur et les mises à jour de la table.

Answer 3

Créez une table personnalisée dans une base de données où vous puede comprennent un champ d'identification incrémentiel. Toute application ayant besoin de ce numéro créera un enregistrement et utilisera la valeur renvoyée. Même si vous prenez cette valeur et ne l'appliquez pas à la table où vous en avez besoin, elle sera toujours unique, même si vous l'appliquez un an plus tard.