sql - Achieve sequence number in concurrency -

i new transaction isolation levels. beginner sql well. have 2 stored procedures below.

  create procedure [dbo].[spupdaterecordinfintab]       @ids bigint        begin begin try begin transaction  declare @id bigint set @id = (select [seedno] [dbo].[seedno] [type] = 'abc')+1 declare @inum nvarchar(50)  set @inum = 'abc'+ cast(@id nvarchar(20))  update [dbo].[exchtab] set      [inumber] = @inum      [id] = @ids  update [dbo].[seedno] set   [seedno] = @id [type] = 'inv'  exec spnewroecordintofintab @ids commit end try begin catch     rollback transaction  end catch      end   create procedure [dbo].spnewroecordintofintab     @ids bigint begin         declare @id bigint     set @id = (select [seedno] [dbo].[seedno] [type] = 'abc')+1      declare @inum nvarchar(50)      set @inum = 'abc'+ cast(@id nvarchar(20))      insert [dbo].[exchtab]     values( @inum,123.78,1)      update [dbo].[seedno]     set [seedno] = @id     [type] = 'inv' end 

i want achieve sequence numbers in inumber in exchtab tbale. how achieve when multiple users executing [spupdaterecordinfintab] stored procedure @ same time(concurrency)? transaction isolation level suitable ?

thanks

i use code follows in select instruction, forcing exclusive lock on it:

create procedure [dbo].spnewroecordintofintab  @ids bigint begin     begin transaction     declare @id bigint     select @id = [seedno] [dbo].[seedno] (rowlock, xlock) [type] = 'abc'     set @id = @id + 1     declare @inum nvarchar(50)     set @inum = 'abc'+ cast(@id nvarchar(20))      insert [dbo].[exchtab]     values( @inum,123.78,1)      update [dbo].[seedno]     set        [seedno] = @id     [type] = 'inv'     commit transaction end 

there 2 key parts in proc. first 1 first select, contains rowlock , xlock hints. tell db engine impose exclusive lock on affected rows (instead of default shared lock reads). blocks other concurrent processes on instruction, forcing them wait proc finish. rowlock blocks actual row read, choosen where clause. note that, work, need unique index on column, otherwise you'll end locking whole table.

the other important part transaction management. i've added begin transaction @ beginning , commit transaction @ end. reason locks held duration of transaction. if left out, each statement constitutes own transaction, other readers able mess table , repeated/skipped numbers.