Tablas mutantes… que no mutan!

Aunque el nombre que Oracle da a estas tablas (‘Mutating Tables’, Tablas Mutantes) parece más propio de una película de George A. Romero, el concepto es más sencillo (y menos terrorífico) que todo eso.

“A mutating table is a table that is currently being modified by an update, delete, or insert statement. When a trigger tries to reference a table that is in state of flux (being changed), it is considered
“mutating” and raises an error since Oracle should not return data that has not yet reached its final state”

Una tabla mutante es una tabla que está siendo modificada. Si un ‘trigger’ hace referencia a la propia tabla sobre la que se dispara se producirá una excepción con error ORA-04091.

Según esto, cualquier ‘trigger’ que haga una simple consulta sobre la tabla que lo dispara generará dicho ORA-04091.

Vamos a verlo.

SQL*Plus: Release 10.2.0.1.0 - Production on Jue Feb 8 10:08:18 2007

Copyright (c) 1982, 2005, Oracle.  All rights reserved.

Introduzca el nombre de usuario: carlos@test10g
Introduzca la contraseña:

Conectado a:
Oracle Database 10g Enterprise Edition Release 10.2.0.2.0 - Production
With the Partitioning, OLAP and Data Mining options

carlos@TEST10G> select * from prueba01;

        ID TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO

carlos@TEST10G> create trigger BID_PRUEBA01_TRG
  2  BEFORE INSERT OR DELETE
  3  ON PRUEBA01
  4  FOR EACH ROW
  5  DECLARE
  6       i_dummy NUMBER;
  7  BEGIN
  8     SELECT MAX(ID)
  9       INTO i_dummy
 10       FROM PRUEBA01;
 11  END BI_PRUEBA01_TRG;
 12  /

Disparador creado.

Verificamos el comportamiento esperado borrando una fila:

carlos@TEST10G> delete from prueba01 where id=5;
delete from prueba01 where id=5
            *
ERROR en línea 1:
ORA-04091: la tabla CARLOS.PRUEBA01 está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "CARLOS.BID_PRUEBA01_TRG", línea 4
ORA-04088: error durante la ejecución del disparador 'CARLOS.BID_PRUEBA01_TRG'

carlos@TEST10G>

Hasta aquí todo ocurre como se espera.

Pero, veamos que ocurre con un INSERT:

carlos@TEST10G> INSERT INTO PRUEBA01 VALUES (5, 'CINCO');

1 fila creada.

¿Cómo? ¿Por qué? ¡La ejecución del trigger debería dar el mismo error que en el caso del DELETE!

Pero la inserción se ha efectuado:


carlos@TEST10G> SELECT * FROM PRUEBA01;

        ID TXT
---------- -------------------------
         1 UNO
         2 DOS
         3 TRES
         4 CUATRO
         5 CINCO

carlos@TEST10G>

Veamos que ocurre si insertamos una fila, pero de otra manera:

carlos@TEST10G> INSERT INTO PRUEBA01 SELECT 6,'SEIS' FROM DUAL;
INSERT INTO PRUEBA01 SELECT 6,'SEIS' FROM DUAL
            *
ERROR en línea 1:
ORA-04091: la tabla CARLOS.PRUEBA01 está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "CARLOS.BI_PRUEBA01_TRG", línea 4
ORA-04088: error durante la ejecución del disparador 'CARLOS.BI_PRUEBA01_TRG'

carlos@TEST10G>

Así pues, sólo en el caso de INSERT’s de una sola fila con VALUES, el ORA-04091 no se produce.

Pero, vayamos un paso más allá: ¿Qué pasará con operaciones ‘BULK INSERT’ que también tienen ‘VALUES’?

carlos@TEST10G> DECLARE
  2     TYPE T_T_PRUEBA01 IS TABLE OF PRUEBA01%ROWTYPE INDEX BY BINARY_INTEGER;
  3     T_PRUEBA01 T_T_PRUEBA01;
  4  BEGIN
  5     T_PRUEBA01(1).ID:= 6;
  6     T_PRUEBA01(1).TXT:= 'SEIS';
  7     T_PRUEBA01(2).ID:= 7;
  8     T_PRUEBA01(2).TXT:= 'SIETE';
  9
 10     FORALL i IN T_PRUEBA01.FIRST..T_PRUEBA01.LAST
 11        INSERT INTO PRUEBA01 VALUES T_PRUEBA01(i); --'VALUES' sin paréntesis!!
 12  END;
 13  /
DECLARE
*
ERROR en línea 1:
ORA-04091: la tabla CARLOS.PRUEBA01 está mutando, puede que el disparador/la función no puedan verla
ORA-06512: en "CARLOS.BID_PRUEBA01_TRG", línea 4
ORA-04088: error durante la ejecución del disparador 'CARLOS.BID_PRUEBA01_TRG'
ORA-06512: en línea 10

