Discussion :

[Tonyryu]

Bonjour,

Notre applicatif dispose d'un package permettant de gérer les différentes numérotations de nos objets métiers (tiers, contrat, etc...). Ce package s'appuie sur une table NUMEROTATION, dont une fonction permet de récupérer une nouvelle numérotation en incrémentant une colonne (VAL_COURANTE). Le commit de la transaction se fait lorsque le tiers, ou le contrat est enregistré.

Lors d'un appel unique cela fonctionne très bien. Cependant lors de 2 appels simultanés et commit sur chaque session à la suite de ces 2 appels, le numéro retourné est le même, et la colonne VAL_COURANTE a été incrémenté qu'une fois.

La première résolution a été d'ajouter un PRAGMA AUTONOMOUS_TRANSACTION;afin de commiter la modification de la table NUMEROTATION, lors de l'appel à la fonction de récupération d'un nouveau numéro, sans pour autant commiter l'intégralité de la création de l'objet métier.

Encore une fois dans le cadre de test solo, cela a résolu le problème principal, mais il reste un point de détail, qui est le sujet de ce topic. En effet, il peut arriver, certes, très rarement, que la procédure puisse être appeler simultanément, et que des insert ou update se fasse avant le commit sur chaque session.

Est-il possible lors de l'appel sur 2 sessions différentes, de mettre en attente l'appel de la fonction, tel que le ferait un mutex dans d'autre langage de programmation?

En gros, voila ce que j'aimerai, en remplaçant les TODO :

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
CREATE OR REPLACE PACKAGE BODY GESTION_NUMEROTATION AS
 
    FUNCTION SUIVANT(pTYPE_NUM NUMEROTATION.TYPE_NUM%TYPE,
                     pNUM_SOC NUMEROTATION.NUM_SOC%TYPE,
                     pID_CI NUMEROTATION.ID_CI%TYPE := NULL,
                     pCD_PR NUMEROTATION.CD_PR%TYPE := NULL,
                     pCD_TT NUMEROTATION.CD_TT%TYPE := NULL,
                     pID_INT NUMEROTATION.ID_INT%TYPE := NULL,
                     pANNEE_NUM NUMEROTATION.ANNEE_NUM%TYPE := NULL,
                     pMOIS_NUM NUMEROTATION.MOIS_NUM%TYPE := NULL) RETURN VARCHAR2 IS
 
    PRAGMA AUTONOMOUS_TRANSACTION;
 
        -- Var de ma fonctions
 
    BEGIN
        -- TODO : Mutex début
 
            -- Code de la fonction
 
            commit;
        -- TODO : Mutex fin
 
 
    EXCEPTION
    WHEN OTHERS THEN
        rollback;
        -- TODO : Mutex fin
    END SUIVANT;
END GESTION_NUMEROTATION;

[McM]

Tu peux faire un select for update de ta table numérotation.
Comme ça tu bloque l'enregistrement tant que tu n'as pas commité.

[mnitu]

...La première résolution a été d'ajouter un PRAGMA AUTONOMOUS_TRANSACTION;afin de commiter la modification de la table NUMEROTATION, lors de l'appel à la fonction de récupération d'un nouveau numéro, sans pour autant commiter l'intégralité de la création de l'objet métier.

...

Relisez attentivement le chapitre 9 Data Concurrency and Consistency dans Oracle Database Concepts. Essayez de bien comprendre le mécanisme de multiversion consistency model sinon des mauvaises surprises vous attentent à chaque pas!

[Edit]
Analysez attentivement votre mécanisme basé sur table NUMEROTATION; il peut constituer un point d’étranglement important dans votre application dans certaines conditions.
[/Edit]

[ARAGONIS]

Pourquoi ne pas utiliser une séquence oracle et faire appel à cette séquence via un select sequence.nextval from ...
Ceci résoudrait le problème d'attribution de numéro et la gestion de sequence est à toute épreuve. Attention cependant, à chaque appel de nextval, le numéro est incrémenté, qu'il soit utilisé ou pas.

[pachot]

Bonjour,

C'est effectivement la séquence la seule solution performante et scalable.

Attention cependant, à chaque appel de nextval, le numéro est incrémenté, qu'il soit utilisé ou pas.

Ce sera de toute façon le cas aussi de la fonction en autonomous transation.

