Incrementing a PK safely

I'm using a inline call in my DCT to set an incremental PK ID (by checking the latest value in the DB and adding 1) for each new DCR that is to be submitted to the DB
However, I have just realised that this is not 'thread' safe.
If two authors/editors are working on DCR's simultaneously they will have both got the same ID from the inline call to the DB. eeek !

I cannot simply use a self-incrementing 'identity' (SQL server term) on the DB as I use this ID value as a foriegn key in other tables that are updated as a 'non root group' from the same DCR.
Is there no standard, safe way, of doing this ?

Many thanks again in advance

/nick

Find more posts tagged with

Comments

Migrateduser

Kinda depends on the data type of your PK column, but one reasonably safe and simple method would be to use the current timestamp, Perl localtime() function, as a scalar, which returns a long value that identifies the current time in seconds as a long. This gets you a unique string for each second. If you then concatenate a random n-digit number (using rand()), you can be reasonably sure it's unique. Increase n to make youself more confident in your PK's odds on being unique.

Add an error test loop around your code that tries to do the SQL INSERT, and if it fails, have it automatically regen a new PK before retrying. That will take care of the astronomically low chance of a collision.

bw

Bob Walden [bob.walden@interwoven.com]
Interwoven Education Group
IM: Yahoo, MSN bob_walden

Migrateduser

Thanks Bob !
Just though of that myself too :-)

Adam Stoller

A minor correction ...

use the current timestamp, Perl localtime() function, as a scalar, which returns a long value that identifies the current time in seconds as a long

localtime() if used in scalar context will print out a nicely formatted date / time string in ctime format:

my $y = localtime();print "$y\n";
Wed Nov  6 21:27:39 2002

localtime() in non-scalar context returns an array of elements representing the various parts of date / time information:

my @y = localtime(); print "@y\n";
15 28 21 6 10 102 3 309 0

If you change the above to look as follows, you get this result:

my @y = localtime(); print @y, "\n";
15282161010233090

This is not the time in seconds, it is simply a contactenation of the elements of the array. If you want the time in seconds, use the time() function:

my $y = time(); print "$y\n";
1036636095

Sorry Bob, couldn't let that one pass ;-)

--fish
(Interwoven Senior Technical Consultant)

Migrateduser

Oops--thanks Fish. Thank **** you're there. :-)

bw

Bob Walden [bob.walden@interwoven.com]
Interwoven Education Group
IM: Yahoo, MSN bob_walden

Migrateduser

Thanks both !

The time in seconds solution is working very nicely thankyou.

Capture.PNG