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: Nuno Pinto do Souto <nsouto_at_optusnet.com.au>
Date: Thu, 06 Nov 2003 12:24:25 -0800
Message-ID: <F001.005D5DAC.20031106122425@fatcity.com>

 ('binary' encoding is not supported, stored as-is)

> Jonathan Gennick <jonathan_at_gennick.com> wrote:
> My concurrency issues probably boil down to the locking
> business.

Purrcisely.

> because they would both issue the SELECT before either one
> got around to the UPDATE. I couldn't screw things up

There you go. You are supposed to LOCK the row on the SELECT, then you grab the key and update it as fast as possible.

That takes care of the "same key for 2 users" issue.

Then you gotta solve the scalability probem. Here, two rules:

1- Make the lock period as small as possible. Only grab the key RIGHT before you're ready to commit the new row. NOT at the start of the transaction.
2- Spread the load. Put one row in the lock table for every 10000 key values, make each row into one block. Now, select in round-robin fashion from one of the rows. That spreads the load across rows/blocks in the lock table.

All par for the course in databases where sequences a-la Oracle do not exist. Of course, if you use Oracle and you still do all this, you're asking for trouble...

And I strongly disagree that a PK ever has to be consecutive (with no gaps). That is NOT a PK. That may be a unique key or some other business requirement, but it is most definitely not a PK.

Cheers
Nuno Souto
nsouto_at_wizofoz2k.com.au

-- 
Please see the official ORACLE-L FAQ: http://www.orafaq.net
-- 
Author: Nuno Pinto do Souto
  INET: nsouto_at_optusnet.com.au

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 - 14:24:25 CST

Original text of this message

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