Résolution du problème de la table mutante (ora-04091)05/07/2003
Par
Pomalaix
Tôt ou tard, un développeur PL/SQL qui utilise des déclencheurs finit par rencontrer l'erreur ORA-04091, dite de la table mutante (mutating table en VO). A. Rappels / définitions B. Dans quelles circonstances l'erreur ORA-04091 se produit-elle ? C. Qu'est-ce qui justifie ce comportement ? D. Peut-on se permettre d'ignorer l'erreur ORA-04091 ? E. Comment contourner l'erreur ORA-04091 ? E-1. Modification de la structure de données E-2. Méthode des deux déclencheurs (plusieurs variantes) E-3. Utilisation d'un déclencheur INSTEAD OF E-4. Je suis grand, c'est moi qui gère F. Sources principales (sans ordre particulier) A. Rappels / définitions
B. Dans quelles circonstances l'erreur ORA-04091 se produit-elle ? L'erreur ORA-04091 se produit dans chacun des cas suivants :
En revanche, il n'y a pas d'erreur ORA-04091 dans les cas suivants :
C. Qu'est-ce qui justifie ce comportement ? Selon la documentation Oracle, ces contraintes s'imposent pour respecter le principe de lecture cohérente. La lecture cohérente s'applique à 2 niveaux.
D. Peut-on se permettre d'ignorer l'erreur ORA-04091 ? La lecture cohérente au niveau des instructions unitaires est une véritable sécurité d'Oracle, et pas uniquement une contrainte pénible. L'exemple fictif suivant, à défaut d'utilité pratique, devrait illustrer la chose. Supposons qu'on veuille gérer manuellement, par le biais d'un déclencheur, une contrainte d'unicité sur une colonne numérique qui contient initialement les valeurs 1, 2, 3, 4, 5. CREATE TABLE TEST(COL1 INTEGER); Pour assurer l'unicité, on crée un déclencheur AFTER INSERT OR UPDATE FOR EACH ROW, lequel va vérifier que la nouvelle valeur n'existe pas déjà dans la table. Puis on exécute l'instruction suivante pour incrémenter COL1 de 1 dans toute la table : UPDATE TEST SET COL1=COL1+1; Pour la première ligne, l'incrémentation a lieu (1+1), puis le déclencheur vérifie si 2 n'existe pas déjà. Il conclut que oui, en conséquence de quoi on va annuler la transaction. Pourtant, si la vérification s'effectuait après le traitement de toutes les lignes, on aurait fort logiquement 2, 3, 4, 5, 6, valeurs pour lesquelles la contrainte d'unicité est respectée. L'utilité de la lecture cohérente de niveau instruction unitaire, c'est justement de ne pas donner accès aux états transitoires des données, qui ne permettent de tirer aucune conclusion valable. L'erreur ORA-04091 est donc un véritable signal d'alarme préventif. Il est à noter qu'Oracle ne garantit absolument pas l'ordre dans lequel seront traitées les lignes au sein d'une instruction unitaire, cet ordre pouvant notamment dépendre des options d'optimisation en vigueur. E. Comment contourner l'erreur ORA-04091 ? Plusieurs pistes sont envisageables et plus ou moins praticables ou recommandables suivant le contexte. En voici 4, appliquées à la situation suivante : On gère des voyages avec un nombre de places limité, et on doit évidemment vérifier qu'il reste des places disponibles avant d'accepter une nouvelle inscription. On utilise les tables CLIENT, VOYAGE, et INSCRIPTION. On utilise initialement un déclencheur BEFORE INSERT FOR EACH ROW sur la table INSCRIPTION pour vérifier qu'il y a encore des places, et on est confronté à l'erreur ORA-04091, car cette table est considérée comme mutante. 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', 25); INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES(11, 'PRAGUE', 20); COMMIT; CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW DECLARE NB_RESERVE INTEGER; -- nombre de réservations déjà faites NB_MAXPLACE INTEGER; -- nombre de places total BEGIN SELECT COUNT(*) INTO NB_RESERVE FROM INSCRIPTION WHERE IDV=:NEW.IDV; SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE WHERE IDV=:NEW.IDV; IF NB_MAXPLACE - NB_RESERVE < 0 THEN DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet'); END IF; END; / -- DUGENOU aimerait bien aller à Venise : INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL; ERREUR à la ligne 1 : ORA-04091: table INSCRIPTION en mutation, déclencheur/fonction ne peut la voir ORA-06512: à "TRIG_INSCRIPTION", ligne 5 ORA-04088: erreur lors d'exécution du déclencheur 'TRIG_INSCRIPTION' E-1. Modification de la structure de données Ce qui pose problème dans ce déclencheur, c'est qu'il va interroger la table INSCRIPTION, sur laquelle il est défini, pour dénombrer les inscriptions déjà effectuées. Il interroge donc une table mutante. Si on arrive à éviter d'interroger cette table au sein du déclencheur, on aura résolu le problème. Dans la table VOYAGE, on a un champ MAXPLACE qui indique le nombre de places total pour le voyage en question. Pourquoi ne pas ajouter un champ dénormalisé PLACEDISPO, qui indiquera dynamiquement combien de places il reste ? 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; INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE, PLACEDISPO) VALUES(10, 'VENISE', 25, 2); 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; / -- DUGENOU aimerait bien aller à Venise : INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL; 1 ligne créée. -- DUBOIS aimerait bien aller à Prague : INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 2, 11, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL; Désolé, voyage complet Cette modification de la structure de données a donc permis de ne plus interroger la table mutante au sein du déclencheur, si bien que l'erreur ORA-04091 ne se produit plus. E-2. Méthode des deux déclencheurs (plusieurs variantes) On a vu plus haut que les déclencheurs de niveau instruction ne sont pas sujets à l'erreur ORA-04091. L'idée est donc de reporter dans un déclencheur AFTER de niveau instruction les actions qui avaient provoqué l'erreur dans le déclencheur de niveau ligne. Mais il faut que ce second déclencheur soit en mesure d'identifier les enregistrements traités par le premier, pour leur appliquer, après coup, les vérifications utiles. On a donc besoin d'un espace de communication entre les deux déclencheurs. Cet espace de communication peut être réalisé de différentes manières : par une table temporaire ou par des variables globales déclarées dans un paquetage. L'option de la table temporaire est sans doute la plus simple, mais n'est praticable qu'à partir d'Oracle 8i. Suivant la nature de l'instruction DML sous-jacente au déclencheur, il peut être suffisant de mémoriser uniquement la clé primaire ou le ROWID des enregistrements (pour un INSERT), ou on peut à l'inverse, dans le cas d'un UPDATE et d'un DELETE, avoir besoin des anciennes valeurs pour être en mesure de les rétablir sélectivement, sans pour autant annuler la transaction complète. Méthode des deux déclencheurs avec table temporaire Méthode des deux déclencheurs avec variables globales 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', 25); INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES(11, 'PRAGUE', 20); COMMIT; -- création dans un paquetage d'une variable globale de type table indicée, dans laquelle le déclencheur de niveau ligne va mémoriser les données insérées. Les tables indicées requièrent la déclaration préalable d'un type équivalent. On utilise également une variable compteur qu'on initialise à 0. CREATE OR REPLACE PACKAGE PKG_INSERT AS TYPE T_INSCRIPTION IS TABLE OF INSCRIPTION%ROWTYPE INDEX BY BINARY_INTEGER; V_INSCRIPTION T_INSCRIPTION; V_COMPTEUR INTEGER:=0; END; / -- 1er déclencheur, de niveau ligne, qui n'interroge plus la table mutante -- à la place, il stocke dans une variable globale les données insérées CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW BEGIN PKG_INSERT.V_COMPTEUR:= PKG_INSERT.V_COMPTEUR + 1; PKG_INSERT.V_INSCRIPTION(PKG_INSERT.V_COMPTEUR).IDC := :NEW.IDC; PKG_INSERT.V_INSCRIPTION(PKG_INSERT.V_COMPTEUR).IDV := :NEW.IDV; PKG_INSERT.V_INSCRIPTION(PKG_INSERT.V_COMPTEUR).DATERESERV := :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 1.. PKG_INSERT.V_INSCRIPTION.COUNT LOOP SELECT COUNT(*) INTO NB_RESERVE FROM INSCRIPTION WHERE IDV=PKG_INSERT.V_INSCRIPTION(LIGNE).IDV; SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE WHERE IDV=PKG_INSERT.V_INSCRIPTION(LIGNE).IDV; IF NB_MAXPLACE - NB_RESERVE < 0 THEN DBMS_OUTPUT.PUT_LINE('Réservation impossible pour voyage ' || PKG_INSERT.V_INSCRIPTION(LIGNE).IDV || ' et client ' || PKG_INSERT.V_INSCRIPTION(LIGNE).IDC); -- on supprime les inscriptions excédentaires DELETE FROM INSCRIPTION WHERE IDV=PKG_INSERT.V_INSCRIPTION(LIGNE).IDV AND IDC=PKG_INSERT.V_INSCRIPTION(LIGNE).IDC; END IF; END LOOP; -- tout à la fin, on remet à zéro le compteur. PKG_INSERT.V_COMPTEUR:=0; END; Le 3ème déclencheur Certains auteurs particulièrement attentifs à la robustesse du code recommandent d'utiliser un 3ème déclencheur de niveau instruction et de type BEFORE, dans lequel on réinitialise les variables globales avant chaque utilisation. En effet, si une erreur non gérée survenait avant la fin du deuxième déclencheur qui réinitialise le compteur, ce compteur conserverait une valeur erronée, car l'annulation de la transaction n'a aucun effet sur les variables globales. -- modification du paquetage : V_VIDE est un tableau qui reste toujours vide CREATE OR REPLACE PACKAGE PKG_INSERT AS TYPE T_INSCRIPTION IS TABLE OF INSCRIPTION%ROWTYPE INDEX BY BINARY_INTEGER; V_INSCRIPTION T_INSCRIPTION; V_VIDE T_INSCRIPTION; V_COMPTEUR INTEGER:=0; END; / -- création du déclencheur supplémentaire qui réiniitialise les variables globales CREATE OR REPLACE TRIGGER TRG_INSCRIPTION3 BEFORE INSERT ON INSCRIPTION BEGIN PKG_INSERT.V_COMPTEUR:=0; PKG_INSERT.V_INSCRIPTION:= PKG_INSERT.V_VIDE; END; / E-3. Utilisation d'un déclencheur INSTEAD OF Dans certains cas, l'utilisation de déclencheurs de type INSTEAD OF peut être adaptée. Cette solution est à utiliser prudemment, dans la mesure où les déclencheurs INSTEAD OF ne déclenchent pas le signal d'alarme ORA-04091, ce qui revient à travailler sans filet. De ce fait, une incohérence dans la lecture consistante peut parfaitement passer inaperçue, avec des conséquences évidemment fâcheuses. Par ailleurs, si quelqu'un attaque directement la table sous-jacente à la vue, le déclencheur INSTEAD OF ne se déclenchera pas. Cette solution est donc réservée à un environnement extrêmement contrôlé. --Attention ! Le code suivant est destiné à illustrer le principe et la syntaxe d'un déclencheur INSTEAD OF. Fonctionnellement, il n'est pas une solution adaptée à notre exemple de la gestion de voyages. En effet, en cas d'insertion multiligne, le corps du déclencheur ne voit que l'état des données avant l'insertion, si bien que le count(*) renverra toujours la même valeur, sans tenir compte de l'insertion des enregistrements précédents. 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', 25); INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES(11, 'PRAGUE', 20); COMMIT; -- Création d'une vue sur la table INSCRIPTION pour le support des déclencheurs INSTEAD OF CREATE OR REPLACE VIEW V_INSCRIPTION AS SELECT * FROM INSCRIPTION; CREATE OR REPLACE TRIGGER TRIG_V_INSCRIPTION INSTEAD OF INSERT ON V_INSCRIPTION FOR EACH ROW DECLARE NB_RESERVE INTEGER; -- nombre de réservations déjà faites NB_MAXPLACE INTEGER; -- nombre de places total BEGIN SELECT COUNT(*) INTO NB_RESERVE FROM V_INSCRIPTION WHERE IDC=:NEW.IDC AND IDV=:NEW.IDV; SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE WHERE IDV=:NEW.IDV; IF NB_MAXPLACE - NB_RESERVE < 1 THEN DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet'); ELSE -- dans un déclencheur INSTEAD OF, l'instruction DML sous-jacente ne s'exécute pas. On traite donc l'insertion manuellement INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) VALUES(:NEW.IDC, :NEW.IDV, :NEW.DATERESERV); END IF; END; / -- DUGENOU aimerait bien aller à Venise : INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL; 1 ligne créée. E-4. Je suis grand, c'est moi qui gère Comme il a été dit, l'erreur ORA-04091 est un signal d'alarme destiné à attirer l'attention sur un risque potentiel d'incohérence.
Si l'on est absolument certain que ce signal d'alarme n'est pas pertinent, on peut envisager de l'ignorer, par le biais d'une interception
d'exception dans laquelle on ne fait rien de concret. 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', 25); INSERT INTO VOYAGE(IDV, DESTINATION, MAXPLACE) VALUES(11, 'PRAGUE', 20); COMMIT; -- on introduit une gestion d'exception CREATE OR REPLACE TRIGGER TRIG_INSCRIPTION BEFORE INSERT ON INSCRIPTION FOR EACH ROW DECLARE NB_RESERVE INTEGER; -- nombre de réservations déjà faites NB_MAXPLACE INTEGER; -- nombre de places total TABLE_MUTANTE EXCEPTION; PRAGMA EXCEPTION_INIT(TABLE_MUTANTE, -4091); BEGIN SELECT COUNT(*) INTO NB_RESERVE FROM INSCRIPTION WHERE IDV=:NEW.IDV; SELECT MAXPLACE INTO NB_MAXPLACE FROM VOYAGE WHERE IDV=:NEW.IDV; IF NB_MAXPLACE - NB_RESERVE < 0 THEN DBMS_OUTPUT.PUT_LINE('Désolé, voyage complet'); END IF; EXCEPTION WHEN TABLE_MUTANTE THEN DBMS_OUTPUT.PUT_LINE('Fausse alerte'); END; / -- DUGENOU aimerait bien aller à Venise : INSERT INTO INSCRIPTION(IDC, IDV, DATERESERV) SELECT 3, 10, TO_DATE(SYSDATE, 'DD/MM/YYYY') FROM DUAL; 1 ligne créée. F. Sources principales (sans ordre particulier)
Tous droits réservés. Merci de me contacter si vous souhaitez utiliser, imprimer ou reproduire tout ou partie de ce document.
|