On the false sense of security with PL/SQL based implementation of business rules – and what to do about it

Building applications in our corner of the world typically revolves around databases. The most important bit about an application is not the application, but rather the data it creates and processes that gets stored in the database. And even before security and performance, the integrity of that data is our prime concern. Making absolutely sure our database is robust is essential. Enforcing all data oriented business rules is a sine qua non for a successful application.

In this article I will discuss a typical way of implementing business rules in the Oracle database, using PL/SQL and table triggers. An approach taken by thousands of organisations and ten of thousands or more developers. An approach proven over the years, one would think. An approach used in a business rule frameworks like CDM RuleFrame. And an approach that is fundamentally flawed.

Date centric business rules come in a number of varities. Some pertain to attributes – like NAME is a String of up to 25 characters or SALARY may not exceed 10,000 or JOB can be one of the following values. Others apply to tuples -entire records – like: an employee with JOB unequal to SALESMAN may not have a (value for) COMMISSION, a CLERK may not earn more than 3000 or You cannot Manage yourself (EMPNO should not be equal to MGR). These rules can be implemented using declarative constraints in the database – CHECK CONSTRAINTS.

Other declarative constraints are Primary Kyy and Unique Constraints and Foreign Key or Referential Constraints. The former allow us to implement rules like no two employees may have the same name and job (that would be utterly confusing) or there can be only one employee in each job per department. The latter enforces rules such as every Employee must belong to a Department.

Implementing a business rule using a declarative constraint is perfect. These constraints are built into the database kernel, are optimized for performance and are guaranteed to be enforced. When these constraints are enabled, we have the certaintity that the data in our tables complies with the rules. That is as robust as it can get.

However, more complex data rules cannot be enforced using these declarative constraints. Let’s take a look at a simple example. Again in the EMP and DEPT tables
in the SCOTT schema – you may have seen them before – we have the
following rule: "no more than three clerks in a department" . The most frequently used way to deal with this type of business rule is using database triggers and PL/SQL packages. Let’s take a look at a fairly common implementation of this particular business rule.

Implementing the business rule using PL/SQL logic: triggers and packages

The first thing to is realize the following:

1. our business rule can be violated in the following ways:

• by creating a new Employee who is a Clerk (in a department that already has three Clerks)
• by moving a Clerk to a department (that already has three Clerks)
• by promoting an Employee who is not a Clerk to the position of Clerk (in a department that already has three Clerks)

in terms of database DML events this means that Insert into table EMP as well as update of the columns DEPTNO and JOB can violate the rule and therefore should trigger validation or enforcement of that rule.

2. while the triggering events themselves are available as row-level triggers, we cannot query the table from those row level triggers (mutating table: ORA-04091: table … is mutating, trigger/function may not see it). See for example Tim Hall on Mutating Table Exceptions: http://www.oracle-base.com/articles/9i/MutatingTableExceptions.php . This means that we need to collect knowledge of the insert/update events in row-level triggers and use that knowledge at statement level.

First attempt at implementing the Business Rule

Now that we know we need row-level triggers on Insert and Update as well as a way to collect information at row-level and transfer it to the statement level where we can use it for validating the success of the statement, we can design the specification of a package used for that collection and remembering functionality:

create or replace package br_helperis

type rowid_stack is table of rowid;

procedure add_rowid( p_rowid rowid);

function get_rowid_stackreturn rowid_stack;

procedure clear_rowid_stack;

end br_helper;/ 

With this specification in hand, let’s create the row level trigger:

create or replace trigger br_emp001_aiurafter insert or update of job, deptno on empfor each rowwhen (new.job ='CLERK')begin  if ( nvl(:old.deptno,-1) <> :new.deptno       or       nvl(:old.job, 'X') <> 'CLERK'     )  then    br_helper.add_rowid(:new.rowid);  end if;end;/ 

The trigger reports its findings to the package. Note that at the beginning of the statement, the package must be reset:

