Re: Oracle does not rollback correctly after ENABLE CONSTRAINT failed

From: John K. Hinsdale <hin_at_alma.com>
Date: 5 Feb 2007 14:50:51 -0800
Message-ID: <1170715851.702965.31370@k78g2000cwa.googlegroups.com>

On Feb 5, 3:56 pm, Maxim Demenko <mdeme..._at_gmail.com> wrote:

> You are surely aware, that, in opposite to Carlos example, yours will
> get a parse error ( you can easily see it in the 10046 trace) and so
> never will be executed, so no impact on previous transaction. A valid
> DDL statement (like alter table add constraint in previous example) will
> be executed , error occurs within recursive sql and statement itself
> fails, but transaction is commited exactly due to reason mentioned by
> Carlos.

OK, Maxim, tried it and you are exactly right. So it would seem that if the DDL fails because of parse error, the commit is not done, and things can be rolled back, but if gets past parse, and fails during execution, the commit IS done and one cannot roll back.

The upshot of all this for our original poster is that the re-enabling of constraints cannot be made part of the work "undone" if things go bad? I.e., the constraints cannot be disabled/re-enabled as "part" of the transaction.

The only way I can think of to handle this processing robustly and gracefully is:

Disable the constraints
Do the INSERT/UPDATE/DELETEs ... do not commit yet.
Before enabling constraints, run queries that will yield non-null result set(s) in those cases where an attempt to enable the constraint WOULD fail. E.g., in the original poster's example, before issuing the re-enable:

ALTER TABLE EMPL ENABLE CONSTRAINT EMPL_DEPT_FK; first run:

SELECT count(*) into nbad
FROM (

      /* Dangling IDs in EMPL.dept_id */
      SELECT dept_id FROM empl
      MINUS
      SELECT id FROM dept

)

This will yield IDs from EMPL that are "dangling references" into DEPT. Then only if things look good (i.e,. no danglers exist) try the ALTER TABLE. If things look bad, roll back:

if nbad > 0 then

         rollback;
    else
      ALTER TABLE empl ENABLE CONSTRAINT empl_dept_fk;     end if;

If there are multiple constraints to be disabled and subsequently re-enabled, one would have to do ALL the updating, then ALL the integrity checking queries, and if ANY of the checks failed, do a total rollback. Otherwise, only after ALL integrity checks succeed, proceed with ALL the re-enables.

uhaham, is this helpful? Comments?

Received on Mon Feb 05 2007 - 16:50:51 CST