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:
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:
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 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:
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:
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.
Toon:
This thread on the OTN forums…
http://forums.oracle.com/forums/thread.jspa?messageID=1455919�
…discusses a way to identify serializable transactions using the FLAG column in V$TRANSACTION. You may already be aware of this method and have chosen not to use it because it is not fully documented. Still, I thought it might interest you in case you were not aware of it.
Hope this helps,
Brian
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).
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.
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.
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.
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
Oops. Sems I read your post while not all was posted yet. I see you now have the thoughts on concurrency.
Again great post!
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