TSQL ID generation

Question

Hi. I have a question regarding locking in TSQL. Suppose I have a the following table:

A(int id, varchar name)

where id is the primary key, but is NOT an identity column.

I want to use the following pseudocode to insert a value into this table:

lock (A)
  uniqueID = GenerateUniqueID()  
  insert into A values (uniqueID, somename)
unlock(A)

How can this be accomplished in terms of T-SQL? The computation of the next id should be done with the table A locked in order to avoid other sessions to do the same operation at the same time and get the same id.

Answer 1

1. You use the NEWID() function and you do not need any locking mechanism

2. You tell a column to be IDENTITY and you do not need any locking mechanism

3. If you generate these IDs manually and there is a chance parallel calls could generate the same IDs then something like this:

---

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE

@NextID = GenerateUniqueID()

WHILE EXISTS (SELECT ID FROM A WHERE ID = @NextID)
BEGIN
  @NextID = GenerateUniqueID()
END

INSERT INTO A (ID, Text) VALUES (@NextID , 'content')

COMMIT TRANSACTION

Answer 2

There are really only three ways to go about this.

1. Change the ID column to be an IDENTITY column where it auto increments by some value on each insert.

2. Change the ID column to be a GUID with a default constraint of NEWID() or NEWSEQUENTIALID(). Then you can insert your own value or let the table generate one for you on each insert.

3. On each insert, start a transaction. Then get the next available ID using something like select max(id)+1 . Do this in a single sql statement if possible in order to limit the possibility of a collision.

On the whole, most people prefer option 1. It's fast, easy to implement, and most people understand it.

I tend to go with option 2 with the apps I work on simply because we tend to scale out (and up) our databases. This means we routinely have apps with a multi-master situation. Be aware that using GUIDs as primary keys can mean your indexes are routinely trashed.

I'd stay away from option 3 unless you just don't have a choice. In which case I'd look at how the datamodel is structured anyway because there's bound to be something wrong.

Answer 3

If you're using SQL2005+, you can use the OUTPUT clause to do what you're asking, without any kind of lock (The table Test1 simulates the table you're inserted into, and since OUTPUT requires a temp table and not a variable to hold the results, #Result will do that):

create table test1( test INT)

create table #result (LastValue INT)


insert into test1
output INSERTED.test into #result(test)
select GenerateUniqueID()

select LastValue from #result

Answer 4

@Markus, you should look at using either IDENTITY or NEWID() as noted in the other answers. if you absolutely can't, here's an option for you...

DECLARE @NewID INT

BEGIN TRAN

SELECT  @NewID = MAX(ID) + 1
FROM    TableA (tablockx)

INSERT  TableA
        (ID, OtherFields)
VALUES  (@NewID, OtherFields)

COMMIT TRAN

Answer 5

If you have custom logic that you want to apply in generating the ids, wrap it up into a user defined function, and then use the user defined function as the default for the column. This should reduce concurrency issue similarly to the provided id generators by deferring the generation to the point of insert and piggy backing on the insert locking behavior.

create table ids  (id int, somval varchar(20))
Go
Create function GenerateUniqueID()
returns int as 
Begin
    declare @ret int
    select @ret =  max(isnull(id,1)) * 2 from ids
    if @ret is null set @ret = 2  
    return @ret
End
go
alter table ids add Constraint DF_IDS Default(dbo.GenerateUniqueID())  for Id