Board index » delphi » Best practice on how to generate primary keys in a multi user system

Best practice on how to generate primary keys in a multi user system

What is a good practice for generating unique primary keys in a multiuser
system? I have seen that some developers just query the table in question to
get the highest number of the column that is the PK while others have a
separate table where they keep the latest, highest, primary key. I guess it
depends on other things to what is the best solution. Maybe there is some
information on this somewhere? I would like to know how others do.

Lasse

 

Re:Best practice on how to generate primary keys in a multi user system


Quote
On Wed, 10 Jul 2002 16:18:50 +0200, "Lasse" <l...@kpwood.com> wrote:
>What is a good practice for generating unique primary keys in a multiuser
>system? I have seen that some developers just query the table in question to
>get the highest number of the column that is the PK while others have a
>separate table where they keep the latest, highest, primary key. I guess it
>depends on other things to what is the best solution. Maybe there is some
>information on this somewhere? I would like to know how others do.

Hi Lasse,

If you don't mind having a large primary key field, you can use GUID's
which are guarenteed to be unique in this sector of the solar system.

--
Carl

Re:Best practice on how to generate primary keys in a multi user system


"Lasse" <l...@kpwood.com> a crit dans le message news: 3d2c42b0_1@dnews...

Quote
> What is a good practice for generating unique primary keys in a multiuser
> system? I have seen that some developers just query the table in question
to
> get the highest number of the column that is the PK while others have a
> separate table where they keep the latest, highest, primary key. I guess
it
> depends on other things to what is the best solution. Maybe there is some
> information on this somewhere? I would like to know how others do.

What I usually do is to start a transaction, insert the record, get the next
number, update the record with the key and commit the transaction.

This way if the insert fails, you haven't got the next number. The theory is
the update is less likely to fail?? <g>

--
Joanna Carter
Consultant Software Engineer
TeamBUG support for UK-BUG
TeamMM support for ModelMaker

Re:Best practice on how to generate primary keys in a multi user system


Quote
"Lasse" <l...@kpwood.com> wrote in message news:3d2c42b0_1@dnews...
> What is a good practice for generating unique primary keys in a multiuser
> system? I have seen that some developers just query the table in question
to
> get the highest number of the column that is the PK while others have a
> separate table where they keep the latest, highest, primary key. I guess
it
> depends on other things to what is the best solution. Maybe there is some
> information on this somewhere? I would like to know how others do.

If at all possible, use the feature provided by the database for this.
Interbase uses Generators, MSSQL use Identity, Oracle uses Sequences, etc.
These features are specifically for this purpose - to guarantee a unique
key.

In a mulit-user system, you should *never* use the method of incrementing
the currently highest key value in a data table. The method of using a
separate table, locking and incrementing, can work but requires pessimistic
locking, checking from the application for lock conditions, and is slower.

--
Wayne Niddery (Logic Fundamentals, Inc.)
RADBooks: http://www.logicfundamentals.com/RADBooks/delphibooks.html
Those who disdain wealth as a worthy goal for an individual or a society
seem not to realize that wealth is the only thing that can prevent
poverty. - Thomas Sowell

Re:Best practice on how to generate primary keys in a multi user system


We use a dedicated table for generating such keys. Granted some keys are
wasted (in case of an error) but in our applications we do not need
sequential keys and a 32 bit integer field can create a lot more keys then
we need so a few wasted ones do no matter.

Oracle has sequences which can be handy.

Regards, Frederick C. Wilt

Re:Best practice on how to generate primary keys in a multi user system


Hi Lasse,

Scott Ambler's paper on "Mapping Objects To Relational Databases" have a
chapter on this matter.

Go to http://www.ambysoft.com/mappingObjects.html and download the pdf.

Hope it helps.

--
Jerome

"Lasse" <l...@kpwood.com> a crit dans le message de news:
3d2c42b0_1@dnews...

Quote
> What is a good practice for generating unique primary keys in a multiuser
> system? I have seen that some developers just query the table in question
to
> get the highest number of the column that is the PK while others have a
> separate table where they keep the latest, highest, primary key. I guess
it
> depends on other things to what is the best solution. Maybe there is some
> information on this somewhere? I would like to know how others do.

> Lasse

Re:Best practice on how to generate primary keys in a multi user system


Quote
> What I usually do is to start a transaction, insert the record, get the
next
> number, update the record with the key and commit the transaction.

> This way if the insert fails, you haven't got the next number. The theory
is
> the update is less likely to fail?? <g>

What?!? <G>

Are you saying something like this:

try
    Start Transaction;
    intPKId := obj.InsertRecordGetPKId(value1, value2);
    obj.Update(intPKId, value1, value 2);
    Commit Trans;
except
    Abort Trans;

Regards

Abdullah

Re:Best practice on how to generate primary keys in a multi user system


In article <3d2c63cd$1_1@dnews>, Wayne Niddery [TeamB] says...

Hi,

Quote
> In a mulit-user system, you should *never* use the method of incrementing
> the currently highest key value in a data table.

Totally agree, I have seen so many people being stung by this one.

J

Re:Best practice on how to generate primary keys in a multi user system


