Discussion :

[mnitu]

J'ai déjà posté ça, mais vu le manque de réaction je me demande si quelqu'un à lu et compris où est le problème.
Donc je vous proposé de reprendre la solution pour contourner l’erreur induite par la table en mutation (http://sgbd.developpez.com/oracle/ora-04091/):

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
 
 
CREATE TABLE CLIENT(
    IDC INTEGER PRIMARY KEY ,
    NOM VARCHAR2 (40)
    )
/
CREATE TABLE VOYAGE(
    IDV INTEGER PRIMARY KEY ,
    DESTINATION VARCHAR2 (40),
    MAXPLACE INTEGER , -- nombre total de places
    PLACEDISPO INTEGER ) -- nombre de places disponibles     
/
 
CREATE TABLE INSCRIPTION(
    IDC INTEGER REFERENCES CLIENT(IDC),
    IDV INTEGER REFERENCES VOYAGE(IDV),
    DATERESERV DATE ,
    CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV)
)
/
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
 
-- un seul place disponoble pour aller à VENISE
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE, PLACEDISPO) VALUES (10, 'VENISE', 25, 1);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE, PLACEDISPO) VALUES (11, 'PRAGUE', 20, 0);
COMMIT ;
 
-- déclencheur qui n'interroge plus la table mutante
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW
DECLARE
    NB_DISPO INTEGER ;
BEGIN
    SELECT PLACEDISPO INTO NB_DISPO FROM VOYAGE
    WHERE IDV=:NEW.IDV;
    IF NB_DISPO < 1 THEN
        DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet');
    ELSE
        UPDATE VOYAGE SET PLACEDISPO=PLACEDISPO - 1
        WHERE IDV=:NEW.IDV;
    END IF ;
END ;
/

Il y a une seule modification concernant le nombre de place disponible pour aller à Venise.

Ensuite j’ouvre une première session et j'ajoute une première inscription sans faire la validation (commit) :

Code :

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

1
2
3
 
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;

Dans une autre session j'ajoute une deuxième inscription pour la même destination

Code :

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

1
2
3
 
-- DUBOIS aimerait lui aussi aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 2, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;

Enfin je valide dans chaque session (commit) et j’interroge la table du voyage :

Code :

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

1
2
3
4
5
6
7
8
9
 
 
SQL> SELECT * FROM voyage
  2  ;
 
       IDV DESTINATION                                MAXPLACE PLACEDISPO
---------- ---------------------------------------- ---------- ----------
        10 VENISE                                           25         -1
        11 PRAGUE                                           20          0

Autrement dit le trigger n'arrive pas à faire son boulot. Pour cella il faut reserver l'enregistrement de la table de voyage en mode pessimiste ou optimiste.

Il est intéressant de noter que la solution proposé par http://www.akadia.com/services/ora_m..._problems.html introduit le même type de problème.

[McM]

Moi je ferais dans tous les cas l'update dans VOYAGE (en lui ayant rajouté une contrainte sur PLACEDISPO > 0).
Tu économises 1 select et surtout tu n'auras plus ce cas au commit.

[mnitu]

Et pourquoi pas? Comme au bout de la ligne le résultat est correcte c'est une bonne solution. Quoi que je ne peux pas m'empêcher de remarquer que la solution que vous proposez est un détail d'implémentation d'un verrou (lock) optimiste .

Mais je vais continuer à illustrer mes idées avec la suite; cette fois il s'agit de la solution basée sur la méthode dite "des deux déclencheurs avec table temporaire"

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
 
CREATE TABLE CLIENT(
    IDC INTEGER PRIMARY KEY ,
    NOM VARCHAR2 (40));
 
CREATE TABLE VOYAGE(
    IDV INTEGER PRIMARY KEY ,
    DESTINATION VARCHAR2 (40),
    MAXPLACE INTEGER ) -- nombre total de places     
;
 
