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

9

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:

<br />create or replace package br_helper<br />is<br /><br />type rowid_stack is table of rowid;<br /><br />procedure add_rowid( p_rowid rowid);<br /><br />function get_rowid_stack<br />return rowid_stack;<br /><br />procedure clear_rowid_stack;<br /><br />end br_helper;<br />/<br />&nbsp;

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

create or replace trigger br_emp001_aiur<br />after insert or update of job, deptno <br />on emp<br />for each row<br />when (new.job ='CLERK')<br />begin<br />  if ( nvl(:old.deptno,-1) &lt;&gt; :new.deptno<br />       or<br />       nvl(:old.job, 'X') &lt;&gt; 'CLERK'<br />     )<br />  then<br />    br_helper.add_rowid(:new.rowid);<br />  end if;<br />end;<br />/<br />&nbsp;

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_bius<br />before insert or update<br />on emp<br />begin<br />  br_helper.clear_rowid_stack;<br />end;<br />/<br />&nbsp;

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 <br />after insert or update<br />on emp<br />declare<br />  l_rowid_stack br_helper.rowid_stack:= br_helper.get_rowid_stack;<br />  l_row number;<br />  l_count number;<br />begin<br />  l_row:= l_rowid_stack.first;<br />  loop<br />    if l_row is null<br />    then<br />      exit;<br />    end if;<br />    --<br />    select count(*)<br />    into   l_count<br />    from   emp<br />    where  deptno = ( select deptno <br />                      from   emp <br />                      where rowid = l_rowid_stack(l_row)<br />                    )<br />    and    job = 'CLERK'<br />    ;<br />    if l_count &gt; 3<br />    then<br />      dbms_output.put_line('Too many clerks in department. This change is not allowed!');<br />      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');<br />    end if;<br />    l_row:= l_rowid_stack.next(l_row);  <br />    end loop;<br />end;<br />/<br />&nbsp;

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 <br />package body br_helper<br />is<br /><br />g_rowid_stack rowid_stack:= rowid_stack();<br /><br />procedure add_rowid( p_rowid rowid)<br />is<br />begin<br />  g_rowid_stack.extend;<br />  g_rowid_stack(g_rowid_stack.last):= p_rowid;<br />end add_rowid;  <br /><br />function get_rowid_stack<br />return rowid_stack<br />is<br />begin<br />  return g_rowid_stack;<br />end get_rowid_stack;  <br /><br />procedure clear_rowid_stack<br />is<br />begin<br />  g_rowid_stack.delete;<br />end clear_rowid_stack;<br /><br />end br_helper;<br />/<br /><br />&nbsp;

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_helper<br />is<br /><br />type rowid_stack is table of rowid;<br /><br />procedure add_rowid<br />( p_br in varchar2<br />, p_rowid rowid<br />);<br /><br />function get_rowid_stack<br />( p_br in varchar2)<br />return rowid_stack;<br /><br />procedure clear_rowid_stack<br />( p_br in varchar2)<br />;<br /><br />end br_helper;<br />/<br />create or replace <br />package body br_helper<br />is<br /><br />type rowid_stack_tbl is table of rowid_stack index by varchar2(30);<br /><br />g_rowid_stack_tbl rowid_stack_tbl;<br /><br />procedure add_rowid<br />( p_br in varchar2<br />, p_rowid rowid<br />) is<br />begin<br />  if not g_rowid_stack_tbl.exists(p_br)<br />  then<br />    g_rowid_stack_tbl(p_br):= rowid_stack();  <br />  end if;<br />  g_rowid_stack_tbl(p_br).extend;<br />  g_rowid_stack_tbl(p_br)(g_rowid_stack_tbl(p_br).last):= p_rowid;<br />end add_rowid;  <br /><br />function get_rowid_stack<br />( p_br in varchar2<br />) return rowid_stack<br />is<br />begin<br />  if g_rowid_stack_tbl.exists(p_br)<br />  then<br />    return g_rowid_stack_tbl(p_br);<br />  else<br />    return null;<br />  end if;<br />end get_rowid_stack;  <br /><br />procedure clear_rowid_stack<br />( p_br in varchar2<br />) is<br />begin<br />  g_rowid_stack_tbl.delete(p_br);<br />end clear_rowid_stack;<br /><br />end br_helper;<br />/<br /><br />

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:

&lt; <code>&gt;create or replace trigger br_emp001_aiur<br />after insert or update of job, deptno <br />on emp<br />for each row<br />when (new.job ='CLERK')<br />begin<br />  if ( nvl(:old.deptno,-1) &lt;&gt; :new.deptno<br />       or<br />       nvl(:old.job, 'X') &lt;&gt; 'CLERK'<br />     )<br />  then<br />    br_helper.add_rowid('BR_EMP001',:new.rowid);<br />  end if;<br />end;<br />/<br /><br /><br />create or replace trigger br_emp_bius<br />before insert or update<br />on emp<br />begin<br />  br_helper.clear_rowid_stack('BR_EMP001');<br />end;<br />/<br /><br />create or replace trigger br_emp001_aius <br />after insert or update<br />on emp<br />declare<br />  l_rowid_stack br_helper.rowid_stack:= br_helper.get_rowid_stack('BR_EMP001');<br />  l_row number;<br />  l_count number;<br />  l_deptno number;<br />begin<br />  l_row:= l_rowid_stack.first;<br />  loop<br />    if l_row is null<br />    then<br />      exit;<br />    end if;<br />    -- find deptno to trace the number of validations taking place:<br />    select deptno <br />    into   l_deptno<br />    from   emp <br />    where  rowid = l_rowid_stack(l_row)<br />    ;<br />    dbms_output.put_line('Validating BR_EMP001 for Department '||l_deptno);<br />    select count(*)<br />    into   l_count<br />    from   emp<br />    where  deptno = ( select deptno &gt;<br />&lt; </co
de>
<code>&gt;                      from   emp &gt;<br />&lt; </code><code>&gt;                      where rowid = l_rowid_stack(l_row)&gt;<br />&lt; </code><code>&gt;                    )<br />    and    job = 'CLERK'<br />    ;<br />    if l_count &gt; 3<br />    then<br />      dbms_output.put_line('Too many clerks in department. This change is not allowed!');<br />      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');<br />    end if;<br />    l_row:= l_rowid_stack.next(l_row);  <br />    end loop;<br />end;<br />/<br />&gt;</code>

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_helper<br />is<br /><br />type reckey_stack is table of boolean index by varchar2(50);<br /><br />procedure add_reckey<br />( p_br in varchar2<br />, p_reckey in varchar2<br />);<br /><br />function get_reckey_stack<br />( p_br in varchar2)<br />return reckey_stack;<br /><br />procedure clear_reckey_stack<br />( p_br in varchar2)<br />;<br /><br />end br_helper;<br />/<br /><br />create or replace <br />package body br_helper<br />is<br /><br />type reckey_stack_tbl is table of reckey_stack index by varchar2(30);<br /><br />g_reckey_stack_tbl reckey_stack_tbl;<br />g_empty_reckey_stack     reckey_stack;<br /><br />procedure add_reckey<br />( p_br in varchar2<br />, p_reckey in varchar2<br />) is<br />begin<br />  if not g_reckey_stack_tbl.exists(p_br)<br />  then<br />    g_reckey_stack_tbl(p_br):= g_empty_reckey_stack;  <br />  end if;<br />  g_reckey_stack_tbl(p_br)(p_reckey):= true;<br />end add_reckey;  <br /><br />function get_reckey_stack<br />( p_br in varchar2<br />) return reckey_stack<br />is<br />begin<br />  if g_reckey_stack_tbl.exists(p_br)<br />  then<br />    return g_reckey_stack_tbl(p_br);<br />  else<br />    return g_empty_reckey_stack;<br />  end if;<br />end get_reckey_stack;  <br /><br />procedure clear_reckey_stack<br />( p_br in varchar2<br />) is<br />begin<br />  g_reckey_stack_tbl.delete(p_br);<br />end clear_reckey_stack;<br /><br />end br_helper;<br />/&nbsp;

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

