Auotincrement without using autoincrement

What do you have against using IDENTITY in this case? The trouble is that
without it you will have to serialize every INSERT, which is likely to be
unacceptable in a multi-user system.
David Portas
SQL Server MVP
--David Portas wrote:
> The trouble is that
> without it (IDENTITY) you will have to serialize every INSERT, which is li
kely to be
> unacceptable in a multi-user system.
It is not really multi-user in the sense you may be thinking. It is a
system which accepts FTP transfers and performs tasks on the received
files. The FTP service I wrote is multi-threaded. Problem is that many
automated clients all like to set up their transfers to run at 1 AM, for
example, so depending on how accurate everyone's clocks are, and how
similar the transmission files are in size, I can easily get 3 additions
made at almost the same time. When there may only be 30-100 additions
total per 24-hour period. I don't need it to be fast, but rather, just
am trying to avoid the primary key constraint issue, if I can, without
error. Obviously I could "try again" if I get an error first time around
from something like
INSERT INTO NMJobTransfers
(WorkID,TransferID,JobID,FileCount,ByteC
ount,Comments)
SELECT MAX(WorkID)+1, 99, 0 ,0 , 0, NULL FROM NMJobTransfers
BUT TO BE SURE I UNDERSTAND your implication, you are saying the above
approach is still going to open a very small window of time where there
could be a duplicate key error on the WorkID primary key column? Is that
correct?
- Lee|||If you set transaction isolation SERIALIZABLE you won't get a duplicate.
What you will get is blocking of simultaneous INSERTs. That is exactly the
problem that the IDENTITY feature is designed to solve. And that's why I
asked why you aren't using IDENTITY to do this.
David Portas
SQL Server MVP
--|||Actually, I am pretty sure that even using serializable you have to do
tricks with locks to make sure that there is no chance of two people getting
the same value because the read is done first, then the insert, so if two
users start looking for the max(nextval) at the same time, they both succeed
unless you use hold exclusive locks on the max value.
----
Louis Davidson - http://spaces.msn.com/members/drsql/
SQL Server MVP
"David Portas" <REMOVE_BEFORE_REPLYING_dportas@.acm.org> wrote in message
news:wIadnZ2dnZ1RIDm4nZ2dnWKlZN-dnZ2dRVn-zZ2dnZ0@.giganews.com...
> If you set transaction isolation SERIALIZABLE you won't get a duplicate.
> What you will get is blocking of simultaneous INSERTs. That is exactly the
> problem that the IDENTITY feature is designed to solve. And that's why I
> asked why you aren't using IDENTITY to do this.
> --
> David Portas
> SQL Server MVP
> --
>