create or replace trigger br_emp_biusbefore insert or updateon empbegin  br_helper.clear_rowid_stack;end;/ 

Now when the statement completes, after having inserted and/or updated (this could be a Merge operation) potentially many Employees, it falls to the after statement trigger to validate whether the business rule is honored:

create or replace trigger br_emp001_aius after insert or updateon empdeclare  l_rowid_stack br_helper.rowid_stack:= br_helper.get_rowid_stack;  l_row number;  l_count number;begin  l_row:= l_rowid_stack.first;  loop    if l_row is null    then      exit;    end if;    --    select count(*)    into   l_count    from   emp    where  deptno = ( select deptno                       from   emp                       where rowid = l_rowid_stack(l_row)                    )    and    job = 'CLERK'    ;    if l_count > 3    then      dbms_output.put_line('Too many clerks in department. This change is not allowed!');      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');    end if;    l_row:= l_rowid_stack.next(l_row);      end loop;end;/ 

Let’s try and see if this does the job:

Looking good. We failed to have four clerks in department 10. So we did a good job, I would like to say.

Note: the implementation of the BR_HELPER package is the following:

create or replace package body br_helperis

g_rowid_stack rowid_stack:= rowid_stack();

procedure add_rowid( p_rowid rowid)isbegin  g_rowid_stack.extend;  g_rowid_stack(g_rowid_stack.last):= p_rowid;end add_rowid;  

function get_rowid_stackreturn rowid_stackisbegin  return g_rowid_stack;end get_rowid_stack;  

procedure clear_rowid_stackisbegin  g_rowid_stack.delete;end clear_rowid_stack;

end br_helper;/

 

Improving on the first attempt

Although the code we have seen so far did the job, it is very crude. The BR_HELPER packages does a job suitable for many business rules, yet accomodates but a single one. So let’s make it a little more advanced and flexible:

create or replace package br_helperis

type rowid_stack is table of rowid;

procedure add_rowid( p_br in varchar2, p_rowid rowid);

function get_rowid_stack( p_br in varchar2)return rowid_stack;

procedure clear_rowid_stack( p_br in varchar2);

end br_helper;/create or replace package body br_helperis

type rowid_stack_tbl is table of rowid_stack index by varchar2(30);

g_rowid_stack_tbl rowid_stack_tbl;

procedure add_rowid( p_br in varchar2, p_rowid rowid) isbegin  if not g_rowid_stack_tbl.exists(p_br)  then    g_rowid_stack_tbl(p_br):= rowid_stack();    end if;  g_rowid_stack_tbl(p_br).extend;  g_rowid_stack_tbl(p_br)(g_rowid_stack_tbl(p_br).last):= p_rowid;end add_rowid;  

function get_rowid_stack( p_br in varchar2) return rowid_stackisbegin  if g_rowid_stack_tbl.exists(p_br)  then    return g_rowid_stack_tbl(p_br);  else    return null;  end if;end get_rowid_stack;  

procedure clear_rowid_stack( p_br in varchar2) isbegin  g_rowid_stack_tbl.delete(p_br);end clear_rowid_stack;

end br_helper;/

We add rowid’s not just blindly, but specifically in the context of a certain business rule. This allows us to later on distinguish between the records that need validation of one and business rules and those we need to consider with respect to another rule. Of course this requires changes in some of our triggers:

< >create or replace trigger br_emp001_aiurafter insert or update of job, deptno on empfor each rowwhen (new.job ='CLERK')begin  if ( nvl(:old.deptno,-1) <> :new.deptno       or       nvl(:old.job, 'X') <> 'CLERK'     )  then    br_helper.add_rowid('BR_EMP001',:new.rowid);  end if;end;/

create or replace trigger br_emp_biusbefore insert or updateon empbegin  br_helper.clear_rowid_stack('BR_EMP001');end;/