create or replace trigger br_emp001_aiur<br />after insert or update of job, deptno <br />on emp<br />for each row<br />when (new.job ='CLERK')<br />begin<br />  if ( nvl(:old.deptno,-1) &lt;&gt; :new.deptno<br />       or<br />       nvl(:old.job, 'X') &lt;&gt; 'CLERK'<br />     )<br />  then<br />    br_helper.add_reckey('BR_EMP001',:new.deptno);<br />  end if;<br />end;<br />/<br /><br />create or replace trigger br_emp001_aius <br />after insert or update<br />on emp<br />declare<br />  l_reckey_stack br_helper.reckey_stack:= br_helper.get_reckey_stack('BR_EMP001');<br />  l_row number;<br />  l_count number;<br />begin<br />  l_row:= l_reckey_stack.first;<br />  loop<br />    if l_row is null<br />    then<br />      exit;<br />    end if;<br />    dbms_output.put_line('Validating BR_EMP001 for Department '||l_row);<br />    --<br />    select count(*)<br />    into   l_count<br />    from   emp<br />    where  deptno = l_row<br />    and    job = 'CLERK'<br />    ;<br />    if l_count &gt; 3<br />    then<br />      dbms_output.put_line('Too many clerks in department. This change is not allowed!');<br />      RAISE_APPLICATION_ERROR(-20007, 'Too many clerks in a department. Please retry this statement.');<br />    end if;<br />    l_row:= l_reckey_stack.next(l_row);  <br />  end loop;<br />end;<br />/<br />&nbsp;

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

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 p
erform
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:

<br />update emp<br />set    deptno = 10<br />where  job = 'CLERK'<br />and    deptno = 30<br />/<br />&nbsp;

Session 2: 

update dept<br />set    loc= lower(loc)<br />where  deptno = 10<br />/<br />&nbsp;

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_helper<br />is<br /><br />type reckey_stack is table of boolean index by varchar2(50);<br /><br />procedure add_reckey<br />( p_br in varchar2<br />, p_reckey in varchar2<br />);<br /><br />function get_reckey_stack<br />( p_br in varchar2)<br />return reckey_stack;<br /><br />procedure clear_reckey_stack<br />( p_br in varchar2)<br />;<br /><br />/**<br />   call this procedure to acquire a lock on the combination of a specific Business Rule and a certain record identification<br />   if the lock cannot be acquired, an error is raised. We can extend the procedure in the future to accept a maximum waittime<br />   before timing out. <br />**/<br />procedure lock_br_for_record<br />( p_br in varchar2<br />, p_reckey in varchar2<br />);<br /><br /><br />end br_helper;<br />/<br /><br />create or replace <br />package body br_helper<br />is<br /><br />type reckey_stack_tbl is table of reckey_stack index by varchar2(30);<br />type lock_tbl is table of varchar2(128) index by varchar2(500);<br /><br />g_reckey_stack_tbl reckey_stack_tbl;<br />g_empty_reckey_stack     reckey_stack;<br />g_lock_tbl lock_tbl;<br /><br />procedure add_reckey<br />( p_br in varchar2<br />, p_reckey in varchar2<br />) is<br />begin<br />  if not g_reckey_stack_tbl.exists(p_br)<br />  then<br />    g_reckey_stack_tbl(p_br):= g_empty_reckey_stack;  <br />  end if;<br />  g_reckey_stack_tbl(p_br)(p_reckey):= true;<br />end add_reckey;  <br /><br />function get_reckey_stack<br />( p_br in varchar2<br />) return reckey_stack<br />is<br />begin<br />  if g_reckey_stack_tbl.exists(p_br)<br />  then<br />    return g_reckey_stack_tbl(p_br);<br />  else<br />    return g_empty_reckey_stack;<br />  end if;<br />end get_reckey_stack;  <br /><br />procedure clear_reckey_stack<br />( p_br in varchar2<br />) is<br />begin<br />  g_reckey_stack_tbl.delete(p_br);<br />end clear_reckey_stack;<br /><br />function get_lockhandle_from_dbmslock<br />( p_lock_name in varchar2<br />) return varchar2<br />is<br />  PRAGMA AUTONOMOUS_TRANSACTION;<br />  l_lock_handle VARCHAR2(128);<br />BEGIN<br />  DBMS_LOCK.ALLOCATE_UNIQUE <br />  ( lockname =&gt; p_lock_name<br />  , lockhandle =&gt; l_lock_handle<br />  , expiration_secs =&gt; 864000 -- 10 days<br />  );   <br />  return l_lock_handle;<br />end get_lockhandle_from_dbmslock;<br /><br />function get_lockhandle<br />( p_lock_name in varchar2<br />) return varchar2<br />is<br />  l_lock_handle VARCHAR2(128);<br />BEGIN<br />  -- check if lockhandle already exists in table<br />  if g_lock_tbl.exists(p_lock_name)<br />  then<br />    l_lock_handle:= g_lock_tbl(p_lock_name);<br />  else<br />    l_lock_handle:= get_lockhandle_from_dbmslock(p_lock_name);<br />    g_lock_tbl(p_lock_name):= l_lock_handle;<br />  end if;<br />  return l_lock_handle;<br />end get_lockhandle;<br /><br />procedure lock_br_for_record<br />( p_br in varchar2<br />, p_reckey in varchar2<br />) is<br />  l_lockhandle varchar2(128);<br />  l_lock_status NUMBER;<br />begin<br />  l_lockhandle:= get_lockhandle( p_br||p_reckey);<br />  l_lock_status := DBMS_LOCK.REQUEST<br />                   ( lockhandle =&gt; l_lockhandle<br />                   , lockmode =&gt; DBMS_LOCK.X_MODE -- eXclusive<br />                   , timeout =&gt; 0 -- wait forever<br />                   , release_on_commit =&gt; true<br />                   );<br />  CASE l_lock_status<br />  WHEN 0 THEN NULL;<br />  WHEN 2 THEN RAISE_APPLICATION_ERROR(-20000,'deadlock detected');<br />  WHEN 4 THEN RAISE_APPLICATION_ERROR(-20000,'lock already obtained');<br />  ELSE RAISE_APPLICATION_ERROR(-20000,'request lock failed - ' ||l_lock_status);<br />  END CASE;<br />end lock_br_for_record;<br /><br /><br />end br_helper;<br />/<br />&nbsp;

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

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!

