Stored Procedures - Getting a Unique Key
Well, after all my b*tching, I think I may have a solution to the
problem of unique key definition within stored procedures.
My original complaint was - if stored procedures are oblivious to
the SQLCA record, two processes could very easily get into a race condition
where each of them is trying to use max(key-value) + 1 as a new key value.
The suggestion had been made to make lavish use of locks, but I see that as
greatly impacting concurrency, particularly in light of the "twin" scenario.
So, I'd like to submit the following two stored procedures for the Net's
review. Essentially, my first SP gets the max(key_nr) of the column. My
second procedure tries to insert with key_nr+1. If I succeed, I know the
value of the candidate key. If I hit a duplicate key-nr, I recursively call the
second procedure until I do get a clean key.
*Please* post your comments. Thanks.
The table I'm using for illustration is the following:
create table "bossert".foo
(
foo_nr serial not null,
foo_date date not null
);
create unique index "bossert".u1_foo on "bossert".foo (foo_nr);
----------------------- stored procedures ---------------------
create procedure IO_ins_foo
(
_000 integer,
_001 date
)
returning
integer;
select
max(foo_nr) + 1
into
_000
from
foo;
call IO_ins_foo2( _000, _001 )
returning _000;
return _000;
end procedure; -- IO_ins_foo()
create procedure IO_ins_foo2
(
_000 integer,
_001 date
)
returning
integer;
define err_num integer;
on exception in ( -239 ) set err_num
let _000 = _000 + 1;
end exception with resume
let err_num = 0;
insert into
foo
values
(
_000,
_001
);
if ( err_num = -239 )
then
call io_ins_foo2( _000, _001 ) returning _000;
end if
return _000;
end procedure; -- IO_ins_foo2()
--
In-Real-Life: John Bossert, Thalatta Corporation, (+1 206.455.9838)
Domain: bossert@thalatta.com Path: ...!sun!sunup!thebes!bossert