SQL SERVER : COMPTEUR ET IDENTITY

Messages postés
36
Date d'inscription
samedi 17 novembre 2007
Statut
Membre
Dernière intervention
8 février 2012
- - Dernière réponse : omardiany
Messages postés
1
Date d'inscription
lundi 2 juillet 2012
Statut
Membre
Dernière intervention
18 mai 2013
- 18 mai 2013 à 14:32
Cette discussion concerne un article du site. Pour la consulter dans son contexte d'origine, cliquez sur le lien ci-dessous.

http://codes-sources.commentcamarche.net/faq/512-sql-server-compteur-et-identity

Afficher la suite 
SQLpro
Messages postés
36
Date d'inscription
samedi 17 novembre 2007
Statut
Membre
Dernière intervention
8 février 2012
1 -
Bonjour,

article intéressant mais contenant un erreur gravissime. En effet le calcul des clefs autoincrémentées, comme je l'ai déjà indiqué dans cet article assez ancien nécessite de maîtriser parfaitement la concurrence afin qu'aucun calcul ne se téléscope et qu'une transaction puisse utiliser par erreur le même auto incrément qu'une autre :
http://sqlpro.developpez.com/cours/clefs/

Or dans votre procédure : GetSeq_NonLache vous ne gérez pas la transaction ! C'est une erreur grave. En effet que se passe t-il si la mise à jour à lieu (UPDATE) et pas le SELECT... Pas bien grave, vous perdez une clef !
Vous avez simpement oublié de gérer les exceptions soit en interrogeant la variable @@ERROR à chaque ordre SQL, soit en utilisant le TRY / CATCH !
Mais le pire est que vous pouvez parfaitement donner la même clef à deux utilisateur distincts.
Exemple, soit les utilisateurs USER1 et USER2 exécutant concurrament la même procédure :

La table Sequence_NonLache contient par exemple :

NUM SEQ INC
---- ---- ----
1 1 1

********** CODE SQL **********

USER1 :: EXEC GetSeq_NonLache 1, @value int OUTPUT

USER2 :: EXEC GetSeq_NonLache 1, @value int OUTPUT

USER1 :: UPDATE Sequence_NonLache
SET Seq = Seq + Inc
WHERE Num = @num

-- contenu de la table :
-- NUM SEQ INC
-- ---- ---- ----
-- 1 2 1

USER2 :: UPDATE Sequence_NonLache
SET Seq = Seq + Inc
WHERE Num = @num

-- contenu de la table :
-- NUM SEQ INC
-- ---- ---- ----
-- 1 3 1

USER1 :: SELECT @value = Seq
FROM Sequence_NonLache
WHERE Num = @num

-- @value contient 3

USER2 :: SELECT @value = Seq
FROM Sequence_NonLache
WHERE Num = @num

-- @value contient 3

********** CODE FIN **********

CQFD !

Visiblement vous maîtrisez mal les problèmes de concurrences transactionnelles qui sont justement la forces des SGBD relationnels.

Pour que votre procédure soit correcte il faut piloter un niveau d'isolation adéquat et gérer les exceptions.
Dans votre cas il est nécessaire de passer en REPEATABLE READ. Si vous aviez inversé le séquencement de l'UPDATE et du SELECT il aurait fallut du sérializable.

La version correcte de votre procédure est donc la suivante :

********** CODE SQL **********

CREATE PROCEDURE GetSeq_NonLache @num int, @value int OUTPUT
AS

SET TRANSACTION ISOLATION LEVEL REPEATABLE READ

DECLARE @acttran int
SET @acttran = @@TRANCOUNT
IF @@TRANCOUNT= 0
BEGIN TRAN

UPDATE Sequence_NonLache
SET Seq = Seq + Inc
WHERE Num = @num
IF @@ERROR <> 0 GOTO LBL_ERROR

SELECT @value = Seq
FROM Sequence_NonLache
WHERE Num = @num
IF @@ERROR <> 0 GOTO LBL_ERROR

IF @acttran <> @@TRANCOUNT
COMMIT TRAN

GOTO LBL_RESUME

LBL_ERROR:
ROLLBACK TRANSACTION

LBL_RESUME:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED

GO

********** CODE FIN **********

Mais il y a encore plus simple. En effet un UPDATE SQL Server commet aussi un read, ne serait-ce que pour lire l'emplacement de la ligne dans la table.

Il suffit alors d'écrire le code suivant qui n'a ni besoin de transaction ni d'un niveau d'isolation supérieur à celui par défaut :

********** CODE SQL **********

CREATE PROCEDURE GetSeq_NonLache @num int, @value int OUTPUT
AS

UPDATE Sequence_NonLache
SET Seq = Seq + Inc
@value = Seq + Inc
WHERE Num = @num

GO

********** CODE FIN **********

Comme vous pourrez le constater c'est encore plus simple !
Notez que c'est un exercice que je donne en final dans mon cours d'optimisation des bases de données SQL Server à Orsys, Paris la Défense.

Sincères salutations


PS : votre site ne marche pas sous Mozilla Firefox. En effet il m'a été impossible de me connecter sous ce navigateur : la connexion boucle sans fin !!!
Rrominet
Messages postés
135
Date d'inscription
lundi 26 janvier 2009
Statut
Membre
Dernière intervention
24 novembre 2014
-
Explication très complète, qui contient juste ce dont j'ai besoin ;-)
Merci !
omardiany
Messages postés
1
Date d'inscription
lundi 2 juillet 2012
Statut
Membre
Dernière intervention
18 mai 2013
-
Bonjour
J'ai besoin de votre aide
J'ai champ de type numérique dans une table. Pour l'instant, les utilisateurs introduisent les numéros (numéro d’opération ) manuellement suivant un masque de saisie :

Les trois premiers alphabète représentent un code de service , suivi d'un slash et puis par les deux dernières lettres de l'année suivi d’un slash et pius Les quatre premiers chiffres représentent un numéro d'ordre d'enregistrement .
Je m'explique : Pour un premier code service (abdd ), le deuxième (2013)l’année d’enregistrement , le troisième numéro d'ordre d'enregistrement .
Exemple : (abdd/ 13/0001)
Ma question : Est-il possible qu'sql server 2008 puisse générer automatiquement la numérotation sans que les utilisateurs soient obligés de faire la saisie manuellement.

Merci infiniment
Commenter la réponse de SQLpro