No
te:
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 <br />on emp <br />with rowid(deptno, job) <br />including  new values;<br />&nbsp;

2. Create the Materialized View for the Business Rule

create materialized view emp_br001_mv<br />refresh fast on commit<br />as<br />select deptno<br />,      count(*) cnt<br />from   emp<br />where  job = 'CLERK' <br />group <br />by     deptno<br />/<br />&nbsp;

3. Add the Check Constraint to enforce the rule

alter table emp_br001_mv<br />add constraint check_cnt<br />check ( cnt &lt; 4)<br />deferrable;<br />&nbsp;

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&gt; select ename<br />  2  ,      deptno<br />  3  from   emp<br />  4  where  job = 'CLERK<br />  5  order<br />  6  by     deptno<br />  7  /<br /><br />ENAME          DEPTNO<br />---------- ----------<br />MILLER             10<br />SMITH              20<br />ADAMS              20<br />JAMES              30 <br />

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

Share.

About Author

Lucas Jellema, active in IT (and with Oracle) since 1994. Oracle ACE Director for Fusion Middleware. Consultant, trainer and instructor on diverse areas including Oracle Database (SQL & PLSQL), Service Oriented Architecture, BPM, ADF, Java in various shapes and forms and many other things. Author of the Oracle Press book: Oracle SOA Suite 11g Handbook. Frequent presenter on conferences such as JavaOne, Oracle OpenWorld, ODTUG Kaleidoscope, Devoxx and OBUG. Presenter for Oracle University Celebrity specials.

