SQL atomic choose unused item from list and allocate to another table

Question

I have a list of Channels. I need to use an available one of these if there is one and allocate it to a session. If no available ones are left over I need to create a new one and allocate it to the session, and let the calling routines know that it is a new Channel.

I have the following implementation, which seems to work, but I was wondering is there is a better way to do it.

PS. This is in SQL 2000, otherwise I would have tried to use the output clause of an update statement.

create procedure [dbo].[ReserveChannelSession]
(
     @ID                     int            output
    ,@ApplicationID          int
    ,@ChannelID              int            output
    ,@IsNewChannel           bit            output
)
as
begin
    begin transaction
        set nocount on
        set @ChannelID = ( select top 1 [ID] from [dbo].[Channels] with (updlock,holdlock) where [InUse] = 0 )
        if @ChannelID is null
        begin
            exec InsertChannel  @ID = @ChannelID output , @InUse = 1  -- create as reserved in use
            set @IsNewChannel = 1;
        end else begin
            update [dbo].[Channels] set [InUse] = 1 where [ID] = @ChannelID
            set @IsNewChannel = 0;
        end
        set nocount off

        if @ChannelID is not null
        begin
            insert into [dbo].[ChannelSessions] (
                 [ApplicationID]
                ,[ChannelID]
            ) values (
                 @ApplicationID
                ,@ChannelID
            )
            set @ID=SCOPE_IDENTITY()
        end
    commit transaction
end

Answer 1

I would use updlock,holdlock,rowlock at least.

Also see this question: SQL Server Process Queue Race Condition