Tutorial :Best way to get the next id number without “identity”


I have to insert some records in a table in a legacy database and, since it's used by other ancient systems, changing the table is not a solution.

The problem is that the target table has a int primary key but no identity specification. So I have to find the next available ID and use that:

select @id=ISNULL(max(recid)+1,1) from subscriber  

However, I want to prevent other applications from inserting into the table when I'm doing this so that we don't have any problems. I tried this:

begin transaction      declare @id as int      select @id=ISNULL(max(recid)+1,1) from subscriber WITH (HOLDLOCK, TABLOCK)      select @id      WAITFOR DELAY '00:00:01'      insert into subscriber (recid) values (@id)  commit transaction  select * from subscriber  

in two different windows in SQL Management Studio and the one transaction is always killed as a deadlock victim.

I also tried SET TRANSACTION ISOLATION LEVEL SERIALIZABLE first with the same result...

Any good suggestions to how I can ensure that I get the next id and use that without risking that someone else (or me!) is getting hosed?

Sorry for not mentioning this earlier, but this is a SQL 2000 server so I can't use things like FOR UPDATE and OUTPUT

UPDATE: This is the solution that worked for me:

BEGIN TRANSACTION      DECLARE @id int        SELECT  @id=recid      FROM    identities WITH (UPDLOCK, ROWLOCK)      WHERE table_name = 'subscriber'        waitfor delay '00:00:06'        INSERT INTO subscriber (recid) values (@id)        UPDATE identities SET recid=recid+1       WHERE table_name = 'subscriber'    COMMIT transaction    select * from subscriber  

The WAITFOR is so that I can have multiple connections and start the query several times to provoke concurrency.

Thanks to Quassnoi for the answer and to all you other guys that contributed! Awesome!


Create another table:

t_identity (id INT NOT NULL PRIMARY KEY CHECK (id = 1), value INT NOT NULL)  

with a single row, lock this row, and increment value by one each time you need an IDENTITY.

To lock, increment, and return the new value in a single statement, use:

UPDATE  t_identity  SET     value = value + 1  OUTPUT  INSERTED.value  

If you don't want to update, just lock, then issue:

SELECT  value  FROM    t_identity WITH (UPDLOCK, ROWLOCK)  

This will lock the table until the end of the transaction.

If you always first lock t_identity before messing with ancient_table, you will never get a deadlock.


Add another table with an identity column and use this new table and column to select/generate your identity values for the old table.

Update: Depending on the frequency of INSERTS (and the number of existing rows e) you could seed your new IDENTITY values at e+x where x is sufficiently large. Thhis would avoid conflict with the legacy inserts. A sad solution, an imperfect one for sure, but something to think about?


EDIT this is basically the method purposed by @Quassnoi, I just implement it in a loop so you can run it against multiple windows at the same time to see it works great.

set up:

create user's existing table:

create table Subscriber  (  recid  int not null primary key  )  

create new table to keep track of the missing identity, you could add an extra column to keep track of a table if this is needed for multiple tables, but I hve not done that in this example:

CREATE TABLE SubscriberIDs  (  SubscriberID int  )  insert into SubscriberIDs values (0) --row must exist first  

create test script, put this into multiple windows and run them at the same time:

declare @idtable table --will hold next ID to use  (  id int  )  declare @x  int  declare @y  int  set @x=0  while @x<5000 --set up loop  begin      set @x=@x+1      begin transaction      --get the next ID to use, lock out other users      UPDATE SubscriberIDs          SET SubscriberID= SubscriberID+ 1          OUTPUT  INSERTED.SubscriberID          INTO @idtable      --capture the next id from temp table variable      select @y=id from @idtable      --print @y      --use the next id in the actual table      insert into subscriber values (@y)        commit      --print @x      waitfor delay '00:00:00.005'  end --while  

EDIT here is my original attempt, which will eventually get some deadlocks when run in a loop and in multiple windows at the same time. The above method always works. I tried all combinations of transactions, with(holdlock), and set transaction isolation level serializable, etc. but could not get it to run as well as the above method.

set up:

create table subscriber  (  recid  int not null primary key  )  

used to capture the id:

declare @idtable table  (  id int  )  

the insert:

insert into subscriber      OUTPUT INSERTED.recid          recid      INTO @idtable      SELECT ISNULL(MAX(recid),0)+1 FROM subscriber  

list the new id:

select * from @idtable  

list all the ids:

select * from subscriber  


You shouldn't get a deadlock here as the second should just wait for the first to complete. Your issue is that you are creating a transaction, then adding another lock while in that transaction.

Also, you are getting the ID then using it in two separate statements, whereas you could do it all in one solution:

set transaction isolation level serializable  begin transaction      insert into subscriber (recid)          SELECT (select ISNULL(max(recid)+1,1) from subscriber)  commit transaction  select * from subscriber  

This should ensure that you have only consistency on your inserts. However, as you specify that legacy applications are also using this table, can you be certain that when they insert new records it won't conflict with this?

Note:If u also have question or solution just comment us below or mail us on toontricks1994@gmail.com
Next Post »