Discussion :

[hamster]

Bonjour,

voilà le contexte de mon problème :
J'ai une table T dans laquelle je stocke un nom et un code. Le code est une clef unique, calculée grâce à une fonction qui est appelée dans un trigger à l'insertion.
La méthode de calcul de ce code est la suivante : on prend les quatre premiers caractères alphabétiques du nom, auxquels on ajoute une séquence de 4 chiffres. Par exemple :

Code :

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

insert into T (nom) values ('Maison');

va valoriser le champ code avec MAIS0001.
Pour le calcul de la séquence numérique, on recherche la valeur max de SUBSTR(T.nom,5,4) et on y ajoute 1.

Code :

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

insert into T (nom) values ('Maisonnette');

va valoriser le champ code avec MAIS0002.

En revanche, si j'exécute les deux instructions dans la même transaction (sans commit),

Code :

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

1
2
insert into T (nom) values ('Chateau');
insert into T (nom) values ('Chaton');

et bien la deuxième instruction sera rejetée au motif que la règle d'unicité du champ code a été violée. Dans ma transaction même sans faire de commit, j'ai bien la visibilité de la première instruction. Si j'insère un select entre les deux, je verrai que le premier enregistrement a bien été créé (même s'il n'a pas encore été commité) ce qui ne semble pas être le cas de la fonction, qui elle ne le voit pas encore. Du coup elle considère que l'enregistrement 'Chateau' n'existe pas et essaie de valoriser T.code à CHAT0001 pour les deux lignes.

Mon besoin est d'insérer un très grand nombre d'enregistrements (potentiellement quelques centaines de milliers) et je me vois mal fixer ma fréquence de commit à 1.

Est-ce que vous savez comment je pourrais faire pour que ma fonction puisse "voir" que le premier enregistrement a déjà été créé, et ainsi le prendre en compte pour le calcul du T.code de l'enregistrement suivant ?

Merci pour toutes vos suggestions !

[mnitu]

