Re: Stored Procedures - Getting a Unique Key
In <1993Apr29.230901.27127@Thalatta.COM> bossert@thalatta.com (John Bossert) writes:
>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.
Why not loop instead of recursing? I would expect it to be more
efficient:
----------------------- stored procedure ---------------------
create procedure IO_ins_foo
(
_000 integer,
_001 date
)
returning
integer;
define err_num integer;
select
max(foo_nr) + 1
into
_000
from
foo;
let err_num = 1;
while err_num <> 0
on exception in ( -239 ) set err_num
let _000 = _000 + 1;
end exception
insert into
foo
values
(
_000,
_001
);
let err_num = 0;
end while;
return _000;
end procedure; -- IO_ins_foo()
graeme
--
Disclaimer: The author's opinions are his own, and not necessarily
those of Pyramid Technology Ltd. or Pyramid Technology Inc.
---------------------------------------------------------------------------
-m------- Graeme Sargent Voice: +44 (0)252 373035
---mmm----- Senior Database Consultant Fax : +44 (0)252 373135
-----mmmmm--- Pyramid Technology Ltd. Telex: Tell who???
-------mmmmmmm- Farnborough, Hants GU14 7PL Email: graeme@pyra.co.uk
---------------------------------------------------------------------------
We have the technology. The tricky bit is learning how to use it.