Mutating-Table-Problem

Als Mutating-Table-Problem bezeichnet man ein Phänomen, das aus dem Grundsatz der Reihenfolgeunabhängigkeit? im relationalen Modell bzw. in SQL und dem Trigger-Ausführungsmodell resultiert.

Im SQL-Standard ist das Problem aufgrund von Restriktionen wie dem Verbot von DML-Anweisungen in allen BEFORE-Triggern (zeilenorientierten wie auch befehlsorientierten ) in Kombination mit dem SQL-Ausführungsmodell ausgeschlossen. Aufgrund der gesammelten Ausführung aller direkt bzw. kaskadierend gefeuerten AFTER-Trigger "en Block" im Anschluss an vollständige Ausführung der DML-Anweisungen sind Mutating-Table-Probleme für die direkt gefeuerten und kaskadierenden AFTER-Trigger ausgeschlossen. Die Restriktion "keine DML-Anweisungen in BEFORE-Triggern" schützt vor dem Mutating-Table-Problem in direkt gefeuerten und kaskadierenden BEFORE-Triggern.

Grund für dieses Phänomen bei Oracle:

In Oracle ist die Freiheit gegeben, in zeilenorientierten BEFORE- wie AFTER-Triggern beliebige DML-Operationen verwenden zu dürfen, was zusammen mit dem oracle-spezifischen Ausführungsmodell ggf. zur Laufzeit das Mutating-Table-Problem hervorrufen kann. Es äußerst sich in einem Laufzeitfehler mit der Fehlermeldung "Mutaing-Table" nicht in einen Komplilierungsfehler, obwohl das Problem sehr einfach syntaktisch zu erkennen ist.

Folgende Syntax-Eigenschaften verusachen zur Laufzeit das Mutating-Table-Problem:

In allen Triggern von Oracle, auch allen Zeilentriggern, sind Anfragen, INSERT-, UPDATE- und DELETE-Anweisungen? syntaktisch zugelassen. Aufgrund der verschachtelten Ausführung immer vor und nach einem geänderten Datensatz könnte es bei einem Zeilentrigger zu einer Verletzung der Reihenfolgeunabhängigkeit? kommen, wenn mit der SELECT-Anweisung oder einer anderen DML-Operation auf die Tabelle des feuernden Ereignisses zugegriffen wird. Um dies zu verhindern, wird beim Oracle-DBMS die gesamte „feuernde“ Tabelle in dem Zeitraum nach der Ausführung aller BEFORE STATEMENT TRIGGER und vor der Ausführung aller AFTER STATEMENT TRIGGER systemintern als „mutating/wird geändert“ gekennzeichnet. Und während dieser Zeitspanne kann nicht durch direkt oder indirekt gefeuerte SELECT/DML-Operationen auf diese Tabelle zugegriffen werden.

Für das Mutating-Table-Problem gibt es aber auch eine Lösung.

Beispiele:

Die syntaktischen Kennzeichen für ein vorliegendes Mutating-Table-Problem sind fett markiert: Es ist ein ROW-Trigger und im Aktionsteil wird mittels Anfrage oder einer DML-Operation auf die feuernde Tabelle zugegriffen.

Hier ein Beispiel für einen Oracle-Trigger, der die Reihenfolgeunabhängigkeit verletzt: Je nach dem, in welcher Reihenfolge die Datensätze geändert werden, würde die Gehaltsgrenze (IF-Bedingung) mal früher erreicht und mal viel später, so dass die UPDATE-Anweisung mit ihren Folgeaktionen in den Triggern völlig unterschiedliche Ergebnisse liefern würde.

   CREATE OR REPLACE TRIGGER Gehalts_trg_mutating
AFTER UPDATE OF Gehalt ON Angestellte
FOR EACH ROW
DECLARE
V_Sum NUMBER;
BEGIN
SELECT SUM(Gehalt) INTO V_Sum FROM Angestellte;
IF V_Sum > 100.000 THEN
...
END;
Oracle-Fehlermeldung:
ORA-04091: table USER.ANGESTELLTE is mutating, trigger/function may not see it

Hier eine andere Problemstellung, die "einfach" gelöst auch zum Mutating-Table-Problem bei Oracle führt:

Jede Abteilung verfügt über ein Gehaltsbudget (Spalte budget). Die Summe der Gehälter aller Mitarbeiter (Spalte gehalt) einer Abteilung darf dieses Budget nicht überschreiten.

   CREATE OR REPLACE TRIGGER gehaltsbudget_trg
   AFTER INSERT OR UPDATE OF gehalt, abtnr ON mitarbeiter
   FOR EACH ROW
   DECLARE
      v_summe     number(20);
      v_budget    abteilung.budget%TYPE;
   BEGIN 
      SELECT SUM(gehalt) INTO v_summe
        FROM mitarbeiter        
       WHERE abtnr = :NEW.abtnr;
      SELECT budget INTO v_budget
        FROM abteilung  
       WHERE abtnr = :NEW.abtnr;
      IF v_summe > v_budget 
         THEN RAISE_APPLICATION_ERROR(-20003,'Summe der Gehälter übersteigt das Abteilungsbudget');
      END IF;
   END;

Quellen:

  • Faeskorn-Woyke, Heide; Bertelsmeier, Birgit; Riemer, Petra; Bauer, Elena: "Datenbanksysteme - Theorie und Praxis mit SQL2003, Oracle und MySQL", Pearson Education, München, 2007, ISBN 978-3-8273-7266-6
  • Feuerstein, Steven, Pribyl. Bill: "Oracle PL/SQL Programming", O'Reilly, 2009, ISBN-13: 978-0596514464
  • Oracle® Database SQL Language Reference 11g Release 1 (11.1) in http://docs.oracle.com/cd/B28359_01/server.111/b28286/toc.htm
  • Oracle® Database PL/SQL Language Reference 11g Release 2 (11.2) in http://docs.oracle.com/cd/E11882_01/appdev.112/e25519/toc.htm
  • Saurabh, Gupta: "Oracle Advanced PL/SQL Developer Professional Guide", Packt Publishing Limited, 2012, ISBN 978-1-84968-722-5

Kategorie: Aktive Datenbanken, Oracle-PL-SQL, SQL, M