CREATE TABLE INSCRIPTION(
    IDC INTEGER REFERENCES CLIENT(IDC),
    IDV INTEGER REFERENCES VOYAGE(IDV),
    DATERESERV DATE ,
    CONSTRAINT INSCRIPTION_PK PRIMARY KEY (IDC, IDV));
 
INSERT INTO CLIENT(IDC, NOM) VALUES (1, 'DURAND');
INSERT INTO CLIENT(IDC, NOM) VALUES (2, 'DUBOIS');
INSERT INTO CLIENT(IDC, NOM) VALUES (3, 'DUGENOU');
COMMIT ;
 
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (10, 'VENISE', 1);
INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES (11, 'PRAGUE', 20);
COMMIT ;
 
-- Création d'une table temporaire vide de même structure que INSCRIPTION
CREATE GLOBAL TEMPORARY TABLE TEMP_INSCRIPTION AS SELECT * FROM INSCRIPTION WHERE 0=1;
 
-- 1er déclencheur, de niveau ligne, qui n'interroge plus la table mutante
-- à la place, il stocke dans la table temporaire les données insérées
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW 
BEGIN 
    INSERT INTO TEMP_INSCRIPTION(IDC, IDV, DATERESERV) VALUES (:NEW.IDC, :NEW.IDV, :NEW.DATERESERV);
END ;
/
 
-- second déclencheur, de niveau instruction, qui vérifie qu'il y a des places libres
-- il s'exécute une seule fois, après le traitement de tous les enregistrements touchés par l'INSERT sous-jacent
CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION2 AFTER INSERT ON INSCRIPTION
DECLARE 
    NB_RESERVE INTEGER ; -- nombre de réservations déjà faites
    NB_MAXPLACE INTEGER ; -- nombre de places total     
BEGIN 
    FOR LIGNE IN (SELECT * FROM TEMP_INSCRIPTION ORDER BY DATERESERV) LOOP 
        SELECT COUNT (*) INTO NB_RESERVE FROM INSCRIPTION 
        WHERE IDV=LIGNE.IDV;
        SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE 
        WHERE IDV=LIGNE.IDV;
        IF NB_MAXPLACE - NB_RESERVE < 0 THEN 
            Raise_application_error(-20000,'Réservation impossible pour voyage ' || LIGNE.IDV || ' et client ' || LIGNE.IDC);
            -- on supprime les inscriptions excédentaires
            DELETE FROM INSCRIPTION WHERE IDV=LIGNE.IDV AND IDC=LIGNE.IDC;
        END IF ;
    END LOOP ;
    -- tout à la fin, on remet à zéro la table temporaire. A noter qu'un TRUNCATE n'est pas possible ici, car il débuterait une nouvelle transaction
    DELETE FROM TEMP_INSCRIPTION;
END ;
/