create or replace trigger br_emp001_aius after insert or updateon empdeclare  l_rowid_stack br_helper.rowid_stack:= br_helper.get_rowid_stack('BR_EMP001');  l_row number;  l_count number;  l_deptno number;begin  l_row:= l_rowid_stack.first;  loop    if l_row is null    then      exit;    end if;    -- find deptno to trace the number of validations taking place:    select deptno     into   l_deptno    from   emp     where  rowid = l_rowid_stack(l_row)    ;    dbms_output.put_line('Validating BR_EMP001 for Department '||l_deptno);    select count(*)    into   l_count    from   emp    where  deptno = ( select deptno >< >                      from   emp >< >                      where rowid = l_rowid_stack(l_row)>< >                    )    and    job = 'CLERK'    ;    if l_count > 3    then      dbms_output.put_line('Too many clerks in department. This change is not allowed!');      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');    end if;    l_row:= l_rowid_stack.next(l_row);      end loop;end;/>

With these changes, we definitely have a robust, flexible and rather elegant solution, now don’t we?

Making our implementation more efficient

The point is, the implementation lacks in efficiency: if we touch several employee in the same department, we will perform multiple business rule validations for that same department – which is not necessay. Can we make it more efficient? Yes, by combining the Business Rule Label with the Department’s primary key and only storing unique combinations:

create or replace package br_helperis

type reckey_stack is table of boolean index by varchar2(50);

procedure add_reckey( p_br in varchar2, p_reckey in varchar2);

function get_reckey_stack( p_br in varchar2)return reckey_stack;

procedure clear_reckey_stack( p_br in varchar2);

end br_helper;/

create or replace package body br_helperis

type reckey_stack_tbl is table of reckey_stack index by varchar2(30);

g_reckey_stack_tbl reckey_stack_tbl;g_empty_reckey_stack     reckey_stack;

procedure add_reckey( p_br in varchar2, p_reckey in varchar2) isbegin  if not g_reckey_stack_tbl.exists(p_br)  then    g_reckey_stack_tbl(p_br):= g_empty_reckey_stack;    end if;  g_reckey_stack_tbl(p_br)(p_reckey):= true;end add_reckey;  

function get_reckey_stack( p_br in varchar2) return reckey_stackisbegin  if g_reckey_stack_tbl.exists(p_br)  then    return g_reckey_stack_tbl(p_br);  else    return g_empty_reckey_stack;  end if;end get_reckey_stack;  

procedure clear_reckey_stack( p_br in varchar2) isbegin  g_reckey_stack_tbl.delete(p_br);end clear_reckey_stack;

end br_helper;/ 

and changes in the triggers to align them with this new functionality

create or replace trigger br_emp001_aiurafter insert or update of job, deptno on empfor each rowwhen (new.job ='CLERK')begin  if ( nvl(:old.deptno,-1) <> :new.deptno       or       nvl(:old.job, 'X') <> 'CLERK'     )  then    br_helper.add_reckey('BR_EMP001',:new.deptno);  end if;end;/

create or replace trigger br_emp001_aius after insert or updateon empdeclare  l_reckey_stack br_helper.reckey_stack:= br_helper.get_reckey_stack('BR_EMP001');  l_row number;  l_count number;begin  l_row:= l_reckey_stack.first;  loop    if l_row is null    then      exit;    end if;    dbms_output.put_line('Validating BR_EMP001 for Department '||l_row);    --    select count(*)    into   l_count    from   emp    where  deptno = l_row    and    job = 'CLERK'    ;    if l_count > 3    then      dbms_output.put_line('Too many clerks in department. This change is not allowed!');      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');    end if;    l_row:= l_reckey_stack.next(l_row);    end loop;end;/ 

Now we have a fine implementation with minimal validation overhead. Let’s try it out:

Well, can we rest on our laurels? Did we do good work?

I am hugely sorry to disappoint you, but no. We can not rest just yet. What we have created looks good and is probably similar to the vast majority of business rule implementations around the world. But there is one thing wrong with it: IT IS NOT ROBUST!

Why is this common implementation not robust?

