Discussion :

[nicolassalocin]

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 :

Sélectionner tout - Visualiser dans une fenêtre à part

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

[SQLpro]

SOLUTION 1 :
Inversez l'update et le SELECT. Placez l'update en premier.
SOLUTION 2 :
Utilisez l'UPDATE pour lire en même temps :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
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;
 
  -- Vérifier si la table demandée est présente dans T_INCREMENT
  IF NOT EXISTS( SELECT * FROM T_INCREMENT WHERE INC_TABLE = @NOM_TABLE)
     INSERT INTO T_INCREMENT (INC_TABLE, INC_VALEUR) VALUES (@NOM_TABLE, 0);
 
  -- Mettre à jour le nouvel incrément de la table demandée dans T_INCREMENT
  UPDATE T_INCREMENT SET @ID = INC_VALEUR + 1, INC_VALEUR = INC_VALEUR + 1  WHERE INC_TABLE = @NOM_TABLE
 
 
END
GO

SOLUTION 3 : avec SQL Server 2012, utilisez les SEQUENCE.

A +

[nicolassalocin]

@SQLPro

La solution 1 ne résout pas le problème

La solution 2 avec une procédure stockée identique à votre proposition (donc sans transaction) fonctionne parfaitement. J'ai testé depuis 3 postes clients et généré plus de 60 000 lignes sans erreur.

Je n'ai pas testé la solution 3 pour l'instant.

Merci encore !

[SQLpro]

Pour la date il faut être au niveau d'isolation REPEATABLE READ il me semble.

A +

[nicolassalocin]

Pour la date il faut être au niveau d'isolation REPEATABLE READ il me semble.

Je n'ai pas compris !

[SQLpro]

pardon... pas DATE, mais solution 1... !

A +

[nicolassalocin]

Pour que l'on soit bien en phase, voici la solution 1 modifiée :

Code :

Sélectionner tout - Visualiser dans une fenêtre à part

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
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 REPEATABLE READ
  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
 
  -- Mettre à jour le nouvel incrément de la table demandée dans T_INCREMENT
  UPDATE T_INCREMENT SET INC_VALEUR = INC_VALEUR + 1 WHERE INC_TABLE = @NOM_TABLE
 
  -- Calculer le prochain incrément
  SELECT @ID = INC_VALEUR FROM T_INCREMENT WHERE INC_TABLE = @NOM_TABLE
 
  -- Terminer la transaction
  COMMIT TRANSACTION PROCHAIN_ID
 
  -- Finalisation
  SET NOCOUNT OFF
END

J'obtiens la même erreur.