SQL Server 2008 - Auto incrément
Bonjour,
Je dois modifier une application Delphi/Interbase pour qu'elle fonctionne avec SQL Serveur 2008.
J'ai des tables qui utilisent des auto-incréments.
L'application fonctionne de la manière suivante :
- une table mémoire (ClientDataSet) ouvre une requête SQL pour récupérer les enregistrements puis ferme cette requête
- lors d'un ajout dans la table mémoire, une requête INSERT est envoyée au serveur
- lors d'une modification dans la table mémoire, une requête UPDATE est envoyée au serveur
- lors d'une suppression dans la table mémoire, une requête DELETE est envoyée au serveur.
Mon problème se situe au niveau d'un traitement par lots de génération de factures avec table en-tête de facture et table détail de facture.
Avec Interbase, à l'ajout d'un enregistrement dans la table mémoire je récupérais la prochaine valeur d'auto-incrément grâce à un générateur d'Interbase.
J'obtenais donc la valeur de l'auto-incrément AVANT de réaliser un INSERT et je pouvais donc créer les enregistrements de la table détail sans problème.
Pour connaître le principe que j'utilisais avec Interbase lire l'article de SQL Pro Clefs auto incrémentées paragraphe "4. Les mécanismes internes aux SGBDR".
J'ai étudié avec attention l'article de SQL Pro Auto incrément IDENTITY avec SQL server mais les solutions "@@IDENTITY", "SCOPE_IDENTITY()" et "IDENT_CURRENT(‘nom_table‘)" ne me conviennent pas puisqu'elles me permettent de connaître la valeur de l'auto-incrément APRES le INSERT ce qui m'oblige à rafraîchir la table mémoire ce qui est inacceptable dans le cas de mon application.
J'ai donc appliqué le principe "3.2. La solution : une table des clefs" de l'article de SQL Pro Clefs auto incrémentées qui me semblait parfaitement adapté.
Voici la procédure que j'ai créée :
Code:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
|
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE P_PROCHAIN_ID
-- Paramètre nom de la table dont on veut le prochain ID
@NOM_TABLE VARCHAR(128),
@ID INTEGER OUTPUT
AS
BEGIN
-- Initialisation
SET NOCOUNT ON;
-- Déclarer un entier
DECLARE @vNombre INTEGER
SET @vNombre = 0
-- Initialiser le résultat
SET @ID = -1
-- Démarrer la transaction
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
BEGIN TRANSACTION PROCHAIN_ID
-- Vérifier si la table T_INCREMENT existe
SELECT @vNombre = COUNT(*) FROM INFORMATION_SCHEMA.TABLES WHERE TABLE_NAME='T_INCREMENT'
-- Créer la table T_INCREMENT si elle n'existe pas
IF @vNombre = 0
BEGIN
CREATE TABLE T_INCREMENT
(
INC_TABLE VARCHAR(128) NOT NULL PRIMARY KEY,
INC_VALEUR INTEGER NOT NULL DEFAULT 0
)
END
-- Vérifier si la table demandée est présente dans T_INCREMENT
SET @vNombre = 0
SELECT @vNombre = COUNT(*) FROM T_INCREMENT WHERE INC_TABLE = @NOM_TABLE
-- Insérer la table demandée si elle n'est pas présente dans T_INCREMENT
IF @vNombre = 0
BEGIN
INSERT INTO T_INCREMENT (INC_TABLE, INC_VALEUR) VALUES (@NOM_TABLE, 0)
END
-- Calculer le prochain incrément
SELECT @ID = INC_VALEUR + 1 FROM T_INCREMENT WHERE INC_TABLE = @NOM_TABLE
-- Mettre à jour le nouvel incrément de la table demandée dans T_INCREMENT
UPDATE T_INCREMENT SET INC_VALEUR = @ID WHERE INC_TABLE = @NOM_TABLE
-- Terminer la transaction
COMMIT TRANSACTION PROCHAIN_ID
-- Finalisation
SET NOCOUNT OFF
END
GO |
Cette procédure fonctionnait très bien jusqu'à ce que je mette l'application dans des conditions réelles c'est-à-dire générer depuis 2 postes clients des factures par lots : 1000 factures sur chaque poste en même temps.
J'obtiens systématiquement, à un moment ou à un autre, l'erreur "La transaction (ID de processus XX) a été bloquée sur les ressources verrou par un autre processus et a été choisie comme victime.".
Après réflexion, je comprends effectivement le "blocage"...
Il semble que l'application A cherche la lire la valeur du prochain auto incrément au moment au l'appli B "verrouille" cette table pour la mettre à jour par exemple.
Mais je ne trouve pas de solution et tourne en rond malgré la lecture de nombreux articles !!!
Merci à vous pour votre aide