This picture tells the story: 

The validation of the business rule is performed at statement level by querying the current status in the table. This query only involves committed changes from other sessions and pending changes from the current session. Even if the query is satisfied and the business rule found to have been adhered to, there may have been a pending change that ultimately, when committed, results in violation of the business rule. So our solution is not robust at all!

How can we make it robust?

One way of minimizing the chance for this to happen is to minimize the window of opportunity between the validation of the business rule – the query – and the time of commit. Only changes made during that interval can cause violation of the business rule. This the approach of transaction level validation, adopted by for example CDM RuleFrame. However: the only robust implementation would be complete elimination of (potentially rule violating) changes being applied between validation of the business rule and the actual commit of the transaction.

Well, there is a way to prevent changes from being made: take a lock on whatever you want to prevent from changing.

We could state: "when we start validating a department, we take a lock on that department; that means that another session cannot perform the same validation until our validation AND transaction are complete. That means the other session has to incorporate our changes in its validation!" So the logic is: in order to validate, you need to have a lock. When you have a lock, no one can make changes that invalidate your validation, so to speak. This could be done in our after statement trigger, as follows:

create or replace trigger br_emp001_aius after insert or updateon empdeclare  l_reckey_stack br_helper.reckey_stack:= br_helper.get_reckey_stack('BR_EMP001');  l_row number;  l_count number;  l_dept dept%rowtype;begin  l_row:= l_reckey_stack.first;  loop    if l_row is null    then      exit;    end if;    dbms_output.put_line('Validating BR_EMP001 for Department '||l_row);    -- Take a lock on the department!    select *    into   l_dept    from   dept    where  deptno = l_row    for update of deptno    ;    select count(*)    into   l_count    from   emp    where  deptno = l_row    and    job = 'CLERK'    ;    if l_count > 3    then      dbms_output.put_line('Too many clerks in department. This change is not allowed!');      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');    end if;    l_row:= l_reckey_stack.next(l_row);    end loop;end;/

We could have taken a table lock on EMP when we start validation of business rules against EMP but you probably and hopefully understand the effect that would have on scalability and concurrence in our database. We take a slightly less incapacitating approach: we take a lock on the Department record for which the business rule is being validated. Not until we commit or rollback the transaction will that lock be let go off. So from the time we do successful validation of the business rule until the moment we commit, we will have that lock. So now one else will be able to perform validation of the business rule for that table until the moment we have committed. And that in turn means that validation of the BR for the department  by some other session will always include our by then committed changes! 

An example:

And how can we make it efficient again?

The approach with the locks as demonstrated above has its merits. The most important one being robust. However, the effects of the locks used can reach too far. Take this simple example:

Session 1:

update empset    deptno = 10where  job = 'CLERK'and    deptno = 30/ 

Session 2: 

update deptset    loc= lower(loc)where  deptno = 10/ 

The lock taken in session one because of the validation of the Business Rule that states that Department (10) may not have more than three Clerks now prevents session 2 from changing the location of the Department. Clearly that change is completely unrelated to the business rule and in no way can cause violation of the business rule or corrupt our data’s integrity. Yet the update has to wait for the lock to be released i.e. for session 1 to commit or rollback.

In general our lock-based solution prevents all updates on a specific department by any session, also prevents DML operations that may trigger entirely different business rules. We have been too harsh, it seems. But can we increase efficiency without sacrificing robustness? I believe we can, using not the standard database locks – as we have used here – but instead user defined locks, through the dbms_lock supplied package. See for example: http://jeffkemponoracle.blogspot.com/2005/10/user-named-locks-with-dbmslock.html .

Instead of locking a specific department’s record, we create a more logical, virtual lock, on the combination of the business rule and the specific department. This lock will not prevent any normal DML activity. It is not a lock on any specific table or record. It is a lock on the logical combination of department and business rule. And that lock has to be acquired by a session in order to perform validation of that business rule for that department. Without the lock, validation fails. And when the lock is acquired, all relevant changes have been committed. The effect is the same as with the real record locks from before, but in a much less incapacitating way.

