![The Hunt for the Oracle Database On Commit trigger 20188367001]({"aperture":"0","credit":"","camera":"","caption":"","created_timestamp":"0","copyright":"http:\/\/seattletimes.nwsource.com\/ABPub\/2012\/08\/02\/2018836700.jpg","focal_length":"0","iso":"0","shutter_speed":"0","title":"","orientation":"0"} "The Hunt for the Oracle Database On Commit trigger 1")
Back in 1998 or 1999 I filed a bug/enhancement request in the Oracle bug database. As I was still working at Oracle back then and I had direct access to the bug database – primarily for the many beta testing activities I was involved in. The request was for an On Commit trigger, to fire immediately before the commit process would irrevokably be under way. The trigger should allow me to do some last minute business rule enforcements, to make sure the transaction was really, really okay.
At the time I was working on Oracle’s CDM RuleFrame framework for the implementation of data oriented business rules. It was somewhat novel at that time, as it focused on transaction level business rule enforcement. Our philosophy was: as long as you do not commit, it does not matter one bit if the data is not in line with all data integrity rules. Who cares? At time you commit – that is when it should be okay. The kind of same idea behind the deferred declarative database integrity constraints. The problem we were facing is that we had no way to invoke this transaction level validation of rules when the application commits. The database will take care of ‘simple’ declarative constraints, and for multi-table constraints or complex single table constraints that had to be programmed in PL/SQL, we could do nothing but enforce applications to perform this validation by deliberately invalidating a deferred check constraint from before statement level triggers for each DML type on all tables protected by our special business rules. Only if our CDM RuleFrame validation code had been called would we rectify that constraint violation and allow the transaction to continue. What I wanted to have was a way for the database to automatically invoke the CDM RuleFrame validation logic when the transaction is committed.
The enhancement request never saw any action. So today, seven years onwards, we still do not have an On Commit trigger in the database. There some ways to emulate one, but none of them is ideal. In this article a brief report on what hooks we have for our make-believe On Commit trigger.
The proceedings in the database surrounding the commit of a transaction include the following steps:
- validate deferred constraints
- delete all data from Temporary Tables with ON COMMIT DELETE ROWS
- synchronize Materialized View with ON COMMIT REFRESH
- operations on Materialized View Logs? Operations on Changed Data Capture Window?
And once the transaction is really found to be okay:
- synchronize redo log buffers to file
- release locks
- ‘submit’ pending jobs
- send alerts
I have looked into these events to see which one could be used as a trigger for my own additional PL/SQL code.
Deferred Constraints
I had some hopes for Validation of Deferred Constraints. Well, that takes place but does not fire any triggers. I once thought that a Deferred Foreign Key Constraint with ON DELETE CASCADE in combination with a delete trigger on the referring (Child) table would work: the parent is deleted somewhere during the transaction, the ON DELETE CASCADE is part of a deferred constraint and therefore takes place after the application has sent the commit command. Well, no: it turns out that even for deferred constraints, the cascade deletion of children is Immediate.
Then I hoped that perhaps in 10gR2 the DML Error Log feature – which allows us to complete statements that violate declarative integrity constraints (see for example: http://orafaq.com/node/76) while writing the offending records to a logging table – would help out: an insert trigger on this logging table in combination with a deferred constraint that always gets violated during my transactions surely would do the trick? No, it does not, as DML Error Log does not work for deferred constraints…
Note that when validation of deferred constraints runs into a violation, the entire transaction gets rolled back. There is an absolute minimum in terms of information on what violation for which record caused this rollback.
Temporary Tables
One other event at commit time is the emptying of temporary tables with ON COMMIT DELETE ROWS set. That means that when the transaction commits, all records are flushed out of the TEMPORARY table. Well, I understood it would be naive to simply create a DELETE trigger on the temporary table (which I can do) and expect it to be fired if the records are removed at commit time (which it does not). The flushing of records from the temporary table is a low level, no log action – much like TRUNCATE. I still have to check whether perhaps a TRUNCATE system even level trigger would do the trick (though I doubt it).
I though a smart approach would be: foreign key to temporary table with ON DELETE CASCADE and a delete trigger on the child table. However, it turned out that Referential Constraints to temporary tables are not allowed in the database. Another idea bites the dust.
Refreshing Materialized Views
Well, that means I am back to a solution I had found quite some time ago: the Materialized View. See for example the article Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints) for some background. The idea is quite simple:
- create a materialized view log for a table X ( the table should have a primary key)
- create a materialized view on that table – set the refresh to ‘on commit’ : create materialized view mv refresh fast on commit…
- Add an insert and/or update trigger to the materialized view (yes, you can do that!); this trigger in turn can invoke whatever code you want to run at this late stage in your transaction.
- add such logic in before insert, update and delete statement level triggers on all to be protected tables in your schema that perform some sort of dml on the table X that will cause the materialized view to be refreshed (update a record associated with the current session is probably best as to minimize contention for this table)
The disadvantages of this approach include the overhead of extra objects – a dummy table, a Materialized View Log on that table, a Materialized View as well as statement level triggers on all meaningful tables in the schema – and the potential contention between sessions all having to refresh the materialized view and locking each other out while the business rules get validated.
And more….?
I still want to investigate if the changed data capture somehow gives me any hooks for event triggers. I should also investigate the TRUNCATE trigger on tempoary tables – though I doubt this approach: I am not sure if this event may be only after the commit itself, too late for my purpose. I am also not exactly clear on what Materialized View Logs exactly go through.
If you would have any suggestions for mechanisms in the database that may help me get as close to a commit trigger as I can get, I would be thankful.
It would be a good trigger on commit for the propagation of messages via the message queue Advanced Queue without impact on the current transaction.
I am wanting to use:
TR_X01 AFTER CREATE TRIGGER ON COMMIT ON EMP FOR EACH ROW
PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
DBMS_AQ.ENQUEUE (…);
COMMIT;
EXCEPTION
SAVE_LOG (‘…’)
EMD;
Lucas,
– Reanalyzing is *only* done when the current DML-statement “can potentially correct the violation”.
In practice this is frequently the next DML-statement. The violation is then cleaned out and no more reanalyzing takes place during the rest of the TX.
– Let’s do the ‘great Java debate’ first. Did you receive the any info yet?
– Finalizing chapter 6 as we speak. The book has 10 chapters in total.
‘Hard work’ would be the correct term yes.
Would be a lot easier if my coauthor would still be around to help finish it…
Toon
Toon,
Sounds very logical. Now if we could only access that memory location…
Is re-analyzing currently violated constraints for every statement a bit expensive performance wise for large transactions? When are we going to sit down and discuss RuleGen? I am getting intrigued!
How is your book coming along? Hard work I suppose?
groeten,
Lucas
Lucas,
My 2 cents on the need for a commit trigger.
I think all constraint checking should be immediate.
Given the weakness of SQL DML though this is unfortunately
not possible.
Weakness 1: only one table can be changed by a statement.
Weakness 2: a table can only be changed in one way by a statement.
These weaknesses of SQL DML force us into dealing with ‘deferred
constraints’ and thus a solution for this such as a commit-trigger.
That given, what I think is the optimal way to deal with
this in SQL systems is as follows (and I think this is how Oracle
does it with deferable declarative constraints, however still need
to confirm this by running traces).
Every constraint is checked at statement level.
If it succeeds then all is OK.
If it fails to succeed (*and* it’s allowed so, i.e. defferable)
then this fact is ‘remembered’; it is stored somewhere (in memory)
that constraint so and so is violated for case so and so.
During every subsequent DML in the same transaction all stored violations
are re-checked (at the end of the DML execution). If success then the
violation stored in memory is cleaned up.
This enables the system to know at all times which deferrable constraints are currently being violated.
And that in turn means that at commit-time, no constraints need to be
checked anymore: the system only needs to do a memory lookup to see if current violations still exist. If so then give an error message.
By the way, this is the way the RuleGen engine does it.
Toon
PS It is very unfortunate that the SQL ISO commitee
‘invented’ that a failing commit should rollback the whole transaction.
It would have been much more practical to just have the commit fail
and have all dml-statements still posted.
John: Using a Table API does not seem to address the issue: the API gets called on a per table basis and I am looking for cross table, transaction level validation. Unless the Table API would always commit once the call is complete and I can insert my hook to transaction level validation prior to that commit, I do not see how the Table API would help. I am looking for a way to fire my custom code when the transaction is complete as far as the user or application is concerned.
Zlatko: I know of the set constraint immediate. The question is: who will do that? Does it need to be built into the application explicitly (currently yes).
Gary: I do not believe implicit commits from DDL are such an issue: most applications do not routinely perform DDL. And of course a certain amount of locking is a necessity when performing the business rule validation. Typically, with a rule like records in DEPT may not have more than three child records with JOB=CLERK in the EMP table, I want my session to have an exclusive lock on the combination of the specific DEPARTMENT and this particular Business Rule – and nothing else! So perhaps create a table with columns Logical_record_identifier and Business_Rule_Identifier and a unique constraint on the pair. Or use dbms_lock for user defined locks.
What I specifically do not want to do is exclude other sessions from doing any kind of business rule validation at all until my transaction commits. And I am afraid the MV solution may end inhibiting concurrency much more than it needs to.
Personally, I think the ‘before commit’ trigger is the wrong solution to the problem.
There’d be a whole mess about the implicit commits from DDL.
But really the problems you identify with the materialized view solution are inherent to the issue, not that solution. Pretty much any complex business rule that you need to apply would need to implement the locking to ensure that it isn’t being violated as a result of different activity by different users.
“Deferred Constraints
…Note that when validation of deferred constraints runs into a violation, the entire transaction gets rolled back…”
But, you can verify the success of deferrable constraints prior to committing them by issuing a statement
SET CONSTRAINTS ALL/constraint_name IMMEDIATE
See (for example):
Deferred Database Constraints and Forms
http://www.quest-pipelines.com/pipelines/plsql/tips03.htm
and
Solving “COMMIT business rules” on the Database Server (04/02/2002)
http://www.quest-pipelines.com/pipelines/plsql/archives/COMMIT_Rules.htm
Best regards,
Zlatko Sirotic
This is probably a good argument for always using Table APIs (TAPIs) to do DML – never doing DML directly. That way, the API’s commit routine can include your ON COMMIT logic. Unfortunately, only Oracle Designer’s Web PL/SQL generator always creates applications that use a TAPI. Designer’s Forms generator CAN be told to use a TAPI too, but most people don’t. You would have to hand code Entity objects to use a TAPI with ADF Business Objects. Still, it wouldn’t be outside the realm of possibility to write a JDeveloper extension to generate Business Objects that use a TAPI.
Hi,
we were on a search for an On Commit Trigger too for starting cleanup jobs before transactions is committed. The application uses an or-mapper. The or-Mapper changes data and link relations to objects too via a set of update statements. But only at he end of the transaction a database siede garbage collecter – implemented via PL/SQL – can clean up objects which are not referenced any more. For this cleanup an On-Commit Trigger would be the best.
Due to the missing feature we implemented a soft – commit procedure which calls the database side garbage collector before real commit;
I also would like to have an ON-ROLLBACK trigger 😉
to cleanup non transactional PL/SQL Package environement.
Karl