Re[2]: How do you genrate primary keys?

Yes, now I understand your concurrency issue. There would have been better ways but it was wiser not to spend time trying to "improve" going down the wrong path. Good that you convinced the managers there to go for Sequences early.

Regards
Hemant
At 05:59 AM 06-11-03 -0800, you wrote:
>Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale
>(hkchital_at_singnet.com.sg) wrote:
>HKC> 1. Hit a table that keeps a counter.
>HKC> Used to be a mechanism in the Oracle5 days [If I remember correctly,
>HKC> Sequences came in Oracle6]. Issues were with locking the single
>HKC> record used as the generator or scanning for the max(value) of the
>HKC> key.
>HKC> Not quite sure I understand how you encountered concurrency issues,
>though.
>
>My concurrency issues probably boil down to the locking
>business. The app I'm thinking of originally did something
>like:
>
>SELECT counter INTO :1
>FROM counter_table
>WHERE counter_name = 'table name';
>
>...some app code goes here...
>
>UPDATE counter_table
>SET counter := counter+1
>WHERE counter_name = 'table name';
>
>Well, it all worked fine in single-user mode<grin>. But it
>was easy enough for me to sit down in front of two
>computers, create two new records, press SAVE at the same
>time, and cause two sessions to grab the same key value,
>because they would both issue the SELECT before either one
>got around to the UPDATE. I couldn't screw things up
>consistently, but just by hitting the SAVE button at the
>same time I could screw things up often enough to make the
>problem obvious.
>
>Maybe there's a way to lock the table, to make the above
>approach work. In my case, I didn't bother trying to find
>that solution. Once I did my little demo, it was easy enough
>to convince the project manager that we should switch to
>using Oracle sequences.
>
>Best regards,
>
>Jonathan Gennick --- Brighten the corner where you are
>http://Gennick.com * 906.387.1698 * mailto:jonathan@gennick.com
>
>Join the Oracle-article list and receive one
>article on Oracle technologies per month by
>email. To join, visit
>http://four.pairlist.net/mailman/listinfo/oracle-article,
>or send email to Oracle-article-request_at_gennick.com and
>include the word "subscribe" in either the subject or body.
>
>--
>Please see the official ORACLE-L FAQ: http://www.orafaq.net
>--
>Author: Jonathan Gennick
> INET: jonathan_at_gennick.com
>
>Fat City Network Services -- 858-538-5051 http://www.fatcity.com
>San Diego, California -- Mailing list and web hosting services
>---------------------------------------------------------------------
>To REMOVE yourself from this mailing list, send an E-Mail message
>to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
>the message BODY, include a line containing: UNSUB ORACLE-L
>(or the name of mailing list you want to be removed from). You may
>also send the HELP command for other information (like subscribing).

Hemant K Chitale
Oracle 9i Database Administrator Certified Professional My personal web site is : http://hkchital.tripod.com

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Hemant K Chitale
  INET: hkchital_at_singnet.com.sg

Fat City Network Services    -- 858-538-5051 http://www.fatcity.com
San Diego, California        -- Mailing list and web hosting services
---------------------------------------------------------------------
To REMOVE yourself from this mailing list, send an E-Mail message
to: ListGuru_at_fatcity.com (note EXACT spelling of 'ListGuru') and in
the message BODY, include a line containing: UNSUB ORACLE-L
(or the name of mailing list you want to be removed from).  You may
also send the HELP command for other information (like subscribing).