Discussion :

[mballasse]

Bonjour à toutes et tous
Je me permets de venir vers vous pour exposer un problème que je rencontre depuis peu avec une base de données Firebird

Envrionnement de production
Moteur Base de données Firebird 2.5 installé en Super Classique sur un serveur Citrix (serveur virtualisé)
Base de données hébergée sur ce même serveur volumétrie 10Go environ, 260 tables, aucune contrainte de clé primaire, ni clé étrangère

Environnement applicatif
Application client développée en Delphi XE6 (accès lient serveur) via FireDac, gestion manuelle des transactions
Application exécutée sur 10 serveurs Citrix (environ 2 à 3 utilisateurs par serveur Citrix)
Quelques pics de montée en charge mais rien de bien phénoménal

Problématique
L'application utilise un générateur Firebird pour obtenir une clé technique unique pour la base de données.
Cette clé est ensuite insérée dans une table de données.
L'obtention de la clé se fait via un composant FireDac procédure stockée qui invoque simplement la procédure stockée de la base qui incrémente le générateur de 1.

Depuis quelque temps, je me rends compte que je peux me retrouver avec des doublons de clé, ce qui commence a mettre en péril l'intégrité de la base de données

En mode deboggage, aucun souci et aucune reproductivité possible, le générateur renvoyé est toujours unique.

Pistes de réflexion
Mauvais paramétrage de Firebird (volume de cache,...)
Temps de latence des serveurs Citrix
Montée en charge excessive

Si quelqu'un a déjà rencontré un problème similaire, ou simplement me donner des pistes de réflexion, je vous en remercie par avance

[SergioMaster]

Bonjour,

je n'ai jamais rencontré ce problème quoique l'environnement soit différent 1 serveur une vingtaine de clients.
Je m'attendais plutôt à un problème du genre "trous" mais pas à un problème de doublons

[QUOTE=mballasse;11069956]Bonjour à toutes et tous
Je me permets de venir vers vous pour exposer un problème que je rencontre depuis peu avec une base de données Firebird

aucune contrainte de clé primaire, ni clé étrangère

là je trouve ça moins que contraignant !

FireDac, gestion manuelle des transactions
L'application utilise un générateur Firebird pour obtenir une clé technique unique pour la base de données.
Cette clé est ensuite insérée dans une table de données.
L'obtention de la clé se fait via un composant FireDac procédure stockée qui invoque simplement la procédure stockée de la base qui incrémente le générateur de 1.

Jamais je n'aurais fait comme ça ! Les triggers de Firebird sont là pour ça, utiliser une procédure stockée pour incrémenter un générateur au sein de transactions gérées manuellement me parait un pari risqué.

J'ajouterai dans les pistes un problème de transaction

[Artemus24]

Salut à tous.

aucune contrainte de clé primaire, ni clé étrangère

Ce n'est pas trop la philosophie des SGBDR de ne pas utiliser des clefs primaires et étrangères.
A l'origine, ce sont des fichiers Excel qui vous chargez dans FireBird ?

Depuis quelque temps, je me rends compte que je peux me retrouver avec des doublons de clé, ce qui commence à mettre en péril l'intégrité de la base de données

Dois-je comprendre que ce problème n'existait pas avant. Qu'est-ce qui a changé depuis ?

Je pense à un problème de transaction. Qu'est-ce que vous utilisez comme mode transactionnel ?

@+

[alanglet]

Bonjour,

Je regarderai plutôt la nature et la méthode d'obtention de cette "clé technique unique".
Si elle est effectivement obtenue depuis un générateur par la fonction gen_id(MonGene,1) et qu'aucune autre procédure ne vient modifier le générateur, peu importe que ce soit directement dans une requête SQL ou par l'intermédiaire d'une procédure stockée, le générateur est "hors transaction" (ou alors il y a un gros problème dans Firebird).

André

[mballasse]

Salut à tous.


