> Process 1 generates 11
> Process 2 generates 12
> Process 3 generates 13
> Process 1 issues a rollback - ID 11 is now unused
> Process 4 generates 11 - based on rolledback seed value.
> Process 5 generates 12 !! Duplicate
> Process 6 generates 13 !! Duplicate
>
> Or am I missing something here? I can't see how to hold a value that is
the
> basis for the next value (sequential or random) that isn't reset by a
> rollback.
I think your problem is better than that, but there is a fix (aside from
using interbase generators or Oracle sequences :) ). I think it would go
like:
proc 1 begin trans
proc 1 gets 11
proc 2 begin trans
proc 2 gets 11, 'cos proc 1 hasn't committed yet, and the ACID properties
require that the data changes are invisible until you comit.... unless you
have dirty reads on :(
etc
you could make a table with just an identity value, insert into that table
and get @@IDENTITY back (not very safe tho - there is a nice bug in that
soup - even in SQL2000)
Other way could be to have a table with a timestamp (which is NOT at all
related to the time, BTW) field and an identifier - insert into it with an
identifier thats unique to your table and key (eg, PERSON_0000345) and then
get the timestamp back and use that - its either a 32 or 64bit number, tho I
have a niggle that its based on the amount of time since either the server
or the DB process was started....
ouch. where's the MSSQL Guru's when you need them (NEVEN!!!!!)
> The other option of holding a table of all used values works well and is
> safe, but we end up with a table of X million rows with an associated
index
> that does nothing useful. If this was the selected option I suspect the
> best functionality would be to create a random number and then check for
> existance. This at least would avoid the index problems that exist with
> sequential keys in some environments.
do you have the PK already for this table? Why not hash (as in, MD5 or
something) the PK and the name of the table, down to a 32 bit number......?
dunno if it would work, but might be a start...
Nic.
---------------------------------------------------------------------------
New Zealand Delphi Users group - Delphi List - [EMAIL PROTECTED]
Website: http://www.delphi.org.nz
To UnSub, send email to: [EMAIL PROTECTED]
with body of "unsubscribe delphi"