Como vemos, aquí se vuelve a dar el error (como era de esperar).

Pero, una vez más ¿y si la tabla sólo tiene una fila?

carlos@TEST10G> DECLARE
  2     TYPE T_T_PRUEBA01 IS TABLE OF PRUEBA01%ROWTYPE INDEX BY BINARY_INTEGER;
  3     T_PRUEBA01 T_T_PRUEBA01;
  4  BEGIN
  5     T_PRUEBA01(1).ID:= 6;
  6     T_PRUEBA01(1).TXT:= 'SEIS';
  7
  8     FORALL i IN T_PRUEBA01.FIRST..T_PRUEBA01.LAST
  9        INSERT INTO PRUEBA01 VALUES T_PRUEBA01(i); --'VALUES' sin paréntesis!!
 10  END;
 11  /

Procedimiento PL/SQL terminado correctamente.

system@TEST10G>

Aunque sea una operación BULK INSERT, la inserción de una sola fila es análoga al caso de INSERT INTO …VALUES simple.

Saludos.

Carlos.

16 respuestas a Tablas mutantes… que no mutan!

  1. Yunier dice:

    Hola como estas?
    Veo que haces referencia a las tablas que estan mutando. Como puedo solucionar el problema? Me parece que no dices como? En mi caso particular lo que pasa es igual. Yo tengo un trigger que se dispara cuando inserto tuplas a una tabla1, el trigger es un merge que hace referencia a la tabla1 y a la tabla2 que es donde voy a hacer un resumen de los datos de la tabla1. Pero me da error pues dice que la tabla1 esta mutando y puede que no vea el trigger. Aca te envio mi trigger :

    CREATE TRIGGER TECHAGRO.TRAMACOSECHADORA_AFTER_INS_TR AFTER INSERT ON TECHAGRO.TRAMACOSECHADORA
    REFERENCING NEW AS NEW OLD AS OLD FOR EACH ROW
    BEGIN
    MERGE INTO TECHAGRO.RESCOSCH
    USING TECHAGRO.TRAMACOSECHADORA
    ON (to_date(:NEW.FechaHoraLocal, ‘dd/mm/yyyy’)= TECHAGRO.rescosch.Fecha and
    :NEW.IDMovil= TECHAGRO.rescosch.IDMovil and
    :NEW.IDOperador= TECHAGRO.rescosch.IDOperador and
    :NEW.turno= TECHAGRO.rescosch.turno and
    :NEW.StopCodeGenerado= TECHAGRO.rescosch.OperationCode and
    :NEW.TipoDeOperacion= TECHAGRO.rescosch.TipoDeOperacion and
    :NEW.AM= TECHAGRO.rescosch.AM and
    :NEW.MotorLigado= TECHAGRO.rescosch.MotorLigado and
    :NEW.StatusOperGenerado= TECHAGRO.rescosch.StatusOperGenerado and
    :NEW.ED2= TECHAGRO.rescosch.ED2 and
    :NEW.ED3= TECHAGRO.rescosch.ED3 and
    :NEW.ED5= TECHAGRO.rescosch.ED5 and
    :NEW.SecuenciaDeCambioDeOperador= TECHAGRO.rescosch.SecuenciaDeCambioDeOperador and
    :NEW.SecDeCambioDeOperYOCentroCosto= TECHAGRO.rescosch.SecDeCambioDeOperYOCentroCosto and
    :NEW.CentroDeCosto= TECHAGRO.rescosch.CentroDeCosto)
    WHEN MATCHED THEN
    UPDATE SET TECHAGRO.rescosch.DeltaT=TECHAGRO.rescosch.DeltaT + :NEW.DeltaT,
    TECHAGRO.rescosch.VelocMedia=((TECHAGRO.rescosch.TramasAnalizadas * TECHAGRO.rescosch.VelocMedia) + :NEW.Speed) / (TECHAGRO.rescosch.TramasAnalizadas + 1),
    TECHAGRO.rescosch.Combustible= TECHAGRO.rescosch.Combustible + :NEW.Combustible,
    TECHAGRO.rescosch.TramasAnalizadas=TECHAGRO.rescosch.TramasAnalizadas + 1
    WHEN NOT MATCHED THEN
    INSERT (TECHAGRO.rescosch.IDMovil,
    TECHAGRO.rescosch.IDOperador,
    TECHAGRO.rescosch.Turno,
    TECHAGRO.rescosch.TipoDeOperacion,
    TECHAGRO.rescosch.OperationCode,
    TECHAGRO.rescosch.DeltaT,
    TECHAGRO.rescosch.VelocMedia,
    TECHAGRO.rescosch.TramasAnalizadas,
    TECHAGRO.rescosch.MotorLigado,
    TECHAGRO.rescosch.AM,
    TECHAGRO.rescosch.FechaHoraUTC,
    TECHAGRO.rescosch.FechaHoraLocal,
    TECHAGRO.rescosch.Fecha,
    TECHAGRO.rescosch.StatusOperGenerado ,
    TECHAGRO.rescosch.ED2,
    TECHAGRO.rescosch.ED3,
    TECHAGRO.rescosch.ED5,
    TECHAGRO.rescosch.CentroDeCosto,
    TECHAGRO.rescosch.Combustible,
    TECHAGRO.rescosch.IDEmpresa,
    TECHAGRO.rescosch.IDZona,
    TECHAGRO.rescosch.IDTalion,
    TECHAGRO.rescosch.IDOrdenServicio,
    TECHAGRO.rescosch.SecuenciaDeCambioDeOperador,
    TECHAGRO.rescosch.SECDECAMBIODEOPERYOCENTROCOSTO)
    VALUES (:NEW.IDMovil,
    :NEW.IDOperador,
    :NEW.Turno,
    :NEW.TipoDeOperacion,
    :NEW.StopCodeGenerado,
    :NEW.DeltaT,
    :NEW.Speed,
    1,
    :NEW.MotorLigado,
    :NEW.AM,
    :NEW.FechaHoraUTC,
    :NEW.FechaHoraLocal,
    to_date(:NEW.FechaHoraLocal, ‘dd/mm/yyyy’),
    :NEW.StatusOperGenerado,
    :NEW.ED2,
    :NEW.ED3,
    :NEW.ED5,
    :NEW.CentroDeCosto,
    :NEW.Combustible,
    :NEW.IDEmpresa,
    :NEW.IDZona,
    :NEW.IDTalion,
    :NEW.IDOrdenServicio,
    :NEW.SecuenciaDeCambioDeOperador,
    :NEW.SecDeCambioDeOperYOCentroCosto);
    END;
    /

  2. carlosal dice:

    No puedes utilizar MERGE en el trigger.

    No conozco la lógica de tu proceso, pero yo intentaría hacer un INSERT en TECHAGRO.RESCOSCH con los valores :new, y capturaría la excepción (DUP_VAL_ON_INDEX) si fuera necesario hacer un UPDATE.

    Un saludo.

    Carlos.

  3. poder dice:

    hola wey tengo un problema con un trigger quiero actualizar una misma tabla pero me bota error que puedo hacer ..
    estas son mi tablas:
    CREATE TABLE CABECERAFACTURA(
    NUMERO CHAR(5) CONSTRAINT CAB_NUM_PK PRIMARY KEY,
    FECHA DATE,
    CLIENTE CHAR(25) CONSTRAINT CAB_CLI_NN NOT NULL,
    DIRECCION CHAR(25) ,
    SUB_TOTAL NUMBER(6,2),
    IGV NUMBER(6,2),
    TOTAL NUMBER(6,2)
    )
    CREATE TABLE DETALLEFACTURA(
    NUMERO CHAR(5),
    COD_PRODUCTO CHAR(5),
    PRODUCTO CHAR(25) CONSTRAINT DET_PRO_NN NOT NULL,
    CANTIDAD NUMBER(4),
    PRECIO_UNITARIO NUMBER(6,2),
    SUB_TOTAL NUMBER(6,2),
    CONSTRAINT DET_COD_PK PRIMARY KEY (NUMERO,COD_PRODUCTO)
    )
    este es mi trigger de actulizacion de la tabla detallefactura
    CREATE OR REPLACE TRIGGER lolo
    after update on detallefactura
    FOR EACH ROW
    BEGIN
    UPDATE detallefactura SET sub_total = cantidad * precio_unitario;
    END;

    la consulta que hago es :
    update detallefactura set cantidad=8

    y sale este error:
    ORA-04091: la tabla FACTURACION.DETALLEFACTURA está mutando, puede que el disparador/la función no puedan verla
    ORA-06512: en “FACTURACION.LOLO”, línea 2
    ORA-04088: error durante la ejecución del disparador ‘FACTURACION.LOLO’

    necesitari aun ayuddita bueno graxias

  4. Charl dice:

    Claro que ya sabemos que es una tabla mutante, y lo sabemos por que nos ha salido este error.
    Lo que queremos saber es como solucionar este problema… o mejor aún qué técnicas existen para solucionarlo. Esto sería un mejor aporte para un artículo.

    • carlosal dice:

      No. No lo sabes, aunque te haya salido este error. Por regla general el ‘problema’, como tú lo llamas, es un mal diseño de los datos o de la lógica de la aplicación. Es un error pretender consultar o modificar una tabla que está siendo modificada. Oracle lo sabe y por eso los ‘triggers’ lanzan errores cuando se intenta.
      Las ‘técnicas’ para solucionarlo pasan por repensar qué se está haciendo, qué se quiere hacer y cuál es la mejor forma de hacerlo. No hay ‘recetas’.

      El objetivo de este artículo no era ‘solucionar tu problema’, sino explicar el extraño comportamiento que se describe en el mismo.

      En cuanto a lo que sería mejor o peor ‘aporte’ para un artículo, me gustaría decidirlo a mí, ya que soy yo quien escribe este ‘blog’.

      Por otra parte, no deja de sorprenderme tu actitud: tienes un problema, acudes a un lugar donde alguien escribe con el principal objetivo de ayudar a la gente. No encuentras la ‘solución’ que buscas y entonces de malas maneras vienes poco menos que a exigir al autor que escriba un artículo que te solucione tu ‘problema’.

      Quizá esas sean las costumbres en Colombia…

      Saludos.

      Carlos.

      • Anhon dice:

        Varios ya sabemos la arquitectura y modo de trabajo de Oracle. Pensamos q tu post serviria de algo, al menos para solucionar este problema, y poder acceder al menos en un simple “select” a una tabla q esta mutando.
        No te alteres, deberías aceptar la critica de los demás.
        Para mí, tu post no me sirve, sé q a otra gente sí.

        Saludos.

        • CarlosAL dice:

          Si ‘supieses la arquitectura’, primero: no habrías escrito este comentario; y segundo: habrías comprendido que lo que se expone en la entrada original no es una ‘solución’ al ‘problema’ de las ‘tablas mutantes’ -que no es más que un mal diseño de lógica de aplicación- sino un funcionamiento anómalo donde NO se produce el error ORA-04091 que n teoría se debería producir.
          Por otra parte, hace ya muchos años que no me altero por los malos modos de la gente en internet.
          Saludos.
          Carlos.

  5. victordemi dice:

    jejejejee
    que sencible😄 pero a mi me sirvio bastante
    lo q aca decimos es simplemente leete todo el foro weon
    jajajajaa o el bloc y despues opina y otros improperios
    jejejeje bastante buena la explicacion gracias

  6. JorgeTony dice:

    Estimado Carlos, tengo un problema cuya causa me es completamente desconocida y en alguna leí que podría este tema del mutating.

    Te cuento.

    Existe un programa desarrollado con Oracle Forms 6i. Este programa entre otras cosas llama a un reporte que sale en blanco. El reporte sólo muestra la información contenida en una tabla que es llenada dentro del form. Ahora bien justo antes de llamar al reporte se realiza un commit_form. Dentro de mi revisión determiné que los datos en la tabla sólo aparecen cuando se cierra el programa, sin importar el commit_form mencionado. ¿Qué crees tu que pueda ser?

    Muy agradecido de tu opinión de antemano. Ah! la base de datos es Oracle Standard Edition 9i.

  7. carlosal dice:

    JorgeTony:

    por lo que dices, el ‘commit_form’ no está haciendo el ‘commit’, o lo hace después de lanzarse el ‘report’. Como Oracle funciona en ‘read commited’ la transacción del informe no ve los datos de la transacción del ‘form’.
    Pero no creo que tenga mucho que ver con el fenómeno de ‘tablas mutantes’.

    Saludos.

    Carlos.

  8. loko dice:

    Me ha gustado tu articulo, me ha ayudado a entender un poco mas los disparadores

    Saludos

  9. andres dice:

    GRACIAS!!!
    por fin

    aprendi lo que es un error de mutuacion!!!

  10. diegod dice:

    bueno el articulo, muy bien explicado el tema, saludos desde Paraguay

  11. Roger dice:

    Creen una funcion, esta funcion debera hacer el select a la tabla, que esta mutando, esta funcion debe retornar el valor que ustedes esperan del select que no se puede hacer, y luego es solo llamar a la funcion en el trigger, pasandole los parametros si es necesario que la funcion pida parametros.

    • carlosal dice:

      No.

      La única forma de que eso pudiera funcionar es que dicha función ejecutase una transacción autónoma, con lo que no se verían los posibles cambios introducidos en la transacción principal.

      Una vez más: si se presenta ese error lo mejor es repensarse qué queremos hacer y por qué lo queremos hacer. La inmensa mayoría de las ocasiones se trata de errores de diseño de los datos o de los procesos que intentamos implementar.

      Saludos.

      Carlos.

Responder

Introduce tus datos o haz clic en un icono para iniciar sesión:

Logo de WordPress.com

Estás comentando usando tu cuenta de WordPress.com. Cerrar sesión / Cambiar )

Imagen de Twitter

Estás comentando usando tu cuenta de Twitter. Cerrar sesión / Cambiar )

Foto de Facebook

Estás comentando usando tu cuenta de Facebook. Cerrar sesión / Cambiar )

Google+ photo

Estás comentando usando tu cuenta de Google+. Cerrar sesión / Cambiar )

Conectando a %s

A %d blogueros les gusta esto: