error - oracle tabla mutando

ORA-04091: table[blah] está mutando, trigger/function puede no verlo

Question

Recientemente comencé a trabajar en una gran aplicación compleja, y me acaban de asignar un error debido a este error:

ORA-04091: table SCMA.TBL1 is mutating, trigger/function may not see it
ORA-06512: at "SCMA.TRG_T1_TBL1_COL1", line 4
ORA-04088: error during execution of trigger ''SCMA.TRG_T1_TBL1_COL1''

El disparador en cuestión se parece a

 create or replace TRIGGER TRG_T1_TBL1_COL1
BEFORE INSERT OR UPDATE OF t1_appnt_evnt_id ON TBL1
FOR EACH ROW
WHEN (NEW.t1_prnt_t1_pk is not null)
DECLARE
v_reassign_count number(20);
BEGIN
select count(t1_pk) INTO v_reassign_count from TBL1
where t1_appnt_evnt_id=:new.t1_appnt_evnt_id and t1_prnt_t1_pk is not null;
IF (v_reassign_count > 0) THEN
RAISE_APPLICATION_ERROR(-20013, ''Multiple reassignments not allowed'');
END IF;
END;

La tabla tiene una clave principal " t1_pk ", una "identificación de evento de cita" t1_appnt_evnt_id y otra columna " t1_prnt_t1_pk " que pueden contener o no el t1_pk otra fila.

Parece que el desencadenante está tratando de asegurarse de que nadie más con el mismo t1_appnt_evnt_id haya referido al mismo que esta fila se refiere a una referencia a otra fila, si esta se refiere a otra fila.

El comentario sobre el informe de errores del DBA dice "quitar el activador y realizar el control en el código", pero lamentablemente tienen un marco de generación de código de propiedad en capas sobre Hibernate, por lo que ni siquiera puedo descubrir dónde está realmente se escribe, así que espero que haya una manera de hacer que este disparador funcione. ¿Esta ahí?

Answer 1

Estoy de acuerdo con Dave en que el resultado deseado por probalby puede y debe lograrse usando restricciones integradas tales como índices únicos (o restricciones únicas).

Si realmente necesita evitar el error de la tabla de mutación, la forma habitual de hacerlo es crear un paquete que contenga una variable con ámbito de paquete que sea una tabla de algo que pueda usarse para identificar las filas modificadas (creo que ROWID es posible, de lo contrario debe usar PK, no uso Oracle actualmente, así que no puedo probarlo). El desencadenador PARA CADA FILA completa luego esta variable con todas las filas modificadas por la instrucción, y luego hay una DESPUÉS de cada activador de enunciado que lee las filas y las valida.

Algo así como (la sintaxis probablemente sea incorrecta, hace algunos años que no trabajo con Oracle)

CREATE OR REPLACE PACKAGE trigger_pkg;
PROCEDURE before_stmt_trigger;
PROCEDURE for_each_row_trigger(row IN ROWID);
PROCEDURE after_stmt_trigger;
END trigger_pkg;
CREATE OR REPLACE PACKAGE BODY trigger_pkg AS
TYPE rowid_tbl IS TABLE OF(ROWID);
modified_rows rowid_tbl;
PROCEDURE before_stmt_trigger IS
BEGIN
modified_rows := rowid_tbl();
END before_each_stmt_trigger;
PROCEDURE for_each_row_trigger(row IN ROWID) IS
BEGIN
modified_rows(modified_rows.COUNT) = row;
END for_each_row_trigger;
PROCEDURE after_stmt_trigger IS
BEGIN
FOR i IN 1 .. modified_rows.COUNT LOOP
SELECT ... INTO ... FROM the_table WHERE rowid = modified_rows(i);
-- do whatever you want to
END LOOP;
END after_each_stmt_trigger;
END trigger_pkg;
CREATE OR REPLACE TRIGGER before_stmt_trigger BEFORE INSERT OR UPDATE ON mytable AS
BEGIN
trigger_pkg.before_stmt_trigger;
END;
CREATE OR REPLACE TRIGGER after_stmt_trigger AFTER INSERT OR UPDATE ON mytable AS
BEGIN
trigger_pkg.after_stmt_trigger;
END;
CREATE OR REPLACE TRIGGER for_each_row_trigger
BEFORE INSERT OR UPDATE ON mytable
WHEN (new.mycolumn IS NOT NULL) AS
BEGIN
trigger_pkg.for_each_row_trigger(:new.rowid);
END;