Utilisez une séquence à la place du calcul du max sur la table (personnellement je ne utiliserait jamais de telles artifices pour le calcul d'une valeur de clé primaire).
En général la solution avec le max sur la table est une catastrophe.

[hamster]

Malheureusement l'utilisation de la séquence ne fonctionne pas ici. Le risque de colision avec un code déjà existant n'est pas nul. La table contient actuellement plus d'un million d'enregistrements, qui ont déjà tous un code unique. Si j'initialise à partir de maintenant une séquence, qui plus est la même pour tous les quadruplets de caractères possibles, je suis quasiment certain de retomber sur un code déjà existant.

[mnitu]

Malheureusement l'utilisation de la séquence ne fonctionne pas ici. Le risque de colision avec un code déjà existant n'est pas nul. La table contient actuellement plus d'un million d'enregistrements, qui ont déjà tous un code unique. Si j'initialise à partir de maintenant une séquence, qui plus est la même pour tous les quadruplets de caractères possibles, je suis quasiment certain de retomber sur un code déjà existant.

Franchement je n'ai pas pensé qu'en plus c'est du vrai .
Le vrai problème n’est pas le trigger mais l’insertion concurrente.
N’est-il pas envisageable d’ajouter une clé primaire ersatz (en anglais surogate primary key) donc un id_entite ?

[hamster]

Je ne saisi pas très bien ta proposition de clé primaire ersatz. Est-ce que tu peux m'en dire un peu plus ?

[mnitu]

Ajoute d'un colonne id_entite dans la table qui peut être valorisé pour les enregistrements existente avec des valeurs numériques (1, 2, 3, ... 1 000 000)et qui ensuite sera alimenté par une séquence et abandon de la clé primaire actuelle (celle avec max sur la table). Est-ce possible ?

[xmamet]

Non je ne peux pas abandonner la clé contenue dans code, car elle est propagée vers d'autres applications. Donc si je mélange des codes pour les enregistrements existants avec des séquences pour les nouveaux enregistrements, je me retrouve avec un problème de cohérence.

[hamster]

Désolé, c'est bien moi qui ai tapé la réponse précédente, mais j'ai été connecté automatiquement avec un autre nom d'user.

[plaineR]

Peut-être en passant par une table temporaire dans laquelle tu stockes les codes insérés.
Donc dans ton trigger tu lis d'abord ta table temporaire, si tu ne trouves rien tu récupères la valeur de ta fonction.

[hamster]

Et ça fonctionnerait même si les insertions dans la table temporaire ne sont pas committées à chaque nouvel enregistrements ?

[plaineR]

Oui, puisque tu ne passes plus par une fonction autonome pour lire les valeurs insérées dans la session courante.

Fonction autonome = session différente = tu ne vois pas ce qui se passe dans la session courante tant que cela n'a pas été committé.
Les fonctions autonomes sont donc à utiliser avec la plus grande vigilence.

[plaineR]

Par table temporaire j'entends GLOBAL TEMPORARY TABLE

[hamster]

Oui, ça a l'air très intéressant comme idée !

Il faut juste que je détermine à quel niveau positionner cette table, afin d'être sûr que les insertions ou les updates seront toujours visibles par la fonction stockée qui génère les champs codes.

je vais creuser ce point. Merci plaineR

[plaineR]

Je ferai qlq chose comme cela :

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
create or replace trigger ....
declare
   maseq number;
begin
   -- lecture table temporaire
   select max(...) into maseq 
   from maTableTemp 
   where code = substr(...);
   -- enreg non trouve, recup seq via fonction
   if maseq is null then
     maseq = maFct;
   end if;
   -- valeur de mon code
   :new.code = ...;
   -- insertion dans ma table temp
   insert into maTableTemp (...) values (....);
end;
/

[hamster]

C'est une possibilité.

Je pensais plutôt charger complètement ma table temporaire au début du traitement, puis faire les recherches uniquement sur cette table. Si la valeur existe, j'incrémente, sinon je l'ajoute à la table temporaire.

[plaineR]

C'est une possibilité.

Je pensais plutôt charger complètement ma table temporaire au début du traitement, puis faire les recherches uniquement sur cette table. Si la valeur existe, j'incrémente, sinon je l'ajoute à la table temporaire.

Attention aux perf.

[mnitu]

Comme je l'ai déjà dit le vrai problème dans cette génération des valeurs uniques est lié à l'accès concurrent de la table, plus précis les insertions et non rien à voir avec les triggers, les global temporary tables, les transactions autonomes ou autres usines à gaz construites sur ces notions.

Je m'explique: sauf le cas où l'insert dans cette table se fait uniquement par un traitement batch

• a un instant T un utilisateur insère la valeur 'Maison'. A ce moment on va aller lire la table TAB pour calculer le MAX de la valeur pour le code 'MAIS'. Supposons que la valeur est N. Il génère la clé MAISN et il l'insère dans la table TAB. Il n'a pas encore fait le commit.
  
• à peu près à ce même instant T un autre utilisateur insère la valeur 'Maisonnette'. Dans ce cas aussi on va aller lire la table TAB et calculer le MAX de la valeur pour le code 'MAIS'. La valeur sera la même: N parce que Oracle garanti le niveau d'isolation READ COMMITED par défaut et donc la clé généré sera 'MAISN': un doublon.

Pour résoudre ce problème il faut garantir que la lecture du MAX se fait seulement après la validation de la première insertion de où l'exception des insertions via programme batch.

Les solutions

• Dans le cas d'un batch nul besoin de lire le Max pour chaque enregistrement il suffit de gérer un sort de tableau des clés de genre ((MAIS, 0001), (CHAT, 2000), etc) dans le programme.
  
• Dans le cas des accès concurrentes on peut passer par une table de compteurs (code, valeur)
  ex:
  CODE VALEUR
  MAIS, 0001
  CHAT, 2000
  et à chaque génération d'une clé verrouiller la table des compteurs pour le code concerné, lire la valeur, l'incrémenter, la remettre à jour dans la table des compteurs.

Mais cette solution a la propriété de poser des problèmes si le nombre des utilisateurs simultanée est important.

[hamster]

@ mnitu > Les insertions se feront uniquement par batch (un seul batch à la fois) et jamais par IHM.

@ plaineR > bonne remarque ! Je vais également étudier les volumes d'enregistrements à insérer. Il est sans doute inutile de charger à chaque lancement du batch l'ensemble des données dans la table temporaire, pour insérer quelques dizaines d'enregistrements. De ce fait, ta solution (d'abord chercher dans la table temporaire, puis dans la table principale) est sans doute plus performante, puisque pour n insertions on aura au pire 2n accès à la table principale et 2n accès à la table temporaire (que ce soit en lecture ou en écriture).

[mnitu]

@ mnitu > Les insertions se feront uniquement par batch (un seul batch à la fois) et jamais par IHM.
...

Donc nul besoin d'un trigger ou du Select Max de la table.

[hamster]

Et pourquoi exactement ?