Oracle FAQ Your Portal to the Oracle Knowledge Grid

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

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

From: Jonathan Gennick <>
Date: Thu, 06 Nov 2003 05:59:25 -0800
Message-ID: <>

Wednesday, November 5, 2003, 9:24:41 AM, Hemant K Chitale ( 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 * 906.387.1698 *

Join the Oracle-article list and receive one article on Oracle technologies per month by email. To join, visit, or send email to and include the word "subscribe" in either the subject or body.


Please see the official ORACLE-L FAQ:

Author: Jonathan Gennick

Fat City Network Services    -- 858-538-5051
San Diego, California        -- Mailing list and web hosting services
To REMOVE yourself from this mailing list, send an E-Mail message to: (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 - 07:59:25 CST

Original text of this message