Foreordain my Transaction to Fail

I saw one of those really interesting pieces of code recently. A guy wanted to run his transaction and make it fail when it was all done. Normally one would put ROLLBACK at the end of the transaction in order to undo a transaction's work. But this guy did not want to do that. He wanted to keep his transaction code unchanged, commit at the end and everything. He had several reasons for this, among them being that he did not have access to all the code he was working with and thus could not put the ROLLBACK where it was needed, and indeed suspected (as we eventually found to be true), that somewhere in the code stream there was a commit being done without his permission thus splitting his transaction in ways he did not intend. So he wanted a way to FOREORDAIN (determine ahead of time) that his transaction would fail no matter even if it went to conclusion without error. For this he came up with a I think a clever hack. Seems to me this might have some use, if I can figure out what that use might be. So here is the cool solution.

The trick of course turned out to be getting some kind of control over commit processing with a commit time event. In that vein you need to find some Oracle hook that happens at commit time. There are at least two common examples:

1) user a materialized view with refresh fast on commit
2) use a deferrable initially deferred constraint

Both these mechanisms cause work to happen at commit time. What they each do is different so you pick what you want depending upon what you need. Let me say now, that these two features are hacks and I do not as yet promote either for use in a production system.

So here is the solution to his problem:

create global temporary table gtt_fail_me
(
    x number
         constraint check_x check ( 1 != 1 ) 
         deferrable 
         initially deferred
)
/

HMM... that is interesting. That check constraint will always fail, what's more because of the doubly deferred nature of the constraint, the constraint will not be evaluated till a commit is issued. That sounds like the right stuff; to cause a failure when a commit is done. Commit being the end of the transaction, if we can get a failure when the commit is attempted, the transaction should fail. So here is an execution of this grand scheme.

--
-- first we insert a row into this fail_me table
--
SQL> insert into gtt_fail_me values (1);

1 row created.

Using a Global Temporary Table means that there won't be any conflict management between transactions.

--
-- now do some stuff and commit the work
--
SQL> begin
  2     insert into a values (1,null);
  3     commit;
  4  end;
  5  /
begin
*
ERROR at line 1:
ORA-02091: transaction rolled back
ORA-02290: check constraint (KM21378.CHECK_X) violated
ORA-06512: at line 3

--
-- make sure we have no data in the tables affected
--
SQL> select * from a;

no rows selected

SQL> select * from gtt_fail_me;

no rows selected

Yep, it all looks good to me. We inserted a row into the GTT_FAIL_ME table. The check constraint will always fail and because the constraint is deferred that failure won't come till the commit is done. We can see this based on the error stack. Notice the error at line 3. This is good, because it means that if we have a very complex transaction using lots of code and somewhere in the code was a hidden commit event, this dump will show us where it is. Seems pretty neat to me; nice and twisted.

Philosophically this is kind of interesting as a strategy. Rather than waiting till the end of a transaction to decide the transaction should fail and rollback because it was bad, we decide at the beginning that the transaction will fail, even if it is good and we make it so in a way that the transaction cannot avoid it. My Buddy Mike is now trying to figure out it there is some legitimate use for this GODLIKE ability. Maybe you guys can can help with that?

Kevin