Cordialement,
Franck

[Tonyryu]

La gestion de la numérotation a été spécialement développée pour laisser le client paramétrer ses propres numérotations, et donc et surtout ne plus avoir a créer de nombreuses séquences.

Dans le contexte métier, pour chaque nouveau produit le client créé un nouveau template de numérotation pour la partie contrat et la partie sinistre. On aurait pu effectivement créer une séquence dynamiquement, mais nous avons pris le parti-pris de ne pas les utiliser.

Comme le précise Pachot, pour l'incrémentation systématique, même si pas utilisée, avec l'utilisation du PRAGMA AUTONOMOUS_TRANSACTION; dans la fonction Suivant, cette contrainte est identique en cas d'erreur de création d'un tiers ou contrat

[Rams7s]

Bonjour,

Perso j'ai toujours pas compris ce que vous vouliez faire exactement. AMHA, le Autonomous transaction ne correspond qu'a quelques cas d'utilisations, et la plus part du temps ce n'est rien de plus qu'un patch qu'on applique sans vraiment comprendre pourquoi ca fonctionne.

Mais bon, pour revenir a ce dont j'ai compris de votre question, il y a DBMS_LOCK.
Vous pouvez donc mettre un mutex au debut de votre procedure, et le relacher a la fin (comme si votre fonction etait synchronized en java).
Mettre un verrou ca peut ralentir tout les gens qui veulent utiliser cette procedure, mais des fois il faut.

Pour le code de la fonction, si c'est jsute une incrementation, si vous faites un select genre pour recuperer la plus grande valeur il y a peut-etre moyen de faire autrement. Voir la question du 19 Juin sur plsqlchallenge.com en utilisant le create or replace context. (c'est une idee, c'est peut-etre a essayer)

[mnitu]

Pas vraiment besoin d’un DBMS_LOCK. La solution proposé par MCM fonctionne sans aucun problème une fois qu’on à compris ses implications en terme de performance et monter en échelle. Si ces contrainte dont inacceptables des autres solutions peuvent être envisagées.

Utiliser une transaction autonome dans ce cas c’est un signé d’incompréhension du mécanisme de multiversion consistency model.

[Tonyryu]

Il est vrai que le PRAGMA AUTONOMOUS_TRANSACTION; répond bien a mes attentes, mais j'avoue également ne pas trop maitriser son utilisation.

En fait, si je ne touche à rien, j'ai une ligne de paramétrage dans ma table numérotation qui me permet de définir un template de numérotation pour les contrats créés: '[NUM_SOC]/[CD_PR]/[VAL_COU]'. Les données entre crochets sont des nom de valeurs qui seront positionnées à leur place (NUM_SOC : Numéro de la société de rattachement du contrat, CD_PR : le code produit souscrit, VAL_COU : Valeur courante de la numérotation stockée dans une autre colonne.

La fonction SUIVANT, commence par récupérer la ligne de paramétrage par un SELECT, remplace les données du template et réalise un UPDATE avec la valeur incrémenté.

Lors de 2 appels simultanés par 2 sessions différentes, le select peut être fait sur chaque session récupérant la valeur courante, avant le passage à l'UPDATE par l'un des 2 appels. Et c'est en lisant la documentation pointé par Mnitu, que j'ai pu me rendre compte que l'utilisation d'une transaction autonome était loin d'être la solution parfaite. Car le but, n'est pas d'empécher la procédure d'être appeler simultanément, mais c'est de ne pas récupérer la même valeur sur une même ligne de la table NUMEROTATION.

La meilleure solution, me parait donc de simplement d'ajouter FOR UPDATE à mes SELECT récupérant la ligne de numérotation, ce qui aura pour effet de poser un lock sur cette enregistrement, jusqu'au commit se trouvant à la fin de l'enregistrement de chaque contrat. La numérotation automatique étant appelé seulement à l'enregistrement du dossier contrat, et non a son initialisation, cela devrait répondre à mes attentes. Je ferais les tests de monté en charges dès les modifications validées, et je vous tient au courant.

Mais je tiens à vous remercier pour les détails techniques sur le fonctionnement interne de oracle (9 Data Concurrency and Consistency). Je suis un fin codeur, mais mes connaissances Oracle sont loin d'être parfaite.