In TSQL, what is the best way to create a bunch of records in sequential order in an identification field?

Question

I want to be able to create a group of records at a time and ensure that the identifier field is continuous for the group (without interruptions because someone else logs in and creates the record while it is in the process). I assume that some kind of lock table will work, but I am not a sql guru, so any advice would be appreciated (what type of lock? Any possible problems? Etc.).

For a small background, the table structure is quite simple ...

TABLE PropertyCode
(
    Code INT IDENTITY,
    UserID INT
)

The property code is assigned to the property in the property table. Property codes can be reused (they are printed on characters, and characters can rotate between multiple properties). It is much cheaper to print a continuous sequence of numbers than random numbers on signs.

Answer 1

I'm not sure if this is a good answer or not, but it works ...

CREATE PROCEDURE ReservePropertyCodes
    @Count INT,
    @UserID INT
AS

DECLARE @i INT

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRANSACTION

-- Locks the table
SELECT TOP 1 * FROM PropertyCode WITH (TABLOCK)

SET @i = 0
WHILE @i < @Count
BEGIN
    INSERT INTO PropertyCode (UserID) VALUES (@UserID)
    SET @i = @i + 1 
END
COMMIT TRANSACTION

I can still make selections against the table, but new inserts are locked until my sproc completes.

Answer 2

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT * FROM PropertyCode
INSERT INTO PropertyCode ..... 
COMMIT TRAN

The serializable transaction isolation mode prevents inserts / updates / deletes that will affect any selected data, hence the SELECT * FROM PropertyCode.

, //, .

Answer 3

, . .

( , Code), RangeLock, , , , , , .

SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;
BEGIN TRAN
SELECT Code FROM PropertyCode WHERE Code > IDENT_CURRENT('PropertyCode')
INSERT INTO PropertyCode ..... 
COMMIT TRAN