Oracle FAQ Your Portal to the Oracle Knowledge Grid
HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US
 

Home -> Community -> Mailing Lists -> Oracle-L -> RE: Re[2]: How do you genrate primary keys?

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

From: Cary Millsap <cary.millsap_at_hotsos.com>
Date: Thu, 06 Nov 2003 09:54:38 -0800
Message-ID: <F001.005D5D92.20031106095438@fatcity.com>


The implementations I've seen all did SELECT...FOR UPDATE. Works.
Doesn't scale.

Cary Millsap
Hotsos Enterprises, Ltd.
http://www.hotsos.com

Upcoming events:
- Performance Diagnosis 101: 11/19 Sydney

-----Original Message-----
Jonathan Gennick
Sent: Thursday, November 06, 2003 7:59 AM To: Multiple recipients of list ORACLE-L

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).

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Cary Millsap
  INET: cary.millsap_at_hotsos.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).
Received on Thu Nov 06 2003 - 11:54:38 CST

Original text of this message

HOME | ASK QUESTION | ADD INFO | SEARCH | E-MAIL US