Tengo que insertar algunos registros en una tabla en una base de datos heredada y, dado que es utilizado por otros sistemas antiguos, cambiar la tabla no es una solución.

El problema es que la tabla de destino tiene una clave primaria int pero ninguna especificación de identidad. Así que tengo que encontrar la siguiente ID disponible y usarla:

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

Sin embargo, quiero evitar que otras aplicaciones se inserten en la tabla cuando estoy haciendo esto para que no tengamos ningún problema. Intenté esto:

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

en dos ventanas diferentes en SQL Management Studio y la única transacción siempre se elimina como víctima de un punto muerto.

También probéSET TRANSACTION ISOLATION LEVEL SERIALIZABLE primero con el mismo resultado ...

¿Alguna buena sugerencia sobre cómo puedo asegurarme de obtener la próxima identificación y usarla sin arriesgarme a que alguien más (¡o yo!) Esté recibiendo una manguera?

Lo siento por no mencionar esto antes, pero este es un servidor SQL 2000, así que no puedo usar cosas como FOR UPDATE y OUTPUT

ACTUALIZA: Esta es la solución que funcionó para mí:

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

El WAITFOR es para que pueda tener múltiples conexiones e iniciar la consulta varias veces para provocar concurrencia.

¡Gracias a Quassnoi por la respuesta y a todos los demás que contribuyeron! ¡Increíble