Answer 2

Con cualquier solución basada en disparador (o basada en código de aplicación), debe ponerla en bloqueo para evitar daños en los datos en un entorno multiusuario. Incluso si el desencadenador funcionó, o se reescribió para evitar el problema de la tabla mutante, no evitaría que 2 usuarios actualizaran simultáneamente t1_appnt_evnt_id al mismo valor en las filas donde t1_appnt_evnt_id no es nulo: supongamos que actualmente no hay filas donde t1_appnt_evnt_id = 123 y t1_prnt_t1_pk no es nulo:

Session 1> update tbl1
set t1_appnt_evnt_id=123
where t1_prnt_t1_pk =456;
/* OK, trigger sees count of 0 */
Session 2> update tbl1
set t1_appnt_evnt_id=123
where t1_prnt_t1_pk =789;
/* OK, trigger sees count of 0 because
session 1 hasn''t committed yet */
Session 1> commit;
Session 2> commit;

¡Ahora tiene una base de datos corrupta!

La forma de evitar esto (en el desencadenante o en el código de la aplicación) sería bloquear la fila primaria en la tabla a la que hace referencia t1_appnt_evnt_id = 123 antes de realizar la comprobación:

select appe_id
into v_app_id
from parent_table
where appe_id = :new.t1_appnt_evnt_id
for update; 

Ahora el activador de la sesión 2 debe esperar a que la sesión 1 se confirme o se retrotraiga antes de realizar la comprobación.

¡Sería mucho más simple y seguro implementar el índice de Dave Costa!

Finalmente, me alegro de que nadie haya sugerido agregar PRAGMA AUTONOMOUS_TRANSACTION a su desencadenante: esto a menudo se sugiere en los foros y funciona en la medida en que la cuestión de la mesa mutadora desaparezca, ¡pero empeora el problema de integridad de datos! Así que no lo hagas ...

Answer 3

Creo que no estoy de acuerdo con tu descripción de lo que el disparador está tratando de hacer. Me parece que está destinado a hacer cumplir esta regla comercial: para un valor dado de t1_appnt_event, solo una fila puede tener un valor no nulo de t1_prnt_t1_pk a la vez. (No importa si tienen el mismo valor en la segunda columna o no).

Curiosamente, está definido para UPDATE OF t1_appnt_event pero no para la otra columna, así que creo que alguien podría romper la regla actualizando la segunda columna, a menos que haya un disparador separado para esa columna.

Puede haber una forma de crear un índice basado en funciones que aplique esta regla para que pueda deshacerse por completo del desencadenador. Se me ocurrió una manera, pero requiere algunas suposiciones:

• La tabla tiene una clave primaria numérica
• La clave principal y t1_prnt_t1_pk son ambos siempre positivos

Si estas suposiciones son verdaderas, puede crear una función como esta:

dev> create or replace function f( a number, b number ) return number deterministic as
2 begin
3 if a is null then return 0-b; else return a; end if;
4 end;

y un índice como este:

CREATE UNIQUE INDEX my_index ON my_table
( t1_appnt_event, f( t1_prnt_t1_pk, primary_key_column) );

Por lo tanto, las filas donde la columna PMNT es NULL aparecerían en el índice con el inverso de la clave principal como el segundo valor, para que nunca entren en conflicto entre sí. Las filas donde no es NULO usarían el valor real (positivo) de la columna. La única forma en que podría obtener una violación de restricción sería si dos filas tienen los mismos valores no NULOS en ambas columnas.

Esto es quizás demasiado "inteligente", pero podría ayudarlo a resolver su problema.

Actualización de Paul Tomblin: fui con la actualización a la idea original que igor puso en los comentarios:

 CREATE UNIQUE INDEX cappec_ccip_uniq_idx
ON tbl1 (t1_appnt_event,
CASE WHEN t1_prnt_t1_pk IS NOT NULL THEN 1 ELSE t1_pk END);

Answer 4

Tuve un error similar con Hibernate. Y la sesión de descarga mediante el uso de

getHibernateTemplate().saveOrUpdate(o);
getHibernateTemplate().flush();

resolvió este problema para mí. (No estoy publicando mi bloque de código ya que estaba seguro de que todo estaba escrito correctamente y debería funcionar, pero no lo hizo hasta que agregué la declaración flush () anterior). Tal vez esto pueda ayudar a alguien.