Skip navigation.

Foreordain my Transaction to Fail

Kevin Meade's picture

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

Nice one

I love a solution waiting for a problem. I'm still searching for some from v7.3. I'll add it to the list.

Commit detection?

Do I understand it correctly that you have implemented a commit-detection? Because I am lost in that "detecting a failure before it fails"...
I guess that it might be a good thing for complex PL/SQL code analysis where everything should run as a huge transaction but someone would put a commit by accident before end of it.
It might be a good thing for small databases where our undo space is very limited (I had similar situation where 2 projects were working on the same server - one has used 99.99% of the undo space while leaving 2MB for the other project). In such case our transaction might be commiter prematurely, savepoints might be lost and some transaction items might be even rolled back. Don't ask what happen when you have multiple transactions running - it is a mess :)
Your hack would allow PL/SQL developer to detect such problems. Now please find a way to detect and handle "out of SGA memory" issue for me and I would be very happy :D

not intended by me for production use

Let me clarify again some points I tried to make:

1) I do not condone this as a production solution of any kind. My buddy and I used it only in testing to find an unwanted commit being done in the code stream. Although I am certain that people smarter than I will find usability in the hack. You have suggested one that sounds like it has merit.

2) This is not a case of "detecting a failure before it fails". I never said that. We are not interested in detecting failure. That is why this solution holds interest for me. Here are some highlights:

a) we do not want to modify our code stream directly
b) we want to know that even though a commit may be executed, no commit will happen and instead all work will be rolled back
c) to achieve this we use a hack tied to commit time processing that forces a failure at commit time
d) whether the code stream completes successfully or not we do not care, we have already determined the transaction will fail, that is the point of the hack

Hope I am clearer. Thanks for you commentary, it was good. Kevin

Nice work!

As always, I find your explanations enlightening and helpful!