Problème de générateur

Version imprimable

01/08/2019, 09h10
mballasse

Problème de générateur

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
01/08/2019, 09h32
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

Citation:

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

8O là je trouve ça moins que contraignant !

Citation:

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
02/08/2019, 10h28
Artemus24

Salut à tous.

Citation:

Envoyé par mballasse

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 ?

Citation:

Envoyé par mballasse

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 ?

@+
02/08/2019, 16h59
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é
05/08/2019, 11h07
mballasse

Citation:

Envoyé par Artemus24

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
05/08/2019, 11h10
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

Citation:

Envoyé par 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

8O 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
05/08/2019, 11h14
Artemus24

Salut mballasse.

Citation:

Envoyé par mballasse

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

Pourquoi ?

Citation:

Envoyé par mballasse

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.

@+
05/08/2019, 14h44
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
12/08/2019, 15h44
Barbibulle

Bonjour,

Citation:

Envoyé par mballasse

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....)

procedure stockée

Code:

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:

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;

Bonjour,
Citation:
Envoyé par mballasse
code Delphi
Code:

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é

24/08/2019, 18h49
SergioMaster

Bonjour,

Citation:

Envoyé par alanglet

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

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:

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

09/09/2019, 09h21
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
09/09/2019, 15h16
SQLpro

Citation:

Envoyé par mballasse

…
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 +

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.
Citation:
Envoyé par mballasse
procedure stockée
Code:

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….
Citation:
code Delphi
Code:

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 +

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:

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.