The following implementation of the BR_HELPER package uses these logical locks:

create or replace package br_helperis

type reckey_stack is table of boolean index by varchar2(50);

procedure add_reckey( p_br in varchar2, p_reckey in varchar2);

function get_reckey_stack( p_br in varchar2)return reckey_stack;

procedure clear_reckey_stack( p_br in varchar2);

/**   call this procedure to acquire a lock on the combination of a specific Business Rule and a certain record identification   if the lock cannot be acquired, an error is raised. We can extend the procedure in the future to accept a maximum waittime   before timing out. **/procedure lock_br_for_record( p_br in varchar2, p_reckey in varchar2);

end br_helper;/

create or replace package body br_helperis

type reckey_stack_tbl is table of reckey_stack index by varchar2(30);type lock_tbl is table of varchar2(128) index by varchar2(500);

g_reckey_stack_tbl reckey_stack_tbl;g_empty_reckey_stack     reckey_stack;g_lock_tbl lock_tbl;

procedure add_reckey( p_br in varchar2, p_reckey in varchar2) isbegin  if not g_reckey_stack_tbl.exists(p_br)  then    g_reckey_stack_tbl(p_br):= g_empty_reckey_stack;    end if;  g_reckey_stack_tbl(p_br)(p_reckey):= true;end add_reckey;  

function get_reckey_stack( p_br in varchar2) return reckey_stackisbegin  if g_reckey_stack_tbl.exists(p_br)  then    return g_reckey_stack_tbl(p_br);  else    return g_empty_reckey_stack;  end if;end get_reckey_stack;  

procedure clear_reckey_stack( p_br in varchar2) isbegin  g_reckey_stack_tbl.delete(p_br);end clear_reckey_stack;

function get_lockhandle_from_dbmslock( p_lock_name in varchar2) return varchar2is  PRAGMA AUTONOMOUS_TRANSACTION;  l_lock_handle VARCHAR2(128);BEGIN  DBMS_LOCK.ALLOCATE_UNIQUE   ( lockname => p_lock_name  , lockhandle => l_lock_handle  , expiration_secs => 864000 -- 10 days  );     return l_lock_handle;end get_lockhandle_from_dbmslock;

function get_lockhandle( p_lock_name in varchar2) return varchar2is  l_lock_handle VARCHAR2(128);BEGIN  -- check if lockhandle already exists in table  if g_lock_tbl.exists(p_lock_name)  then    l_lock_handle:= g_lock_tbl(p_lock_name);  else    l_lock_handle:= get_lockhandle_from_dbmslock(p_lock_name);    g_lock_tbl(p_lock_name):= l_lock_handle;  end if;  return l_lock_handle;end get_lockhandle;

procedure lock_br_for_record( p_br in varchar2, p_reckey in varchar2) is  l_lockhandle varchar2(128);  l_lock_status NUMBER;begin  l_lockhandle:= get_lockhandle( p_br||p_reckey);  l_lock_status := DBMS_LOCK.REQUEST                   ( lockhandle => l_lockhandle                   , lockmode => DBMS_LOCK.X_MODE -- eXclusive                   , timeout => 0 -- wait forever                   , release_on_commit => true                   );  CASE l_lock_status  WHEN 0 THEN NULL;  WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');  WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');  ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed - ' ||l_lock_status);  END CASE;end lock_br_for_record;

end br_helper;/ 

And the trigger performing the actual validation of the business will make use of that new locking support in BR_HELPER:

