Pages

Thursday, September 13, 2007

Way back in 1995/1996, when I started working, I remember my colleagues being very enthusiastic about the upgrade from Oracle6 to Oracle7. Many new features became available, and the one that stood out the most, according to my coworkers, was the birth of database triggers. Before Oracle7, only primary, unique, check and unenforced foreign keys were implemented. The other constraints were typically validated in the client. With the arrival of database triggers, it was now possible to have all business rules implemented on the database. No matter what client application was accessing the database, they all had to satisfy the business rules. A great idea.

I too loved the idea. In 1998 I was responsible for implementing the entire business rule layer of a new system and I made sure no invalid data was able to enter the database. I remember my colleagues even getting a little frustrated when simple insert scripts to enter test data didn't work because of yet another business rule implemented by me with the use of database triggers. We were using Headstart Utilities, which made the implementation of business rules with database triggers even easier, by offering numerous utilities that generated database trigger code for:- populating the ID column from a sequence- populating auditing columns- standard code for circumventing the well-known mutating table problem- and much moreThe result was a system that used database triggers heavily which I thought was very robust. And I certainly wasn't alone. This way of dealing with business rules became the de facto standard in Oracle consultancy in the Netherlands for many years to come.

Then, somewhere in 2002/2003, I discovered the Ask Tom website, and I was intrigued by the quality of the answers, by the abundance of test cases clearly showing all Tom's points, and by the use of clear and simple language that makes reading for foreigners almost a piece of cake. And above all, I agreed with virtually every opinion of Tom on several subjects. But to my big surprise, he seemed to dislike database triggers. How on earth was that possible? One of my favourite features! I concluded I had met the first topic on which we disagreed. His arguments against database triggers are roughly:

1) they make things happen "automagically"2) they are rarely implemented correct3) they slow down DML

At the time, I could understand the first point, but the second and third one didn't drive home with me. And, what's the alternative when you want to implement business rules, but don't want to use database triggers? About two years ago, during and just after Tom gave a three day seminar in my home city, it all suddenly made sense to me. Shortly after, the alternative became clear as well. First of all let me elaborate on the disadvantages of database triggers.

1) Database triggers make things happen "automagically"

The first point was one I could immediately agree with, and my initial reaction was: that's the price to pay. It's possible you deliver value 'A' and some trigger changed the value into 'B'. This is non intuitive and you might get surprised by this behaviour. Things will get worse when doing DML inside a trigger, because you could get a chain of reactions: table t1 gets mutated, which fires a trigger that does an insert into t2, which has a trigger that does ... and so on. This can be a nightmare to understand and debug and you can imagine some nasty unexpected side effects happening. This problem can of course be slightly alleviated by proper coding and documentation, but it will never be straightforward.

2) Database triggers are rarely implemented correct

Most developers tend to forget they are writing code that has to work in a multi user environment, especially when coding database triggers. And I used to be one of them. For example, let's take a typical overlap business rule stating that periods, defined by the columns startdate and enddate, shall not overlap with other periods in the same table. If I opened two SQL*Plus sessions and inserted a period in session1, not committing yet, then switch to session2 and inserted the same period, they would both not see each others data and be not overlapping. Now simply commit both and voila: inconsistent data has entered your database! Because most applications, including Oracle*Forms, almost always do their DML statements immediately followed by a commit, the chances of this happening are reduced a lot, but it remains a fundamental mistake.

I have spent a lot of time tuning queries and batches lately on a system that was built on Oracle 7.3 with A LOT of triggers. Using a database trigger compared to the alternative - which I'll discuss in a minute - is a bit slower. But the real pain comes when doing set operations, typically at night in batches. If coded recently, the code is probably doing single DML statements or bulk processing in PL/SQL.

But when doing bulk processing on table X, you will find yourself very unpleasantly surprised by the fact that all row level triggers on table X are fired "for each row". So even if you coded all nice bulk inserts adding 100 rows at once to table X, all your row level triggers will fire 100 times and all SQL in those row level triggers are executed 100 times, as can be seen in this little test:

So 100 executions of the insert statement and certainly no nice bulk processing as in the initial update statement. If your system was built using database triggers to enforce your business rules, like I described in the first paragraphs, then you are in a lot of trouble performance wise these days, because:- you are stuck doing things row by row, for the enforcement of each and every business rule- the data volumes are increasing every year, in a successful business at leastSo you may find yourself being unable to complete a simple batch job processing 1,000,000 records in a nightly batch window, only because of the presence of numerous row level triggers.

So what's the alternative?

The alternative, as Tom has often mentioned, is an api. An Application Programming Interface. In other words, just a package containing all actions that can be done to the entity. For each action all necessary checks are performed. I'll show the difference between the two approaches with an example of implementing a business rule "It is not possible to end a customer's last contract." on a contracts table. First I'll demonstrate the trigger approach, which suffers from the mutating table problem, so some code to circumvent this is applied as well:

So we can see the business rule is enforced. Note that there is some code as well to lock the customer, so in case another session simultaneously tries to end another contracts from the same customer, then this session gets blocked until the first session is either committed or rollbacked.

Now let's take a look at an api approach. Please note that I used the phrase "an api approach", because I could think of several valid ones:

Here the api calls have exactly the same results as the trigger based approach. But let's compare the performance of both, by performing 2000 updates, 1000 successful and 1000 unsuccessful ones caught in an exception handler:

So a single ending of a contract is almost four times as fast and using only 32% of the latches. I admit that I have chosen a business rule that is one of the worst kinds. It has to take care of the mutating table problem and it has to have code to handle multi user situations. The differences will not be this big when for example comparing a business rule to automatically fill an ID by a sequence. But even then there are differences as can be seen in this OTN thread.

But how do you make sure people cannot write direct DML statements using the api approach?

This is done by getting your privileges right. Typically you will have an owner schema that has only granted the execute privilege on all api's to all user schemas. There shouldn't be a single table privilege granted to anyone. You could even revoke the "create session" privilege from the owner schema after each install, to be really safe. This way no direct DML on the underlying tables is allowed.

But what if my screen painting application should be based on a table or view?

If there really is no other way in your developing environment than selecting a table or view to create screens/forms for entering data, then you can always create views with instead of triggers. These triggers will then redirect all DML on the views to the existing api. Additionally, you'd have to grant select, update, insert and delete on those views to the users.

But how is an api advantageous in batches when doing set operations?

Because now you have the flexibility to code a separate api call to update in bulk. Or, to code one routine that processes an array of input values where you'd call the routine with only 1 record during daily OLTP activities, and with lots of records during batches at night. Here is an example with the contracts table, doing a 5000 row update. The contracts_trigger_approach is the same as listed earlier. For the api approach, a new api is created with a general routine that can handle either 1 or many ending of contracts at the same time:

Here an implementation is chosen that is based on the assumption that most contracts that are ended will not fail due to this business rule. That's why the update is done first, and after the update a check is performed to see if all records got updated. If not all records were updated, an error is raised. Here too, different implementations are possible.

A performance comparison between a 5000 row update using the trigger approach and the api approach:

So even when you are doing a single update statement updating 5000 rows, the trigger approach is more than two times slower. The total number of latches do not differ very much, but the difference in latches on the library cache is still huge. The more business rules you have, the bigger the difference will be between the two alternatives.

The api approach is in my opinion very clear, less code, faster and more scalable. Needless to say, I now totally agree with Tom on database triggers: I hate them and I wish they didn't exist :-)

All new home grown systems will never use database triggers anymore, but I guess professionally I will have to live with them for a long time, unfortunately ...

6 comments:

Imagine two contracts of the same customer with end_date = null.what would happen if two sessions run the api each one for each contract at the same time? Don't you have to lock at contract level (or contract level + end_date=null)?

If two contracts of the same customer would be entered, then they are executed serially because of the lock_customer procedure. So the second one will have to wait for the first one to finish (rollback/commit).

You are absolutely right of course. I coded the lock_customer routine for the trigger approach, but the api approach needs it as well. The post is updated to reflect the new situation. The performance numbers have also been adjusted.

Funny how I wrote "And I used to be one of them." and still forgot about it...

Thanks for your thorough reading and thinking and for mentioning it to me!