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_helper
is

type rowid_stack is table of rowid;

procedure add_rowid( p_rowid rowid);

function get_rowid_stack
return 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_aiur
after insert or update of job, deptno
on emp
for each row
when (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_bius
before insert or update
on emp
begin
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 update
on emp
declare
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:

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

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_helper
is

g_rowid_stack rowid_stack:= rowid_stack();

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

function get_rowid_stack
return rowid_stack
is
begin
return g_rowid_stack;
end get_rowid_stack;

procedure clear_rowid_stack
is
begin
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_helper
is

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_helper
is

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
) is
begin
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_stack
is
begin
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
) is
begin
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_aiur
after insert or update of job, deptno
on emp
for each row
when (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_bius
before insert or update
on emp
begin
br_helper.clear_rowid_stack('BR_EMP001');
end;
/

create or replace trigger br_emp001_aius
after insert or update
on emp
declare
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_helper
is

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_helper
is

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
) is
begin
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_stack
is
begin
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
) is
begin
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_aiur
after insert or update of job, deptno
on emp
for each row
when (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 update
on emp
declare
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:

On the false sense of security with PL/SQL based implementation of business rules - and what to do about it brlock2
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: 

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

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 update
on emp
declare
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 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:

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

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 emp
set deptno = 10
where job = 'CLERK'
and deptno = 30
/
 

Session 2: 

update dept
set 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_helper
is

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_helper
is

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
) is
begin
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_stack
is
begin
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
) is
begin
g_reckey_stack_tbl.delete(p_br);
end clear_reckey_stack;

function get_lockhandle_from_dbmslock
( p_lock_name in varchar2
) return varchar2
is
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 varchar2
is
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 update
on emp
declare
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!

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 
on emp
with rowid(deptno, job)
including new values;
 

2. Create the Materialized View for the Business Rule

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

3. Add the Check Constraint to enforce the rule

alter table emp_br001_mv
add constraint check_cnt
check ( 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 10
SMITH 20
ADAMS 20
JAMES 30

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

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

9 Comments

  1. Dan D February 12, 2010
  2. Brian Camire October 24, 2006
  3. Toon Koppelaars October 23, 2006
  4. John Flack October 23, 2006
  5. Lucas Jellema October 23, 2006
  6. Brian Camire October 23, 2006
  7. Lucas Jellema October 22, 2006
  8. Toon Koppelaars October 22, 2006
  9. Toon Koppelaars October 22, 2006