Comme avant, les modifications sont minimales, un seul place pour aller a Venise en tout (mais oui il faut se préparer pour les voyage à l'époque du vélo ) et le trigger soulève une exception à la place d'un message.

Ensuite une inscription non validé saisie dans une session

Code :

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

1
2
3
 
-- DUGENOU aimerait bien aller à Venise :
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;

et une deuxième dans une autre session

Code :

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

1
2
 
INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 2, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL ;

Et en fin on valide une après l'autre dans les deux sessions et on interroge la table des inscriptions et de voyage

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
 
SQL> select * from inscription;
 
       IDC        IDV DATERESE
---------- ---------- --------
         2         10 05/11/07
         3         10 05/11/07
 
SQL> select * from voyage;
 
       IDV DESTINATION                                MAXPLACE
---------- ---------------------------------------- ----------
        10 VENISE                                            1
        11 PRAGUE                                           20
 
SQL>

Encore une fois, deux réservations pour un seul place libre.
Et bien, la solution telle qu'elle est actuellement, tout simplement ne fonctionne pas! Avez vous aussi une proposition pour ce cas ?
(PS. le lien correct est http://sgbd.developpez.com/oracle/ora-04091/)

[orafrance]

le PRAGMA AUTONOMOUS_TRANSACTION doit fonctionner sans problème

Pour le 2° cas, je dirais que c'est une erreur du code, j'essayerai de voir ça aujourd'hui... le trigger traite toutes les lignes de la table temporaire au lieu de ne prendre que la ligne intéressante... et d'ailleurs, comment l'autre session peut voir les lignes de la 1° sans commit

Essaye de remplacer la table temporaire par une table normal STP ou au moins ajoute la propriété : ON COMMIT DELETE ROWS

Edit : en fait, c'est plutôt le contraire... dans ta session tu ne peux pas voir la ligne ajoutée par la 1° à moins de faire un commit... c'est bien une erreur de ton code

[orafrance]

je teste et finalement, me rends compte que je dis n'importe quoi

1° cas : ça fonctionne parfaitement, le message s'affiche bien au 2° insert. Bien entendu, il faut ajouté un RAISE pour faire tomber le trigger en erreur et donc éviter d'insérer la ligne dans INSCRIPTION

Idem pour le 2° cas. Dans l'exemple on cherche pas a interrompre l'insert mais à alerter l'utilisateur. Ajoute un RAISE si tu veut que l'insertion ne se produise pas.

remplace DBMS_OUTPUT( par RAISE_APPLICATION_ERROR(-20001,

[mnitu]

Dans le deux cas il suffit de tester comme je vous le propose pour voire que cella ne marche pas ! Si vous regarder le deuxième cas j’ai remplacé bien le dbms_output par RAISE (et je l'ai fait aussi dans mon première test mais je repris le code telle qu'il est).
Est-ce que vous avez vraiment testé avec deux sessions comme je l’indique ?
Et je ne cherche pas une solution, j'essai de vous démontrer toute simplement que les solutions proposées ne fonctionnent pas.

Le PRAGMA_AUTONOMUS_TRANSACTION doit fonctionner sans problème

Désolé, mais « Perdu »

[Pomalaix]

Bonjour, et en particulier à mnitu

Eh bien on dirait que le sujet a suscité des débats enflammés !

Quand j'ai rédigé l'article en question, il y a plus de 4 ans, l'objectif était simplement de présenter le principe des différentes méthodes permettant de résoudre l'erreur ORA-04091, une telle information étant à ma connaissance inexistante en français.

L'objectif n'a jamais été été de fournir du code complet et pleinement fonctionnel, d'où par exemple l'usage d'un message d'erreur affiché par DBMS_OUTPUT, qui ne rimerait à rien dans une application réelle.
En toute honnêteté, je n'avais à l'époque absolument pas songé au problème potentiel des surréservations dues à l'absence de gestion du verrouillage. Ce problème est réel et extrêmement grave dans une application, mais n'a pas de lien particulier avec l'erreur ORA-04091, qui elle survient déjà lors d'un test mono session. J'aurais donc probablement choisi de ne pas traiter la question du verrouillage, hormis par une note rapide, car elle nécessite sinon des développements considérables.

Il y a plus de 18 mois, je disais déjà à un interlocuteur qui me faisait également des suggestions très pertinentes :
"Vous pourriez reprendre et compléter mon article, qui renferme plus d'une lacune et ne brille pas toujours par sa clarté.
En particulier, je ne cite pas les transactions autonomes, que je n'arrive pas à considérer comme une manière saine de pratiquer.
Mon exception qui ne fait rien ne fonctionnerait pas telle quelle, et mon système de voyages, dans un environnement multi utilisateurs réel, permettrait d'attribuer plus de places qu'il n'y en a, et nécessiterait une sérialisation manuelle via un LOCK au moment où on consulte le nombre de places libres."

Si vous êtes tenté, n'hésitez donc pas à reprendre cet article à votre compte pour rendre les exemples plus robustes et réalistes, les participations constructives étant toujours les bienvenues !