Re: Is this safe???

A copy of this was sent to Chris Forlano <cforlano_at_nortelnetworks.com> (if that email address didn't require changing) On Sun, 26 Sep 1999 23:00:20 +0100, you wrote:

>Does the following procedure guarantee atomicity for the id_generator
>table?
>What I mean to say is, how can I guarantee that no one else will be able
>to
>update the id_generator table at the same time this function is running?
>

no one will be able to update it concurrently -- HOWEVER since you select first and then update -- you will generate many many duplicate IDs out of this routine.

change it to this in Oracle:

create sequence my_seq;

create or replace function generate_id( p_type_in varchar2 ) return varchar2 as

   l_id number;
begin

   select my_seq.nextval into l_id from dual;

   return p_type_in || to_char( l_id, 'fm000000009' ); end;
/

Use a sequence, don't use a table.

If you must, absolutely must, use the table (bad idea), put the UPDATE first and then read the value out to SERIALIZE (eg: have no concurrency) on this operation.    

>If not, how can I guarantee it?
>Can I do this as a function?
>
>create or replace
>PROCEDURE generate_id( type_in in varchar2, id_out out varchar2 )
>AS
> CURSOR id_cursor IS
> SELECT prefix, next_id FROM id_generator WHERE type = type_in;
> prefix varchar2(20);
> next_id number(9);
>BEGIN
>
> OPEN id_cursor;
>
> /* Get the next available id. */
> FETCH id_cursor INTO prefix, next_id;
>
> /* Update to the next available id. */
> UPDATE id_generator SET next_id = next_id + 1 WHERE type = type_in;
>
> /* Create an id containing a prefix and id (ex, TST000123). */
> id_out := CONCAT( prefix, LPAD( TO_CHAR( next_id ), 9, '0' ) );
>
> CLOSE id_cursor;
>
>END generate_id;
>
>I'm running Oracle 8.0.5 on Solaris 2.6.
>
>Thanks,
>
>Chris

--
See http://govt.us.oracle.com/~tkyte/ for my columns 'Digging-in to Oracle8i'... Current article is "Part I of V, Autonomous Transactions" updated June 21'st  

Thomas Kyte                   tkyte_at_us.oracle.com
Oracle Service Industries     Reston, VA   USA

Opinions are mine and do not necessarily reflect those of Oracle Corporation Received on Sun Sep 26 1999 - 20:34:41 CDT