Best way to get the next id number without "identity"

Question

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!

Answer 1

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?

Answer 2

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.

Answer 3

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

Answer 4

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?