create or replace trigger br_emp001_aius after insert or updateon empdeclare  l_reckey_stack br_helper.reckey_stack:= br_helper.get_reckey_stack('BR_EMP001');  l_row number;  l_count number;  l_dept dept%rowtype;begin  l_row:= l_reckey_stack.first;  loop    if l_row is null    then      exit;    end if;    dbms_output.put_line('Validating BR_EMP001 for Department '||l_row);    -- Take a lock on the combination of the business rule and this specific department    br_helper.lock_br_for_record    ( p_br => 'BR_EMP001'    , p_reckey => l_row    );    select count(*)    into   l_count    from   emp    where  deptno = l_row    and    job = 'CLERK'    ;    if l_count > 3    then      dbms_output.put_line('Too many clerks in department. This change is not allowed!');      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');    end if;    l_row:= l_reckey_stack.next(l_row);    end loop;end;/ 

Instead of taking a normal lock (SELECT FOR UPDATE OF) we take a lock through BR_HELPER.lock_br_for_record, on the combination of the unique label of the Business Rule and the primary key of the record being logically locked. Note that this implementation is generic: it is not tied to this particular business rule or table!

Note: we have to carefully think about what we want to happen when a session cannot acquire a lock to perform business rule validation. If it waits for that lock to come available, we may end up in deadlock. If it gives up without waiting at all, we may have far too many transactions failing. So wait a little while is probably the best strategy. By the way, I would recommend performing business rule validation at transaction (i.e. commit) level at any rate. Then the period of time between validation and commit is smallest and therefore the time the lock is being held. Chances of sessions blocking each other out are minimized.

Other options: Unique Expression Based Index  and Check Constraints on Materialized Views

Using Unique Index on Expressions – see for example Using unique function based index as check constraint – we can, still declaratively, enforce rules like: no more than one CLERK per Department or not more than one birthdays per department per month. But even then at some point declarative solutions fall short, and we have to turn to PL/SQL for implementing advanced complex rules.

Another option is available through Materialized Views. Though perhaps not best known for the role they can play in implementing and enforcing business logic, Materialized Views nevertheless are a powerful instrument for rules that are too complex for normal declarative constraints or Exression Based Unique Indexs. The MV route is clean: no triggers or supporting packages are needed. Note that the Materialized View option – see also Introducing Materialized Views as mechanism for Business Rule implementation (complex declarative constraints) – powerful as it may be is still limited to a subset of complex business rule we will have to implement in our databases.

Applying the Materialized View approach to our example of ‘no more than three Clerks per Department’ we could implement that rule as follows (for explanation and details see the article referred to above):

• Create a Materialized View Log for Table EMP: since we want the rule to be enforced at commit time, we need a REFRESH ON COMMIT Materialized View and for that we need to have Materialized View Log; note that this View Log can be reused for all complex business rules concerning table EMP.
• Create a Materialized View on table EMP for this particular Business Rule; we may be able to reuse the MV for other business rules as well
• Create a Check Constraint on the Materialized View that enforced the business rule

The steps are pretty simple, especially compared to what we have seen before when implementing the same rule using PL/SQL:

1. Create the Materialized View Log for table EMP

create materialized view log on emp with rowid(deptno, job) including  new values; 

2. Create the Materialized View for the Business Rule

create materialized view emp_br001_mvrefresh fast on commitasselect deptno,      count(*) cntfrom   empwhere  job = 'CLERK' group by     deptno/ 

3. Add the Check Constraint to enforce the rule

alter table emp_br001_mvadd constraint check_cntcheck ( cnt < 4)deferrable; 

Note: since the MV will only be refresh when the transaction commits, the check constraint will take effect only at that time. It is somewhat like a deferred (declarative) constraint. In the actual time-line, this constraint will be enforced after all ‘normal’, table based deferred declarative constraint were successfully enforced. That is a good thing as you would like to enforce this business rule at the end of the transaction, rather than after each individual statement. Transferring Clerks between department would otherwise become hopelessly complex.

Putting this MV based implementation to the test

The starting point is the following:

SQL> select ename  2  ,      deptno  3  from   emp  4  where  job = 'CLERK  5  order  6  by     deptno  7  /

ENAME          DEPTNO---------- ----------MILLER             10SMITH              20ADAMS              20JAMES              30 

Now we open two database sessions and make some changes in the data: