sql - 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

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.