9 Comments

  1. Concerning the materialized view, if the view is used solely to implement check constraint you can apply the HAVING count(*) >= 4.  On large data sets you practically don’t consume any disk (still pay the penalty for refresh on underlying table changes but still better than triggers.

  2. Lucas,

    With regards to your offline question of how this business rule would implement in RuleGen, here it is.

    You need to tell RuleGen ‘when’ the rule needs to be checked. This particular rule needs to be checked when CLERKS are inserted or, in case of updates, the number of employees being promoted to CLERK exceeds the number being demoted (within a same department).

    You specify this through queries on the ‘transition effect’ of the currently executed DML. RuleGen supplies views that hold the transition effect.

    For inserts you register this query:
    select distinct deptno
    from v_insert_tx_effect
    where new_job=’CLERK’
    /

    For updates you register this query:
    select deptno
    from (
    select deptno,sum(case new_job
    when ‘CLERK’ then 1
    else 0) promoted
    ,sum(case old_job
    when ‘CLERK’ then 1
    else 0) demoted
    from v_update_tx_effect
    where updated_job=’true’
    and (old_job=’CLERK’ or new_job=’CLERK’)
    group by deptno)
    where promoted-demoted>0
    /

    There is no need to register a query for deletes: they can never violate this rule.

    Next you tell RuleGen, ‘how’ the rule should be checked, in case one of the queries above actually returns rows. This also is done through supplying RuleGen with a query. This query can use the selected values of the queries above (available through local variable with name p_.

    Here is the ‘how’ for this rule.

    select ‘At most three CLERKs allowed (found ‘||
    to_char(num_clerks)||’) in department ‘||p_deptno||’.’
    as msg
    from (select count(*) as num_clerks
    from emp
    where job=’CLERK’ and deptno=p_deptno)
    where num_clerks > 3
    /

    The semantics is that this query selects the actual error-message that should be reported back in case of a violation. If no row gets selected then the rule is considered OK.

    And finally you need to tell RuleGen how code-execution for checking this rule is to be serialized, this is done via dbms-lock in a similar manner as your code above. In this case you register a call to a service procedure of RuleGen that acquires the necessary application lock for this rule: sp_rg_lock_rule(‘X’,’BREMP001′,p_deptno);

    That’s it in a nutshell: for this rule you register the three queries mentioned plus the serialization code, then execute a service procedure to generate the triggers (or modify existing generated triggers) and you are done. The rule is maintained.

    (there are a few more details to it, but the essence is the stuff above).

    Regards,
    Toon

    PS: The issue with serializable isolation level is a known issue in the framework. I’m still trying to find a way to at least *detect* that the current transaction (or session) runs in serializable isolation mode. And if detected generate a generic error-message that rules cannot be maintained in this mode. But I haven’t found a way to do this, nor another way of getting the rule-validation robust in this mode (in an acceptable scalable manner, that is).

  3. Terrific stuff, Lucas – thanks. I was aware of a lot of the information, but not the use of DBMS_LOCK. Besides, this puts it all in one place, well thought out and well organized.

  4. Brian,

    Thanks for your comment. You are making a good point, though in my experience it is rare for applications to use serializable isolation level and do data manipulation. But you are right: in those (perhaps not so frequently occurring) situations you have to be aware that the approach I present is not secure. So either you do not serialize or you do not do DML. If you want to do both, you may have to use the FOR UPDATE OF approach.

    best regards,

    Lucas

    Note: For more observations on Isolation Levels, you may want to check out http://www.oracle.com/technology/oramag/oracle/05-nov/o65asktom.html.

  5. Using DBMS_LOCK is not completely robust when using serializable isolation level because (unlike SELECT…FOR UPDATE) Oracle “forgets” about the lock once the transaction that acquired it commits (or is rolled back).

    For example consider the following sequence of events under two concurrent sessions, A and B.

    1. Session A starts a new transaction *with serializable isolation level*.

    2. Session B starts a new transaction and makes a “valid” change to EMP that fires the trigger and acquires the lock.

    3. Session B commits, and Oracle releases the lock.

    4. Session A makes a change to EMP that fires the trigger. Since A uses serializable isolation level, it can’t “see” the changes made by B. Since B’s lock has been released, it cannot prevent A from making a change that, when combined with the change made by B, violates the business rule.

    There is a more concrete example of this (relating to preventing overlapping ranges) at http://asktom.oracle.com/pls/ask/f?p=4950:61:18110426428579554560::::P61_ID:474121407101#20229209009012.

    Using release_on_commit => FALSE in DBMS_LOCK.REQUEST() will not solve the problem, because the lock could still be “prematurely” released — for example, if the session that started transaction B is closed before transaction A changes EMP.

    So, SELECT…FOR UPDATE is too restrictive (since it prevents all concurrent changes, even if they don’t violate the business rule), and DBMS_LOCK is not completely robust.

  6. Toon,

    You were absolutely right – now you are not anymore. I was still writing the article when I already posted part of it (afraid to lose content while editing) in the assumption that on a Sunday afternoon no one would read the article in the 20 minutes between the partial publication and the final post. However, I underestimated you – of course.

    I have just completed the whole article. I think you will find the fundamental flaw described now – it is not robust! As well as a solution.

    I would love to have your feedback on the complete article too by the way.

    best reagrds,

    Lucas

  7. Toon Koppelaars on

    Oops. Sems I read your post while not all was posted yet. I see you now have the thoughts on concurrency.

    Again great post!

  8. Lucas,

    Good thoughts on integrity constraint implementation: you fail to mention though what it is that is ‘fundamentally flawed’. Unless you mean that checks are executed too many times. Your solution to that issue will not work when the rule typically involves three or more tables: rules do not have a single ‘case’ (as was the department key in your example) any more.

    Also I would expect that you were going to mention that one has to think about locking ‘something’ in order for the triggers firing simultaneously to still enforce the rule correctly.

    Toon