Ce n'est pas trop la philosophie des SGBDR de ne pas utiliser des clefs primaires et étrangères.
A l'origine, ce sont des fichiers Excel qui vous chargez dans FireBird ?


Dois-je comprendre que ce problème n'existait pas avant. Qu'est-ce qui a changé depuis ?

Je pense à un problème de transaction. Qu'est-ce que vous utilisez comme mode transactionnel ?

@+

Je ne peux pas ajouter de contraintes clé primaire clé étrangère

Pour l'ancienneté du problème, je n'ai pas encore eu le temsp de regarder si cela se produisait avant, mais nous avons eu récemment une montée en charge de la volumétrie, qui pourrait expliquer ce phénomène
Le mode transactionnel utilisé en ReadCommitted

[mballasse]

[QUOTE=SergioMaster;11070002]Bonjour,

je n'ai jamais rencontré ce problème quoique l'environnement soit différent 1 serveur une vingtaine de clients.
Je m'attendais plutôt à un problème du genre "trous" mais pas à un problème de doublons

Bonjour à toutes et tous
Je me permets de venir vers vous pour exposer un problème que je rencontre depuis peu avec une base de données Firebird


là je trouve ça moins que contraignant !



Jamais je n'aurais fait comme ça ! Les triggers de Firebird sont là pour ça, utiliser une procédure stockée pour incrémenter un générateur au sein de transactions gérées manuellement me parait un pari risqué.

J'ajouterai dans les pistes un problème de transaction

Bonjour Sergio
Je ne peux pas utiliser de trigger dans la mesure où dans certains cas je dois récupérer la clé renvoyée pour définir une relation maitre détail.
Auriez vous du coup un exemple de mise en place a me proposer

[Artemus24]

Salut mballasse.

Je ne peux pas ajouter de contraintes clé primaire clé étrangère

Pourquoi ?

Je ne peux pas utiliser de trigger dans la mesure où dans certains cas je dois récupérer la clé renvoyée pour définir une relation maitre détail.

Un exemple détailé de ce que vous ne savez pas faire serait bienvenue.

@+

[mballasse]

L'impossibilité de mettre en place des clés primaires et étrangères est imposée par un mécanisme de réplication multi sites (vieux de plus de 20 ans et qui fonctionne parfaitement) qui fonctionne en XML

La question cocnernnat l'exemple est pour répondre à la problématique de l'appel via TRIGGER, je sais très bien faire fonctionner des relations maitre-détail via Delphi et Firedac, mais cela était pour répondre à la suggestion de SergioMaster

[Barbibulle]

Bonjour,

L'obtention de la clé se fait via un composant FireDac procédure stockée qui invoque simplement la procédure stockée de la base qui incrémente le générateur de 1.

Je voudrais juste que vous nous donniez le source de cette procédure. Car votre problème n'a rien à voir avec le type de transaction que vous utilisez (sauf si effectivement votre procédure n'utilise pas ce qu'il faut pour générer des identifiants....)

[mballasse]

procedure stockée

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SET TERM ^ ;
CREATE PROCEDURE INCVERSION
RETURNS (
    NUMVERSION Integer )
AS
begin
  NumVersion = Gen_Id (IncNumVersion,1);
  suspend;
  exit;
end^
SET TERM ; ^

code Delphi

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
function Version_Id: integer;
 
 
function TDtmPrincipal.Version_Id: Integer;
begin
	FDSPVersionTablette.Prepare;
	FDSPVersionTablette.ExecProc;
 
	Result := FDSPVersionTablette.ParamByName('numversion').AsInteger;
end;

[alanglet]

Bonjour,

code Delphi

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
function Version_Id: integer;
 
 
function TDtmPrincipal.Version_Id: Integer;
begin
	FDSPVersionTablette.Prepare;
	FDSPVersionTablette.ExecProc;
 
	Result := FDSPVersionTablette.ParamByName('numversion').AsInteger;
end;

Je n'utilise pas Firedac mais les IBX plus basiques (un IBSQL est suffisant, même en appelant directement gen_id, et ne nécessite pas de Prepare, mais un Close après lecture), pour la lecture du résultat on utilise FieldByName ou en plus rapide Fields[0].asInteger au lieu de ParamByName(..).asInteger.

André

[SergioMaster]

Bonjour,

Je n'utilise pas Firedac mais les IBX plus basiques (un IBSQL est suffisant, même en appelant directement gen_id, et ne nécessite pas de Prepare, mais un Close après lecture), pour la lecture du résultat on utilise FieldByName ou en plus rapide Fields[0].asInteger au lieu de ParamByName(..).asInteger.

Je suis d'accord, obtenir la valeur de l'incrément ne nécessite pas l'utilisation d'une procédure alors qu'un SQL SELECT Gen_Id (IncNumVersion,1) FROM RDB$DATABASEMais il est aussi possible après un SQL INSERT d'obtenir la valeur générée par le trigger en utilisant RETURNING, le seul truc avec Firedac/FDQuery est d'utiliser un Open ou un OpenOrExecute au lieu de ExecSQL

[Barbibulle]

Ca ne change pas son problème, toutes ces méthodes fonctionnent.

Dans la PS, le "exit;" ne sert à rien, je l'enlèverais.


Conclusion, vous utilisez bien un générateur firebird, la question reste donc : "Pourquoi il y a des doublons ?"

Etant donnée que les générateurs fonctionnent très bien dans firebird, qu'il n'y a pas de bug connue et qu'ils sont utilisés par énormément de programmes, la piste la plus logique c'est malheureusement pour vous un bug dans vos programmes.


Je commencerais par modifier la fonction d'appel de la procédure :

Code :

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

1
2
3
4
5
6
7
8
9
10
11
function Version_Id: integer;
 
 
function TDtmPrincipal.Version_Id: Integer;
begin
        FDSPVersionTablette.Close;      // pour m assurer que le composant est bien fermé je ne suis pas certain que si le composant est ouvert( suite à une première utilisation) que le "ExecProc" relance la demande au serveur... Donc par prudence je fermerais
	                                             // j enlèverai le "prepare" qui est surtout utile si FDSPVersionTablette était définie en runtime (ici le nom de la procédure est défini en designtime donc la liste des parametres est connue) le prepare va demander au serveur la liste des parametres de la procedure
	FDSPVersionTablette.ExecProc; // dans la doc firedac il est ecrit qu "ExecProc" est à utiliser si la procedure ne renvoie pas de données Essayez "ExecFunc" à la place...
 
	Result := FDSPVersionTablette.ParamByName('numversion').AsInteger;
end;

Enfin une seconde piste possible :

Dans votre programme sous certaines conditions, vous devez insérer ou mettre à jour vos identifiants de table sans avoir appelé votre fonction "Version_Id" et du coup utiliserait la dernière valeur mémorisée.

Bonne recherche

[mballasse]

Merci pour ces éléments de réponse, je vais les mettre en place

Après avoir recherché les éléments invoqués par Barbibulle, pas de souci dans l'appel de Version_Id, l'appel à la procédre est bien invoqué chaque fois que j'en ai besoin

Une autre piste que j'ai en tête est l'environnement Citrix : les programmes sont montés sur des machines virtuelles en mémoire et je me demande s'il n'y a a pas à un moment ou à un autre un partage de mémoire ou un cache excessif qui pourrait produire ce phénomène

[SQLpro]

…
Depuis quelque temps, je me rends compte que je peux me retrouver avec des doublons de clé, ce qui commence a mettre en péril l'intégrité de la base de données

En mode deboggage, aucun souci et aucune reproductivité possible, le générateur renvoyé est toujours unique….

Ceci est parfaitement normal. En effet, aucun mécanisme SQL autre qu'une contrainte d'unicité ou de clef primaire ne peut garantir des valeurs unique dans une série du fait de la concurrence d'accès.

En gros, quand vous testez, vous êtes seul à tester donc il n'y a aucun accès concurrent et donc les valeurs sont bien uniques.

Dès que vous êtres plusieurs utilisateurs à exécuter SIMULTANÉMENT des requêtes, il y a parallélisation et vous vous retrouver avec des doublons.

C'est une erreur gravissime que rencontrent tous les développeurs qui n'étudient pas comment fonctionne les bases de données et croient que les applicatifs qu'ils développent sont supérieurs en qualité et fonctionnalités, au fonctionnement d'un SGBDR, alors que c'est tout le contraire...

Il n'y a donc aucun paramétrage à faire côté FireBird ni CITRIX, rien que votre cerveau à formatter pour repartir sur de bonnes bases !

A +

[SQLpro]

Pour que tout ceci puisse avoir une chance de fonctionner sans générer de doublons, il faudrait…

PREMIÈREMENT
que la procédure stockée soit transactionnée au niveau d'isolation SERIALIZABLE.

procedure stockée

Code :

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

1
2
3
4
5
6
7
8
9
10
11
12
 
SET TERM ^ ;
CREATE PROCEDURE INCVERSION
RETURNS (
    NUMVERSION Integer )
AS
begin
  NumVersion = Gen_Id (IncNumVersion,1);
  suspend;
  exit;
end^
SET TERM ; ^

Si vous ne savez pas ce qu'est un niveau d'isolation transactionnel, ni comment piloter une transaction explicite, lisez l'article que j'ai écrit à ce sujet :
https://sqlpro.developpez.com/isolation-transaction/


DEUXIÈMEMENT

Que votre code Delphi soit encapsulé dans un sémaphore système….

code Delphi

Code :

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

1
2
3
4
5
6
7
8
9
10
11
 
function Version_Id: integer;
 
 
function TDtmPrincipal.Version_Id: Integer;
begin
	FDSPVersionTablette.Prepare;
	FDSPVersionTablette.ExecProc;
 
	Result := FDSPVersionTablette.ParamByName('numversion').AsInteger;
end;

Et enfin que votre application ou votre service Delphi ne soit pas dédoublée au niveau de l'exécution.

Ce qui bien évidemment est irréaliste car cela revient à dire, si c'est une application, qu'elle ne doit pas être lancée par plus d'un utilisateur simultanément….

A +

[mballasse]

Je vais un peu compléter mes tests

L'appel ExecFunc ne fonctionne pas : cela renvoie systématiquement une exception: Paramètre Result non trouvé
Cela fonctionne par contre avec ExecProc

Concernant les appels de Version_Id, ils se font hors transaction comme suit :

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
 
NEWID := Version_ID
FDConnection.StartTrasaction
try
  FDQryInsertTOT.Close;
  if not (FDQryInsertTOT.Prepared) then 
  begin
    FDQryInsertTOT.Prepare;
  end;
  FDQryInsertTOT.ParamByname('VID').AsInteger :=   NEWID;
  ....
  FDQryInsertTOT.ExecSQL;
 
  FDConnection.Commit.
except
  FDConnection.Rollback;
  raise;
end;

Lorsque l'utilisateurs et stout seul, il n'y a jamais aucun souci.
Les dysfonctionnement apparaissent lorsque de la charge du serveur devient conséquente et que le nombre d'utilisateurs utilisant les mêmes écrans (donc même fonctionnalité d'insertion) augmentent.
Pourtant lorsque le problème survenir, les utilisateurs sont bien sur le même écran , la même fonctionnalité d'insertion, mais sur des Serveurs Citrix différents (User1 sur CitriX1 , User2 sur Citrix9 par exemple).
Autre élément, l'application utilise un seul utilisateur firebird pour se connecter à la base de données (utilisateur applicatif), configuré dnas la se de registre de l'utilisateur Windows.