> I have an ID table containing ID numbers that are randomly generated
> and need to be unique. There is a stored procedure that potentially
> generates thousands of these IDs in one execution and inserts them
> into the ID table and various other tables. The basic idea is as
> follows:
>
> Begin Transaction
> While not all IDs generated {
> GenID:
> @NewID = GenerateID()
> If @NewID exists in ID table
> GOTO GenID
>
> Insert into ID table
> Insert into various other tables
> }
> Commit Transaction
>
> The problem occurs when the stored procedure is being run by more than
> one process concurrently. The check to see whether @NewID exists in
> the ID table will block, waiting for the transaction in the other
> process to commit.

> That was how the procedure was originally written; the new keys were
> put into a temporary table as they were generated, and then got copied
> over inside of a transaction. The problem we encountered was that the
> stored procedure generates potentially thousands of keys in one
> execution. With two processes running the stored procedure
> concurrently, we saw a lot of duplicate keys, and had to rollback.

Erland Sommarskog <esquel@sommarskog.se> wrote in message news:<Xns95A8B958CEC2AYazorman@127.0.0.1>...

> OK, so the keys has to be written to a table to be persisted. And this
> may require a transaction, but the transaction should be committed here.
>
> Here is a procedure that we use:
>
>
> CREATE PROCEDURE ak_get_aidkey_sp @aidkey int OUTPUT AS
>
> DECLARE @err int
>
> -- Check transaction.
> IF @@trancount > 0
> BEGIN
> RAISERROR('Internal error: to avoid contention issues, this procedure
> must not be called from a transaction in progress.', 16, 1)
> RETURN 55555
> END
>
> -- Aidkeys is supposed to be emptied once a day, so the below is likely
> -- to generate a unique key at the first shot.
> WHILE 1 = 1
> BEGIN
> SELECT @aidkey = -1 * abs(checksum(newid()))
>
> BEGIN TRANSACTION
>
> IF NOT EXISTS (SELECT * FROM aidkeys (SERIALIZABLE)
> WHERE aidkey = @aidkey)
> BEGIN
> INSERT aidkeys (aidkey) VALUES (@aidkey)
> SELECT @err = @@error IF @err <> 0 RETURN @err
> BREAK
> END
>
> COMMIT TRANSACTION
> END
>
> COMMIT TRANSACTION
>
>
> As you see, there is a transaction, but a very short one. Since you
> generate many keys, you might need to modify the routine. Particularly,
> if you generate 1000 keys in one go, the probability for at least one
> collision increases.
>
> A more brutal solution is to replace you current key column with a
> uniqueidentifier and then use newid(). Then you can forget all about