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