Re: Is this safe???

From: Jeff Hunter <jeffh_at_btitelecom.net>
Date: Mon, 27 Sep 1999 08:41:00 -0400
Message-ID: <37ef6615@defiant.btitelecom.net>

I see what you are trying to do here, and I don't think you can do it with a sequence. It looks like you are trying to generate a unique identifier based on a "Type"; you would have multiple type's that generate id numbers from 1..N.

For example,
Type NextID

A           3232
B           7161
C           9191

etc...

Your procedure will work, but won't guarantee uniqueness. Depending on how many TYPE's you have, you will have to explicitly lock the table in exclusive mode (LOCK TABLE command) BEFORE you update and select (I like Thomas' suggestion of updating before you select).

However, if you have a lot of users hitting this table, you will run into lock contention. It will be best to cache this table into the SGA for faster access (depending, of course on resources)...

Chris Forlano wrote in message <37EE9774.AF71ECD8_at_nortelnetworks.com>...
>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?
>
>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
>
>--
>Chris Forlano
>Automation Development
>Nortel Networks, Maidenhead
>590 4342 (01628 434 342)
>cforlano_at_nortelnetworks.com
>
>
Received on Mon Sep 27 1999 - 07:41:00 CDT