Can you not use generators, sequences, identity (InterBase, Oracle, MS
SQL)?
--
Shiv Kumar
The Delphi Apostle
http://www.matlus.com
http://www.delphisoap.com

Re:Best practice on how to generate primary keys in a multi user system


Quote
"Jerome Bouvattier" <jerome.bouvatt...@no.thanks.fr> wrote in message

news:3d2c671b$1_1@dnews...

Quote
> Scott Ambler's paper on "Mapping Objects To Relational Databases" have
a
> chapter on this matter.

> Go to http://www.ambysoft.com/mappingObjects.html and download the
pdf.

> Hope it helps.

Specifically look at Scott's write up of the "high-low pattern", which
is a robust and proven scalable solution for this.

An anecdote:

I once was working on a data import app which needed to import a few
hundred thousand records to Interbase.  It was too slow.  I profiled it.
It spent a remarkable amount of time getting the next value of an IB
generator for each record.  It spend more time getting the ID from the
generator then inserting the record.

I changed the code to increment the generator by a big chunk, then use
the whole range of IDs ("Inc(NextId);").  This one change made the
import application very significantly faster.  Using a database
generator / sequence / etc. in the obvious way is simple and
straightforward, but can be naive and non-performant.

--
[ Kyle Cordes * k...@kylecordes.com * http://kylecordes.com ]
[ Consulting, Training, and Software development tips and   ]
[ techniques: Java, Delphi, ASTA, BDE Alternatives Guide,   ]
[ JB Open Tools, EJB, Web applications, methodologies, etc. ]

Re:Best practice on how to generate primary keys in a multi user system


The problem with GUIDs is that not all DBMS support them unless you store
them as strings (which brings you quite large indexes).

I use a combination of IDH and IDL for Object ID. The first time application
saves new objects to DB it reads new IDH from DB. For further additions
application  just increments IDL by one. The key can never be duplicated
since every running instance of the application has uniue IDH.

Branko

Quote
"Carl Caulkett" <car...@dircon.co.uk> wrote in message

news:2bioiu4aume0qgok6kgdpov7us53o3lbeu@4ax.com...
Quote
> On Wed, 10 Jul 2002 16:18:50 +0200, "Lasse" <l...@kpwood.com> wrote:

> >What is a good practice for generating unique primary keys in a multiuser
> >system? I have seen that some developers just query the table in question
to
> >get the highest number of the column that is the PK while others have a
> >separate table where they keep the latest, highest, primary key. I guess
it
> >depends on other things to what is the best solution. Maybe there is some
> >information on this somewhere? I would like to know how others do.

> Hi Lasse,

> If you don't mind having a large primary key field, you can use GUID's
> which are guarenteed to be unique in this sector of the solar system.

> --
> Carl

Re:Best practice on how to generate primary keys in a multi user system


Quote
> I use a combination of IDH and IDL for Object ID.

This assumes you always have a database connection available :-) For
disconnected work, then GUIDs might be necessary.

Cheers,
Jim Cooper

____________________________________________

Jim Cooper      jcoo...@tabdee.ltd.uk
Tabdee Ltd      http://www.tabdee.ltd.uk

TurboSync - Connecting Delphi with your Palm
____________________________________________

Re:Best practice on how to generate primary keys in a multi user system


Quote
> This assumes you always have a database connection available :-) For

Not necessarily. You can allways save last used idh, idl combination to ini
file and reuse them next time your app has to work in disconnected mode.
Only the first time you start your app you would need network connection or
supply idh value in some other way.

Re:Best practice on how to generate primary keys in a multi user system


On Wed, 10 Jul 2002 12:40:04 -0400, "Wayne Niddery [TeamB]"

Quote
<wnidd...@chaffaci.on.ca.com> wrote:
>If at all possible, use the feature provided by the database for this.
>Interbase uses Generators, MSSQL use Identity, Oracle uses Sequences, etc.
>These features are specifically for this purpose - to guarantee a unique
>key.

Seconded ! Let the DB handle all the nitty-gritty details of hashing
out a unique key - heck, if the feature is there, use it !

Marc

------------------------------------------------------------------------
Marc Scheuner                                          Software Engineer
Quest Software Canada                       Halifax, Nova Scotia, Canada
Email: marc.scheu...@quest.com                      http://www.quest.com

Re:Best practice on how to generate primary keys in a multi user system


"Kyle Cordes" <k...@kylecordes.com> wrote

Quote

> Specifically look at Scott's write up of the "high-low pattern", which
> is a robust and proven scalable solution for this.

I don't think you'd get much argument on this being one of our elusive 'best
practices,' but I've sometimes pondered the implementation decisions. For
example--you using this with

-- a composite index on the guarded table (index = hiInt field and lowInt
field)

-- a datatype difference (local hiInt32/lowInt32 --> db index = bigInt (64
bit field)

-- a datatype difference (local hiSmallInt/lowSmallInt --> db index = Int
(32 bit field)

or some other method?

Anyone implemented this with an asymmetrical hi/low split? (for example, 64
bit (bigInt) keyfield, using 48 bits for the high int and 16 for the low).

Regards,
bobD

Go to page: [1] [2]

Other Threads