Restrict the Number of Records[akadia]

While developping database application we often have to restrict the number of records an end user can insert into a table. A simple solution is the following code fragment in the BEFORE-INSERT-FOR-EACH-ROW Trigger:

select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(-20101,
'Maximum number of records exceeded');
end if;

To reconstruct or understand the following descriptions please download the example code ex_restr_nbrof_records.sql or consult the source code, we assume, that the maximum number of detailrecords must be limited to five.

We try to insert a 6Th record:

insert into detail values (6,'M1');
*
ERROR at line 1:
ORA-20101: Maximum number of recors reached
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'

So far so good. We’ll now demonstrate what happens, when we insert records from two concurrent transactions. Delete one record in order to be able to insert one record hereafter:

delete from detail where id=5;
1 row deleted.
commit;

Invoke the SQL-Plus tool and insert one record

insert into detail values (5,'M1');1 row created.

DO NOT COMMIT !

Invoke a second session by starting a SQL-Plus again and run the statement

insert into detail values (6,'M1');
1 row created.
commit;
Commit complete.

Change to the other session and issue a commit

commit;Commit complete.

select count(*) from detail;
COUNT(*)
----------
6

We now have 6 records. The maximum number allowed was 5 !

How to avoid this situation ?

Oracle does not support the "dirty read“ isolation level which is defined with:
"A transaction reads data written by concurrent uncommitted transaction“

A solution to solve this problem is to lock the corresponding master record:
Change the ON INSERT Trigger:

create or replace trigger bi_detail
before insert
on detail
for each row
declare
l_cnt number;
l_dummy master.id%type;
begin -- lock the master record to avoit too many record
-- by using concurrent sessions.
select id into l_dummy from master
where id = :new.m_id for update nowait;
select count(*) into l_cnt from detail;
if l_cnt >= 5 then
raise_application_error(
-20101,'Maximum number of recors reached');
end if;
end;

Delete the 2 records to test the solution:

delete from detail where id=5;
delete from detail where id=6;
commit;

There should be 4 records now:

select count(*) from detail;
COUNT(*)
----------
4

Now we try again with the two concurrent sessions:

insert into detail values (5,'M1');
1 row created.

Change to the second session and issue:

insert into detail values (6,'M1');ERROR at line 1:
ORA-00054: resource busy and acquire with NOWAIT specified
ORA-06512: at "SCOTT.BI_DETAIL", line 6
ORA-04088: error during execution of trigger
'SCOTT.BI_DETAIL'

Go back to the first session and run:

commit;Commit complete.

Remarks

If an exact maximum number of records is specified for a table, using a lock is the only solution to avoid the above situation demonstrated. Often we want to limit the number of deteilrecords in a way, that an end user can’t claim a huge amount of disk space by inserting unbound number of records. In this case, the simple solution shown at the begin will do it.

If the table doesn’t use a foreign key constraint, you can implement an auxilliary table, insert one row and lock this row instead of the masterrecord.

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/936/viewspace-60607/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/936/viewspace-60607/