On this page we examine the source code used to implement the T2_API package demonstrated in Precreated Serial Numbers.
IMPORTANT NOTE: While I have not personally used or seen this approach in the systems I have worked with, the following solution is how I expect it could be implemented. I have only run it on a single user test system so please treat it as a first draft. I have done minimal testing on this code and only included basic exception handling to make the solution easier to understand. If you notice any issues or bugs please drop me a line at the email address listed in the About: Feedback page.
Here is the package.
create package t2_api as
function new_item( p_name t2.name%type )
return t2.key%type -- returns the primary key of the new row
;
end;
/
create package body t2_api as
procedure create_t2_batch is -- see Note 1
pragma autonomous_transaction;
v_create_t2_batch_lock_id constant integer := 123 ; -- See Note 2
v_batch_size constant binary_integer := 5 ; -- See Note 3
v_max_create_batch_run_time constant number := 10 ; -- See Note 4
v_dbms_lock_request_status integer ;
v_max_serial_number t2.serial_number%type ;
begin
------------------------------------------------------------
-- ensure that only one instance of CREATE_T2_BATCH
-- runs at any given time (See Note 5)
------------------------------------------------------------
v_dbms_lock_request_status :=
dbms_lock.request
( id => v_create_t2_batch_lock_id ,
timeout => v_max_create_batch_run_time ,
release_on_commit => true
) ;
case v_dbms_lock_request_status
when 0 then null ;
when 1 then -- timeout detected
raise_application_error
( -20000, 'Another CREATE_T2_BATCH process appears to be hung.' ) ;
else
raise_application_error
( -20000,
'DBMS_LOCK reported error status ' || v_dbms_lock_request_status
) ;
end case ;
------------------------------------------------------------
-- create new rows with non-null serial numbers
------------------------------------------------------------
select nvl( max( serial_number ), 0 )
into v_max_serial_number
from t2 ;
insert into t2 ( key, name, status, serial_number )
select
ts.nextval as key ,
'I' || ts.currval as name ,
'Available' as status ,
v_max_serial_number + level as serial_number
from
dual
connect by
level <= v_batch_size -- see Note 6
;
commit;
end;
function new_item( p_name in t2.name%type ) return t2.key%type is
v_row_id urowid ;
v_key t2.key%type ;
v_max_attempts constant binary_integer := 3 ;
e_resource_busy exception ;
pragma exception_init ( e_resource_busy, -54 );
begin
for v_attempt_number in 1 .. v_max_attempts loop
------------------------------------------------------------
-- find the first available row, lock it, update it, return
------------------------------------------------------------
for v_row in -- (see Gotcha #1)
( select ROWID as row_id, key
from t2
where status = 'Available'
order by key
)
loop begin
select key
into v_key
from t2
where rowid = v_row.row_id
for update nowait ;
update t2 set status = 'Assigned', name = p_name
where rowid = v_row.row_id ;
return( v_row.key );
exception
when e_resource_busy then null; -- try the next 'Available' row
end; end loop ; -- v_row loop
------------------------------------------------------------
-- at this point there are no 'Available', unlocked rows
-- left in T; create a new batch
------------------------------------------------------------
create_t2_batch ;
------------------------------------------------------------
-- there should be some 'Available' rows now,
-- loop back and try again
------------------------------------------------------------
end loop; -- v_attempt_number loop
raise_application_error( -20000, 'Too many unsuccessful attempts.' );
end;
end;
/
Note 1: This method only works when CREATE_T2_BATCH is the only module in the application allowed to insert non-null T2.SERIAL_NUMBER values. No modules should be allowed to update T2.SERIAL_NUMBER.
Note 2: Use a number that is unique among all modules in your database that use DBMS_LOCK.
Note 3: Set this to the number of rows you want to create in each batch. We use "5" here for demonstration purposes. Typically this number would be higher. The best value will be a compromise between frequency and duration. A number that is too small will block NEW_ROW callers frequently for short periods of time. A number that is too large will block NEW_ROW callers infrequently for long periods of time.
Note 4: Set this to the largest number of seconds you expect CREATE_T2_BATCH will ever take to run. If CREATE_T2_BATCH hangs for some reason, the next transaction that tries to call it will fail with a timeout error.
Note 5: To serialize access to CREATE_T2_BATCH we use a User Lock here to implement what is sometimes called a MUTEX, a mechanism for controlling mutually exclusive access to a shared resource.
Note 6: See
Integer Series Generators: CONNECT BY LEVEL Method
for an explanation of the
connect by level <= v_batch_size
clause.
Gotchas
1. Some people may be tempted to replace this loop with a single SELECT statement that uses the undocumented "FOR UPDATE NOWAIT SKIP LOCKED" syntax. I do not recommend this practise for the simple fact that undocumented features may not always work as expected (of course, the documented ones don't always either, but that's another story ;-) ).
2. If you insert item A at a given time and then insert item B some time later do not expect A's serial number to always be lower than B's. In certain situtations item A's serial number may be higher than B's. Here is an example.
- User 1 creates item X with serial number 123
- User 2 creates item A with serial number 124
- User 2 commits item A
- User 1 rolls back creation of item X
- User 3 creates item B with serial number 123
- User 2 commits item B